Excel VLOOKUP sangat berguna, tetapi tidak akan berfungsi saat Anda memiliki situasi tertentu. Hari ini, lihat cara memecahkan masalah VLOOKUP.
VLOOKUP adalah fungsi favorit saya di Excel. Jika Anda dapat melakukan VLOOKUP, Anda dapat memecahkan banyak masalah di Excel. Tetapi ada hal-hal yang dapat membuat VLOOKUP tersandung. Topik ini berbicara tentang beberapa di antaranya.
Tapi pertama-tama, dasar-dasar VLOOKUP dalam bahasa Inggris.
Data di A: C berasal dari departemen IT. Anda meminta penjualan berdasarkan item dan tanggal. Mereka memberi Anda nomor Item. Anda membutuhkan Deskripsi Item. Daripada menunggu departemen TI untuk menjalankan kembali data, Anda menemukan tabel yang ditunjukkan di kolom F: G.
Anda ingin VLOOKUP menemukan item dalam A2 saat mencari melalui kolom pertama tabel di $ F $ 3: $ G $ 30. Saat VLOOKUP menemukan kecocokan di F7, Anda ingin VLOOKUP mengembalikan deskripsi yang ditemukan di kolom kedua tabel. Setiap VLOOKUP yang mencari kecocokan persis harus diakhiri dengan False (atau nol, yang setara dengan False). Rumus di bawah ini disiapkan dengan benar.
Perhatikan bahwa Anda menggunakan F4 untuk menambahkan empat tanda dolar ke alamat tabel pencarian. Saat Anda menyalin rumus ke bawah kolom D, Anda memerlukan alamat untuk tabel pencarian agar tetap konstan. Ada dua alternatif umum: Anda dapat menentukan seluruh kolom F: G sebagai tabel pemeta. Atau, Anda bisa memberi nama F3: G30 dengan nama seperti ItemTable. Jika Anda menggunakan =VLOOKUP(A2,ItemTable,2,False), rentang bernama bertindak seperti referensi absolut.
Setiap kali Anda melakukan banyak VLOOKUP, Anda perlu mengurutkan kolom VLOOKUP. Urutkan ZA, dan kesalahan # N / A akan muncul di urutan teratas. Dalam hal ini, ada satu. Item BG33-9 hilang dari tabel pencarian. Mungkin itu salah ketik. Mungkin itu barang baru. Jika baru, sisipkan baris baru di mana saja di tengah tabel pencarian Anda dan tambahkan item baru.
Cukup normal untuk memiliki beberapa kesalahan # N / A. Namun pada gambar di bawah, rumus yang persis sama tidak menghasilkan apa-apa selain # N / A. Ketika ini terjadi, saya melihat apakah saya dapat menyelesaikan VLOOKUP pertama. Anda mencari BG33-8 yang ditemukan di A2. Mulailah menelusuri kolom pertama tabel pencarian. Seperti yang Anda lihat, nilai pencocokan jelas ada di F10. Mengapa Anda bisa melihat ini, tetapi Excel tidak bisa melihatnya?
Pergi ke setiap sel dan tekan tombol F2. Ini F10. Perhatikan bahwa kursor penyisipan muncul tepat setelah angka 8.
Berikut adalah sel A2 dalam mode Edit. Kursor penyisipan berjarak beberapa spasi dari 8. Ini adalah tanda bahwa pada titik tertentu, data ini disimpan dalam kumpulan data COBOL lama. Kembali ke COBOL, jika bidang Item didefinisikan sebagai 10 karakter dan Anda hanya mengetik 6 karakter, COBOL akan mengisinya dengan 4 spasi ekstra.
Solusinya? Alih-alih mencari A2, cari TRIM(A2).
Fungsi TRIM () menghilangkan spasi di depan dan di belakangnya. Jika Anda memiliki banyak spasi di antara kata, TRIM akan mengubahnya menjadi satu spasi. Pada gambar di bawah ada spasi sebelum dan sesudah kedua nama di A1. =TRIM(A1)menghapus semua kecuali satu ruang di A3.
Ngomong-ngomong, bagaimana jika masalahnya adalah spasi di kolom F dan bukan di kolom A? Tambahkan kolom fungsi TRIM () ke E, menunjuk ke kolom F. Salin itu dan tempel sebagai nilai di F untuk membuat pencarian mulai bekerja lagi.
Alasan paling umum lainnya yang membuat VLOOKUP tidak berfungsi ditampilkan di sini. Kolom F memiliki bilangan real. Kolom A memiliki teks yang terlihat seperti angka.
Pilih semua kolom A. Tekan alt = "" + D, E, F. Ini melakukan Teks default ke Kolom dan akan mengubah semua nomor teks menjadi bilangan real. Pencarian mulai bekerja kembali.
Menonton video
- VLOOKUP memecahkan banyak masalah
- Masalah umum VLOOKUP:
- Jika VLOOKUP mulai berfungsi, tetapi # N / A menjadi lebih menonjol: lupa $ di tabel pencarian
- Beberapa item # N / A: hilang dari tabel
- Tak satu pun dari VLOOKUP yang berfungsi: periksa spasi tertinggal
- Hapus spasi di belakang dengan TRIM
- Angka dan angka disimpan sebagai teks
- Pilih kedua kolom dan gunakan alt = "" + DEF
- Episode mencakup lelucon yang dianggap lucu oleh akuntan dan orang TI, tetapi karena alasan yang berbeda
Transkrip Video
Belajar Excel dari podcast, episode 2027 - Memecahkan Masalah VLOOKUP!
Baiklah, membuat podcasting seluruh buku ini, klik "i" di pojok kanan atas untuk membuka playlist!
VLOOKUP Adalah fungsi favorit saya di semua Excel, dan saya selalu menceritakan lelucon ini di salah satu seminar saya, dan itu membuat tertawa apakah Anda orang IT atau bukan orang IT. Saya selalu berkata, “Lihat, kami memiliki kumpulan data ini di sini di sebelah kiri, dan saya dapat melihat data itu dan mengatakan bahwa data tersebut berasal dari departemen TI karena itu persis seperti yang saya minta, tetapi sebenarnya bukan yang saya butuhkan. Saya meminta tanggal dan kuantitas barang, dan mereka memberi saya tanggal dan jumlah nomor barang, tetapi mereka tidak repot-repot memberi saya deskripsi, bukan? ” Dan para akuntan selalu menertawakan hal ini, karena ini terjadi pada mereka sepanjang waktu, dan orang-orang TI berkata, "Baiklah, saya memberikan apa yang Anda minta, itu bukan salah saya!" Jadi mereka berdua berpikir itu lucu untuk alasan yang berbeda jadi, Anda tahu, dan orang TI sedang sibuk, dia tidak bisa kembali menulis ulang kueri,jadi kita harus melakukan sesuatu untuk menyelamatkan ini sendiri.
Jadi tabel kecil ini saya salin dari tempat lain di komputer saya, dalam bahasa Inggris sederhana, yang dilakukan VLOOKUP adalah mengatakan "Hei, kami memiliki nomor item W25-6." Kami memiliki meja di sini, saya ingin menelusuri kolom pertama dari tabel sampai saya menemukan nomor item itu, dan kemudian mengembalikan sesuatu dari baris itu. Baiklah, dalam hal ini, yang saya inginkan adalah kolom ke-2 dari baris itu. Dan kemudian di akhir setiap VLOOKUP kita harus meletakkan FALSE atau 0. Sekarang di sini, setelah saya memilih range, saya akan menekan tombol F4, dan kemudian saya ingin kolom ke-2 dan kemudian FALSE untuk tepatnya pertandingan. Jangan pernah memilih perkiraan kecocokan, tidak pernah, tidak pernah! Ini bukan pertandingan perkiraan, ini hal yang sangat istimewa, tidak selalu berhasil. Jika Anda ingin menyatakan kembali nomor Anda ke Securities and Exchange Commission, silakan gunakan,BENAR atau biarkan saja, SALAH. Tetapi setiap VLOOKUP yang pernah Anda buat harus diakhiri dengan, FALSE atau, 0, 0 lebih pendek dari FALSE, Anda seharusnya meletakkan FALSE di sana, tetapi 0 adalah sama dengan FALSE.
Baiklah sekarang, pemecahan masalah, hal pertama, ketika Anda melakukan sejumlah besar VLOOKUP, sangat normal untuk memiliki beberapa # N / As, bukan? Dan saya selalu menemukan # N / A dengan pergi Data, ZA, yang membawa # N / As ke atas, tepat di sana, BG33-9, entah itu salah ketik atau itu barang baru, oke, dan kami mendapat untuk memecahkan masalah. Jadi di sini di sebelah kanan saya memiliki data baru, saya akan memotongnya, dan kemudian Alt + IED, masukkan sel-sel itu di tengah, tidak harus alfabet, tabel ini tidak harus diurutkan. Saat Anda menggunakan, versi SALAH, tabelnya tidak- Anda bisa meletakkannya di mana saja Anda mau, saya tidak meletakkannya di akhir karena saya tidak perlu menulis ulang rumusnya, saya hanya ingin rumusnya kerja. Baiklah, jadi beberapa # N / A, sangat, sangat normal, tapi lihat ini.
Ketika Anda memulai dengan jawaban yang berhasil, tetapi kemudian # N / A mulai muncul sedikit sering, dan akhirnya muncul sepenuhnya, itu pertanda pasti bahwa Anda tidak mengunci referensi tabel. Baiklah lihat, jadi di sini tabel bergerak saat saya menyalin rumus ke bawah, benar, jadi saya beruntung mendapatkan beberapa yang ada di akhir daftar. Tapi akhirnya saya turun ke titik di mana ia melihat ke sini dalam sel yang benar-benar kosong, dan tentu saja tidak ada yang ditemukan. Baiklah, jadi itu hal pertama, Anda mendapatkan beberapa yang berfungsi, beberapa # N / A, beberapa lagi yang berfungsi, dan kemudian semua # N / A adalah sisa jalannya - pertanda pasti bahwa Anda tidak meletakkan $ masuk
Kembali saja, F2 untuk mode Edit, pilih titik dua, tekan F4, yang memasukkan semua $ masuk, klik dua kali untuk menembaknya, dan semuanya mulai bekerja lagi, oke. Berikutnya, rumus yang sama persis, rumusnya sempurna, BG33-8 lihat, kita tidak mendapatkan yang benar. Baiklah, jadi saya lihat, BG33-8, hei, itu dia, itu di sana, warnanya merah, saya seharusnya melihatnya! Jadi saya datang ke yang ini di sisi kanan, saya tekan F2, dan saya melihat titik penyisipan yang berkedip, dan lihat, itu tepat setelah 8, seperti itu, dan kemudian saya datang ke sini dan saya tekan F2, ada beberapa ruang tertinggal di sana. Ini sangat, sangat umum di masa COBOL, mereka akan berkata "Anda tahu, lihat, kami akan memberi Anda 10 spasi untuk nomor item, dan jika Anda tidak mengetik semua 10 spasi, mereka akan mengisi spasi . ” Dan ini sangat umum,dan solusi terbaik di sini adalah menyingkirkan spasi di depan dan di belakang dengan fungsi TRIM. Alih-alih A2 gunakan TRIM (A2), klik dua kali untuk menembaknya, baiklah, BG33-9 masih kembali ke tabel lain.
Baiklah, supaya Anda mengerti cara kerja TRIM, jadi saya mengetik banyak spasi, John, banyak spasi, Durran, dan banyak spasi, lalu saya gabungkan * sebelum dan sesudah sehingga Anda bisa melihat seperti apa tampilannya . Ketika saya meminta TRIM (P4), kami menyingkirkan semua ruang utama, semua ruang tambahan, dan kemudian beberapa ruang interior dikurangi menjadi satu ruang. Baiklah, jadi Anda bisa melihat, semacam visualisasi di sana, bahwa mereka menyingkirkan ruang depan dan belakang, setiap ruang berlipat ganda di tengah menjadi satu ruang seperti itu. Jadi TRIM, alat hebat, hebat di gudang senjata Anda, baiklah, ini satu lagi yang sangat umum.
Jika bukan spasi di belakangnya, maka hal paling umum yang pernah saya lihat, adalah di mana di sini kita memiliki bilangan asli, dan di sini kita memiliki nomor yang disimpan sebagai teks. Dan VLOOKUP tidak akan melihatnya sebagai kecocokan, meskipun 4399, ini adalah angka, ini teks, tidak berfungsi. Cara tercepat untuk mengonversi kolom teks menjadi angka, pilih kolom, 3 huruf berurutan, alt = "" DEF, dan tiba-tiba, VLOOKUP kami mulai berfungsi lagi, kiat hebat dari sekitar 1500 podcast yang lalu. Baiklah, jadi itu adalah masalah VLOOKUP yang paling umum, entah Anda lupa $, atau Anda memiliki spasi tambahan, atau Anda memiliki angka dan angka yang disimpan sebagai teks. Semua tips ini ada di buku “MrExcel XL” ini, klik “i” di pojok kanan atas, Anda bisa membeli bukunya.
Baiklah, rekap cepat: VLOOKUP memecahkan banyak masalah, jika VLOOKUP mulai bekerja tetapi # N / A! menjadi lebih menonjol, Anda lupa meletakkan $ di tabel pencarian. Jika ada beberapa # N / A, itu hanya item yang hilang dari tabel. Jika tidak ada VLOOKUP yang berfungsi dan teksnya, periksa spasi di belakangnya, Anda dapat menggunakan fungsi TRIM. Jika Anda memiliki angka dan angka yang disimpan sebagai teks, pilih salah satu kolom, yang memiliki teks, lalu lakukan alt = "" DEF harus mengembalikan semua itu ke angka.
Baiklah, hei, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2027.xlsx
