Cara mengekstrak data dari satu lembar ke lembar lainnya di excel

Saat Anda perlu menemukan dan mengekstrak kolom data dari satu tabel dan meletakkannya di tabel lain, gunakan fungsi VLOOKUP. Fungsi ini berfungsi di semua versi Excel di Windows dan Mac, dan juga di Google Sheets. Ini memungkinkan Anda untuk menemukan data dalam satu tabel menggunakan beberapa pengidentifikasi yang memiliki kesamaan dengan tabel lain. Kedua tabel tersebut bisa berada di lembar yang berbeda atau bahkan di buku kerja yang berbeda. Ada juga fungsi HLOOKUP, yang melakukan hal yang sama, tetapi dengan data yang disusun secara horizontal, melintasi baris

Fungsi MATCH dan INDEX bagus untuk digunakan saat Anda memperhatikan lokasi data tertentu, seperti kolom atau baris yang berisi nama seseorang.  

Opsi Premium

Sebelum kita masuk ke fungsi Excel, tahukah Anda bahwa Envato Market memiliki serangkaian skrip dan plugin Excel yang memungkinkan Anda menjalankan fungsi tingkat lanjut?

Skrip dan plugin Excel di Envato Market

Siaran layar

Cara Mengekstrak Data dari Spreadsheet menggunakan VLOOKUP, MATCH, dan INDEX

Jika Anda ingin mengikuti tutorial ini menggunakan file Excel Anda sendiri, Anda bisa melakukannya. Atau jika Anda suka, unduh file zip yang disertakan untuk tutorial ini, yang berisi contoh buku kerja yang disebut contoh vlookup. xlsx

Menggunakan VLOOKUP

Saat VLOOKUP menemukan pengenal yang Anda tentukan di data sumber, VLOOKUP kemudian dapat menemukan sel apa pun di baris tersebut dan mengembalikan informasinya kepada Anda. Perhatikan bahwa dalam data sumber, pengidentifikasi harus berada di kolom pertama tabel

Pengidentifikasi unik harus seperti nomor seri, di mana tidak ada dua yang sama dalam tabel yang sama

Sintaksis

Sintaks dari fungsi VLOOKUP adalah

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

Inilah yang dimaksud dengan argumen ini

• Nilai lookup. Sel yang memiliki pengidentifikasi unik
• Kisaran meja. Rentang sel yang memiliki pengidentifikasi di kolom pertama, diikuti oleh sisa data di kolom lainnya
• Nomor kolom. Jumlah kolom yang berisi data yang Anda cari. Jangan bingung dengan huruf kolom. Pada ilustrasi di atas, negara bagian ada di kolom 4
• Benar salah. Argumen ini opsional. Benar berarti pencocokan perkiraan dapat diterima, dan Salah berarti hanya pencocokan persis yang dapat diterima

Kami ingin mencari jumlah penjualan dari tabel pada ilustrasi di atas, jadi kami menggunakan argumen ini

Sintaks fungsi VLOOKUP

Tentukan Nama Rentang untuk Membuat Referensi Mutlak

Dalam contoh Vlookup. xlsx, lihat lembar kerja Jumlah Penjualan. Kami akan memasukkan rumus di B5, lalu gunakan fitur IsiOtomatis untuk menyalin rumus ke bawah lembar. Itu berarti rentang tabel dalam rumus harus menjadi referensi mutlak. Cara yang baik untuk melakukannya adalah dengan menentukan nama untuk rentang tabel

Mendefinisikan Nama Rentang di Excel

1. Sebelum memasukkan rumus, buka lembar kerja data sumber
2. Pilih semua sel dari A4 (header untuk kolom Order #) hingga H203. Cara cepat melakukannya adalah dengan mengklik A4, lalu tekan Ctrl-Shift-End (Command-Shift-End di Mac)
3. Klik di dalam Kotak Nama di atas kolom A (Kotak Nama sekarang menampilkan A4)
4. Ketik data, lalu tekan Enter
5. Sekarang Anda dapat menggunakan data nama dalam rumus, bukan $A$4. $H$203

Kotak nama biasanya menampilkan alamat sel saat ini. Klik di dalamnya dan ketik nama untuk menentukan rentang

Menentukan nama Range di Google Sheets

Di Google Sheets, menentukan nama sedikit berbeda

1. Klik tajuk kolom pertama dari data sumber Anda, lalu tekan Ctrl-Shift-Panah Kanan (Command-Shift-Panah Kanan di Mac). Itu memilih baris tajuk kolom
2. Tekan Ctrl-Shift-Panah Bawah (Command-Shift-Panah Bawah di Mac). Itu memilih data aktual
3. Klik menu Data, lalu pilih Named and protected ranges
4. Di kotak Nama dan rentang terlindungi di sebelah kanan, ketik data, lalu klik Selesai

Menentukan nama rentang di Google Sheets

Memasukkan Formula

Untuk memasukkan formula, buka lembar kerja Jumlah Penjualan dan klik di B5

Masukkan rumus

=VLOOKUP(A5,data,8,FALSE)_

tekan enter

Memasuki fungsi VLOOKUP

Hasilnya harus 40. Untuk mengisi nilai di bawah kolom, klik kembali pada B5, jika perlu. Letakkan penunjuk tetikus pada titik IsiOtomatis di pojok kanan bawah sel, sehingga penunjuk tetikus menjadi tanda silang

Saat Anda meletakkan penunjuk tetikus pada titik di pojok kanan bawah sel, itu menjadi tanda silang IsiOtomatis

Klik dua kali untuk mengisi nilai di bawah kolom

Klik dua kali palang IsiOtomatis untuk menyalin rumus ke bawah kolom

Jika mau, Anda dapat menjalankan fungsi VLOOKUP di beberapa kolom berikutnya untuk mengekstrak bidang lain, seperti nama belakang atau negara bagian

Menggunakan PERTANDINGAN

Fungsi MATCH tidak mengembalikan nilai data kepada Anda; . Ini seperti menanyakan di mana #135 Main Street, dan mendapatkan jawaban bahwa itu adalah gedung ke-4 di ujung jalan

Sintaksis

Sintaks fungsi MATCH adalah

=MATCH(lookup value, table range, [match type])

Argumennya adalah

• Nilai lookup. Sel yang memiliki pengidentifikasi unik
• Kisaran meja. Rentang sel yang Anda telusuri
• Jenis pertandingan. Opsional. Begitulah cara Anda menentukan seberapa dekat kecocokan yang Anda inginkan, sebagai berikut

Nilai tertinggi berikutnya

-1

Nilai harus dalam urutan menurun

Nilai target

0

Nilai bisa dalam urutan apapun

Nilai terendah berikutnya

1

Jenis bawaan. Nilai harus dalam urutan menaik

Seperti fungsi VLOOKUP, Anda mungkin akan menemukan fungsi MATCH lebih mudah digunakan jika Anda menerapkan nama rentang. Buka lembar Data Sumber, pilih dari B4 (tajuk kolom untuk pesanan #) ke bawah, klik di kotak Nama di atas kolom A, dan beri nama nomor_pesanan. Perhatikan bahwa nilainya dalam urutan menaik

Rentang bernama bisa hanya satu kolom, hanya satu baris, atau bahkan hanya satu sel

Buka tab Cocokkan lembar kerja. Di B5, masukkan fungsi MATCH

=MATCH(A5,order_number,1)

Memasuki fungsi MATCH

Jika Anda tidak menentukan nama rentang, Anda akan menulis fungsinya sebagai

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

Bagaimanapun, Anda dapat melihat bahwa ini berada di posisi ke-14 (menjadikannya urutan ke-13)

Hasil dari fungsi MATCH

Menggunakan INDEX

Fungsi INDEX adalah kebalikan dari fungsi MATCH dan mirip dengan VLOOKUP. Anda memberi tahu fungsi apa baris dan kolom dari data yang Anda inginkan, dan memberi tahu Anda nilai dari apa yang ada di dalam sel

Sintaksis

Sintaks fungsi INDEX adalah

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

Argumennya adalah

• Rentang data. Sama seperti dua fungsi lainnya, ini adalah tabel data
• Nomor baris. Nomor baris data, yang belum tentu merupakan baris lembar kerja. Jika rentang tabel dimulai pada baris 10 lembar, maka itu adalah baris #1
• Nomor kolom. Nomor kolom rentang data. Jika rentang dimulai pada kolom E, itu adalah kolom #1

Dokumentasi Excel akan memberi tahu Anda bahwa argumen nomor kolom adalah opsional, tetapi nomor baris juga opsional. Jika rentang tabel hanya memiliki satu baris atau satu kolom, Anda tidak perlu menggunakan argumen lainnya

Buka lembar Indeks buku kerja dan klik di C6. Kami pertama-tama ingin menemukan apa yang terkandung di baris 9, kolom 3 tabel. Di formula, kami akan menggunakan nama rentang yang kami buat sebelumnya

Masukkan rumus

=INDEX(data,A6,B6)_

memasuki fungsi indeks

Ini mengembalikan nama belakang pelanggan. Strevell. Ubah nilai A6 dan B6, dan hasil di C6 akan menunjukkan hasil yang berbeda (perhatikan bahwa banyak baris memiliki status dan nama produk yang sama)

Kesimpulan

Kemampuan lembar kerja untuk melihat lembar kerja lain dan mengekstrak data adalah alat yang hebat. Dengan cara ini, Anda dapat memiliki satu lembar yang berisi semua data yang Anda perlukan untuk berbagai keperluan, lalu mengekstrak yang Anda perlukan untuk contoh tertentu.  

Bagaimana cara menarik data dari satu lembar kerja ke lembar kerja lainnya di Excel?

Bagaimana cara memasukkan data secara otomatis dari satu lembar Excel ke lembar lainnya?

Bagaimana cara menarik data dari lembar lain di lembar?

Bagaimana cara mentransfer data dari satu lembar ke lembar lainnya di Excel menggunakan rumus?