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

Excel’de ALTTOPLAM ve TOPLAMÇARPIM işlevlerini birleştirmek için aşağıdaki formülü kullanabilirsiniz:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Bu özel formül, C2:C11 aralığı ile D2:D11 aralığı değerlerinin çarpımını, o hücre aralığı bir şekilde filtrelendikten sonra bile toplamanıza olanak tanır.

Aşağıdaki örnekte bu formülün pratikte nasıl kullanılacağı gösterilmektedir.

Örnek: Excel’de SUMproduct ile SUBTOTAL nasıl kullanılır?

İki farklı marketteki çeşitli ürünlerin satışına ilişkin bilgileri içeren aşağıdaki veri setine sahip olduğumuzu varsayalım:

Daha sonra verileri yalnızca Mağaza sütunundaki değeri B olan satırları gösterecek şekilde filtreleyelim.

Bunu yapmak için A1:D11 hücre aralığını vurgulayın. Ardından üst şeritteki Veri sekmesine tıklayın ve Filtre düğmesine tıklayın.

Ardından Mağaza’nın yanındaki açılır oka tıklayın ve yalnızca B’nin yanındaki kutunun işaretlendiğinden emin olun ve ardından Tamam’a tıklayın:

Veriler, yalnızca Mağaza sütununun B’ye eşit olduğu satırları gösterecek şekilde otomatik olarak filtrelenecektir:

Satış ve Fiyat sütunu değerlerinin çarpımını toplamak için SUMRPODUCT() işlevini kullanmaya çalışırsak, aslında orijinal veri kümesindeki bu iki sütunun çarpımının toplamını döndürecektir:

Bunun yerine aşağıdaki formülü kullanmamız gerekiyor:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Aşağıdaki ekran görüntüsü bu formülün pratikte nasıl kullanılacağını göstermektedir:

Bu formül 77,5’in doğru toplamını döndürür.

Satış ve Fiyat sütunları arasındaki değerlerin çarpımının toplamını manuel olarak hesaplayarak bunun doğru olduğunu teyit edebiliriz:

Satış ve fiyat arasındaki değerlerin çarpımının toplamı: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

Ek kaynaklar

Aşağıdaki eğitimlerde Excel’de diğer yaygın işlemlerin nasıl gerçekleştirileceği açıklanmaktadır:

Excel’de filtrelenen satırlar nasıl silinir
Excel’de filtrelenmiş satırlar nasıl sayılır?
Excel’de filtrelenen satırlar nasıl toplanır