Memahami Penggunaan INDEX dan MATCH

Ada beberapa pembaca blog saya yang kurang memahami kombinasi INDEX dan MATCH, kesempatan ini saya akan memberikan penjelasan yang mendetail dan step by step.

1. Saya akan mulai data sederhana seperti ini

Cobalah praktek dengan saya, buatlah tabel diatas jika sudah jadi ketikkanlah ini di cell kosong
=MATCH("Udin";A2:A5;0)
"Udin" ini bisa anda ganti dengan referensi cell ya, nah fungsi MATCH disini adalah menghasilkan nilai baris dimana nama "Udin" berada dan jawabannya adalah 2. Cobalah mengganti "Udin dengan nama yang lain supaya tahu perbedaannya. Jika sudah puas dan paham tentang MATCH kita beralih dengan INDEX

syntax dari INDEX adalah INDEX(array;row_num;[column_num]), tetapi kita fokus yang ini saja INDEX(array;row_num), contoh penggunaannya
=INDEX(A2:A5;3)
rumus diatas akan menghasilkan nama "Didit" , angka 3 diganti angka 2 akan menghasikan nama "Udin", dst
Tetapi hal itu terasa manual kurang praktis dan sangat pas jika INDEX digabung dengan MATCH, seperti ini
=INDEX(B2:B5;MATCH("Udin";A2:A5;0))
Secara bahasa simpel INDEX akan mengambil data sesuai dengan nomor baris yang dihasilkan oleh MATCH, jika MATCH hasilnya 2 maka INDEX akan muncul data baris ke 2 juga.

Mohon maaf jikadi dalam memberikan penjelasan dan keterangan kurang mengena atau kurang jelas.

Horizontal Counting

Secara umum orang pasti pernah menggunakan fungsi COUNTIF atau COUNTIFS untuk menghitung data secara vertikal, tetapi data tidak selalu berbentuk vertikal ada juga yang berbentuk horizontal, nah untuk yang horizontal fungsi COUNTIF dan COUNTIFS tidak bisa digunakan dengan kondisi menggunakan kriteria oada kolom vertikal, perlu modifikasi dengan fungsi2x yang lain, langsung dengan tabel kasus di bawah ini:

KASUS 1

Hasil yang diinginkan adalah menghitung kursus bahasa yang diambil dengan kriteria "Nama"

Rumus 1 (letakan pada cell B10 - (rumus non array)):
=COUNTIF(INDEX($B$2:$D$7;MATCH($A10;$A$2:$A$7;0);0);"<>"&"")

Rumus 2 (rumus non array)
=SUMPRODUCT(($A$2:$A$7=$A10)*($B$2:$D$7<>""))

Rumus 3 (rumus non array)
=COUNTIFS($A$2:$A$7;$A10;$B$2:$B$7;"<>"&"")+COUNTIFS($A$2:$A$7;$A10;$C$2:$C$7;"<>"&"")+COUNTIFS($A$2:$A$7;$A10;$D$2:$D$7;"<>"&"")

Rumus 4 (rumus non array)
=COUNTA(INDEX(B$2:D$7;MATCH(A10;A$2:A$7;0);0))

Rumus 5 (rumus array)
=SUM(IF($A$2:$A$7=A10;IF($B$2:$D$7<>"";1;0)))

KASUS 2

Hasil yang diharapkan

Jadi hasil diatas adalah menghitung kolom B, C, dan D yang berisi teks atau tidak blank
Rumus 1:
=COUNTA(B2:D2)

Rumsu 2
=COUNTIF(B2:D2;"="&"?*")

Rumus 3
=SUMPRODUCT(--(B2:D2<>""))

FILE LATIHAN

Menyembunyikan Angka Nol Dengan Custom Format dan Conditional Formatting

Untuk memperluas wawasan, akan saya selingi trik dengan custom format dan conditional formatting. Apa ya bedanya antara keduanya? Dan sesuai topik pagi, bagaimana menghilangkan atau menyembunyikan angka nol yang ada di data kita. Tampilan permasalahan

Tidak semua orang suka ya dengan cell yang berisi angka nol, bos juga tidak terlalu suka laporannya muncul angka nol, nah bagaimana triknya? Yang pertama dengan menggunakan Custom Format, ikuti langkahnya ya
1. Letakkan kursor mouse anda pada cell yang akan di format
2. Klik kana mouse anda dan pilih "Format Cells......."

3. Pilih "Custom"

4. Letakkan kursor mouse anda pada isian "Type:" dan isikan 0;-0;;@

5. Last step, mengcopy custom format dengan menggunakan Format Painter terletak di menu "Home"

kemudian and copas ke semua bidang tabel data dan nan ti akan anda perhatikan semua angka nol akan jadi blanks atau hilang ya. Bagaimana dengan conditional formatting atau format bersyarat, contoh di bawah ini menggunakan video.
Link Video-1

Contoh video yang lain
Link Video-2