Mengapa kita perlu mempelajari fungsi ikon dan menu di Microsoft Excel

Kiat. Coba gunakan fungsi XLOOKUP baru, versi VLOOKUP yang disempurnakan yang bekerja ke segala arah dan mengembalikan kecocokan persis secara default, lebih mudah dan nyaman digunakan daripada pendahulunya

Gunakan VLOOKUP saat Anda perlu menemukan sesuatu di tabel atau rentang per baris. Misalnya mencari harga komponen otomotif berdasarkan nomor komponen, atau mencari nama karyawan berdasarkan ID karyawan

Dalam bentuknya yang paling sederhana, kata fungsi VLOOKUP

=VLOOKUP(Apa yang ingin Anda cari, di mana Anda ingin mencarinya, nomor kolom dalam rentang yang berisi nilai yang akan dikembalikan, mengembalikan hasil Terdekat atau Tepat – ditampilkan sebagai 1/BENAR, atau 0/FALSE)

Kiat. Rahasia VLOOKUP adalah mengatur data Anda sehingga nilai yang Anda cari (Buah) berada di sebelah kiri nilai yang dikembalikan (Jumlah) yang ingin Anda temukan

Detail teknis

Gunakan fungsi VLOOKUP untuk menemukan nilai dalam tabel

Sintaksis

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Sebagai contoh

• =VLOOKUP(A2,A10. C20,2, BENAR)

• =VLOOKUP("Febriansyah",B2. E7,2,SALAH)

• =VLOOKUP(A2,'Detail Klien'. SEBUAH. F,3,SALAH)

Nama argumen

Keterangan

lookup_value (wajib)

Nilai yang ingin Anda temukan. Nilai yang ingin Anda temukan harus berada di kolom pertama rentang sel yang Anda tentukan dalam argumen table_array

Misalnya, jika tabel-array menyertakan sel B2. D7, maka lookup_value Anda harus berada di kolom B

Lookup_value bisa berupa nilai atau referensi ke sel

table_array (wajib)

Rentang sel yang akan dicari oleh VLOOKUP untuk lookup_value dan nilai kembalian. Anda bisa menggunakan rentang atau tabel bernama, dan Anda bisa menggunakan nama dalam argumen, bukan referensi sel.  

Kolom pertama dalam rentang sel harus berisi lookup_value. Rentang sel juga perlu menyertakan nilai yang dikembalikan yang ingin Anda temukan

Pelajari cara memilih rentang di lembar kerja

col_index_num (wajib)

Nomor kolom (dimulai dengan 1 untuk kolom paling kiri dari table_array) yang berisi nilai yang dikembalikan

range_lookup (opsional)

Nilai logis yang menentukan apakah Anda ingin VLOOKUP menemukan hasil yang persis sama atau mendekati

• Estimasi kecocokan - 1/TRUE mengasumsikan kolom pertama dalam tabel diurutkan secara numerik atau alfabet, kemudian akan dicari nilai terdekat. Ini adalah metode default jika Anda tidak menentukannya. Misalnya, =VLOOKUP(90,A1. B100,2, BENAR)

• Pencocokan tepat - 0/FALSE mencari nilai tepat di kolom pertama. Misalnya, =VLOOKUP("Smith",A1. B100,2,SALAH)

Bagaimana cara memulai

Ada empat informasi yang akan dibutuhkan untuk membangun sintaks VLOOKUP

1. Nilai yang ingin Anda temukan, juga disebut nilai pencarian

2. Rentang tempat nilai pencarian berada. Ingatlah bahwa nilai pencarian harus selalu berada di kolom pertama dalam rentang agar VLOOKUP berfungsi dengan benar. Misalnya, jika nilai pencarian ada di sel C2, rentang Anda harus dimulai dengan C

3. Nomor kolom dalam rentang yang berisi nilai kembalian. Misalnya, jika Anda menentukan B2. D11 sebagai rentang, Anda harus menghitung B sebagai kolom pertama, C sebagai kolom kedua, dan seterusnya

4. Opsional, Anda dapat menentukan TRUE jika Anda menginginkan hasil yang mendekati atau FALSE jika Anda menginginkan hasil yang sama persis dari nilai kembalian. Jika Anda tidak menentukan apa pun, nilai default akan selalu BENAR atau sesuatu yang mendekatinya

Sekarang, letakkan semua hal yang disebutkan di atas sebagai berikut

=VLOOKUP(nilai pencarian, rentang yang berisi nilai pencarian, nomor kolom dalam rentang yang berisi nilai yang dikembalikan, Pencocokan Perkiraan (BENAR) atau Pencocokan Persis (FALSE))

Contoh

Berikut beberapa contoh VLOOKUP

Contoh 1

Contoh 2

Contoh 3

Contoh 4

Contoh 5

Gabungkan data dari beberapa tabel menjadi satu lembar kerja menggunakan VLOOKUP

Anda dapat menggunakan VLOOKUP untuk menggabungkan beberapa tabel menjadi satu, asalkan salah satu tabel memiliki kolom yang sama dengan semua tabel lainnya. Ini bisa sangat berguna jika Anda perlu berbagi buku kerja dengan orang yang memiliki versi Excel lebih lama yang tidak mendukung fitur data dengan beberapa tabel sebagai sumber data - dengan menggabungkan sumber ke dalam satu tabel dan mengubah sumber data dari fitur data ke tabel baru, data fitur dapat digunakan di versi Excel yang lebih lama (selama fitur data itu sendiri didukung oleh versi yang lebih lama)

Disini kolom A-F dan H memiliki nilai atau rumus yang hanya menggunakan nilai pada lembar kerja, dan kolom lainnya menggunakan VLOOKUP dan nilai kolom A (Kode Klien) dan kolom B (Pengacara) untuk mendapatkan data dari tabel lainnya

1. Salin tabel yang memiliki bidang umum ke lembar kerja baru, dan beri nama

2. Klik Data > Alat Data > Hubungan untuk membuka kotak dialog Kelola Hubungan

   

3. Untuk setiap hubungan yang terdaftar, perhatikan yang berikut ini

   • Bidang yang menautkan tabel (tercantum dalam tanda kurung di kotak dialog). Ini adalah lookup_value untuk rumus VLOOKUP Anda

   • Nama Tabel Pencarian Terkait. Ini adalah table_array dalam rumus VLOOKUP Anda

   • Bidang (kolom) di Tabel Pencarian Terkait yang memiliki data yang Anda inginkan di kolom baru Anda. Informasi ini tidak ditampilkan dalam dialog Kelola Kontak - Anda harus melihat Tabel Pencarian Terkait untuk mengetahui bidang mana yang ingin Anda ambil. Anda ingin mencatat nomor kolom (A=1) - ini adalah col_index_num dalam rumus Anda

4. Untuk menambahkan bidang ke tabel baru, masukkan rumus VLOOKUP Anda di kolom kosong pertama menggunakan informasi yang Anda kumpulkan di langkah 3

   Dalam contoh kita, kolom G menggunakan Pengacara ( lookup_value ) untuk mendapatkan data Billing Tariff dari kolom keempat (col_index_num = 4) tabel lembar kerja Pengacara, tblAttorneys ( table_array ), dengan rumus =VLOOKUP([@Attorney],tbl_Attorneys, 4, SALAH)

   Rumus juga dapat menggunakan referensi sel dan referensi rentang. Dalam contoh kita, itu adalah =VLOOKUP(A2,'Pengacara'. SEBUAH. D,4,SALAH)

5. Lanjutkan menambahkan bidang hingga Anda memiliki semua bidang yang diperlukan. Jika Anda mencoba menyiapkan buku kerja yang berisi fitur data yang menggunakan beberapa tabel, ubah sumber data fitur data ke tabel baru

(adsbygoogle = window.adsbygoogle || []).push({}); Masalah umum

Masalah

Apa yang salah

Nilai yang dikembalikan salah

Jika range_lookup BENAR atau dihilangkan, kolom pertama harus diurutkan menurut abjad. Jika kolom pertama tidak diurutkan, nilai pengembaliannya mungkin sesuatu yang tidak Anda harapkan. Urutkan kolom itu terlebih dahulu, atau gunakan FALSE untuk pencocokan persis

#N/A dalam sel

• Jika range_lookup adalah TRUE, maka jika nilai pada lookup_value lebih kecil dari nilai terkecil pada kolom pertama table_array, Anda akan mendapatkan nilai error #N/A

• Jika range_lookup adalah FALSE, nilai kesalahan #N/A menunjukkan angka persisnya tidak ditemukan

Untuk informasi selengkapnya tentang memperbaiki kesalahan #N/A di VLOOKUP, lihat Cara memperbaiki kesalahan #N/A di fungsi VLOOKUP

#REF. di dalam sel

Jika col_index_num lebih besar dari jumlah kolom dalam array-tabel, Anda akan mendapatkan #REF. nilai kesalahan

Untuk informasi lebih lanjut tentang mengatasi #REF. di VLOOKUP, lihat Cara memperbaiki kesalahan #REF

#NILAI. di dalam sel

Jika table_array kurang dari 1, Anda akan mendapatkan #VALUE. nilai kesalahan

Untuk informasi lengkap mengatasi error #VALUE. di VLOOKUP, lihat Cara memperbaiki kesalahan #VALUE. dalam fungsi VLOOKUP

#NAMA?

Nilai kesalahan #NAME? . Untuk menemukan nama seseorang, pastikan Anda menggunakan tanda kutip di sekitar nama dalam rumus. Misalnya, masukkan nama sebagai "Febriansyah" di =VLOOKUP("Febriansyah", B2. E7,2,SALAH)

Untuk informasi selengkapnya, lihat Cara memperbaiki kesalahan #NAME

#meluap kesalahan. di dalam sel

kesalahan #SPILL. Ini biasanya berarti bahwa rumus Anda bergantung pada irisan implisit untuk nilai pencarian, dan menggunakan seluruh kolom sebagai referensi. Misalnya, =VLOOKUP(A. A A. C,2,SALAH). Anda dapat mengatasi masalah tersebut dengan menutup referensi pencarian dengan operator @ seperti ini. =VLOOKUP(@A. A A. C,2,SALAH). Atau, Anda dapat menggunakan metode VLOOKUP tradisional dan merujuk ke satu sel, bukan seluruh kolom. =VLOOKUP(A2,A. C,2,SALAH)

Praktik terbaik

Melakukan hal ini

Mengapa

Gunakan referensi absolut untuk range_lookup

Menggunakan referensi absolut memungkinkan Anda mengisi rumus sehingga rumus selalu mencari rentang pencarian yang sama

Pelajari cara menggunakan referensi sel absolut

Jangan simpan nomor atau tanggal sebagai teks

Saat mencari nilai numerik atau tanggal, pastikan data di kolom pertama table_array tidak disimpan sebagai nilai teks. Jika tidak, VLOOKUP dapat mengembalikan nilai yang salah atau tidak diharapkan

Urutkan kolom pertama

Urutkan kolom pertama dari table_array sebelum menggunakan VLOOKUP ketika range_lookup adalah BENAR

Gunakan karakter wildcard

Jika range_lookup adalah FALSE dan lookup_value adalah teks, Anda dapat menggunakan karakter wildcard—tanda tanya (?) dan tanda bintang (*)—dalam lookup_value. Tanda tanya cocok dengan karakter tunggal apa pun. Tanda bintang cocok dengan urutan karakter apa pun. Jika Anda ingin menemukan tanda tanya atau tanda bintang yang sebenarnya, ketikkan tanda gelombang (~) sebelum karakter

Misalnya, =VLOOKUP("Font?",B2. E7,2,FALSE) akan menemukan semua contoh Fontana dengan variabel huruf terakhir

Pastikan data Anda tidak mengandung karakter yang salah

Saat mencari nilai teks di kolom pertama, pastikan data di kolom pertama tidak memiliki spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan kurva ( ' atau " yang tidak konsisten), atau karakter yang tidak dapat dicetak. Dalam hal ini, VLOOKUP dapat mengembalikan nilai yang salah atau tidak diharapkan

Untuk mendapatkan hasil yang akurat, coba gunakan fungsi CLEAN atau fungsi TRIM untuk menghapus spasi tambahan setelah nilai tabel di dalam sel

butuh lebih banyak bantuan?

Anda dapat bertanya kapan saja kepada pakar di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas Jawaban

Mengapa kita harus belajar Microsoft Excel?

Mengapa Microsoft Excel sangat penting dalam dunia kerja?

Apa fungsi utama Microsoft Excel?