Excel'de if ile subtotal nasıl kullanılır?

BU FORMÜL SAF BİR SİHİRDİR.

Excel’de IF (koşul) ile ALTTOPLAM formülü yazmak istiyorsanız, bunu yapmak için çeşitli işlevleri kullanmanız gerekir. Ancak bunu yapmadan önce bu örnek için elimizdeki verileri anlayalım.

Yukarıdaki örnekte üç sütununuz var:

• İsim
• Yaş levhası
• Cinsiyet

Yaş Tablosu sütunundan bir döşemeyi filtrelediğinizde F1 hücresindeki kadın sayısı görüntülenir. Yani bu, filtrelenen değerlerin sayısını ancak bir koşulla görüntüleyen bir formülümüz olduğu anlamına gelir.

Elimizdeki formül:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

ALTTOPLAM IF formülünü anlama

Bu formül beş işlevi kullanır: TOPLAM ÇARPIM, ALTTOPLAM, KAYDIRMA, SATIR ve MAKS. Bu nedenle bu formülü anlamak için onu birkaç parçaya ayırmamız gerekiyor.

1.SATIR(C2:C41)-2.0)

Formülün bu kısmı MIN ve SATIR işlevlerini kullanır.

• SATIR’da “Cinsiyet” sütunlarına başvuruda bulunduk ve bu, bir satır numarası dizisi döndürüyor.
• Bundan sonra MIN bu dizi veya satır numaralarını alır ve minimum satır sayısını döndürür. Bu yüzden formülün bu kısmında 2 var.

2.SATIR(C2:C41)

Bu bölümde yalnızca satır numaralarının bir dizisini döndüren ROW fonksiyonumuz var.

3. OFSET(C2,SATIR(C2:C41)-MIN(SATIR(C2:C41))),0)

Artık OFFSET fonksiyonumuz var. Başlangıç noktası olarak hücre referansını kullanarak bir aralığa referans oluşturmanıza yardımcı olur. Referans argümanında cinsiyet aralığımızın başladığı ilk hücre olan C2 hücresinden bahsettik.

Çizgiler argümanında, yukarıda ilk iki bölümde tartışılan formülün bu kısmına sahibiz. Bundan sonra cols argümanında 0 kullandık. Tüm bunlarla birlikte OFFSET, “Sex” sütunundaki tüm değerlerin bir dizisini döndürür.

4. ALTTOPLAM(3,KAYDIRMA(C2,SATIR(C2:C41)-MIN(SATIR(C2:C41))),0))

ALTTOPLAM’da OFFSET’in döndürdüğü diziyi kullandık. Ve fonksiyon_numunda, SUBTOTAL’a hesaplama için COUNTA fonksiyonunu kullanmasını söyleyen 3’ü kullandık.

“Yaş Döşemesi” sütununda bir filtre kullandığınızda, formülün bu ALTTOPLAM kısmı 0 ve 1 içeren bir dizi döndürür.

Bu tabloda filtreyi uyguladığımız değere eşdeğer değerler için 1 değerimiz var. Aşağıdaki örneğe bakın:

5. (C2:C41=E1)

Formülün bu kısmı, bir koşulu test ederek bir dizi döndürür. Bu durumda aralık değerinin “Kadın” olup olmadığını test ediyoruz ve dizide DOĞRU ve YANLIŞ değerini döndürüyor.

Bu tabloda “Kadın” değeri için DOĞRU, diğerleri için YANLIŞ değerini aldık.

7. Son bölüm

Sonuç olarak SUMproduct’ta iki tablomuz var. Ayrıca bu diziler arasında bir yıldız işareti operatörümüz var.

İki tabloyu birbiriyle çarptığımızda 0 ve 1’den oluşan tek bir tablo elde ediyoruz. Bu tabloda bir (1) cinsiyette “Kadın” değeri için, 21-30 ise “Yaş Plakası” için geçerlidir.

Sonunda SUMproduct bu diziyi kullanarak toplamı döndürür. Bu toplam da “Yaş Aralığı” sütununda 21-30 arası filtreleme yaptığınızda cinsiyet sütununda “Kadın” değerini taşıyan hücrelerin sayısıdır.