Bir satış veya stok listesinde filtre uyguladığınızda ekranda görünen satırlar değişir. Normal TOPLA formülünün sonucu ise aynı kalabilir. Bu durum çoğu zaman bir hata değil, seçilen işlevin davranışıdır. ALTTOPLAM, filtrelenmiş kayıtlara göre özet üretmenizi sağlar. Ayrıca elle gizlenen satırların dahil edilmesi konusunda iki farklı işlev kodu grubu sunar. Bu rehberde toplam ve sayım örnekleriyle hangi kaydın hesaba girdiğini göreceğiz. Özellikle rapor ekranındaki görünür toplam ile bütün kaynak verinin toplamını birbirinden ayırmak isteyenler için uygulanabilir bir kontrol sırası hazırlayacağız.
Bu rehberde neler var?
Küçük bir veri listesiyle başlayın
A sütununda Ürün, B sütununda Şube, C sütununda Miktar olsun. Dört satıra sırasıyla Defter, Merkez, 10; Kalem, Merkez, 20; Defter, Şube, 15; Dosya, Şube, 5 yazın. Bütün miktarlar sayı türünde olmalıdır. Genel toplam 50’dir. Başlıkları ilk satırda tutup veriye filtre ekleyin. Dilerseniz Excel tablosu oluşturarak kapsamı daha yönetilebilir hâle getirebilirsiniz. İlk denemede ara toplam veya boş kayıt eklemeyin. Beklenen toplamı önceden bilmek, filtre sonrası davranışı doğru yorumlamanızı kolaylaştıracaktır.
Veri alanı C2:C5 ise normal toplam için =TOPLA(C2:C5) kullanın. Yanına =ALTTOPLAM(9;C2:C5) yazın. Filtre yokken ikisi de 50 verir. Türkçe Excel’de ALTTOPLAM adı bitişik yazılır; uygulamanın otomatik tamamlama listesinden seçmek yazım hatasını azaltır. İngilizce arayüzde işlev SUBTOTAL olarak bulunur. Noktalı virgül yerine başka bağımsız değişken ayırıcısı kullanan bölgesel ayarlarda formülün ayırıcısını uyarlamanız gerekebilir. İlk bağımsız değişken olan 9, hangi özet hesabının istendiğini belirtir; hücre adresi veya satır numarası değildir.
Filtrelenen kayıtlar neden dışarıda kalır?
Şube alanında yalnızca Merkez seçin. Görünen miktarlar 10 ve 20 olduğundan ALTTOPLAM sonucu 30 olmalıdır. Normal TOPLA ise kaynak alanın tamamını değerlendirmeye devam ederek 50 verir. Hangisinin doğru olduğu rapor sorusuna bağlıdır. Bütün kayıtları özetliyorsanız 50, seçilen şubenin görünür kayıtlarını soruyorsanız 30 beklenir. Bir sayının filtreyle değişmemesini otomatik hata sanmayın. Aynı raporda iki hesap da yararlı olabilir; başlıklarını farklı verin ve etkin filtreyi okuyucunun görebileceği şekilde tutun. Özellikle dışarıya gönderilen PDF’de filtre kapsamı açıkça belirtilmelidir.
Şimdi ürün filtresinde Defter seçin. Şube filtresinin hâlâ Merkez olarak kaldığını fark edin. İki filtre birlikte uygulandığında yalnızca 10 miktarlı satır görünür. Beklediğiniz 25 yerine 10 sonucu geldiğinde formülü değiştirmeden önce diğer etkin filtreleri inceleyin. Filtreyi temizlemek ile satırı elle gizlemek de aynı işlem değildir. Kaydı bulamadığınızda satır numaralarındaki atlamaları ve filtre simgelerini kontrol edin. Veri silinmediyse bütün filtreleri kaldırınca dört kayıt tekrar görünmelidir. Bu kısa deney raporlamada en sık karşılaşılan kapsam karışıklıklarından birini ortaya çıkarır.
9 ile 109 arasındaki önemli fark
Toplam kodu 9, filtreyle dışarıda kalan satırları yok sayar fakat elle gizlenen satırları dahil eder. 109 ise toplam hesabında elle gizlenen satırları da dışarıda bırakır. Bütün filtreleri temizleyin, ardından 20 miktarlı satırı satır menüsünden gizleyin. 9 kullanan hesap 50 kalırken 109 kullanan hesap 30 olur. İki kod grubunun farkını bu kontrollü örnekte görün. Sayfa düzenini sadeleştirmek için gizlenmiş bir satırın iş toplamından çıkarılması her durumda istenmeyebilir. Görsel düzen tercihini iş kuralı gibi uygulamadan önce raporun amacını belirleyin.
Kod seçimini yalnızca kısa bir formül kopyalayarak yapmayın. Excel yardımındaki işlev kodu tablosunu okuyun. Örneğin ortalama için 1 ve 101, sayısal hücre sayısı için 2 ve 102, dolu hücre sayısı için 3 ve 103 kullanılır. Filtreyle gizlenen satırlar her iki grupta dışarıda kalır; grupları ayıran nokta elle gizlenen satırlardır. Miktar sütununda boş hücre ile sıfırın farklı olduğunu unutmayın. Sayısal sayım sıfırı sayabilir, boş hücreyi saymaz. Dolu hücre sayımı ise açıklama metnini de sayabileceği için kayıt ölçünüzü bilinçli seçmelisiniz.
Sayım ve toplamı birlikte kontrol edin
Merkez filtresinde iki kayıt ve toplam 30 bekliyoruz. Bir miktarı boş bırakınca kayıt kimliği sayısı aynı kalırken sayısal miktar sayısı ve toplam değişebilir. Bu yüzden miktar sütununu sayarak her zaman gerçek kayıt sayısını elde edemezsiniz. Dolu olması zorunlu işlem kodu varsa sayım için onu kullanmak daha anlamlı olabilir. Ancak formülle boş metin döndüren hücrelerin sayım davranışını ayrıca deneyin. Raporda Toplam miktar ve Kayıt sayısı başlıklarını ayrı verin. Birinin diğerine eşit olması gerekmez; farklı sorulara cevap verirler.
ALTTOPLAM aralığı içinde başka ALTTOPLAM hesapları varsa işlev iç içe ara toplamların iki kez toplanmasını önlemek üzere bunları yok sayar. Bu davranış normal TOPLA ile yazılmış ara toplamlar için aynı sonucu garanti etmez. Kaynak listeye manuel genel toplam satırları ekleyip hepsini aynı kapsama almak beklenmedik çift sayımlara yol açabilir. Düz hareket listesini ve rapor alanını ayırmak daha anlaşılır bir yapıdır. Çok koşullu ve filtrelerden bağımsız sabit rapor gerekiyorsa ÇOKETOPLA kullanmayı değerlendirin. Görünür satır özeti ile ölçütlere bağlı özet farklı ihtiyaçlardır.
Son kontrol ve paylaşım
Teslim etmeden önce filtresiz toplamı, tek filtreyi, iki filtreyi ve elle gizlenen satırı ayrı ayrı test edin. Her adım için beklenen sayıyı not edin. Formül alanınızın yeni kayıtları kapsadığını kontrol edin; ALTTOPLAM da yanlış aralığı kendiliğinden düzeltemez. Dosyayı başka kullanıcıya gönderirken 9 veya 109 seçiminin nedenini açıklayın. Çıktıda yalnızca görünen veriler bulunacaksa başlığı buna göre yazın. Farklı şubelerin raporlarını karşılaştırırken aynı filtre ve kod politikasının kullanılması gerekir. Bu basit açıklamalar hesap doğru olsa bile sonuçların yanlış yorumlanmasını önleyebilir.
Grafiğin de aynı kapsamı gösterdiğinden emin olun
Rapora bağlı bir grafik varsa filtre sonrası onun da aynı kapsamı gösterdiğini inceleyin. Toplam değişirken grafik eski bütün kayıtları gösteriyorsa okuyucu iki alanın aynı listeyi temsil ettiğini düşünebilir. Başlıkları ve veri kaynaklarını birlikte kontrol edin. Miktar birimini açıkça yazın; adet ile koli aynı toplamda birleştirilmemelidir. Filtreyi temizleyip ilk genel toplama geri dönebildiğinizi son kez doğrulayın. Böylece deneme sırasında yanlışlıkla silinen veya düzenlenen bir değer olup olmadığını daha kolay fark edebilirsiniz.
Kaynak ve kapsam
Başvuru: Microsoft ALTTOPLAM işlevi. Bu örnekler satır tabanlı listeler içindir; özel rapor düzeninizde gizleme ve filtreleme davranışını ayrıca sınayın.