Kumpulan Kasus Excel Jilid 6

Kumpulan Kasus back to fight guys, malam ini melanjutkan seri yang ke 6 dan mudah2xan dapat digunakan untuk keperluan para readers, langsung saja ke urutan pertama

KASUS 1

Kasus pertama ini adalah menggabungkan kolom, 2 kolom atau lebih ya

Rumus:
a. =IFERROR(IF(OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10)))=0;"";OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10))));"")

b. =IFERROR(INDEX($A$2:$B$10;MOD(ROWS($A$2:A2)-1;ROWS($A$2:$B$10))+1;INT((ROWS($A$2:A2)-1)/ROWS($A$2:$B$10))+1);"")

KASUS 2

Kasus ke 2 ini adalah kasus penjumlahan dengan kolom kriteria yang di-merged, hasilnya

Rumus:
a. =SUM(MMULT(N(LOOKUP(ROW($A$1:$A$49);IF($A$1:$A$49<>"";ROW($A$1:$A$49)))=TRANSPOSE(IF($A$1:$A$49=D4;ROW($A$1:$A$49))));ROW($A$1:$A$49)^0)*$B$1:$B$49)

b. =SUM(IFERROR(IF(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0);V);0)) --> array formula

c. =SUM(ISNUMBER(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0))*V) --> array formula

d. =SUM(IF(LOOKUP(ROW(D);ROW(D)/(D<>"");D)=D4;V))

KASUS 3

Kasus ini merupakan kasus lookup dengan beberapa kolom, hasilnya

Rumus:
=IFERROR(SUMIF(INDEX($B$2:$F$8;MATCH(B10;$A$2:$A$8;0);0);A10;INDEX($C$2:$G$8;MATCH(B10;$A$2:$A$8;0);0));"")

KASUS 4

Kasus ke 4 ini adalah inspirasi dari mas Hendra jadi saya share untuk artikel kali ini tentang Golongan dan Kepangkatan PNS, untuk hasilnya adalah yang diwarna kuning ya, dan ini merupakan tabel panduannya

Rumusnya
=IFERROR(IF(RIGHT($D3;1)="E";$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT(D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)<RIGHT($D3;1));$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)=RIGHT($D3;1));$D3;IF(RIGHT($D3;1)="D";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4})+1;{1;2;3;4};{"I";"II";"III";"IV"})&"/"&"A";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});{1;2;3;4};{"I";"II";"III";"IV"})&"/"&CHAR(CODE(RIGHT($D3;1))+1)))));"")

KASUS 5

Hasil yang diharapkan

Rumus:
a. =INDEX(AGGREGATE({14;15};6;$B$2:$E$5/(($G2=$A$2:$A$5)*ISNUMBER($B$2:$E$5));1);COLUMNS(A:$A))

b. =IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$5;MATCH($G2;$A$2:$A$5;0);0)=$H2;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($G$2:G2;G2;$H$2:H2;H2)));"")

KASUS 6

Kasus terakhir ini merupakan kasus split teks dengan kriteria, pada tabel diatas saya berikan contoh menggunakan angka supaya hasilnya lebih jelas walaupun jika diganti teks bisa saja, hasil

Rumus di cell A8:
=LEFT($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6))-1)

Rumus di cell A9 dan copas ke bawah
=IFERROR(MID($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6*ROWS($1:1)))+1;MMULT(FIND("@";SUBSTITUTE($A$1;" ";"@";TRANSPOSE(MMULT(ROW($A1:$A2);1))*$A$6));{-1;1})-1);"")


FILE LATIHAN EXCEL

Kumpulan Kasus Excel Jilid 5

Hello guys and girls, long time no post ha..ha.. , mohon maaf saya akhir2x ini suibuk bgt, belum bisa sempat buat bahan kasus untuk artikel di blog ini, lanjut ya ke TKP..................

KASUS 1

Hasilnya berupa konversi ke table bentuk yang lain dengan menyisipkan baris kosong

Rumus untuk "Hasil 1":
1. Produk : =IF(ISERROR(INDEX($A$3:$A$5;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)));"";INDEX($A$3:$A$5;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)))

2. "Jual", "Beli", "Profit" : =IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";INDEX($B$2:$D$2;CEILING(ROWS(A$1:A1)/(SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1);1)))

3. Value : =IF(ISERROR(INDEX($B$3:$D$5;MATCH($A8;$A$3:$A$5;0);MATCH($B8;$B$2:$D$2;0)));"";INDEX($B$3:$D$5;MATCH($A8;$A$3:$A$5;0);MATCH($B8;$B$2:$D$2;0)))

Rumus untuk "Hasil 2" :
1. Produk : =IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";INDEX($A$3:$A$5;INT((ROWS($1:1)-1)/4)+1))

2. "Jual", "Beli", "Profit" : =IF(ISERROR(INDEX($B$2:$D$2;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)));"";INDEX($B$2:$D$2;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)))

3. Value : =IFERROR(INDEX($B$3:$D$5;MATCH($E8;$A$3:$A$5;0);MATCH($F8;$B$2:$D$2;0));"")

KASUS 2

Kemudian di-konversi menjadi :

Rumus (letakkan di cell C2 kemudian copied down and cross) :
=IF(($A1=0)*($A2<>"");IFERROR(INDEX($A2:INDEX($A2:$A$100;MATCH(1;INDEX(0+($A2:$A$100="");0);0));COLUMNS($A:A))&"";"");"")

 KASUS 3

Kasus ke 3 ini mirip dengan yang diatas yaitu meng-konversi tabel ke bentuk tabel yang lain (beda lay out)

Hasil

Rumus untuk kolom KOTA :
1. Pada cell A7, rumusnya =A2
2. Pada cell A8 dan copy down, =IFERROR(IF(COUNTIF(A$7:A7;A7)<COLUMNS($B$2:$D$5);A7;INDEX($A$2:$A$5;MATCH(A7;$A$2:$A$5;0)+1)); "")

Rumus untuk kolom TANGGAL :
=IF(A7 = ""; ""; INDEX($B$1:$D$1;MOD((ROW(A1)-1);COLUMNS($B$1:$D$1)) + 1))

Rumus untuk kolom ANGKA :
=IF(A7=""; "";INDEX($B$2:$D$5;MATCH(A7;$A$2:$A$5;0);COUNTIF(A$7:A7; A7)))

KASUS 4

Kasus ke 4 ini berbeda ya, disini saya beri contoh  bagaimana caranya membuat nomor dan data dengan sisipan baris kosong pada nomor baris2x tertentu.

Rumus:
1. Untuk Nomor : =IF(OR(ROWS(A1:$A$1)={5\11\32\38\45\77\100\110\132\144\166\172\199\207\217\240\262\272\295\312\318\343\350\356\370\392\403\421\429\444\461\478\480});"";MAX(A$1:A1)+1)
2. Untuk Area : =IF(OR(ROWS(A1:$A$1)={5\11\32\38\45\77\100\110\132\144\166\172\199\207\217\240\262\272\295\312\318\343\350\356\370\392\403\421\429\444\461\478\480});"";"Area"&COUNTIF(B$1:B1;""))


KASUS 5

Ini kasus merubah lay out lagi ya, menjadi........

Rumus:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15;6;ROW($2:$10)/1%+{1\2}/(COUNTIF(D$2:D2;A$2:B$10)=0);1);"R0C00");0);"")

KASUS 6

Kasus 6 ini membuat rumus untuk menggabungkan beberapa cell sekaligus, rumusnya :
=SUBSTITUTE(TRIM((CONCATENATE(A1;" ";B1;" ";C1;" ";D1;" ";E1;" ";F1)));" ";", ")

KASUS 7

Kasus ke 7 ini adalah mencocokkan antara kata pada kolom A dengan kolom B (secara parsial), bila ada ada kesamaan maka muncul "Yes", jika tidak "No", rumusnya (rusmus array) :
=IF(OR(A2="";B2="");"";IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2;" ";REPT(" ";99));1+99*(ROW(OFFSET($A$1;;;1+LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))-1);99));B2)));"Yes";IF(OR(A2="";B2="");"";IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(B2;" ";REPT(" ";99));1+99*(ROW(OFFSET($B$1;;;1+LEN(B2)-LEN(SUBSTITUTE(B2;" ";""))))-1);99));A2)));"Yes";"No"))))

KASUS 8

Kasus ke 8 ini bagaimana caranya memisahkan data yang menjadi satu pada contoh di atas, hasil yang diinginkan

Rumus (letakkan di C2 kemudian copas ke bawah):
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&$A$3;" ";REPT(" ";255));255*ROWS($A$1:A1);255));",";"")

KASUS 9
Pada kasus 9 ini saya akan memberikan contoh rumus penomoran pada 2 kolom dan 3 kolom, sperti ini misalnya

Rumus pada kolom A :
=COUNTA($B$2:B2)

Rumus pada kolom C :
=COUNTA(D$2:D2)+MAX($A$2:$A$6)

Rumus pada kolom A :
=COUNTA($B$10:B10)

Rumus pada kolom C :
=COUNTA(D$10:D10)+MAX($A$10:$A$14)

Rumus pada kolom E :
=COUNTA(F$10:F10)+MAX($C$10:$C$14)

KASUS 10

Hasil yang ingin ditampilkan :

Rumus pada kolom G :
=LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$3:C$3;;;ROW($1:$4););"<>")+1;D$4:D$6)&""

Rumus pada kolom H :
=IF(G3="";"";LOOKUP(COUNTIF(G$3:G3;G3)-1;IFERROR(SUMIF(OFFSET(INDEX(A$4:A$6;MATCH(G3;D$4:D$6;0));;;;ROW($1:$3)-1);"<>");0);T(OFFSET(A$3;0;{0;1;2;0;1;2;0;1;2}))&""))

Rumus pada kolom I :
=LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$3:C$3;;;ROW($1:$4););"<>")+1;E$4:E$6)&""

KASUS 11

Dari tabel diatas dirubah menjadi :

Rumus (rumus array) :
=TRIM(IFERROR(INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+2;MOD(ROW(1:1)-1;3)+1)&"-"&INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+3;MOD(ROW(1:1)-1;3)+1)&"-"&INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+4;MOD(ROW(1:1)-1;3)+1);""))

LINK FILE KASUS

Kumpulan Kasus Excel Jilid 4

Good afternoon everybody senang saya bisa posting artikel lagi kali ini kumpulan kasus excel mudah2xan bisa diambil sebagai contoh penyelesaian kasus yang mungkin anda kerjakan. Langsung saja

KASUS 1

Dari data diatas yang ingin kita lakukan adalah mengambil nilai yang paling besar dari kolom dari A sampai G sesuai dengan kriteria judul kolom, hasil yang diinginkan:

Rumusnya:
1. =MAX(OFFSET($A$1;1;MATCH($A$14;$A$1:$G$1;0)-1;COUNTA($A:$A)-1;1))
2. =MAX(OFFSET(INDEX(1:1;MATCH(A14;1:1;0));0;0;ROWS(A:A)))
3. =MAX(INDEX(A:Z;0;MATCH(A14;1:1;0)))

KASUS 2

Hasil yang diinginkan:

Rumusnya:
1. Untuk Nama : =IF(IFERROR(LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$2:C$2;;;ROW($1:$4););"<>")+1;D$3:D$5);"")=0;"";IFERROR(LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$2:C$2;;;ROW($1:$4););"<>")+1;D$3:D$5);""))
2. Untuk Mata Pelajaran : =IF(G3="";"";LOOKUP(COUNTIF(G$3:G3;G3)-1;IFERROR(SUMIF(OFFSET(INDEX(A$3:A$5;MATCH(G3;D$3:D$5;0));;;;ROW($1:$3)-1);"<>");0);T(OFFSET(A$2;0;{0;1;2;0;1;2;0;1;2}))&""))

KASUS 3

Kasus ini adalah tranposing merubah bentuk tabel dari yang multi kolom dan muli baris ke satu kolom saja seperti ini
=IFERROR(INDIRECT(TEXT(AGGREGATE(15;6;(ROW($2:$4)/1%+{1\2\3\4\5})/(A$2:E$4>0);ROW(A1));"R0C00");0);"")

Hasilnya jadi seperti ini

KASUS 4

Hasil yang diinginkan adalah menghilangkan duplikasi nama dan menghilangkan baris kosong (blank) dari tabel diatas, seperti ini hasilnya

Rumusnya (rumus array)
=IFERROR(INDEX(INDEX(TRIM(MID(SUBSTITUTE($A$2:$A$11;" ";REPT(" ";999));TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))-1)+1);999));N(IF(1;1+INT((ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1)/MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";""))))));N(IF(1;1+MOD(ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1;MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))));SMALL(IF(FREQUENCY(IF(INDEX(TRIM(MID(SUBSTITUTE($A$2:$A$11;" ";REPT(" ";999));TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))-1)+1);999));N(IF(1;1+INT((ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1)/MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";""))))));N(IF(1;1+MOD(ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1;MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))))<>"";MATCH(INDEX(TRIM(MID(SUBSTITUTE($A$2:$A$11;" ";REPT(" ";999));TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))-1)+1);999));N(IF(1;1+INT((ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1)/MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";""))))));N(IF(1;1+MOD(ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1;MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))));INDEX(TRIM(MID(SUBSTITUTE($A$2:$A$11;" ";REPT(" ";999));TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))-1)+1);999));N(IF(1;1+INT((ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1)/MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";""))))));N(IF(1;1+MOD(ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11))))-1;MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))))));0));ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11)))));ROW(INDIRECT("1:"&(MAX(1+LEN($A$2:$A$11)-LEN(SUBSTITUTE($A$2:$A$11;" ";"")))*ROWS($A$2:$A$11)))));ROWS($1:1)));"")



KASUS 5

Kasus ke-5 ini adalah mengambil angka berdasar ganjil dan genap kemudian dijumlahkan, rumusnya
1. Untuk Angka Ganjil
=SUMPRODUCT((MOD(--MID($A$2;ROW(OFFSET($A$1;;;LEN($A$2)));1);2)=1)*MID($A$2;ROW(OFFSET($A$1;;;LEN($A$2)));1))
2. Untuk Angka Genap
=SUMPRODUCT((MOD(--MID($A$2;ROW(OFFSET($A$1;;;LEN($A$2)));1);2)=0)*MID($A$2;ROW(OFFSET($A$1;;;LEN($A$2)));1)) 


FILE EXCEL

Aplikasi Validasi Kartu Kredit dan Kartu NPWP

Lagi pada liburan ya bro dan bre? Saya lagi selesai bikin aplikasi buat memvalidasi kartu debit/kredit sama kartu NPWP, bagaimana bentuknya?

dan

Rumus yang dipakai untuk validdasi kartu debit/kredit (Rumus Array):
=IFERROR(IF(MOD(SUMPRODUCT(IF(((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16};2)=1)*MID(D2;ROW(INDIRECT("1:16"));1))*({2;2;2;2;2;2;2;2;2;2;2;2;2;2;2;2})>9;((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16};2)=1)*MID(D2;ROW(INDIRECT("1:16"));1))*({2;2;2;2;2;2;2;2;2;2;2;2;2;2;2;2})-9;((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16};2)=1)*MID(D2;ROW(INDIRECT("1:16"));1))*({2;2;2;2;2;2;2;2;2;2;2;2;2;2;2;2})))+SUMPRODUCT(((MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16};2)=0)*MID(D2;ROW(INDIRECT("1:16"));1)));10)=0;"Kartu Valid";"Kartu Tidak Valid");"Kartu Tidak Valid")

Rumus cek validasi kartu NPWP:
=IF(MOD(SUMPRODUCT((MOD({1;2;3;4;5;6;7;8};2)=1)*MID(A17;ROW(INDIRECT("1:8"));1))*({1;1;1;1;1;1;1;1})+((MOD({1;2;3;4;5;6;7;8};2)=0)*MID(A17;ROW(INDIRECT("1:8"));1))*({2;2;2;2;2;2;2;2});10)=VALUE(MID(A17;9;1));"Kartu NPWP Valid";"Kartu NPWP Tidak Valid")

FILE APLIKASI

Kumpulan Kasus Excel Jilid 3

Happy weekend guys and girls, long time no see yes? Miss you already, langsung saja ya saya akan menyajikan rangkuman beberapa kasus lookup dan beberapa kasus yang lain, mudah2xan anda suka :)

Kasus 1

dan hasil yang diharapkan

dan rumusnya adalah
=IFERROR(INDEX(INDEX(B$1:B$1000;MATCH($D3;A$1:A$1000;0)):B$1000;AGGREGATE(15;6;(ROW(INDEX(A$1:A$1000;MATCH($D3;A$1:A$1000;0)):A$1000)-ROW(INDEX(A$1:A$1000;MATCH($D3;A$1:A$1000;0)))+1)/(E3=INDEX(A$1:A$1000;MATCH($D3;A$1:A$1000;0)):A$1000);1));"")

Kasus 2

Kasus ke 2 ini adalah mencari nilai terakhir dari 3 tabel dengan kriteria, dan hasil yang diinginkan adalah seperti ini

Rumusnya :
=IFERROR(IFERROR(IFERROR(1/(1/VLOOKUP(J3;$G$3:$H$12;2;0));1/(1/VLOOKUP(J3;$D$3:$E$12;2;0)));1/(1/VLOOKUP(J3;$A$3:$B$12;2;0)));0)

Kasus 3

Hasil yang diinginkan

Rumus :
=IFERROR(INDEX($B$2:$E$5;MATCH(A9;$A$2:$A$5;0);COUNTIF(A$9:A9;A9));"")

Kasus 4

Hasil yang diharapkan

Rumusnya
=INDEX(A$2:A$13;(ROW()-ROW($E$4))+MATCH($F$2;$A$2:$A$13;0);1)

Kasus 5

Kasus ke 5 ini adalah mencari nilai terdekat dari kriteria dan hasil yang diinginkan

Rumus :
=IFERROR(LARGE(A1:A10;COUNTIF(A1:A10;">"&C2)+1);"")
atau
=INDEX($A$1:$A$10;MATCH(MIN(ABS($A$1:$A$10-C2));INDEX(ABS($A$1:$A$10-C2);;);0);1) --> rumus array

Kasus 6

Kasus ke 6 ini mencari nilai tanpa duplikasi (unique values) dari tabel, hasilnya

Rumus (rumus array):
=IFERROR(INDEX($A$2:$E$9;MIN(IF(COUNTIF($G$1:$G1;$A$2:$E$9);FALSE;ROW($A$2:$E$9)-MIN(ROW($A$2:$E$9))+1));MATCH(TRUE;ISNA(MATCH(INDEX($A$2:$E$9;MIN(IF(COUNTIF($G$1:$G1;$A$2:$E$9);FALSE;ROW($A$2:$E$9)-MIN(ROW($A$2:$E$9))+1));0);$G$1:$G1;0));0));"")

FILE LATIHAN

Mengambil Data Berdasar Banyaknya Kemunculan Teks

Met malem, kebetulan bangun tidur dan susah mau tidur lagi kemudian posting lagi aja deh, kali ini kasusnya adalah pengambilan data berdasar frejuensi teks terbanyak. Tabelnya sepeti ini

Hasil yang diharapkan seperti ini

Rumusnya :
letakkan di C3 dan tekan secara array atau CSE kemudian copas ke bawah.
=INDEX($A$1:$A$10;MATCH(LARGE(FREQUENCY(MATCH($A$1:$A$10;$A$1:$A$10;0);ROW($A$1:$A$10)-ROW($A$1)+1)+ROW($A$1:$A$11)/10000;ROWS(A$1:A1));FREQUENCY(MATCH($A$1:$A$10;$A$1:$A$10;0);ROW($A$1:$A$10)-ROW($A$1)+1)+ROW($A$1:$A$11)/10000;0))

atau
=IFERROR(INDEX($A$1:$A$10;MODE(MATCH($A$1:$A$10;$A$1:$A$10;0)+{0\0}));"")
baris ke-2
=IFERROR(INDEX($A$1:$A$10;MODE(IF(COUNTIF(C$8:C8;$A$1:$A$10)=0;MATCH($A$1:$A$10;$A$1:$A$10;0)+{0\0})));"")

Semuanya rumus array, download file latihan