Formula umum
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Ringkasan
Untuk menghitung sel yang tidak sama dengan salah satu dari banyak hal, Anda bisa menggunakan rumus berdasarkan fungsi MATCH, ISNA, dan SUMPRODUCT. Dalam contoh yang diperlihatkan, rumus di sel F5 adalah:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
di mana "data" adalah rentang bernama B5: B16 dan "kecualikan" adalah rentang bernama D5: D7.
Penjelasan
Pertama, sedikit konteks. Biasanya, jika Anda hanya memiliki beberapa hal yang tidak ingin dihitung, Anda dapat menggunakan COUNTIFS seperti ini:
=COUNTIFS(range,"apple",range,"orange")
Tetapi ini tidak berskala dengan baik jika Anda memiliki daftar banyak hal, karena Anda harus menambahkan pasangan rentang / kriteria tambahan untuk setiap hal yang tidak ingin Anda hitung. Akan jauh lebih mudah untuk membuat daftar dan meneruskan referensi ke daftar ini sebagai bagian dari kriteria. Persis seperti itulah rumus di halaman ini.
Pada intinya, rumus ini menggunakan fungsi MATCH untuk menemukan sel yang tidak sama dengan "a", "b", atau "c" dengan ekspresi ini:
MATCH(data,exclude,0)
Perhatikan nilai pencarian dan array pencarian "dibalik" dari konfigurasi normal - kami memberikan semua nilai dari rentang bernama "data" sebagai nilai pencarian, dan memberikan semua nilai yang ingin kami kecualikan dalam rentang bernama "kecualikan". Karena kami memberikan MATCH lebih dari satu nilai pencarian, kami mendapatkan lebih dari satu hasil dalam array seperti ini:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Intinya, MATCH memberi kita posisi nilai yang cocok sebagai angka, dan mengembalikan # N / A untuk semua nilai lainnya.
Hasil # N / A adalah yang kami minati, karena mereka mewakili nilai yang tidak sama dengan "a", "b", atau "c". Karenanya, kami menggunakan ISNA untuk memaksa nilai-nilai ini menjadi TRUE, dan angka-angka menjadi FALSE:
ISNA(MATCH(data,exclude,0)
Kemudian kami menggunakan negatif ganda untuk memaksa TRUE menjadi 1 dan FALSE menjadi nol. Array yang dihasilkan, di dalam SUMPRODUCT terlihat seperti ini:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Dengan hanya satu larik untuk diproses, SUMPRODUCT menjumlahkan dan mengembalikan hasil akhir, 4.
Catatan: Menggunakan SUMPRODUCT sebagai ganti SUM menghindari kebutuhan untuk menggunakan control + shift + enter.
Hitung minus pertandingan
Cara lain untuk menghitung sel yang tidak sama dengan salah satu dari beberapa benda adalah dengan menghitung semua nilai, dan mengurangi kecocokan. Anda dapat melakukannya dengan rumus seperti ini:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Di sini, COUNTA mengembalikan hitungan semua sel yang tidak kosong. Fungsi COUNTIF, diberi rentang bernama "kecualikan" akan mengembalikan tiga hitungan, satu untuk setiap item dalam daftar. SUMPRODUCT menjumlahkan total, dan jumlah ini dikurangi dari jumlah semua sel yang tidak kosong. Hasil akhirnya adalah jumlah sel yang nilainya tidak sama di "exclude".
Literal mengandung logika tipe
Rumus di halaman ini dihitung dengan logika "sama dengan". Jika Anda perlu menghitung sel yang tidak berisi banyak string, di mana berisi berarti string dapat muncul di mana saja dalam sel, Anda memerlukan rumus yang lebih kompleks.
