Formula generik
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Ringkasan
Untuk mengira baris yang dapat dilihat hanya dengan kriteria, anda boleh menggunakan formula yang agak rumit berdasarkan SUMPRODUCT, SUBTOTAL, dan OFFSET. Dalam contoh yang ditunjukkan, formula di C12 adalah:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Kata Pengantar
Fungsi SUBTOTAL dapat menghasilkan jumlah dan kiraan dengan mudah untuk baris tersembunyi dan tidak tersembunyi. Walau bagaimanapun, ia tidak dapat menangani kriteria seperti COUNTIF atau SUMIF tanpa bantuan. Salah satu penyelesaiannya adalah dengan menggunakan SUMPRODUCT untuk menerapkan fungsi SUBTOTAL (melalui OFFSET) dan kriteria. Perincian pendekatan ini dijelaskan di bawah.
Penjelasan
Pada intinya, formula ini berfungsi dengan menetapkan dua tatasusunan di dalam SUMPRODUCT. Array pertama menggunakan kriteria, dan larik kedua menangani keterlihatan:
=SUMPRODUCT(criteria*visibility)
Kriteria diterapkan dengan sebahagian formula:
=(C5:C8=C10)
Yang menghasilkan susunan seperti ini:
(FALSE;TRUE;FALSE;TRUE)
Di mana BENAR bermaksud "memenuhi kriteria". Perhatikan kerana kita menggunakan pendaraban (*) pada array ini, nilai TRUE FALSE secara automatik akan ditukar menjadi 1 dan 0 dengan operasi matematik, jadi kita berakhir dengan:
(0;1;0;1)
Penapis keterlihatan digunakan menggunakan SUBTOTAL, dengan fungsi nombor 103.
SUBTOTAL dapat mengecualikan baris tersembunyi ketika menjalankan pengiraan, jadi kami dapat menggunakannya dalam hal ini untuk menghasilkan "filter" untuk mengecualikan baris tersembunyi di dalam SUMPRODUCT. Masalahnya ialah SUBTOTAL mengembalikan satu nombor, sementara kita memerlukan pelbagai hasil untuk berjaya menggunakannya dalam SUMPRODUCT. Caranya adalah dengan menggunakan OFFSET untuk memberi SUBTOTAL satu rujukan setiap baris, sehingga OFFSET akan mengembalikan satu hasil per baris.
Sudah tentu, itu memerlukan muslihat lain, iaitu dengan memberi OFFSET array yang mengandungi satu nombor setiap baris, bermula dengan sifar. Kami melakukannya dengan ungkapan yang dibina berdasarkan fungsi ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
yang akan menghasilkan susunan seperti ini:
(0;1;2;3)
Ringkasnya, susunan kedua (yang menangani keterlihatan menggunakan SUBTOTAL), dihasilkan seperti ini:
=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)
Dan akhirnya, kami mempunyai:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Yang mengembalikan 1.
Pelbagai kriteria
Anda boleh memperluas formula untuk menangani pelbagai kriteria seperti ini:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Hasil penjumlahan
Untuk mengembalikan jumlah nilai dan bukannya kiraan, anda boleh menyesuaikan formula untuk memasukkan julat jumlah:
=SUMPRODUCT(criteria*visibility*sumrange)
Susunan kriteria dan keterlihatan berfungsi sama seperti yang dijelaskan di atas, tidak termasuk sel yang tidak kelihatan. Sekiranya anda memerlukan sepadan separa, anda boleh membina ungkapan menggunakan ISNUMBER + SEARCH, seperti yang dijelaskan di sini.
