Çoğu zaman, bazı değerleri aya göre hesaplamak isteriz. Mesela, belirli bir ayda ne kadar satış yapıldı. Bu, pivot tablolar kullanılarak kolayca yapılabilir, ancak dinamik bir rapora sahip olmaya çalışıyorsanız, aya göre toplamak için SUMPRODUCT veya SUMIFS formülünü kullanabiliriz.
SUMPRODUCT çözümü ile başlayalım.
Excel'de aya göre toplamı almak için genel formül
=TOPLA(toplam_aralık,--( METİN(tarih_aralığı,"MMM")=ay_metin))
Toplam aralığı : Ay bazında toplamak istediğiniz aralıktır.
Tarih aralığı : Aylarca bakacağınız tarih aralığıdır.
ay_metni: Değerlerini toplamak istediğiniz metin formatındaki aydır.
Şimdi bir örnek görelim:
Örnek: Excel'de Aya Göre Toplam Değerler
Burada tarihlerle ilişkili bazı değerlerimiz var. Bu tarihler 2019 yılının Ocak, Şubat ve Mart ayıdır.
Yukarıdaki resimde görebileceğiniz gibi, tüm tarihler 2019 yılına aittir. Şimdi sadece E2:G2'deki değerleri E1:G1'deki aylara göre toplamamız gerekiyor.
Şimdi değerleri aylara göre toplamak için bu formülü E2'ye yazın:
=TOPLA(B2:B9,--(METİN(A2:A9,"MMM")=E1)))
Bitişik hücrelere kopyalamak istiyorsanız, görüntüdeki gibi mutlak referanslar veya adlandırılmış aralıklar kullanın.
Bu bize her ayın tam toplamını verir.
Nasıl çalışır?
İçeriden başlayarak, duruma bakalım METİN(A2:A9,"MMM") Bölüm. Burada METİN işlevi, A2:A9 aralığındaki her tarihten ayı metin biçiminde bir diziye çıkarır. Formüle =SUMPRODUCT(B2:B9,--({"Oca";"Oca";"Şubat";"Oca";"Şubat";"Mart";"Oca";"Şubat"}=E1) şeklinde çevriliyor )
Ardından, METİN(A2:A9;"MMM")=E1: Burada dizideki her ay E1'deki metinle karşılaştırılır. E1, “Oca” içerdiğinden, dizideki her “Oca” DOĞRU'ya ve diğeri YANLIŞ'a dönüştürülür. Bu, formülü =TOPLA($B$2:$B$9,--{DOĞRU;DOĞRU;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ}) şeklinde çevirir.
Sonraki --(METİN(A2:A9,"MMM")=E1), DOĞRU YANLIŞ'ı 1 ve 0 ikili değerlerine dönüştürür. Formül =SUMÜRÜN($B$2:$B$9,{1;1;0; 1;0;0;1;0}).
Nihayet SUMPRODUCT($B$2:$B$9,{1;1;0;1;0;0;1;0}): SUMPRODUCT işlevi, $B$2:$B$9 içindeki karşılık gelen değerleri {1;1; 0;1;0;0;1;0} ve onları toplar. Dolayısıyla, E1'de 20052 değerine göre toplamı elde ederiz.
Farklı Yıldan Aylar EĞER TOPLA
Yukarıdaki örnekte, tüm tarihler aynı yıla aittir. Ya farklı yıllardan olsalardı? Yukarıdaki formül, yıldan bağımsız olarak değerleri aya göre toplayacaktır. Örneğin, yukarıdaki formülü kullanırsak Ocak 2018 ve Ocak 2019 eklenecektir. Çoğu durumda yanlış olan.
Bu, yukarıdaki örnekte yıl için herhangi bir kriterimiz olmadığı için olacaktır. Yıl kriterlerini de eklersek işe yarayacaktır.
Excel'de aya ve yıla göre toplam almak için genel formül
=SUMÜRÜN(toplam_aralık,--( METİN(tarih_aralığı,"MMM")=ay_metin),--( METİN(tarih_aralığı,"yyyy")=METİN(yıl,0)))
Burada yılı kontrol eden bir kriter daha ekledik. Diğer her şey aynı.
Yukarıdaki örneği çözelim, 2017 yılının Ocak ayı toplamını almak için bu formülü E1 hücresine yazalım.
=TOPLA(B2:B9,--(METİN(A2:A9,"MMM")=E1),--(METİN(A2:A9,"yyyy")=METİN(D2,0)))
Aşağıdaki hücrelere kopyalamadan önce adlandırılmış aralıkları veya mutlak referansları kullanın. Resimde, bitişik hücrelerde kopyalamak için adlandırılmış aralıklar kullandım.
Artık değer toplamını aylara ve yıllara göre de görebiliriz.
O nasıl çalışır?
Formülün ilk kısmı önceki örnekle aynıdır. Yıl kriterlerini ekleyen ek kısmı anlayalım.
--(METİN(A2:A9,"yyyy")=METİN(D2,0)): METİN(A2:A9,"yyyy"), A2:A9'daki tarihi metin biçiminde yıllar olarak bir diziye dönüştürür. {"2018";"2019";"2017";"2017";"2019";"2017";"2019";"2017"}.
Çoğu zaman yıl sayı biçiminde yazılır. Bir sayıyı biz metinle karşılaştırmak için TEXT(D2,0) kullanarak yıl int metnini dönüştürdüm. Daha sonra bu metin yılını TEXT(A2:A9,"yyyy")=TEXT(D2,0) olarak yıl dizisiyle karşılaştırdık. Bu, bir doğru-yanlış {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE} dizisi döndürür. Daha sonra -- operatörünü kullanarak true false'u sayıya dönüştürdük. Bu bize {0;0;1;1;0;1;0;1} değerini verir.
Sonuç olarak formül =TOPLAÇARI(B2:B9,{1;1;0;1;0;0;1;0},{0;0;1;1;0;1;0;1 şeklinde çevrilecektir. }). İlk dizinin değerler olduğu yer. Sonraki eşleşen ay ve üçüncü yıl. Sonunda değerlerimizin toplamını 2160 olarak elde ederiz.
Aya göre toplamak için SUMIFS işlevini kullanma
Genel Formül
=TOPLA(sum_range,date_range,”>=” & startdate, date_range,”<=” & EOMONTH(start_date,0))
Buraya,Toplam aralığı : Ay bazında toplamak istediğiniz aralıktır.
Tarih aralığı : Aylarca bakacağınız tarih aralığıdır.
Başlangıç tarihi : Toplamını yapmak istediğiniz başlangıç tarihidir. Bu örnek için, verilen ayın 1'i olacaktır.
Örnek: Excel'de Aya Göre Toplam Değerler
Burada tarihlerle ilişkili bazı değerlerimiz var. Bu tarihler 2019 yılının Ocak, Şubat ve Mart ayıdır.
Sadece bu değerleri o aya kadar toplamamız gerekiyor. Artık aylarımız ve yıllarımız ayrı olsaydı kolaydı. Ama değiller. Burada herhangi bir yardım sütunu kullanamayız.
Bu yüzden rapor hazırlamak için ay ve değerlerin toplamını içeren bir rapor formatı hazırladım. Ay sütununda aslında ayın başlangıç tarihi var. Sadece ayı görmek için başlangıç tarihini seçin ve CTRL+1'e basın.
Özel formatta “mmm” yazın.
Artık verilerimiz hazır. Değerleri aya göre toplayalım.
Aya göre toplamak için bu formülü E3'e yazın.
=TOPLA(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMONTH(D3,0))
Formülü kopyalamadan önce mutlak referanslar veya adlandırılmış aralıklar kullanın.
Yani sonunda sonucu aldık.
Peki, Nasıl Çalışır?
SUMIFS işlevinin birden çok kriterdeki değerleri toplayabileceğini bildiğimiz gibi.
Yukarıdaki örnekte, ilk ölçüt, B3:B10'daki tüm değerlerin toplamıdır; burada, A3:A10'daki tarih, içindeki tarihten büyük veya ona eşittir. D3. D3, 1 Ocak'ı içerir. Bu da tercüme ediyor.
=TOPLA(B3:B10,A3:A10,">="& “1 Ocak-2019” ,A3:A10,"<="EOMONTH(D3,0))
Sonraki Ölçüt, yalnızca Tarih içindeyse toplamıdır A3:A10 EOMONTH(D3,0)'dan küçük veya eşittir. EOMONTH İşlevi yalnızca sağlanan ayın son tarihinin seri numarasını döndürür. Sonunda formül de çevirir.
=TOPLA(B3:B10,A3:A10,">=1-ocak-2019” ,A3:A10,"<=31-ocak-2019”)
Bu nedenle, toplamı excel'de aya göre alıyoruz.
Bu yöntemin faydası, değerleri toplamak için başlangıç tarihini ayarlayabilmenizdir.
Tarihlerinizin yılları farklıysa, pivot tabloları kullanmak en iyisidir. Pivot tablolar, verileri yıllık, üç aylık ve aylık biçimde kolayca ayırmanıza yardımcı olabilir.
Evet arkadaşlar, değerleri aya göre bu şekilde toplayabilirsiniz. Her iki yolun da kendine has özellikleri vardır. Hangi yolu beğeneceğinizi seçin.
Bu makale veya excel ve VBA ile ilgili diğer sorularınızla ilgili herhangi bir sorunuz varsa, yorumlar bölümü sizin için açıktır.
İlgili Makaleler:
Excel'de SUMIF İşlevi Nasıl Kullanılır
Excel'de tarih içeren SUMIFS
Boş olmayan hücrelerle SUMIF
Excel'de TOPLA İşlevi Nasıl Kullanılır
VE-VEYA mantığını kullanan SUMIFS
Popüler Makaleler
Verimliliğinizi Arttıracak 50 Excel Kısayolu: Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
nasıl kullanılırExcel'de DÜŞEYARA İşlevi: Bu, farklı aralık ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de COUNTIF işlevi nasıl kullanılır?: Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi nasıl kullanılır?: Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.