Formula umum
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Ringkasan
Untuk menghitung sel yang tidak berisi banyak string berbeda, Anda bisa menggunakan rumus yang agak rumit berdasarkan fungsi MMULT. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
di mana "data" adalah rentang bernama B5: B14, dan "kecualikan" adalah rentang bernama D5: D7.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter
Kata pengantar
Rumus ini diperumit dengan persyaratan "berisi". Jika Anda hanya memerlukan rumus untuk menghitung sel yang tidak * sama dengan * banyak hal, Anda dapat menggunakan rumus yang lebih mudah berdasarkan fungsi MATCH. Selain itu, jika Anda memiliki sejumlah string untuk dikecualikan, Anda dapat menggunakan fungsi COUNTIFS seperti ini:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Namun, dengan pendekatan ini, Anda harus memasukkan pasangan argumen rentang / kriteria baru untuk setiap string yang akan dikecualikan. Sebaliknya, rumus yang dijelaskan di bawah ini dapat menangani sejumlah besar string untuk dikecualikan yang dimasukkan langsung di lembar kerja.
Akhirnya, rumus ini rumit. Beri tahu saya jika Anda memiliki rumus yang lebih sederhana untuk diusulkan :)
Penjelasan
Inti dari rumus ini adalah ISNUMBER dan SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Di sini, kami mengubah urutan item dalam rentang bernama "kecualikan", lalu masukkan hasilnya ke SEARCH sebagai "temukan teks", dengan "data" sebagai "dalam teks". Fungsi SEARCH mengembalikan array 2d nilai TRUE dan FALSE, 10 baris kali 3 kolom, seperti ini:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Untuk setiap nilai dalam "data", kami memiliki 3 hasil (satu per string penelusuran) yang berupa kesalahan #VALUE atau angka. Angka mewakili posisi string teks yang ditemukan, dan kesalahan mewakili string teks yang tidak ditemukan. Ngomong-ngomong, fungsi TRANSPOSE diperlukan untuk menghasilkan larik 10 x 3 hasil lengkap.
Larik ini dimasukkan ke dalam ISNUMBER untuk mendapatkan nilai TRUE FALSE, yang kita ubah menjadi 1 dan 0 dengan operator negatif ganda (-). Hasilnya adalah array seperti ini:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
yang masuk ke fungsi MMULT sebagai array1. Mengikuti aturan perkalian matriks, jumlah kolom di array1 harus sama dengan jumlah baris di array2. Untuk menghasilkan array2 , kami menggunakan fungsi ROW seperti ini:
ROW(exclude)^0
Ini menghasilkan larik 1s, 3 baris kali 1 kolom:
(1;1;1)
yang masuk ke MMULT sebagai array2 . Setelah perkalian array, kita memiliki sebuah array yang berdimensi agar sesuai dengan data asli:
(2;1;0;0;1;1;0;0;0;2)
Dalam larik ini, angka bukan nol apa pun mewakili nilai di mana setidaknya satu string yang dikecualikan telah ditemukan. Angka nol menunjukkan tidak ditemukan string yang dikecualikan. Untuk memaksa semua nilai bukan nol menjadi 1, kami menggunakan lebih besar dari nol:
(2;1;0;0;1;1;0;0;0;2)>0
yang membuat larik lain atau nilai TRUE dan FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tujuan akhir kita adalah menghitung hanya nilai teks yang tidak ditemukan string yang dikecualikan, jadi kita perlu membalikkan nilai-nilai ini. Kami melakukan ini dengan mengurangi array dari 1. Ini adalah contoh logika boolean. Operasi matematika secara otomatis memaksa nilai TRUE dan FALSE menjadi 1 dan 0, dan akhirnya kita memiliki array untuk kembali ke fungsi SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Fungsi SUM mengembalikan hasil akhir dari 5.
