Kumpulan Kasus Excel Jilid 1

Met siang ya, berlanjut saya akan memposting bebeberapa kasus ya kali ini ada 4 kasus yang akan share ke anda.

KASUS 1.

dan hasil yang diharapkan seperti ini

Rumus seperti ini:
1. =IFERROR(INDEX($A$2:$D$6;SUMPRODUCT(($A$2:$D$6=A9)*(ROW($A$2:$D$6)-1));SUMPRODUCT(($A$2:$D$6=A9)*(COLUMN($A$2:$D$6)))+1);"")
2. =IFERROR(INDEX(A2:D6;AGGREGATE(15;6;ROW($1:$5)/(A2:D6=A9);1);AGGREGATE(15;6;COLUMN($A:$D)/(A2:D6=A9);1)+1);"")


KASUS 2

Hasil yang diinginkan

rumusnya seperti ini, diletakkan di cell D2 dan copas ke bawah
=IF(ROWS($1:1)<=($C$2<>"Irna")+2;INDEX(A$2:A$12;MATCH($C$2;$A$2:$A$12;0)+ROWS($1:1));"")


KASUS 3

Hasilnya adalah mengambil data yang tidak kosong pada masing2x kolom

rumus array:
=IF(COUNTA(A2:C2)=0;"";INDEX(A2:C2;1;MAX((A2:C2<>"")*COLUMN(A2:C2))-COLUMN(A2:C2)+1))
KASUS 4

Hasil yang diinginkan adalah memasukkan baris kosong diantara data dengan kriteria atau istilahnya "insert rows" ya

Jadi baris kosong yang ingin dimasukkan diantara data bisa sesuai kriteria tetapi dikurangi 1, misalnya anda menulis angka 3 maka baris yang kosong adalah 2, atau jika anda ingin 'insert rows" nya 6 baris maka anda harus input 7, dst.
rumusnya, untuk kolom E
=IFERROR(IF(MOD(ROWS($A1:A$1)-$D$2;$D$2)=0;"";INDIRECT("Kasus4!A"&2+((ROWS($A1:A$1)-1)/$D$2)));"")
untuk kolom F
=IFERROR(IF(MOD(ROWS($A1:B$1)-$D$2;$D$2)=0;"";INDIRECT("Kasus4!B"&2+((ROWS($A1:B$1)-1)/$D$2)));"")

FILE LATIHAN

Dependent Data Validation (Advanced)-2

Met pagi semua para followers tips rumus excel, senang bisa posting lagi, kali ini tentang data validation lagi, agak berbeda dengan postingan terdahulu dan perbedaannya pada tabel data yang digunakan untuk data validation.

dan hasil yang ingin ditampilan adalah

Jadia ada 2 data validation, yang pertama adalah dv utama dan yang ke-2 adalah dv member, dan tergantung pada dv utama.
Buatlah dulu dv utama, buatlah dengan contoh dibawah ini langsung ketik saja pada "source" tanpa perlu tabel lagi dengan tanda pemisah "tanda titik koma", ini mungkin relatif ya mungkin jika sistem komputer anda berbeda mungkin memakai "tanda koma". Letakkanlah dv utama pada cell D2.

Kemudian untuk membuat DV diperlukan rumus, letakkanlah di cell kosong seperti contoh dibawah
=OFFSET(B1;MATCH(D2;A2:A10;0);0;COUNTIF(A2:A10;D2);1)
ini rumus array jadi harus tekan tombol CSE, lalu buatlah data validationnya seperti biasa, pada menu data carilah tab "Data Validation" kemudian masukkanlah rumus diatas pada isian "source"

Kemudian klik "OK" dan lihatlah dan test perubahan apa yang terjadi, Untuk Lebih jelasnya keseluruhan dari pembuatan data validation ini silahkan download dan lihat LINK VIDEO

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