İleri Ders 4: TOPLA.ÇARPIM ile Çok Koşullu Hesap

TOPLA.ÇARPIM tam olarak ne yapar?

TOPLA.ÇARPIM, aynı boyuttaki aralıkları satır satır çarpar ve çarpımların toplamını verir. Adet sütunu C2:C7, birim fiyat sütunu D2:D7 ise =TOPLA.ÇARPIM(C2:C7;D2:D7) her satırın cirosunu bulup toplar. Bu dersteki altı satırlık satış listesinde sonuç 12.400 TL olur.

Aynı sonucu E sütununa =C2*D2 yazıp aşağı kopyalayarak ve sonra TOPLA ile toplayarak da alırsınız. TOPLA.ÇARPIM yardımcı sütuna gerek bırakmaz. Fonksiyon her Excel sürümünde vardır ve dizi formülü gibi Ctrl+Shift+Enter istemez.

Koşul neden çarpılır?

A2:A7="Ankara" gibi bir karşılaştırma, her satır için DOĞRU ya da YANLIŞ üretir. Bu değerleri bir sayıyla çarptığınızda Excel DOĞRU'yu 1, YANLIŞ'ı 0 olarak işler. Böylece koşulu sağlamayan satırın çarpımı sıfırlanır ve toplama katkısı kalmaz.

Bu mantıkla birden fazla koşul art arda çarpılır. (A2:A7="Ankara")*(C2:C7>=100) yalnızca iki koşulun da sağlandığı satırlarda 1 olur. Çarpım "ve" anlamı taşır. "Ya da" için koşulları toplarsınız, ama iki koşulu birden sağlayan satır 2 sayılacağı için sonucu (koşul1+koşul2)>0 biçiminde yeniden karşılaştırmanız gerekir.

ÇOKETOPLA'nın yetmediği yer

ÇOKETOPLA yalnızca var olan bir sütunu toplar. Ankara'da 100 adet ve üzeri satılan kalemlerin cirosunu istediğinizde toplanacak değer adet ile fiyatın çarpımıdır ve böyle bir sütun yoktur. TOPLA.ÇARPIM bu hesabı tek formülde yapar.

=TOPLA.ÇARPIM((A2:A7="Ankara")*(C2:C7>=100)*C2:C7*D2:D7) formülünde iki koşul ve iki sayı sütunu tek bir çarpım dizisinde birleşir. Ankara'nın Kalem satırı 120 x 12,5 = 1.500, Silgi satırı 200 x 8 = 1.600 TL katkı yapar. Defter satırı 60 adette kaldığı için elenir ve sonuç 3.100 TL olur.

Koşulun içinde fonksiyon

ÇOKETOPLA'nın ölçüt aralığına fonksiyon yazamazsınız, TOPLA.ÇARPIM'da ise yazabilirsiniz. Sipariş tarihlerinden yalnızca mart ayındakileri toplamak için =TOPLA.ÇARPIM((AY(A2:A6)=3)*B2:B6) yeterlidir. AY her tarihin ay numarasını çıkarır ve 3'e eşit olanlar 1 olur.

Beş siparişten üçü martta: 850, 1.200 ve 300 TL. Sonuç 2.350 TL'dir. Veri birden fazla yılı kapsıyorsa (YIL(A2:A6)=2026) koşulunu da çarpıma eklemelisiniz, yoksa farklı yılların martları birlikte toplanır.

Ağırlıklı ortalama fiyat

TOPLA.ÇARPIM'ın en pratik kullanımlarından biri ağırlıklı ortalamadır. Kalem, defter ve silgi farklı fiyatlardan ve farklı adetlerde satıldığında "ortalama satış fiyatı" sorusunun cevabı, birim fiyatların basit ortalaması değildir. Çok satılan ürünün fiyatı sonuca daha fazla etki etmelidir.

Doğru hesap toplam ciroyu toplam adede bölmektir: =TOPLA.ÇARPIM(C2:C7;D2:D7)/TOPLA(C2:C7). Satış listesinde ciro 12.400 TL, toplam adet 700'dür ve ağırlıklı ortalama yaklaşık 17,71 TL çıkar. Altı satırdaki birim fiyatların basit ortalaması ise 22,58 TL'dir. Aradaki fark, ucuz silgi ve kalemin çok daha yüksek adetle satılmasından gelir.

Aynı yapı not ortalaması (kredi x not), portföy getirisi (ağırlık x getiri) ve stok değerlemesi gibi her ağırlıklı hesapta kullanılır. Ağırlık sütunu ile değer sütununun aynı satırlarda başlayıp bittiğini kontrol etmek yeterlidir.

Formülün içini görmek

TOPLA.ÇARPIM tek bir sayı döndürdüğü için arka planda üretilen diziyi görmek zordur. Formül çubuğunda formülün bir parçasını, örneğin (A2:A7="Ankara") ifadesini seçip F9'a basarsanız Excel o parçanın sonucunu gösterir: DOĞRU ve YANLIŞ değerlerinden oluşan bir liste. Esc tuşuyla çıkarsanız formül eski hâline döner.

Bu yöntem, beklediğinizden farklı bir sonuç aldığınızda hangi koşulun hangi satırı elediğini bulmanın en hızlı yoludur. Enter'a basarsanız F9 ile hesaplanan değer formüle sabit olarak yazılır, bu yüzden incelemeyi her zaman Esc ile bitirin.

Dikkat edilecek noktalar

Bütün aralıklar aynı sayıda satır içermelidir. A2:A7 ile C2:C8'i birlikte kullanırsanız TOPLA.ÇARPIM #DEĞER! hatası verir. Sayı sütunlarında metin olarak girilmiş bir değer varsa çarpım yine #DEĞER! üretir, bu durumda veriyi sayıya çevirmek gerekir.

Tüm sütun başvurusu (A:A) teknik olarak çalışır, ama bir milyondan fazla satırı çarptığı için dosyayı yavaşlatır. Aralığı veriyle sınırlı tutun ya da veriyi Excel tablosuna çevirip yapılandırılmış başvuru kullanın.

Sıradaki adım

Alıştırmada aynı satış listesinde her bölgenin cirosunu, yardımcı sütun açmadan hesaplayacaksınız. Bölge adını formüle yazmak yerine yandaki hücreden okuyun, böylece formülü aşağı kopyalayabilirsiniz. Bitirdikten sonra koşula (C2:C7>=100) ekleyip yalnızca yüksek adetli satışların bölge cirosunu da hesaplamayı deneyin.

=TOPLA.ÇARPIM(C2:C7;D2:D7)

=SUMPRODUCT(C2:C7,D2:D7)

=TOPLA.ÇARPIM((A2:A7="Ankara")*(C2:C7>=100)*C2:C7*D2:D7)

=SUMPRODUCT((A2:A7="Ankara")*(C2:C7>=100)*C2:C7*D2:D7)

=TOPLA.ÇARPIM((AY(A2:A6)=3)*B2:B6)

=SUMPRODUCT((MONTH(A2:A6)=3)*B2:B6)