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

  2. Sayın Hocam günlerdir ugrastigim bir konuda yardımınıza ihtiyacim var. Excelde bir tabloda indis ile 2 kosulu verilerde ayni degerlere sahip hucrelerin toplamini almak istiyorum. Bunu coketopla ile yapabiliyorum fakat indis ile mumkunmudur bu tesekkur ederim

  3. merhaba hocam , a sütünunda alt alta 744 adet 00:00 dan 23:00 şekilde genel olarak biçimlendirilmiş hücre var T1 zamanı 06:00 ile 17:00 arasındaki saatlerin sağındaki hücrelerde tüketim değerleri var bu değerlerin toplamını başka bir hücreye toplamasını yaptırmak istiyorum yardımcı olursanız sevinirim

    • merhaba,
      =ÇOKETOPLA(C2:C5;B2:B5;”>=06:00″;B2:B5;”<=17:00") gibi bir formülle yapabilirsiniz.

  4. Merhaba,

    Aşağıda ki tarihleri ay olarak toplamak istiyorum. Mesela Haziran 2020 ayında kaç aracın trafik sigorta poliçesinin biteceğini görmek istiyorum. Bunu nasıl yapabilirim?
    Teşekkür ederim.

    düzenleme: tablo çok uzun olduğundan silinmiştir.

    • Merhaba,

      Tarih aralığına göre verileri saymak için aşağıdaki formülü kullanabilirsiniz.

      =ÇOKEĞERSAY($C$4:$C$40;”>=” & TARİHSAYISI(“01/06/2020″);$C$4:$C$40;”<=" & TARİHSAYISI("30/06/2020"))

  5. merhaba bir çalışma sayfamda 4 sütun 13 bin satır veri var a sütunundaki verilerin altına a sütunun içinde olan 3 bin tane veri daha ekledik bu verileri koşullu biçimlendirme yapıp benzer olanları bulabiliyorum ama benim ihtiyacım olan aynı olanların yanına b c d sütunundaki verilerin de gelmesi nasıl yapabilirim yardımcı olursanız çok sevinirim

    • Merhaba,

      Bu sonradan eklediğiniz veri yeni veri mi, yoksa sadece A sütununda olan verilerin bir listesi mi? Yani siz ana listenizin A sütununda yer alan verilerin benzersiz olanlarını alıp bunu alta mı eklediniz? eğer öyle ise bu sorunun cevabı bir yorumda verilemeyecek kadar uzun olacaktır. İletişim formundan iletişime geçerseniz daha iyi olur.

      Saygılarımla,

      • 13 bin satır 7 haneli sicil numalarından oluşan arşiv listemizde çalışma yapıp 3 binini imhaya ayırdık b c ve d sütununda bu dosyalara ait diğer bilgiler var onlarında 3 bin sicilin yanına getirilmesi gerekiyor sorunum tam olarak bu umarım açıklayabilmişimdir.

        • DÜŞEYARA formülünü kullanmayı denediniz mi? Sicil numaraları A sütununda diyelim
          =DÜŞEYARA(A2;AnaTabloAaralığı;2)
          gibi. Eğer işe yaramaz ise Power Query ile de yapılabilir ancak örnek dosya üzerinde çalışmak daha doğru olur.

  6. merhaba,
    bir konuyla ilgili yardımınızı rica ederim.
    diyelim ki aşağıdaki gibi bir tablo var ve ben aylara göre toplam almak istiyorum. formülü ne yazmalıyım.

    TARİH FİRMA TUTAR
    13.03.2020 A FİRMASI 14.096,98
    13.03.2020 C FİRMASI 833,38
    13.04.2020 D FİRMASI 620,72
    18.03.2020 G FİRMASI 2.519,93
    13.04.2020 B FİRMASI 622,57
    13.05.2020 H FİRMASI 9.473,60
    13.07.2020 B FİRMASI 2.237,15
    14.03.2020 F FİRMASI 1.894,76
    14.02.2020 C FİRMASI 6.576,05

  7. Merhabalar çok bilgilendirici yazılar elinize sağlık. Benim bir sorum var. Bu formulde ”>= veya >=” ölçütleri kullanırlan tarih aralığını hücreden seçememiz mümün mü?

    Örneğin sütunlarda şirket adı, ödeme tarihi, ödeme miktarları yazıyor. Başka hücrelere 2 farklı tarih girerek bu hücrelerdeki tarih aralığında toplam yapmak.

    Bunu denediğimde ölçütlerde tarihi el ile girersem kabul ediyor fakat ”>=G7” gibi bir ölçütü hücreden almasını isteyince kabul etmiyor. Bunu nasıl çözebiliriz?

    Şimdiden teşekkürler

    • Merhaba,
      Evet bu mümkündür. Zaten en güzel yöntem de bu tarz karşılaştırmaları hücre değeri ile yapmaktır.
      Saygılarımla,

  8. Merhabalar Hocam;
    Bir sorunum var ve tam olarak çözemedim yardımcı olursanız çok mutlu olurum.
    a sütunu b sütunu
    2 x
    2 y
    şeklinde olan hücreleri tek hücrede 2,x,y şekline getirmek istiyorum.
    Bunu nasıl yapabilirim yardımcı olabilir misiniz, şimdiden teşekkürler

  9. Merhabalar Hocam;
    Aynı sütunda 30k satır var ve bazı hücreler tekrarlanıyor. Benim yapmaya çalıştığım ise tekrarlanan hücrelerin kalması fakat tekrarlanmayan hücrelerin silinmesi ve aynı zamanda diğer sütunların da silinen satırlara göre hücrelerinin silinmesi.
    Bunun çözümü nedir? Teşekkürlerimi şimdiden iletirim 🙂
    Saygılarımla

Leave a Reply