Orta Ders 1: ETOPLA ve EĞERSAY ile Koşullu Hesap

Koşullu hesap neden gerekir?

Koşullu hesap, bir tablonun tamamını değil yalnız belirli bir şartı sağlayan satırları toplamanızı ya da saymanızı sağlar. Başlangıç seviyesinde TOPLA ile bir sütunun hepsini toplamıştınız. Gerçek tablolarda ise soru çoğu zaman daha dardır: "Elektronik kategorisinde ne kadar satış yaptık?" ya da "Kaç sipariş İzmir'den geldi?"

Bu soruları filtre açıp elle toplamak yerine tek formülle cevaplayabilirsiniz. Excel ETOPLA formülü toplama işini, EĞERSAY ise sayma işini koşula bağlar. Veri değiştiğinde iki formülün sonucu da kendiliğinden güncellenir.

ETOPLA nasıl çalışır?

ETOPLA üç bilgi ister: koşulun aranacağı aralık, koşulun kendisi ve toplanacak aralık. Söz dizimi =ETOPLA(aralık; ölçüt; [toplam_aralığı]) biçimindedir. Yedi siparişlik örnek tabloda kategoriler A sütununda, tutarlar C sütununda duruyor.

=ETOPLA(A2:A8;"Elektronik";C2:C8)

=SUMIF(A2:A8,"Elektronik",C2:C8)

Formül A2:A8 aralığında "Elektronik" yazan satırları bulur ve aynı satırlardaki C değerlerini toplar: 4.500 + 3.200 + 2.800 = 10.500. Aralık ile toplam aralığı aynı boyda olmalıdır. A2:A8'e karşılık C2:C7 yazarsanız Excel hata vermez ama son satırı sessizce atlayabilir.

Ölçüt yazmanın üç yolu

Ölçüt; düz bir metin, bir karşılaştırma ya da bir hücre başvurusu olabilir. Metin ölçütlerinde büyük küçük harf ayrımı yapılmaz, "elektronik" yazmanız da aynı sonucu verir. Karşılaştırma işaretleri ise tırnak içinde yazılır.

=ETOPLA(C2:C8;">=1000")

=SUMIF(C2:C8,">=1000")

Bu örnekte toplam aralığı verilmediği için ETOPLA koşulu kontrol ettiği C sütununu toplar. 1.000 TL ve üzerindeki dört sipariş (4.500, 1.200, 3.200 ve 2.800) toplamda 11.700 eder.

Ölçütü bir hücreden okumak, formülü yeniden yazmadan farklı sorular sormanızı sağlar. F1 hücresine 1000 yazdıysanız ölçüt ">="&F1 olur; işaret tırnak içinde kalır, & ile hücre değeri eklenir. Jokerler de işe yarar: "Elek*" Elek ile başlayan her metni, "?????" tam beş karakterlik metinleri yakalar.

EĞERSAY ile saymak

EĞERSAY, koşulu sağlayan hücre sayısını verir ve yalnız iki argüman alır: aralık ve ölçüt. Toplanacak bir sütun olmadığı için yapısı ETOPLA'dan daha sadedir.

=EĞERSAY(A2:A8;"Giyim")

=COUNTIF(A2:A8,"Giyim")

Giyim kategorisinde iki sipariş olduğu için sonuç 2'dir. Aynı mantıkla =EĞERSAY(B2:B8;"İzmir") İzmir siparişlerini, =EĞERSAY(C2:C8;"<500") 500 TL altındaki küçük siparişleri sayar. İkisini birleştirip ortalama sipariş tutarını da bulabilirsiniz: ETOPLA sonucunu EĞERSAY sonucuna bölmek EĞERORTALAMA ile aynı değeri verir.

Sık karşılaşılan hatalar

Formül sıfır döndürüyorsa ilk şüpheli, metindeki görünmez boşluklardır. "Elektronik " gibi sonunda boşluk olan bir hücre ölçütle eşleşmez. Bu durumda verinizi KIRP ile temizlemek ya da ölçüte "Elektronik*" yazmak işe yarar.

İkinci tuzak sayı gibi görünen metinlerdir. Başka bir sistemden kopyalanan tutarlar sola yaslanmış duruyorsa Excel onları metin sayar ve ETOPLA toplamaz. Hücreleri seçip sayıya dönüştürdüğünüzde sonuç düzelir.

Üçüncüsü, formülü aşağı kopyalarken aralıkların kaymasıdır. Bir özet tablosunda her kategori için ayrı satır kuruyorsanız aralıkları $A$2:$A$8 biçiminde sabitleyin. Ölçüt hücresi ise göreli kalmalı ki her satır kendi kategorisini okusun.

Bir özet tablosu kurmak

Gerçek işte bu iki formül genellikle küçük bir özet tablosunda birlikte kullanılır. E sütununa kategori adlarını alt alta yazar, F sütununa toplamı, G sütununa sipariş sayısını hesaplatırsınız. Kategori listesi değişmediği sürece yeni sipariş eklediğinizde özet de güncel kalır.

Bu düzenin bir sınırı vardır: tek koşulla çalışır. "Ankara'daki Elektronik satışları" gibi iki şartlı bir soru için bir sonraki dersteki ÇOKETOPLA ve ÇOKEĞERSAY gerekir.

Gerçek bir senaryo: aylık kampanya raporu

Bir e-ticaret mağazasında ay sonunda "kampanyalı ürünlerden ne kadar ciro geldi ve kaç sipariş alındı?" sorusu sorulduğunu düşünün. Sipariş dökümünde bir Kampanya sütunu varsa ve kampanyalı satırlarda "Evet" yazıyorsa iki formül cevabı verir: =ETOPLA(D2:D500;"Evet";E2:E500) ciroyu, =EĞERSAY(D2:D500;"Evet") sipariş sayısını bulur.

Aynı raporda iade edilen siparişleri ayırmak isteyebilirsiniz. Durum sütununda "İade" yazan satırları =EĞERSAY(F2:F500;"İade") ile sayıp toplam sipariş sayısına bölerseniz iade oranını elde edersiniz. 480 siparişin 24'ü iade edildiyse oran 24 / 480 = %5 olur.

Rapor her ay aynı biçimde hazırlanıyorsa ölçütleri formüle gömmek yerine raporun üst kısmındaki hücrelere yazın. Böylece "Evet" yerine bir kampanya adı yazarak aynı tabloyu farklı kampanyalar için kullanabilirsiniz.

Sıradaki adım

Alıştırmada aynı sipariş tablosu için kategori bazında toplam satışı hesaplayacaksınız. F2'ye yazacağınız formülü F4'e kadar kopyalayın ve hangi aralıkların $ ile sabitlenmesi gerektiğini sonuçlara bakarak kontrol edin.