Örnek çalışma kitabını indirin
Bu eğitici, Excel ve Google E-Tablolar'da belirli aylara karşılık gelen verileri toplamak için SUMIFS İşlevinin nasıl kullanılacağını gösterecektir.
Aya göre ise toplamı
İlk olarak, belirli bir ay ve yıla denk gelen tarihlere karşılık gelen verilerin nasıl toplandığını göstereceğiz.
DATE, YIL, AY ve EOMONTH İşlevleriyle birlikte SUMIFS İşlevini kullanabiliriz. Satış Sayısı her biri içinde Ay.
| 1 | =TOPLA(C3:C9,B3:B9,">="&TARİH(YIL(E3),AY(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Yukarıdaki formülü oluşturmak için her ayın tarih aralığını tanımlayarak başlıyoruz.. Ayın ilk gününü tanımlamak için DATE İşlevini kullanırız (ör. 5/1/2021). Bunu, tarihi formüle "sabit kodlayarak" yapabiliriz:
| 1 | ">="&TARİH(2021,5,1) |
Ya da daha iyisi, yukarıdaki örnekte gösterildiği gibi, E3 hücresindeki bir tarihe başvurarak, tarihin yılını ve ayını çıkararak (ve günü 1'e eşitleyerek) ölçütleri esnek hale getirebiliriz:
| 1 | ">="&TARİH(YIL(E3),AY(E3),1) |
Ayın son gününü tanımlamak için EOMONTH İşlevini kullanabiliriz:
| 1 | "<="&EOMONTH(E3,0) |
Tüm bu kriterleri bir araya getirerek aşağıdaki SUMIFS formülünü yazabiliriz:
| 1 | =TOPLA(C3:C9,B3:B9,">="&TARİH(YIL(E3),AY(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Hücre Referanslarını Kilitleme
Formüllerimizin okunmasını kolaylaştırmak için formülleri kilitli hücre referansları olmadan gösterdik:
| 1 | =TOPLA(C3:C9,B3:B9,">="&TARİH(YIL(E3),AY(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Ancak bu formüller, dosyanızın başka bir yerine kopyalayıp yapıştırdığınızda düzgün çalışmayacaktır. Bunun yerine, aşağıdaki gibi kilitli hücre referansları kullanmalısınız:
| 1 | =TOPLA($C$3:$C$9,$B$3:$B$9,">="&TARİH(YIL(E3),AY(E3),1),$B$3:$B$9,"<=" &EOMONTH(E3,0)) |
Daha fazla bilgi için Hücre Referanslarını Kilitleme hakkındaki makalemizi okuyun.
Ay Değerlerini Biçimlendirme
Bu örnekte, E sütununda ayları listeledik. Bu ay değerleri aslında özel sayı biçimlendirmesi kullanılarak günü atlamak üzere biçimlendirilmiş tarihlerdir.
Özel veri biçimi, Mayıs 2022'yi göstermek için "aa yyyy" şeklindedir.
Birden Fazla Yıl Boyunca Ay Bazında Toplam
Yukarıdaki örnek, belirli bir ay ve yıla denk gelen tarihlerle verileri özetledi. Bunun yerine, SUMPRODUCT İşlevini kullanarak herhangi bir yılda bir aya denk gelen tarihlerle verileri toplayabilirsiniz.
| 1 | =TOPLA(C3:C8,--(AY(B3:B8)=AY(G3))) |
Bu örnekte, karmaşık “eğer toplamı” hesaplamaları yapmak için SUMPRODUCT İşlevini kullanıyoruz. Formül üzerinden gidelim.
Bu bizim son formülümüz:
| 1 | =TOPLA(C3:C8,--(AY(B3:B8)=AY(G3))) |
İlk olarak, SUMPRODUCT İşlevi şunları listeler: Satış Sayısı her biri için Satış Tarihi ve sonra her birinin ayını karşılaştırır Satış Tarihi belirtilene karşı Ay, aylar eşleşiyorsa DOĞRU, uymuyorsa YANLIŞ döndürür:
| 1 | =TOPLAMÇARPIM({30;42;51;28;17;34},--({DOĞRU;YANLIŞ;DOĞRU;YANLIŞ;DOĞRU;YANLIŞ})) |
Ardından çift tire (-) DOĞRU ve YANLIŞ değerlerini 1'lere ve 0'lara dönüştürür:
| 1 | =TOPLAÇARI({30;42;51;28;17;34},{1;0;1;0;1;0}) |
SUMPRODUCT İşlevi daha sonra dizilerdeki her bir giriş çiftini çarpar:
| 1 | =TOPLAÇA({30;0;51;0;17;0}) |
Son olarak, dizideki sayılar toplanır:
| 1 | =98 |
Bir SUMPRODUCT İşlevinde Boolean deyimlerini ve “-” komutunu kullanma hakkında daha fazla ayrıntı burada bulunabilir
Google E-Tablolar'da Aya Göre Topla
Bu formüller, Google E-Tablolar'da Excel'dekiyle tamamen aynı şekilde çalışır.
