Jika penghitungan salah, rumus menampilkan beberapa jenis kesalahan, bukan nilai. Mari kita lihat contoh praktis dalam proses pengerjaan rumus yang memberikan hasil perhitungan yang salah.

Kesalahan rumus Excel ditampilkan dalam sel

Pelajaran ini akan menjelaskan arti kesalahan rumus yang mungkin terdapat pada sel. Mengetahui arti setiap kode (misalnya: #VALUE!, #DIV/0!, #NUMBER!, #N/A!, #NAME!, #EMPTY!, #LINK!), Anda dapat dengan mudah mengetahui caranya temukan kesalahan dalam rumus dan hilangkan dia.

Cara menghapus #DIV/0 di Excel

Seperti yang Anda lihat, ketika membagi sel dengan nilai kosong, program menganggapnya membaginya dengan 0. Hasilnya, ia menghasilkan nilai: #DIV/0! Anda dapat memverifikasi ini dengan bantuan petunjuk.

Dalam perhitungan aritmatika lainnya (perkalian, penjumlahan, pengurangan), sel kosong juga bernilai nol.

Hasil perhitungan yang salah adalah #NUMBER!

Nomor salah: #NUMBER! – Ini adalah kesalahan ketidakmampuan melakukan penghitungan dalam rumus.

Beberapa contoh praktis:

Kesalahan: #NUMBER! terjadi ketika nilai numerik terlalu besar atau terlalu kecil. Juga kesalahan ini dapat terjadi ketika mencoba mendapatkan akar bilangan negatif. Misalnya, =ROOT(-25).

Sel A1 berisi angka yang terlalu besar (10^1000). Excel tidak bisa menangani angka sebesar itu.

Sel A2 memiliki masalah yang sama dengan jumlah yang besar. Tampaknya 1000 adalah angka yang kecil, namun mengembalikan hasil faktorialnya dalam nilai numerik yang terlalu besar untuk ditangani oleh Excel.

Di sel A3, akar kuadrat tidak boleh berupa angka negatif, dan program menampilkan hasil ini dengan kesalahan yang sama.

Cara menghapus ND di Excel

Nilai tidak tersedia: #N/A! – berarti nilainya tidak tersedia untuk rumus:

Rumus tertulis di B1: =MATCH(„Maxim”, A1:A4) mencari konten teks “Maxim” di rentang sel A1:A4. Konten ditemukan di sel kedua A2. Oleh karena itu, fungsi mengembalikan hasil 2. Rumus kedua mencari konten teks “Andrey”, maka rentang A1:A4 tidak mengandung nilai tersebut. Oleh karena itu, fungsi mengembalikan kesalahan #N/A (tidak ada data).

Kesalahan #NAME! di Excel

Termasuk dalam kategori kesalahan penulisan fungsi. Nama tidak valid: #NAME! - berarti Excel tidak mengenali teks yang tertulis di rumus (nama fungsi =SUM() tidak diketahui, ditulis dengan kesalahan). Ini adalah akibat dari kesalahan sintaksis saat menulis nama fungsi. Misalnya:

Kesalahan #KOSONG! di Excel

Set kosong: #KOSONG! adalah kesalahan dari operator persimpangan himpunan. Di Excel ada yang namanya perpotongan himpunan. Ini digunakan untuk penerimaan cepat data dari tabel besar berdasarkan permintaan titik perpotongan rentang sel vertikal dan horizontal. Jika rentang tidak berpotongan, program akan menampilkan nilai yang salah - #EMPTY! Operator perpotongan himpunan adalah spasi tunggal. Ini memisahkan rentang vertikal dan horizontal yang ditentukan dalam argumen fungsi.

Dalam hal ini, perpotongan rentang adalah sel C3 dan fungsinya menampilkan nilainya.

Argumen yang diberikan dalam fungsi: =SUM(B4:D4 B2:B3) – tidak membentuk perpotongan. Oleh karena itu, fungsi mengembalikan nilai kesalahan - #EMPTY!

#LINK! – Kesalahan referensi sel Excel

DI DALAM dalam contoh ini Kesalahan terjadi ketika rumus disalin secara tidak benar. Kami memiliki 3 rentang sel: A1:A3, B1:B4, C1:C2.

Di bawah rentang pertama, di sel A4, masukkan rumus penjumlahan: =SUM(A1:A3). Dan kemudian kita menyalin rumus yang sama di bawah rentang kedua, di sel B5. Rumusnya, seperti sebelumnya, hanya menjumlahkan 3 sel B2:B4, melewati nilai B1 pertama.

Ketika rumus yang sama disalin di bawah rentang ketiga, fungsi tersebut mengembalikan kesalahan #REF! Karena hanya ada 2 sel di atas sel C3 dan bukan 3 (seperti yang disyaratkan oleh rumus aslinya).

Catatan. Dalam hal ini, akan lebih mudah untuk menekan kombinasi tombol pintas di bawah setiap rentang sebelum memulai input. tombol ALT+=. Kemudian masukkan fungsi penjumlahan dan secara otomatis menentukan jumlah sel penjumlahan.

Cara memperbaiki NILAI di Excel

#NILAI! – kesalahan dalam arti. Jika kita mencoba menambahkan angka dan kata di Excel, hasilnya adalah #VALUE! Fakta menariknya adalah jika kita mencoba menjumlahkan dua sel yang nilai sel pertama adalah angka dan sel kedua adalah teks menggunakan fungsi =SUM(), maka tidak akan terjadi kesalahan, dan teks akan mengambil nilai 0 selama perhitungan. Misalnya:

Grid dalam sel Excel

Serangkaian tanda pagar alih-alih nilai sel ###### - nilai ini bukan kesalahan. Ini hanyalah informasi bahwa lebar kolom terlalu sempit untuk menampung konten sel yang ditampilkan dengan benar. Anda hanya perlu memperluas kolomnya. Misalnya, lakukan klik dua kali tombol kiri mouse di perbatasan header kolom sel ini.

Jadi tanda pagar (######) sebagai pengganti nilai sel dapat dilihat dengan tanggal negatif. Misalnya, kita mencoba mengurangi tanggal baru dari tanggal lama. Dan sebagai hasil perhitungan, format sel diatur ke “Tanggal” (dan bukan “Umum”).

Format sel yang salah mungkin juga menampilkan serangkaian simbol hash (######) alih-alih nilai.

Kesalahan di Excel adalah pendamping yang sangat diperlukan bagi semua orang yang... Ketika ekspresi dalam sel tidak dapat dievaluasi, program akan menampilkan pesan kesalahan di dalam sel. Ini dimulai dengan "#" diikuti dengan nama kesalahan. Tidak perlu takut akan hal ini jika Anda terbiasa dengan fungsi Excel dan tahu cara mengikuti logika operasi matematika yang paling sederhana - Anda akan dengan mudah menemukan dan memperbaiki kesalahan tersebut.

Jika sel terisi penuh dengan tanda pagar (#), ini bukan kesalahan sama sekali. Tidak ada cukup ruang di sel untuk menampilkan hasilnya. Memperbesar ukuran sel atau memperkecil ukuran font agar hasilnya dapat ditampilkan.

Jenis kesalahan

Jika kesalahan masih terjadi, dekripsi akan membantu memperbaikinya:

Ketika ada kesalahan dalam rumus di sel, penanda akan muncul di sebelahnya. Dengan mengkliknya, Anda dapat membaca bantuan tentang kesalahan ini. Anda juga bisa melihat tahapan perhitungannya. Pilih item ini, dan program akan menampilkan jendela di mana lokasi kesalahan akan digarisbawahi. Ini cara terbaik menentukan di mana kesalahan itu terjadi.

Selain itu, Anda dapat melakukannya tanpa memperbaiki kesalahan tersebut, tetapi secara sederhana . Tapi itu harus sesuai. Kesalahan hanya boleh diatasi jika tidak dapat diperbaiki. Jika tidak, hasil penghitungan mungkin terdistorsi.

Tautan melingkar di Excel

Jenis kesalahan lainnya adalah referensi melingkar. Ini terjadi ketika Anda mereferensikan sel yang nilainya bergantung pada sel tempat Anda menulis rumus. Misalnya saja di dalam sangkar A1 rumus =A2+1 ditulis, dan masuk A2 write =A1 , akan muncul referensi siklik yang akan dihitung ulang tanpa henti. Dalam hal ini, program memperingatkan tentang munculnya referensi siklik dan menghentikan penghitungan "rumus siklik". Panah berkepala dua muncul di sisi kiri sel. Anda harus memperbaiki kesalahan yang terjadi dan mengulangi perhitungannya.

Terkadang “perulangan” yang kompleks terjadi ketika tautan siklik dibentuk dengan beberapa rumus perantara.

Untuk melacak kesalahan tersebut, jalankan Rumus – Ketergantungan Rumus – Pemeriksaan Kesalahan – Referensi Melingkar. Dalam daftar drop-down, program menampilkan alamat sel yang membuat loop tanpa akhir. Yang tersisa hanyalah memperbaiki rumus di sel ini.

Di Excel, Anda bisa mencoba menghitung hasil perulangan rumus. Untuk melakukan ini, centang kotaknya File – Opsi – Rumus – Aktifkan Perhitungan Iteratif. Di blok yang sama, Anda dapat mengatur jumlah maksimum iterasi (perhitungan) untuk menemukan keseimbangan dan kesalahan yang diizinkan. Dalam kebanyakan kasus, hal ini tidak diperlukan, jadi saya sarankan untuk tidak mencentang kotak ini. Namun, jika Anda mengetahui bahwa rumus perulangan sudah benar dan penghitungannya akan memberikan hasil yang stabil - mengapa tidak melakukannya?

Itu saja tentang jenis-jenis kesalahan di Excel. Artikel singkat ini telah memberi Anda informasi yang cukup untuk mengatasi kesalahan paling umum di Excel dengan menganalisis nilai kembalian. Tapi bacalah daftar kesalahan yang diperluas! Saya siap menjawab pertanyaan Anda - tulis di komentar.

Di artikel berikutnya saya akan memberi tahu Anda. Tak perlu dikatakan bahwa fungsi Excel adalah “segalanya bagi kami”?
Saya rasa tidak. Oleh karena itu, silakan baca, ini akan menjadi langkah pertama menuju dunia rumus kompleks dengan hasil yang tepat!

Setelah memasukkan atau menyesuaikan rumus, serta saat mengubah nilai fungsi apa pun, yang muncul adalah kesalahan rumus, bukan nilai yang diperlukan. Total editor spreadsheet mengakui tujuh jenis utama kesalahan perhitungan tersebut. Kita akan melihat seperti apa kesalahan di Excel dan cara memperbaikinya di bawah ini.

Di bawah ini kami akan menyajikan penjelasan rumus yang ditunjukkan pada gambar dengan informasi rinci untuk setiap kesalahan.

1. #KASUS!– “pembagian dengan 0”, paling sering terjadi ketika mencoba membagi dengan nol. Artinya, rumus yang tertanam di dalam sel, yang menjalankan fungsi pembagian, menemukan sel dengan nilai nol atau “Kosong”. Untuk mengatasi masalah ini, periksa semua sel yang terlibat dalam penghitungan dan perbaiki nilai yang tidak valid. Tindakan kedua mengarah ke #DIV/O! – ini adalah pemasukan nilai yang salah ke dalam beberapa fungsi, seperti =AVERAGE(), jika pada saat perhitungan rentang nilainya mengandung 0. Hasil yang sama akan disebabkan oleh sel kosong yang diakses oleh rumus yang memerlukan spesifik data untuk perhitungan.
2. #T/A- “tidak ada datanya.” Beginilah cara Excel menandai nilai yang tidak jelas dalam rumus (fungsi). Dengan memasukkan angka yang tidak pantas ke dalam suatu fungsi, Anda pasti akan memicu kesalahan ini. Saat muncul, pastikan semua sel input diisi dengan benar, dan terutama di sel yang tulisannya sama menyala. Sering ditemukan saat digunakan
3. #NAMA? – “nama tidak valid”, indikator nama rumus atau sebagian rumus yang salah. Masalahnya hilang jika Anda memeriksa dan memperbaiki semua judul dan nama yang menyertai algoritma perhitungan.
4. #KOSONG!– “ada nilai kosong dalam rentang”, sinyal bahwa di suatu tempat dalam perhitungan terdapat area yang tidak tumpang tindih atau ada spasi yang disisipkan di antara rentang yang ditentukan. Kesalahan yang cukup jarang terjadi. Entri yang salah mungkin terlihat seperti ini:

JUMLAH(G10:G12 I8:J8)

Excel tidak mengenali perintah seperti itu.
5. #NOMOR!– kesalahan disebabkan oleh rumus yang berisi angka yang tidak sesuai dengan rentang yang ditentukan.
6. #LINK!– memperingatkan bahwa sel yang terkait dengan rumus ini telah hilang. Periksa untuk melihat apakah sel yang ditentukan dalam rumus telah dihapus.
7. #NILAI!– jenis argumen untuk operasi fungsi tidak dipilih dengan benar.

8. Bonus, kesalahan ##### — lebar sel tidak cukup untuk menampilkan seluruh angka

Selain itu, Excel mengeluarkan peringatan bahwa rumusnya salah. Program ini akan mencoba memberi tahu Anda cara menempatkan tanda baca (misalnya, tanda kurung). Jika opsi yang diusulkan memenuhi kebutuhan Anda, klik “Ya”. Jika sebuah petunjuk memerlukan penyesuaian manual. Kemudian pilih “Tidak” dan atur ulang tanda kurungnya sendiri.

Kesalahan di Excel. Menggunakan fungsi ERROR() untuk Excel 2003

Fungsinya membantu menghilangkan kesalahan di Excel dengan baik. Ia bekerja dengan menemukan kesalahan dalam sel; jika menemukan kesalahan dalam rumus, ia mengembalikan TRUE dan sebaliknya. Dikombinasikan dengan =IF(), ini akan memungkinkan Anda mengganti nilai jika ditemukan kesalahan.

Rumus kerja: =IF(ERROR(ekspresi),kesalahan,ekspresi).

JIKA(ISERROR (A1/A2);””;A1/A2)

Penjelasan: Jika ditemukan kesalahan saat menjalankan A1/A2, kosong ("") akan dikembalikan. Jika semuanya berjalan dengan benar (yaitu ERROR (A1/A2) = FALSE), maka A1/A2 dihitung.

Kesalahan di Excel.Menggunakan IFERROR() untuk Excel 2007 dan lebih tinggi

Salah satu alasan mengapa saya segera beralih ke Excel 2007 adalah IFERROR() (yang paling banyak alasan utama- Ini )

Fungsi iferror berisi kemampuan kedua fungsi - ERROR() dan IF(), namun tersedia di versi Excel yang lebih baru, yang sangat memudahkan

Alat ini diaktifkan sebagai berikut: =IFERROR(nilai; nilai kesalahan). Alih-alih "nilai" ada ekspresi terhitung/tautan ke sel, dan alih-alih "nilai jika terjadi kesalahan" - apa yang harus dikembalikan jika terjadi ketidakakuratan, misalnya, jika saat menghitung A1/A2 #KASUS! maka rumusnya akan terlihat seperti ini:

IFERROR(A1/A2;””)

» unduh di sini

Kesalahan di Excel cukup sering terjadi. Anda mungkin memperhatikan nilai-nilai aneh dalam sel, bukan hasil yang diharapkan, yang dimulai dengan tanda # . Ini menunjukkan bahwa rumus tersebut mengembalikan kesalahan. Untuk menghilangkan kesalahan, Anda harus memperbaiki penyebabnya, dan penyebabnya bisa sangat berbeda.

Tanda kurung buka dan tutup tidak cocok

Contoh kesalahan rumus Excel yang paling umum adalah ketidakcocokan tanda kurung buka dan tutup. Saat pengguna memasukkan rumus, Excel secara otomatis memeriksa sintaksnya dan tidak akan mengizinkan Anda menyelesaikan pengetikan jika rumus tersebut mengandung kesalahan sintaksis.

Misalnya pada gambar di atas, kami sengaja menghilangkan tanda kurung tutup saat memasukkan rumus. Jika Anda menekan tombol Memasuki, Excel akan mengeluarkan peringatan berikut:

Dalam beberapa kasus, Excel menawarkan versi perbaikan kesalahannya sendiri. Anda bisa menyetujui Excel atau mengoreksi sendiri rumusnya. Bagaimanapun, Anda tidak boleh begitu saja mengandalkan perbaikan ini. Misalnya, pada gambar berikut, Excel memberi kita solusi yang salah.

Sel itu dipenuhi dengan tanda pagar

Ada kalanya sel di Excel terisi penuh dengan tanda pagar. Ini berarti salah satu dari dua pilihan:

Dalam hal ini, menambah lebar kolom tidak akan membantu.

Kesalahan #DIV/0!

Kesalahan #DIV/0! Terjadi ketika Excel membagi dengan nol. Ini bisa berupa pembagian eksplisit dengan nol atau pembagian dengan sel yang berisi nol atau kosong.

Kesalahan #T/A

Kesalahan #T/A Terjadi ketika nilai tidak tersedia untuk rumus atau fungsi. Berikut adalah beberapa kasus dimana kesalahan terjadi: #T/A:

Kesalahan #NAMA?

Kesalahan #NAMA? Terjadi ketika rumus berisi nama yang tidak dipahami Excel.

Dalam contoh ini, rentang nama tidak ditentukan.

Kesalahan #KOSONG!

Kesalahan #KOSONG! terjadi ketika perpotongan dua rentang yang tidak mempunyai titik persekutuan ditentukan.

Kesalahan #NUMBER!

Kesalahan #NOMOR! terjadi ketika masalah dalam rumus terkait dengan nilai.

Jangan lupa bahwa Excel mendukung nilai numerik dari -1E-307 hingga 1E+307.

1. Kasus kesalahan lainnya #NOMOR! adalah penggunaan fungsi yang menggunakan metode iterasi saat menghitung dan tidak dapat menghitung hasilnya. Contoh mencolok dari fungsi tersebut di Excel adalah PENAWARAN Dan VSD.

Kesalahan #LINK!

Kesalahan #VALUE!

Kesalahan #NILAI! salah satu kesalahan paling umum yang ditemukan di Excel. Hal ini terjadi ketika nilai salah satu argumen rumus atau fungsi berisi nilai yang tidak valid. Kasus terjadinya kesalahan yang paling umum #NILAI!.