= SUMPRODUCT ( ISNUMBER ( MATCH (rng1,{'A','B'},0))* ISNUMBER ( MATCH (rng2,{'X','Y','Z'},0)))Ringkasan
Untuk mengira baris yang sepadan dengan beberapa kriteria ATAU, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam F10 adalah:
= SUMPRODUCT ( ISNUMBER ( MATCH (B5:B11,{'A','B'},0))* ISNUMBER ( MATCH (C5:C11,{'X','Y','Z'},0)))
Rumus ini mengembalikan bilangan baris di mana lajur satu adalah A atau B dan lajur dua adalah X, Y, atau Z.
PenjelasanBekerja dari dalam ke luar, setiap kriteria diterapkan dengan pembinaan ISNUMBER + MATCH yang terpisah. Untuk menghasilkan kiraan baris di lajur satu di mana nilainya adalah A atau B yang kami gunakan:
ISNUMBER ( MATCH (B5:B11,{'A','B'},0)
MATCH menghasilkan susunan hasil yang kelihatan seperti ini:
{12#N/A1212}
dan ISNUMBER menukar tatasusunan ini kepada tatasusunan ini:
{TRUETRUEFALSETRUETRUETRUETRUE}
Untuk menghasilkan kiraan baris di lajur dua di mana nilainya adalah X, Y, atau Z kita gunakan:
ISNUMBER ( MATCH (C5:C11,{'X','Y','Z'},0))
Kemudian MATCH kembali:
{1233#N/A12}
dan ISNUMBER menukar kepada:
{TRUETRUETRUETRUEFALSETRUETRUE}
Dua tatasusunan ini digandakan bersama di dalam SUMPRODUCT, yang secara automatik menukar nilai TRUE FALSE menjadi 1 dan 0 sebagai sebahagian daripada operasi matematik.
Jadi, untuk menggambarkan, hasil akhir dihasilkan seperti ini:
cara menghapus keseluruhan baris di excel
= SUMPRODUCT ({1101111}*{1111011}) = SUMPRODUCT ({1101011}) =5
Dengan rujukan sel
Contoh di atas menggunakan pemalar susunan berkod keras, tetapi anda juga boleh menggunakan rujukan sel:
= SUMPRODUCT ( ISNUMBER ( MATCH (B5:B11,E5:E6,0))* ISNUMBER ( MATCH (C5:C11,F5:F7,0)))
Lebih banyak kriteria
Pendekatan ini dapat 'ditingkatkan' untuk menangani lebih banyak kriteria. Awak boleh lihat contoh dalam cabaran formula ini .
Pengarang Dave Bruns