Apabila menggunakan formula VLOOKUP dalam Excel, kadangkala anda mungkin menghadapi ralat #N/A yang jelek.Ini berlaku apabila formula anda tidak dapat mencari nilai carian.
Dalam tutorial ini, saya akan menunjukkan kepada anda cara berbeza untuk menggunakan IFERROR dan VLOOKUP untuk mengendalikan ralat #N/A ini dalam lembaran kerja.
Gunakan IFERROR dalam kombinasi dengan VLOOKUP untuk memaparkan sesuatu yang bermakna sebagai ganti ralat #N/A (atau sebarang ralat lain).
Sebelum kita masuk ke butiran cara menggunakan gabungan ini, mari kita lihat fungsi IFERROR untuk melihat cara ia berfungsi.
Contents [show]
- 0.1 Penerangan fungsi IFERROR
- 0.2 Sebab yang mungkin untuk VLOOKUP mengembalikan ralat #N/A
- 0.3 Gantikan ralat VLOOKUP #N/A dengan teks yang bermakna
- 0.4 VLOOKUP bersarang dengan fungsi IFERROR
- 0.5 Menggunakan VLOOKUP dengan IF dan ISERROR (pra-Excel 2007)
- 0.6 IFERROR dan IFNA
- 1 Oh hai, selamat berkenalan.
Penerangan fungsi IFERROR
Menggunakan fungsi IFERROR, anda boleh menentukan perkara yang harus berlaku apabila formula atau rujukan sel mengembalikan ralat.
Ini ialah sintaks fungsi IFERROR.
=IFERROR(nilai, nilai_jika_ralat)
- nilai - Ini ialah parameter untuk menyemak ralat.Dalam kebanyakan kes, ia sama ada formula atau rujukan sel.Apabila menggunakan VLOOKUP dengan IFERROR, formula VLOOKUP akan menjadi parameter ini.
- nilai_jika_ralat – Ini ialah nilai yang dikembalikan apabila ralat berlaku.Jenis ralat berikut telah dinilai: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, dan #NULL!.
Sebab yang mungkin untuk VLOOKUP mengembalikan ralat #N/A
Fungsi VLOOKUP mungkin mengembalikan ralat #N/A untuk mana-mana sebab berikut:
- Nilai carian tidak ditemui dalam tatasusunan carian.
- Terdapat ruang hadapan, belakang atau berganda dalam nilai carian (atau tatasusunan jadual).
- Terdapat kesilapan taip dalam nilai carian atau nilai carian dalam tatasusunan.
Anda boleh menggunakan IFERROR bersama-sama dengan VLOOKUP untuk mengendalikan semua punca ralat ini.Walau bagaimanapun, anda harus mengambil kira sebab #2 dan #3, dan membetulkannya dalam data sumber, dan bukannya membiarkan IFERROR mengendalikannya.
Nota: IFERROR akan mengendalikan ralat tanpa mengira apa yang menyebabkannya.Jika anda hanya mahu mengendalikan ralat yang disebabkan oleh VLOOKUP tidak dapat mencari nilai carian, gunakan IFNA sebaliknya.Ini akan memastikan ralat selain #N/A tidak dikendalikan dan anda boleh menyiasat ralat lain ini.
Anda boleh menggunakan fungsi TRIM untuk mengendalikan ruang hadapan, mengekor dan berganda.
Gantikan ralat VLOOKUP #N/A dengan teks yang bermakna
Katakan anda mempunyai set data yang kelihatan seperti ini:
Seperti yang anda lihat, formula VLOOKUP mengembalikan ralat kerana nilai carian tiada dalam senarai.Kami sedang mencari markah Glen, ia tiada dalam jadual skor.
Walaupun ini adalah set data yang sangat kecil, anda mungkin mempunyai set data yang besar di mana anda perlu menyemak kejadian banyak item.Anda akan mendapat ralat #N/A untuk setiap kes yang nilainya tidak ditemui.
Ini adalah formula yang anda boleh gunakan untuk mendapatkan sesuatu yang bermakna dan bukan #N/A salah.
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,0),"Tidak Ditemui")
Formula di atas mengembalikan teks "Tidak Ditemui" dan bukannya ralat #N/A.Anda juga boleh menggunakan formula yang sama untuk mengembalikan kosong, sifar atau mana-mana teks lain yang bermakna.
VLOOKUP bersarang dengan fungsi IFERROR
Jika anda menggunakan VLOOKUP dan jadual carian anda tersebar merentasi lembaran kerja yang sama atau lembaran kerja yang berbeza, anda perlu menyemak nilai VLOOKUP melalui semua jadual ini.
Sebagai contoh, dalam set data yang ditunjukkan di bawah, terdapat dua jadual berasingan nama dan markah pelajar.
Jika saya perlu mencari skor Grace dalam set data ini, saya perlu menggunakan fungsi VLOOKUP untuk menyemak jadual pertama, dan jika nilai tidak ditemui di dalamnya, semak jadual kedua.
Berikut ialah formula IFERROR bersarang yang boleh saya gunakan untuk mencari nilai:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Menggunakan VLOOKUP dengan IF dan ISERROR (pra-Excel 2007)
Fungsi IFERROR telah diperkenalkan dalam Excel 2007 untuk Windows dan Excel 2016 dalam Mac.
Jika anda menggunakan versi sebelumnya, fungsi IFERROR tidak akan berfungsi pada sistem anda.
Anda boleh meniru fungsi fungsi IFERROR dengan menggabungkan fungsi IF dengan fungsi ISERROR.
Biar saya cepat menunjukkan kepada anda cara menggunakan gabungan IF dan ISERROR dan bukannya IFERROR.
Dalam contoh di atas, bukannya menggunakan IFERROR, anda juga boleh menggunakan formula yang ditunjukkan dalam sel B3:
=IF(ISERROR(A3),"Tidak Ditemui",A3)
Bahagian ISERROR formula menyemak ralat (termasuk ralat #N/A) dan mengembalikan BENAR jika ralat ditemui, FALSE sebaliknya.
- Jika BENAR (menunjukkan ralat), fungsi IF akan mengembalikan nilai yang ditentukan ("Tidak Ditemui" dalam kes ini).
- Jika FALSE (yang bermaksud tiada ralat), fungsi IF akan mengembalikan nilai ini (A3 dalam contoh di atas).
IFERROR dan IFNA
IFERROR mengendalikan semua jenis ralat, manakala IFNA hanya mengendalikan ralat #N/A.
Apabila menangani ralat yang disebabkan oleh VLOOKUP, anda perlu memastikan anda menggunakan formula yang betul.
Apabila anda ingin menangani pelbagai kesilapan, silaGunakan IFERROR.Ralat kini boleh disebabkan oleh pelbagai faktor (seperti formula yang salah, julat nama yang salah eja, nilai carian tidak ditemui dan nilai salah yang dikembalikan daripada jadual carian).IFERROR tidak penting, ia menggantikan semua ralat ini dengan nilai yang ditentukan.
Gunakan IFNA apabila anda hanya mahu menangani ralat #N/A, yang berkemungkinan besar disebabkan oleh formula VLOOKUP tidak dapat mencari nilai carian.
