Cara mengelola data adalah hal yang penting dalam kegiatan pengolahan data baik itu di dalam perusahaan atau bagi individu yang melakukan pengolahan data. Saat ini, software pengolah data semakin variatif dan Excel merupakan salah satu software pengolah data terpopuler di dunia. Dalam artikel ini, kita akan membahas cara mengambil data dari sheet lain dengan kriteria tertentu di Excel, cara filter data dan menampilkannya pada sheet lain dengan fitur, cara mengambil data berdasarkan nilai atau record yang sama, serta cara mengambil data dari web melalui Excel.
Cara Mengambil Data dari Sheet Lain dengan Kriteria Tertentu di Excel
Excel memiliki fitur VLOOKUP atau Vertical Lookup yang berguna untuk mencari data pada tabel atau database. VLOOKUP dapat digunakan untuk mencari data dari sheet lain dengan kriteria tertentu. Berikut langkah-langkahnya:
- Pilih cell dimana data hasil pencarian akan ditempatkan
- Tuliskan rumus VLOOKUP, yaitu =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- Lookup_value adalah nilai yang ingin dicari, misalnya nama produk
- Table_array adalah rentang data atau tabel dimana nilai dicari, misalnya tabel produk di sheet lain
- Col_index_num adalah nomor kolom tempat nilai yang ingin ditemukan berada, misalnya nomor kolom harga produk
- Range_lookup adalah nilai opsional yang menentukan atau tidaknya pencarian benar-benar akurat, jika nilainya FALSE maka pencarian akan benar-benar akurat, jika TRUE maka pencarian akan mencari nilai terdekat.
Contoh kasus, pengguna memiliki tabel produk dengan data sebagai berikut:
| Nama Produk | Harga | ID Produk |
|---|---|---|
| Product 1 | 10000 | 001 |
| Product 2 | 20000 | 002 |
| Product 3 | 30000 | 003 |
| Product 4 | 40000 | 004 |
Di sheet lain, pengguna ingin mencari harga dari produk 3. Berikut rumus VLOOKUP yang digunakan:
=VLOOKUP(“Product 3”,’Sheet2′!A1:C5,2,FALSE)
Hasilnya adalah 30000, sesuai dengan harga produk 3 pada tabel.
Cara Filter Data Dan Menampilkannya Pada Sheet Lain Dengan Fitur
Selain fitur VLOOKUP, Excel juga memiliki fitur lain yang berguna untuk mengelola data, yaitu Advanced Filter. Fitur ini digunakan untuk mencari data pada tabel tertentu dan menampilkan hasilnya pada sheet lain. Berikut adalah langkah-langkah penggunaannya:
- Pilih tabel yang ingin difilter pada sheet tertentu
- Klik tombol Data dan pilih Advanced pada grup Sort & Filter
- Isi kriteria filter sesuai dengan data yang ingin dicari, misalnya kriteria pada kolom dengan header Harga adalah “>=50000”
- Pilih Copy to another location pada opsi Action dan masukkan range data dimana hasil filter akan ditampilkan pada sheet lain
- Pilih sheet dimana hasil filter akan ditampilkan
- Klik OK untuk menampilkan data hasil filter pada sheet baru
Contoh kasus, pengguna memiliki tabel produk dengan data sebagai berikut:
| Nama Produk | Harga | ID Produk |
|---|---|---|
| Product 1 | 10000 | 001 |
| Product 2 | 20000 | 002 |
| Product 3 | 30000 | 003 |
| Product 4 | 40000 | 004 |
| Product 5 | 50000 | 005 |
| Product 6 | 60000 | 006 |
Untuk mencari produk yang memiliki harga diatas 50000, berikut adalah langkah-langkah yang dilakukan:
- Pilih tabel produk pada sheet 1
- Pilih tombol Data dan pilih Advanced Filter pada grup Sort & Filter
- Di opsi Filter Criteria, pilih Harga, kemudian masukkan kriteria “>=50000”
- Pilih Copy to another location pada opsi Action, kemudian pilih range dimana hasil filter akan ditampilkan pada sheet 2
- Klik OK untuk menampilkan data hasil filter
Hasil filter akan ditampilkan seperti pada gambar berikut:
Cara Mengambil Data Berdasarkan Nilai atau Record yang Sama di Excel
Salah satu cara untuk mengambil data berdasarkan nilai atau record yang sama di Excel adalah menggunakan fitur COUNTIFS dan SUMIFS. COUNTIFS dan SUMIFS dapat digunakan bersamaan untuk mencari data yang berdasarkan nilai tertentu pada rentang data. Berikut langkah-langkah penggunaannya:
- Tentukan kondisi yang harus dipenuhi pada rentang data, misalnya cari data hanya pada bulan Januari
- Buat formula COUNTIFS dan SUMIFS, dengan sintax umum =COUNTIFS(RangeKondisi1,Kondisi1,RangeKondisi2,Kondisi2) dan =SUMIFS(RangeData,RangeKondisi1,Kondisi1,RangeKondisi2,Kondisi2)
- Hasil COUNTIFS adalah jumlah data yang memenuhi semua kondisi, sementara hasil SUMIFS adalah jumlah nilai pada data yang memenuhi semua kondisi
Contoh kasus, pengguna memiliki data penjualan produk selama 3 bulan sebagai berikut:
| Bulan | Nama Produk | Jumlah Terjual |
|---|---|---|
| Januari | Product A | 10 |
| Januari | Product B | 5 |
| Februari | Product A | 8 |
| Februari | Product B | 7 |
| Maret | Product A | 12 |
| Maret | Product B | 14 |
Untuk mencari jumlah terjual pada Januari, berikut adalah langkah-langkah yang dilakukan:
- Buat kondisi pada rentang data yang dipilih, misalnya Bulan pada rentang A2:A7 harus sama dengan Januari
- Gunakan formula COUNTIFS dan SUMIFS, dengan rinciannya adalah =COUNTIFS(A2:A7,”Januari”) dan =SUMIFS(C2:C7,A2:A7,”Januari”)
- Hasil dari COUNTIFS adalah 2, yaitu jumlah data yang memenuhi kondisi “Bulan = Januari”
- Hasil dari SUMIFS adalah 15, yaitu jumlah terjual dari produk pada bulan Januari
Cara Mengambil Data dari Web Melalui Excel
Selain mengambil data dari tabel atau database yang ada di Excel, Excel juga dapat digunakan untuk mengambil data dari web. Oleh karena itu, Excel menawarkan fitur web queries yang berguna untuk mengambil data dari web secara otomatis dan menampilkannya dalam format tabel di Excel. Berikut adalah cara mendapatkan data dari web melalui Excel:
- Buka Excel dan buat New Workbook
- Klik data -> From Web
- Masukkan URL web yang ingin diambil datanya
- Setelah itu, Excel akan menampilkan struktur data yang ingin diambil sesuai dengan tabel di web
- Setelah pengguna memilih tabel yang ingin diambil datanya, Excel akan menampilkan data dalam format tabel
- Jika pengguna ingin mengambil data secara berkala, pengguna dapat memilih Refresh Data pada opsi Connection Properties
Contoh kasus, pengguna ingin mengambil data nilai tukar mata uang rupiah ke dolar Amerika Serikat dari website www.exchange-rates.org. Berikut cara mendapatkan data tersebut:
- Buka Excel dan buat New Workbook
- Klik Data -> From Web
- Masukkan URL website www.exchange-rates.org pada kolom “Address”
- Excel akan menampilkan isi dari website, tunjukkan pada tabel yang ingin diambil datanya
- Klik Add untuk menambahkan tabel tersebut
- Klik “Load”
- Excel akan memuat data dari web dan menampilkannya dalam format tabel
FAQ:
1. Apa bedanya antara VLOOKUP dan HLOOKUP?
VLOOKUP merujuk ke kolom pada tabel, sementara HLOOKUP merujuk ke baris pada tabel.
2. Apa kegunaan dari Advanced Filter pada Excel?
Advanced Filter digunakan untuk mencari data pada tabel tertentu dan menampilkan hasilnya pada sheet lain.
Video Tutorial:
