Excel SUMPRODUCT İşlevi Nasıl Kullanılır

Örnekler ve açıklamalarla Excel‘deki SUMPRODUCT işlevi ve uygulamaları hakkında bilmeniz gereken her şey.

Toplam, değerlerin toplamıdır ve ürün, değerlerin çarpımıdır. Bu nedenle, SUMPRODUCT işlevi, hücre değerlerini çarpmak ve ardından elde edilen ürünlerini toplamak için kullanılır. Excel’deki en kullanışlı ve gelişmiş matematiksel işlevlerden biridir. Karşılık gelen dizilerdeki veya aralıklardaki sayıları çarpmak ve bu ürün sonuçlarının toplamını döndürmek için kullanılır.

SUMPRODUCT çok yönlü bir işlevdir ve hücreleri sayma, birden çok kritere göre değerleri toplama, değerleri arama, iki aralık arasındaki verileri karşılaştırma ve daha pek çok amaç için kullanılabilir. Ayrıca genellikle ağırlıklı ortalamayı hesaplamak için kullanılır. Toplama ve çıkarmanın yanı sıra, çıkarma ve bölme yapmak için de değiştirilebilir.

Bu yazıda, SUMPRODUCT fonksiyonunun ne olduğunu ve Excel’deki temel ve gelişmiş kullanımlarını örneklerle tartışacağız.

SUMPRODUCT işlevi nedir?

SUMPRODUCT işlevi, karşılık gelen dizileri veya rangerleri çarpmak ve ardından sonuçları toplamak için tasarlanmıştır.

Sözdizimi

=SUMPRODUCT(array1, [array2], [array3],...)

Argümanlar:

Hücre dizisi veya aralıkları aynı sayıda sütun veya satıra sahip olmalıdır, aksi takdirde bir #DEĞER! hata. Dizilerde sayısal olmayan herhangi bir dizi değeri varsa, bunlar sıfır olarak kabul edilecektir.

SUMPRODUCT İşlevinin Temel Kullanımı

SUMPRODUCT fonksiyonunu daha iyi tanımak için öncelikle bazı temel örneklerle fonksiyonun nasıl çalıştığını görelim.

Örnek 1:

Örneğin, ürünlerin, miktarlarının ve fiyatlarının bir listesini içeren aşağıdaki tabloya sahip olduğunuzu varsayalım.

Şimdi, tüm ürünlerin toplam maliyetini bulmak istiyorsunuz. Bunu yapmak için aşağıdaki formülü kullanabilirsiniz:

=SUMPRODUCT(B2:B6,C2:C6)

Yukarıdaki formül, B2:B6’dan her birini karşılık gelen C2:C6 hücreleriyle çarpar ve çarpma sonuçlarını toplar.

SUMPRODUCT işlevinin işlemi formülün içinde şöyle görünür:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)
=(25*8)+(5*9)+(33*12)+(14*15)+(51*5)
=200+45+396+210+255=1106

Örnek 2:

Yukarıdaki formülde, hem dizi 1 hem de dizi 2, tek sütunlara sahiptir. Böylece iki tek sayı sütununu çarpar ve sonuçlarını toplar. Ancak dizileriniz birden çok sütundan oluşuyorsa (örneğin B2:C6), aşağıdaki gibi bir formül yazabilirsiniz:

=SUMPRODUCT(B2:C6,D2:E6)

Formül, A2:B6’nın her değerini karşılık gelen B2:C6 değeriyle çarpar ve ardından tüm ürün değerlerini toplar:

=(B2*D2)+(B3*D3)+(B4*D4)+(B5*D5)+(B6*D6)+(C2*E2)+(C3*E3)+(C4*E4)+(C5*E5)+(C6*E6)
=(25*8)+(5*9)+(33*12)+(14*15)+(51*5)+(17*9)+(7*7)+(53*15)+(24*28)+(82*6)
=200+45+396+210+255+153+49+795+672+492
=3267

Örnek 3:

SUMPRODUCT işlevinde kaç tane dizi belirtirseniz seçin, hepsinin aynı boyutta olması gerekir, aksi takdirde #DEĞER hatası alırsınız. Aşağıdaki formülde, B2:B6 ve C2:C7 dizileri uyumsuz, dolayısıyla #DEĞER hatası.

=SUMPRODUCT(B2:B6,C2:C7)

SUMPRODUCT kullanarak Diğer Aritmetik İşlemleri Gerçekleştirin

SUMPRODUCT, dizileri bölme, toplama veya çıkarma gibi diğer aritmetik işlemleri gerçekleştirmek için de kullanılabilir. Bunu yapmak için tek yapmanız gereken, her diziyi varsayılan virgül (,) yerine uygun matematiksel operatörlerle (+,-,/) ayırmaktır. Ardından fonksiyon, kullanıcı tarafından belirlenen matematiksel işlemleri gerçekleştirecek ve sonuçları toplayacaktır. Matematiksel operatörleri kullanarak diziler arasındaki işlemleri de özelleştirebilirsiniz. Bunu birkaç örnekle nasıl yapacağımızı görelim:

Toplama için SUMPRODUCT :

İki sayı dizisi eklemek (çarpmak yerine) ve ardından sonuçları toplamak için aşağıdaki formülü kullanmanız gerekir:

=SUMPRODUCT(A1:A5+B2:B5)

Burada fonksiyon, her değeri karşılık gelen hücre değerleriyle toplar ve sonucu toplar.

Çıkarma için SUMPRODUCT :

Çarpma yerine çıkarma işlemleri yapmak istiyorsanız aşağıdakine benzer bir formül yazın:

=SUMPRODUCT(A1:A5-B1:B5)

Burada formül, A1:A5’teki her değeri karşılık gelen B1:B5’ten çıkarır ve elde edilen değerleri toplar.

Bölüm için SUMPRODUCT:

Bölme için SUMPRODUCT kullanmak istiyorsanız, aşağıdaki formülü kullanın:

=SUMPRODUCT(A1:A5/B1:B5)

Yukarıdaki formül, A1’deki değeri B1’deki değere, A2’deki değeri B2’ye ve ardından A3’ü B3’e böler ve son olarak sonucu sağlamak için tüm sonuçları toplar: 17.563

SUMPRODCUT Function ile, farklı operatörler kullanarak dizi elemanları (argümanlar) arasındaki aritmetik işlemleri de özelleştirebilirsiniz.

=SUMPRODUCT((A1:A5+B1:B5)/B1:B5)

Burada formül, parantez içine alındığından önce A1:A5 ve B1:B5 aralıklarını ekler ve sonuç değerleri, sonucu veren B1:B5’e bölünür: 22.56.

Tek Kriterli SUMPRODUCT

Sumproduct, SUMIF ve COUNTIF gibi belirli bir koşulu veya kriterleri karşılayan ancak daha fazla esnekliğe sahip değerleri toplamak veya saymak için kullanılabilir. Öncelikle bunu tek bir kriterle nasıl yapabileceğinizi görelim.

Sözdizimi

=SUMPRODUCT(--(criteria_range=criteria),sum_range)

Neresi

Bir koşulu kontrol etmek için, yalnızca Boole değerleriyle (DOĞRU veya YANLIŞ) sonuçlanan mantıksal bir işlem kullanmalıyız. Ancak DOĞRU veya YANLIŞ’ı toplama_aralığı ile çarpmak yalnızca 0’larla sonuçlanır. Bu nedenle, bu boole değerlerini kullanılabilir 1’lere (DOĞRU) ve 0’lara (YANLIŞ) dönüştürmek için koşulu parantez içine almamız ve bundan önce bir çift olumsuzlama operatörü (–) eklememiz gerekir.

Örnek 1:

Örneğin, ‘Portakal’ toplam satışını öğrenmek istediğiniz aşağıda gösterildiği gibi bir veri kümeniz var.

Portakalların toplam satışlarını toplamak için aşağıdaki formülü kullanın:

=SUMPRODUCT(--(A2:A9="Oranges"),C2:C9)

Yukarıdaki formülde, A2:A9="Oranges"bir dizi mantıksal değer döndürür: {YANLIŞ, DOĞRU, YANLIŞ, YANLIŞ, YANLIŞ, DOĞRU, YANLIŞ, DOĞRU}. Formül yalnızca A2:A9 aralığında “Portakallar” değerini içeren hücreler için DOĞRU, diğer değerleri içeren hücreler için YANLIŞ döndürür. Ve çift olumsuzlama (çift eksi) bu değerleri 1’ler ve 0’lar dizisine dönüştürür: {0, 1, 0, 0, 0, 1, 0, 1}.

Bundan sonra formül şöyle görünecek:

=SUMPRODUCT({0, 1, 0, 0, 0, 1, 0, 1}, {5620, 854, 623, 452, 56, 56, 85, 100})
=(0, 1, 0, 0, 0, 1, 0, 1) x (5620, 854, 623, 452, 56, 56, 85, 100)
=854+56+100
=1010

Ardından SUMPRODUCT, dizi1’deki her değeri dizi2’deki karşılık gelen değerle çarpar ve ardından elde edilen diziyi toplar: 1010.

Örnek 2:

Belirli bir koşulu karşılayan değerlerin toplam çarpımını bulmak için SUMPRODUCT formülünü de kullanabilirsiniz. Örneğin, aşağıdaki örnekte Sumproduct’ı iki dizi için çarpmak istiyoruz, ancak yalnızca ‘Oranges’ adlı ürün için.

Bunu yapmak için aşağıdaki formülü kullanın:

=SUMPRODUCT((B2:B9*C2:C9)*(A2:A9="Oranges"))

Burada formül, A2:A9="Oranges"A2:A9 aralığında ‘Portakallar’ ürün adlarını arar ve diziyi döndürür – {YANLIŞ, DOĞRU, YANLIŞ, YANLIŞ, YANLIŞ, DOĞRU, YANLIŞ, DOĞRU}. Ardından formül, B2:B9 ve C2:C9 dizilerini çarpar ve başka bir sonuç dizisi döndürür.

Koşul bağımsız değişkeninden önce çift olumsuzlama (–) kullanmaya gerek yoktur, çünkü iki bağımsız değişken arasındaki çarpma operatörü aynı zamanda bir dönüştürücü görevi görür ve DOĞRU ve YANLIŞ değerlerini (A2:A9=”Portakallar”) değerinden 1s ve 0s değerine zorlar. . Sonuç olarak, yalnızca aralıkta ‘Portakal’ bulunan değerler sonuç olarak 1’e sahip olacaktır. Böylece formül şöyle yüklenecek: =SUMPRODUCT({280, 360, 30, 252, 225, 90, 288, 160}*{0, 1, 0, 0, 0, 1, 0, 1}). Dolayısıyla çıktı olarak ‘610’ elde ederiz.

Çoklu Kriterli SUMPRODUCT

SUMPRODUCT, değerleri birden çok koşula göre saymak ve toplamak için EĞERSAY ve ETOPLA için iyi bir alternatiftir. EĞERSAYLAR ve ETOPLALAR’dan farklı olarak hem VE hem de VEYA mantık formülleriyle çalışabilir.

AND Mantığı ile Çoklu Kriterlere Dayalı Toplam Hücreler

AND mantık formülü için, belirtilen dizideki karşılık gelen değerleri toplamak için tüm koşullar veya ölçütler bir satırda karşılanmalıdır.

Sözdizimi

=SUMPRODUCT(--(criteria_range1=criteria1),--(criteria_range2=criteria2), sum_range)

Formülü kısaltmak için dizi öğeleri arasındaki virgül yerine AND işleci yıldız işareti (*) de koyabilirsiniz. 

Örnek 1:

Aşağıdaki veri setine sahip olduğumuzu ve ‘Doğu’ bölgesi için ‘Şeftali’ meyvesinin toplam Satış değerlerini toplamak istediğimizi varsayalım.

‘Doğu’ bölgesi için ‘Şeftali’ meyvesinin toplam satışını toplamak için aşağıdaki formülü yazın:

=SUMPRODUCT(--(A2:A15="Peach"),--(B2:B15="East"),D2:D15)

veya

=SUMPRODUCT(--(A2:A15=G2),--(B2:B15=G3),D2:D15)

Yukarıdaki formüllerin ikisi de size aynı sonucu verir. Ancak ikinci formülde, formülü kısaltmak için ölçüt değerlerini içeren hücrelere atıfta bulunduk.

Yukarıdaki formülde Şeftali ve Doğu, belirtilen iki kriterdir.

Alternatif olarak, yukarıdaki formülden biraz daha kısa olan aşağıdaki formülü kullanarak yukarıdaki hesaplamayı yapabilirsiniz:

=SUMPRODUCT((A2:A15=G2)*(B2:B15=G3)*D2:D15)

Yukarıdaki formülde Yıldız işareti (*) iki koşulu çarpmak için kullanılır ve yıldız işareti de SUMPRODUCT’te AND operatörü olarak kabul edilir. Diziler arasına yıldız işareti karakterleri eklerseniz, mantıksal koşuldan önce çift olumsuzlama (–) eklemeniz gerekmez.

Örnek 2:

Örneğin, miktar ve fiyatı çarparak Doğu bölgesindeki Şeftali meyvesinin toplam satışını bulun ve ardından sonuçları toplayın. Bunun için aşağıdaki formülü kullanabilirsiniz

=SUMPRODUCT((A2:A15=G2)*(B2:B15=G3)*(C2:C15*D2:D15))

Bu formül, sırasıyla A ve Sütun B’deki koşulları (Şeftali ve Doğu) kontrol eder. Ve her iki değer de aynı satırda bulunursa (her iki koşul da karşılanırsa), formül çarpar ve karşılık gelen değerleri C ve D sütunlarına ekler.

VEYA Mantığı ile Çoklu Kriterlere Dayalı Toplam Hücreler

EĞERSAYLAR ve ETOPLALAR’dan farklı olarak, SUMPRODUCT, VEYA mantığıyla koşullu olarak değerleri toplamanıza veya saymanıza izin verir. VEYA mantık formülü için, belirtilen dizideki karşılık gelen değerleri toplamak için ölçütlerden herhangi birinin bir satırda karşılanması gerekir. VEYA mantığını uygulamak için diziler arasında yıldız işareti yerine artı sembolü (+) kullanmanız gerekir.

Artı işareti (+), VEYA operatörü olarak çalışır ve formülde verilen kriterlerden herhangi biri DOĞRU olarak değerlendirilirse DOĞRU döndürür.

sözdizimi :

=SUMPRODUCT((criteria_range1=criteria1)+(criteria_range2=criteria2), sum_range)

Örnek vermek:

Bölgeden bağımsız olarak Şeftali ve/veya Elma toplam satışlarını bulmak istiyorsanız aşağıdaki gibi bir formül yazın:

=SUMPRODUCT((A2:A15=G2)+(A2:A15=I3),C2:C15*D2:D15)

Yukarıdaki formül, A sütunundaki ‘Elmalar’ (I2) ve ‘Şeftali’ (G2) ürünlerini arar ve ardından C (Miktar) ve D (Fiyat) sütunlarındaki karşılık gelen değerleri çarpar ve koşullardan herhangi biri karşılanırsa sonucu toplar. . Bu, C ve D sütunlarını çarpacağı ve bölgeden bağımsız olarak tüm Elmalar ve Şeftali ürünleri için değerler ekleyeceği anlamına gelir.

VE ve VEYA Mantığı ile Çoklu Kriterlere Dayalı Toplam Hücreler

Bazen, SUMPRODUCT işleviyle kolayca yapabileceğiniz gibi, aynı anda hem VEYA hem de VE mantığıyla hücreleri koşullu olarak toplamanız gerekebilir.

Örnek 1:

Örneğin, Doğu bölgesindeki Elma ve Şeftali satışlarının toplamını bulmak istiyorsanız, AND ve OR mantığının birleşimi şu şekilde görünecektir (sözdizimi):

=Sum If ((Product="Apples") OR (Product="Peach")) AND (Region="East"))

Şimdi, ‘VE’yi (*) ve VEYA’yı (+) ile değiştirerek yukarıdaki mantığı gerçek formüle uygulayın.

Doğu bölgesindeki ‘Şeftali’ ve ‘Elma’ satışlarını toplamak için aşağıdaki formülü kullanın:

=SUMPRODUCT(((A2:A15=G2)+(A2:A15=I2))*(B2:B15=G3),D2:D15)

Formülü çözelim:

Örnek 2:

Yukarıdaki formül, VE ve VEYA mantığıyla birden çok kritere dayalı değerlerin toplamını bulur. Ancak değerlerin Toplam Ürününü bulmak istiyorsanız, aşağıdaki formülü kullanmanız gerekir:

=SUMPRODUCT(((A2:A15=G2)+(A2:A15=I2))*(B2:B15=G3),C2:C15*D2:D15)

Bu formül, formüle başka bir dizi eklememiz dışında hemen hemen aynıdır. Şimdi, aynı satırda ‘Elma’ veya ‘Şeftali’ ve ‘Doğu’ değeri bulunursa, C ve D sütunlarındaki karşılık gelen değerler çarpılır ve şu sonucu vermek için toplanır: 637.

SUMPRODUCT ve Tam İşlevi Kullanan Toplam Hücreler

Büyük/küçük harfe duyarlı bir değer aramanız ve bunlara karşılık gelen değerleri SUMPRODUCT işlevini kullanarak toplamanız gerekirse, formülün içine EXACT işlevini eklemeniz gerekir.

Bir tabloda büyük/küçük harfe duyarlı bazı değerleriniz olduğunu ve bunlara karşılık gelen değerleri toplamak istediğinizi varsayalım, aşağıdaki formülü deneyin:

=SUMPRODUCT(--EXACT("Oranges",A2:A15),D2:D15)

Formülün bölümü , EXACT("Oranges",A2:A15)A2:A15 aralığında aynı büyük/küçük harf karakterleriyle tam olarak belirtilen dizeyi (Portakallar) arar. Bir eşleşme bulunursa, DOĞRU veya YANLIŞ döndürür. Bu doğru veya yanlış sonuçlar, çift negatif – {0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 1} tarafından sayısal değerlere dönüştürülür. Ardından, elde edilen dizi toplam dizisi (D2:D15) ile çarpılır. Ve nihai ürün sonuçları toplanır ve size toplam toplamı verir – 248.

SUMPRDUCT işlevini kullanarak Hücreleri Say

Excel’de hücrelerin koşullu olarak nasıl toplandığını gördük, şimdi de SUMPRODUCT fonksiyonunu kullanarak ölçüt/kriterleri karşılayan hücrelerin nasıl sayılacağını görelim.

sözdizimi :

=SUMPRODUCT(--(array=condition))

Tek Kriterli Hücreleri Sayma

Sırasıyla A, B ve C sütunlarında meyvelerin, miktarlarının ve fiyatlarının bir listesinin olduğunu varsayalım.

Şimdi, miktarı 20’den az olan kaç tane öğeyi bulmak istiyoruz. Bunu yapmak için aşağıdaki formüllerden herhangi birini kullanabiliriz:

=SUMPRODUCT(--(C2:C15<20))

Burada C2:C15<20koşul, C sütunundaki her bir değerin 20’den küçük olup olmadığını kontrol eder ve bu sonuç dizisini döndürür –

{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}.

Ardından, sonuç dizisi çift olumsuzlama ile 1’lere ve 0’lara dönüştürülür: {0, 0, 1, 1, 1, 0, 1, 0, 0, 1, 0, 1, 0,1}. Bundan sonra, SUMPRODUCT bize hücre sayısını vermek için tüm sonuçları toplar – ‘7’.

Alternatif olarak, sonuç dizisini 1 ile çarparak boole değerlerini sayısal değerlere de dönüştürebilirsiniz:

SUMPRODUCT((C2:C15<20)*1)

Her iki durumda da, her iki formül de size aynı sayıda sayı verecektir.

AND Mantığı ile Çoklu Kriterlere Dayalı Hücreleri Sayma

Birden fazla koşulunuz varsa ve yalnızca tüm bu koşullar karşılandığında hücreleri saymak istiyorsanız, VE mantığı ile SUMPRODUCT formülünü kullanabilirsiniz.

Örnek vermek:

Örneğin, Şeftali satışlarının kaç kez 100’den az olduğunu saymak istiyorsanız, SUMPRODUCT formülüne iki ölçüt ekleyebilirsiniz – biri A sütununda ‘Şeftali’ öğesini aramak için ve diğeri ilgili satışların daha az olup olmadığını kontrol etmek için. 100’den fazla:

=SUMPRODUCT(--(A2:A15="Peach"),--(D2:D15<100))

veya kısa bir formül istiyorsanız, bunun yerine şunu deneyebilirsiniz:

=SUMPRODUCT((A2:A15=G3)*(D2:D15<100))

A2:A15="Peach"veya A2:A15=G3A2:A15 sütunundaki ‘Şeftali’ öğesini D2:D15<100kontrol eder ve 100’den az satış tutarını kontrol eder. Aynı satırda her iki koşul da karşılanıyorsa (4. satır ve 6. satır), bu satırları sayar ve sonucu döndürür. .

Formül içinde A2:A15="Peach"koşul, dönüştürülmüş sonuçların bu dizisini döndürür: {0, 0, 1, 0, 1, 0, 0, 0, 1, 0, 1, 0, 1, 0} ve D2: D15<100dönüştürülen sonuçların bu dizisini döndürür : {0, 0, 1, 1, 1, 1, 1, 0, 0, 0,0,1, 0, 0}. Ve her ikisi de diziler çarpıldığında, ‘2’ sayısını alacaksınız.

VEYA Mantığı ile Çoklu Kriterlere Dayalı Hücreleri Sayma

VEYA mantığı ile hücreleri saymak için diziler arasında artı sembolü (+) sembolünü kullanabilirsiniz.

Örnek vermek:

Bölgeden bağımsız olarak Şeftali ve Portakal satışlarının sayısını bulmak için aşağıdaki formülü girin:

=SUMPRODUCT((A2:A15="Peach")+(A2:A15="Oranges"))

Yukarıdaki formülde, diziler arasındaki artı işareti (+), VEYA operatörü olarak işlev görür ve verilen iki koşuldan herhangi biri karşılanırsa DOĞRU döndürür. Listede ‘Şeftali’ veya ‘Turuncu’ öğelerinden biri bulunursa, formül DOĞRU değerini döndürür. VEYA operatörü ayrıca bir dönüştürücü görevi görür ve şu değerler dizisini üretir: {0, 1, 1, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1, 0}. Ardından, çıktıyı üretmek için ortaya çıkan dizi eklenir: 8.

Hücreleri Çoklu Kriterlere Dayalı Olarak VE ve VEYA VEYA Mantığıyla Sayma

Benzer şekilde, aynı anda hem VEYA hem de VE mantığıyla SUMPRODUCT işlevini kullanarak hücreleri koşullu olarak sayabilirsiniz.

Örnek vermek:

Doğu bölgesindeki Portakal ve Şeftali satışlarının sayısını bulmak için aşağıdaki formülü kullanın:

=SUMPRODUCT(((A2:A15="Peach")+(A2:A15="Oranges"))*(B2:B15="East"))

Burada, (A2:A15="Peach")+(A2:A15="Oranges")formülün bir kısmı A2:A15’te ‘Şeftali’ veya ‘Portakal’ öğesini kontrol eder. Öğelerden herhangi biri A sütununda bulunursa, ‘1’, aksi takdirde ‘0’ olan DOĞRU (aksi halde YANLIŞ) olarak değerlendirilir.

(B2:B15="East") B sütununda ‘Doğu’ değerini arar ve bulunursa, yine ‘1’e dönüştürülen DOĞRU olarak değerlendirilir. Ardından, VEYA ve VE mantığından elde edilen sonuçların her ikisi de çarpılarak – {0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 0, 1, 0,0} elde edilir. Sonunda, sonuçların son dizisi – ‘4’ olarak toplanır.

SUMPRODUCT ve COUNTIF Formülünü kullanarak Farklı Değerleri Sayma

SUMPRODUCT formülü, bir değerler listesinden farklı değerler saymak istediğinizde de yararlıdır. Farklı değerler, bir listedeki benzersiz girişler de dahil olmak üzere tüm farklı değerlerdir. Farklı değerler de bazı durumlarda benzersiz değerler olarak kabul edilir, bu nedenle onları nasıl bulacağınızı bilmek faydalıdır.

Bir değerin kaç kopyası olduğu önemli değildir, sayıma bu değerin yalnızca bir örneği dahil edilir. Örneğin, ‘New York’ değeri bir listede 5 kez tekrarlansa yine de ‘1’ olarak sayılır.

Farklı değerleri saymanın en kolay formülü, SUMPRODUCT ve COUNTIF kombinasyonudur.

Bir sütundaki benzersiz ve farklı değerleri saymak için kullanılan sözdizimi şöyledir:

=SUMPRODUCT(1/COUNTIF(data,data))

data Değerleri saymak istediğiniz veri aralığı nerede

Örneğin, aşağıdaki listedeki farklı değerlerin sayısını bulmak istiyoruz:

Bunu yapmak için aşağıdaki formülü kullanın:

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

Sizin için parçalayalım:

Gördüğünüz gibi, yalnızca bir kez görünen (benzersiz) değer 1’dir ve birden çok kez görünen değerler kesir haline gelecektir. Örneğin, ‘San Francisco’ değeri iki kez görünür, yani 1 ikiye bölündüğünde ‘0.5’ elde edersiniz.

Son olarak, SUMPRODUCT işlevi dizideki tüm sayıları toplar ve farklı değerlerin sayısını döndürür: ‘5’. Sayı, yinelenenler hariç, listede en az bir kez görünen tüm farklı değerleri içerir.

SUMPRODUCT ve COUNTIF Formülünü Kullanarak Boş Hücreleri Yok Sayarak/İçererek Farklı Değerleri Sayma

Ancak, yukarıdaki formülü kullanırken, aralıktaki herhangi bir hücre boş veya boşsa, formül #SAYI/0 hatası verebilir. Bunun nedeni, EĞERSAY formülü tarafından oluşturulan sonuç dizisinde boş bir hücrenin 0 üretmesidir. Dolayısıyla, 1’in 0’a bölünmesi, aşağıda gösterildiği gibi bir #SAYI/0 hatasıyla sonuçlanacaktır.

Farklı değerleri sayarken boş veya boş hücreleri sayıma dahil  etmek için aşağıdaki formülü deneyin:

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10&""))

Yukarıda görebileceğiniz gibi, EĞERSAY işlevinin ölçüt argümanındaki verilere boş bir dize (“”) eklediğimizde, sonuç dizisindeki boş bir hücre için 1 döndürür:  {1;4;1;4;2;1;1;2;4}. Yani formül,  1/COUNTIF(A2:A10,A2:A10&"") =  {1;0.25;1;0.25;0.5;1;1;0.5;0.25}. Son olarak, SUMPRODUCT işlevi dizideki tüm sayıları toplar ve bize sayımdaki boş hücreyi de içeren 6 farklı değerler verir.

Farklı değerleri sayarken boş veya boş hücreleri yok saymak  için aşağıdaki formülü kullanın:

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

Burada,  A2:A10<>"" DOĞRU veya YANLIŞ bir sonuç dizisi üretir. Aralıkta bir hücre boş veya boş olduğunda, YANLIŞ döndürür. Dolayısıyla YANLIŞ değeri herhangi bir sayıya bölündüğünde 0 döndürür. Sonuç olarak, şimdi bu formül (A2:A10<>””)/ÇOK EĞERSAY(A2:A10,A2:A10&””) şu diziyi döndürür: {1 ;0.25;1;0.25;0.5;1;0;0.5;0.25}. SUMPRODUCT, sonuç dizisini özetlediğinde, son sayı olarak ‘5’ alırsınız.

SUMPRODUCT Fonksiyonu ile İki Yönlü Arama

Genellikle, değerleri iki boyutta aramak için INDEX ve MATCH işlevlerini kullanabilirsiniz, ancak Excel’in önceki sürümlerinde, SUMPRODUCT genellikle iki yönlü arama ve ayıklama için kullanılırdı. SUMPRODCUT aynı zamanda değerleri iki yönde arama konusunda da mükemmel bir yeteneğe sahiptir.

Eric’in Geçmiş konusundaki puanını aramak ve döndürmek istediğimiz aşağıdaki veri setine sahip olduğumuzu varsayalım.

İki yönlü arama yapmak için şu SUMPRODUCT formülünü yazın:

=SUMPRODUCT((A2:A10=D13)*(A1:G1=D14),A2:G10)

Formülde, A2:A10=D13A2:A10 sütununun aşağısındaki D13 hücresinin değerini A1:G1=D14arar ve A2:A10 satırı boyunca D14 hücresinin değerini arar. Ardından, SUMPRODUCT işlevi, iki aramanın kesiştiği noktada 67 olan değeri döndürür.

Aynı koşulu sağlayan birden fazla değer varsa, formül, dönen değerlerin toplamını döndürür.

Örneğin, öğrenci adları sütununda iki Eric varsa, işlev her ikisinin de Geçmiş puanlarının toplamını döndürür:

SUMPRODUCT Fonksiyonu ile Ağırlıklı Ortalamayı Hesaplayın

SUMPRODUCT işlevinin başka bir yaygın uygulaması, bir veri kümesindeki her değere aynı ağırlığın atandığı ağırlıklı bir ortalama hesaplamaktır. Ağırlıklı ortalama, değişen önem derecelerini hesaba katan bir veri kümesinin ortalamasıdır.

SUMPRODUCT ağırlıklı ortalama formülünün sözdizimi:

=SUMPRODUCT(values, weights) / SUM(weights)

Örneğin, B sütununda aşağıdaki not istatistikleri tablosuna ve C sütununda ağırlıklarına sahip olduğunuzu varsayalım.

=SUMPRODUCT(C2:C6, B2:B6) / SUM(B2:B6)

Yukarıdaki formülde, her bir yüzdeyi (ağırlık) karşılık gelen notla çarpar ve elde edilen bu ürün değerlerini birbirine eklersiniz. Bundan sonra, bu ürün sayısı toplamını beş ağırlığın toplamına bölün.

Yukarıdaki formülün içinde şöyle görünür:=(B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(B2+B3+B4+B5+B6)

EĞER işlevli SUMPRODUCT

Bir koşula göre değerleri toplamak için ‘SUMPRODUCT’ ve ‘IF’ işlevini birleştirebilirsiniz.

Sözdizimi:

=SUMPRODUCT(IF(criteria_range=criteria, values_range1*values_range2))

Aşağıdaki veri setinden ‘Portakalların’ toplam satışını bulmamız gerekiyor. Bunu yapmak için aşağıdaki formülü kullanabiliriz:

=SUMPRODUCT(IF(A2:A15=G3,C2:C15*D2:D15))

 Formülü girdikten sonra bu formülü dizi formülü olarak uygulamak için Ctrl+ + Shifttuşlarına aynı anda basmanız gerekir .Enter

EĞER işlevi, A2:A15 aralığında “Portakallar” değerini kontrol eder ve bulunursa, C ve D sütunlarındaki karşılık gelen değerleri çarpar. Ardından, Sumproduct işlevi tüm ürün değerlerini toplar.

Çoklu Kriterli SUMPRODUCT ve IF

Batı bölgesindeki Portakalların toplam satışını bulmak için formüle başka bir kriter eklememiz gerekiyor:

=SUMPRODUCT(IF(A2:A15=G2,IF(B2:B15=G3,C2:C15*D2:D15)))

Formülü yazdıktan sonra dizi formülü olarak uygulamak için Ctrl+ Shift+ tuşlarına bastığınızdan emin olun.Enter

SUMPRODUCT formülündeki ilk EĞER işlevi, A2:A15 aralığında G2’ye (Turuncular) girilen değeri arar. Değer bulunursa, ikinci EĞER işlevi G3’te (Batı) B2:B15 aralığında girilen değerleri kontrol eder. Ve her iki koşul da karşılanırsa, SUMPRODUCT, C2:C15 ve D2:D15’teki karşılık gelen değerleri çarpar. Belirtilen değerlerden herhangi birinin aralıklarda bulunmaması durumunda YANLIŞ döndürür. 

Sonuç olarak, şu sonuç dizisini elde ederiz – {FALSE;760;FALSE;FALSE;FALSE;FALSE;114;FALSE;FALSE;FALSE;1311;FALSE;FALSE;99. Burada YANLIŞ, 0 anlamına gelir. Son olarak, SUMPRODUCT işlevi, 859 – döndürmek için sonuçları toplar. 

SUMPRODUCT ve Diğer Fonksiyonlar

SUMPRODUCT işlevi, dizi işlevselliğini işlemek için diğer işlevlerle entegre edilebilir. Uzun formüller girmek ve dizi formüllerini her çalıştırmak istediğinizde Ctrl+ Shift+ tuşlarına basmak yerine, doğrudan diziler üzerinde hesaplamalar yapmak için SUMPRODUCT’i kullanabilirsiniz.Enter

Örnek 1:

Örneğin, A2:A6 aralığında 5 farklı kelime veya metin dizesi listeniz olduğunu ve 5 metin dizesinin toplam karakterlerini saymak istediğinizi varsayalım. Normalde, B sütununa bir yardımcı sütun ekler ve =UZUNLUK(A2) gibi bir UZUNLUK işlevi girersiniz, ardından aynı formülü B3, B4, B5 ve B6 hücrelerine uygularsınız. Son olarak, =SUM(A2:A6) gibi tüm sonuçları eklemek için SUM işlevini kullanırsınız.

Ancak, SUMPRODUCT işleviyle, ekstra yardımcı sütun olmadan tüm karakterleri tek bir formülle toplayabilirsiniz:

=SUMPRODUCT(LEN(A2:A6))

AY işlevli SUMPRODUCT

SUMPRODUCT, AY işlevini kullanarak aya göre değerleri toplamak veya saymak için kullanılabilir.

Örneğin, D2 hücresine bir ay numarası girdik ve belirtilen ay için değerleri saymak ve toplamak istiyoruz.

Mart ayı satışlarını saymak için aşağıdaki formülü kullanabiliriz:

=SUMPRODUCT(--(MONTH(B2:B12)=E3))

Burada MONTH işlevi, B2:B12’deki her tarihten ayı çeker ve E3’te belirtilen aya göre kontrol eder. Eşitse, DOĞRU, YANLIŞ, aksi halde – {DOĞRU, YANLIŞ, YANLIŞ, YANLIŞ, YANLIŞ, YANLIŞ, YANLIŞ, DOĞRU, YANLIŞ, DOĞRU, DOĞRU} döndürür. 

Ardından, çift tire veya negatif diziyi şuna dönüştürür: {1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1}. Bundan sonra, SUMPRODUCT, mart satışlarının sayısını – 4’ü üretmek için diziyi toplar.

Mart ayı toplam satışlarını bulmak isterseniz aşağıdaki formülü girin:

=SUMPRODUCT(--(MONTH(B2:B12)=E3),C2:C12)

MONTH işlevi, B2:B12’deki her tarihten ayı çeker ve E3’te belirtilen aya göre kontrol eder. Eşitse DOĞRU, YANLIŞ döndürür, aksi takdirde {1, 0, 0, 0, 0, 0, 0, 1, 0, 1,1}’e dönüştürülür. Ardından, SUMPRODUCT, elde edilen diziyi Satış sütunundaki (C2:C12) karşılık gelen değerlerle çarpar ve Mart ayının toplam satışlarını – 2118’i döndürmek için ürün değerlerini toplar.

ISNUMBER işlevli SUMPRODUCT

Bir hücre aralığındaki belirli sözcüklerin oluşum sayısını saymak istiyorsanız, bir formül oluşturmak için SUMPRODUCT ile ISNUMBER ve BUL işlevini birleştirebilirsiniz.

D5 hücresinde belirtilen sözcüklerin tüm oluşumlarını saymak için aşağıdaki formülü kullanın:

=SUMPRODUCT(--(ISNUMBER(FIND(D5,A2:A16))))

Yukarıdaki formülde, BUL işlevi, A2:A16 aralığının her hücresinde D5 (Xerox) hücresine girilen metin dizesini arar. Metin dizesi bulunursa, işlev kelimenin başlangıç ​​konumunu döndürür, aksi takdirde bir #DEĞER! hata.

Bundan sonra, BUL işlevi bir sayı üretirse ISNUMBER işlevi DOĞRU, aksi takdirde YANLIŞ döndürür. Ardından, çift negatif ISNUMBER sonuçlarını buna dönüştürür – {0, 1, 1, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0, 1}. Son olarak, SUMPRODUCT sonuç dizisini – 6 olarak toplar.

BÜYÜK/KÜÇÜK işlevli SUMPRODUCT

BÜYÜK ve KÜÇÜK işlevi, değerlere göre azalan veya artan düzende sıralandığında, değerler listesindeki en büyük ve en küçük n’inci sayıları döndürür. 

Örneğin, en büyük 5 değeri toplamak istiyorsanız, aşağıdaki formülü deneyin:

=SUMPRODUCT(LARGE(A1:A15,{1,2,3,4,5}))

Formülde, BÜYÜK işlevi A1:A15 listesinden ilk 5 değeri döndürür. Genellikle, BÜYÜK işlevi en büyük n’inci değeri döndürür, ancak {1, 2, 3, 4, 5}’i çift parantez içine aldık. Böylece, en büyük 5 sayının tümünü döndürür – {111, 95, 69, 52, 49}. Ardından, SUMPRODUCT formülü bu sayıları bir toplama – 377’ye ekler.

En küçük 5 değeri toplamak için aşağıdaki formülü girin:

=SUMPRODUCT(SMALL(A1:A15,{1,2,3,4,5}))

Formülde, KÜÇÜK işlevi, A1:A15 aralığının en alttaki 5 sayısını döndürür. Genellikle, SMALL işlevi en büyük n’inci değerleri döndürür, ancak {1, 2, 3, 4, 5}’i çift parantez içine aldık. Böylece, en küçük 5 sayının tümünü döndürür – {3, 5, 9, 10, 12}. Ardından, SUMPRODUCT formülü bu sayıları toplar ve en küçük 5 sayının toplamını verir – 39.

Dizileri SUMPRODUCT ile Karşılaştırın

SUMPRODUCT işlevi, yalnızca birden çok sütundan birden çok kritere sahip hücreleri koşullu olarak saymak, toplamak, ortalamakla kalmaz, aynı zamanda iki veya daha fazla diziyi karşılaştırabilir.

Aşağıdaki tabloya sahip olduğunuzu ve dünkü satışlarla bugünün satışlarını karşılaştırmak ve bugün kaç ürünün dünden daha fazla satışı olduğunu bulmak istediğinizi varsayalım. Bunu yapmak için normalde C sütununu D ile karşılaştırmanız ve skoru korumanız gerekir. Ancak SUMPRODUCT ile işlemi tek bir formülle otomatikleştirebilirsiniz. Bunu nasıl yapabileceğimize bir bakalım:

İşte C ve D sütunlarını karşılaştırmak için kullanabileceğiniz bir SUMPRODUCT formülü:

=SUMPRODUCT(--(D2:D12>C2:C12))

Burada, bugünün satışlarının dünün satışlarından fazla olup olmadığını görmek istiyoruz, bu nedenle dizi argümanları arasına (>) operatörünü ekledik.

Formül, Bugünün satışları sütununun her değerini, Dünün Satışları sütununun karşılık gelen değeriyle kontrol eder. D2:D12 aralığındaki bir değer, C2:C12’deki karşılık gelen değerlerden büyükse, DOĞRU sonucunu verir; değilse, YANLIŞ döndürür. Ardından, çift tekli (–) bu sonucu {1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0} dizisine dönüştürür. Bundan sonra, tüm bu değerler toplanır ve bugün dünden daha fazla satılan ürün sayısı olan toplam (3) olarak döndürülür.

Peki ya sadece düne göre kaç tane Şeftali satışının daha yüksek olduğunu görmek istiyorsanız? Ardından, argümanlarda başka bir koşula ihtiyacınız var:

=SUMPRODUCT(--(A2:A12="Peach"),--(D2:D12>C2:C12))

Argüman --(A2:A12=”Peach”), ‘Şeftali’ değerlerinin A2:A12 aralığında olup olmadığını kontrol eder ve bir hücrede ise 1 döndürür; değilse, 0 döndürür. Bu, {0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1} başka bir sonuç dizisi oluşturur.

=SUMPRODUCT({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1}, {0, 1, 1, 0, 1, 0, 1, 1, 0, 1,1})

Ardından, Sumproduct işlevi, ilk diziyi ikinci dizideki karşılık gelen çiftiyle çarpar ve çıktıyı üretmek için ürün sonucunu toplar, bu 2’dir. Gördüğünüz gibi, bugünün Şeftali satışlarının yalnızca 2 katı vardır. dün.

 

Check Also

Masaüstü ve Mobil için Gmail’de E-postalar Nasıl Planlanır

E-postalarınızı planlamak, belirli e-postaları göndermeyi silmenin harika bir yoludur! Gmail, bugün dünyanın en popüler e-posta …

Bir cevap yazın

E-posta hesabınız yayımlanmayacak.