Formula generik
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Ringkasan
Untuk mengira sel yang tidak mengandungi banyak rentetan yang berbeza, anda boleh menggunakan formula yang agak rumit berdasarkan fungsi MMULT. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
di mana "data" adalah julat bernama B5: B14, dan "kecualikan" adalah julat bernama D5: D7.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter
Kata Pengantar
Formula ini rumit oleh keperluan "mengandung". Sekiranya anda hanya memerlukan formula untuk mengira sel yang tidak * sama dengan banyak perkara, anda boleh menggunakan formula yang lebih mudah berdasarkan fungsi MATCH. Juga, jika anda mempunyai rentetan yang terhad untuk dikecualikan, anda boleh menggunakan fungsi COUNTIFS seperti ini:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Namun, dengan pendekatan ini, anda mesti memasukkan sepasang argumen rentang / kriteria baru untuk setiap rentetan untuk dikecualikan. Sebaliknya, formula yang dijelaskan di bawah ini dapat menangani sebilangan besar rentetan untuk mengecualikan yang dimasukkan secara langsung di lembaran kerja.
Akhirnya, formula ini rumit. Beritahu saya jika anda mempunyai formula yang lebih mudah untuk dicadangkan :)
Penjelasan
Inti formula ini ialah ISNUMBER dan CARI:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Di sini, kami mengalihkan item dalam julat bernama "exclude", kemudian memasukkan hasilnya ke SEARCH sebagai "cari teks", dengan "data" sebagai "dalam teks". Fungsi SEARCH mengembalikan array 2d nilai BENAR dan SALAH, 10 baris dengan 3 lajur, 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 rentetan carian) yang sama ada #VALUE kesalahan atau angka. Nombor mewakili kedudukan rentetan teks yang dijumpai, dan kesalahan menunjukkan rentetan teks yang tidak dijumpai. By the way, fungsi TRANSPOSE diperlukan untuk menghasilkan 10 x 3 hasil lengkap.
Array ini dimasukkan ke dalam ISNUMBER untuk mendapatkan nilai TRUE FALSE, yang kami ubah menjadi 1s dan 0s dengan operator negatif (-) berganda. Hasilnya adalah susunan 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 peraturan pendaraban matriks, bilangan lajur dalam array1 mesti sama dengan bilangan baris dalam array2. Untuk menghasilkan array2 , kami menggunakan fungsi ROW seperti ini:
ROW(exclude)^0
Ini menghasilkan susunan 1s, 3 baris dengan 1 lajur:
(1;1;1)
yang menjadi MMULT sebagai array2 . Setelah pendaraban array, kita mempunyai array yang dimensi agar sesuai dengan data asal:
(2;1;0;0;1;1;0;0;0;2)
Dalam tatasusunan ini, sebarang nombor bukan sifar mewakili nilai di mana sekurang-kurangnya satu rentetan yang dikecualikan telah dijumpai. Nol menunjukkan tiada rentetan yang dikecualikan dijumpai. Untuk memaksa semua nilai bukan sifar menjadi 1, kami menggunakan lebih besar daripada sifar:
(2;1;0;0;1;1;0;0;0;2)>0
yang mencipta satu lagi array atau nilai BENAR dan SALAH:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Matlamat akhir kami adalah mengira hanya nilai teks di mana tidak ada rentetan yang dikecualikan, jadi kami perlu membalikkan nilai ini. Kami melakukan ini dengan mengurangkan larik dari 1. Ini adalah contoh logik boolean. Operasi matematik secara automatik mengumpulkan nilai BENAR dan SALAH menjadi 1s dan 0s, dan akhirnya kita mempunyai susunan untuk kembali ke fungsi SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Fungsi SUM mengembalikan hasil akhir 5.
