Excel

Sekiranya sel mengandungi salah satu daripada banyak perkara

If Cell Contains One Many Things

Formula Excel: Sekiranya sel mengandungi salah satu daripada banyak perkaraFormula generik
{= INDEX (results, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}
Ringkasan

Untuk menguji sel untuk salah satu daripada beberapa rentetan, dan mengembalikan hasil khusus untuk pertandingan pertama yang dijumpai, anda boleh menggunakan Formula INDEX / MATCH berdasarkan pada Fungsi CARIAN . Dalam contoh yang ditunjukkan, formula dalam C5 adalah:





apakah formula untuk sisihan piawai dalam excel
 
{= INDEX (results, MATCH (TRUE, ISNUMBER ( SEARCH (things,B5)),0))}

di mana perkara (E5: E8) dan hasil (F5: F8) adalah julat bernama .

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





Penjelasan

Formula ini menggunakan dua julat bernama : perkara , dan hasil . Sekiranya anda menggunakan formula ini secara langsung, pastikan untuk menggunakan julat bernama dengan nama yang sama (ditentukan berdasarkan data anda). Sekiranya anda tidak mahu menggunakan julat bernama, gunakan rujukan mutlak sebaliknya.

Inti formula ini adalah coretan ini:



 
 ISNUMBER ( SEARCH (things,B5)

Ini berdasarkan formula lain ( dijelaskan secara terperinci di sini ) yang memeriksa sel untuk satu substring. Sekiranya sel mengandungi substring, formula mengembalikan BENAR. Sekiranya tidak, formula mengembalikan PALSU.

Kerana kita memberi Fungsi CARIAN lebih daripada satu perkara yang perlu dicari, dalam julat yang dinamakan perkara , ini akan memberi kita lebih banyak satu hasil, dalam susunan yang kelihatan seperti ini:

 
{#VALUE!9#VALUE!#VALUE!}

Nombor mewakili padanan dalam perkara , kesalahan mewakili item yang tidak dijumpai.

Untuk mempermudah susunan, kami menggunakan Fungsi ISNUMBER untuk menukar semua item dalam larik menjadi BENAR atau SALAH. Sebarang nombor yang sah menjadi BENAR, dan sebarang kesalahan (iaitu perkara yang tidak dijumpai) menjadi SALAH. Hasilnya adalah susunan seperti ini:

 
{FALSETRUEFALSEFALSE}

yang masuk ke fungsi MATCH sebagai cari_array hujah, dengan a cari_nilai BENAR:

 
 MATCH (TRUE,{FALSETRUEFALSEFALSE},0) // returns 2

MATCH kemudian mengembalikan kedudukan TRUE pertama yang dijumpai, 2 dalam kes ini.

Akhirnya, kami menggunakan Fungsi INDEX untuk mendapatkan hasil dari julat yang dinamakan hasil pada kedudukan yang sama:

 
= INDEX (results,2) // returns 'found red'

Anda boleh menyesuaikan hasil berkisar dengan apa sahaja nilai yang masuk akal dalam kes penggunaan anda.

cara membuat buah ara sig di excel

Mencegah pertandingan palsu

Satu masalah dengan pendekatan ini dengan pendekatan PENCARIAN ISNUMBER + ialah anda mungkin mendapat padanan palsu dari separa padanan dengan kata-kata yang lebih panjang. Sebagai contoh, jika anda cuba memadankan 'dr', anda mungkin juga menemui 'Andrea', 'mabuk', 'tetes', dan lain-lain kerana 'dr' muncul di dalam kata-kata ini. Ini berlaku kerana SEARCH secara automatik melakukan pertandingan 'berisi-jenis'.

Untuk memperbaiki cepat, anda boleh membungkus kata carian dengan watak spasi (iaitu 'dr', atau 'dr') untuk mengelakkan mencari 'dr' dengan kata lain. Tetapi ini akan gagal sekiranya 'dr' muncul pertama atau terakhir di dalam sel.

Sekiranya anda memerlukan penyelesaian yang lebih mantap, satu pilihan adalah menormalkan teks pertama dalam a tiang penolong , dan tambahkan ruang utama dan belakang. Kemudian gunakan formula di halaman ini pada teks di lajur pembantu, bukan teks asalnya.

Pengarang Dave Bruns


^