Kumpulan Kasus Excel Jilid 2

Kembali berjumpa dengan anda, senang rasanya bisa memberikan sebuah trik rumus excel kita kali ini dengan beberapa kasus pada excel, langsung ya

KASUS 1

Kemudian hasil yang diinginkan

Yang warna kuning berisi rumus dan yang putih adalah kriterianya, dan rumusnya
diletakkan di B12 dan di-ENTER secara array atau CSE, kemudian copas ke bawah
=INDEX($B$3:$F$8;SMALL(IF(A12=$A$3:$A$8;ROW($A$3:$A$8)-MIN(ROW($A$3:$A$8))+1);COUNTIF($A$12:A12;A12));MATCH(B$11;$B$2:$F$2;0))

Kasus 2

dan hasil yang diharapkan

Kasus ini adalah mengambil data sesuai kriteria pada data yang banyak di dalam 1 cell dan rumusnya adalah (diletakkan di B8 (rumus array)
=INDEX($B$2:$B$4;MATCH(TRUE;SUBSTITUTE($A$2:$A$4;A8;"")<>$A$2:$A$4;0))

Kasus 3

Hasil

Kasus 3 ini adalah mencari data berdasar rentang nilai, jika kriteria adalah 250 dan jika diperhatikan pada tabel 250 merupakan nilai diantara nilai 200 dan 500 maka hasil yang diambil adalah baris dibawahnya, dan rumusnya
=IFERROR(LOOKUP(B5;SUBSTITUTE(B1:L1;L1;-1)+1;B2:L2)+0,1;B2)

Kasus 4

hahasil yang diharapkan

Kasus 4 mirip dengan Kasus 3 hanya beda pada data di baris qty, rumus
=LOOKUP(B13;--LEFT(SUBSTITUTE(B9:L9;">2500";"2501-");FIND("-";SUBSTITUTE(B9:L9;">2500";"2501-"))-1);B10:L10)

Kasus 5

Kasus 5 adalah trik untuk memisahkan antara nama depan, nama tengah dan belakang dan terakhir nama keluarga, syaratnya data harus konsisten yaitu nama yang dipisahkan semuanya mempunyai nama keluarga, hasilnya seperti ini

Rumusnya
a. Untuk Nama Depan
=LEFT(A2;FIND(" ";A2)-1)
b. Untuk Nama Tengah dan Belakang
=SUBSTITUTE(SUBSTITUTE(A2;B2;"");D2;" ")
c. Untuk Nama Keluarga (array formula)
=MID(A2;MAX(IF(MID(A2;ROW($1:$100);1)=" ";ROW($1:$100)))+1;LEN(A2))

FILE EXCEL LATIHAN DOWNLOAD

Kumpulan Kasus Excel Jilid 1

Met siang ya, berlanjut saya akan memposting bebeberapa kasus ya kali ini ada 4 kasus yang akan share ke anda.

KASUS 1.

dan hasil yang diharapkan seperti ini

Rumus seperti ini:
1. =IFERROR(INDEX($A$2:$D$6;SUMPRODUCT(($A$2:$D$6=A9)*(ROW($A$2:$D$6)-1));SUMPRODUCT(($A$2:$D$6=A9)*(COLUMN($A$2:$D$6)))+1);"")
2. =IFERROR(INDEX(A2:D6;AGGREGATE(15;6;ROW($1:$5)/(A2:D6=A9);1);AGGREGATE(15;6;COLUMN($A:$D)/(A2:D6=A9);1)+1);"")


KASUS 2

Hasil yang diinginkan

rumusnya seperti ini, diletakkan di cell D2 dan copas ke bawah
=IF(ROWS($1:1)<=($C$2<>"Irna")+2;INDEX(A$2:A$12;MATCH($C$2;$A$2:$A$12;0)+ROWS($1:1));"")


KASUS 3

Hasilnya adalah mengambil data yang tidak kosong pada masing2x kolom

rumus array:
=IF(COUNTA(A2:C2)=0;"";INDEX(A2:C2;1;MAX((A2:C2<>"")*COLUMN(A2:C2))-COLUMN(A2:C2)+1))
KASUS 4

Hasil yang diinginkan adalah memasukkan baris kosong diantara data dengan kriteria atau istilahnya "insert rows" ya

Jadi baris kosong yang ingin dimasukkan diantara data bisa sesuai kriteria tetapi dikurangi 1, misalnya anda menulis angka 3 maka baris yang kosong adalah 2, atau jika anda ingin 'insert rows" nya 6 baris maka anda harus input 7, dst.
rumusnya, untuk kolom E
=IFERROR(IF(MOD(ROWS($A1:A$1)-$D$2;$D$2)=0;"";INDIRECT("Kasus4!A"&2+((ROWS($A1:A$1)-1)/$D$2)));"")
untuk kolom F
=IFERROR(IF(MOD(ROWS($A1:B$1)-$D$2;$D$2)=0;"";INDIRECT("Kasus4!B"&2+((ROWS($A1:B$1)-1)/$D$2)));"")

FILE LATIHAN

Dependent Data Validation (Advanced)-2

Met pagi semua para followers tips rumus excel, senang bisa posting lagi, kali ini tentang data validation lagi, agak berbeda dengan postingan terdahulu dan perbedaannya pada tabel data yang digunakan untuk data validation.

dan hasil yang ingin ditampilan adalah

Jadia ada 2 data validation, yang pertama adalah dv utama dan yang ke-2 adalah dv member, dan tergantung pada dv utama.
Buatlah dulu dv utama, buatlah dengan contoh dibawah ini langsung ketik saja pada "source" tanpa perlu tabel lagi dengan tanda pemisah "tanda titik koma", ini mungkin relatif ya mungkin jika sistem komputer anda berbeda mungkin memakai "tanda koma". Letakkanlah dv utama pada cell D2.

Kemudian untuk membuat DV diperlukan rumus, letakkanlah di cell kosong seperti contoh dibawah
=OFFSET(B1;MATCH(D2;A2:A10;0);0;COUNTIF(A2:A10;D2);1)
ini rumus array jadi harus tekan tombol CSE, lalu buatlah data validationnya seperti biasa, pada menu data carilah tab "Data Validation" kemudian masukkanlah rumus diatas pada isian "source"

Kemudian klik "OK" dan lihatlah dan test perubahan apa yang terjadi, Untuk Lebih jelasnya keseluruhan dari pembuatan data validation ini silahkan download dan lihat LINK VIDEO