Cara menggunakan fungsi IFNA dalam Excel dengan contoh

Mendapatkan banyak kesilapan #N/A dalam lembaran kerja anda dan ingin tahu jika ada cara untuk memaparkan teks tersuai? Formula IFNA adalah penyelesaian yang anda perlukan.

Apabila formula Excel tidak dapat mengenal pasti atau mencari sesuatu, ia melemparkan ralat #N/A. Untuk menangkap ralat sedemikian dan menggantikannya dengan mesej mesra pengguna, anda boleh menggunakan fungsi IFNA. Dalam erti kata lain, #N/A adalah cara Excel untuk mengatakan bahawa nilai yang anda cari tidak hadir dalam dataset yang dirujuk. IFNA adalah cara anda untuk memerangkap dan mengendalikan kesilapan itu.

• Apa itu Ifna dalam Excel?
• Cara menggunakan ifna di excel
• Cara menggunakan ifna dengan vlookup
• Ifna dalam formula array
• Ifna vs Iferror

Ifna berfungsi dalam Excel

Fungsi Excel IFNA bertujuan untuk menangkap dan mengendalikan kesilapan #N/A. Jika formula menilai ke #N/A, IFNA memerangkap ralat itu dan menggantikannya dengan nilai tersuai yang anda tentukan; Jika tidak, hasil yang normal dari formula.

Sintaks IFNA

Sintaks fungsi IFNA adalah seperti berikut:

Ifna (nilai, nilai_if_na)

Di mana:

Nilai (diperlukan) - Formula, nilai, atau rujukan untuk memeriksa ralat #N/A.

Value_if_na (diperlukan) - Nilai untuk kembali jika ralat #N/A dikesan.

Nota penggunaan

• Fungsi IFNA hanya mengendalikan #N/A tanpa menekan sebarang kesilapan lain.
• Jika argumen nilai adalah formula array , IFNA mengembalikan pelbagai hasil, satu setiap sel, seperti yang ditunjukkan dalam contoh ini.

Ketersediaan IFNA

Fungsi IFNA diperkenalkan di Excel 2013 dan boleh didapati dalam semua versi berikutnya termasuk Excel 2016, Excel 2019, Excel 2021, dan Microsoft 365.

Dalam versi terdahulu, anda boleh menangkap kesilapan #N/A dengan menggunakan fungsi IF dan ISNA bersama -sama.

Cara menggunakan fungsi ifna dalam excel

Untuk menggunakan IFNA dengan berkesan, ikuti pendekatan generik ini:

1. Dalam hujah pertama ( nilai ), letakkan formula yang dipengaruhi oleh ralat #N/A.
2. Dalam hujah kedua ( value_if_na ), taipkan teks yang anda mahu kembali dan bukannya notasi ralat standard. Untuk mengembalikan sel kosong apabila tiada apa yang dijumpai, membekalkan rentetan kosong ('"").

Untuk mengembalikan teks tersuai , formula generik adalah:

Ifna ( formula (), " teks tersuai ")

Untuk mengembalikan sel kosong , formula generik adalah:

Ifna ( formula (), "")

Mari lihat bagaimana ia berfungsi dengan contoh mudah. Dalam jadual di bawah, katakan anda ingin tahu bagaimana skor pelajar yang diberikan antara lain. Oleh kerana data disusun oleh lajur skor dari tertinggi hingga terendah, pangkat akan sepadan dengan kedudukan relatif pelajar dalam jadual. Dan untuk mendapatkan kedudukan, anda boleh menggunakan fungsi perlawanan dalam bentuk yang paling mudah:

=MATCH(E1, A2:A10, 0)

Kerana nilai carian (NEAL) tidak tersedia dalam array carian (A2: A10), ralat #N/A berlaku.

Berlari ke dalam kesilapan ini, pengguna yang tidak berpengalaman mungkin berfikir ada sesuatu yang salah dengan formula, dan anda sebagai pencipta buku kerja akan menerima banyak soalan. Untuk mengelakkan ini, anda boleh secara jelas menunjukkan bahawa formula itu betul, ia tidak dapat mencari nilai yang diminta untuk dicari. Oleh itu, anda sarang formula perlawanan dalam hujah pertama IFNA dan, dalam hujah kedua, taipkan teks tersuai anda, "tidak dijumpai" dalam kes kami:

=IFNA(MATCH(E1, A2:A10, 0), "Not found")

Sekarang, bukannya notasi ralat standard, teks anda sendiri dipaparkan dalam sel, memaklumkan pengguna bahawa nilai carian tidak hadir dalam dataset:

Cara menggunakan ifna dengan vlookup

Selalunya kesilapan #N/A berlaku dalam fungsi yang mencari sesuatu seperti VLOOKUP, HLOOKUP, LOOKUP, dan POLT. Contoh di bawah meliputi beberapa kes penggunaan biasa.

Contoh 1. Asas IFNA VLOOKUP Formula

Untuk memerangkap #N/A kesilapan yang berlaku apabila VLOOKUP tidak dapat mencari perlawanan, periksa hasilnya menggunakan IFNA dan tentukan nilai yang akan dipaparkan dan bukannya ralat. Amalan biasa adalah untuk membungkus fungsi IFNA di sekitar formula VLOOKUP sedia ada anda menggunakan sintaks ini:

Ifna (vlookup (), " teks anda ")

Dalam jadual sampel kami, katakan anda ingin mengambil skor pelajar tertentu (E1). Untuk ini, anda menggunakan formula VLOOKUP klasik ini:

=VLOOKUP(E1, A2:B10, 2, FALSE)

Isu ini adalah bahawa Neal tidak mengambil peperiksaan, oleh itu namanya tidak dalam senarai, dan jelas VLOOKUP gagal mencari perlawanan.

Untuk menyembunyikan kesilapan, kami membungkus vlookup dalam IFNA seperti ini:

=IFNA(VLOOKUP(E1, A2:B10, 2, FALSE), "Did not take the exam")

Sekarang, hasilnya tidak kelihatan begitu menakutkan kepada pengguna dan lebih banyak maklumat:

Contoh 2. IFNA VLOOKUP untuk mencari beberapa helaian

Fungsi IFNA juga berguna untuk melaksanakan carian berurutan atau rantaian yang dipanggil di pelbagai helaian atau buku kerja yang berbeza. Ideanya ialah anda bersarang beberapa formula IFNA (VLOOKUP (...)) yang lain dengan cara yang lain dengan cara ini:

Ifna (vlookup (...), ifna (vlookup (...), ifna (vlookup (...), "tidak dijumpai")))

Sekiranya vlookup utama tidak menemui apa -apa, fungsi IFNA berjalan seterusnya VLOOKUP seterusnya sehingga nilai yang dikehendaki dijumpai. Jika semua carian gagal, formula akan mengembalikan teks yang ditentukan.

Katakan anda mempunyai banyak kelas yang disenaraikan dalam lembaran yang berbeza (bernama Kelas A , Kelas B , dan Kelas C ). Matlamat anda adalah untuk mendapatkan skor pelajar tertentu, yang namanya input dalam sel B1 dalam lembaran kerja semasa anda. Untuk memenuhi tugas, gunakan formula ini:

=IFNA(VLOOKUP(B1, 'Class A'!A2:B5, 2, FALSE), IFNA(VLOOKUP(B1, 'Class B'!A2:B5, 2, FALSE), IFNA(VLOOKUP(B1, 'Class C'!A2:B5, 2, FALSE), "Not found")))

Formula ini secara berurutan melihat nama yang ditentukan dalam tiga helaian yang berbeza dalam perintah VLOOKUP bersarang dan membawa perlawanan pertama yang dijumpai:

Contoh 3. Ifna dengan perlawanan indeks

Dalam cara yang sama, IFNA dapat menangkap kesilapan #N/A yang dihasilkan oleh fungsi carian lain. Sebagai contoh, mari kita gunakannya bersama -sama dengan formula perlawanan indeks:

=IFNA(INDEX(B2:B10, MATCH(E1, A2:A10, 0)), "Not found")

Intipati formula adalah sama seperti dalam semua contoh sebelumnya - Perlawanan Indeks melakukan carian, dan IFNA menilai hasilnya dan menangkap ralat #N/A jika nilai rujukan tidak dijumpai.

Ifna mengembalikan hasil berganda

Sekiranya fungsi dalaman (iaitu formula yang diletakkan dalam argumen nilai ) mengembalikan pelbagai nilai, IFNA akan menguji setiap nilai yang dikembalikan secara individu dan mengeluarkan pelbagai hasil. Contohnya:

=IFNA(VLOOKUP(D2:D4, A2:B10, 2, FALSE), "Not found")

Dalam array dinamik Excel (Microsoft 365 dan Excel 2021), formula biasa dalam sel paling atas (E2) menumpahkan semua hasil dalam sel -sel jiran secara automatik (dari segi Excel, ia dipanggil pelbagai tumpahan).

Dalam versi pra-dinamik (Excel 2019 dan lebih rendah), kesan yang sama dapat dicapai dengan menggunakan formula pelbagai sel, yang disiapkan dengan peralihan CTRL Enter Pintasan.

Apakah perbezaan antara Ifna dan Iferror?

Bergantung pada punca utama masalah, formula Excel boleh mencetuskan kesilapan yang berbeza seperti #N/A, #Name, #Value, #Ref, #Div/0, #Num, dan lain -lain. Fungsi IFError menangkap semua kesilapan itu manakala IFNA hanya terhad kepada #n/a. Mana yang lebih baik untuk dipilih? Itu bergantung pada keadaan.

Jika anda ingin menindas apa -apa jenis ralat , maka gunakan fungsi IFError. Ia amat berguna dalam pengiraan kompleks apabila formula termasuk beberapa fungsi yang boleh menghasilkan kesilapan yang berbeza.

Dengan fungsi carian , anda lebih baik menggunakan IFNA kerana ia memaparkan hasil tersuai hanya apabila nilai carian tidak dijumpai dan tidak menyembunyikan masalah yang mendasari dengan formula itu sendiri.

Untuk menggambarkan perbezaannya, mari kita bawa kembali formula asas IFNA VLOOKUP kami dan "secara tidak sengaja" salah ejaan nama fungsi (vlokup bukan vlookup).

=IFNA(VLOKUP(E1, A2:B10, 2, FALSE), "Did not take the exam")

IFNA tidak menindas kesilapan ini, jadi anda dapat melihat dengan jelas bahawa sesuatu yang salah dengan salah satu nama fungsi:

Sekarang, mari kita lihat apa yang berlaku jika anda menggunakan ifeRror:

=IFERROR(VLOKUP(E1, A2:B10, 2, FALSE), "Did not take the exam")

Hmm ... ia mengatakan bahawa Olivia tidak mengambil peperiksaan, yang tidak benar! Ini kerana fungsi iferror memerangkap #name? Ralat dan mengembalikan teks tersuai. Dalam keadaan ini, ia bukan sahaja mengembalikan maklumat yang salah tetapi juga mengaburkan masalah dengan formula.

Itulah cara menggunakan formula IFNA dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun yang ada

Contoh Formula Excel IFNA (fail .xlsx)

Atas ialah kandungan terperinci Cara menggunakan fungsi IFNA dalam Excel dengan contoh. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!