Örnek çalışma kitabını indirin
Bu öğretici, yalnızca ölçütlü görünür satırları sayarak "eğer alt toplamı"nın nasıl hesaplanacağını gösterecektir..
ARA TOPLAM İşlev
ARA TOPLAM işlevi, bir dizi veri üzerinde (sayı, toplam, ortalama vb.) çeşitli hesaplamalar yapabilir. En önemlisi, yalnızca görünür (filtrelenmiş) satırlarda hesaplama yapmak için kullanılabilir. Bu örnekte, ALTTOPLAM işlev_sayısı bağımsız değişkenini 3'e ayarlayarak (COUNTA) görünür satırları saymak için işlevi kullanacağız (Olası işlevlerin tam listesi burada bulunabilir.)
=ARATOPLAM(3,$D$2:$D$14)
Satırları manuel olarak filtreledikçe sonuçların nasıl değiştiğine dikkat edin.
ARA TOPLAM EĞER
Bir "Alt Toplam If" oluşturmak için, bir dizi formülünde SUMPRODUCT, ARATOPLAM, OFFSET, ROW ve MIN kombinasyonunu kullanacağız. Bu kombinasyonu kullanarak, esasen genel bir “ARA TOPLAM EĞER” işlevi oluşturabiliriz. Bir örnek üzerinden gidelim.
Her etkinlik için bir üye listemiz ve katılım durumlarımız var:
Listeyi şu şekilde manuel olarak filtrelediğimizde, bir etkinliğe katılan üye sayısını dinamik olarak saymamız istendiğini varsayalım:
Bunu başarmak için şu formülü kullanabiliriz:
=TOPLA((=)*(ARA TOPLAM(3,OFFSET(,SATIR()-MIN(SATIR()),0))))
=SUMPRODUCT((D2:D14="Katıldı")*(ARA TOPLAM(3,OFFSET(D2,SATIR(D2:D14)-MIN(SATIR(D2:D14)),0))))
Excel 2022 ve önceki sürümlerini kullanırken, dizi formülünü tuşuna basarak girmelisiniz. CTRL + ÜST KRKT + ENTER Excel'e bir dizi formülü girdiğinizi söylemek için. Formülün etrafında küme parantezleri göründüğünde, formülün bir dizi formülü olarak doğru şekilde girildiğini anlayacaksınız (yukarıdaki resme bakın).
Formül nasıl çalışır?
Formül, SUMPRODUCT içindeki iki diziyi çarparak çalışır, burada ilk dizi bizim kriterlerimizle ilgilenir ve ikinci dizi yalnızca görünür satırlara filtre uygular:
=TOPLA(*)
Ölçüt Dizisi
Ölçüt dizisi, değer aralığımızdaki her satırı değerlendirir (bu örnekte "Katıldı" Durumu) ve şöyle bir dizi oluşturur:
=(=)
=(D2:D14="Katıldı")
Çıktı:
{NS; YANLIŞ; YANLIŞ; NS; YANLIŞ; TUR; TUR; TUR; YANLIŞ; YANLIŞ; NS; YANLIŞ; NS}
Formülümüzdeki ilk dizideki çıktının, satırın görünür olup olmadığını görmezden geldiğini unutmayın, bu da ikinci dizimizin yardıma geldiği yerdir.
Görünürlük Dizisi
Aralığımızda görünmeyen satırları hariç tutmak için SUBTOTAL kullanarak görünürlük dizimizi oluşturabiliriz. Ancak, SUMPRODUCT bir dizi değer beklerken, SUBTOTAL tek başına tek bir değer döndürür. Bu soruna geçici bir çözüm bulmak için, her seferinde bir satır geçmek için OFFSET kullanırız. Bu teknik, her seferinde bir sayı içeren bir diziyi OFFSET beslemeyi gerektirir. İkinci dizi şöyle görünür:
=ARATOPLAM(3,OFFSET(,SATIR()-MIN(SATIR()),0))
=ARA TOPLAM(3,OFFSET(D2,SATIR(D2:D14)-MIN(SATIR(D2:D14))),0))
Çıktı:
{1;1;0;0;1;1}
İkisini birbirine dikmek:
=TOPLAÇA({DOĞRU; DOĞRU; YANLIŞ; YANLIŞ; DOĞRU; DOĞRU} * {1; 1; 0; 0; 1; 1})
= 4
Çoklu Kriterli EĞER ARA TOPLAMI
Birden çok ölçüt eklemek için, SUMPRODUCT içinde aşağıdaki gibi birden çok ölçütü bir araya getirmeniz yeterlidir:
=TOPLA((=)*(=)*(ARA TOPLAM(3,OFFSET(,SATIR()-MIN(SATIR()),0))))
=SUMPRODUCT((E2:E14="Katıldı")*(B2:B14=2019)*(ARA TOPLAM(3,OFFSET(E2,SATIR(E2:E14)-MIN(SATIR(E2:E14))),0)) ))
ARA TOPLAM EĞER Google E-Tablolar'da
ARA TOPLAM EĞER İşlevi, Google E-Tablolar'da Excel'dekiyle tamamen aynı şekilde çalışır:
