Rumus Excel: Sel mengandungi beberapa perkataan tetapi bukan yang lain -

Formula generik

(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))

Ringkasan

Untuk menguji sel untuk melihat apakah mengandungi kata-kata tertentu tetapi tidak yang lain, anda boleh menggunakan formula larik berdasarkan fungsi COUNT dan SEARCH, yang dibungkus dalam fungsi AND.

Dalam contoh yang ditunjukkan, formula dalam C5 adalah:

(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))

Formula ini mengembalikan TRUE apabila B5 mengandungi mana-mana perkataan dalam julat yang dinamakan inc dan tiada satu pun perkataan dalam julat yang dinamakan EXC .

Ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.

Penjelasan

Formula ini bergantung pada fungsi AND untuk menguji dua keadaan pada masa yang sama:

  1. Mengira kata-kata dari pelbagai bernama inc adalah> 0
  2. Jumlah perkataan dari rentang exc bernama = 0

Sekiranya kedua-dua keadaan itu BENAR, formula mengembalikan BENAR. Sekiranya salah satu syarat adalah SALAH, formula mengembalikan PALSU.

Ujian untuk pelbagai perkataan dilakukan menggunakan fungsi SEARCH dengan bantuan dari COUNT.

Apabila SEARCH menerima lebih dari satu item untuk dicari, ia mengembalikan pelbagai hasil, satu per item. Apabila perlawanan dijumpai, SEARCH mengembalikan kedudukan perlawanan. Apabila tiada perlawanan, SEARCH mengembalikan ralat # NILAI. Fungsi COUNT mendapat jumlah nombor dalam array yang dikembalikan oleh SEARCH. COUNT mengabaikan ralat secara lalai.

Dalam contoh yang ditunjukkan, formula diselesaikan dengan langkah seperti ini

=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE

Dengan nilai keras

Tidak ada syarat anda menggunakan rangkaian untuk senarai perkara anda. Sekiranya anda hanya mencari sebilangan kecil perkara, anda boleh menggunakan senarai dalam format array, yang dipanggil pemalar array. Sebagai contoh, untuk menguji warna merah, biru, atau hijau, tetapi tidak termasuk merah jambu dan oren, anda boleh menggunakan: 

=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)

Versi ini tidak memerlukan sintaks control + shift + enter array array.

Artikel menarik...