ALTTOPLAM Formülü: Excel'de Filtrelenmiş Satırları Toplama

ALTTOPLAM (İngilizce SUBTOTAL) formülü =ALTTOPLAM(9;C2:C8), C2:C8'deki sayıları TOPLA gibi toplar, iki farkla: aralığın içindeki diğer ALTTOPLAM formüllerini atlar ve filtreyle gizlenen satırları atlar. İlk bağımsız değişken olan 9, hangi hesaplamanın yapılacağını söyler. Tablodaki formülleri de bu şekilde, Türkçe adlarla ve noktalı virgülle yazabilirsiniz.

FormüllerSıfırla

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =ALTTOPLAM(9;C2:C7)

C8'deki genel toplam alt toplam satırları dahil bütün sütunu kapsar ve yine 445 gösterir: ALTTOPLAM, C4 ve C7'yi dışarıda bırakır, çünkü onlar ALTTOPLAM formülleri içerir. F2 aynısını TOPLA ile yapar ve 890 gösterir, her satış iki kez sayılmıştır. Her toplam satırında ALTTOPLAM varken genel toplamı yeniden yazmadan grup ekleyebilir veya grupları taşıyabilirsiniz.

ALTTOPLAM işlev numaraları

=SUBTOTAL(function_num, ref1, [ref2], ...)

=ALTTOPLAM( yazdığınızda Excel bu listeyi gösterir, bu yüzden ezberlemeniz gerekmez. En çok kullanılanlar 9 ve 109 (TOPLA), 1 (ORTALAMA) ve 103'tür (görünür satırları sayma).

FormüllerSıfırla

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =ALTTOPLAM(1;C2:C7)

Burada hiçbir şey gizli değil, bu yüzden her satır sıradan fonksiyona eşittir: 88.33 ortalama, 6 satır, 200 en büyük değer, 30 en küçük değer ve 530 toplam. Fark yalnızca satırlar gizlendiğinde ortaya çıkar; bir sonraki bölüm bununla ilgili.

ALTTOPLAM 9 ile 109 ve filtrelenen satırlar

Veri > Filtre ile bir filtre açın (Ctrl+Shift+L, Mac'te Cmd+Shift+F), sonra Region açılır listesinde North'u seçin. Diğer bölgelerin satırları gizlenir:

• =TOPLA(C2:C7) yine altı satırın hepsini toplar.
• =ALTTOPLAM(9;C2:C7) ve =ALTTOPLAM(109;C2:C7) yalnızca görünür North satırlarını toplar.
• =ALTTOPLAM(103;A2:A7) ekranda kalan satırları sayar: 2, durum çubuğunda bulunan kayıt sayısıyla aynı.

İki aile yalnızca elle gizlediğiniz satırlarda farklıdır (satırları seçip sağ tık > Gizle). 9 onları yine toplar; 109 toplamaz. Toplam her zaman ekranda görünenle eşleşmeliyse 109 kullanın. Satırları yalnızca görünümü toparlamak için gizliyor ve onların yine toplamda olmasını istiyorsanız 9 kullanın.

ALTTOPLAM yalnızca satırlarda çalışır. Gizli sütunlar her zaman dahil edilir, bu yüzden bir satır boyunca =ALTTOPLAM(109;B2:G2) gizli sütunları da toplar.

ALTTOPLAM almanın en hızlı yolu, filtre açıkken Otomatik Toplam düğmesidir: Excel TOPLA yerine =ALTTOPLAM(9;...) yazar. Veri > Alt Toplam bir adım öteye gider: bir sütuna göre sıralanmış bir listede her grubun altına bir toplam satırı ve bir genel toplam ekler, hepsi ALTTOPLAM ile, ayrıca grupları daraltmak için ana hat düğmeleri koyar.

TOPLAMA: hataları atlayabilen ALTTOPLAM

Aralıktaki bir hücre hata içeriyorsa TOPLA ve ALTTOPLAM o hatayı döndürür. TOPLAMA (İngilizce AGGREGATE, Excel 2010 ve sonrası), fazladan bir seçenekler bağımsız değişkeni olan ALTTOPLAM'dır; 6 seçeneği hata değerlerini yok sayar.

FormüllerSıfırla

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =TOPLAMA(9;6;C2:C7)

C3 #N/A (Türkçe Excel'de #YOK) içerir, bu yüzden F2 de #N/A gösterir; tablo hata adlarını İngilizce gösterir. F3 onu yok sayar ve diğer beşini toplar: 450. Türkçe Excel'de F3 =TOPLAMA(9;6;C2:C7) olarak yazılır. İlk bağımsız değişkeni ALTTOPLAM ile aynı numaraları kullanır (9 TOPLA, 4 MAK'tır). Diğer seçenekler: 5 gizli satırları, 7 gizli satırları ve hataları, 3 gizli satırları, hataları ve iç içe ALTTOPLAM ile TOPLAMA formüllerini yok sayar. C3'ü bir sayıyla değiştirin, F2 F3 ile aynı toplamı gösterir.

Alıştırma: alt toplamların üzerinde bir genel toplam

FormüllerSıfırla

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.

Sıra sende: Listede her bölgenin altında bir alt toplam var. C9'a, alt toplam satırlarını iki kez saymadan C2:C8 aralığını kapsayan bir genel toplam koyun.

Kontrol etCevabı göster

ALTTOPLAM toplamı neden hâlâ yanlış

• Grup toplamları TOPLA kullanıyor. ALTTOPLAM aralığının içindeki diğer ALTTOPLAM formüllerini atlar, TOPLA formüllerini değil. =TOPLA(C2:C3) olarak yazılmış bir grup toplamı yeniden sayılır. Her toplam satırını ALTTOPLAM'a çevirin.
• Satırlar elle gizlendi ve işlev numarası 9. 109 kullanın.
• Veriler satırlarda değil sütunlarda. Gizli sütunlar asla atlanmaz.
• Size filtre değil koşul gerekiyor. ALTTOPLAM filtrenin gizlediğini izler. Filtrelemeden North'u toplamak için ETOPLA kullanın. Her grubun özetini tek seferde almak için bir özet tablo bunu verilerde toplam satırları olmadan yapar.