Tutorial Mudah Penggunaan Fungsi Lanjutan Excel

Sebagai salah satu tools analisis data, Excel memiliki banyak fungsi dan rumus yang disesuaikan dengan tingkat kesulitannya. Ada yang dasar, tingkat lanjut, hingga level profesional yang biasanya membutuhkan logika mendalam dan kemampuan coding. Mayoritas orang sudah mahir pada tingkat dasar, tapi belum banyak yang menguasai fungsi lanjutan.

Oleh karena itu, artikel kali ini bertujuan untuk mengenalkan beberapa fungsi lanjutan Excel tersebut sekaligus tutorial untuk menggunakannya. Fokus tutorial ini hanya pada fungsi lanjutan yang sering digunakan di tempat kerja. Apa saja fungsi-fungsi tersebut? Yuk, cari tahu langsung di pembahasan berikut ini!

1. VLOOKUP dan HLOOKUP

VLOOKUP dan HLOOKUP adalah dua fungsi Excel yang berguna untuk mencari nilai di dalam tabel berdasarkan kriteria tertentu. VLOOKUP digunakan untuk mencari nilai secara vertikal, sementara HLOOKUP untuk mencari nilai secara horizontal.

Format umum dari VLOOKUP an HLOOKUP adalah:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Supaya lebih jelas, perhatikan contoh data berikut. Misalnya disajikan data buah dan harganya per kg.

Kemudian kita ingin mengetahui harga pisang. Karena data dalam bentuk memanjang vertikal, gunakan rumus VLOOKUP. Ikuti rumus berikut.

=VLOOKUP("Pisang";B2:C6;2;FALSE)

Maka nantinya akan diperoleh nilai 8000, sesuai dengan data yang diberikan harga per kg dari pisang adalah Rp8.000. Bagaimana jika datanya berbentuk memanjang horizontal seperti berikut ini?

Nah, jika demikian kalian bisa menggunakan HLOOKUP. Ikuti rumus berikut ini.

=HLOOKUP("Pisang";F2:J3;2;FALSE)

Hasilnya akan tetap sama yaitu Rp8.000. Apakah kalian sudah bisa memahami perbedaannya? Jadi, kalau datanya memanjang vertikal gunakan fungsi VLOOKUP, sedangkan untuk data memanjang horizontal gunakan fungsi HLOOKUP.

Baca juga : Rumus Excel yang Paling Sering Digunakan dalam Dunia Kerja

2. INDEX dan MATCH

INDEX dan MATCH adalah dua fungsi Excel yang bekerja bersamaan untuk melakukan pencarian nilai di dalam tabel. Fungsi INDEX mengembalikan nilai dari sel di dalam rentang berdasarkan nomor baris dan kolom yang ditentukan. Format umumnya adalah:

=INDEX(array, row_number, [column_number])

Sementara fungsi MATCH mencari nilai tertentu dalam satu baris atau kolom dan mengembalikan posisinya. Rumus umumnya yaitu:

=MATCH(lookup_value, lookup_array, [match_type])

Penggunaan bersamaan INDEX dan MATCH memungkinkan pencarian nilai dalam rentang data yang lebih fleksibel, terutama saat tabel yang dimiliki lebih kompleks.

Kita gunakan data yang sama dari sebelumnya, yaitu buah dengan harga per kg. Kemudian kita akan mencari harga pisang dengan menggunakan kombinasi INDEX dan MATCH. Maka, rumusnya adalah:

=INDEX(C2:C6; MATCH("Pisang";B2:B6;0))

Dalam contoh ini MATCH("Pisang", B2:B6, 0) mencari posisi "Pisang" dalam kolom Buah. Sedangkan INDEX(C2:C6, ...) mengambil nilai dari kolom Harga (kolom C) pada posisi yang telah ditemukan oleh MATCH.

3. SUMIF dan SUMIFS

SUMIF dan SUMIFS adalah fungsi dalam Excel yang digunakan untuk menjumlahkan nilai dalam suatu rentang berdasarkan kriteria tertentu. Perbedaan keduanya terletak pada banyaknya kriteria.

Kalau SUMIF menggunakan satu kriteria, sedangkan SUMIFS lebih dari satu. Masing-masing rumusnya adalah:

=SUMIF(range, criteria, [sum_range])

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Yuk, perhatikan contoh di bawah ini untuk tahu penggunaan rumus tersebut. Misalkan kamu memiliki data penjualan produk dengan jumlah dan nama seller.

Kemudian kita ingin menghitung total penjualan laptop. Maka gunakan rumus SUMIF seperti contoh berikut.

=SUMIF(B2:B7;"Laptop";C2:C7)

Tapi, jika kalian ingin lebih spesifik misalnya laptop yang dijual oleh Anindya, maka gunakan rumus SUMIFS.

=SUMIFS(C2:C7;B2:B7;"Laptop";D2:D7;"Anindya")

Jadi, bisa disimpulkan gunakan SUMIF jika hanya ada satu kriteria dan SUMIFS untuk kriteria yang lebih kompleks. Mudah dipahami kan?

Baca juga : Bootcamp Data Analyst with SQL and Python

4. Latihan Lanjutan Ada di DQLab!

Pasti seru belajar fungsi lanjutan Excel. Karena dengan memahami beberapa fungsi tersebut, kalian bisa melakukan analisis data yang lebih mendalam dengan cara yang mudah. Penasaran apa lagi fungsi lanjutan lainnya yang ada di Excel? Kalian bisa mulai belajar di DQLab.

Modul ajarnya lengkap dan bervariasi. Semua skill yang dibutuhkan akan diajarkan. Dilengkapi studi kasus yang membantu kalian belajar memecahkan masalah dari berbagai industri. Bahkan diintegrasikan dengan ChatGPT. Manfaatnya apa?

• Membantu kalian menjelaskan lebih detail code yang sedang dipelajari

• Membantu menemukan code yang salah atau tidak sesuai

• Memberikan solusi atas problem yang dihadapi pada code

• Membantu kalian belajar kapanpun dan dimanapun

Selain itu, DQLab juga menggunakan metode HERO yaitu Hands-On, Experiential Learning & Outcome-based, yang dirancang ramah untuk pemula. Tunggu apa lagi, segera Sign Up dan persiapkan diri untuk menguasai Excel dari level apapun. Yuk ikuti Baca juga : Bootcamp Data Analyst with Excel! 

Penulis : Dita Feby 

Editor : Annissa Widya