Excel'de Filtrelenmiş Hücreler Nasıl Toplanır?

TOPLA filtreli listede neden yanlış sonuç verir?

TOPLA, aralıktaki bütün hücreleri toplar ve bir satırın filtreyle gizlenip gizlenmediğine bakmaz. Satış listesinde yalnız Ege bölgesini filtrelediğinizde ekranda iki satır görürsünüz, ama altındaki TOPLA hâlâ bütün bölgelerin toplamını gösterir. Görünen satırların toplamı için başka bir fonksiyon gerekir.

Bu işi ALTTOPLAM yapar. İlk argümanı hangi hesabın yapılacağını belirler: 9 toplam, 1 ortalama, 3 dolu hücre sayısı demektir.

ALTTOPLAM ile görünen satırları toplamak

Filtreli bir sütunun altına =ALTTOPLAM(9;C2:C100) yazdığınızda formül yalnız görünen satırları toplar. Filtreyi değiştirdikçe sonuç da kendiliğinden güncellenir. Excel'de filtre açıkken Otomatik Toplam düğmesine basarsanız Excel TOPLA yerine bu formülü yazar.

9 ile 109 arasındaki fark elle gizlenen satırlardadır. Filtreyle gizlenen satırları iki kod da dışarıda bırakır. Satırı sağ tıklayıp Gizle ile sakladıysanız 9 bu satırı toplamaya devam eder, 109 ise atlar.

İç içe alt toplamlar iki kez sayılmaz

ALTTOPLAM'ın az bilinen bir özelliği, aralıktaki diğer ALTTOPLAM formüllerini yok saymasıdır. Bölge bölge ara toplam satırları olan bir raporda genel toplamı TOPLA ile alırsanız ara toplamlar da eklenir ve sonuç iki katına çıkar. ALTTOPLAM bu satırları atlayarak doğru genel toplamı verir.

=ALTTOPLAM(9;C2:C7)

=SUBTOTAL(9,C2:C7)

Bu örnekte TOPLA kullanılsaydı sonuç 357.500 TL olurdu. Ara toplam satırlarının da ALTTOPLAM ile yazılmış olması şarttır; TOPLA ile yazılmış ara toplamları ALTTOPLAM tanımaz ve toplamaya katar.

Hata içeren sütunlarda TOPLAMA

Sütunda #YOK gibi bir hata varsa hem TOPLA hem ALTTOPLAM hata döndürür. Excel 2010 ile gelen TOPLAMA fonksiyonu hem gizli satırları hem hata değerlerini atlayabilir. İlk argüman yine hesap türüdür, ikinci argüman neyin yok sayılacağını belirler.

=TOPLAMA(9;6;C2:C6)

=AGGREGATE(9,6,C2:C6)

İkinci argümanda 6 yalnız hataları, 7 hem gizli satırları hem hataları, 5 yalnız gizli satırları atlar. Filtreli ve hatalı bir sütunda görünen değerlerin toplamı için =TOPLAMA(9;7;C2:C100) yazmanız yeterlidir.

Filtreden bağımsız toplam: ÇOKETOPLA

Bazen toplamın filtreye değil, sabit bir koşula bağlı olması istenir. Raporu okuyan kişi filtreyi değiştirse bile Marmara toplamının aynı kalmasını istiyorsanız ÇOKETOPLA daha doğru araçtır.

=ÇOKETOPLA(C2:C7;A2:A7;"Marmara")

=SUMIFS(C2:C7,A2:A7,"Marmara")

İki yaklaşımı yan yana kullanmak da mümkündür. Üst kısımda ÇOKETOPLA ile sabit bölge toplamları, listenin altında ALTTOPLAM ile filtreye göre değişen toplam yer alabilir. Okuyan kişi iki rakam arasındaki farka bakarak hangi satırların filtre dışında kaldığını da anlar.

Toplamları listenin altına değil üstüne koymak da iyi bir alışkanlıktır. Filtre uygulandığında liste kısalır ve alttaki formül ekranın ortasına kayar; üstteki bir toplam ise hep aynı yerde durur.

Filtrelenmiş hücreleri kopyalama ve yapıştırma

Bir listeyi filtreledikten sonra seçip kopyaladığınızda Excel yalnız görünen satırları kopyalar. Satırları elle gizlediyseniz durum farklıdır ve gizli satırlar da kopyalanır. Bunu önlemek için alanı seçtikten sonra Alt+; kısayoluyla yalnız görünür hücreleri seçin, sonra kopyalayın.

Filtreli bir listeye yapıştırmak ise daha risklidir. Değerler görünen satırlara değil, gizli satırlar dahil art arda gelen hücrelere yazılabilir. Toplu düzeltmeyi filtresiz bir kopyada ya da formülle yapmak daha güvenlidir.

Toplam dışındaki hesaplar

ALTTOPLAM yalnız toplam almaz; ilk argümanı değiştirerek ortalama, sayı, en büyük ve en küçük değeri de görünen satırlardan hesaplarsınız. 1 ortalama, 2 sayı içeren hücre sayısı, 3 dolu hücre sayısı, 4 en büyük, 5 en küçük değer demektir. Bu kodların 100 eklenmiş hâlleri, yani 101, 103, 104 gibi değerler, elle gizlenen satırları da hesaba katmaz.

Rapor başlığında kaç kaydın görüntülendiğini göstermek için bu kodları metinle birleştirebilirsiniz. ="Görünen kayıt: "&ALTTOPLAM(103;A2:A100) filtre değiştikçe "Görünen kayıt: 12" gibi güncellenen bir başlık üretir.

Görünen satırların ortalamasını alırken boş hücrelere dikkat edin. Ortalama boş hücreleri hesaba katmaz, ama 0 yazılmış hücreleri katar. Satışı henüz girilmemiş satırlara 0 yazmak ortalamayı aşağı çeker.

Raporunuza uygulayın

Filtreli listelerinizin altındaki TOPLA formüllerini bulun ve ALTTOPLAM(9;...) ile değiştirin. Elle satır gizleme alışkanlığınız varsa 109 kodunu tercih edin; ara toplam içeren raporlarda ara satırların da ALTTOPLAM ile yazıldığını kontrol edin.