Konten
Pelajaran ini menjelaskan cara cepat menangani situasi di mana fungsi VPR (VLOOKUP) tidak ingin bekerja di Excel 2013, 2010, 2007 dan 2003, serta cara mengidentifikasi dan memperbaiki kesalahan umum serta mengatasi keterbatasan VPR.
Dalam beberapa artikel sebelumnya, kami telah menjelajahi berbagai aspek fungsi VPR di Excel. Jika Anda telah membacanya dengan cermat, Anda seharusnya sekarang menjadi ahli di bidang ini. Namun, bukan tanpa alasan banyak pakar Excel percaya VPR salah satu fitur yang lebih kompleks. Ini memiliki banyak keterbatasan dan fitur yang menjadi sumber banyak masalah dan kesalahan.
Dalam artikel ini Anda akan menemukan penjelasan sederhana tentang kesalahan #PADA (#T/A), #NAME? (#NAMA?) dan #NILAI! (#VALUE!) yang muncul saat bekerja dengan fungsi VPR, serta teknik dan metode untuk menghadapinya. Kami akan mulai dengan kasus yang paling umum dan alasan yang paling jelas mengapa. VPR tidak berfungsi, jadi lebih baik mempelajari contoh-contoh dalam urutan yang diberikan dalam artikel.
Memperbaiki kesalahan #N/A dalam fungsi VLOOKUP di Excel
Dalam rumus dengan VPR pesan kesalahan #PADA (#T/A) artinya tidak tersedia (tidak ada data) – muncul saat Excel tidak dapat menemukan nilai yang Anda cari. Hal ini dapat terjadi karena beberapa alasan.
1. Nilai yang diinginkan salah eja
Ide bagus untuk memeriksa item ini terlebih dahulu! Kesalahan ketik sering terjadi saat Anda bekerja dengan jumlah data yang sangat besar, terdiri dari ribuan baris, atau saat nilai yang Anda cari ditulis ke dalam rumus.
2. Kesalahan #N/A saat mencari perkiraan kecocokan dengan VLOOKUP
Jika Anda menggunakan rumus dengan kondisi pencarian kecocokan perkiraan, yaitu argumen rentang_pencarian (range_lookup) BENAR atau tidak ditentukan, rumus Anda mungkin melaporkan kesalahan # N / A dalam dua kasus:
- Nilai yang akan dicari kurang dari nilai terkecil dalam array yang dicari.
- Kolom pencarian tidak diurutkan dalam urutan menaik.
3. Kesalahan #N/A saat mencari kecocokan persis dengan VLOOKUP
Jika Anda mencari kecocokan yang tepat, yaitu argumen rentang_pencarian (range_lookup) adalah FALSE dan nilai pastinya tidak ditemukan, rumus juga akan melaporkan kesalahan # N / A. Pelajari lebih lanjut tentang cara mencari kecocokan tepat dan perkiraan dengan suatu fungsi VPR.
4. Kolom pencarian tidak paling kiri
Seperti yang mungkin Anda ketahui, salah satu batasan paling signifikan VPR itu tidak bisa menghadap ke kiri, maka kolom pencarian di tabel Anda harus paling kiri. Dalam praktiknya, kita sering melupakan hal ini, yang mengarah pada formula yang tidak berfungsi dan kesalahan. # N / A.
Keputusan: Jika tidak memungkinkan untuk mengubah struktur data sehingga kolom pencarian paling kiri, Anda dapat menggunakan kombinasi fungsi INDEKS (INDEX) dan LEBIH TERBUKTI (MATCH) sebagai alternatif yang lebih fleksibel untuk VPR.
5. Angka diformat sebagai teks
Sumber kesalahan lainnya # N / A dalam rumus dengan VPR adalah angka dalam format teks di tabel utama atau tabel pencarian.
Ini biasanya terjadi saat Anda mengimpor informasi dari database eksternal, atau saat Anda mengetikkan apostrof sebelum angka untuk mempertahankan nol di depan.
Tanda-tanda angka yang paling jelas dalam format teks ditunjukkan pada gambar di bawah ini:
Selain itu, angka dapat disimpan dalam format Umum (Umum). Dalam hal ini, hanya ada satu fitur yang terlihat – angka disejajarkan dengan tepi kiri sel, sedangkan secara default mereka disejajarkan dengan tepi kanan.
Keputusan: Jika itu adalah nilai tunggal, cukup klik pada ikon kesalahan dan pilih Ubah ke Angka (Konversi ke Angka) dari menu konteks.
Jika ini adalah situasi dengan banyak angka, pilih dan klik kanan pada area yang dipilih. Di menu konteks yang muncul, pilih Format Cells (Memformat Sel) > tab Jumlah (Nomor) > format Jumlah (Numerik) dan tekan OK.
6. Ada spasi di awal atau di akhir
Ini adalah alasan kesalahan yang paling tidak jelas. # N / A dalam fungsi VPR, karena secara visual sulit untuk melihat ruang ekstra ini, terutama saat bekerja dengan tabel besar, saat sebagian besar data berada di luar layar.
Solusi 1: Spasi ekstra di tabel utama (di mana fungsi VLOOKUP berada)
Jika spasi tambahan muncul di tabel utama, Anda dapat memastikan bahwa rumus bekerja dengan benar dengan menyertakan argumen nilai lookup (lookup_value) menjadi sebuah fungsi TRIM (MEMANGKAS):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solusi 2: Spasi ekstra di tabel pencarian (di kolom pencarian)
Jika spasi tambahan ada di kolom pencarian – cara sederhana # N / A dalam rumus dengan VPR tidak bisa dihindari. Dari pada VPR Anda dapat menggunakan rumus array dengan kombinasi fungsi INDEKS (INDEKS), LEBIH TERBUKTI (PERTANDINGAN) TRIM (MEMANGKAS):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Karena ini adalah rumus array, jangan lupa tekan Ctrl + Shift + Enter bukannya biasa Enteruntuk memasukkan rumus dengan benar.
Kesalahan #VALUE! dalam rumus dengan VLOOKUP
Dalam kebanyakan kasus, Microsoft Excel melaporkan kesalahan #NILAI! (#VALUE!) bila nilai yang digunakan dalam rumus tidak cocok dengan tipe data. Tentang VPR, maka biasanya ada dua alasan untuk kesalahan tersebut #NILAI!.
1. Nilai yang Anda cari lebih panjang dari 255 karakter
Hati-hati: fungsi VPR tidak dapat mencari nilai yang mengandung lebih dari 255 karakter. Jika nilai yang Anda cari melebihi batas ini, Anda akan menerima pesan kesalahan. #NILAI!.
Keputusan: Gunakan banyak fitur INDEKS+MATCH (INDEKS + PERTANDINGAN). Di bawah ini adalah rumus yang akan baik-baik saja untuk tugas ini:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Jalur lengkap ke buku kerja pencarian tidak ditentukan
Jika Anda mengambil data dari buku kerja lain, Anda harus menentukan jalur lengkap ke file tersebut. Lebih khusus lagi, Anda harus menyertakan nama buku kerja (termasuk ekstensi) dalam tanda kurung siku [ ], diikuti dengan nama lembar, diikuti dengan tanda seru. Semua konstruksi ini harus diapit dengan apostrof, jika nama buku atau lembar berisi spasi.
Berikut adalah struktur lengkap dari fungsi VPR untuk mencari di buku lain:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Rumus sebenarnya mungkin terlihat seperti ini:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Rumus ini akan mencari nilai sel A2 dalam kolom B di atas lembaran Sheet1 di buku kerja Harga Baru dan ekstrak nilai yang sesuai dari kolom D.
Jika ada bagian dari jalur tabel yang dihilangkan, fungsi Anda VPR tidak akan berfungsi dan akan melaporkan kesalahan #NILAI! (bahkan jika buku kerja dengan tabel pencarian sedang terbuka).
Untuk informasi lebih lanjut tentang fungsi VPRreferensi file Excel lain, lihat pelajaran: Mencari buku kerja lain menggunakan VLOOKUP.
3. Argumen Kolom_num kurang dari 1
Sulit membayangkan situasi di mana seseorang memasukkan nilai kurang dari 1untuk menunjukkan kolom dari mana untuk mengekstrak nilai. Meskipun dimungkinkan jika nilai argumen ini dihitung oleh fungsi Excel lain yang bersarang di dalam VPR.
Jadi, jika terjadi argumen itu col_index_num (nomor_kolom) kurang dari 1fungsi VPR juga akan melaporkan kesalahan #NILAI!.
Jika argumen col_index_num (column_number) lebih besar dari jumlah kolom dalam array yang diberikan, VPR akan melaporkan kesalahan # REF! (#SSIL!).
Kesalahan #NAME? di VLOOKUP
Kasus paling sederhana adalah kesalahan #NAME? (#NAME?) – akan muncul jika Anda tidak sengaja menulis nama fungsi dengan kesalahan.
Solusinya jelas – periksa ejaan Anda!
VLOOKUP tidak berfungsi (batasan, peringatan, dan keputusan)
Selain sintaks yang agak rumit, VPR memiliki lebih banyak keterbatasan daripada fungsi Excel lainnya. Karena keterbatasan ini, formula yang tampaknya sederhana dengan VPR sering menyebabkan hasil yang tidak terduga. Di bawah ini Anda akan menemukan solusi untuk beberapa skenario umum di mana: VPR salah.
1. VLOOKUP tidak peka huruf besar-kecil
fungsi VPR tidak membedakan antara huruf besar dan kecil dan menerima karakter huruf kecil dan huruf besar sebagai sama. Oleh karena itu, jika ada beberapa elemen dalam tabel yang berbeda hanya dalam kasus, fungsi VLOOKUP akan mengembalikan elemen pertama yang ditemukan, terlepas dari kasusnya.
Keputusan: Gunakan fungsi Excel lain yang dapat melakukan pencarian vertikal (LOOKUP, SUMPRODUCT, INDEX, dan MATCH) dalam kombinasi dengan TEPATA yang membedakan kasus. Untuk lebih jelasnya, Anda dapat belajar dari pelajaran – 4 cara untuk membuat VLOOKUP peka huruf besar/kecil di Excel.
2. VLOOKUP mengembalikan nilai pertama yang ditemukan
Seperti yang sudah Anda ketahui, VPR mengembalikan nilai dari kolom yang diberikan sesuai dengan kecocokan pertama yang ditemukan. Namun, Anda dapat memintanya mengekstrak 2, 3, 4, atau pengulangan lain dari nilai yang Anda inginkan. Jika Anda perlu mengekstrak semua nilai duplikat, Anda memerlukan kombinasi fungsi INDEKS (INDEKS), PALING SEDIKIT (KECIL) dan GARIS (BARIS).
3. Kolom ditambahkan atau dihapus ke tabel
Sayangnya, rumusnya VPR berhenti bekerja setiap kali kolom baru ditambahkan atau dihapus ke tabel pencarian. Ini terjadi karena sintaks VPR mengharuskan Anda untuk menentukan rentang penuh pencarian dan nomor kolom tertentu untuk ekstraksi data. Secara alami, rentang yang diberikan dan nomor kolom berubah saat Anda menghapus kolom atau menyisipkan yang baru.
Keputusan: Dan lagi fungsi sedang terburu-buru untuk membantu INDEKS (INDEX) dan LEBIH TERBUKTI (COCOK). Dalam rumus INDEKS+MATCH Anda secara terpisah menentukan kolom pencarian dan pengambilan, dan sebagai hasilnya, Anda dapat menghapus atau menyisipkan kolom sebanyak yang Anda inginkan tanpa khawatir harus memperbarui semua rumus pencarian terkait.
4. Referensi sel kacau saat menyalin formula
Judul ini menjelaskan esensi masalah secara mendalam, bukan?
Keputusan: Selalu gunakan referensi sel absolut (dengan simbol $) pada catatan rentang, misalnya $ A $ 2: $ C $ 100 or $ A: $ C. Di bilah rumus, Anda dapat dengan cepat mengganti jenis tautan dengan mengklik F4.
VLOOKUP – bekerja dengan fungsi IFERROR dan ISERROR
Jika Anda tidak ingin menakut-nakuti pengguna dengan pesan kesalahan # N / A, #NILAI! or #NAME?, Anda dapat menampilkan sel kosong atau pesan Anda sendiri. Anda dapat melakukan ini dengan menempatkan VPR menjadi fungsi KESALAHAN (IFERROR) di Excel 2013, 2010 dan 2007 atau gunakan banyak fungsi JIKA+ISERROR (JIKA+ISERROR) di versi sebelumnya.
VLOOKUP: bekerja dengan fungsi IFERROR
Sintaks fungsi KESALAHAN (IFERROR) sederhana dan berbicara sendiri:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Yaitu, untuk argumen pertama Anda memasukkan nilai yang akan diperiksa untuk kesalahan, dan untuk argumen kedua Anda menentukan apa yang harus dikembalikan jika kesalahan ditemukan.
Misalnya, rumus ini mengembalikan sel kosong jika nilai yang Anda cari tidak ditemukan:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Jika Anda ingin menampilkan pesan Anda sendiri alih-alih pesan kesalahan standar fungsi VPR, masukkan dalam tanda kutip, seperti ini:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: bekerja dengan fungsi ISERROR
Karena fungsi KESALAHAN muncul di Excel 2007, saat bekerja di versi sebelumnya Anda harus menggunakan kombinasi IF (JIKA) dan EOSHIBKA (ISERROR) seperti ini:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Misalnya rumus JIKA+ISERROR+VLOOKUP, mirip dengan rumus IFERROR+VLOOKUPditunjukkan di atas:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Itu saja untuk hari ini. Saya harap tutorial singkat ini akan membantu Anda mengatasi semua kemungkinan kesalahan. VPR dan membuat rumus Anda bekerja dengan benar.