Kumpulan Kasus Excel Jilid 8

Met siang ini saya mo share lagi kasus2x lookup ya mudah2xan dapat berguna buat bro dan sis semua

Kasus 1

Dari tabel diatas hasil yang kita inginkan seperti ini:

Rumus yang digunakan:
=INDEX(A:C;INT((ROWS($1:1)-1)/3)+3;MOD(ROWS($1:1)-1;3)+1)

Kasus 2

Hasil yang diharapkan

Rumus (yang warna kuning):
=IFERROR(VLOOKUP(B$23;IF(IFERROR(LOOKUP(ROW($4:$17);ROW($4:$17)/($A$4:$A$17<>"");$A$4:$A$17);"Jumlah Area 0")="Jumlah Area "&RIGHT($A24)-1;$B$4:$C$17);2;0);0)

Kasus 3

Untuk hasil yang diwarna kuning diatas, dan rumusnya:
=INDEX(D11:G17;MATCH(B2;C11:C17;0);MATCH(1;MMULT({1\1\1\1\1};--(B1=D6:G10));0))

Kasus 4

Kriteria dan hasil yang diharapkan dari tabel diatas

Untuk mata pelajaran yang berurutan dari skor tertinggi ke terendah:
=IF(C11="";"";INDEX($C$1:$G$1;AGGREGATE(15;6;COLUMN($C$1:$G$1)-COLUMN($B$1)/(INDEX($C$2:$G$6;MATCH($B$10;$B$2:$B$6;0);0)=C11);COUNTIF(C$11:C11;C11))))

Untuk Nilai pelajaran tertinggi yang diambil yang berurutan dari skor tertinggi ke terendah:
=IFERROR(LARGE(INDEX($C$2:$G$6;MATCH($B$10;$B$2:$B$6;0);0);ROWS($1:1));"")

Kasus 5

Kriteria dan hasil yang diharapkan dari tabel diatas:

Untuk hewan yang dipilih dengan tanda "X" dengan skor berurutan dari yang tertinggi:
=IFERROR(INDEX(LOOKUP(COLUMN(B$4:Q$4);COLUMN(B$4:Q$4)/(B$3:Q$3<>"");B$3:Q$3);AGGREGATE(15;6;COLUMN(B$4:Q$4)-COLUMN(A$4)/((B$4:Q$4/(INDEX(B$5:Q$10;MATCH(A$14;A$5:A$10;0);0)="x"))=C14);COUNTIF(C$14:C14;C14)));"")

Untuk Nilai/skor hewan yang dipilih dengan tanda "X" dengan skor berurutan dari yang tertinggi:
=IFERROR(AGGREGATE(14;6;B$4:Q$4/(INDEX(B$5:Q$10;MATCH(A$14;A$5:A$10;0);0)="x");ROWS($1:1));"")

Kasus 6

Hasil yang diharapkan adalah seperti ini:

Rumusnya:
=IFERROR(INDEX($J$3:$J$5;SMALL(INDEX((ISERROR(SEARCH($J$3:$J$5;$A2)))*10^10+(ROW($J$3:$J$5)-ROW($J$2)););COLUMNS($B1:B1)));"")

 Kasus 6

Hasil Yang diinginkan 

Rumusnya ada 3 versi:
1. =MAX(VLOOKUP(A9;$A$2:$C$5;2;0);VLOOKUP(A9;$A$2:$C$5;3;0))
2. =IFERROR(1/(1/VLOOKUP(A9;A2:B5;2;0));1/(1/VLOOKUP(A9;A2:C5;3;0)))
3. =IF(VLOOKUP(A9;A2:B5;2;FALSE)=0;VLOOKUP(A9;A2:C5;3;FALSE);VLOOKUP(A9;     A2:B5;2;FALSE))

FILE LATIHAN

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