Excel adalah salah satu software pengolah data yang banyak digunakan dalam dunia profesional. Dalam sebuah perusahaan atau lembaga, penggunaan Excel biasanya diperlukan dalam mengolah data seperti laporan keuangan, data pelanggan, data produk, dan lain sebagainya.
Namun, terkadang data yang harus diolah bersumber dari beberapa file Excel yang berbeda. Sebagai pengolah data, tentunya kita ingin mendapatkan hasil yang akurat dan cepat tanpa harus memeriksa data satu per satu. Oleh karena itu, kali ini Duuwi akan memberikan tips Mengambil Data Dari Sheet Lain Dengan Kriteria Tertentu.
1. Menggunakan Rumus VLOOKUP
Rumus VLOOKUP merupakan rumus yang paling sering digunakan dalam mengambil data dari sheet lain dengan kriteria tertentu. VLOOKUP adalah kepanjangan dari Vertical Lookup, yang berarti pencarian data secara vertikal berdasarkan kriteria tertentu.
Rumus VLOOKUP menggunakan data referensi untuk mencari data dari data sumber. Data referensi dan data sumber harus memiliki kolom yang sama dan data referensi terletak di sebelah kiri data sumber. Umumnya, kolom yang sama pada kedua tabel ini adalah kolom ID atau kode unik, seperti nama pelanggan, nomor faktur, ID produk, dan sebagainya.
Cara Menggunakan Rumus VLOOKUP
Pada dasarnya, rumus VLOOKUP terdiri dari empat bagian, yaitu :
- Nilai yang ingin dipilih
- Data Referensi (nilai yang akan dicari)
- Nomor kolom dari data sumber yang ingin dipilih
- TRUE atau FALSE Untuk menentukan jenis pencarian
Berikut adalah contoh rumus VLOOKUP untuk mengambil data dari sheet lain dengan kriteria tertentu:
=VLOOKUP (nilai_yang_dicari, range_data_referensi, nomor_kolom_dari_data_sumber, jenis_pencarian)
Dimana :
- nilai_yang_dicari : nilai yang dicari dalam data referensi, seperti nama pelanggan, nomor faktur, atau ID produk.
- range_data_referensi : data referensi dari sheet lain yang berisi data atau nilai yang akan diambil dari sheet yang akan kita olah.
- nomor_kolom_dari_data_sumber : nomor kolom dari data sumber yang akan kita ambil. Nomor kolom harus dihitung mulai dari kolom pertama dari data sumber (Bukan dari data referensi).
- jenis_pencarian : DIGIT 0 atau FALSE menandakan pencarian nilai eksak (exact match atau sama persis), DIGIT 1 atau TRUE menandakan pencarian nilai tidak eksak (approximate match atau sama satu sama lain).
Untuk lebih memahaminya, mari kita lihat contoh berikut:
Contoh Penggunaan Rumus VLOOKUP untuk Mengambil Data Dari Sheet Lain
Misalnya, kita memiliki dua sheet Excel yang berbeda. Sheet Excel pertama berisi daftar data produk beserta harga dan sheet Excel kedua berisi daftar pelanggan yang telah melakukan pemesanan, beserta produk yang dipesan dan jumlahnya.
Sheet Produk
| ID Produk | Nama Produk | Harga |
|---|---|---|
| 001 | Monitor Dell | 1,500,000 |
| 002 | Keyboard Logitech | 200,000 |
| 003 | Mouse Wireless Microsoft | 300,000 |
| 004 | Harddisk WD | 1,000,000 |
Sheet Pemesanan
| Nomor Order | Nama Pelanggan | ID Produk | Jumlah |
|---|---|---|---|
| 001 | Ahmad | 001 | 2 |
| 001 | Ahmad | 002 | 2 |
| 002 | Nita | 004 | 1 |
| 003 | Iwan | 003 | 3 |
Misalnya, kita ingin mengambil harga produk dari sheet produk untuk seluruh produk yang dipesan oleh Ahmad dan menampilkan jumlah total harga. Berikut adalah rumus yang digunakan:
=SUM(VLOOKUP(A9,Sheet1!$A$2:$C$5,3,0)*B9+VLOOKUP(A10,Sheet1!$A$2:$C$5,3,0)*B10)
Keterangan rumus:
- nilai_yang_dicari = ID produk pada sheet pemesanan
- range_data_referensi = Sheet1!$A$2:$C$5 (Sheet produk)
- nomor_kolom_dari_data_sumber = kolom harga (kolom ke-3) pada sheet produk
- jenis_pencarian = 0 (eksak atau sama persis)
Contoh rumus di atas akan mengambil harga dari produk yang dipesan oleh Ahmad pada sheet pemesanan. Rumus kemudian akan mengalikan harga dengan jumlah produk dan menampilkan hasil jumlah total harga.
2. Menggunakan Add-Ins Power Query
Power Query adalah add-in Excel yang dapat digunakan dalam mengakses, menggabungkan, dan memanipulasi data dari berbagai sumber. Power Query dapat mengambil data dari berbagai sumber seperti file Excel, file CSV, database SQL Server, dan situs web.
Fitur ini sangat bermanfaat dalam mengambil data dari sheet lain, terutama jika data yang akan diolah sangat besar dan terdiri dari beberapa file Excel. Dengan Power Query, pengguna dapat menggabungkan data dari beberapa file Excel sekaligus dengan mudah.
Berikut adalah langkah-langkah menggunakan Power Query untuk mengambil data dari sheet lain:
Langkah 1: Import Data
- Pilih tab Data pada Ribbon Excel, dan klik tombol From Other Sources, kemudian pilih From Excel pada menu drop-down.
- Pilih file Excel yang berisi data atau nilai yang akan diambil dan klik tombol OK.
- Pilih lembar kerja yang berisi data atau nilai yang akan diambil.
- Klik tombol Load atau tidak jangan pilih Load lagi untuk memuat data Excel ke Power Query.
Langkah 2: Manipulasi Data
- Lakukan manipulasi data jika diperlukan untuk memenuhi persyaratan pengolahan data.
- Berikut ini beberapa manipulasi data yang dapat dilakukan:
- Filtering (Penyaringan): menghapus atau menyimpan baris data berdasarkan kondisi tertentu.
- Sorting (Pengurutan): mengurutkan data atau nilai berdasarkan kolom tertentu.
- Grouping (Pengelompokkan): mengelompokkan data atau nilai berdasarkan kolom tertentu.
- Split Columns (Pemisahan Kolom): memisahkan kolom menjadi kolom baru berdasarkan delimiter tertentu.
- Merge Columns (Penggabungan Kolom): Menggabungkan kolom menjadi kolom baru.
- dll.
Setelah manipulasi data selesai, data dapat disimpan ke dalam file Excel atau disimpan sebagai koneksi ke sumber data eksternal.
Langkah 3: Refresh Data
Pastikan untuk selalu memperbarui data jika terjadi perubahan data pada sheet sumber yang telah dihubungkan menggunakan Power Query. Untuk memperbarui data, tekan tombol data refresh pada tab data pada ribbon Excel.
FAQ
Pertanyaan 1: Saya ingin menampilkan data dari sheet lain dengan rentang nilai tertentu. Bagaimana cara melakukannya?
Jawaban: Anda dapat menggunakan rumus VLOOKUP dengan menambahkan rumus IF. Contoh rumus VLOOKUP yang mengambil data dengan rentang nilai tertentu:
=IF($B$2<=VLOOKUP($A2,Sheet1!$A$2:$C$5,3,0)<=$B$3,VLOOKUP($A2,Sheet1!$A$2:$C$5,3,0),"")
Dimana $B$2 dan $B$3 adalah sejauh mana data merupakan nilai yang diperbolehkan.
Pertanyaan 2: Apakah rumus VLOOKUP hanya bisa mengambil data dari sheet lain dengan satu kondisi saja?
Jawaban: Tidak, rumus VLOOKUP dapat digunakan untuk mengambil data dengan beberapa kondisi menggunakan rumus CONCATENATE atau ampersand.
Berikut contoh rumus VLOOKUP untuk mengambil data dari sheet lain dengan dua kondisi:
=VLOOKUP(CONCATENATE(A2,B2),Sheet1!$A$2:$D$6,4,0)
Dimana A2 dan B2 adalah dua nilai referensi yang akan digunakan sebagai kriteria.
Video Tutorial
Itulah informasi seputar cara Mengambil Data Dari Sheet Lain Dengan Kriteria Tertentu menggunakan rumus VLOOKUP dan add-ins Power Query di Excel. Semoga informasi ini dapat membantu Anda dalam pengolahan data di Excel dan memudahkan pekerjaan Anda.
