Pada kesempatan kali ini, kita akan belajar bagaimana cara mencari error di Excel.
Saat menggunakan rumus di Excel, jumlah pesan error yang tampil berbanding lurus dengan pengetahuan dan kompleksitas rumus yang kita tulis, serta jumlah data.
Makin besar data, makin besar kemungkinan error tidak terlacak di dalam di dalam suatu worksheet atau workbook.
Karena itu penting melacak setiap pesan error untuk menentukan tindak lanjut yang perlu diambil.
Download file berikut sebagai bahan demo.
File: data-mencari-error-excel.xlsx.
Apa itu Pesan Error di Excel?
Pesan error adalah pesan yang tampil di cell sebagai akibat terjadinya kesalahan saat penggunaan rumus.
Excel mengenal beragam pesan error, masing-masing menginformasikan kondisi kesalahan tertentu. Sebagai contoh, #DIV/0! berarti ada rumus yang melakukan pembagian terhadap nilai nol.
Bila tidak ditangani dengan baik, pesan error berdampak negatif terhadap citra kita sebagai pengolah data. Kerjaan kita terkesan jorok dan tidak rapi.
Bagaimana Cara Mencari Error di Excel?
Ada beragam cara mencari pesan error di Excel. Cara termudah adalah menggunakan mata kita sendiri, melototin satu per satu cell di worksheet. Cara ini beresiko error luput dari pengawasan.
Cara lainnya adalah menggunakan salah satu atau gabungan dari beberapa cara berikut, yaitu:
- Fungsi penangkap error.
- Find and Replace.
- Conditional Formatting.
Kita akan membahas ketiganya di artikel ini.
Cara Mengatasi Error di Excel dengan Rumus
Cara mengatasi error di Excel dengan rumus adalah cara mengatasi error sebelum error tersebut terjadi.
Excel mengenal beberapa fungsi untuk tujuan ini. IFNA dan IFERROR adalah dua di antaranya yang bisa digunakan untuk menangkap error di Excel.
Keduanya akan mengevaluasi nilai atau rumus dan mengembalikan nilai yang diharapkan atau nilai alternatif bila terjadi kesalahan.
IFNA khusus digunakan untuk menangkap pesan kesalahan #N/A, yaitu absennya nilai yang dicari. Umum terjadi saat kita menggunakan fungsi XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, atau MATCH.
IFERROR digunakan untuk menangkap pesan kesalahan selain #N/A, seperti #CALC!, #DIV/0!, #NAME?, #NUM!, #NULL!, #REF!, #VALUE!, dan #SPILL!.
IFERROR bisa menangkap #N/A. Namun, IFERROR akan memperlakukan #N/A seperti pesan error lain yang bisa menyebabkan salah kaprah.
Ingat bahwa tidak semua rumus perlu penangkap error. Kita menggunakan penangkap error hanya bila rumus beresiko menghasilkan kesalahan.
Pada demo berikut, kita akan menghitung nominal komisi setiap penjual. Besaran komisi didapat dari tabel persentase komisi dengan cara bayar sebagai referensi. Bila cara bayar tidak ditemukan, tampilkan pesan Cara bayar tidak ada.
(Demo 01) Berikut contoh cara mengatasi error di Excel dengan fungsi penangkap error IFNA.
- Pada cell D2, awali rumus dengan
=IFNA(. - Lanjutkan dengan
VLOOKUP(C2; $F$2:$G$3; 2; FALSE)*B2, lalu;. IFNA akan mengevaluasi rumus ini dan mengembalikan nilai perhitungan bila tidak ditemukan error. - Ketikkan
"Cara bayar tidak ada". IFNA akan mengembalikan pesan teks ini saat perhitungan rumus VLOOKUP mengembalikan #N/A. - Akhiri dengan
). - Tekan Enter.
- Copykan rumus D2 ke semua cell di bawahnya sampai D6.
Pada gambar di atas, D2 menampilkan teks Cara bayar tidak ada karena cara bayar GI tidak ditemukan di tabel persentase pembayaran. Sementara itu, D3 dan D5 menampilkan perhitungan komisi karena masing-masing memiliki cara bayar terdaftar.
Bila kita hapus fungsi IFNA dari rumus Demo 01, kita akan mandapatkan D2, D4, dan D6 masing-masing bernilai #N/A.
Cara Mencari Error di Excel dengan Find and Replace
Bila karena suatu sebab kita tidak menggunakan fungsi penangkap error dan tidak mau menambahkannya kemudian (karena mengubah rumus beresiko mengundang error yang tidak diinginkan), gunakan Find and Replace.
Find and Replace bisa membantu menemukan error, baik dalam worksheet tertentu atau pada skala workbook, dalam satu pencarian.
(Demo 02) Berikut cara mencari error di Excel menggunakan Find and Replace.
- Buka file Excel di mana error yang ingin dicari berada.
- Klik perintah Find (Shortcut: Alt,H,FD,F atau Ctrl+F) di tab Home → Kelompok perintah Editing → Find & Select.
- Klik tab Find.
- Pada Find what, masukkan #N/A atau pesan error lain yang ingin dicari.
- Klik Option >> untuk melihat pengaturan-pengaturan lainnya.
- Pada Within, pilih Workbook. Bila ingin mencari hanya di worksheet aktif, pilih Worksheet.
- Pada Look in, pastikan Values berada pada kondisi terpilih. Pilihan Values berarti kita hanya ingin mencari nilai, terlepas pada proses dari mana nilai tersebut berasal.
- Klik Find All.
Setelah mengklik Find All, Excel akan menampilkan pesan-pesan error dan lokasinya masing-masing.
Cukup klik salah satu hasil pencarian dan Excel akan mengaktifkan cell di mana pesan error berada.
Perhatikan cell A13 pada Gambar 02. Nilai Tidak ada adalah hasil dari penggunaan rumus IFNA.
Pencarian yang kita lakukan dengan Find and Replace hanya akan menampilkan cell-cell dengan nilai error. Cell dengan nilai error yang sudah ditangkap dengan fungsi seperti IFNA atau IFERROR tidak akan muncul di hasil pencarian.
Cara Mencari Error di Excel dengan Conditional Formatting
Conditional formatting adalah fitur Excel untuk mencari dan menandai cell-cell dengan nilai tertentu.
Dibanding Find and Replace, kita tidak perlu menuliskan error yang ingin dicari dan ditandai.
Kekurangannya, conditional formatting hanya mencari error di lokasi range dan worksheet tertentu.
(Demo 03) Berikut cara mencari error di Excel dengan conditional formatting.
- Pilih range di mana error ingin dicari.
- Pada tab Home → Kelompok perintah Styles → Conditional Formatting, pilih perintah New Rule (Shortcut: Alt,H,L,N) untuk menampilkan kotak dialog New Formatting Rule.
- Pada Select a Rule Type, pilih Format only cells that contain.
- Pada Format only cells with, pilih Errors.
- Klik tombol Format untuk memberikan format khusus pada cell-cell dengan nilai error.
- Klik OK.
Lihat gambar di bawah untuk hasilnya.
Seperti mencari error dengan Find and Replace, conditional formatting hanya akan menandai cell dengan nilai error. Fitur ini tidak akan menandai cell dengan error yang sudah ditangkap oleh fungsi seperti IFNA atau IFERROR.
Tips: Bila ingin mencari semua nilai error di dalam suatu worksheet, klik tombol Select All yang terletak di bagian kiri atas worksheet (tepat di atas kepala Baris 1 dan di kiri Kolom A).
Penutup
Semua demo di atas bisa dilihat lebih jelas melalui file xlsx berikut.