Mendeteksi kesalahan dalam rumus

Tabel berikut ini merangkum beberapa kesalahan paling umum yang mungkin dilakukan pengguna saat memasukkan rumus, dan menjelaskan cara mengoreksinya:

Pastikan bahwa Anda	Informasi selengkapnya
Mengawali tiap fungsi dengan tanda sama dengan (=)	Jika Anda menghilangkan tanda sama dengan, apa yang Anda ketikkan bisa ditampilkan sebagai teks atau sebagai tanggal. Misalnya, jika mengetik SUM(A1:A10), Excel menampilkan string teks SUM(A1:A10) dan tidak melakukan penghitungan. Jika mengetik 2/11, Excel menampilkan tanggal 2-Nov (berasumsi bahwa format sel adalah Umum), bukannya membagi 2 dengan 11.
Mencocokkan semua tanda kurung buka dan tutup	Pastikan bahwa semua tanda kurung adalah bagian dari pasangan yang cocok (buka dan tutup). Saat Anda menggunakan fungsi dalam rumus, penting bagi setiap tanda kurung berada dalam posisi yang benar agar fungsi berfungsi dengan benar. Misalnya, rumus =IF(B5<0),"Tidak valid",B5*1.05) tidak akan berfungsi karena ada dua kurung tutup dan hanya ada satu kurung buka, ketika seharusnya setiap kurung cukup ada satu. Rumus yang benar terlihat seperti ini: =IF(B5<0,"Tidak valid",B5*1.05).
Menggunakan titik dua untuk mengindikasikan rentang	Saat Anda merujuk ke rentang sel, gunakan tanda titik dua (:) untuk memisahkan referensi ke sel pertama di dalam rentang dan referensi ke sel terakhir di dalam rentang. Misalnya, =SUM(A1:A5), bukan =SUM(A1 A5), yang akan mengembalikan #NULL! Kesalahan.
Memasukkan semua argumen yang diperlukan	Beberapa fungsi memerlukan argumen. Selain itu, pastikan bahwa Anda tidak memasukkan terlalu banyak argumen.
Memasukkan tipe argumen yang tepat	Beberapa fungsi, seperti SUM, memerlukan argumen numerik. Fungsi lain, seperti REPLACE, memerlukan nilai teks untuk sedikitnya satu argumen. Jika Anda menggunakan tipe data yang salah sebagai argumen, Excel bisa mengembalikan hasil yang tidak diharapkan atau menampilkan kesalahan.
Menumpuk tidak lebih dari 64 fungsi	Anda bisa memasukkan, atau menumpuk, tidak lebih dari 64 tingkat fungsi dalam sebuah fungsi.
Mengapit nama lembar lainnya dalam tanda kutip tunggal	Jika rumus merujuk pada nilai atau sel di lembar kerja atau buku kerja lainnya, dan nama buku kerja atau lembar kerja lain tersebut berisi spasi atau karakter non-alfabet, Anda harus mengapit namanya di dalam tanda kutip tunggal ( ' ), seperti ='Data Kuartal'!D3, atau =‘123’!A1.
Meletakkan tanda seru (!) setelah nama lembar kerja saat Anda merujuknya dalam rumus	Misalnya, untuk mengembalikan nilai dari sel D3 dalam lembar kerja bernama Quarterly Data dalam buku kerja yang sama, gunakan rumus ini: ='Quarterly Data'!D3.
Menyertakan jalur ke buku kerja eksternal	Pastikan bahwa setiap referensi eksternal berisi nama buku kerja dan jalur ke buku kerja tersebut. Referensi ke buku kerja mencakup nama buku kerja dan harus diapit dalam kurung siku ([Namabukukerja.xlsx]). Referensi juga harus berisi nama lembar kerja dalam buku kerja. Jika buku kerja yang ingin dirujuk tidak dibuka di Excel, Anda masih dapat menyertakan referensi ke buku kerja tersebut dalam sebuah rumus. Sediakan jalur lengkap ke file tersebut, seperti dalam contoh berikut: =ROWS('C:\My Documents\[Q2 Operasi.xlsx]Penjualan'!A1:A8). Rumus ini mengembalikan jumlah baris dalam rentang yang berisi sel A1 hingga A8 di buku kerja lainnya (8). Catatan: Jika jalur lengkap berisi karakter spasi, seperti di contoh sebelumnya, Anda harus mengapit jalur tersebut dalam tanda kutip tunggal (di permulaan jalur dan setelah nama lembar kerja, sebelum tanda seru).
Memasukkan angka tanpa pemformatan	Jangan memformat angka saat Anda memasukkannya dalam rumus. Misalnya, jika nilai yang ingin dimasukkan adalah $1.000, masukkan 1000 dalam rumus. Jika memasukkan koma sebagai bagian dari angka, Excel akan mengartikannya sebagai karakter pemisah. Jika ingin angka ditampilkan dengan pemisah ribuan atau jutaan, atau simbol mata uang, format sel setelah angka dimasukkan. Misalnya, jika Anda ingin menambahkan 3100 ke nilai di sel A3, dan Anda memasukkan rumus =SUM(3,100,A3), Excel menambahkan angka 3 dan 100 lalu menambahkan total tersebut ke nilai dari A3, daripada menambahkan 3100 ke A3, yang akan menjadi =SUM(3100,A3). Atau, jika memasukkan rumus =ABS(-2,134), Excel akan menampilkan kesalahan karena fungsi ABS hanya menerima satu argumen: =ABS(-2134).

Anda dapat menerapkan aturan tertentu untuk memeriksa kesalahan dalam rumus. Aturan ini tidak menjamin bahwa lembar kerja akan bebas dari kesalahan, tetapi dapat membantu dalam menemukan kesalahan umum. Aturan ini masing-masing dapat diaktifkan atau dinonaktifkan.

Kesalahan dapat ditandai dan dikoreksi dalam dua cara: satu kesalahan dalam satu waktu (seperti pemeriksa ejaan), atau langsung ketika kesalahan terjadi di lembar kerja saat Anda memasukkan data.

Anda bisa mengatasi kesalahan dengan menggunakan opsi yang ditampilkan Excel, atau Anda bisa mengabaikan kesalahan dengan memilih Abaikan Kesalahan. Jika Anda mengabaikan kesalahan di sel tertentu, kesalahan di dalam sel tersebut tidak muncul dalam pemeriksaan kesalahan mendatang. Namun, Anda bisa mereset semua kesalahan yang sebelumnya diabaikan agar muncul lagi.

1. Untuk Excel di Windows, masuk ke Opsi > File > Rumus, atau
   untuk Excel di Mac, pilih menu Excel > Preferensi > Pemeriksaan Kesalahan.

2. Di bawah Pemeriksaan Kesalahan, centang Aktifkan pemeriksaan kesalahan latar belakang. Kesalahan apa pun yang ditemukan ditandai dengan segitiga di sudut kiri atas sel.

   

3. Untuk mengubah warna segitiga yang menandai tempat kesalahan terjadi, dalam kotak Indikasikan kesalahan menggunakan warna ini, pilih warna yang diinginkan.

4. Di bawah Aturan pemeriksaan Excel, pilih atau kosongkan kotak centang salah satu aturan berikut:

   • Sel berisi rumus yang menghasilkan kesalahan: Rumus tidak menggunakan sintaks, argumen, atau tipe data yang diharapkan. Nilai kesalahan meliputi #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, dan #VALUE!. Setiap nilai kesalahan ini memiliki penyebab yang berbeda dan diatasi dengan cara yang berbeda.

     Catatan: Jika Anda memasukkan nilai kesalahan secara langsung dalam sel, nilai kesalahan tersebut disimpan sebagai nilai kesalahan tersebut tetapi tidak ditandai sebagai kesalahan. Namun, jika rumus di sel lain merujuk ke sel tersebut, rumus tersebut mengembalikan nilai kesalahan dari sel itu.

   • Rumus kolom terhitung yang tidak konsisten dalam tabel: Kolom yang dihitung dapat mencakup rumus tersendiri yang berbeda dari rumus kolom utama, yang menghasilkan pengecualian. Pengecualian kolom yang dihitung dibuat saat Anda melakukan salah satu hal berikut:

     • Ketik data selain rumus di dalam sel kolom yang dihitung.

     • Ketikkan rumus dalam sel kolom terhitung, lalu gunakan Ctrl +Z atau pilih Batalkan di Bilah Alat Akses Cepat.

     • Ketik rumus baru di kolom terhitung yang sudah berisi satu atau beberapa pengecualian.

     • Menyalin data ke dalam kolom yang dihitung yang tidak cocok dengan rumus kolom yang dihitung. Jika data yang disalin berisi rumus, rumus ini akan menimpa data di kolom yang dihitung.

     • Memindahkan atau menghapus sel di area lembar kerja lain yang dirujuk oleh salah satu baris di dalam kolom yang dihitung.

   • Sel berisi tahun yang dinyatakan dalam 2 digit: Sel berisi tanggal teks yang mungkin disalahartikan sebagai abad yang salah saat digunakan dalam rumus. Misalnya, tanggal di dalam rumus =YEAR("1/1/31") dapat diartikan menjadi 1931 atau 2031. Gunakan aturan ini untuk memeriksa tanggal teks yang ambigu.

   • Angka yang diformat sebagai teks atau didahului oleh apostrof: Sel berisi angka yang disimpan sebagai teks. Hal ini biasanya terjadi saat data diimpor dari sumber lain. Angka yang disimpan sebagai teks bisa menyebabkan hasil pengurutan yang tidak diharapkan, jadi paling baik untuk mengonversinya menjadi angka. ‘=SUM(A1:A10) dianggap sebagai teks.

   • Rumus yang tidak konsisten dengan rumus lain di kawasan: Rumus tidak cocok dengan pola rumus lain di dekatnya. Dalam banyak kasus, rumus yang berdekatan dengan rumus lain hanya berbeda dalam referensi yang digunakan. Dalam contoh berikut empat rumus yang berdekatan, Excel menampilkan kesalahan di samping rumus =SUM(A10:C10) di sel D4 karena rumus yang berdekatan bertambah satu baris, dan satu penambahan dengan 8 baris — Excel mengharapkan rumus =SUM(A4:C4).

     

     Jika referensi yang digunakan dalam rumus tidak konsisten dengan referensi dalam rumus yang berdekatan, Excel menampilkan kesalahan.

   • Rumus yang menghilangkan sel dalam kawasan: Rumus mungkin tidak secara otomatis mencakup referensi ke data yang Anda sisipkan di antara rentang data asli dan sel yang berisi rumus. Aturan ini membandingkan referensi dalam rumus dengan rentang asli sel yang berdekatan dengan sel yang berisi rumus. Jika sel yang berdekatan berisi nilai tambahan dan tidak kosong, Excel menampilkan kesalahan di sebelah rumus.

     Misalnya, Excel menyisipkan kesalahan di samping rumus =SUM(D2:D4) ketika aturan ini diterapkan, karena sel D5, D6, dan D7 berdekatan dengan sel yang dirujuk dalam rumus dan sel yang berisi rumus (D8), dan sel tersebut berisi data yang seharusnya dirujuk dalam rumus.

     

   • Sel tidak terkunci yang memuat rumus: Rumus tersebut tidak dikunci untuk proteksi. Secara default, semua sel di lembar kerja dikunci, sehingga tidak dapat diubah saat lembar kerja diproteksi. Hal ini dapat membantu menghindari kesalahan yang tidak diinginkan, seperti menghapus atau mengubah rumus secara tidak sengaja. Kesalahan ini menunjukkan bahwa sel diatur untuk tidak dikunci, namun lembarnya belum diproteksi. Periksa untuk memastikan bahwa Anda tidak ingin sel terkunci.

   • Rumus yang merujuk pada sel kosong: Rumus berisi referensi ke sel kosong. Hal ini dapat menyebabkan hasil yang tidak direncanakan, seperti yang dapat dilihat dalam contoh berikut.

     Misalnya Anda ingin menghitung rata-rata jumlah di dalam kolom sel berikut. Jika sel ketiga kosong, sel tersebut tidak disertakan dalam perhitungan dan hasilnya adalah 22,75. Jika sel ketiga berisi 0, hasilnya adalah 18,2.

     

   • Data yang dimasukkan ke dalam tabel tidak valid: Terdapat kesalahan validasi dalam tabel. Periksa pengaturan validasi untuk sel tersebut dengan masuk ke tab Data > grup Alat Data > Validasi Data.

1. Pilih lembar kerja yang ingin Anda periksa kesalahannya.

2. Jika lembar kerja dihitung secara manual, tekan F9 untuk menghitung ulang.

   Jika kotak dialog Pemeriksaan Kesalahan tidak ditampilkan, pilih Rumus > Pengauditan Rumus > Pemeriksaan Kesalahan.

3. Jika sebelumnya mengabaikan kesalahan, Anda dapat memeriksa kembali kesalahan tersebut dengan melakukan hal berikut: masuk ke Opsi > File > Rumus. Untuk Excel di Mac, pilih menu Excel > Preferensi > Pemeriksaan Kesalahan.

   Di bagian Pemeriksaan Kesalahan , pilih Reset Kesalahan yang Diabaikan > OK.

   

   Catatan: Mereset kesalahan yang diabaikan akan mereset semua kesalahan di dalam lembar kerja di buku kerja aktif.

   Tips: Mungkin akan lebih membantu jika Anda memindahkan kotak dialog Pemeriksaan Kesalahan ke bawah bilah rumus.

   

4. Pilih salah satu tombol tindakan di sisi kanan kotak dialog. Tindakan yang tersedia berbeda-beda untuk tiap tipe kesalahan.

5. Pilih Berikutnya.

1. Di samping sel, pilih Pemeriksaan Kesalahan , lalu pilih opsi yang Anda inginkan. Perintah yang tersedia berbeda untuk setiap tipe kesalahan, dan entri pertama mendeskripsikan kesalahan.

   Jika Anda memilih Abaikan Kesalahan, kesalahan ditandai untuk diabaikan untuk setiap pemeriksaan berturut-turut.

   

Jika rumus tidak bisa mengevaluasi hasil dengan benar, Excel menampilkan nilai kesalahan, seperti #####, #DIV/0 !, #N/A, #NAME?, #NULL!, #NUM!, #REF!, dan #VALUE!. Setiap tipe kesalahan memiliki sebab yang berbeda, dan solusi yang berbeda.

Tabel berikut ini berisi link ke artikel yang menjelaskan kesalahan ini secara mendetail, dan deskripsi singkat untuk membantu Anda memulai.

Topik	Deskripsi
Mengoreksi kesalahan ####	Excel menampilkan kesalahan ini jika sebuah kolom tidak cukup lebar untuk menampilkan semua karakter dalam sel, atau sel berisi nilai tanggal atau waktu negatif. Misalnya, rumus yang mengurangkan tanggal di masa mendatang dari suatu tanggal di masa lalu, seperti =06/15/2008-07/01/2008, menghasilkan nilai tanggal negatif. Tips: Cobalah untuk mengepaskan sel otomatis dengan mengklik dua kali di antara header kolom. Jika ### ditampilkan karena Excel tidak bisa menampilkan semua karakter, ini akan memperbaikinya.
Memperbaiki kesalahan #DIV/ 0! .	Excel menampilkan kesalahan ini jika angka dibagi dengan nol (0) atau dengan sel yang tidak berisi nilai. Tips: Menambahkan penanganan kesalahan seperti dalam contoh berikut ini, yaitu =IF(C2,B2/C2,0)
Mengoreksi kesalahan #N/A	Excel menampilkan kesalahan ini jika sebuah nilai tidak tersedia untuk fungsi atau rumus. Jika menggunakan fungsi seperti VLOOKUP, apakah hal yang sedang dicari memiliki kecocokan dalam rentang pencarian? Seringkali, tidak. Coba gunakan IFERROR untuk menghilangkan #N/A. Dalam hal ini, Anda dapat menggunakan: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,BENAR),0)
Memperbaiki kesalahan #NAME? .	Kesalahan ini ditampilkan ketika Excel tidak mengenali teks di dalam rumus. Misalnya, nama rentang atau nama fungsi bisa salah dieja. Catatan: Jika menggunakan fungsi, pastikan nama fungsi dieja dengan benar. Dalam hal ini, SUM salah dieja. Hapus "e" dan Excel akan mengoreksinya.
Perbaiki #NULL! .	Excel menampilkan kesalahan ini ketika Anda menentukan irisan dari dua area yang tidak beririsan (bersilang). Operator irisan adalah karakter spasi yang memisahkan referensi di dalam rumus. Catatan: Pastikan rentang Anda dipisahkan dengan benar - area C2:C3 dan E4:E6 tidak beririsan, jadi memasukkan rumus =SUM(C2:C3 E4:E6) mengembalikan #NULL! . Meletakkan tanda koma di antara rentang C dan E mengoreksinya =SUM(C2:C3,E4:E6)
Mengoreksi kesalahan #NUM! .	Excel memperlihatkan kesalahan ini saat rumus atau fungsi berisi nilai numerik yang tidak valid. Apakah Anda menggunakan fungsi yang diulang, seperti IRR atau RATE? Jika ya, kesalahan #NUM! mungkin dikarenakan fungsi tidak dapat menemukan hasil. Lihat topik untuk langkah-langkah pemecahan masalah.
Memperbaiki kesalahan #REF! .	Excel menampilkan kesalahan ini saat referensi sel tidak valid. Misalnya, Anda mungkin telah menghapus sel yang dirujuk oleh rumus lain, atau Anda mungkin telah menempelkan sel yang Anda pindahkan di atas sel yang dirujuk oleh rumus lain. Apakah Anda menghapus baris atau kolom secara tidak sengaja? Kami menghapus kolom B dalam rumus ini, =SUM(A2,B2,C2), dan lihat yang terjadi. Gunakan Batalkan (Ctrl+Z) untuk membatalkan penghapusan, susun ulang rumus, atau gunakan referensi rentang berkelanjutan seperti: =SUM(A2:C2), yang akan secara otomatis diperbarui saat kolom B dihapus.
Memperbaiki kesalahan #VALUE! .	Excel dapat menampilkan kesalahan ini jika rumus mencakup sel yang berisi tipe data berbeda. Apakah Anda menggunakan operator Matematika (+, -, *, /, ^) dengan tipe data yang berbeda? Jika ya, coba gunakan fungsi sebagai gantinya. Dalam hal ini, =SUM(F2:F5) akan memperbaiki masalah.

Saat sel tidak terlihat di lembar kerja, Anda bisa watch sel tersebut dan rumusnya di toolbar Jendela Pengawas. Jendela Pengawas memudahkan untuk memeriksa, mengaudit, atau mengonfirmasi penghitungan rumus dan hasil dalam lembar kerja yang berukuran besar. Dengan menggunakan Jendela Pengawas, Anda tidak perlu berulang kali menggulir atau membuka berbagai bagian lembar kerja.

Toolbar ini dapat dipindahkan atau ditambatkan seperti toolbar lainnya. Misalnya, Anda dapat menambatkannya di bawah jendela. Toolbar akan melacak properti sel berikut: 1) Buku kerja, 2) Lembar, 3) Nama (jika sel memiliki Rentang Bernama terkait), 4) Alamat sel, 5) Nilai, dan 6) Rumus.

Menambahkan sel ke Jendela Pengawas

1. Pilih sel yang akan Anda awasi.

   Untuk memilih semua sel di lembar kerja dengan rumus, masuk ke Beranda > Pengeditan > pilih Temukan & Pilih (atau Anda bisa menggunakan Ctrl+G, atau Control+G di Mac)> Masuk Ke Rumus > Khusus.

   

2. Masuk ke Rumus > Pengauditan Rumus > pilih Jendela Pengawas.

3. Pilih Tambahkan Pengawas.

   

4. Konfirmasi bahwa Anda telah memilih semua sel yang ingin anda watch dan pilih Tambahkan.

   

5. Untuk mengubah lebar kolom Jendela Pengawas, seret batas di sisi kanan judul kolom.

6. Untuk menampilkan sel yang direferensikan oleh sebuah entri di Jendela Pengawas, klik dua kali pada entri.

   Catatan: Sel yang memiliki referensi eksternal ke buku kerja lain hanya ditampilkan di toolbar Jendela Pengawas saat buku kerja lain tersebut terbuka.

Menghapus sel dari Jendela Pengawas

1. Jika toolbar Jendela Pengawas tidak ditampilkan, masuk ke Rumus > Pengauditan Rumus > pilih Jendela Pengawas.

2. Pilih sel yang akan Anda hapus.

   Untuk memilih beberapa sel, tekan CTRL lalu pilih sel.

3. Pilih Hapus Pengawas.

   

Terkadang, sulit untuk memahami bagaimana rumus bertumpuk menghitung hasil akhir karena ada beberapa perhitungan menengah dan uji logis. Namun, dengan menggunakan kotak dialog Evaluasi Rumus, Anda dapat melihat setiap bagian dari rumus bertumpuk yang dievaluasi sesuai urutan rumus dihitung. Misalnya, rumus =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) lebih mudah dipahami ketika Anda bisa melihat hasil menengah berikut:

Dalam kotak dialog Evaluasi Rumus	Deskripsi
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Rumus bertumpuk pada awalnya ditampilkan. Fungsi AVERAGE dan fungsi SUM ditumpuk dalam fungsi IF. Rentang sel D2:D5 berisi nilai 55, 35, 45, dan 25, sehingga hasil dari fungsi AVERAGE(D2:D5) adalah 40.
=IF(40>50,SUM(E2:E5),0)	Rentang D2:D5 berisi nilai 55, 35, 45, dan 25, sehingga hasil dari fungsi AVERAGE(D2:D5) adalah 40.
=IF(False,SUM(E2:E5),0)	Karena 40 tidak lebih besar dari 50, ekspresi dalam argumen pertama fungsi IF (argumen logical_test) adalah False. Fungsi IF mengembalikan nilai dari argumen ketiga (argumen value_if_false). Fungsi SUM tidak dievaluasi karena ini adalah argumen kedua untuk fungsi IF (argumen value_if_true), dan hanya dikembalikan jika ekspresi adalah True.

1. Pilih sel yang akan Anda evaluasi. Hanya satu sel yang dapat dievaluasi pada satu waktu.

2. Masuk ke Rumus > Rumus Pengauditan > Evaluasi Rumus.

3. Pilih Evaluasi untuk memeriksa nilai referensi bergaris bawah. Hasil dari evaluasi diperlihatkan dalam huruf miring.

   Jika bagian rumus bergaris bawah adalah referensi ke rumus lain, pilih Langkah Masuk untuk menampilkan rumus lain dalam kotak Evaluasi . Pilih Keluar untuk kembali ke sel dan rumus sebelumnya.

   Tombol Melangkah Masuk tidak tersedia sebagai referensi saat referensi muncul untuk kedua kalinya di dalam rumus, atau jika rumus merujuk ke sel di buku kerja terpisah.

4. Lanjutkan memilih Evaluasi hingga setiap bagian rumus telah dievaluasi.

5. Untuk melihat evaluasi lagi, pilih Mulai ulang.

6. Untuk mengakhiri evaluasi, pilih Tutup.