Kumpulan Kasus Excel Jilid 7

Jumpa lagi di gelombang yang sama, maaf ya teman2x semua lama tak posting maklum makin kesini makin tak ada ide aku ini haha.. tapi yang penting sekarang posting, horeeee, langsung yak...

KASUS 1

Dari tabel diatas kita ingin rubah ke

Rumusnya (Array Formula):
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$2:$C$2;MATCH(0;IF(ISBLANK($A$2:$C$2);"";COUNTIF($F$1:F1;$A$2:$C$2));0));INDEX($A$3:$C$3;MATCH(0;IF(ISBLANK($A$3:$C$3);"";COUNTIF($F$1:F1;$A$3:$C$3));0)));INDEX($A$4:$C$4;MATCH(0;IF(ISBLANK($A$4:$C$4);"";COUNTIF($F$1:F1;$A$4:$C$4));0)));INDEX($A$5:$C$5;MATCH(0;IF(ISBLANK($A$5:$C$5);"";COUNTIF($F$1:F1;$A$5:$C$5));0)));INDEX($A$6:$C$6;MATCH(0;IF(ISBLANK($A$6:$C$6);"";COUNTIF($F$1:F1;$A$6:$C$6));0)))

KASUS 2

Kasus diatas misal kita punya nomor yang di merged (digabung) di beberapa cell, kita inginnya di unmerged atau di rubah menjadi

Rumusnya:
=IFERROR(INDEX(A:A;SMALL(INDEX((A$1:INDEX(A:A;MATCH(99^99;A:A))="")*99^99+ROW(A$1:INDEX(A:A;MATCH(99^99;A:A)));0);ROWS(C$2:C2)));"")

KASUS 3

Hasil yang diinginkan

Ini kasus lookup ya, rumusnya:
=IFERROR(INDEX($B$1:$B$19;MATCH($D2;$A$1:$A$19;0)+MATCH($E$1;INDIRECT("A"&MATCH($D2;$A$1:$A$19;0)&":A19");0)-1);"")

KASUS 4

Dari tabel diatas kita ingin hasilnya seperti ini

Rumus Array:
=IFERROR(INDEX(A$2:A$12;SMALL(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12))=LARGE(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));"");1);ROW($B$2:$B$12);"");1)-ROW($A$2)+1);"")

KASUS 5

Dari dua tabel diatas kita ingin merubah lay out tabelnya menjadi

Rumus kolom F
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$4:$C$6<>"";ROW($A$4:$C$6)*10^4+COLUMN($A$4:$C$6));ROWS($A$4:A4));"R0000C0000");0);"")

Rumus kolom G
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$10:$C$12<>"";ROW($A$10:$C$12)*10^4+COLUMN($A$10:$C$12));ROWS($A$10:B10));"R0000C0000");0);"")

Rumus kolom I (Array Formula)
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF(($A$4:$C$12<>"")*(LEFT($A$4:$C$12;5)<>"Table");ROW($A$4:$C$12)*10^4+COLUMN($A$4:$C$12));ROWS($A$4:A4));"R0000C0000");0);"")

KASUS 6

Hasil yang diharapkan

Rumus Non Array:
=SUMPRODUCT((LOOKUP(ROW($A$3:$A$14);ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

Rumus Array:
=SUM((VLOOKUP(N(IF({1};ROW($A$3:$A$14)));IF({1\0};ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14);2;1)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

KASUS 7

Kasus kali ini agak menarik ya yaitu mengurutkan angka dalam satu cell, rumusnya adalah rumus array atau rumus CSE:
=REPT(0;LEN(A2)-LEN(SUBSTITUTE(A2;0;"")))&SUM(10^(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))-1)*LARGE(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)+0;ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))))

KASUS 8

Kasus ke 8 ini adalah menyisipkan spasi di anatara angka, rumusnya:
=TEXT(A1;REPT("0 ";LEN(A1)))

KASUS 9

Kasus terakhir ini adalah lookup dengan mengganti teks berdasar kolom kata kunci (keyword)
Rumusnya
=IFERROR(INDEX($D$3:$D$5;LOOKUP(10^308;MATCH(TRIM(MID(SUBSTITUTE(A3;" ";REPT(" ";255));{1;2;3;4;5}*255-254;255));$D$3:$D$5;0));1);"")

KASUS 10

Kasus terkahir ini adalah cara menghilangkan angka dan menghilangkan teks
1. Rumus menghilangkan huruf dari sebuah kata (Rumus Array)
=NPV(-0,9;;IFERROR(MID(A2;1+LEN(A2)-ROW(OFFSET(A$1;;;LEN(A2)));1)%;""))
2. Rumus menghilangkan angka dari sebuah kata (Rumus Biasa)
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;""))

Unduh File Excel