Semua rahasia fungsi VLOOKUP Excel untuk menemukan data dalam tabel dan mengekstraknya ke tabel lain

2024 Pengarang: Malcolm Clapton | [email protected]. Terakhir diubah: 2023-12-17 04:00

Setelah membaca artikel ini, Anda tidak hanya akan mempelajari cara menemukan data dalam spreadsheet Excel dan mengekstraknya ke yang lain, tetapi juga teknik yang dapat digunakan bersama dengan fungsi VLOOKUP.

Saat bekerja di Excel, sangat sering ada kebutuhan untuk menemukan data di satu tabel dan mengekstraknya ke tabel lain. Jika Anda masih tidak tahu bagaimana melakukan ini, maka setelah membaca artikel, Anda tidak hanya akan belajar bagaimana melakukan ini, tetapi juga mencari tahu dalam kondisi apa Anda dapat memeras kinerja maksimum dari sistem. Sebagian besar teknik yang sangat efektif yang harus digunakan bersama dengan fungsi VLOOKUP dipertimbangkan.

Bahkan jika Anda telah menggunakan fungsi VLOOKUP selama bertahun-tahun, maka dengan probabilitas tinggi artikel ini akan bermanfaat bagi Anda dan tidak akan membuat Anda acuh tak acuh. Misalnya, sebagai spesialis TI, dan kemudian menjadi kepala di bidang TI, saya telah menggunakan VLOOKUP selama 15 tahun, tetapi saya hanya berhasil menangani semua nuansa sekarang, ketika saya mulai mengajar orang Excel secara profesional.

VLOOKUP adalah singkatan dari vvertikal NSinspeksi. Demikian juga, VLOOKUP - LOOKUP Vertikal. Nama fungsi itu sendiri mengisyaratkan kepada kita bahwa ia mencari di baris tabel (secara vertikal - mengulangi baris dan memperbaiki kolom), dan bukan di kolom (secara horizontal - mengulangi kolom dan memperbaiki baris). Perlu dicatat bahwa VLOOKUP memiliki saudara perempuan - itik jelek yang tidak akan pernah menjadi angsa - ini adalah fungsi HLOOKUP. HLOOKUP, sebagai lawan dari VLOOKUP, melakukan pencarian horizontal, tetapi konsep Excel (dan memang konsep organisasi data) menyiratkan bahwa tabel Anda memiliki lebih sedikit kolom dan lebih banyak baris. Itulah mengapa kita perlu mencari berdasarkan baris berkali-kali lebih sering daripada berdasarkan kolom. Jika Anda terlalu sering menggunakan fungsi HLO di Excel, maka kemungkinan Anda tidak memahami sesuatu dalam hidup ini.

Sintaksis

Fungsi VLOOKUP memiliki empat parameter:

= VLOOKUP (;; [;]), di sini:

- nilai yang diinginkan (jarang) atau referensi ke sel yang berisi nilai yang diinginkan (sebagian besar kasus);

- referensi ke rentang sel (array dua dimensi), di kolom PERTAMA (!) yang nilai parameternya akan dicari;

- nomor kolom dalam rentang dari mana nilai akan dikembalikan;

- ini adalah parameter yang sangat penting yang menjawab pertanyaan apakah kolom pertama dari rentang diurutkan dalam urutan menaik. Jika array diurutkan, kami menentukan nilai TRUE atau 1, jika tidak - FALSE atau 0. Jika parameter ini dihilangkan, defaultnya adalah 1.

Saya yakin banyak dari mereka yang mengetahui fungsi VLOOKUP sebagai serpihan, setelah membaca deskripsi parameter keempat, mereka mungkin merasa tidak nyaman, karena mereka terbiasa melihatnya dalam bentuk yang sedikit berbeda: biasanya mereka berbicara tentang kecocokan yang tepat ketika pencarian (FALSE atau 0) atau pemindaian rentang (TRUE atau 1).

Sekarang Anda perlu menyaring dan membaca paragraf berikutnya beberapa kali sampai Anda merasakan arti dari apa yang dikatakan sampai akhir. Setiap kata penting di sana. Contoh akan membantu Anda mengetahuinya.

Bagaimana tepatnya rumus VLOOKUP bekerja?

• Rumus tipe I. Jika parameter terakhir dihilangkan atau ditentukan sama dengan 1, maka VLOOKUP mengasumsikan bahwa kolom pertama diurutkan dalam urutan menaik, sehingga pencarian berhenti pada baris yang segera mendahului garis yang berisi nilai lebih besar dari yang diinginkan … Jika tidak ada string seperti itu yang ditemukan, baris terakhir dari rentang akan dikembalikan.

  

• Formula II. Jika parameter terakhir ditentukan sebagai 0, maka VLOOKUP memindai kolom pertama dari array secara berurutan dan segera menghentikan pencarian ketika kecocokan pertama dengan parameter ditemukan, jika tidak, kode kesalahan # N / A (# N / A) dikembalikan.

  

Alur kerja rumus

VPR tipe I

VPR tipe II

Akibat wajar untuk rumus bentuk I

1. Rumus dapat digunakan untuk mendistribusikan nilai di seluruh rentang.
2. Jika kolom pertama berisi nilai duplikat dan diurutkan dengan benar, maka baris terakhir dengan nilai duplikat akan dikembalikan.
3. Jika Anda mencari nilai yang jelas-jelas lebih besar dari yang dapat ditampung kolom pertama, maka Anda dapat dengan mudah menemukan baris terakhir tabel, yang bisa sangat berharga.
4. Tampilan ini akan mengembalikan kesalahan # N / A hanya jika tidak menemukan nilai yang kurang dari atau sama dengan yang diinginkan.
5. Agak sulit untuk memahami bahwa rumus mengembalikan nilai yang salah jika array Anda tidak diurutkan.

Akibat wajar untuk formula tipe II

Jika nilai yang diinginkan muncul beberapa kali di kolom pertama larik, maka rumus akan memilih baris pertama untuk pengambilan data selanjutnya.

Performa VLOOKUP

Anda telah mencapai klimaks artikel. Tampaknya, yah, apa bedanya jika saya menentukan nol atau satu sebagai parameter terakhir? Pada dasarnya, semua orang menunjukkan, tentu saja, nol, karena ini cukup praktis: Anda tidak perlu khawatir menyortir kolom pertama dari array, Anda dapat langsung melihat apakah nilainya ditemukan atau tidak. Tetapi jika Anda memiliki beberapa ribu rumus VLOOKUP di lembar Anda, Anda akan melihat bahwa VLOOKUP II lambat. Pada saat yang sama, biasanya semua orang mulai berpikir:

• Saya membutuhkan komputer yang lebih kuat;
• Saya membutuhkan formula yang lebih cepat, misalnya, banyak orang tahu tentang INDEX + MATCH, yang seharusnya lebih cepat 5-10%.

Dan beberapa orang berpikir bahwa segera setelah Anda mulai menggunakan VLOOKUP tipe I dan memastikan bahwa kolom pertama diurutkan dengan cara apa pun, kecepatan VLOOKUP akan meningkat 57 kali lipat. Saya menulis dengan kata-kata - LIMA PULUH TUJUH KALI! Bukan 57%, tapi 5.700%. Saya memeriksa fakta ini dengan cukup andal.

Rahasia kerja cepat tersebut terletak pada kenyataan bahwa algoritma pencarian yang sangat efisien dapat diterapkan pada array yang diurutkan, yang disebut pencarian biner (metode separuh, metode dikotomi). Jadi VLOOKUP tipe I menerapkannya, dan VLOOKUP tipe II mencari tanpa optimasi sama sekali. Hal yang sama berlaku untuk fungsi MATCH, yang menyertakan parameter serupa, dan fungsi LOOKUP, yang hanya berfungsi pada array yang diurutkan dan disertakan dalam Excel untuk kompatibilitas dengan Lotus 1-2-3.

Kekurangan dari rumus

Kerugian VLOOKUP jelas: pertama, ia hanya mencari di kolom pertama dari array yang ditentukan, dan kedua, hanya di sebelah kanan kolom ini. Dan seperti yang Anda pahami, mungkin saja kolom yang berisi informasi yang diperlukan berada di sebelah kiri kolom tempat kita akan mencari. Kombinasi rumus INDEX + MATCH yang telah disebutkan tidak memiliki kelemahan ini, yang menjadikannya solusi paling fleksibel untuk mengekstraksi data dari tabel dibandingkan dengan VLOOKUP (VLOOKUP).

Beberapa aspek penerapan rumus dalam kehidupan nyata

Pencarian jangkauan

Ilustrasi klasik dari pencarian rentang adalah tugas menentukan diskon berdasarkan ukuran pesanan.

Cari string teks

Tentu saja, VLOOKUP tidak hanya mencari angka, tetapi juga teks. Harus diingat bahwa formula tidak membedakan kasus karakter. Jika Anda menggunakan wildcard, Anda dapat mengatur pencarian fuzzy. Ada dua wildcard: "?" - mengganti satu karakter apa pun dalam string teks, "*" - mengganti sejumlah karakter apa pun.

Ruang pertempuran

Pertanyaan yang sering muncul adalah bagaimana mengatasi masalah ruang ekstra saat melakukan pencarian. Jika tabel pencarian masih dapat dibersihkan darinya, maka parameter pertama dari rumus VLOOKUP tidak selalu terserah Anda. Oleh karena itu, jika ada risiko penyumbatan sel dengan ruang ekstra, maka Anda dapat menggunakan fungsi TRIM untuk membersihkannya.

Format data yang berbeda

Jika parameter pertama dari fungsi VLOOKUP merujuk ke sel yang berisi angka, tetapi disimpan dalam sel sebagai teks, dan kolom pertama larik berisi angka dalam format yang benar, maka pencarian akan gagal. Situasi sebaliknya juga mungkin terjadi. Masalahnya dapat dengan mudah diselesaikan dengan menerjemahkan parameter 1 ke dalam format yang diperlukan:

= VLOOKUP (−− D7; Produk! $ A $ 2: $ C $ 5; 3; 0) - jika D7 berisi teks dan tabel berisi angka;

= VLOOKUP (D7 & ""); Produk!$A$2:$C$5; 3; 0) - dan sebaliknya.

Omong-omong, Anda dapat menerjemahkan teks ke dalam beberapa cara sekaligus, pilih:

• Negasi ganda -D7.
• Perkalian dengan satu D7 * 1.
• Nol tambahan D7 + 0.
• Menaikkan ke kekuatan pertama D7 ^ 1.

Mengonversi angka menjadi teks dilakukan melalui penggabungan dengan string kosong, yang memaksa Excel untuk mengonversi tipe data.

Cara menekan # N / A

Ini sangat mudah dilakukan dengan fungsi IFERROR.

Misalnya: = IFERROR (VLOOKUP (D7; Produk! $A$2:$C$5; 3; 0); "").

Jika VLOOKUP mengembalikan kode kesalahan # N / A, maka IFERROR akan mencegatnya dan mengganti parameter 2 (dalam hal ini string kosong), dan jika tidak terjadi kesalahan, maka fungsi ini akan berpura-pura tidak ada sama sekali, tetapi hanya ada VLOOKUP yang mengembalikan hasil normal.

Himpunan

Seringkali mereka lupa untuk membuat referensi array menjadi absolut, dan ketika merentangkan array "mengambang". Ingatlah untuk menggunakan $A$2:$C$5 sebagai ganti A2:C5.

Sebaiknya tempatkan larik referensi pada lembar buku kerja yang terpisah. Itu tidak akan terinjak, dan itu akan lebih aman.

Ide yang lebih baik lagi adalah mendeklarasikan array ini sebagai rentang bernama.

Banyak pengguna, saat menentukan array, menggunakan konstruksi seperti A: C, menentukan seluruh kolom. Pendekatan ini memiliki hak untuk eksis, karena Anda terhindar dari keharusan melacak fakta bahwa array Anda menyertakan semua string yang diperlukan. Jika Anda menambahkan baris ke lembar dengan larik asli, maka rentang yang ditentukan sebagai A: C tidak perlu disesuaikan. Tentu saja, konstruksi sintaksis ini memaksa Excel untuk melakukan sedikit lebih banyak pekerjaan daripada menentukan rentang dengan tepat, tetapi overhead ini dapat diabaikan. Kita berbicara tentang seperseratus detik.

Nah, di ambang jenius - untuk mengatur array dalam bentuk.

Menggunakan fungsi COLUMN untuk menentukan kolom yang akan diekstrak

Jika tabel tempat Anda mengambil data menggunakan VLOOKUP memiliki struktur yang sama dengan tabel pencarian, tetapi hanya berisi lebih sedikit baris, maka Anda dapat menggunakan fungsi COLUMN () di VLOOKUP untuk secara otomatis menghitung jumlah kolom yang akan diambil. Dalam hal ini, semua rumus VLOOKUP akan sama (disesuaikan untuk parameter pertama, yang berubah secara otomatis)! Perhatikan bahwa parameter pertama memiliki koordinat kolom absolut.

Membuat kunci komposit dengan & "|" &

Jika perlu mencari beberapa kolom secara bersamaan, maka perlu membuat kunci komposit untuk pencarian. Jika nilai yang dikembalikan tidak tekstual (seperti halnya dengan bidang "Kode"), tetapi numerik, maka rumus SUMIFS yang lebih nyaman akan cocok untuk ini dan kunci kolom komposit tidak diperlukan sama sekali.

Ini adalah artikel pertama saya untuk Lifehacker. Jika Anda menyukainya, saya mengundang Anda untuk mengunjungi, dan juga dengan senang hati membaca di komentar tentang rahasia Anda menggunakan fungsi VLOOKUP dan sejenisnya. Terima kasih.:)