Örnek çalışma kitabını indirin
Bu eğitici, Excel ve Google E-Tablolar'daki SUMIFS İşlevini kullanarak alt toplamların gruplara göre nasıl hesaplanacağını gösterecektir.
Kategori veya Gruba Göre Ara Toplam Tablosu
İlk olarak, Excel 365'ten itibaren veya Google E-Tablolar'da bir veri aralığından dinamik bir ara toplam özet tablosunun nasıl oluşturulacağını göstereceğiz.
Otomatik olarak alt toplamları toplamak için BENZERSİZ İşlevi ve SUMIFS İşlevini kullanırız. Ürün Sayısı tarafından Ürün grubu:
| 1 | =TOPLA(C3:C11;B3:B11;E3) |
Bu ara toplam tablosunu oluşturmak için, toplamları toplamak için SUMIFS İşlevinin standart uygulamasını kullanırız. Ürün Sayısı her biriyle eşleşen Ürün grubu. Ancak, bu mümkün olmadan önce benzersiz bir liste oluşturmamız gerekiyor. Ürün Grupları. Microsoft Excel 365 ve Google E-Tablolar kullanıcıları, bir hücre aralığından benzersiz değerlerin dinamik bir listesini oluşturmak için BENZERSİZ İşleve erişebilir. Bu örnekte, E3 hücresine aşağıdaki formülü ekliyoruz:
| 1 | =EŞSİZ(B3:B11) |
Bu formül girildiğinde, hücrenin altında bulunan tüm benzersiz değerleri göstermek için otomatik olarak bir liste oluşturulur. Ürün grubu veri aralığı. Bu örnekte, liste 3 benzersiz öğenin tümünü göstermek için E3:E5'i kapsayacak şekilde genişletildi. Ürün grubu değerler.
Bu, sonuç listesinin boyutunun tanımlanmasının gerekmediği dinamik bir dizi işlevidir ve giriş verileri değerleri değiştikçe otomatik olarak küçülür ve büyür.
Excel 365'te UNIQUE İşlevinin büyük/küçük harfe duyarlı olmadığını, ancak Google E-Tablolar'da öyle olduğunu unutmayın. {“A”; "a"; "B"; "C"}. EŞSİZ İşlev çıktısı programa bağlıdır:
- {"A"; "B"; Excel 365'te “c”}
- {"A"; "a"; "B"; Google E-Tablolar'da "c"}
Excel 365'ten önceki bir Excel sürümünü kullanıyorsanız, farklı bir yaklaşım izlemeniz gerekecektir. Bu bir sonraki bölümde ele alınmıştır.
Kategori veya Gruba Göre Ara Toplam Tablosu - Excel Öncesi 365
Excel'in Excel 365'ten önceki bir sürümünü kullanıyorsanız, BENZERSİZ İşlev kullanılamaz. Aynı davranışı çoğaltmak için, bir dizi hücreden benzersiz değerlerin bir listesini oluşturmak üzere bir dizi formülü oluşturmak üzere İNDEKS İşlevi ve KAÇINCI İşlevini bir EĞERSAY İşleviyle birleştirebilirsiniz:
| 1 | {=INDEX($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Bu formülün çalışması için, E2'den başlayarak formülü içeren hücrenin üstündeki hücreye kadar olan aralık olan $E$2:E2 aralığına başvuran EĞERSAY İşlevi ile sabit hücre referanslarının dikkatli bir şekilde yazılması gerekir.
Formül ayrıca yazıldıktan sonra CTRL + SHIFT + ENTER tuşlarına basarak dizi formülü olarak girilmelidir. Bu formül bir 1 hücreli dizi formülü, daha sonra E4, E5 vb. hücrelere kopyalayıp yapıştırılabilir. Bunu, tek bir işlemde tüm E3:E5 aralığı için bir dizi formülü olarak girmeyin.
Önceki örnekte olduğu gibi, daha sonra alt toplamları toplamak için bir SUMIFS İşlevi kullanılır. Ürün Sayısı tarafından Ürün grubu:
| 1 | =TOPLA(C3:C11;B3:B11;E3) |
Kategori veya Gruba Göre Toplam - Veri Tablolarında Ara Toplamlar
Yukarıda gösterilen özet tablo yöntemine alternatif olarak, alt toplamları doğrudan bir veri tablosuna ekleyebiliriz. Bunu, SUMIFS İşlevi ile birlikte EĞER İşlevlerini kullanarak göstereceğiz. Gruba göre ara toplam orijinal veri tablosuna.
| 1 | =EĞER(B3=B2,"",TOPLA(C3:C11;B3:B11;B3)) |
Bu örnek, bir EĞER İşlevi içinde yuvalanmış bir SUMIF İşlevi kullanır. Örneği adımlara ayıralım:
Özet istatistikleri doğrudan bir veri tablosuna eklemek için SUMIFS İşlevini kullanabiliriz. toplamını alarak başlıyoruz Ürün Sayısı ilgili ile eşleşen Ürün grubu:
| 1 | =TOPLA(C3:C11;B3:B11;B3) |
Bu formül, her veri satırı için bir ara toplam değeri üretir. Alt toplamları yalnızca her birinin ilk veri satırında göstermek için Ürün grubu, EĞER İşlevini kullanırız. Verilerin zaten şuna göre sıralanmış olması gerektiğini unutmayın: Ürün grubu alt toplamların doğru görüntülendiğinden emin olmak için.
| 1 | =EĞER(B3=B2,"",TOPLA(C3:C11;B3:B11;B3)) |
EĞER İşlevi, her veri satırının Ürün grubu üzerindeki veri satırı ile değer ve aynı değere sahiplerse boş bir hücre (“”) verir.
Eğer Ürün grubu değerler farklıysa, toplam görüntülenir. Bu şekilde, her Ürün grubu toplam yalnızca bir kez görüntülenir (ilk örneğinin satırında).
Veri Kümelerini Gruba Göre Sıralama
Veriler zaten sıralanmamışsa, ara toplam için yine aynı formülü kullanabiliriz.
Yukarıdaki veri kümesi şuna göre sıralanmamıştır: Ürün grubu, Böylece Gruba göre ara toplam sütun, her bir ara toplamı bir kereden fazla görüntüler. Verileri istediğimiz formata getirmek için veri tablosunu seçip “A'dan Z'ye Sırala” seçeneğine tıklayabiliriz.
Hücre Referanslarını Kilitleme
Formüllerimizin okunmasını kolaylaştırmak için bazı formülleri kilitli hücre referansları olmadan gösterdik:
| 1 | =EĞER(B3=B2,"",TOPLA(C3:C11;B3:B11;B3)) |
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 | =EĞER(B3=B2,"",TOPLA($C$3:$C$11,$B$3:$B$11,B3)) |
Daha fazla bilgi için Hücre Referanslarını Kilitleme hakkındaki makalemizi okuyun.
Ara Toplamları Göstermek için Özet Tabloları Kullanma
Verileri önceden sıralama gereksinimini ortadan kaldırmak için Ürün grubu, bunun yerine verileri özetlemek için Pivot Tabloların gücünü kullanabiliriz. Pivot Tablolar, alt toplamları otomatik olarak hesaplar ve toplamları ve alt toplamları birkaç farklı biçimde görüntüler.
Google E-Tablolar'da Kategoriye veya Gruba Göre Toplama
Bu formüller, Google E-Tablolar'da Excel'de olduğu gibi çalışır. Ancak, UNIQUE İşlevi Google E-Tablolar'da büyük/küçük harf duyarlıdır.
