Özet
Aylara göre sütunlar halinde toplamak için, ETOPLA işlevini EOMONTH işleviyle birlikte kullanabilirsiniz. Gösterilen örnekte, G5'teki formül şöyledir:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Bu formül üç adlandırılmış aralık kullanır: "miktar" (D5: D15), "müşteri" (B5: B15) ve "tarih" (C5: C15).
Açıklama
SUMIFS işlevi, bir veya daha fazla ölçüt temelinde bir aralıktaki değerleri toplamak için tasarlanmıştır. Bu durumda, üç kritere ihtiyacımız var:
- Müşteri adı = F sütunundaki müşteri
- Tarih> = ayın ilk (4. sıradaki tarihten itibaren)
- Tarih <= ayın sonu (4. sıradaki tarihten itibaren)
Önemli nokta: G4: I4'teki ay adları, özel sayı biçimi "aaa" ile biçimlendirilmiş aslında geçerli tarihlerdir. Bu, G4: I4'teki tarih değerlerini doğrudan yukarıdaki kriter # 2 ve # 3 için kullanmamızı sağlar.
SUMIFS'e döndüğünüzde, ilk aralık / ölçüt çifti, istemci adını kontrol etmek için kullanılır:
client,$F5
Dikkat F5, sütun kilitliyken karışık bir referanstır. Bu, formül tablo üzerinden kopyalandıkça satırın değişmesine izin verir, ancak müşteri adı her zaman F sütunundan gelir.
İkinci aralık / ölçüt çifti, tarihleri ayın ilkine göre kontrol etmek için kullanılır:
date,">="&G$4
Yukarıda bahsedildiği gibi, G4: I4'teki değerler aslında tarihlerdir: 1 Ocak 2019, 1 Şubat 2019 ve 1 Mart 2019. Bu nedenle, bu kriter yalnızca 4. sıradaki ayın ilk tarihlerinden daha büyük tarihleri kontrol eder. bu başka bir karışık referanstır, bu sefer satır kilitliyken. Bu, formül tablo boyunca kopyalandıkça sütunun değişmesine izin verir, ancak satır numarasını sabit tutar. Mantıksal bir işleç ve başka bir hücreden bir değer kullanan ölçütler oluştururken bir ve işareti (&) ile birleştirme gereklidir.
Üçüncü aralık / ölçüt çifti, tarihleri ayın son gününe göre kontrol etmek için kullanılır:
date,"<="&EOMONTH(G$4,0)
Her ayın son gününü elde etmek için, 4. satırdaki sütun başlığındaki tarihte EOMONTH işlevini kullanırız. Aylar bağımsız değişkeni için sıfır sağlandığında, EOMONTH aynı ayın son gününü döndürür. Yine, satırın değişmesini önlemek için G4 referansı karıştırılır.
Pivot Tablo çözümü
Herhangi bir formül olmadan otomatik olarak aylara göre gruplandırılabildiğinden, bir pivot tablo bu sorun için mükemmel bir çözüm olabilir. Formüllerle pivot tabloların yan yana karşılaştırması için şu videoyu izleyin: Neden pivot tablolar.
