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 -