Analisis Data Excel - Fungsi Pencarian
Anda dapat menggunakan fungsi Excel untuk -
- Temukan nilai dalam berbagai data - VLOOKUP dan HLOOKUP
- Dapatkan nilai atau referensi ke nilai dari dalam tabel atau rentang - INDEX
- Dapatkan posisi relatif dari item tertentu dalam rentang sel - MATCH
Anda juga dapat menggabungkan fungsi-fungsi ini untuk mendapatkan hasil yang diperlukan berdasarkan masukan yang Anda miliki.
Menggunakan Fungsi VLOOKUP
Sintaks dari fungsi VLOOKUP adalah
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])Dimana
lookup_value- adalah nilai yang ingin Anda cari. Lookup_value bisa berupa nilai atau referensi ke sel. Lookup_value harus berada di kolom pertama dari rentang sel yang Anda tentukan di table_array
table_array- adalah rentang sel di mana VLOOKUP akan mencari lookup_value dan return value. table_array harus berisi
lookup_value di kolom pertama, dan
nilai kembali yang ingin Anda temukan
Note- Kolom pertama yang berisi lookup_value dapat diurutkan dalam urutan menaik atau tidak. Namun, hasilnya akan berdasarkan urutan kolom ini.
col_index_num- adalah nomor kolom dalam table_array yang berisi nilai yang dikembalikan. Angka dimulai dengan 1 untuk kolom paling kiri dari tabel-larik
range_lookup- adalah nilai logika opsional yang menentukan apakah Anda ingin VLOOKUP menemukan kecocokan persis atau perkiraan kecocokan. range_lookup bisa
dihilangkan, dalam hal ini dianggap TRUE dan VLOOKUP mencoba menemukan perkiraan yang cocok
BENAR, dalam hal ini VLOOKUP mencoba menemukan perkiraan yang cocok. Dengan kata lain, jika pencocokan tepat tidak ditemukan, nilai terbesar berikutnya yang kurang dari lookup_value akan dikembalikan
FALSE, dalam hal ini VLOOKUP mencoba menemukan kecocokan yang sama persis
1, dalam hal ini diasumsikan TRUE dan VLOOKUP mencoba menemukan perkiraan yang cocok
0, dalam hal ini dianggap FALSE dan VLOOKUP mencoba menemukan kecocokan yang sama persis
Note- Jika range_lookup dihilangkan atau TRUE atau 1, VLOOKUP hanya berfungsi dengan benar saat kolom pertama dalam table_array diurutkan dalam urutan menaik. Jika tidak, ini dapat menghasilkan nilai yang salah. Dalam kasus seperti itu, gunakan FALSE untuk range_lookup.
Menggunakan Fungsi VLOOKUP dengan range_lookup TRUE
Pertimbangkan daftar nilai siswa. Anda bisa mendapatkan nilai yang sesuai dengan VLOOKUP dari array yang berisi interval tanda dan kategori lulus.
table_array -
Perhatikan bahwa tanda kolom pertama berdasarkan nilai yang diperoleh diurutkan dalam urutan menaik. Karenanya, menggunakan TRUE untuk argumen range_lookup Anda bisa mendapatkan perkiraan kecocokan yang diperlukan.
Beri nama array ini sebagai Grades.
Ini adalah praktik yang baik untuk menamai array dengan cara ini sehingga Anda tidak perlu mengingat rentang sel. Sekarang, Anda siap mencari nilai untuk daftar nilai yang Anda miliki sebagai berikut -
Seperti yang bisa Anda amati,
col_index_num - menunjukkan kolom nilai yang dikembalikan dalam table_array adalah 2
itu range_lookup adalah benar
Kolom pertama yang berisi nilai pencarian di nilai table_array dalam urutan menaik. Karenanya, hasilnya akan benar.
Anda juga bisa mendapatkan nilai pengembalian untuk perkiraan kecocokan. Yaitu VLOOKUP menghitung sebagai berikut -
| Tanda | Lulus Kategori |
|---|---|
| <35 | Gagal |
| > = 35 dan <50 | Kelas ke tiga |
| > = 50 dan <60 | Kelas kedua |
| > = 60 dan <75 | Kelas utama |
| > = 75 | Kelas Satu dengan Perbedaan |
Anda akan mendapatkan hasil sebagai berikut -
Menggunakan Fungsi VLOOKUP dengan range_lookup FALSE
Pertimbangkan daftar produk yang berisi ID Produk dan harga untuk setiap produk. ID produk dan harga akan ditambahkan ke akhir daftar setiap kali produk baru diluncurkan. Ini berarti ID produk tidak harus dalam urutan menaik. Daftar produk mungkin seperti yang ditunjukkan di bawah ini -
table_array -
Beri nama array ini sebagai ProductInfo.
Anda bisa mendapatkan harga produk berdasarkan ID produk dengan fungsi VLOOKUP karena ID produk ada di kolom pertama. Harganya ada di kolom 3 dan karenanya nomor col_index_ harus 3.
- Gunakan Fungsi VLOOKUP dengan range_lookup sebagai TRUE
- Gunakan Fungsi VLOOKUP dengan range_lookup sebagai FALSE
Jawaban yang benar adalah dari larik ProductInfo adalah 171.65. Anda bisa mengecek hasilnya.
Anda mengamati bahwa Anda mendapat -
- Hasil yang benar saat range_lookup adalah FALSE, dan
- Hasil yang salah saat range_lookup TRUE.
Ini karena, kolom pertama dalam larik ProductInfo tidak diurutkan dalam urutan menaik. Oleh karena itu, ingatlah untuk menggunakan FALSE setiap kali data tidak diurutkan.
Menggunakan Fungsi HLOOKUP
Kamu dapat memakai HLOOKUP berfungsi jika data dalam baris, bukan kolom.
Contoh
Mari kita ambil contoh informasi produk. Misalkan array terlihat sebagai berikut -
Beri nama Array ProductRange ini. Anda dapat menemukan harga produk berdasarkan ID produk dengan fungsi HLOOKUP.
Sintaks dari fungsi HLOOKUP adalah
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])Dimana
lookup_value - adalah nilai yang dapat ditemukan di baris pertama tabel
table_array - adalah tabel informasi tempat data dicari
row_index_num - adalah nomor baris dalam table_array dari mana nilai yang cocok akan dikembalikan
range_lookup - adalah nilai logika yang menentukan apakah Anda ingin HLOOKUP menemukan kecocokan tepat atau perkiraan kecocokan
range_lookup dapat
dihilangkan, dalam hal ini diasumsikan TRUE dan HLOOKUP mencoba menemukan perkiraan yang cocok
BENAR, dalam hal ini HLOOKUP mencoba menemukan perkiraan yang cocok. Dengan kata lain, jika pencocokan tepat tidak ditemukan, nilai terbesar berikutnya yang kurang dari lookup_value akan dikembalikan
FALSE, dalam hal ini HLOOKUP mencoba menemukan kecocokan persis
1, dalam hal ini diasumsikan TRUE dan HLOOKUP mencoba menemukan perkiraan yang cocok
0, dalam hal ini diasumsikan sebagai FALSE dan HLOOKUP mencoba menemukan kecocokan yang sama persis
Note- Jika range_lookup Dihilangkan atau TRUE atau 1, HLOOKUP hanya berfungsi dengan benar saat kolom pertama dalam table_array diurutkan dalam urutan menaik. Jika tidak, ini dapat menghasilkan nilai yang salah. Dalam kasus seperti itu, gunakan FALSE untuk range_lookup.
Menggunakan Fungsi HLOOKUP dengan range_lookup FALSE
Anda bisa mendapatkan harga produk berdasarkan ID produk dengan fungsi HLOOKUP karena ID produk berada di baris pertama. Harganya ada di baris 3 dan oleh karena itu nomor baris_indeks harus menjadi 3.
- Gunakan Fungsi HLOOKUP dengan range_lookup sebagai TRUE.
- Gunakan Fungsi HLOOKUP dengan range_lookup sebagai FALSE.
Jawaban yang benar dari larik ProductRange adalah 171,65. Anda bisa mengecek hasilnya.
Anda mengamati bahwa seperti dalam kasus VLOOKUP, Anda mendapatkannya
Hasil yang benar saat range_lookup adalah FALSE, dan
Hasil yang salah saat range_lookup TRUE.
Ini karena baris pertama dalam larik ProductRange tidak diurutkan dalam urutan menaik. Oleh karena itu, ingatlah untuk menggunakan FALSE setiap kali data tidak diurutkan.
Menggunakan Fungsi HLOOKUP dengan range_lookup TRUE
Perhatikan contoh nilai siswa yang digunakan dalam VLOOKUP. Misalkan Anda memiliki data dalam baris dan bukan kolom seperti yang ditunjukkan pada tabel yang diberikan di bawah ini -
table_array -
Beri nama array ini sebagai GradesRange.
Perhatikan bahwa tanda baris pertama berdasarkan nilai yang diperoleh diurutkan dalam urutan menaik. Karenanya, menggunakan HLOOKUP dengan TRUE untuk argumen range_lookup, Anda bisa mendapatkan Nilai dengan perkiraan kecocokan dan itulah yang diperlukan.
Seperti yang bisa Anda amati,
row_index_num - menunjukkan kolom nilai yang dikembalikan dalam table_array adalah 2
itu range_lookup adalah benar
Kolom pertama yang berisi nilai pencarian dalam table_array Grades berada dalam urutan menaik. Karenanya, hasilnya akan benar.
Anda juga bisa mendapatkan nilai pengembalian untuk perkiraan kecocokan. yaitu HLOOKUP menghitung sebagai berikut -
| Tanda | <35 | > = 35 dan <50 | > = 50 dan <60 | > = 60 dan <75 | > = 75 |
|---|---|---|---|---|---|
| Lulus Kategori | Gagal | Kelas ke tiga | Kelas kedua | Kelas utama | Kelas Satu dengan Perbedaan |
Anda akan mendapatkan hasil sebagai berikut -
Menggunakan Fungsi INDEX
Saat Anda memiliki larik data, Anda bisa mengambil nilai dalam larik dengan menentukan nomor baris dan nomor kolom dari nilai itu dalam larik.
Pertimbangkan data penjualan berikut, di mana Anda menemukan penjualan di masing-masing wilayah Utara, Selatan, Timur, dan Barat menurut staf penjualan yang terdaftar.
- Beri nama array sebagai SalesData.
Menggunakan Fungsi INDEX, Anda dapat menemukan -
- Penjualan salah satu Penjual di Wilayah tertentu.
- Total Penjualan di suatu Wilayah oleh semua Penjual.
- Total Penjualan oleh Penjual di semua Wilayah.
Anda akan mendapatkan hasil sebagai berikut -
Misalkan Anda tidak mengetahui nomor baris untuk tenaga penjual dan nomor kolom untuk wilayah. Kemudian, Anda perlu mencari nomor baris dan nomor kolom terlebih dahulu sebelum mengambil nilai dengan fungsi indeks.
Anda dapat melakukannya dengan fungsi MATCH seperti yang dijelaskan di bagian selanjutnya.
Menggunakan Fungsi MATCH
Jika Anda membutuhkan posisi item dalam suatu rentang, Anda dapat menggunakan fungsi MATCH. Anda dapat menggabungkan fungsi MATCH dan INDEX sebagai berikut -
Anda akan mendapatkan hasil sebagai berikut -
