Bu yazımda Excel’de bir tabloda yer alan farklı sütunlardaki verilerin içeriğine göre başka bir sütundaki verilerin süzülerek toplanması konusunu anlatacağım.

Öncelikle elimizde aşağıdaki gibi bir tablo olduğunu varsayalım. Bu tablo isteğe göre satış adetleri, ürün türü, fiyatı, toplam satış tutarı gibi farklı bilgileri de barındırabilir ancak ben daha kolay anlaşılabilmesi için basit bir tablo yaptım. Tablo aşağıya doğru kaydırılabiliyor o yüzden lütfen önce tabloyu inceleyin. Kopyalayarak Excel’e yapıştırabilirsiniz. Excel’e kopyalayacaklar için A-B-C sütunlarına kopyalama yapın, bu sayede daha sonra yazılacak formüller düzgün çalışır. Aksi takdirde ilgili hücre adreslerinin düzenlenmesi gerekecektir.

SatışçılarTarihSatış Adedi
Ahmet1 January 201730
Mehmet1 January 201760
Hasan1 January 201725
Mahmut1 January 201740
Muammer1 January 201730
Ahmet2 January 201720
Hasan2 January 201760
Mahmut2 January 201770
Muammer2 January 201780
Mehmet2 January 201750
Ahmet3 January 201790
Mehmet3 January 2017100
Hasan3 January 2017110
Mahmut3 January 2017130
Muammer3 January 201760
Mehmet4 January 201730
Hasan4 January 201740
Mahmut4 January 201770
Muammer4 January 201780
Ahmet4 January 201720
Muammer5 January 201745
Ahmet5 January 201715
Mehmet5 January 201720
Hasan5 January 201730
Mahmut5 January 201725

Şimdi bu tablodan belli bir satıcının belli tarihler arasında kaç adet satış yaptığını bulmamız gereksin. Bunun için kullanabileceğiniz farklı yöntemler mevcut. İlk formüle gelelim tabii bunun için hangi kıstaslara göre arama yaptığımı gösterecek bir tablo daha oluşturmam gerekecek ki formül içinde bu kıstasları kullanalım. (Excel’de E-F-G-H sütunlarına kopyalama yapın)

ÇokETopla Formülü İle

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

ÇokETopla Formülünün Kullandığı Aralıklar
ÇokETopla Formülünün Kullandığı Aralıklar

$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.

ToplaÇarpım Formülü İle

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.

TOPLA.ÇARPIM formülünün referans aldığı aralıklar
TOPLA.ÇARPIM formülünün referans aldığı aralıklar

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.

VSeçTopla Formülü İle

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.

Satıcıİlk TarihSon TarihToplam Satış
=”=Mahmut”=”>=02/01/2017″=”<=04/01/2017"

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.

VERİSEÇTOPLA formülünün referans aldığı aralıklar
VERİSEÇTOPLA formülünün referans aldığı aralıklar

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.

Bunlar da ilginizi çekebilir:
Etiketler: , , , , ,
  1. Sayın hocam sadece bir tek hücredeki değişen sayıların(bir hücrede oluşturulan rastgele sayılar gibi) toplamını bir başka hücrede nasıl yapabiliriz,teşekkürler, ayrıca sitenizde çok güzel düzenlenmiş ve bilgilenmek isteyenler için çok iyi,emeğinize sağlık.

    • Eğer değerler tek bir hücre içinde metin olarak birbirine ekli olarak girildi ise bunu formüller ile ayırmak çok zor, çünkü tam olarak kaç tane değer var bilinmiyor. Önce bu değerleri Veri sekmesindeki Metni Sütunlara dönüştür komutu ile sütunlara çekmek sonrasında üzerlerinde işlem yapmak gerekir. Yada sağdan, soldan, uzunluk formülleri ile gerekli bölme işlemlerini yaptıktan sonra toplam alabilirsiniz. son seçenek ise Power Query

Leave a Reply