Excel countif dan countifs dengan atau logik

Tutorial ini menunjukkan cara menggunakan fungsi COUNTIF dan COUNTIFS Excel untuk mengira sel -sel yang memenuhi pelbagai atau keadaan (contohnya, jika sel mengandungi x, y, atau z).

Walaupun COUNTIF mengendalikan kriteria tunggal dan menggunakan dan COUNTIFS , tutorial ini menyediakan penyelesaian untuk atau senario logik. Ia menganggap kebiasaan dengan sintaks asas dan penggunaan kedua -dua fungsi.

Mengira sel dengan atau keadaan

Kes yang paling mudah melibatkan pengiraan sel yang memuaskan sekurang -kurangnya satu daripada beberapa keadaan.

Kaedah 1: COUNTIF COUNTIF

Ini menambah hasil fungsi COUNTIF individu untuk setiap kriteria. Sebagai contoh, untuk mengira sel dalam lajur A yang mengandungi "epal" atau "pisang":

=COUNTIF(A:A, "apples") COUNTIF(A:A, "bananas")

Menggunakan rujukan sel (misalnya, kriteria dalam F1 dan G1) meningkatkan kecekapan dan penyelenggaraan:

=COUNTIF(A2:A10, F1) COUNTIF(A2:A10, G1)

Kaedah 2: COUNTIF dengan pemalar array

Ini menawarkan pendekatan yang lebih padat:

SUM(COUNTIF(*range*, {*criterion1*, *criterion2*, *criterion3*, …}))

Kriteria disertakan dalam array pemalar {"apples","bananas","lemons"} . COUNTIF mengembalikan pelbagai tuduhan, yang SUM tambah.

=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))

Menggunakan rujukan sel (F1: H1) memerlukan formula array (CTRL Shift ENTER):

=SUM(COUNTIF(A2:A10,F1:H1))

Kaedah 3: SUMPRODUCT

SUMPRODUCT(1\*(*range* ={*criterion1*, *criterion2*, *criterion3*, …})) atau SUMPRODUCT((*range*=*criterion1*) (*range*=*criterion2*) …)

Ini menguji setiap sel terhadap setiap kriteria, menjumlahkan array yang benar/palsu (ditukar kepada 1/0).

=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))

atau

=SUMPRODUCT((A2:A10="apples") (A2:A10="bananas") (A2:A10="lemons"))

Menggunakan Rujukan Sel (F1: H1):

=SUMPRODUCT(1*( A2:A10=F1:H1))

Nota: SUMPRODUCT boleh lebih perlahan daripada COUNTIF untuk dataset besar.

Mengira sel dengan atau dan logik

Ini memanjangkan konsep untuk memasukkan kedua -dua atau dan keadaan. Sebagai contoh, mengira "epal," "pisang," atau "lemon" yang "disampaikan" (lajur a dan lajur c):

Kaedah 1: COUNTIFS COUNTIFS

Ini menambah pelbagai fungsi COUNTIFS , masing -masing memeriksa satu atau keadaan dan keadaan dan.

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") COUNTIFS(A2:A10, "bananas", C2:C10, "delivered") COUNTIFS(A2:A10, "lemons", C2:C10, "delivered")

Kaedah 2: COUNTIFS dengan pemalar array

Versi yang lebih ringkas menggunakan pemalar array untuk atau kriteria:

=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))

Dengan rujukan sel (F1: H1 dan F2), formula array (CTRL Shift Enter) diperlukan:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))

Kad liar disokong (contohnya, "*bananas*" ). Tambahan dan syarat boleh ditambah kepada COUNTIFS .

Mengira sel dengan pelbagai atau keadaan

Untuk pelbagai set atau kriteria, COUNTIFS dengan pemalar array (terhad kepada dua set) atau SUMPRODUCT dengan ISNUMBER dan MATCH (mengendalikan lebih banyak set) digunakan.

Untuk dua set, gunakan susunan mendatar dan menegak dalam COUNTIFS :

=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))

Untuk pelbagai set, SUMPRODUCT dengan MATCH lebih serba boleh:

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

Tutorial ini menyediakan pelbagai kaedah untuk pengendalian atau syarat dalam fungsi pengiraan Excel, memenuhi tahap kerumitan dan saiz dataset yang berbeza. Buku kerja sampel disediakan untuk amalan selanjutnya.

Atas ialah kandungan terperinci Excel countif dan countifs dengan atau logik. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!