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

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