Fungsi VLOOKUP Versus INDEX dan MATCH

Mungkin sudah banyak Rekan-rekan yang mengetahui Fungsi VLOOKUP di dalam Excel yang berfungsi mengambil informasi spesifik dari sebuah Tabel Database, yang mungkin berskala besar.

Contoh Sederhana penggunaan Fungsi VLOOKUP()

Disini kita perhatikan, bahwa Fungsi VLOOKUP() mencari data sesuai ke arah kanan dari Kolom Kunci yang terletak paling kiri sebanyak n kolom. Contoh di atas data yang diambil dari Tabel Data adalah kolom ke-2 (Kolom Harga).

Pertanyaan yang muncul kemudian adalah, Bagaimana penerapan rumus excel jika kita ingin mengambil data dari Tabel Database tetapi data tersebut terletak di sebelah kiri dari data/referensi?

Disini saya akan menunjukkan kegunaan Perpaduan Fungsi Index() dan Match(), yang berguna mengambil data di sebelah kiri dari Kolom Kunci.



Tetapi Fungsi VLOOKUP sebenarnya juga bisa mencari data di sebelah kiri dari Kolom Kunci, tentu saja dengan modifikasi dengan dipadukan dengan fungsi CHOOSE.

Ya ini Fungsi Left Lookup dengan VLOOKUP.......

Download File Latihan

Menampilkan Hari dan Bulan Secara Otomatis

Saya perbaiki dengan penambahan materi pada postingan ini, supaya lebih berkualiatas bobotnya, saya akan memberikan beberapa trik menampilkan hari dan bulan secara otomatis, mudan2xan bermanfaat.

Rumus Mengambil Nama Hari:
1. =CHOOSE(WEEKDAY(A3);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu")
2. =TEXT(A3;"dddd")
3. =TEXT(A3;"[$-21]dddd")
atau berbahasa inggris:
1.=CHOOSE(WEEKDAY(A3);"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday")
2. =TEXT(A3;"[$-9]dddd")

Rumus Mengambil Nama Bulan:
1. =CHOOSE(MONTH(A3);"Januari";"Februari";"Maret";"April";"Mei";"Juni";"Juli";"Agustus";"September";"Oktober";"Desember")
2. =TEXT(A3;"mmmm")
3. =TEXT(A3;"[$-21]mmmm")
atau berbahasa inggris:
1.=CHOOSE(MONTH(A3);"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"December")
2.=TEXT(A3;"[$-9]mmmm")

Bagaimana dengan menggabungkan Hari dengan Tanggalnya?

Rumusnya:
1. =CHOOSE(WEEKDAY(A3);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu")&", "&TEXT(A3;"dd mmm yyy")
2. =TEXT(A3;"[$-9]dddd")&", "&TEXT(A3;"[$-9]dd mmmm yyy")

Rumusnya (yang diwarnai kuning yaaa):
1. Hari =DAY(A29)
2. Bulan =MONTH(A29)
3. Tahun =YEAR(A29)

Nah kali ini membuat list nama2x bulan:
Rumus ini diletakkan pada cell A32 (perhatikan gambar kemudian di-copy kebawah sampai hasilnya blank.
1.=IF(ROWS($A$1:A1)>12;"";CHOOSE(ROWS($A$1:A1);"Januari";"Februari";"Maret";"April";"Mei";"Juni";"Juli";"Agustus";"September";"Oktober";"Nopember";"Desember"))
atau bahasa inggris
=IF(ROWS($A$1:A1)>12;"";CHOOSE(ROWS($A$1:A1);"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"))

Nama-nama hari juga bisa dibuatkan list-nya secara otomatis tanpa harus diketik manual
1. =IF(ROWS(A1:$A$1)>7;"";CHOOSE(ROWS(A1:$A$1);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu"))
atau bahasa inggris
=IF(ROWS(A1:$A$1)>7;"";CHOOSE(ROWS(A1:$A$1);"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"))

Sekian dari saya
Wass Wr Wb
Rizky

Menghitung Umur dengan Fungsi DATEDIF

Bagaimanakah menampilkan umur seseorang dengan rumus excel, saya akan memeberikan contoh dengan beberapa kasus.

KASUS A:

Nah kasus A ini ingin menghitung umur dari 2 Tanggal (Hari ini dan Ulang Tahun), tetapi berupa angka2x, mohon diperhatikan di gambar, jadi bukan berupa format tanggal.
Rumus 1:
=IF(OR(A4="";B4="";C4="");"";IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"y")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"y")&" tahun ")&IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"ym")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"ym")&" bulan ")&IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"md")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"md")&" hari"))
Rumus 2:
=IF(OR(A4="";B4="";C4="");"";IF(INT((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25)=0;"";INT((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25)&" tahun ")&IF(INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25;1)*12)=0;"";INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25;1)*12)&" bulan ")&IF(INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/30,4375;1)*30,4375)=0;"";INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/30,4375;1)*30,4375)&" hari"))

KASUS B:

Kasus B ini berbeda sedikit, karena saya akan membandingkannya langsung dengan fungsi TODAY(), jadi yang di-input hanya tanggal ulang tahunnya saja.
Rumus 1:
=IF(OR(A17="";B17="";C17="");"";IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"y")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"y")&" tahun ")&IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"ym")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"ym")&" bulan ")&IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"md")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"md")&" hari"))
Rumus 2:
=IF(OR(A17="";B17="";C17="");"";IF(INT((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25)&" tahun ")&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25;1)*12)&" bulan ")&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/30,4375;1)*30,4375)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/30,4375;1)*30,4375)&" hari"))

KASUS C:

Nah yang ini ber-format tanggal, rumusnya jadi lebih mudah:
Rumus 1:
=IF(OR(A26="";B26="");"";IF(DATEDIF(B26;A26;"y")=0;"";DATEDIF(B26;A26;"y")&" tahun ")&IF(DATEDIF(B26;A26;"ym")=0;"";DATEDIF(B26;A26;"ym")&" bulan ")&IF(DATEDIF(B26;A26;"md")=0;"";DATEDIF(B26;A26;"md")&" hari"))
Rumus 2:
=IF(OR(A26="";B26="");"";IF(INT((A26-B26)/365,25)=0;"";INT((A26-B26)/365,25)&" tahun ")&IF(INT(MOD((A26-B26)/365,25;1)*12)=0;"";INT(MOD((A26-B26)/365,25;1)*12)&" bulan ")&IF(INT(MOD((A26-B26)/30,4375;1)*30,4375)=0;"";INT(MOD((A26-B26)/30,4375;1)*30,4375)&" hari"))

KASUS D:

Sama dengan yang diatas, tetapi yang sekarang dibandingkan langsung dengan fungsi TODAY()
Rumus 1:
=IF(A35="";"";IF(DATEDIF(A35;TODAY();"y")=0;"";DATEDIF(A35;TODAY();"y")&" tahun ")&IF(DATEDIF(A35;TODAY();"ym")=0;"";DATEDIF(A35;TODAY();"ym")&" bulan ")&IF(DATEDIF(A35;TODAY();"md")=0;"";DATEDIF(A35;TODAY();"md")&" hari"))
Rumus 2:
=IF(A35="";"";IF(INT((TODAY()-A35)/365,25)=0;"";INT((TODAY()-A35)/365,25)&" tahun ")&IF(INT(MOD((TODAY()-A35)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A35)/365,25;1)*12)&" bulan ")&IF(INT(MOD((TODAY()-A35)/30,4375;1)*30,4375)=0;"";INT(MOD((TODAY()-A35)/30,4375;1)*30,4375)&" hari"))

FILE LATIHAN
Salam

Penomoran Otomatis

Ini dia tips untuk membuat penomoran secara otomatis pada excel tanpa kita melakukan drag and drop dengan mouse. Hal yang paling memudahkan adalah penomoran ini otomatis akan menyesuaikan jika kita menghapus salah satu list yang ada.

Menghitung Rata2x Kelas, Frekuensi dan Konversi Nilai

Saya melakukan revisi pada tulisan ini dengan penambahan materi tentunya, mudah2xan bermanfaat.

Kasus 1 (Menghitung Nilai Di Atas Rata2x Kelas)

Rumus untuk jumlah murid yang mempunyai nilai di atas rata2x :
=COUNTIF($B$2:$B$15;">" &AVERAGE($B$2:$B$15))

Rumus untuk mengambil nama2 murid yang mempunyai nilai di atas rata2x kelas (array formula):
=IFERROR(INDEX($A$2:$A$15;SMALL(IF($B$2:$B$15>AVERAGE($B$2:$B$15);ROW($A$2:$A$15)-ROW($A$2)+1);ROWS($A$1:A1)));"")

Kasus 2 (Mengambil Nilai Rata2x Dengan Menggunakan Tabel Konversi Nilai)

Rumus:
=SUMPRODUCT(SUMIF($A$2:$A$4;$C$7:$C$10;$B$2:$B$4))/COUNTA($C$7:$C$10)

Kasus 3 (Mencari Jumlah Murid Dengan Kriteria Nilai Rentang Nilai Tertentu)

Saya berikan 3 contoh rumus yang digunakan :
1. =SUM(B2:B11>30;IF(B2:B11<75;1;0);0) --> rumus array
2. =SUMPRODUCT(--(B2:B11>30);--(B2:B11<75))
3. =COUNTIFS(B2:B11;">"&30;B2:B11;"<"&70)

Paling simpel menggunakan COUNTIFS bukan?

Kasus 4 (Mencari Nilai Dengan Menggunakan Tabel Konversi)

Rumus untuk IPK :
=SUMPRODUCT(SUMIF($A$2:$A$10;$E$2:$E$4;$B$2:$B$10))/COUNTA($E$2:$E$4)

Rumus untuk Nilai KOnversi :
=IFERROR(INDEX(A2:A10;MATCH(D7;B2:B10;0));INDEX(A2:A10;MATCH(MIN(IF(IF(B2:B10<=D7;B2:B10)=MAX(IF(B2:B10<=D7;B2:B10));B2:B10));B2:B10;0)))

Sekian Wass.Wr.Wb
Rizky

File Excel Latihan

Menghitung Kelompok Umur

Saya ingin memberikan contoh penerapan rumus excel untuk mengetahui berapa jumlah kelompok umur dari sebuah tabel database umur.

Berikut Tabel Database Umur :

Kriteria yang dicari adalah sebagai berikut:
a. Kurang dari 30 Tahun
b. 30 - 40 Tahun
c. 41 - 50 Tahun
d. Lebih dari 50 Tahun


Saya akan memberikan contoh pemecahan masalah perhitungan ini dengan 5 Solusi dibawah ini :

Solusi 1:

Pada contoh diatas, saya menambahkan Kolom Logic untuk memberikan pemahaman logikanya, seperti berikut :
a. < 30 Tahun --> Jelas
b. 30 - 40 Tahun --> Yang jadi patokan angka 30 saja, jadi >=30
c. 41 - 50 Tahun --> Yang adi patokan angka 41 saja, jadi >=40
d. > 50 Tahun --> Jelas

Perhatikan jika Kolom Logic saya hilangkan :

Solusi 2:

Mudah-mudahan jelas ya..... 

Solusi 3:

Di solusi 3 ini disisipkan Array Formula, coba diperhatikan :

Rumus Array digunakan untuk mencari Range Umur :

30 - 40 Tahun dan 41 - 50 Tahun

Silahkan anda gunakan rumus ini untuk pemecahan masalah yang serupa dengan range umur sesuai kebutuhan.

Solusi 4: COUNTIF-1

Solusi 5: COUNTIF-2

Banyak jalan menuju Roma kan.... Guys... ;)


Solusi 6: dengan COUNTIFS

 Fungsi COUNTIFS() ini tersedia di Excel 2007 dan 2010.

Solusi 7: dengan SUMPRODUCT

Solusi 8: Menggunakan SUM dan IF

Solusi 9:

Solusi 10:



Download File Latihan