Temukan alternatif modern dan ringkas untuk pencarian data dinamis di Excel.
Bagi pengguna Excel, melakukan
pencarian data adalah tugas sehari-hari. Namun, mencari nilai secara dinamis di
tabel dua dimensi, misalnya berdasarkan kriteria baris dan kolom, seringkali
memerlukan rumus yang kompleks. Dulu, kombinasi INDEX + MATCH + MATCH adalah
solusi standar, namun sintaksnya yang panjang dan cenderung rumit seringkali
membuat bingung.
Kabar baiknya, seiring dengan
evolusi Excel, kini ada berbagai fungsi yang lebih modern dan ringkas untuk
melakukan lookup dinamis, bahkan tanpa perlu rumus sekompleks
itu. Artikel ini akan membahas beberapa alternatif terbaik, terutama bagi Anda
yang menggunakan Microsoft 365 atau Excel versi terbaru.
XLOOKUP: Solusi Tercanggih
untuk Pencarian Dua Arah
Jika Anda menggunakan Microsoft
365 atau Excel 2021, fungsi XLOOKUP adalah pilihan utama yang
sangat direkomendasikan. Dirancang untuk menggantikan VLOOKUP, HLOOKUP,
dan bahkan kombinasi INDEX + MATCH, XLOOKUP menawarkan sintaks
yang lebih sederhana dan fleksibilitas yang luar biasa, terutama untuk
pencarian dua arah (dua kriteria).
Mengapa XLOOKUP Unggul?
- Sintaks Lebih Simpel: Jauh lebih mudah
dibaca dan ditulis dibandingkan INDEX + MATCH untuk pencarian
dua arah.
- Pencarian Fleksibel: Dapat mencari
nilai ke kiri atau ke kanan, serta ke atas atau ke bawah, mengatasi
batasan VLOOKUP.
- Pencocokan Tepat (Default): Secara default, XLOOKUP mencari
kecocokan yang tepat, mengurangi kesalahan umum.
- Penanganan Nilai Tidak Ditemukan: Memiliki
argumen bawaan untuk menentukan apa yang harus dikembalikan jika nilai
tidak ditemukan, menghindari kesalahan #N/A.
Contoh XLOOKUP untuk Pencarian
Dua Arah:
Bayangkan Anda memiliki tabel
penjualan dengan produk di kolom pertama dan bulan di baris header. Anda ingin
mencari penjualan suatu produk di bulan tertentu.
|
Jan |
Feb |
Mar |
|
|
Apel |
100 |
120 |
110 |
|
Pisang |
150 |
130 |
160 |
|
Jeruk |
90 |
115 |
95 |
Misalnya, Anda ingin mencari
penjualan "Pisang" di bulan "Februari".
- Nilai Produk yang dicari: "Pisang"
(misalnya di sel G1)
- Nilai Bulan yang dicari: "Feb" (misalnya
di sel G2)
- Hasil akan muncul di G3.
Rumusnya akan menjadi:
=XLOOKUP(G2, B1:D1, XLOOKUP(G1,
A2:A4, B2:D4))
Bagaimana rumus ini bekerja?
- XLOOKUP(G1, A2:A4, B2:D4) (XLOOKUP Bagian
Dalam):
- G1 ("Pisang") adalah nilai yang
dicari untuk produk.
- A2:A4 adalah rentang tempat mencari nama
produk.
- B2:D4 adalah rentang data yang akan
dikembalikan. Karena lookup_array (A2:A4) adalah rentang
vertikal dan return_array (B2:D4) mencakup banyak kolom, XLOOKUP bagian
dalam ini akan mengembalikan seluruh baris yang sesuai
dengan "Pisang" sebagai sebuah array, yaitu {150, 130,
160}.
- =XLOOKUP(G2, B1:D1, {150, 130, 160}) (XLOOKUP
Bagian Luar):
- G2 ("Feb") adalah nilai yang dicari
untuk bulan.
- B1:D1 adalah rentang tempat mencari header bulan.
- {150, 130, 160} adalah return_array (hasil
dari XLOOKUP bagian dalam). XLOOKUP bagian luar akan
menemukan "Feb" di B1:D1 dan mengembalikan nilai yang
sesuai dari posisi kedua dalam array {150, 130, 160}, yaitu 130.
Dengan rumus ini, Anda dapat
mengubah nama produk atau bulan di sel G1 dan G2, dan hasil di G3 akan otomatis
diperbarui, menjadikan lookup ini benar-benar dinamis.
Anda juga bisa menambahkan
penanganan error untuk tampilan yang lebih rapi:
=XLOOKUP(G2, B1:D1, XLOOKUP(G1,
A2:A4, B2:D4, "Produk Tidak Ditemukan"), "Bulan Tidak
Ditemukan")
GETPIVOTDATA: Dinamis untuk
PivotTable
Jika data Anda sudah diatur dalam
bentuk PivotTable, GETPIVOTDATA adalah cara yang sangat
efisien dan dinamis untuk mengambil data tertentu. Fungsi ini merujuk pada
struktur PivotTable, bukan lokasi sel statis, sehingga tetap berfungsi meskipun
tata letak PivotTable berubah. Ini sangat ideal untuk membuat dashboard dan
laporan interaktif.
Sintaks GETPIVOTDATA:
=GETPIVOTDATA(data_field,
pivot_table, [field1, item1], [field2, item2], ...)
- data_field: Nama bidang nilai (misalnya "Sum
of Sales").
- pivot_table: Referensi ke sel mana pun di dalam
PivotTable.
- [field, item]: Pasangan nama bidang dan item yang
menentukan kriteria pencarian.
Membuat GETPIVOTDATA Dinamis
dengan Referensi Sel:
Kunci untuk membuat GETPIVOTDATA dinamis
adalah mengganti nilai yang dikodekan secara langsung dengan referensi sel.
Contoh Dinamis:
Asumsikan PivotTable Anda dimulai
di A1.
- Sel D1 berisi nama bidang data yang
dicari (misal: "Sum of Sales").
- Sel D2 berisi nama produk (misal:
"Product A").
- Sel D3 berisi nama wilayah (misal:
"North").
Rumus dinamisnya:
=GETPIVOTDATA($D$1, $A$1,
"Product", $D$2, "Region", $D$3)
Setiap kali Anda mengubah nilai
di D1, D2, atau D3, rumus akan otomatis memperbarui hasilnya.
Anda juga dapat menghubungkannya dengan drop-down list untuk
membuat laporan interaktif. Jangan lupa gunakan IFERROR untuk
menangani data yang tidak ditemukan.
=IFERROR(GETPIVOTDATA("Sum
of Sales", $A$1, "Product", F1, "Region", F2),
"N/A")
Alternatif Lainnya untuk
Pencarian Dinamis
Meskipun XLOOKUP dan GETPIVOTDATA adalah
bintang utama, ada fungsi lain yang tak kalah berguna tergantung kebutuhan dan
versi Excel Anda.
1. Fungsi FILTER (Microsoft
365)
Fungsi FILTER adalah
fungsi array dinamis yang tersedia di Microsoft 365. Ini sangat baik untuk
mengekstrak catatan yang cocok berdasarkan satu atau beberapa kondisi.
Keunggulan FILTER:
- Mengembalikan Banyak Kecocokan: Berbeda
dengan VLOOKUP atau XLOOKUP yang hanya mengembalikan
kecocokan pertama, FILTER dapat mengembalikan semua
baris yang memenuhi kriteria.
- Multi-Kriteria: Mudah menerapkan
beberapa kondisi untuk memfilter data Anda.
- Output Dinamis: Hasilnya
"meluas" ke sel-sel yang berdekatan dan otomatis diperbarui jika
data sumber atau kriteria berubah.
Contoh:
=FILTER(A2:D10,
(B2:B10="Produk A") * (C2:C10="Region Utara"), "Tidak
Ditemukan")
Ini akan mengembalikan semua
baris dari A2:D10 di mana kolom B adalah "Produk A" dan
kolom C adalah "Region Utara".
2. Fungsi SUMPRODUCT
(Kompatibilitas Mundur)
Meskipun bukan fungsi lookup langsung, SUMPRODUCT dapat
digunakan secara efektif untuk lookup multi-kriteria, terutama
ketika Anda perlu menjumlahkan atau menghitung nilai berdasarkan beberapa
kondisi.
Keunggulan SUMPRODUCT:
- Kalkulasi Multi-Kriteria: Unggul dalam
melakukan perhitungan (seperti penjumlahan) di mana beberapa kondisi harus
dipenuhi.
- Kompatibilitas Mundur: Telah tersedia
di Excel sejak lama, cocok untuk pengguna versi Excel yang lebih lama yang
tidak memiliki XLOOKUP atau FILTER.
Contoh:
=SUMPRODUCT((A2:A10="Produk
A")*(B2:B10="Jan")*(C2:C10))
Ini akan menjumlahkan nilai
di C2:C10 di mana kolom A adalah "Produk A" dan kolom B
adalah "Januari".
Kesimpulan
Mengganti rumus INDEX +
MATCH + MATCH yang panjang untuk lookup dinamis di Excel
kini lebih mudah dari sebelumnya. XLOOKUP adalah pilihan
terbaik dan paling serbaguna bagi pengguna Excel modern, menawarkan sintaks
yang lebih bersih dan fleksibilitas tak tertandingi untuk pencarian dua arah.
Jika Anda bekerja dengan PivotTable, GETPIVOTDATA adalah alat
yang tak tergantikan untuk laporan yang sangat dinamis. Sementara itu, FILTER memperluas
kemampuan Anda untuk mengembalikan banyak hasil, dan SUMPRODUCT tetap
menjadi solusi Andal untuk perhitungan multi-kriteria di versi Excel yang lebih
lama.
Manfaatkan fungsi-fungsi dasar
Excel yang canggih ini untuk meningkatkan efisiensi dan akurasi analisis data
Anda!
Temukan alternatif modern dan ringkas untuk pencarian data dinamis di Excel.
Bagi pengguna Excel, melakukan
pencarian data adalah tugas sehari-hari. Namun, mencari nilai secara dinamis di
tabel dua dimensi, misalnya berdasarkan kriteria baris dan kolom, seringkali
memerlukan rumus yang kompleks. Dulu, kombinasi INDEX + MATCH + MATCH adalah
solusi standar, namun sintaksnya yang panjang dan cenderung rumit seringkali
membuat bingung.
Kabar baiknya, seiring dengan
evolusi Excel, kini ada berbagai fungsi yang lebih modern dan ringkas untuk
melakukan lookup dinamis, bahkan tanpa perlu rumus sekompleks
itu. Artikel ini akan membahas beberapa alternatif terbaik, terutama bagi Anda
yang menggunakan Microsoft 365 atau Excel versi terbaru.
XLOOKUP: Solusi Tercanggih
untuk Pencarian Dua Arah
Jika Anda menggunakan Microsoft
365 atau Excel 2021, fungsi XLOOKUP adalah pilihan utama yang
sangat direkomendasikan. Dirancang untuk menggantikan VLOOKUP, HLOOKUP,
dan bahkan kombinasi INDEX + MATCH, XLOOKUP menawarkan sintaks
yang lebih sederhana dan fleksibilitas yang luar biasa, terutama untuk
pencarian dua arah (dua kriteria).
Mengapa XLOOKUP Unggul?
- Sintaks Lebih Simpel: Jauh lebih mudah
dibaca dan ditulis dibandingkan INDEX + MATCH untuk pencarian
dua arah.
- Pencarian Fleksibel: Dapat mencari
nilai ke kiri atau ke kanan, serta ke atas atau ke bawah, mengatasi
batasan VLOOKUP.
- Pencocokan Tepat (Default): Secara default, XLOOKUP mencari
kecocokan yang tepat, mengurangi kesalahan umum.
- Penanganan Nilai Tidak Ditemukan: Memiliki
argumen bawaan untuk menentukan apa yang harus dikembalikan jika nilai
tidak ditemukan, menghindari kesalahan #N/A.
Contoh XLOOKUP untuk Pencarian
Dua Arah:
Bayangkan Anda memiliki tabel
penjualan dengan produk di kolom pertama dan bulan di baris header. Anda ingin
mencari penjualan suatu produk di bulan tertentu.
|
Jan |
Feb |
Mar |
|
|
Apel |
100 |
120 |
110 |
|
Pisang |
150 |
130 |
160 |
|
Jeruk |
90 |
115 |
95 |
Misalnya, Anda ingin mencari
penjualan "Pisang" di bulan "Februari".
- Nilai Produk yang dicari: "Pisang"
(misalnya di sel G1)
- Nilai Bulan yang dicari: "Feb" (misalnya
di sel G2)
- Hasil akan muncul di G3.
Rumusnya akan menjadi:
=XLOOKUP(G2, B1:D1, XLOOKUP(G1,
A2:A4, B2:D4))
Bagaimana rumus ini bekerja?
- XLOOKUP(G1, A2:A4, B2:D4) (XLOOKUP Bagian
Dalam):
- G1 ("Pisang") adalah nilai yang
dicari untuk produk.
- A2:A4 adalah rentang tempat mencari nama
produk.
- B2:D4 adalah rentang data yang akan
dikembalikan. Karena lookup_array (A2:A4) adalah rentang
vertikal dan return_array (B2:D4) mencakup banyak kolom, XLOOKUP bagian
dalam ini akan mengembalikan seluruh baris yang sesuai
dengan "Pisang" sebagai sebuah array, yaitu {150, 130,
160}.
- =XLOOKUP(G2, B1:D1, {150, 130, 160}) (XLOOKUP
Bagian Luar):
- G2 ("Feb") adalah nilai yang dicari
untuk bulan.
- B1:D1 adalah rentang tempat mencari header bulan.
- {150, 130, 160} adalah return_array (hasil
dari XLOOKUP bagian dalam). XLOOKUP bagian luar akan
menemukan "Feb" di B1:D1 dan mengembalikan nilai yang
sesuai dari posisi kedua dalam array {150, 130, 160}, yaitu 130.
Dengan rumus ini, Anda dapat
mengubah nama produk atau bulan di sel G1 dan G2, dan hasil di G3 akan otomatis
diperbarui, menjadikan lookup ini benar-benar dinamis.
Anda juga bisa menambahkan
penanganan error untuk tampilan yang lebih rapi:
=XLOOKUP(G2, B1:D1, XLOOKUP(G1,
A2:A4, B2:D4, "Produk Tidak Ditemukan"), "Bulan Tidak
Ditemukan")
GETPIVOTDATA: Dinamis untuk
PivotTable
Jika data Anda sudah diatur dalam
bentuk PivotTable, GETPIVOTDATA adalah cara yang sangat
efisien dan dinamis untuk mengambil data tertentu. Fungsi ini merujuk pada
struktur PivotTable, bukan lokasi sel statis, sehingga tetap berfungsi meskipun
tata letak PivotTable berubah. Ini sangat ideal untuk membuat dashboard dan
laporan interaktif.
Sintaks GETPIVOTDATA:
=GETPIVOTDATA(data_field,
pivot_table, [field1, item1], [field2, item2], ...)
- data_field: Nama bidang nilai (misalnya "Sum
of Sales").
- pivot_table: Referensi ke sel mana pun di dalam
PivotTable.
- [field, item]: Pasangan nama bidang dan item yang
menentukan kriteria pencarian.
Membuat GETPIVOTDATA Dinamis
dengan Referensi Sel:
Kunci untuk membuat GETPIVOTDATA dinamis
adalah mengganti nilai yang dikodekan secara langsung dengan referensi sel.
Contoh Dinamis:
Asumsikan PivotTable Anda dimulai
di A1.
- Sel D1 berisi nama bidang data yang
dicari (misal: "Sum of Sales").
- Sel D2 berisi nama produk (misal:
"Product A").
- Sel D3 berisi nama wilayah (misal:
"North").
Rumus dinamisnya:
=GETPIVOTDATA($D$1, $A$1,
"Product", $D$2, "Region", $D$3)
Setiap kali Anda mengubah nilai
di D1, D2, atau D3, rumus akan otomatis memperbarui hasilnya.
Anda juga dapat menghubungkannya dengan drop-down list untuk
membuat laporan interaktif. Jangan lupa gunakan IFERROR untuk
menangani data yang tidak ditemukan.
=IFERROR(GETPIVOTDATA("Sum
of Sales", $A$1, "Product", F1, "Region", F2),
"N/A")
Alternatif Lainnya untuk
Pencarian Dinamis
Meskipun XLOOKUP dan GETPIVOTDATA adalah
bintang utama, ada fungsi lain yang tak kalah berguna tergantung kebutuhan dan
versi Excel Anda.
1. Fungsi FILTER (Microsoft
365)
Fungsi FILTER adalah
fungsi array dinamis yang tersedia di Microsoft 365. Ini sangat baik untuk
mengekstrak catatan yang cocok berdasarkan satu atau beberapa kondisi.
Keunggulan FILTER:
- Mengembalikan Banyak Kecocokan: Berbeda
dengan VLOOKUP atau XLOOKUP yang hanya mengembalikan
kecocokan pertama, FILTER dapat mengembalikan semua
baris yang memenuhi kriteria.
- Multi-Kriteria: Mudah menerapkan
beberapa kondisi untuk memfilter data Anda.
- Output Dinamis: Hasilnya
"meluas" ke sel-sel yang berdekatan dan otomatis diperbarui jika
data sumber atau kriteria berubah.
Contoh:
=FILTER(A2:D10,
(B2:B10="Produk A") * (C2:C10="Region Utara"), "Tidak
Ditemukan")
Ini akan mengembalikan semua
baris dari A2:D10 di mana kolom B adalah "Produk A" dan
kolom C adalah "Region Utara".
2. Fungsi SUMPRODUCT
(Kompatibilitas Mundur)
Meskipun bukan fungsi lookup langsung, SUMPRODUCT dapat
digunakan secara efektif untuk lookup multi-kriteria, terutama
ketika Anda perlu menjumlahkan atau menghitung nilai berdasarkan beberapa
kondisi.
Keunggulan SUMPRODUCT:
- Kalkulasi Multi-Kriteria: Unggul dalam
melakukan perhitungan (seperti penjumlahan) di mana beberapa kondisi harus
dipenuhi.
- Kompatibilitas Mundur: Telah tersedia
di Excel sejak lama, cocok untuk pengguna versi Excel yang lebih lama yang
tidak memiliki XLOOKUP atau FILTER.
Contoh:
=SUMPRODUCT((A2:A10="Produk
A")*(B2:B10="Jan")*(C2:C10))
Ini akan menjumlahkan nilai
di C2:C10 di mana kolom A adalah "Produk A" dan kolom B
adalah "Januari".
Kesimpulan
Mengganti rumus INDEX +
MATCH + MATCH yang panjang untuk lookup dinamis di Excel
kini lebih mudah dari sebelumnya. XLOOKUP adalah pilihan
terbaik dan paling serbaguna bagi pengguna Excel modern, menawarkan sintaks
yang lebih bersih dan fleksibilitas tak tertandingi untuk pencarian dua arah.
Jika Anda bekerja dengan PivotTable, GETPIVOTDATA adalah alat
yang tak tergantikan untuk laporan yang sangat dinamis. Sementara itu, FILTER memperluas
kemampuan Anda untuk mengembalikan banyak hasil, dan SUMPRODUCT tetap
menjadi solusi Andal untuk perhitungan multi-kriteria di versi Excel yang lebih
lama.
Manfaatkan fungsi-fungsi dasar
Excel yang canggih ini untuk meningkatkan efisiensi dan akurasi analisis data
Anda!
.png)
Tidak ada komentar:
Posting Komentar