() translation by (you can also view the original English article)
Ketika anda mau menemukan dan mengekstrak kolom data dari sebuah tabel dan menempatkannya di tempat lain, gunakan fungsi VLOOKUP. Fungsi ini bekerja dalam tiap versi Excel pada Windows dan Mac, dan juga Google Sheets. Fungsi ini membantu anda menemukan data dalam sebuah tabel menggunakan beberapa pengenal yang dimilikinya dengan tabel lain. Dua tabel ini dapat berada dalam beberapa sheet atau bahkan dalam beberapa workbook. Ada juga fungsi HLOOKUP, yang melakukan hal yang sama, tapi dengan data yang tersusun secara horizontal, dalam baris.
Fungsi MATCH dan INDEX sangat baik untuk digunakan ketika anda memperhatikan lokasi data spesifik, seperti kolom atau baris yang berisi nama seseorang.
Screencast
Jika anda ingin mengikuti tutorial menggunakan file Excel anda sendiri, anda boleh melakukannya. Atau jika anda ingin, unduh file zip yang ada bersama tutorial ini, yang berisi contoh workbook bernama vlookup example.xlsx.
Menggunakan VLOOKUP
Ketika VLOOKUP menemukan pengenal yang telah anda tentukan secara spesifik dalam source data, fungsi tersebut akan menemukan cell dalam baris tersebut dan mengembalikan informasi kembali kepada anda. Catat bahwa dalam source data, pengenal harus berada dalam kolom pertama pada tabel.
Syntax
Syntax fungsi VLOOKUP adalah:
=VLOOKUP(lookup value, table range, column number, [true/false])
Berikut makna dari argumen - argumen tersebut:
- Lookup value. Cell yang memiliki pengenal unik.
- Table range. Rentang cell yang memiliki pengenal dalam kolom pertama, diikuti dengan sisa data dalam kolom lainnya.
- Column number. Nomor kolom yang memiliki data yang sedang anda cari. Jangan keliru dengan huruf kolom. Pada ilustrasi di atas, nama negara berada dalam kolom 4.
- True/False. Argumen ini optional. True berarti nilai mendekati masih diterima, dan False berarti hanya nilai yang cocok yang diterima.
Kita ingin menemukan jumlah penjualan dari tabel pada ilustrasi di atas, jadi kita menggunakan argumen ini:
Menentukan rentang nama untuk membuat sebuah Referensi Tetap
Dalam Vlookup example.xlsx, lihat pada worksheet Sales Amounts. Kita akan memasukkan formula dalam B5, kemudian menggunakan fitur AutoFill untuk mengcopy formula ke bawah sheet. Itu berarti rentang tabel dalam formula haruslah sebuah referensi tetap. Cara yang baik untuk melakukan itu adalah dengan menentukan sebuah nama untuk rentang tabel.
Menentukan Sebuah Rentang Nama Dalam Excel
- Sebelum memasukkan formula, masuk ke worksheet source data.
- Pilih seluruh cell dari A4 (header untuk kolom Order #) ke bawah sampai H203. Cara cepat untuk melakukan ini adalah dgn mengklik A4, kemudian tekan Ctrl-Shift-End (Command-Shift-End pada Mac).
- Klik di dalam Name Box di atas kolom A (Name Box sekarang menampilkan A4).
- Ketik data, kemudian tekan Enter.
- Anda sekarang dapat menggunakan data dalam formula tanpa perlu mengetik $A$4:$H$203.
Menentukan rentang nama dalam Google Sheets
Pada Google Sheets, menentukan sebuah nama agak sedikit berbeda.
- Klik header kolom pertama pada source data, kemudian tekan Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow pada Mac). Itu akan memilih baris pada header kolom.
- Tekan Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow pada Mac). Itu akan memilih data aktual.
- Klik menu Data, kemudian pilih Named and protected ranges.
- Pada Name and protected ranges box pada bagian kanan, ketik data, kemudian klik Done.
Memasukkan Formula
Untuk memasukkan formula, masuk ke worksheet Sales Amounts dan klik pada cell B5.
Masukkan formula:
=VLOOKUP(A5,data,8,FALSE)
Tekan Enter.
Hasilnya adalah 40. Untuk mengisi nilai pada kolom di bawahnya, klik lagi pada cell B5, jika perlu. Letakkan pointer mouse pada titik AutoFill pada sudut kanan bawah cell, sehingga pointer berubah menjadi bentuk palang.
Klik ganda untuk mengisi nilai di bawah kolom.
Jika anda ingin, anda dapat menjalankan fungsi VLOOKUP pada beberapa kolom berikutnya untuk mengekstrak field yang lain, seperti nama belakang atau negara asal.
Menggunakan MATCH
Fungsi MATCH tidak menampilkan nilai data; anda menyediakan nilai yang anda cari, dan fungsi ini akan menampilkan posisi nilai tersebut. Itu sama seperti menanyakan lokasi #135 Main Street, dan mendapatkan jawaban bangunan ke 4 pada jalan tersebut.
Syntax
Syntax fungsi MATCH adalah:
=MATCH(lookup value, table range, [match type])
Arguments:
- Lookup value. Cell yang memiliki pengenal unik.
- Table range. Rentang cell yang sedang anda cari.
- Match type. Optional. Ini tentang bagaimana anda menentukan seberapa dekat kecocokan yang anda inginkan, sebagaimana berikut ini:
|
Nilai terbesar berikutnya |
-1 |
Values harus berada pada urutan dari terkecil ke terbesar. |
|
Target value |
0 |
Values dapat berada dalam urutan apapun. |
|
Nilai terendah berikutnya |
1 |
Default type. Value harus berada dalam urutan dari terbesar ke terkecil. |
Seperti pada fungsi VLOOKUP, anda akan mungkin menemukan fungsi MATCH lebih mudah digunakan jika anda menerapkan rentang nama. Masuk ke menu sheet Source Data, pilih dari B4 (header kolom ke urutan #) ke bawah, klik pada Name box di atas kolom A, dan menyebutnya order_number. Catat bahwa value berada pada urutan dari terbesar ke terkecil.
Masuk ke tab Match pada worksheet. Pada B5, masukkan fungsi MATCH:
=MATCH(A5,order_number,1)
Jika anda tidak menentukan rentang nama, anda akan menuliskan fungsi sebagai:
=MATCH(A5,'Source Data'!A5:A203,0)
Cara yang manapun, anda dapat melihat bahwa ini berada pada posisi ke 14 (membuatnya pada urutan ke 13).
Menggunakan INDEX
Fungsi INDEX merupakan kebalikan dari fungsi MATCH dan mirip dengan fungsi VLOOKUP. Anda memberitahu fungsi baris dan kolom data mana yang anda inginkan, dan fungsi tersebut memberitahu anda apa yang ada di dalam cell.
Syntax
Syntax fungsi INDEX adalah:
=INDEX(data range, row number, [column number])
Arguments:
- Data range. Sama seperti pada dua fungsi sebelumnya, ini adalah tabel data.
- Row number. Nomor baris data, dimana ini tidak berhubungan dengan baris terhadap worksheet. Jika rentang tabel dimulai dari baris ke 10 pada sheet, maka itu adalah baris #1.
- Column number. Nomor kolom rentang data. Jika rentang dimulai dari kolom E, itu adalah kolom #1.
Dokumentasi Excel akan memberitahu anda bahwa argument column number adalah pilihan, namun row number semacam pilihan juga. Jika rentang tabel hanya memiliki satu baris atau kolom, anda tidak harus menggunakan argument lainnya.
Masuk ke sheet Index pada workbook dan klik cell C6. Pertama - tama kita ingin menemukan apa yang berada di dalam baris 9, kolom 3 pada tabel. Pada formula, kita akan menggunakan rentang nama yang telah kita buat sebelumnya.
Masukkan formula:
=INDEX(data,A6,B6)
Fungsi tersebut akan menampilkan nama belakang pelanngan: Strevell. Ganti nilai A6 dan B6, dan hasil pada C6 akan menunjukkan hasil yang berbeda (catat bahwa banyak baris memiliki negara bagian dan nama produk yang sama).
Kesimpulan
Kemampuan sebuah worksheet untuk melihat worksheet lainnya dan mengekstrak data adalah sebuah tool yang hebat. Dengan cara ini, anda dapat memiliki satu sheet yang berisi seluruh data yang diperlukan untuk banyak tujuan, kemudian mengekstrak apa yang anda butuhkan untuk contoh spesifik.
