Apabila bekerja dengan data dan formula dalam Excel, anda pasti akan menghadapi ralat.
Untuk mengendalikan ralat, Excel menyediakan fungsi yang berguna - fungsi IFERROR.
Sebelum kita memahami mekanisme penggunaan fungsi IFERROR dalam Excel, mari kita fahami pelbagai jenis ralat yang mungkin anda hadapi semasa menggunakan formula.
Contents [show]
Jenis ralat dalam Excel
Memahami ralat dalam Excel akan membantu anda menentukan kemungkinan punca dan cara terbaik untuk menanganinya.
Berikut ialah jenis ralat yang mungkin anda temui dalam Excel.
#N/A ralat
Ini dipanggil ralat "nilai tidak tersedia".
Anda melihat ini apabila anda menggunakan formula carian dan nilainya tidak dijumpai (dan oleh itu tidak tersedia).
Di bawah ialah contoh di mana saya menggunakan formula VLOOKUP untuk mencari harga item, tetapi ia mengembalikan ralat apabila ia tidak dapat mencari item dalam tatasusunan jadual.
#DIV/0!Kesalahan
Anda mungkin melihat ralat ini apabila nombor dibahagikan dengan 0.
Ini dipanggil ralat pembahagian.Dalam contoh di bawah ia memberikan #DIV/0!Ralat kerana nilai kuantiti (pembahagi dalam formula) ialah 0.
#NILAI!Kesalahan
Ralat nilai berlaku apabila anda menggunakan jenis data yang salah dalam formula.
Sebagai contoh, dalam contoh di bawah, apabila saya cuba menambah sel dengan nombor dan aksara A, ia memberikan ralat nilai.
Ini berlaku kerana anda hanya boleh menambah nilai angka, tetapi saya cuba menambah nombor dengan aksara teks.
#REF! Kesalahan
Ini dipanggil ralat rujukan dan anda melihat ralat ini apabila rujukan dalam formula tidak lagi sah.Ini boleh berlaku apabila formula merujuk kepada rujukan sel dan rujukan sel itu tidak wujud (ia berlaku apabila anda memadamkan baris/lajur atau lembaran kerja yang dirujuk dalam formula).
Dalam contoh di bawah, walaupun formula asal ialah =A2/B2, apabila saya memadamkan lajur B, semua rujukan kepadanya menjadi #REF!Ia juga memberi #REF!ralat akibat daripada formula.
#NAME? ralat
Ralat ini mungkin disebabkan oleh salah ejaan fungsi.
Sebagai contoh, jika anda tersilap menggunakan VLOKUP dan bukannya VLOOKUP, anda akan mendapat ralat nama.
#NUM ralat
Ralat bilangan mungkin berlaku jika anda cuba mengira nilai yang sangat besar dalam Excel.Cth, = 187 ^ 549 akan mengembalikan ralat angka.
Satu lagi situasi di mana ralat NUM boleh berlaku ialah apabila anda membekalkan hujah angka yang tidak sah kepada formula.Sebagai contoh, jika anda mengira punca kuasa dua, ia akan mengembalikan ralat angka jika ia adalah nombor dan anda memberikan nombor negatif sebagai hujah.
Sebagai contoh, dalam kes fungsi punca kuasa dua, jika anda memberikan nombor negatif sebagai hujah, ia akan mengembalikan ralat berangka (ditunjukkan di bawah).
Walaupun saya hanya menunjukkan beberapa contoh di sini, mungkin terdapat banyak sebab lain untuk ralat dalam Excel.Apabila anda mendapat ralat dalam Excel, anda tidak boleh meninggalkannya di sana.Jika data digunakan selanjutnya dalam pengiraan, anda perlu memastikan bahawa ralat dikendalikan dengan cara yang betul.
Fungsi Excel IFERROR ialah cara terbaik untuk mengendalikan semua jenis ralat dalam Excel.
Fungsi Excel IFERROR - gambaran keseluruhan
Menggunakan fungsi IFERROR, anda boleh menentukan apa yang anda mahu formula kembalikan dan bukannya ralat.Jika formula tidak mengembalikan ralat, ia mengembalikan hasil sendiri.
Sintaks fungsi IFERROR
=IFERROR(nilai, nilai_jika_ralat)
Parameter input
- nilai - Ini ialah parameter untuk menyemak ralat.Dalam kebanyakan kes, ia sama ada formula atau rujukan sel.
- 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!.
Nota tambahan:
- Jika anda menggunakan "" sebagai parameter value_if_error, sel tidak memaparkan apa-apa apabila ralat berlaku.
- Jika nilai atau parameter value_if_error merujuk kepada sel kosong, fungsi Excel IFERROR menganggapnya sebagai nilai rentetan kosong.
- Jika argumen nilai ialah formula tatasusunan, IFERROR mengembalikan tatasusunan hasil untuk setiap item dalam julat yang ditentukan oleh nilai.
Fungsi Excel IFERROR - contoh
Di bawah ialah tiga contoh penggunaan fungsi IFERROR dalam Excel.
Contoh 1 - Mengembalikan sel kosong dan bukannya ralat
Jika anda mempunyai fungsi yang mungkin mengembalikan ralat, anda boleh membungkusnya dalam fungsi IFERROR dan nyatakan kosong sebagai nilai untuk dikembalikan sekiranya berlaku ralat.
Dalam contoh yang ditunjukkan di bawah, keputusan dalam D4 ialah #DIV/0!Ralat membahagi dengan 0.
Dalam kes ini, anda boleh menggunakan formula berikut untuk mengembalikan kosong dan bukannya ralat DIV yang hodoh.
=IFERROR(A1/A2,"")
Fungsi IFERROR ini akan menyemak sama ada pengiraan menghasilkan ralat.Jika ya, ia hanya mengembalikan tempat kosong yang dinyatakan dalam formula.
Di sini anda juga boleh menentukan sebarang rentetan atau formula lain untuk dipaparkan dan bukannya kosong.
Sebagai contoh, formula di bawah akan mengembalikan teks "Ralat" dan bukannya sel kosong.
=IFERROR(A1/A2,"Ralat")
Nota: Jika anda menggunakan Excel 2003 atau lebih awal, anda tidak akan menemui fungsi IFERROR di sana.Dalam kes ini, anda perlu menggunakan fungsi IF bersama-sama dengan fungsi ISERROR.
Contoh 2 - Mengembalikan "Tidak Ditemui" apabila VLOOKUP tidak dapat mencari nilai
Apabila anda menggunakan fungsi Excel VLOOKUP, ralat #N/A dikembalikan jika nilai carian tidak ditemui dalam julat yang ditentukan.
Sebagai contoh, di bawah ialah set data nama pelajar dan markah mereka.Saya menggunakan fungsi VLOOKUP untuk mendapatkan markah tiga orang pelajar (dalam D2, D3 dan D4).
Walaupun formula VLOOKUP dalam contoh di atas menemui dua nama pelajar pertama, ia tidak dapat mencari nama Josh dalam senarai, jadi ia mengembalikan ralat #N/A.
Di sini kita boleh menggunakan fungsi IFERROR untuk mengembalikan teks kosong atau beberapa teks yang bermakna dan bukannya ralat.
Di bawah ialah formula yang mengembalikan "Tidak Ditemui" dan bukannya ralat.
=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),"Tidak Ditemui")
Ambil perhatian bahawa anda juga boleh menggunakan IFNA dan bukannya IFERROR dalam VLOOKUP.Walaupun IFERROR mengendalikan semua jenis nilai ralat, IFNA hanya terpakai pada ralat #N/A, bukan ralat lain.
Contoh 3 - Kembalikan 0 apabila ralat
Jika anda tidak menyatakan nilai yang IFERROR pulangkan sekiranya berlaku ralat, ia akan mengembalikan 0 secara automatik.
Sebagai contoh, jika saya membahagikan 100 dengan 0 seperti yang ditunjukkan di bawah, ia akan mengembalikan ralat.
Walau bagaimanapun, jika saya menggunakan fungsi IFERROR di bawah, ia mengembalikan 0.Ambil perhatian bahawa anda masih perlu menggunakan koma selepas parameter pertama.
Contoh 4 - Menggunakan IFERROR Bersarang dengan VLOOKUP
Kadangkala apabila menggunakan VLOOKUP, anda mungkin perlu melihat jadual pemecahan untuk tatasusunan.Sebagai contoh, katakan anda mempunyai transaksi jualan dalam 2 lembaran kerja yang berasingan dan anda ingin mencari nombor item dan melihat nilainya.
Melakukannya memerlukan penggunaan IFERROR bersarang dengan VLOOKUP.
Katakan anda mempunyai set data yang kelihatan seperti ini:
Dalam kes ini, untuk mencari skor Grace, anda perlu menggunakan formula IFERROR bersarang berikut:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Formula bersarang ini memastikan anda mendapat nilai daripada sama ada jadual dan mengendalikan sebarang ralat yang dikembalikan.
Ambil perhatian bahawa jika helaian ini berada pada helaian yang sama, walau bagaimanapun, dalam kehidupan sebenar ia mungkin berada pada helaian yang berbeza.
