Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

Bagaimana Mengekstrak Data Dari Spreadsheet Menggunakan VLOOKUP, MATCH dan INDEX

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Indonesian (Bahasa Indonesia) translation by Yosef Andreas (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.

unique identifiers for vlookup
Sebuah pengenal unik harus tampak seperti nomor seri, dimana tidak ada dua nomor yang sama dalam tabel yang sama.

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:

syntax of vlookup
Syntax fungsi VLOOKUP

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

  1. Sebelum memasukkan formula, masuk ke worksheet source data.
  2. 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).
  3. Klik di dalam Name Box di atas kolom A (Name Box sekarang menampilkan A4).
  4. Ketik data, kemudian tekan Enter.
  5. Anda sekarang dapat menggunakan data dalam formula tanpa perlu mengetik $A$4:$H$203.
Name box displaying a range name
Name box biasanya menampilkan alamat cell terkini. Klik di dalamnya dan ketikkan nama untuk menentukan sebuah rentang.

Menentukan rentang nama dalam Google Sheets

Pada Google Sheets, menentukan sebuah nama agak sedikit berbeda.

  1. 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.
  2. Tekan Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow pada Mac). Itu akan memilih data aktual.
  3. Klik menu Data, kemudian pilih Named and protected ranges.
  4. Pada Name and protected ranges box pada bagian kanan, ketik data, kemudian klik Done.
Data menu in Google Sheets
Menentukan rentang nama pada Google Sheets

Memasukkan Formula

Untuk memasukkan formula, masuk ke worksheet Sales Amounts dan klik pada cell B5.

Masukkan formula:

=VLOOKUP(A5,data,8,FALSE)

Tekan Enter.

entering the vlookup function
Memasukkan fungsi VLOOKUP

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.

mouse pointer on the autofill dot
Ketika anda meletakkan pointer mouse pada titik bagian sudut kanan bawah cell, pointer tersebut akan berubah bentuk menjadi palang sebagai AutoFill

Klik ganda untuk mengisi nilai di bawah kolom.

worksheet with data after using autofill
Klik ganda pada titik AutoFill untuk mengcopy formula 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.

name box with one column defined
Rentang nama dapat hanya satu kolom, hanya satu baris, atau bahkan hanya satu cell

Masuk ke tab Match pada worksheet. Pada B5, masukkan fungsi MATCH:

=MATCH(A5,order_number,1)

entering the match function
Memasukkan fungsi MATCH

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).

completed match function
Hasil fungsi MATCH

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)

entering the index function
memasukkan fungsi index

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.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.