Students Save 30%! Learn & create with unlimited courses & creative assets Students Save 30%! Save Now
Advertisement
  1. Computer Skills
  2. Office
Computers

Cara Extract Data daripada 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

Malay (Melayu) translation by Zidan Al-Farizi (you can also view the original English article)

Apabila anda perlu mencari dan mengeluarkan lajur data dari satu jadual dan letakkannya di lain, gunakan fungsi VLOOKUP. Fungsi ini berfungsi dalam sebarang versi Excel dalam Windows dan Mac, dan juga dalam Google Sheets. Ia membolehkan anda mencari data dalam satu jadual menggunakan beberapa pengecam yang mempunyai persamaan dengan jadual lain. Kedua-dua jadual boleh berada pada helaian yang berbeza atau di dalam buku kerja yang berbeza. Terdapat juga fungsi HLOOKUP, yang melakukan perkara yang sama, tetapi dengan data yang diatur secara mendatar, merentasi baris.

Fungsi MATCH dan INDEX adalah baik untuk digunakan apabila anda bimbang dengan lokasi data tertentu, seperti lajur atau baris yang mengandungi nama seseorang.

Pilihan Premium

Sebelum kita masuk ke fungsi Excel, adakah anda tahu bahawa Pasaran Envato mempunyai pelbagai skrip dan plugin Excel yang membolehkan anda melakukan fungsi lanjutan?

Sebagai contoh, anda boleh:

Excel scripts and plugins on Envato Market
Skrip dan plugin Excel di Envato Market

Screencast

Sekiranya anda mahu mengikuti tutorial ini menggunakan fail Excel anda sendiri, anda boleh melakukannya. Atau jika anda lebih suka, muat turun fail zip yang disertakan untuk tutorial ini, yang mengandungi contoh buku kerja yang disebut vlookup example.xlsx.

Menggunakan VLOOKUP

Apabila VLOOKUP mendapati pengecam yang anda nyatakan dalam data sumber, ia kemudiannya boleh mencari mana-mana sel dalam baris itu dan mengembalikan maklumat kepada anda. Ambil perhatian bahawa dalam data sumber, pengecam mesti berada di lajur pertama jadual.

unique identifiers for vlookup
Pengenal pasti unik sepertinya nombor bersiri, di mana tidak ada dua sama dalam jadual yang sama.

Sintaks

Sintaks fungsi VLOOKUP ialah:

=VLOOKUP(lookup value, table range, column number, [true/false])

Inilah hujah-hujah ini:

  • Lookup value. Sel yang mempunyai pengecam unik.
  • Table range. Julat jadual. Julat sel yang mempunyai pengenal di lajur pertama, diikuti oleh seluruh data dalam lajur yang lain.
  • Column number. Bilangan lajur yang mempunyai data yang anda cari. Jangan terkeliru dengan huruf lajur itu. Dalam ilustrasi di atas, negeri-negeri berada dalam ruang 4.
  • True/False. Hujah ini adalah pilihan. True bermakna bahawa padanan anggaran boleh diterima, dan False bermakna bahawa hanya padanan tepat boleh diterima.

Kami ingin mencari jumlah jualan dari jadual dalam ilustrasi di atas, jadi kami menggunakan hujah-hujah ini:

syntax of vlookup
Sintaks fungsi VLOOKUP

Tentukan Nama Julat untuk Membuat Rujukan Mutlak

Dalam example.xlsx Vlookup, lihat lembaran kerja Jumlah Jualan. Kami akan memasukkan formula dalam B5, kemudian gunakan ciri Auto Isi untuk menyalin formula di bawah helaian. Ini bermakna rentang jadual dalam formula harus menjadi rujukan mutlak. Cara yang baik untuk melakukannya adalah untuk menentukan nama untuk julat jadual.

Menetapkan Nama Julat dalam Excel

  1. Sebelum memasukkan formula, pergi ke lembaran kerja data sumber.
  2. Pilih semua sel dari A4 (header untuk lajur # Pesanan) ke bawah H203. Cara cepat untuk melakukannya ialah dengan mengklik A4, kemudian tekan Ctrl-Shift-End (Command-Shift-End pada Mac).
  3. Klik di dalam Peti Nama di atas lajur A (Kotak Nama kini memaparkan A4).
  4. Taipkan data, kemudian tekan Enter.
  5. Anda kini boleh menggunakan data nama dalam formula dan bukannya $A$4:$H$203.
Name box displaying a range name
Kotak nama biasanya memaparkan alamat sel semasa. Klik di dalamnya dan taipkan nama untuk menentukan julat.

Menentukan nama Julat dalam Helaian Google

Dalam Helaian Google, menentukan nama adalah sedikit berbeza.

  1. Klik tajuk lajur pertama data sumber anda, kemudian tekan Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow pada Mac). Yang memilih baris pengepala lajur.
  2. Tekan Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow pada Mac). Yang memilih data sebenar.
  3. Klik menu Data, kemudian pilih Rangkaian dinamakan dan dilindungi.
  4. Dalam Nama dan kotak julat yang dilindungi di sebelah kanan, taip data, kemudian klik Selesai.
Data menu in Google Sheets
Menentukan nama pelbagai dalam Google Sheets

Memasuki Formula

Untuk memasukkan formula, pergi ke Lembaran Kerja Jumlah Jualan dan klik B5.

Masukkan formula:

=VLOOKUP(A5,data,8,FALSE)

Tekan enter.

entering the vlookup function
Memasuki fungsi VLOOKUP

Hasilnya hendaklah 40. Untuk mengisi nilai-nilai di bawah lajur, klik kembali pada B5, jika perlu. Masukkan penunjuk tetikus pada titik AutoFill di sudut bawah kanan sel, supaya penunjuk tetikus menjadi rambut salib.

mouse pointer on the autofill dot
Apabila anda meletakkan penuding tetikus pada titik di sudut kanan bawah sel, ia menjadi rambut silang AutoFill

Klik dua kali untuk mengisi nilai di bawah lajur.

worksheet with data after using autofill
Klik dua kali rambut silang Auto Isi untuk menyalin formula ke bawah lajur

Sekiranya anda mahu, anda boleh menjalankan fungsi VLOOKUP dalam beberapa lajur yang akan datang untuk mengekstrak medan lain, seperti nama belakang atau negeri.

Menggunakan MATCH

Fungsi MATCH tidak mengembalikan nilai data kepada anda; anda memberikan nilai yang anda cari, dan fungsi itu mengembalikan kedudukan nilai itu. Ia seperti bertanya di mana adalah # 135 Main Street, dan mendapat jawapan bahawa ia adalah bangunan ke-4 di jalan.

Sintaks

Sintaks fungsi MATCH ialah:

= MATCH (nilai pencarian, julat jadual, [jenis padanan])

Argumen-argumen tersebut adalah:

  • Lookup value. Sel yang mempunyai pengecam unik.
  • Table range. Pelbagai sel yang anda cari.
  • Match type. Pilihan. Itulah cara anda menentukan sejauh mana padanan yang anda mahu, seperti berikut:

Nilai tertinggi seterusnya

-1

Nilai mesti dalam urutan menurun.

Target value

0

Nilai boleh dalam sebarang pesanan.

Next lowest value

1

Jenis lalai. Nilai mestilah dalam urutan menaik.

Seperti fungsi VLOOKUP, anda mungkin akan mendapati fungsi MATCH lebih mudah digunakan jika anda menggunakan nama pelbagai. Pergi ke lembaran Data Sumber, pilih dari B4 (pengepala lajur untuk pesanan #) ke bahagian bawah, klik pada kotak Nama di atas lajur A, dan panggil ia order_number. Ambil perhatian bahawa nilai-nilai itu dalam urutan menaik.

name box with one column defined
Julat bernama hanya boleh satu lajur, hanya satu baris, atau satu sel sahaja

Pergi ke tab Match dalam lembaran kerja. Di B5, masukkan fungsi MATCH:

=MATCH(A5,order_number,1)

entering the match function
Memasuki fungsi MATCH

Jika anda tidak menentukan nama pelbagai, anda akan menulis fungsi sebagai:

=MATCH(A5,'Source Data'!A5:A203,0)

Sama ada cara, anda dapat melihat bahawa ini berada di kedudukan ke-14 (menjadikannya urutan ke-13).

completed match function
Keputusan fungsi MATCH

Menggunakan INDEX

Fungsi INDEX adalah bertentangan dengan fungsi MATCH dan sama dengan VLOOKUP. Anda memberitahu fungsi apa baris dan lajur data yang anda mahu, dan ia memberitahu anda nilai apa yang ada di dalam sel.

Syntax

Sintaks fungsi INDEX ialah:

=INDEX(data range, row number, [column number])

Argumen-argumen tersebut adalah:

  • Data range. Sama seperti dua fungsi lain, ini adalah jadual data.
  • Row number. Nombor baris data, yang tidak semestinya baris lembaran kerja. Sekiranya jadual jadual bermula pada baris 10 helaian, maka itu adalah baris # 1.
  • Column number. Nombor lajur rangkaian data. Jika julat bermula pada lajur E, itu lajur # 1.

Dokumentasi Excel akan memberitahu anda bahawa argumen nombor lajur adalah pilihan, tetapi nombor baris adalah jenis pilihan juga. Jika julat jadual hanya mempunyai satu baris atau satu lajur, anda tidak perlu menggunakan argumen yang lain.

Pergi ke lembaran Index buku kerja dan klik pada C6. Kami mula-mula ingin mencari apa yang terkandung dalam baris 9, lajur 3 jadual. Dalam formula, kami akan menggunakan nama jarak yang kami buat sebelum ini.

Masukkan formula:

=INDEX(data,A6,B6)

entering the index function
memasuki fungsi indeks

Ia mengembalikan nama pelanggan pelanggan: Strevell. Tukar nilai A6 dan B6, dan hasilnya dalam C6 akan menunjukkan hasil yang berbeza (perhatikan bahawa banyak baris mempunyai nama dan nama produk yang sama).

Kesimpulannya

Keupayaan lembaran kerja untuk melihat lembaran kerja lain dan mengekstrak data adalah alat yang hebat. Dengan cara ini, anda boleh mempunyai satu helaian yang mengandungi semua data yang anda perlukan untuk banyak tujuan, kemudian cabut apa yang anda perlukan untuk contoh tertentu.

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.