23 Juni 2017
24 Mei 2017
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
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
7 Mei 2017
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
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
8 April 2017
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
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
15 Januari 2017
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
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
13 Januari 2017
REVERSE DAN FLIP DATA
Selamat siang para penggemar dan pemerhati Excel, artikel ini mengawali tahun 2017, mudah2xan anda semua sehat dan selalu mendapatkan keberkahan dalam hidup, tetap semangat ya readers. Berkaitan dengan judul Reverse Data, apa itu reverse? artinya membalikkan data, ya hampir sama artinya dengan flip ya. Apa yang dibalik? Jadi yang saya bahas yaitu bagaimana mambalikkan lay out data. Langsung tkp ya guys, tabel permasalahan seperti ini
dan bagaimana membuatnya seperti ini
Jadi hasilnya jadi selain urutannya terbalik dan flip gitu, gimana ya rumusnya, diletakkan di cell D2 dan cops ke samping dan kebawah:
=INDEX($A$2:$B$6;ROWS(A2:A$6);COLUMNS(A2:$B$6))
atau yang ini juga bisa
=INDEX(A$2:A$6;ROWS(A$2:A$6)-ROW(A1)+1;1)
Kasus berikut
tetapi bagaimana dengan tabel yang ada baris yang kosong? tentu tidak bisa pake rumus yang diatas
rumus, diletakkan di D16 dan ENTER secara array, kemudian dicopas ke bawah dan samping
=IFERROR(INDEX(A$16:A$20;SMALL(IF(A$16:A$20<>"";ROW(A$16:A$20)-ROW($A$16)+1);COUNTIF($A$16:$A$20;"="&"?*")-ROW(A1)+1));"")
Kasus ke 3
Nah yang ini multiple columns ya dan hasil yang diinginkan ada 5 lay out baru
1.
rumus:
=IFERROR(INDEX($A$25:$E$29;6-ROWS($1:1);7-COLUMNS($A:A)-ROWS($1:1));"")
2.
rumus:
=IFERROR(INDEX($A$25:$E$29;6-ROWS($1:1);COLUMNS($A:A)-ROWS($1:1)+1);"")
3.
rumus:
=IFERROR(1/(1/INDEX(A$25:A$29;6-ROWS($1:1)));"")
4.
rumus:
=IFERROR(1/(1/INDEX($A$25:$E$29;6-ROWS($1:1);COLUMNS($A$1:$F$1)-COLUMNS($A$1:A1)));"")
5.
rumus:
=IF(OR(ROWS($A$1:$A1) > 5;ROWS($A$1:$A1) < COLUMNS($A$1:A$1));"";OFFSET($A$25;ROWS($A$1:$A1)-1;ROWS($A$1:$A1)-COLUMNS($A$1:A$1)))
6.
rumus:
=IFERROR(INDEX($A25:$E25;AGGREGATE(14;6;COLUMN($A25:$E25)/($A25:$E25<>"");COLUMNS(A25:$E25)));"")
7.
rumus:
=IFERROR(1/(1/INDEX($A$25:$E$29;ROWS($1:1);6-COLUMNS($A$1:A1)));"")
FILE LATIHAN
dan bagaimana membuatnya seperti ini
Jadi hasilnya jadi selain urutannya terbalik dan flip gitu, gimana ya rumusnya, diletakkan di cell D2 dan cops ke samping dan kebawah:
=INDEX($A$2:$B$6;ROWS(A2:A$6);COLUMNS(A2:$B$6))
atau yang ini juga bisa
=INDEX(A$2:A$6;ROWS(A$2:A$6)-ROW(A1)+1;1)
Kasus berikut
tetapi bagaimana dengan tabel yang ada baris yang kosong? tentu tidak bisa pake rumus yang diatas
rumus, diletakkan di D16 dan ENTER secara array, kemudian dicopas ke bawah dan samping
=IFERROR(INDEX(A$16:A$20;SMALL(IF(A$16:A$20<>"";ROW(A$16:A$20)-ROW($A$16)+1);COUNTIF($A$16:$A$20;"="&"?*")-ROW(A1)+1));"")
Kasus ke 3
Nah yang ini multiple columns ya dan hasil yang diinginkan ada 5 lay out baru
1.
rumus:
=IFERROR(INDEX($A$25:$E$29;6-ROWS($1:1);7-COLUMNS($A:A)-ROWS($1:1));"")
2.
rumus:
=IFERROR(INDEX($A$25:$E$29;6-ROWS($1:1);COLUMNS($A:A)-ROWS($1:1)+1);"")
3.
rumus:
=IFERROR(1/(1/INDEX(A$25:A$29;6-ROWS($1:1)));"")
4.
rumus:
=IFERROR(1/(1/INDEX($A$25:$E$29;6-ROWS($1:1);COLUMNS($A$1:$F$1)-COLUMNS($A$1:A1)));"")
5.
rumus:
=IF(OR(ROWS($A$1:$A1) > 5;ROWS($A$1:$A1) < COLUMNS($A$1:A$1));"";OFFSET($A$25;ROWS($A$1:$A1)-1;ROWS($A$1:$A1)-COLUMNS($A$1:A$1)))
6.
rumus:
=IFERROR(INDEX($A25:$E25;AGGREGATE(14;6;COLUMN($A25:$E25)/($A25:$E25<>"");COLUMNS(A25:$E25)));"")
7.
rumus:
=IFERROR(1/(1/INDEX($A$25:$E$29;ROWS($1:1);6-COLUMNS($A$1:A1)));"")
FILE LATIHAN
Langganan:
Postingan (Atom)