Genel formül
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Özet
Görünür satırları yalnızca ölçütlerle saymak için, SUMPRODUCT, SUBTOTAL ve OFFSET'e dayalı oldukça karmaşık bir formül kullanabilirsiniz. Gösterilen örnekte, C12'deki formül şöyledir:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Önsöz
ALTTOPLAM işlevi, gizli ve gizli olmayan satırlar için kolayca toplamlar ve sayımlar oluşturabilir. Ancak, yardım almadan COUNTIF veya SUMIF gibi ölçütleri işleyemez. Çözümlerden biri, SUMPRODUCT kullanarak hem ALTTOPLAM işlevini (KAYDIRMA yoluyla) hem de ölçütleri uygulamaktır. Bu yaklaşımın ayrıntıları aşağıda açıklanmıştır.
Açıklama
Özünde, bu formül SUMPRODUCT içinde iki dizi kurarak çalışır. İlk dizi ölçüt uygular ve ikinci dizi görünürlüğü işler:
=SUMPRODUCT(criteria*visibility)
Kriterler, formülün bir kısmı ile uygulanır:
=(C5:C8=C10)
Bunun gibi bir dizi oluşturan:
(FALSE;TRUE;FALSE;TRUE)
DOĞRU, "kriterleri karşılar" anlamına geldiğinde. Bu dizide çarpma (*) kullandığımız için, DOĞRU YANLIŞ değerleri matematik işlemiyle otomatik olarak 1'lere ve 0'lara dönüştürülecektir, bu nedenle sonuçta:
(0;1;0;1)
Görünürlük filtresi, 103 işlev numaralı SUBTOTAL kullanılarak uygulanır.
SUBTOTAL, hesaplamaları çalıştırırken gizli satırları dışlayabilir, bu nedenle bu durumda SUMPRODUCT içindeki gizli satırları dışlamak için bir "filtre" oluşturmak için kullanabiliriz. Sorun, SUBTOTAL'ın tek bir sayı döndürmesidir, oysa SUMPRODUCT içinde başarılı bir şekilde kullanmak için bir sonuç dizisine ihtiyacımız var. İşin püf noktası, OFFSET'i satır başına SUBTOTAL bir referans beslemek için kullanmaktır, böylece OFFSET satır başına bir sonuç döndürecektir.
Tabii ki, bu başka bir numara gerektirir, OFSET'e sıfırdan başlayarak satır başına bir sayı içeren bir dizi vermek. Bunu SATIR işlevi üzerine kurulu bir ifade ile yapıyoruz:
=ROW(C5:C8)-MIN(ROW(C5:C8)
bunun gibi bir dizi oluşturacak:
(0;1;2;3)
Özetle, görünürlüğü SUBTOTAL kullanarak işleyen ikinci dizi şu şekilde oluşturulur:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Ve nihayet elimizde:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Hangi 1 döndürür.
Birden çok kriter
Formülü aşağıdaki gibi birden çok ölçütü işleyecek şekilde genişletebilirsiniz:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Toplama sonuçları
Sayım yerine bir toplam değer döndürmek için formülü bir toplam aralığı içerecek şekilde uyarlayabilirsiniz:
=SUMPRODUCT(criteria*visibility*sumrange)
Ölçütler ve görünürlük dizileri, görünmeyen hücreler hariç yukarıda açıklandığı gibi çalışır. Kısmi eşlemeye ihtiyacınız varsa, burada açıklandığı gibi ISNUMBER + SEARCH kullanarak bir ifade oluşturabilirsiniz.
