Membuat Histogram Menggunakan Rumus Excel

Grafik histogram di excel biasanya dibuat menggunakan fitur Insert Chart. Namun tahukah anda, ternyata histogram juga dapat dibuat menggunakan rumus. Nah, sesuai topik blog ini yang membahas berbagai contoh rumus excel, kali ini kita akan membahas bagaimana membuat histogram menggunakan rumus. Fungsi yang dapat digunakan untuk membuat histrogram adalah fungsi REPT dengan tambahan sedikit modifikasi pada jenis font.

Histogram sejatinya berupa grafik batang berbentuk horizontal maupun vertikal yang menunjukan nilai tertentu. Histogram horizontal, karena bentuknya yang sederhana ternyata bisa dibuat dari susunan karakter tetentu dalam sebuah sel.

Contoh Soal:

Anggaplah sudah tersedia sebuah tabel pencapaian target penjualan dari beberapa sales. Tabel terdiri atas 5 kolom, kolom ke-1 berisi nama SPG, kolom ke-2 berisi data target penjualan, kolom ke-3 berisi aktual pencapaian, kolom ke-4 berisi persen pencapaian dibanding target dan terakhir kolom ke-5 kita harus mengisinya dengan grafik histogram horizontal yang dihasilkan oleh sebuah formula excel. Gambaran contoh soal dapat dilihat pada gambar di bawah ini:

Membuat Histogram Horizontal Menggunakan Rumus Excel

Untuk membuat histogram ini kita bisa menggunakan salah satu fungsi excel yang seperti nya jarang tersentuh oleh pengguna excel, yaitu fungsi REPT. Fungsi merupakan salah satu fungsi pengolah TEXT yang berguna untuk menghasilkan gabungan text yang sama sejumlah yang ditentukan dalam argumen fungsi.

Fungsi REPT dapat dituliskan sebagai berikut:

=REPT(text,jumlahPengulangan)

Misalnya:

• Rumus =REPT("ka",1) ⇒ hasilnya "ka"
• Rumus =REPT("ka",2) ⇒ hasilnya "kaka"
• Rumus =REPT("ka",3) ⇒ hasilnya "kakaka"

Begitu seterusnya, rumus REPT akan menghasilkan pengulangan text "ka" sejumlah nilai yang ditentukan pada argumen jumlahPengulangan.

Kemampuan fungsi REPT ini yang bisa dimanfaatkan untuk membuat grafik histogram horizontal. Caranya adalah dengan menmilih karakter tertentu dan jenis font yang tepat sehingga jika karakter tersebut disusun secara berulang akan membentuk sebuah balok.

Dengan menggunakan tabel contoh, maka kita bisa membuat rumus di cell E2 sebagai berikut:

=REPT("|",D2*100/10)

Kemudian copy rumus tersebut ke sel di bawahnya sampai baris terakhir dalam tabel.

Set jenis font di kolom E = "Arial"

Maka kita akan melihat susunan pengulangan karakter "|" dengan jumlah berbanding lurus dengan persentase pencapaian target. Pembagi 10 dimaksudkan supaya susunan karakter tidak terlalu panjang.

Untuk mendapatkan bentuk grafik histogram yang lebih menarik, kita bisa mencoba beberapa karakter dengan jenis font tertentu. Sebagai contoh,  jika jenis font pada gambar diatas dirubah menjadi font "Britannic Bold" maka hasilnya akan seperti gambar dibawah ini:

Sampai pada tahap ini kita sudah berhasil membuat grafik histogram horizontal menggunakan rumus excel. Cara kerja dan metode cara kerja rumus ini sangat mudah dan sederhana. Yang diperlukan hanya sedikit kreatifitas dengan mencoba berbagai karakter dan jenis font untuk mendapatkan tampilan grafik yang lebih menarik.

Demikian pembahasan contoh rumus excel untuk membuat grafik histogram. Semoga bermanfaat.

Contoh Rumus Excel Lainnya:

• Rumus Excel Jika Minus Maka Nol
• Merubah Angka Menjadi Bulan
• Merubah Tanggal Menjadi Hari dan Bulan
• Menghitung Banyaknya Data Tertentu

Rumus Excel Jika Minus Maka Nol

Rumus pengurangan menggunakan excel bisa menghasilkan bilangan positif, nol, atau negatif. Akan tetapi dalam beberapa kasus mungkin bilangan negatif atau minus tidak dikehendaki. Misalnya pada saat kita kita menginginkan nilai minimum hasil pengurangan adalah nol. Dengan kata lain jika operasi pengurangan menghasilkan bilangan minus maka bilangan tersebut harus dirubah menjadi Nol. Di excel hal ini dapat dibuat menggunakan rumus yang cukup sederhana. Dalam kesempatan ini akan disampaikan dua alternative rumus untuk keperluan tersebut. Rumus pertama menggunakan fungsi IF, sedangkan rumus kedua menggunakan fungsi MAX.

Contoh Soal:

Tersedia sebuah tabel dalam lembar kerja excel. Tabel tersebut terdiri atas 3 kolom. Kolom pertama berisi bilangan nilai awal, kolom kedua berisi nilai pengurang dan kolom ketiga berisi nilai akhir. Nilai pada kolom ketiga harus diformulasikan yaitu berupa pengurangan bilangan di kolom pertama dengan nilai pada kolom ke-2 dengan satu syarat: Jika hasil pengurangan berupa bilangan negatif atau minus, maka hasil akhirnya harus bilangan Nol.

Gambaran contoh soal dapat dilihat dalam screenshot berikut ini.

Untuk menjawab soal tersebut, disini kita akan mengunakan dua alternative rumus. Yaitu rumus IF dan rumus MAX.

Contoh rumus pertama: Menjadikan Nilai Minus menjadi Nol menggunakan rumus IF.

Secara umum, rumus IF untuk tujuan ini dapat dituliskan sebagai berikut:

=IF(nilai-pengurang<0,0,nilai-pengurang)

Logika rumus tersebut dapat kita uraikan sebagai berikut: Jika nilai dikurangi pengurang hasilnya lebih kecil dari Nol, maka hasil akhir sama dengan Nol, jika tidak kurang dari nol maka hasilnya sama dengan nilai dikurangi pengurang.

Dengan menerapkan pola rumus diatas pada spreadsheet di sel C2, maka rumus dapat dituliskan sebagai berikut

=IF(A2-B2<0,0,A2-B2)

Copy rumus dari sel B2 tersebut ke sel dibawahnya sampai baris akhir tabel. Maka kita akan melihat hasilnya seperti screenshot di bawah ini.

Perhatikan sel yang di-hightlight kuning pada gambar di atas. Misalnya sel C2. Kita tahu bahwa 25 dikurangi 30 seharusnya adalah -5. Namun dengan menggunakan fungsi IF bilangan minus dapat kita ganti menjadi Nol.

Contoh Rumus Kedua: Menjadikan Nilai Minus Menjadi Nol Menggunakan Fungsi MAX.

Fungsi MAX akan menghasilkan nilai maksimum dari serangkaian bilangan. Dengan kegunaan nya ini kita bisa membandingkan nilai negatif dengan nol untuk menghasilkan nilai nol, karena nol pasti lebih besar nilainya dari bilangan negatif.

Rumus Umum:

=MAX(0,nilai-pengurang)

Rumus di atas dapat dibaca sebagai berikut: Fungsi MAX mencari nilai maksimum atau terbesar apakah nilai nol atau nilai-pengurang. Ini artinya jika nilai dikurangi pengurang hasilnya minus maka fungsi MAX akan memilih nol sebagai hasil dari rumus tersebut.

Dengan menerapkan fungsi MAX pada contoh soal, maka kita bisa menuliskan rumus di cell C2 sebagai berikut:

=MAX(0,A2-B2)

Copy rumus tersebut sampai baris akhir tabel, sehingga hasilnya akan nampak seperti gambar di bawah ini:

Dari gambar di atas, kita dapat melihat kolom Nilai Akhir berisi bilangan dengan nilai minimal 0, bukan bilangan negatif. Hal ini karena karena fungsi MAX membandingkan bilangan negatif dan bilangan NOL kemudian menghasilkan bilangan Nol.

Demikian pembahasan sangat singkat mengenai contoh rumus excel untuk operasi pengurangan dengan syarat jika hasilnya negatif atau minus maka hasil akhir dijadikan nol. Semoga penjelasan dengan bahasa Indonesia dan tehnik menulis seadanya ini dapat mudah difahami. Terimakasih.

Contoh rumus excel lainnya:

• Merubah angka menjadi bulan
• Merubah tanggal menjadi hari dan bulan
• Rumus IF Untuk Keterangan Lulus Tidak Lulus
• Rumus IF Untuk Menghitung Jam Lembur.

Merubah Angka Menjadi Bulan

Dalam postingan sebelumnya sudah dibahas mengenai cara merubah tanggal menjadi hari dan bulan. Nah dalam kesempatan ini kita akan membahas mengenai contoh rumus untuk merubah angka menjadi bulan. Dengan memahami tehnik ini, kita bisa dengan mudah mengkonversi bilangan 1 s/d 12 menjai text bulan “January” s.d “December”. Fungsi yang dapat digunakan dalam formula ini adalah fungsi TEXT dan fungsi DATE. Untuk memastikan supaya nama bulan dalam bahasa Indonesia, maka kode “[$-421]” perlu ditambahkan dalam parameter fungsi TEXT.  Dengan kode tersebut maka kita bisa merubah angka 1,2 s/d 12 menjadi “Januari”, “Februari” s/d “Desember” dalam bahasa Indonesia.

Contoh soal:

Anggaplah terdapat sebuah tabel terdiri atas 2 kolom. Kolom pertama berisi bilangan 1 s/d 12 dan kolom ke-2 berisi Nama Bulan. Data Nama bulan di kolom ke-2 tidak boleh diketik secara langsung. Namun harus diformulasikan dengan mengambil referensi dari kolom pertama. Contoh soal dapat dilihat dalam gambar di bawah ini.

Sebagaimana sudah dibahas dalam artikel merubah tanggal menjadi hari dan bulan, kita bisa merubah data berjenis tanggal menjadi nama bulan dengan rumus =TEXT(tanggal,"mmmm"). Dalam kasus tersebut diasumsikan sudah tersedia argumen tanggal yaitu data yang memang ber-type tanggal (date) dan diinput secara tepat mengikuti kaidah penulisan tanggal di excel.

Sedangkan dalam konteks pembahasan kali ini, di dalam tabel hanya tersedia bilangan 1 s/d 12, bukan data bertype tanggal. Karena angka tersebut akan dikonversi menjadi nama bulan, maka kita harus memperlakukannya sebagai bulan. Proses ini dapat dibantu menggunakan fungsi DATE yang nantinya digunakan untuk menempati parameter value tanggal dalam fungsi TEXT.

=DATE(tahun,bulan,hari)

Argumen tahun dan hari dapat kita ganti dengan sembarang angka positif, sedangkan argumen bulan dapat diganti dengan nomor urut bulan yang ingin kita rubah menjadi nama bulan. Fungsi DATE ini akan menghasilkan data bertype tanggal dengan bulan yang ditentukan.

Misalnya: anggaplah kita gunakan angka 1 untuk menggantkan parameter tahun dan tanggal. Maka rumus DATE dapat dituliskan sebagai berikut:

=DATE(1,1,1) ⇒ hasilnya adalah tanggal 1/1/2017  (format "d/m/yyyy")

=DATE(1,2,1) ⇒ hasilnya adalah tanggal 1/2/2017  (format "d/m/yyyy")

=DATE(1,3,1) ⇒ hasilnya adalah tanggal 1/3/2017  (format "d/m/yyyy")

Hasil dari fungsi DATE di atas kemudian dapat diolah lagi menggunakan fungsi TEXT sehingga dapat dikonversi menjadi nama bulan. Pada akhirnya rumus dapat ditulis sebagai berikut:

=TEXT(DATE(1,1,1), "mmmm") ⇒ Hasilnya "January"

=TEXT(DATE(1,2,1), "mmmm") ⇒ Hasilnya "February"

=TEXT(DATE(1,3,1), "mmmm") ⇒ Hasilnya "March"

Atas dasar contoh di atas dan jika bilangan nomor urut bulan diletakan di sel A2,  maka rumus dapat dituliskan sebagai berikut:

=TEXT(DATE(1,A2,1), "mmmm") 

Ketik rumus tersebut di sel B2 kemudian copy dan paste ke sel di bawahnya, sehingga hasilnya akan nampak seperti screenshot di bawah ini.

Catatan: Dari contoh di atas, rumus menghasilkan nama bulan dalam bahasa Inggris. Hal ini karena nama bulan yang diperoleh dengan kode "mmmm" akan tergantung pada language setting komputer yang anda gunakan. Jika komputer anda menggunakan setting Indonesia, mungkin hasilnya akan berbeda dengan contoh gambar di atas.

Untuk memaksa rumus supaya menghasilkan nama bulan dalam bahasa indonesia maka kita bisa memodifikasi format dengan menambahkan kode "[$-421]", sehingga rumus di sel B2 dapat kita tuliskan sebagai berikut:

=TEXT(DATE(1,A2,1), "[$-421]mmmm") 

Copy rumus di atas sampai baris akhir data, maka hasilnya dapat kita lihat seperti screenshot di bawah ini

Sampai pada tahap ini, kita sudah berhasil mengkonversi bilangan nomor urut bulan menjadi nama bulan dalam bahasa Indonesia.

Demikian pembahasan singkat mengenai contoh rumus untuk merubah angka menjadi bulan dan bagaimana cara memaksa rumus supaya menghasilkan nama bulan dalam bahasa Indonesia. Harapannya, postingan ini bisa bermanfaat bagi saya sendiri dan juga bagi pembaca.

Contoh Rumus Excel Lainnya:

• Merubah Tanggal Menjadi Hari dan Bulan
• Menghitung Banyaknya Data Tertentu
• Rumus VLOOKUP Dinamis dengan Fungsi INDIRECT
• Konversi Nomor Urut Kolom Menjadi Huruf

Merubah Tanggal Menjadi Hari dan Bulan

Dari sebuah data ber-type tanggal atau date, kita bisa menarik informasi terkait tanggal tersebut, misalnya informasi nama hari dan bulan. Sebagian pengguna excel masih saja ada yang menggunakan kalender untuk mengetahui hari apa di tanggal tertentu lalu mengetiknya secara manual di excel. Sedangkan untuk mengenali bulan secara manual mungkin bukan hal yang sulit. Namun tetap saja cukup merepotkan jika datanya sangat banyak. Excel sendiri sebenarnya sudah menyediakan beberapa alternative untuk menyajikan tanggal dalam bentuk nama hari dan bulan. Salah satunya adalah dengan menggunakan rumus atau formula excel. Fungsi yang diperlukan untuk keperluan ini adalah fungsi TEXT. Kode [$-421] diperlukan untuk mendapatkan nama hari dan bulan dalam bahasa Indonesia.

Contoh Soal

Misalnya sudah disediakan sebuah tabel yang terdiri atas 3 kolom. Kolom pertama berisi data tanggal, kolom kedua berisi nama hari serta kolom ketiga berisi nama bulan. Secara sederhana, tabel dapat dicontohkan seperti dalam gambar di bawah ini.

Rumus excel seperti apa yang bisa kita gunakan untuk mendapkan nama hari dan bulan?

Untuk mendapatkan nama  hari dan bulan kita bisa menggunakan fungsi TEXT. Fungsi ini dapat kita tuliskan sebagai berikut:

=TEXT(tanggal,format)

Tanggal = data bertype tanggal atau date, misalnya "01/05/2018"

Format = kode format tampilan text nama hari atau bulan yang diinginkan

Sebelum melangkah ke contoh rumus, perlu diperhatikan cara penulisan tanggal karena hal ini sangat dipengaruhi oleh regional setting. Umumnya kita perlu mengetikan tanggal dengan pola m/d/yyyy di formula bar. Artinya yang diketik pertama kali adalah bulan, kemudian hari (nomor urut tanggal) dan selanjutnya tahun. Masing-masing dipisahkan oleh garis miring. Makanya sangat penting untuk mengenali bagaimana excel merespon data tanggal yang kita ketik, karena umumnya orang indonesia menuliskan tanggal dengan pola dd/mm/yyyy

Sebagai contoh, jika kita mengetik 5/1/2018, mungkin excel akan mengenalinya sebagai tanggal 1 Mei 2018, bukan 5 Januari 2018, seperti contoh dalam gambar di bawah ini.

Kembali ke rumus excel untuk merubah tanggal menjadi hari dan bulan. Kunci dari rumus ini adalah kode format yang digunakan sebagai parameter fungsi TEXT

Untuk merubah tanggal menjadi nama hari kita bisa gunakan kode "ddd" (bentuk singkat) dan kode "dddd" (bentuk nama hari lengkap). Sedangkan untuk mendapatkan nama bulan, kode yang bisa digunakan adalah "mmm" (singkat) dan "mmmm" (lengkap). Dalam hal ini kita akan gunakan contoh versi format lengkap.

Sesuai tabel contoh, kita akan mulai mengetikan formula excel di sel B2 dan sel C2 yang merujuk pada data tanggal di sel A2.

Rumus di sel B2, merubah tanggal yang ada pada sel A2 menjadi hari

=TEXT(A2,"dddd")

Rumus di Sel C2, merubah tanggal yang ada pada sel A2 menjadi bulan

=TEXT(A2,"dddd")

Kemudian copy rumus di sel B2 dan sel C2 ke sel di bawahnya sampai baris akhir data. Hasilnya akan nampak seperti screenshot berikut:

Berdasarkan gambar di atas, dengan menggunakan kode format "dddd" dan "mmmm" maka diperoleh nama hari dan bulan dalam bahasa Inggris. Ini karena komputer yang saya gunakan menggunakan regional setting USA.

Supaya bisa diperoleh nama hari dan bulan dalam bahasa Indonesia, tanpa peduli regional setting komputernya, maka kita harus memaksa rumus excel supaya menampilkan tanggal dan hari dalam bahasa Indonesia. Caranya adalah dengan menambahkan kode "[$-421]" sebelum "dddd" atau sebelum "mmmm”

Sehingga rumus di atas dapat kita modifikasi lagi sebagai berikut

Rumus di sel B2, merubah tanggal yang ada pada sel A2 menjadi hari dalam bahasa Indonesia

=TEXT(A2,"[$-421]dddd")

Rumus di Sel C2, merubah tanggal yang ada pada sel A2 menjadi bulan dalam bahasa Indonesia

=TEXT(A2,"[$-421]mmmm")

Kemudian copy rumus di sel B2 dan sel C2 ke sel di bawahnya sampai baris akhir data, sehingga hasil akhirnya akan nampak seperti screenshot di bawah ini:

Sampai disini dulu pembahasan kali ini mengenai Contoh Rumus Untuk Merubah Tanggal Menjadi Hari dan Bulan menggunakan fungsi TEXT. Selain itu juga sudah digunakan bagaimana cara memaksa rumus excel supaya bisa menampilkan nama hari dan bulan dalam bahasa Indonesia. Semoga uraiannya mudah difahami dan bisa bermanfaat.

Contoh Rumus Excel Lainnya:

• Menghitung Jumlah Hari Tertentu:
• Menghitung Jumlah Hari Antara 2 Tanggal.
• Rumus IF Untuk Menghitung Jam Lembur.
• Rumus VLOOKUP Dinamis Dengan Fungsi INDIRECT

Menghitung Banyaknya Data Tertentu

Menghitung banyaknya data dengan kriteria atau nilai tertentu sering terjadi dalam kehidupan sehari-hari. Contoh praktisnya misalnya bagaimana menghitung jumlah item barang berwarna merah, hijau dan sebagainya. Contoh kasus lainnya bagaimana menghitung jumlah penduduk berusia tertentu ataupun dengan jenis kelamin tertentu. Menghitung banyaknya data tertentu di excel dapat dilakukan dengan sangat mudah dan cepat.  Diantara rumus yang dapat digunakan adalah rumus COUNTIF atau COUNTIFS. Selain itu, fungsi SUMPRODUCT juga dapat digunakan sebagai alternative COUNTIFS pada versi excel yang lebih lama.

Contoh Soal.

Misalnya tersedia sebuah tabel data anak-anak di sebuah kampung. Tabel terdiri atas 3 kolom. Kolom ke-1 berisi data nama, kolom ke-2 berisi data jenis kelamin, kolom ke-3 berisi data umur. Tugas kita selanjutnya adalah menjawab pertanyaan berikut:

1. Ada berapa jumlah anak berjenis kelamin tertentu?
2. Ada berapa jumlah anak berumur tertentu?
3. Ada berapa jumlah anak berjenis kelamin dan umur tertentu.

Gambaran visual contoh soal dapat dilihat dalam screenshot di bawah ini:

Pertanyan pertama dan kedua mempertimbangkan 1 kriteria perhitungan. Sedangkan pertanyaaan ke-3 mempertimbangkan 3 kriteria.

Menjawab Pertanyaan Pertama: Berapa banyak jumlan anak berdasarkan jenis kelamin?

Secara umum dapat dijawab menggunakan rumus fungsi COUNTIF

=COUNTIF(range,kriteria)

Range = range kolom berisi data jenis kelamin yang ingin dicek, contoh range B2:B14

Kriteria = kondisi yang ingin dihitung apakah laki-laki atau perempuan, data input cek di sel G3.

Selanjutnya rumus dapat dituliskan seperti berikut ini:

=COUNTIF(B2:B14,G3)

Menjawab Pertanyaan Kedua: Berapa banyak jumlah anak yang berumur tertentu.

Untuk menjawab pertanyaan ke-dua ini prinsipnya sama dengan rumus pertama yaitu menggunakan fungsi COUNTIF, hanya tinggal mengganti parameternya.

Range = range kolom berisi data umur yaitu range C2:C14

Kriteria = sel input umur yaitu sel G7. Sehingga rumusdapat diketik sebagai berikut:

=COUNTIF(C2:C14,G7)

Menjawab Pertanyaan Ketiga: Berapa jumlah anak berjenis kelami dan berumur tertentu?

Untuk kasus pertanyaan nomor 3 ini kita perlu mempertimbangkan 2 kriteria yaitu kriteria Jenis Kelamin dan kriteria Umur, sehingga harus diselesaikan dengan rumus perhitungan multi kriteria. Salah satu fungsi yang dapat digunakan untuk menjawab pertanyaan ini adalah fungsi COUNTIFS.

Secara umum, formula dapat dituliskan sebagai berikut:

=COUNTIFS(range1,kriteria1,range2,riteria2)

Range1 = range kolom berisi data jenis kelamin (range B2:B14)

Kriteria1= sel berisi input cek jenis kelamin (sel G11)

Range2 = range kolom berisi data umur (range C2:C14)

Kriteria2 = sel berisi input cek umur (sel G12)

Sehingga rumus praktisnya diketik sebagai berikut:

=COUNTIFS(B2:B14,G11, C2:C14,G12)

Penerapan rumus pertama, kedua dan ketiga dapat dilihat dalam sreenshot di bawah ini:

Catatan: Fungsi COUNTIFS selain bisa menghitung jumlah data dengan 2 kondisi atau lebih,  sebenarnya bisa juga untuk menghitung jumlah data dengan satu kriteria seperti halnya yang dapat dilakukan oleh fungsi COUNTIF (tanpa S).

Sebagai contoh :

Rumus =COUNTIF(B2:B14,G3) dapat diganti menjadi =COUNTIFS(B2:B14,G3)

Sayangnya fungsi COUNTIFS hanya tersedia pada versi excel 2007 atau yang lebih baru.

Jika ada pembaca yang masih menggunakan versi yang lebih lama, anda tidak akan menemukan fungsi COUNTIFS. Sebagai alternativenya bisa digunakan fungsi SUMPRODUCT. Dengan menggunakan fungsi ini, contoh rumus nomor 3 dapat dimodifikasi menjadi:

=SUMPRODUCT((B2:B14=G11)*(C2:C14=G12))

Sampai disini dulu pembahasan singkat mengenai contoh rumus untuk menghitung banyaknya data bernilai tertentu, baik dengan kriteria tunggal maupun kriteria ganda. Semoga bermanfaat.

Contoh Rumus Excel Lainnya:

• Rumus VLOOKUP Dinamis Dengan Fungsi INDIRECT
• Menggali Informasi dengan Rumus INDEX MATCH
• Menghitung Jumlah Hari Tertentu
• Menghitung Jam Kerja Karyawan

Rumus VLOOKUP Dinamis dengan Fungsi INDIRECT

Pencarian data dengan rumus VLOOKUP akan lebih powerfull jika dilengkapi tabel lookup  yang bersifat dinamis. Tabel lookup yang dinamis memungkinkan kita mengganti tabel rujukan tanpa perlu mengutak-atik rumus untuk merubah referensi. Cukup hanya dengan merubah data input maka informasi dari tabel yang sesuai dapat dengan cepat segera diperoleh. Untuk menjadikan tabel lookup bersifat dinamis, kita cukup menggabungkan fungsi VLOOKUP dengan fungsi INDIRECT.

Contoh Soal:

Anggaplah sudah tersedia dua buah tabel penjualan produk, masing-masing tabel terdiri atas 2 kolom. Tabel 1 berisi data penjualan di bulan Januari dan Tabel 2 berisi data penjualan di bulan Februari. Selanjutnya anda harus membuat rumus yang dapat mengambil data penjualan dari kedua tabel tersebut. Namun rumus tersebut nantinya harus fleksibel dan berlaku umum, dengan artian tidak perlu merubah referensi tabel lookup ketika rumus dicopy ke sel di bawahnya. Untuk lebih jelasnya, contoh soal excel ini dapat dilihat dalam gambar di bawah ini:

Sebelum melangkah ke contoh rumus VLOOKUP dengan tabel referensi yang bersifat dinamis, mari kita coba terlebih dahulu menggunakan rumus yang biasa. Misalnya di sel F3, kita ketikan rumus berikut untuk mencari nilai penjualan buah apel di bulan Januari.

=VLOOKUP(D3,$A$3:$B$5,2,0)

Jika rumus tersebut di copy ke sel di bawahnya (sel F4) maka rumus nya akan menjadi 

=VLOOKUP(D4,$A$3:$B$5,2,0)

Perhatikan parameter yang di-highlight kuning yaitu range  tabel lookup $A$3:$B$5. Supaya lebih mudah dibaca selanjutnya saya tulis A3:B5. Referensi ini ketika di copy ke sel dibawahnya masih sama dan mengacu pada tabel bulan Januari. Padahal kita menginginkan data di bulan Februari. 

Untuk mendapatkan hasil yang benar, tentu saja kita harus merubah referensi tersebut menjadi $A$9:$B$11 atau A9:B11 yang mengacu ke tabel di bulan Februari. Jika ada banyak tabel maka kita harus merubah secara manual setiap kali mencopy rumus. Cara ini mungkin tidak dikehendaki.

Solusi: Jadikan Tabel Lookup Dinamis Menggunakan INDIRECT.

Untuk menjadikan tabel dinamis, bisa saja di kolom E (kolom Bulan), langsung diketikan referensi A3:B5 untuk menggantikan Januari serta referensi A9:B11 untuk menggantikan Februari. Tetapi penggunaan kode referensi secara langsung akan menyulitkan memahami formula. Oleh karena itu sangat disarankan untuk membuat nama pada masing-masing tabel.

Misalnya: Tabel 1 kita nama “Januari”

Caranya seleksi range A3:B5 kemudian ketikan "Januari" (tanpa tanda kutip) pada kotak nama yang terletak di bagian sebelah kiri atas (disamping formula bar) 

Lakukan  proses yang sama pada tabel 2 (range A9:B11) dan beri nama "Februari"

Sampai tahap ini, kita sudah memberikan nama pada masing-masing tabel. Nama inilah yang pada tahap selanjutnya akan dijadikan referensi bagi fungsi INDIRECT.

Sehingga kita bisa mengetikan rumus berikut untuk mendapatkan data penjualan sesuai data produk dan bulan yang diinput pada kolom D dan kolom E

=VLOOKUP(D3,INDIRECT(E3),2,0)

Penjelasan Rumus:

Kita ketahui, secara umum rumus VLOOKUP dapat dituliskan sebagai berikut:

=VLOOKUP(kataKunci,TabelLookup,NomorKolomHasil,0)

Sehingga rumus =VLOOKUP(D3,INDIRECT(E3),2,0) dapat dijelaskan secara rinci melalui argumen rumus yang digunakan yaitu:

• kataKunci merupakan nilai yang diinput pada sel D3 (contoh “Apel”)
• tabelLookup diwakili oleh fungsi INDIRECT(E3). Rumus ini akan mereferensikan nama range yang diinput di sel E3. Jika diinput Januari, maka akan mereferensikan range yang bernama Januari
• nomorKolomHasil adalah nomor 2 yaitu nomor urut kolom data penjualan dalam tabel.

Dengan menggunakan bantuan fungsi INDIRECT ini maka jika kita merubah inputan nama bulan pada kolom E, maka hasil rumus akan mengikuti dan menyesuaikan dengan bulan yang sesuai. Rumus tersebut juga bisa dicopy ke sel dibawahnya dengan aman tanpa harus mengutak-atik referensi lagi untuk menghasilkan nilai yang benar.

Demikian pembahasan singkat mengenai penggunaan fungsi INDIRECT untuk menjadikan tabel dinamis dalam rumus pencarian data menggunakan VLOOKUP.

Contoh Rumus Excel Lainnya:

• Menggali Informasi Dengan Rumus INDEX MATCH
• Rumus VLOOKUP Untuk Membuat Kode Barang
• Rumus VLOOKUP Untuk Membaca Kode Barang
• Rumus VLOOKUP Ke Kiri

Menggali Informasi Dengan Rumus INDEX MATCH

Pada beberapa artikel sebelumnya sudah dibahas cara mencari atau menemukan data tertentu menggunakan rumus VLOOKUP. Dalam kesempatan ini kita akan membahas salah satu rumus alternative unggulan lainnya yang juga dapat digunakan untuk mencari data dan menggali informasi tertentu dari sebuah tabel data. Ialah rumus INDEX MATCH, yang oleh banyak pakar excel sangat direkomendasikan sebagai pengganti VLOOKUP karena beberapa poin keunggulannya. Yuk kita pelajari dengan seksama.

Contoh Soal.

Tersedia sebuah tabel data karyawan terdiri atas 5 kolom. 

• Kolom ke-1  : Nama
• Kolom ke-2  : NIK
• Kolom ke-3  : Jenis Kelamin
• Kelom ke-4  : Tgl Lahir
• Kolom ke-5  : Asal Kota

Selanjutnya kita diminta untuk menggali informasi karyawan dari tabel yang sudah disediakan. Adapun kata kunci untuk mencari informasi data tersebut adalah berupa NIK, dimana untuk NIK ini sudah disediakan kolom input secara manual. Sedangkan untuk informasi lainnya harus diformulasikan supaya bisa muncul secara otomatis, cepat dan akurat ketika NIK diinput.

Untuk lebih jelasnya contoh soal ini dapat dilihat dalam screenhot di bawah ini:

Contoh Rumus INDEX MATCH Untuk Menggali Informasi Karyawan.

Sebelum melanjutkan. Silahkan diperhatikan terlebih dahulu gambar tabel contoh di atas. Kata kunci yang kita gunakan adalah NIK, dan ternyata NIK terletak pada kolom ke-2 dalam tabel data karyawan. Pola tabel seperti ini tentu saja bukan kondisi yang ideal untuk rumus VLOOKUP, kecuali dengan bantuan fungsi CHOOSE seperti yang sudah dijelaskan dalam pembahasan Rumus  VLOOKUP ke kiri.

Sebagai solusi rumus pencarian data, maka disini kita akan gunakan rumus INDEX MATCH. Sebagaimana tersurat jelas dari namanya, formula ini merupakan gabungan dari fungsi INDEX dan MATCH.

Fungsi INDEX digunakan untuk mengambil data dari nomor urut baris dan nomor kolom tertentu dalam sebuah range. 

Fungsi ini dapat dituliskan sebagai berikut:

=INDEX(range, nomorBaris, nomorKolom)

Jika range hanya terdiri atas satu baris saja, maka parameter nomor baris bisa ditiadakan sehingga rumus umumnya bisa diubah menjadi:

=INDEX(rangeBaris,nomorKolom)

Jika range hanya terdiri atas satu kolom saja, maka argumen yang diperlukan cukup berupa range kolom dan nomor baris. Rumusnya dapat dituliskan sebagai berikut:

=INDEX(rangeKolom,nomorBaris)

Rumus terakhir di atas ini yang akan kita gunakan untuk menyelesaikan kasus sesuai contoh soal yang sudah diberikan. 

Misalnya kita ingin mendapatkan informasi data nama pada kolom Nama (range A2:A6), maka kita bisa menuliskan rumus berikut:

=INDEX(rangeKolom,nomorBaris) = Hasil

=INDEX(A2:A6,1) ⇒ Hasil = "Dani Ramdan"

=INDEX(A2:A6,2) ⇒ Hasil = "Badu Baidu"

=INDEX(A2:A6,3) ⇒ Hasil = "Ratna Anjani"

=INDEX(A2:A6,4) ⇒ Hasil = "Galih Prima"

=INDEX(A2:A6,5) ⇒ Hasil = "Dewi Yuli"

Contoh-contoh rumus diatas menunjukan hasil yang diperoleh bergerak dari atas ke bawah sesuai nomor urut baris 1,2,3,4,5 dalam range A2:A6.

Namun secara praktis, rumus-rumus tersebut kurang mengena. Kenapa demikian? Karena kita harus mengeikan nomor urut baris secara manual. Padahal dalam prakteknya justru kita ingin mencari nomor urut baris tersebut supaya diperoleh informasi yang tepat.

Disinilah peranan fungsi MATCH

Fungsi MATCH digunakan untuk mencari sebuah nilai atau kata kunci dalam sebuah range kolom dan menghasilkan nomor urut baris dalam kolom dimana kata kunci tersebut ditemukan.

Fungsi MATCH dapat dituliskan sebagai berikut:

=MATCH(kataKunci,rangeKolom,0) 

Contoh: kita bisa mencari nomor urut NIK 12562 ada kolom NIK (range B2:B6) dengan rumus berikut:

=MATCH(12562,B2:B6,0) ⇒ Hasil = 3 (baris ke-3)

Kemampuan fungsi MATCH ini yang bila dikombinasikan dengan fungsi INDEX makan akan menjadikannya rumus yang sangat powerfull dalam tugas pencarian data.

Secara umum, formula pencarian data menggunakan fungsi INDEX MATCH dapat dituliskan sebagai berikut:

=INDEX(rangeKolomHasil,MATCH(kataKunci,rangeKolomKunci,0))

Jika diterapkan pada contoh soal, maka parameter-parameter tersebut bisa dirinci sebagai berikut:

• rangeKolomHasil adalah kolom Nama (range A2:A6), kolom Jenis Kelamin (range C2:C6), kolom Tgl Lahr (range D2:D6) dan kolom Asal Kota (rangen E2:E6)
• kataKunci adalah nilai yang sudah diketahui dan akan dicari informasi lainnya yang terkait dengan kata kunci tersebut. Dalam hal ini berupa NIK yang sudah diketahui (cell C10)
• rangeKolomKunci adalah kolom dalam tabel dimana kata kunci berada (range B2:B6)

Selanjutnya dengan menggunakan referensi tersebut, kita bisa menuliskan rumus berikut:

Mencari Nama

=INDEX(A2:A6,MATCH(C10,B2:B6,0))

Mencari Jenis Kelamin

=INDEX(C2:C6,MATCH(C10,B2:B6,0))

Mencari Tanggal Lahir

=INDEX(C2:C6,MATCH(C10,B2:B6,0))

Mencari Asal Kota

=INDEX(E2:E6,MATCH(C10,B2:B6,0))

Penerapannya dalam spreadsheet excel dapat dilihat dalam screenshot di bawah ini:

Catatan:

Referensi rangeKolomHasil dan rangeKolomKunci dianjurkan untuk memiliki jumlah baris yang sama dan sejajar untuk meminimalisir kesalahan hasil yang diperoleh

Sampai disini pembahasan mengenai contoh rumus pencarian data secara cepat dan akurat menggunakan rumus INDEX Match. Semoga bermanfaat dan dapat diterapkan dalam pekerjaan sehari-hari.

Contoh Rumus Excel Lainnya

• Rumus VLOOKUP Untuk Apa Sich?
• Rumus VLOOKUP Untuk Membuat Kode Barang
• Rumus VLOOKUP Untuk Membaca Kode Barang
• Rumus VLOOKUP Ke Kiri