Saat bekerja dengan Excel, kita semua pasti akan mengalami yang namanya error, salah satunya #VALUE.
Berbeda dengan error lain seperti #DIV/0, #VALUE lebih sulit dicari penyebabnya.
Artikel ini membahas error #VALUE di Excel, penyebab, dan cara mengatasinya.
Apa Itu Error #VALUE di Excel?
Ada banyak sekali penyebab error #VALUE.
Secara umum, error #ini adalah cara Excel untuk mengatakan bahwa ada masalah dengan nilai yang kita gunakan di dalam rumus.
Bila tidak menemukan apa yang dicari, bisa jadi sebenarnya penyebab masalah yang teman-teman alami adalah variasi dari salah satu bahasan kami.
Perhitungan Tidak Menggunakan Tipe Data Angka
Secara umum, ada dua tipe data Excel, yaitu teks dan angka.
Komponen perhitungan apa pun di Excel harus menggunakan tipe data angka atau variasinya.
Rumus di bawah ini menjanjikan error #VALUE.
="Dua"+"Tiga"
Tapi, rumus di bawah ini memberikan hasil angka 5.
=2+3
Perhatikan gambar di bawah.
Pada gambar di atas, cell D3 tidak menghasilkan nilai nol karena nilai Penjualan di B3 adalah tanda dash (-).
Di bawahnya, cell D4 adalah hasil perkalian nilai 4541000 dengan nilai teks Belum Ada.
Baik cell D3 dan D4 menghasilkan nilai error #VALUE karena salah satu komponen perhitungan adalah teks.
Untuk menghindari kondisi seperti ini, ada beberapa alternatif solusi sebagai cara mengatasi error #VALUE di Excel:
Ganti nilai teks dengan nilai angka sesungguhnya. Kita bisa mengetikkan nilai nol (0) di B3 dan C4 sebagai pengganti dash (-) atau kata Belum Ada.
Catatan: Nilai dash (-) pada cell B3 Gambar 01 adalah nilai yang kami ketikkan sendiri. Sementara nilai dash (-) pada cell B3 Gambar 02 adalah bagaimana format Accounting menampilkan angka nol (0).
Berhubung nilai Komisi Nominal adalah perkalian antara nilai Penjualan dan Persentase, gunakan fungsi yang mengabaikan referensi nilai teks seperti PRODUCT.
Pertimbangkan untuk menggunakan fungsi NA bila cell yang dimaksudkan memang belum memiliki nilai. Fungsi NA akan menghasilkan nilai #N/A, seperti terlihat pada Gambar 01 cell B6.
Semua perhitungan yang menggunakan komponen #N/A akan menghasilkan nilai yang sama, sebagai pengingat bahwa cell tersebut belum memiliki nilai.
Catatan: Teman-teman mungkin melihat pada Gambar 01 bahwa nilai B5 dan B7 memiliki karakter teks, yaitu rata kiri, tapi tetap memberikan hasil sesuai harapan.
Hal ini terjadi karena Excel cukup pintar melihat nilai teks tertentu sebagai angka.
Walau pun begitu, kami tetap menganjurkan nilai angka sesungguhnya dalam perhitungan.
Spasi dan Karakter Tersembunyi
Penyebab error #VALUE di Excel satu ini masih berhubungan dengan tipe data teks dalam perhitungan.
Excel akan menganggap nilai cell kosong sebagai nol.
Tapi, kadang cell yang terlihat kosong tidak benar-benar kosong karena menyimpan nilai seperti spasi atau karakter yang tidak terlihat lainnya.
Spasi atau nilai lain yang tidak terlihat dianggap sebagai teks. Kita tau apa yang terjadi bila operasi matematika menyertakan teks sebagai komponen perhitungan.
Solusi untuk penyebab ini mirip dengan penggunaan tipe data teks untuk perhitungan.
Sebagai contoh, kita perlu edit cell bersangkutan dan menghilangkan segala spasi atau karakter-karakter lain yang tidak kelihatan.
Cara lainnya adalah menggunakan rumus yang mengabaikan referensi nilai teks. Dalam konteks Gambar 05, untuk menjumlahkan semua penjualan, gunakan fungsi SUM.
Penggunaan Parameter Range Tidak Sesuai
Saat menggunakan fungsi dengan range sebagai parameter, selalu ingat bahwa kita harus selalu mengacu pada cakupan range tersebut.
Bila acuan tidak sesuai, #VALUE akan tampak sebagai gantinya.
Pada Gambar 06, kami mengunakan fungsi SORT untuk mengurutkan data penjualan A3:B7 dari terendah ke tertinggi.
Sesuai spesifikasi, parameter kedua SORT adalah indikator kolom mana yang akan diurutkan. Sebagai contoh, bila kita masukkan 1, maka data akan diurutkan berdasarkan kolom Sales. Bila 2, berdasarkan Penjualan.
Tapi, kami masukkan nilai 3, melebihi jumlah kolom pada range A3:B7.
Hasilnya bisa dilihat pada Gambar 06.A di mana #VALUE muncul.
Solusinya tentu saja menggunakan parameter yang sesuai dengan cakupan range. Perhatikan 06.B.
Tanggal Sebagai Teks
Tanggal adalah salah satu nilai Excel yang paling gampang terinput sebagai teks.
Bila hal itu terjadi, Excel akan memberikan #VALUE saat kita menggunakan nilai tersebut di dalam perhitungan.
Solusi mengatasi masalah ini adalah menggunakan format tanggal yang sesuai dengan ketentuan Excel.
Ingat, Excel pintar, tapi sepintar itu.
Sintaks Fungsi Tidak Benar
#VALUE juga bisa terjadi bila kita salah menggunakan sintaks.
Pada Gambar 08, kami kembali ingin mengurutkan data penjualan A3:B7 dari terendah dan tertinggi.
Untuk mengurutkan penjualan, kami menentukan kolom kedua sebagai obyek pengurutan. Tapi, kami tidak memasukkan angka 2, melainkan teks Kolom 2.
Excel melihat ini sebagai kesalahan dalam penggunaan sintaks fungsi dan menampilkan #VALUE.
Solusinya tentu saja menggunakan fungsi sesuai spesifikasi yang ditentukan. Dalam hal ini, rumus yang seharusnya kami gunakan adalah:
=SORT(A3:B7; 2)
Error #VALUE di VLOOKUP
Dengan VLOOKUP, ada beberapa kemungkinan di mana #VALUE terjadi, yaitu:
- Nilai acuan lebih dari 255 karakter.
- Sumber data berada di workbook lain dan path workbook tidak valid.
- Nilai indeks kolom darimana nilai akan diambil lebih kecil dari satu. Contoh:
=VLOOKUP(A2; $D$2:$F$6; -1).
Berikut adalah contoh penggunaan VLOOKUP dengan nilai acuan lebih dari 255 karakter.
Spesifikasi VLOOKUP menyatakan bahwa fungsi ini tidak terima bila nilai yang dicari melebihi 255 karakter.
Bila nilai acuan memang melebihi 255 karakter, sementara nilai yang dicari tidak sepanjang itu, gunakan fungsi seperti MID, LEFT, RIGHT, INDEX, atau fungsi lain yang sesuai, untuk mengambil sebagian nilai teks dari acuan.
Pada gambar di bawah, kami menggunakan LEFT untuk mengambil dua karakter terkiri dari Kode.
Error #VALUE di XLOOKUP
XLOOKUP digunakan untuk mencari data berdasarkan rentang data tertentu dalam orientasi baris, sama seperti VLOOKUP.
XLOOKUP juga bisa menghasilkan error #VALUE, umumnya saat data array yang digunakan sebagai sumber acuan dan nilai kembaliannya berbeda besaran.
Pada gambar A di atas, sumber acuan adalah range F2:F3 (1 kolom, 2 baris), sementara range dari mana nilai akan dikembalikan adalah H2:H5 (1 kolom, 4 baris). Karena berbeda ukuran, Excel menampilkan #VALUE.
Untuk memperbaikinya, gunakan besaran data array yang sepadan, seperti terlihat pada gambar B.
Cara Mengatasi Error #VALUE di Excel dengan IF dan IFERROR
Cara terbaik untuk menghindari error adalah dengan melenyapkan resiko error.
Namun, kita tidak selalu bisa melenyapkan resiko error 100%.
Sebagai jaga-jaga, gunakan fungsi IF atau IFERROR untuk menangkap kemungkinan error.
Pada Gambar A, kami menggunakan kombinasi fungsi IF dan ISNUMBER.
ISNUMBER akan menentukan apakah komponen perhitungan adalah angka atau bukan. IF kemudian akan mengevaluasi bila salah satu komponen perhitungan bukan angka, maka nilai nol (0) akan ditampilkan.
IFERROR lebih terus terang dalam menangkap kemungkinan error. Fungsi ini akan membungkus perhitungan. Bila perhitungan mengembalikan nilai error #VALUE (tak peduli apa penyebab errornya), maka nilai alternatif (dalam hal ini angka nol) akan ditampilkan.
Cara Mencari Error #VALUE di Excel
Pada data besar, besar pula kemungkinan error #VALUE luput dari pengawasan kita.
Kita membutuhkan bantuan untuk menemukannya.
Salah satu alat yang bisa diandalkan untuk tujuan ini adalah Find and Replace.
Teman-teman bisa belajar mengenai cara menemukan error #VALUE di Excel serta error-error lainnya lewat artikel berikut.
Baca: Menemukan Pesan Error.