Formula generik
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Ringkasan
Untuk mengira sel yang tidak sama dengan banyak perkara, anda boleh menggunakan formula berdasarkan fungsi MATCH, ISNA, dan SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam sel F5 adalah:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
di mana "data" adalah julat bernama B5: B16 dan "kecualikan" adalah julat bernama D5: D7.
Penjelasan
Pertama, sedikit konteks. Biasanya, jika anda mempunyai beberapa perkara yang tidak mahu anda hitung, anda boleh menggunakan COUNTIFS seperti ini:
=COUNTIFS(range,"apple",range,"orange")
Tetapi ini tidak sesuai dengan skala jika anda mempunyai senarai banyak perkara, kerana anda harus menambahkan pasangan julat / kriteria tambahan untuk setiap perkara yang tidak ingin anda hitung. Akan lebih mudah untuk membina senarai dan menyampaikan rujukan ke senarai ini sebagai sebahagian daripada kriteria. Itulah formula yang dilakukan oleh halaman ini
Pada intinya, formula ini menggunakan fungsi MATCH untuk mencari sel yang tidak sama dengan "a", "b", atau "c" dengan ungkapan ini:
MATCH(data,exclude,0)
Perhatikan nilai carian dan tatasusunan "dibalikkan" dari konfigurasi normal - kami memberikan semua nilai dari julat bernama "data" sebagai nilai pencarian, dan memberikan semua nilai yang ingin kami kecualikan dalam julat bernama "kecualikan". Kerana kami memberikan MATCH lebih dari satu nilai pencarian, kami mendapat lebih dari satu hasil dalam array seperti ini:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Pada dasarnya, MATCH memberi kita kedudukan nilai yang sepadan sebagai nombor, dan mengembalikan # N / A untuk semua nilai lain.
Hasil # N / A adalah yang kami minati, kerana mewakili nilai yang tidak sama dengan "a", "b", atau "c". Oleh itu, kami menggunakan ISNA untuk memaksa nilai-nilai ini menjadi BENAR, dan nombornya menjadi PALSU:
ISNA(MATCH(data,exclude,0)
Kemudian kami menggunakan negatif dua kali untuk memaksa TRUE ke 1 dan FALSE menjadi sifar. Susunan yang dihasilkan, di dalam SUMPRODUCT kelihatan 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 dan bukannya SUM mengelakkan keperluan untuk menggunakan kawalan + shift + enter.
Kira tolak
Cara lain untuk mengira sel tidak sama dengan beberapa perkara adalah dengan mengira semua nilai, dan tolak padanan. Anda boleh melakukannya dengan formula seperti ini:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Di sini, COUNTA mengembalikan jumlah semua sel yang tidak kosong. Fungsi COUNTIF, diberi julat bernama "exclude" akan mengembalikan tiga kiraan, satu untuk setiap item dalam senarai. SUMPRODUCT menambah jumlahnya, dan jumlah ini dikurangkan dari jumlah semua sel yang tidak kosong. Hasil akhirnya adalah bilangan sel yang tidak sama dengan nilai di "exclude".
Literal mengandungi logik jenis
Rumus di halaman ini dikira dengan logik "sama dengan". Sekiranya anda perlu mengira sel yang tidak mengandungi banyak rentetan, di mana mengandungi bermaksud tali boleh muncul di mana saja dalam sel, anda memerlukan formula yang lebih kompleks.
