Pilar Menarik untuk Mengambil Data dari Sheet Lain dengan Kriteria Tertentu di Excel
Apakah Anda pernah kesulitan mengambil data yang terdapat di sheet lain di Excel dengan kriteria tertentu? Jangan khawatir, karena kami akan memberikan panduan cara mudah untuk mengambil data dari sheet lain dengan kriteria tertentu di Excel. Di sini, kami akan membahas beberapa langkah-langkah sederhana beserta gambar untuk memudahkan pemahaman pembaca.
Langkah pertama yang harus dilakukan adalah membuka file Excel yang akan digunakan untuk mengambil data dari sheet lain. Setelah itu, buatlah worksheet baru sebagai sheet sumber data. Pada worksheet baru ini, masukkan data yang akan digunakan sebagai sumber data. Sebagai contoh, kita akan menggunakan data penghasilan dan jumlah karyawan pada tabel berikut:
Dalam contoh ini, data penghasilan dan jumlah karyawan dikelompokkan berdasarkan jenis usaha, yaitu produk IT, produk keuangan, dan produk logistik. Data tersebut akan digunakan sebagai sumber data untuk mengambil data dari sheet lain.
Langkah berikutnya adalah membuat worksheet yang akan digunakan sebagai sheet target, yaitu sheet yang akan menampilkan data yang telah diambil dari sheet sumber dengan kriteria tertentu. Sebagai contoh, kita akan membuat worksheet baru bernama “Produk IT” yang akan menampilkan data penghasilan dan jumlah karyawan dari jenis usaha produk IT saja.
Untuk mengambil data dari sheet sumber dengan kriteria tertentu, kita akan menggunakan rumus INDEX dan MATCH. Rumus INDEX berfungsi untuk mengembalikan nilai dalam suatu array pada posisi tertentu. Sedangkan rumus MATCH berfungsi untuk mencari nilai tertentu dalam suatu array dan mengembalikan posisi atau nomor baris atau kolom dari nilai tersebut.
Untuk mengambil data penghasilan dari jenis usaha produk IT, masukkan rumus berikut pada sel E2 di worksheet “Produk IT”:
“`
=INDEX(SumberData!$B$2:$D$6,MATCH($B2,SumberData!$A$2:$A$6,0),MATCH(E$1,SumberData!$B$1:$D$1,0))
“`
Rumus di atas akan mengambil data penghasilan dari jenis usaha produk IT pada sheet sumber data. Pada rumus di atas, range $B$2:$D$6 merupakan range data pada sheet sumber data yang akan digunakan sebagai sumber data. Range $A$2:$A$6 merupakan range jenis usaha pada sheet sumber data yang akan digunakan sebagai kriteria. Sedangkan range $B$1:$D$1 merupakan range header pada sheet sumber data yang akan digunakan sebagai acuan kolom data yang akan diambil.
Untuk mengambil data jumlah karyawan dari jenis usaha produk IT, masukkan rumus berikut pada sel F2 di worksheet “Produk IT”:
“`
=INDEX(SumberData!$B$2:$D$6,MATCH($B2,SumberData!$A$2:$A$6,0),MATCH(F$1,SumberData!$B$1:$D$1,0))
“`
Rumus di atas akan mengambil data jumlah karyawan dari jenis usaha produk IT pada sheet sumber data. Pada rumus di atas, range $B$2:$D$6, $A$2:$A$6, dan $B$1:$D$1 memiliki fungsi yang sama dengan rumus sebelumnya.
Setelah memasukkan rumus di atas, hasilnya akan menjadi seperti tabel berikut:
Tabel Penghasilan dan Jumlah Karyawan untuk Produk IT
Dari tabel di atas, kita dapat melihat penghasilan dan jumlah karyawan untuk jenis usaha produk IT saja. Dengan menggunakan cara yang sama, kita dapat membuat worksheet baru untuk jenis usaha lain seperti produk keuangan dan produk logistik.
FAQ:
1. Apakah rumus INDEX dan MATCH hanya dapat digunakan untuk mengambil data dari sheet lain dengan kriteria tertentu?
– Tidak, rumus INDEX dan MATCH dapat digunakan untuk mengambil data dari sheet lain dengan berbagai kriteria seperti bilangan tertentu, range waktu, teks tertentu, dan lain sebagainya.
2. Bagaimana jika terdapat beberapa baris data dengan kriteria yang sama pada worksheet sumber data?
– Jika terdapat beberapa baris data dengan kriteria yang sama pada worksheet sumber data, maka rumus di atas akan mengembalikan nilai pada baris pertama yang ditemukan. Untuk mengambil nilai pada baris kedua atau selanjutnya, kita dapat menambahkan nilai ke dalam rumus. Sebagai contoh, jika kita ingin mengambil data pada baris kedua yang memiliki kriteria yang sama, maka rumus dapat diubah menjadi sebagai berikut:
“`
=INDEX(SumberData!$B$2:$D$6,MATCH($B2,SumberData!$A$2:$A$6,0)+1,MATCH(E$1,SumberData!$B$1:$D$1,0))
“`
Pada rumus di atas, nilai “+1” ditambahkan pada nilai hasil fungsi MATCH untuk mengambil data pada baris kedua. Untuk mengambil data pada baris ketiga atau selanjutnya, nilai pada rumus dapat diubah sesuai dengan urutan baris data.
Dalam tutorial singkat ini, kami telah membahas cara untuk mengambil data dari sheet lain dengan kriteria tertentu di Excel menggunakan rumus INDEX dan MATCH. Dengan menggunakan cara ini, Anda dapat menghemat waktu dan usaha untuk mengambil data yang dibutuhkan dari file Excel yang besar. Semoga tutorial ini dapat bermanfaat untuk Anda. Dan berikut ini adalah video tutorial cara mengambil data dari sheet lain di Excel:
