Tablodan hangi satıcının hangi tarihler arası ne kadar toplam satış yaptığını hesaplamak istiyorum. “Toplam Satış” sütununa yazacağım ilk formül “çoketopla” formülü. İngilizce olarak “countifs” kullanabilirsiniz. Ne demek bu formül, kısaca seçilen sütundaki verilerin, farklı sütunlarda tanımlayacağımız kıstaslara uygun olanlarını toplamakta kullanılır. Baktığınızda “etopla” (ingilizce sumif) formülü ile benzer bir mantığı olsa da farklı olarak birden fazla kıstas tanımlamamıza izin verir. Bu kadar bilgiden sonra formülümüzü yazalım. (Eğer yukarıdaki kopyalamaları belirtildiği gibi yaptıysanız H2 hücresine aşağıdaki formülü yazın)

=ÇOKETOPLA($C$2:$C$26;$A$2:$A$26;”=”&$E2;$C2:$C$26;”>=”&$F2;$C2:$C26;”<=”&$G2)

Bu formülü inceleyelim isterseniz. Aşağıda formülün bağlı olduğu hücrelerin görüntüsü mevcut

$C$2:$C$26 : toplanacak aralığı ifade eder. Satış Adedini toplamak istediğimiz için tabloda satış adedinin yer aldığı sütunu buraya giriyoruz.

$A$2:$A$26 : ilk kıstas aralığını ifade eder. İlk kıstas olarak satıcı adını alacağımızdan buraya tablonun A sütununu giriyoruz.

“=”&$E2 : Burada ilk kıstasımızı yazıyoruz. Eğer anlatılan şekilde kopyalama işlemleri yaptıysanız E2 hücresinde “Mahmut” değerinin yer alacaktır. “=”& ifadesi teknik olarak iki metni birleştirmekte kullanılır. Excel burada E2 hücresinde yer alan “Mahmut” ifadesi ile “=” ifadesini birleştirerek “=Mahmut” sonucunu elde eder. Bunun sonucunda tabloda “B2:B26” arasında içinde “Mahmut” bulunan satırları bulur. Eşittir haricinde burada kullanılabilecek farklı ifadeler var, programlamaya aşina olanlar bilir ancak bilmeyenler için kullanılabilecek tüm ifadeleri aşağıda veriyorum.

“=” : Eşittir
“<” : Küçüktür “>” : Büyüktür
“<=” : Küçük Eşit “>=” : Büyük Eşit
“<>” : Eşit Değil

Formülün bundan sonrası farklı kıstas sütunları ve bu sütunlar için farklı kıstaslar tanımlamaktan ibaret. Unutmayın önce sütun sonra o sütun için kıstası giriyoruz. Sırası ile F2 ve G2 hücrelerinde başlangıç ve bitiş tarihlerinin yer aldığını düşünürsek Excel yazdığımız formülü aşağıdaki şekilde yorumlayacaktır.

=ÇOKETOPLA($B$2:$B$26;$A$2:$A$26;”=Mahmut”;$C2:$C$26;”>=02/01/2017″;$C2:$C26;”<=04/02/2017″)

Buradaki formülü bu şekilde de yazabiliriz ancak bu şekilde yazılır ise formül çoğaltılırken metin değerleri değişmeyeceğinden her seferinde aynı kıstaslara göre arama yapılacaktır. Bu özellikle birden fazla satır içeren ve özet bilgi göstermek istediğimiz tablolarda sıkıntıya yol açacak ve elle müdahale gerektirecektir.

Bu formülde gördüğünüz gibi tablonun C2:C26 aralığı için iki farklı tarih kıstası girebiliyoruz. Bu durumda Excel bu aralıkta tarih değer 02/01/2017’den büyük ve eşit, 04/02/2017 tarihinden küçük ve eşit olan tarihleri arayacaktır.

Gelelim bu amaç için kullanılacak ikinci formüle. Bu formül Excel’in TOPLA.ÇARPIM özelliğini kullanıyor. Bu özellik kısaca seçilen farklı aralıklardaki verileri çarparak toplamak için kullanılan bir formül. Gelelim formülümüze:

=TOPLA.ÇARPIM($B$2:$B$26*($A$2:$A$26=$E2)*($C$2:$C$26>=$F2)*($C$2:$C$26<=$G2))

Bu formülün referans aldığı alanlar da aşağıda gösterilmiştir.

Bu formül ne yapıyor derseniz eğer, burada gördüğünüz gibi sıralı bir çarpma işlemi söz konusu. Öncelikle Satış adetlerinin yer aldığı B2:B26 aralığındaki verileri alıyoruz. Bunu bir dizin olarak gösterirsek (30, 60, 25, 40, 30, 20,…) sayılar dizini anlamına geliyor.

İkinci kısımda ise A2:A26 aralığını ele alıyor ve bu aralıkta E2 hücresinin sahip olduğu değere eşit olan değerleri kontrol ediyor. E2 hücresinde “Mahmut” değeri var idi. Bu durumda Excel A2:A26 aralığındaki her bir hücre değerini “Mahmut” ile kıyaslıyor ve sonucu “doğru/yanlış” veya “0/1” olarak bir dizine atıyor. Yani A2:A26 dizinindeki değerler (Ahmet, Mehmet, Hasan, Mahmut, Muammer, Ahmet,…) şekliden (Ahmet=?Mahmut, Mehmet=?Mahmut, Hasan=?Mahmut, Mahmut=?Mahmut,…) şekline buradan da sonuca göre (0,0,0,1,0,0,…) şeklinde bir dizine dönüştürülüyor.

Sonraki kısımlarda aynı kıyaslama tarih için yapılarak onlar için de bir dizin oluşturuluyor. Sonra bu üç dizinin ilk elemandan başlanarak her bir elemanı çarpılarak sonuçları toplanıyor.

Veri çekip toplama için göstereceğim en son yöntem Excel’in veri formüllerini içeriyor. Bunun için yapmamız gereken tabloda veri toplamada kullanılacak kıstasları tablo başlığı ve kıstas olacak şekilde alt alta yeni bir tablo şeklinde yazmamız gerekiyor. Aşağıdaki tablodaki değerleri Excel’de E5:H6 sütunlarına kopyalayın. Her formülün önünde yer alan “=” işareti önemlidir, Excel bunu göstermez ancak formülün çalışması için bu işaret kesinlikle atlanmamalıdır.

Burada kullanacağımız formül de:

=VSEÇTOPLA($A$1:$C$26;$B$1;E5:G6)

Formülün referans aldığı aralıklar aşağıdaki resimde gösteriyorum.

Gördüğünüz gibi formül iki aralıktan referans almakta. Bu aralıklardan ilki “$A$1:$C$26” ana tablonun bütününü kapsar ve bir veri tabanı olarak bu tablonun kullanılmasını sağlar. Yani bu formülde kullanılacak her bir verinin hangi başlık altında hangi sütunlarda yer aldığı bilgisini bu veri tabanından almasını sağlanır. “$B$1” kısmı veri tabanındaki hangi sütunda toplama işlemi yapılacağını gösterir. “E5:G6” ise ana tablonun hangi başlığında ne tür bir kıstas kullanılacağını ifade eder. Bu tabloyu incelediğimizde ana tablodaki tüm başlıkların burada da yer aldığını görüyoruz. Eğer bir sütunda birden fazla kıstas kullanmak istiyorsak bu tabloda bu başlığı birden fazla kullanabiliriz. Tek dikkat edeceğimiz husus her iki tabloda da yer alan başlıkların bire bir aynı olmak zorunda olmasıdır. Eğer farklı yazımlar kullanıldı ya da boşluk gibi görünmeyen karakterler diğerinden farklı ise Excel hatalı sonuç verir.

Bu açıklamadan sonra kıstas tablosunu incelediğimizde satıcı sütununda “=Mahmut”, tarih sütununda ise “>=02/01/2017” ve diğer tarih sütununda “<=04/01/2017” tarihleri yer alıyor. Excel ana tablonun ilgili sütunlarını inceler ve aynı sütun başlıklarında yer alan kıstaslara uyan satırları bulur ve bu satırların Satış sütunuyla kesiştiği hücrelerde yer alan verileri toplar.

Sonuç önceki formüllerle aynıdır ancak bu formülün kullanılmasında bazı sorunlar var. Öncelikle birden fazla kişi için satış adedi bulunacak ise kıstasların yer aldığı tablo her bir kişi için ayrı ayrı aynı şekilde hazırlanmalı ve formülde bu yeni aralık kullanılmalıdır. Bu da formül çoğaltmanın bu formülde kullanılmayacağı anlamına gelir. Tabloda yer alan her bir kullanıcı için aynı tablonun el ile hazırlanması gerektiğinden zaman kaybına yol açar. Ve olası yazım hatalarının düzeltilmesi özellikle çok fazla başlık içeren tablolarda teker teker kontrol etmek zaman kaybını arttırır. Ve en kötüsü de yeni kişiler tabloya eklendiğinde tekrar bir el işçiliğine ihtiyaç duymasıdır. Bu nedenle bu formülün kullanılmasını şahsen tavsiye etmiyorum.

Evet, bu son formül ile bu konu tamamlanmış oldu. Umarım yardımcı olmuştur. Sorularınız için lütfen irtibata geçin.