Mencegah kesalahan rumus di Excel menggunakan IFERROR atau IFNA. Ini lebih baik daripada ISERROR ISERR dan ISNA yang lama. Pelajari lebih lanjut di sini.
Kesalahan rumus bisa jadi umum. Jika Anda memiliki kumpulan data dengan ratusan rekaman, divide-by-Zero atau # N / A pasti akan muncul sekarang dan nanti.
Di masa lalu, mencegah kesalahan membutuhkan upaya yang sangat besar. Angguk kepala Anda dengan sadar jika Anda pernah pingsan =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Selain sangat lama untuk mengetik, solusi itu membutuhkan Excel untuk melakukan VLOOKUP dua kali lebih banyak. Pertama, Anda melakukan VLOOKUP untuk melihat apakah VLOOKUP akan menghasilkan kesalahan. Kemudian Anda melakukan VLOOKUP yang sama lagi untuk mendapatkan hasil non-error.
Excel 2010 memperkenalkan IFERROR (Rumus, Nilai Jika Kesalahan) yang sangat ditingkatkan. Saya tahu bahwa IFERROR terdengar seperti fungsi ISERROR, ISERR, ISNA yang lama, tetapi ini sama sekali berbeda.
Ini adalah fungsi brilian: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Jika Anda memiliki 1.000 VLOOKUP dan hanya 5 pengembalian # N / A, maka 995 yang berfungsi hanya memerlukan satu VLOOKUP. Hanya 5 yang mengembalikan # N / A yang perlu beralih ke argumen kedua IFERROR.
Anehnya, Excel 2013 menambahkan fungsi IFNA (). Ini seperti IFERROR tetapi hanya mencari kesalahan # N / A. Orang mungkin membayangkan situasi aneh di mana nilai dalam tabel pencarian ditemukan, tetapi jawaban yang dihasilkan adalah pembagian dengan 0. Jika Anda ingin mempertahankan kesalahan bagi-dengan-nol karena suatu alasan, maka IFNA () akan melakukan ini.
Tentu saja, orang yang membuat tabel pemeta harus menggunakan IFERROR untuk mencegah pembagian dengan nol sejak awal. Pada gambar di bawah, "nm" adalah kode mantan manajer untuk "tidak bermakna".
Terima kasih kepada Justin Fishman, Stephen Gilmer, dan Excel oleh Joe.
Menonton video
- Di masa lalu, Anda akan menggunakan
=IF(ISNA(Formula),0,Formula) - Mulai di Excel 2010,
=IFERROR(Formula,0) - Tetapi IFERROR memperlakukan kesalahan DIV / 0 sama seperti # N / A! kesalahan
- Mulai di Excel 2013, gunakan
=IFNA(Formula,0)untuk mendeteksi kesalahan # N / A saja - Terima kasih kepada Justin Fishman, Stephen Gilmer, dan Excel oleh Joe.
Transkrip Video
Pelajari Excel dari podcast, episode 2042 - IFERROR dan IFNA!
Saya akan membuat podcasting semua tip saya dari buku ini, klik "i" di pojok kanan atas untuk melihat seluruh playlist!
Baiklah, mari kita kembali ke masa lalu, Excel 2007 atau sebelumnya, jika Anda perlu mencegah kesalahan # N / A! dari formula VLOOKUP seperti ini, kami biasa melakukan hal gila ini. Sendok semua karakter campuran VLOOKUP kecuali untuk =, Ctrl + C untuk meletakkannya di clipboard, = IF (ISNA (, tekan tombol Putus untuk sampai ke akhir, jika # N / A !, maka kita katakan "Tidak Ditemukan", koma, jika tidak kita lakukan VLOOKUP! Benar, saya tempel di sana, dan lihat berapa panjang rumus itu, Ctrl + Enter, tambahkan a) di sana, Ctrl + Enter, baiklah lihat, itu manis. Baiklah, tapi masalahnya adalah, saat menyelesaikan masalah # N / A! Setiap formula melakukan VOOKUP dua kali. Ia tidak ingin kita mengatakan "Hei, apakah ini kesalahan? Oh, tidak, tidak kesalahan. Ayo lakukan lagi! ”Jadi dibutuhkan waktu dua kali lebih lama untuk melakukan semua VLOOKUP ini. Di Excel 2010,mereka memberi kami formula IFERROR yang mengagumkan dan mengagumkan!
Sekarang saya tahu kedengarannya seperti yang kita miliki sebelumnya, ISERROR atau ISERR, tapi ini IFERROR. Dan cara kerjanya, ambil rumus apa pun yang mungkin mengembalikan kesalahan, lalu Anda mengatakan = IFERROR, ada rumusnya, koma, apa yang harus dilakukan jika itu kesalahan, jadi “Tidak Ditemukan”. Baiklah sekarang, hal yang indah tentang ini adalah, katakanlah Anda memiliki seribu VLOOKUP yang harus dilakukan, dan 980 di antaranya berfungsi. Untuk 980 itu hanya melakukan VLOOKUP, itu bukan kesalahan, ia mengembalikan jawabannya, tidak pernah melanjutkan untuk melakukan VLOOKUP kedua, itulah keindahan IFERROR. IFERROR Excel hadir di Excel 2010, tapi tentu saja, satu masalah yang sangat bodoh di sini adalah tabel itu sendiri mengembalikan kesalahan. Benar, seseorang memiliki 0 kuantitas, Pendapatan / Kuantitas, jadi kami mendapatkan # DIV / 0! kesalahan di sana, dan kesalahan itu juga akan dideteksi sebagai kesalahan oleh IFERROR. Baik,dan itu akan terlihat seperti tidak ditemukan, dia ditemukan, hanya saja siapa pun yang membangun meja ini tidak melakukan pekerjaan yang baik dalam membangun meja ini.
Baiklah, pilihan # 1, Excel 2013 atau yang lebih baru, alihkan ke fungsi yang mereka tambahkan di 2013 yang tidak mencari semua kesalahan, t hanya mencari # N / A! Ctrl + Enter, dan sekarang kita akan mendapatkan Not Found untuk ExcelIsFun, tetapi mengembalikan # DIV / 0! kesalahan. Sungguh, apa yang harus kita lakukan, di sini dalam rumus ini, kita harus menangani rumus ini untuk mencegah pembagian dengan nol di tempat pertama. Jadi = IFERROR, ini berfungsi untuk semua jenis hal, tekan tombol Putus untuk mengakhiri, koma, lalu apa yang harus dilakukan? Saya akan selalu menempatkan nol di sini sebagai harga rata-rata.
Saya pernah memiliki manajer, Susanna (?), “Itu tidak benar secara matematis, Anda harus memasukkan“ nm ”agar tidak bermakna.” Persis seperti itu, sungguh gila berapa lama manajer saya membuat saya gila dengan permintaan gila mereka, jadi, mari kita salin, Tempel Rumus Khusus, alt = "" ES F. Sekarang kita mendapatkan kesalahan "tidak berarti" di sini, " Tidak Ditemukan "ditemukan oleh IFERROR, dan" tidak bermakna "sebenarnya adalah hasil dari VLOOKUP. Baiklah, jadi ada beberapa cara untuk melakukannya, mungkin hal yang aman untuk dilakukan di sini adalah menggunakan IFNA, asalkan Anda memiliki Excel 2013, dan setiap orang yang Anda ajak berbagi buku kerja memiliki Excel 2013. Buku ini, ditambah banyak lagi lainnya Ada di dalam buku ini, penuh dengan tip pedas, 200 halaman, mudah dibaca, penuh warna mengagumkan, buku mengagumkan. Lihat ini, klik "Saya" di pojok kanan atas,Anda bisa membeli bukunya.
Baiklah, rekap episode: Dulu kami akan menggunakan format panjang = IF (ISNA (rumusnya, 0, jika tidak rumusnya, rumusnya dihitung dua kali, yang mengerikan untuk VLOOKUP. Mulai di Excel 2010, = IFERROR , cukup letakkan rumusnya sekali, koma, apa yang harus dilakukan jika itu adalah kesalahan. Namun, IFERROR memperlakukan kesalahan # DIV / 0! sama dengan kesalahan # N / A !, jadi mulai Excel 2013 fungsi IFNA berfungsi seperti IFERROR, tetapi itu hanya akan mendeteksi kesalahan # N / A !.
Tip ini, disarankan oleh pembaca Justin Fishman, Stephen Gilmer, dan Excel oleh Joe. Terima kasih kepada mereka karena telah menyarankan ini, dan terima kasih kepada Anda karena telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2042.xlsm
