Excel Sekiranya Pernyataan untuk Perlawanan Teks Separa (Wildcard)

Cuba untuk membina pernyataan IF dengan teks wildcard, tetapi ia gagal setiap kali? Masalahnya tidak dalam formula anda tetapi dalam fungsi itu sendiri - Excel jika tidak menyokong watak Wildcard. Walau bagaimanapun, ada cara untuk membuatnya berfungsi untuk pertandingan teks separa, dan tutorial ini akan mengajar anda bagaimana.

Setiap kali anda ingin melakukan sepadan dengan separa atau kabur dalam Excel, penyelesaian yang paling jelas adalah menggunakan Wildcards. Tetapi bagaimana jika fungsi khusus yang perlu anda gunakan tidak menyokong aksara liar? Malangnya, Excel jika salah satu fungsi sedemikian. Ini amat mengecewakan memandangkan fungsi "bersyarat" lain seperti Countif, Sumif, dan Purata bekerja dengan Wildcards dengan sempurna.

Nasib baik, bukan halangan yang boleh menghentikan pengguna Excel yang kreatif :) Dengan menggabungkan jika dengan fungsi lain, anda boleh memaksanya untuk menilai perlawanan separa dan mendapatkan alternatif yang bagus untuk Excel jika formula Wildcard.

• Mengapa cemerlang jika berfungsi dengan kad liar tidak berfungsi
• Cara membuat jika pernyataan dengan teks wildcard
• Sekiranya Formula Carian Isnumber untuk Perlawanan Separa
• Jika atau pernyataan wildcard
• Jika formula dan formula wildcard

Mengapa cemerlang jika berfungsi dengan kad liar tidak berfungsi

Dalam jadual sampel di bawah, anggap anda ingin menyemak sama ada ID dalam lajur pertama mengandungi huruf "A". Jika dijumpai - paparan "ya" dalam lajur b, jika tidak - paparan "tidak".

Nampaknya termasuk teks wildcard dalam ujian logik akan menjadi penyelesaian yang mudah:

=IF(A2="*a*","Yes", "No")

Tetapi dikesali ia tidak berfungsi. Formula mengembalikan "tidak" untuk semua sel, bahkan yang mengandungi "A":

Mengapa kad liar jika pernyataan gagal? Dari semua penampilan, Excel tidak mengiktiraf kad liar yang digunakan dengan tanda yang sama atau pengendali logik lain. Melihat dengan lebih dekat senarai fungsi yang menyokong kad liar, anda akan melihat bahawa sintaks mereka menganggap teks wildcard muncul secara langsung dalam hujah seperti ini:

=COUNTIF(A2:A10, "*a*")

Cemerlang jika mengandungi teks separa

Sekarang anda tahu sebab mengapa kad liar jika formula gagal, mari kita cari bagaimana untuk mendapatkannya berfungsi. Untuk ini, kita hanya akan menanamkan fungsi yang menerima kad liar dalam ujian logik jika, iaitu fungsi countif:

Jika (countif ( sel , " * teks *"), value_if_true, value_if_false)

Dengan pendekatan ini, jika tidak mempunyai masalah dengan memahami wildcards dan dengan sempurna mengenal pasti sel-sel yang mengandungi sama ada "a" atau "a" (kerana countif tidak sensitif kes):

=IF(COUNTIF(A2, "*a*"),"Yes", "No")

Formula ini pergi ke B2, atau mana -mana sel lain dalam baris 2, dan kemudian anda boleh menyeretnya ke seberapa banyak sel yang diperlukan:

Penyelesaian ini juga boleh digunakan untuk mencari rentetan corak tertentu . Dengan mengandaikan hanya ID yang terdiri daripada 2 kumpulan 2 aksara yang dipisahkan dengan tanda hubung yang sah, anda boleh menggunakan "??-??" String kad liar untuk mengenal pasti mereka:

=IF(COUNTIF(A2, "??-??"), "Valid", "")

Bagaimana formula ini berfungsi:

Untuk ujian logik jika, kami menggunakan fungsi countif yang mengira bilangan sel yang sepadan dengan rentetan wildcard yang ditentukan. Oleh kerana julat kriteria adalah sel tunggal (A2), hasilnya sentiasa 1 (perlawanan dijumpai) atau 0 (perlawanan tidak dijumpai). Memandangkan 1 sama dengan benar dan 0 kepada palsu, formula mengembalikan "sah" (value_if_true) apabila kiraannya adalah 1 dan rentetan kosong (value_if_false) apabila kiraannya adalah 0.

Sekiranya Formula Carian Isnumber untuk Perlawanan Separa

Satu lagi cara untuk memaksa Excel jika bekerja untuk perlawanan teks separa adalah memasukkan sama ada fungsi mencari atau carian dalam ujian logik. Perbezaannya ialah mencari kes-sensitif semasa carian tidak.

Oleh itu, bergantung kepada sama ada anda ingin merawat huruf kecil dan huruf besar sebagai watak yang sama atau berbeza, salah satu daripada formula ini akan berfungsi dengan baik:

Formula kes-insensitif untuk perlawanan separa:

Jika (isNumber (carian (" teks ", sel )), value_if_true, value_if_false)

Formula sensitif kes untuk perlawanan separa:

Jika (isNumber (cari (" teks ", sel )), value_if_true, value_if_false)

Memandangkan kedua -dua fungsi direka untuk melaksanakan "sel mengandungi" jenis perlawanan, kad liar tidak diperlukan dalam kes ini.

Sebagai contoh, untuk mengesan ID yang mengandungi "A" atau "A", formula adalah:

=IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No")

Untuk hanya mencari modal "a" dan abaikan "a", formula adalah:

=IF(ISNUMBER(FIND("A", A2)), "Yes", "No")

Dalam B6 dalam tangkapan skrin di bawah, anda dapat melihat perbezaan hasilnya:

Bagaimana formula ini berfungsi:

Di tengah -tengah formula, terdapat gabungan isnumber dan carian (atau mencari):

ISNUMBER(SEARCH("A", A2))

Fungsi carian mencari teks yang ditentukan ("A" dalam contoh ini) dan mengembalikan kedudukannya dalam rentetan dalam A2. Jika teks tidak dijumpai, ralat #value dikembalikan. Memandangkan kedua -dua carian dan mencari direka untuk melakukan "sel mengandungi" jenis perlawanan, kad liar tidak benar -benar diperlukan dalam kes ini.

Fungsi ISNumber menukar nombor kepada benar dan apa -apa nilai lain termasuk ralat kepada palsu. Nilai logik terus ke ujian logik jika. Dalam kes kita, A2 mengandungi "A", jadi Isnumber kembali benar:

IF(TRUE, "Yes", "No")

Hasilnya, jika mengembalikan nilai yang ditetapkan untuk argumen value_if_true , iaitu "ya".

Excel jika atau pernyataan dengan kad liar

Perlu mengenal pasti sel -sel yang mengandungi salah satu rentetan teks wildcard? Dalam kes ini, anda boleh menggabungkan klasik jika atau pernyataan dengan formula carian countif atau isnumber yang dibincangkan di atas.

Sebagai contoh, untuk mencari "AA" atau "BB" dalam A2 yang mengabaikan kes surat dan kembali "ya" jika sama ada dijumpai, gunakan salah satu formula ini:

=IF(OR(ISNUMBER(SEARCH("aa", A2)), ISNUMBER(SEARCH("bb", A2))), "Yes", "")

atau

=IF(OR(COUNTIF(A2, "*aa*"), COUNTIF(A2, "*bb*")), "Yes", "")

Menambah dua fungsi Countif juga akan berfungsi. Dalam kes ini, tanda tambah berfungsi seperti atau pengendali:

=IF(COUNTIF(A3, "*aa*") COUNTIF(A3, "*bb*"), "Yes", "")

Daripada rentetan kad liar hardcoding dalam formula, anda boleh memasukkannya ke dalam sel berasingan, katakan D2 dan F2, seperti yang ditunjukkan dalam tangkapan skrin di bawah. Sila ambil perhatian bahawa rujukan sel ini dikunci dengan tanda $ supaya formula itu menyalin dengan betul ke sel di bawah:

=IF(OR(COUNTIF(A2, "*"&$D$2&"*"), COUNTIF(A2, "*"&$F$2&"*")), "Yes", "")

Formula di atas berfungsi dengan baik untuk 2 perlawanan separa, tetapi jika anda mencari 3 atau lebih, mereka akan menjadi terlalu panjang. Dalam kes ini, ia menjadi alasan untuk mendekati tugas dengan cara yang berbeza:

Membekalkan pelbagai substrings ke fungsi carian dalam pemalar array, hitung nombor yang dikembalikan, dan periksa sama ada hasilnya lebih besar daripada sifar (yang bermaksud bahawa sekurang -kurangnya satu daripada substring jika dijumpai):

=IF(COUNT(SEARCH({"aa","bb"}, A2))>0, "Yes", "")

Dengan cara ini, anda akan mendapat hasil yang sama dengan formula yang lebih padat:

Excel jika dan formula dengan kad liar

Apabila anda ingin menyemak sama ada sel mengandungi dua atau lebih substrings yang berbeza, cara paling mudah adalah menggunakan fungsi countifs dengan wildcard untuk ujian logik.

Katakan anda ingin mencari sel dalam lajur A yang mengandungi kedua -dua "B" dan "2". Untuk melakukannya, gunakan "*b*" dan "*2*" untuk kriteria countifs dan A2 untuk pelbagai kriteria:

=IF(COUNTIFS(A2, "*b*", A2, "*2*"), "Yes", "")

Cara lain ialah menggunakan IF dan formula bersama -sama dengan carian Isnumber:

=IF(AND(ISNUMBER(SEARCH("b", A2)), ISNUMBER(SEARCH("2", A2))), "Yes", "")

Walaupun kita tidak memasukkan sebarang watak wildcard dalam formula ini, ia berfungsi seperti mencari dua rentetan kad liar ("*b*" dan "*2*") dalam sel yang sama.

Sudah tentu, tidak ada yang menghalang anda daripada memasuki nilai carian dalam sel yang telah ditetapkan, D2 dan F2 dalam kes kami, dan membekalkan rujukan sel kepada formula:

=IF(AND(ISNUMBER(SEARCH($D$2, A2)), ISNUMBER(SEARCH($F$2, A2))), "Yes", "")

Jika anda lebih suka menggunakan formula yang lebih padat di mana mungkin, maka anda mungkin lebih baik seperti pendekatan berterusan array. Formula carian IF IF sangat seperti dalam contoh sebelumnya, tetapi kerana kali ini kedua -dua substring mesti muncul dalam A2, kita periksa sama ada kiraannya sama dengan 2:

=IF(COUNT(SEARCH({"b","2"}, A2))=2, "Yes", "")

Ini adalah kaedah utama menggunakan wildcard dalam jika pernyataan dalam Excel. Jika anda tahu sebarang penyelesaian lain, pengguna lain pasti akan menghargai jika anda berkongsi pengalaman anda dalam komen. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Excel jika contoh formula wildcard (fail .xlsx)

Atas ialah kandungan terperinci Excel Sekiranya Pernyataan untuk Perlawanan Teks Separa (Wildcard). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!