Alt Toplam

Alt Toplam (Subtotal), bir listedeki verileri gruplara ayırıp her grup için toplam, ortalama, sayı gibi özet değerler üreten Excel özelliğidir. Hem Veri sekmesindeki Alt Toplam komutu hem de ALTTOPLAM işlevi aynı mantıkla çalışır. Filtrelenmiş satırları hesaba katmaz ve aynı aralıktaki diğer alt toplamları tekrar saymaz.

Nerede bulunur​

Komut Veri > Anahat > Alt Toplam (Data > Outline > Subtotal) yolundadır. Bu komutun ayrı bir klavye kısayolu yoktur. ALTTOPLAM işlevi ise Formüller > İşlev Kitaplığı > Matematik ve Trigonometri (Formulas > Function Library > Math & Trig) altında yer alır; doğrudan hücreye de yazabilirsiniz.

Önemli: Veriniz bir Excel Tablosu ise Alt Toplam komutu pasif görünür. Önce Tablo Tasarımı > Aralığa Dönüştür (Table Design > Convert to Range) ile tabloyu normal aralığa çevirin. Tabloların kendi Toplam Satırı (Total Row) seçeneği ise arka planda ALTTOPLAM işlevini kullanır.

Alt Toplam komutunu kullanma​

Komut, verileri gruplayıp her grubun altına özet satırı ekler ve sayfanın soluna bir anahat (gruplama) yapısı kurar. Adımlar şöyledir:

• Veriyi sıralayın: Gruplayacağınız sütuna göre sıralayın (ör. Şehir). Sıralanmamış veride aynı şehir birden fazla grup olarak ayrılır.
• Aralık seçin: Veri aralığındaki herhangi bir hücreyi seçin. Başlık satırı olmalı ve sütunlarda boş başlık bulunmamalıdır.
• Komutu açın: Veri > Anahat > Alt Toplam yolunu izleyin.
• Her değişiklikte: (At each change in) Gruplanacak sütunu seçin.
• İşlev: (Use function) Toplam, Sayı, Ortalama, Maks, Min, Çarpım gibi bir işlev seçin.
• Alt toplam ekleme yeri: (Add subtotal to) Özetlenecek sütunları işaretleyin.
• Seçenekler: Geçerli alt toplamları değiştir (Replace current subtotals), Gruplar arasında sayfa sonu (Page break between groups) ve Verilerin altında özet (Summary below data) kutularını ihtiyacınıza göre ayarlayın.
• Tamam: Düğmesine basın. Her grubun sonuna Ankara Toplam gibi bir satır ve listenin sonuna Genel Toplam satırı eklenir.

Kaldırmak için aynı pencerede Tümünü Kaldır (Remove All) düğmesini kullanın. Sol üstteki 1, 2, 3 düğmeleri sırasıyla yalnız genel toplamı, grup özetlerini ve tüm ayrıntıları gösterir.

ALTTOPLAM işlevi ve işlev numaraları​

Alt Toplam komutunun eklediği satırlar aslında ALTTOPLAM (SUBTOTAL) formülleridir. İşlevi elle de yazabilirsiniz. Söz dizimi şöyledir:
İlk bağımsız değişken, hangi hesabın yapılacağını belirleyen sayıdır:

• 1 / 101: Ortalama
• 2 / 102: Sayı (yalnız sayılar)
• 3 / 103: Dolu hücre sayısı
• 4 / 104: En büyük değer
• 5 / 105: En küçük değer
• 6 / 106: Çarpım
• 7 / 107: Örnek standart sapma
• 8 / 108: Tüm kütle standart sapması
• 9 / 109: Toplam
• 10 / 110: Örnek varyans
• 11 / 111: Tüm kütle varyansı

En çok kullanılanlar toplam için 9 ve 109, görünen satırları saymak için 103 değeridir.

9 ile 109 arasındaki fark ve sınırlar​

1-11 arası numaralar, filtreyle gizlenen satırları hesaba katmaz ama elle gizlenen satırları (satır başlığına sağ tıklayıp Gizle) hesaba katar. 101-111 arası numaralar ise elle gizlenmiş satırları da dışarıda bırakır. Filtreyle gizlenmiş satırlar her iki grupta da her zaman hariç tutulur.

Diğer önemli noktalar:

• İç içe alt toplam: Aralık içindeki başka ALTTOPLAM formülleri yok sayılır; böylece genel toplam çift saymaz.
• Sütun gizleme: Gizli sütunlar yatay aralıklarda 101-111 numaralarını etkilemez; bu numaralar yalnız satır gizlemeye duyarlıdır.
• 3B başvuru: Birden fazla sayfayı kapsayan 3B başvurularla çalışmaz ve #DEĞER! hatası verir.
• Başvuru sayısı: En fazla 254 başvuru verilebilir.

1. Filtrelenmiş listede görünen satırların toplamı​

Altı satırlık satış listesinde Tutar sütunu Adet ile Birim Fiyat çarpımıdır. Liste, Şehir sütununda yalnız Ankara görünecek şekilde filtrelenmiştir. Alttaki üç formül aynı aralığı farklı yöntemlerle özetler. Kendiniz denemek için listeye Veri > Sırala ve Filtre > Filtre (Data > Sort & Filter > Filter) ile filtre koyup Şehir sütunundan Ankara seçin.
Filtre uygulandığında ALTTOPLAM yalnız görünen Ankara satırlarını toplar: E9 hücresi 103.400,00 değerini verir. TOPLA işlevi ise gizli satırları da dahil ettiği için E10 hücresinde 197.000,00 çıkar. 103 numarası görünen dolu hücreleri sayar ve E11 hücresinde 3 sonucunu verir. Filtreyi değiştirdiğinizde ALTTOPLAM sonuçları anında güncellenir.

2. Alt Toplam komutuyla şehir bazlı özet​

Veri Şehir sütununa göre sıralıdır. Veri > Anahat > Alt Toplam yolunda Her değişiklikte kutusunda Şehir, İşlev kutusunda Toplam seçilir; Alt toplam ekleme yeri olarak Adet ve Tutar sütunları işaretlenir. Aşağıdaki tablo, bu işlemin uygulanmasından sonraki durumu gösterir; işlem örnek dosyada Subtotal yöntemiyle uygulanmıştır ve tabloda şehir toplam satırları ile Genel Toplam satırı yer alır.
Komut uygulandığında Ankara, Bursa ve İzmir gruplarının her birinin altına Adet ve Tutar için birer toplam satırı eklenir; listenin sonunda da Genel Toplam satırı oluşur. Eklenen hücreler ALTTOPLAM formülleridir ve 9 numarasını kullanır. Sayfanın solundaki 1, 2 ve 3 düğmeleriyle yalnız genel toplamı, şehir toplamlarını ya da tüm ayrıntıyı görüntüleyebilirsiniz. Alt toplamları silmek için Alt Toplam penceresinde Tümünü Kaldır düğmesini kullanın.

3. Gizli satırlarda 9 ve 109 farkı​

Aylık satış listesinde Mart satırı (4. satır) elle gizlenmiştir; satır başlığına sağ tıklayıp Gizle komutunu kullanarak aynı durumu kurabilirsiniz. Alttaki üç formül aynı aralığı ALTTOPLAM 9, ALTTOPLAM 109 ve TOPLA ile toplar.
Mart satırı elle gizlendiği için 9 numarası bu satırı hesaba katmaya devam eder ve C9 hücresinde 759.750,00 sonucunu verir. 109 numarası gizli satırı dışarıda bırakır; C10 hücresi 649.750,00 değerini gösterir. TOPLA işlevi gizli satırı da toplar ve C11 hücresinde 759.750,00 çıkar. Gizli satırları rapordan çıkarmak istiyorsanız 109 numarasını kullanın.

Kullanırken dikkat edin​

• Sıralamayı unutmayın: Alt Toplam komutu yalnız yan yana duran aynı değerleri grup sayar. Veriyi önce gruplayacağınız sütuna göre sıralamazsanız aynı şehir için birden fazla alt toplam satırı oluşur.
• Yanlış numara seçimi: Gizli satırları dışarıda bırakmak istediğiniz raporlarda 9 yerine 109 kullanın. Yalnızca filtre kullanıyorsanız ikisi aynı sonucu verir; elle gizleme kullanıyorsanız fark ortaya çıkar. Sütun gizleme ise 101-111 numaralarını etkilemez.
• Tablolarda komut pasiftir: Excel Tablosu içinde Alt Toplam komutu kullanılamaz. Tabloyu aralığa dönüştürün ya da tablonun Toplam Satırı seçeneğini kullanın; bu satır ALTTOPLAM formülüyle çalışır ve filtreye duyarlıdır.
• Alt toplam satırlarını kopyalama: Anahat düzeyi 2 görünümündeyken seçimi kopyalarsanız gizli ayrıntı satırları da kopyalanabilir. Önce Git > Özel > Yalnızca görünen hücreler (Go To > Special > Visible cells only) seçeneğini ya da Alt+; kısayolunu kullanın.
• Değer kopyalarken dikkat: Alt Toplam komutunu uyguladıktan sonra listeyi sıralarsanız alt toplam satırları da sıralamaya karışır. Sıralama gerekiyorsa önce alt toplamları kaldırın, sıralayın, sonra yeniden ekleyin.
• Diğer hata yapan durumlar: 3B başvuru (Sayfa1:Sayfa3!A1) kullanılırsa ALTTOPLAM #DEĞER! hatası döndürür. Gelişmiş seçenekler (hataları yok sayma gibi) gerekiyorsa AGGREGATE işlevine bakabilirsiniz.
• İlgili konular: Veri > Filtre (Otomatik Filtre), Gruplandırma (Anahat), Tablo Toplam Satırı, Özet Tablo (PivotTable), TOPLA, ETOPLA, BAĞ_DEĞ_SAY, ALTTOPLAM

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.