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

Memahami Penggunaan INDEX dan MATCH

Ada beberapa pembaca blog saya yang kurang memahami kombinasi INDEX dan MATCH, kesempatan ini saya akan memberikan penjelasan yang mendetail dan step by step.

1. Saya akan mulai data sederhana seperti ini

Cobalah praktek dengan saya, buatlah tabel diatas jika sudah jadi ketikkanlah ini di cell kosong
=MATCH("Udin";A2:A5;0)
"Udin" ini bisa anda ganti dengan referensi cell ya, nah fungsi MATCH disini adalah menghasilkan nilai baris dimana nama "Udin" berada dan jawabannya adalah 2. Cobalah mengganti "Udin dengan nama yang lain supaya tahu perbedaannya. Jika sudah puas dan paham tentang MATCH kita beralih dengan INDEX

syntax dari INDEX adalah INDEX(array;row_num;[column_num]), tetapi kita fokus yang ini saja INDEX(array;row_num), contoh penggunaannya
=INDEX(A2:A5;3)
rumus diatas akan menghasilkan nama "Didit" , angka 3 diganti angka 2 akan menghasikan nama "Udin", dst
Tetapi hal itu terasa manual kurang praktis dan sangat pas jika INDEX digabung dengan MATCH, seperti ini
=INDEX(B2:B5;MATCH("Udin";A2:A5;0))
Secara bahasa simpel INDEX akan mengambil data sesuai dengan nomor baris yang dihasilkan oleh MATCH, jika MATCH hasilnya 2 maka INDEX akan muncul data baris ke 2 juga.

Mohon maaf jikadi dalam memberikan penjelasan dan keterangan kurang mengena atau kurang jelas.

Horizontal Counting

Secara umum orang pasti pernah menggunakan fungsi COUNTIF atau COUNTIFS untuk menghitung data secara vertikal, tetapi data tidak selalu berbentuk vertikal ada juga yang berbentuk horizontal, nah untuk yang horizontal fungsi COUNTIF dan COUNTIFS tidak bisa digunakan dengan kondisi menggunakan kriteria oada kolom vertikal, perlu modifikasi dengan fungsi2x yang lain, langsung dengan tabel kasus di bawah ini:

KASUS 1

Hasil yang diinginkan adalah menghitung kursus bahasa yang diambil dengan kriteria "Nama"

Rumus 1 (letakan pada cell B10 - (rumus non array)):
=COUNTIF(INDEX($B$2:$D$7;MATCH($A10;$A$2:$A$7;0);0);"<>"&"")

Rumus 2 (rumus non array)
=SUMPRODUCT(($A$2:$A$7=$A10)*($B$2:$D$7<>""))

Rumus 3 (rumus non array)
=COUNTIFS($A$2:$A$7;$A10;$B$2:$B$7;"<>"&"")+COUNTIFS($A$2:$A$7;$A10;$C$2:$C$7;"<>"&"")+COUNTIFS($A$2:$A$7;$A10;$D$2:$D$7;"<>"&"")

Rumus 4 (rumus non array)
=COUNTA(INDEX(B$2:D$7;MATCH(A10;A$2:A$7;0);0))

Rumus 5 (rumus array)
=SUM(IF($A$2:$A$7=A10;IF($B$2:$D$7<>"";1;0)))

KASUS 2

Hasil yang diharapkan

Jadi hasil diatas adalah menghitung kolom B, C, dan D yang berisi teks atau tidak blank
Rumus 1:
=COUNTA(B2:D2)

Rumsu 2
=COUNTIF(B2:D2;"="&"?*")

Rumus 3
=SUMPRODUCT(--(B2:D2<>""))

FILE LATIHAN

Menyembunyikan Angka Nol Dengan Custom Format dan Conditional Formatting

Untuk memperluas wawasan, akan saya selingi trik dengan custom format dan conditional formatting. Apa ya bedanya antara keduanya? Dan sesuai topik pagi, bagaimana menghilangkan atau menyembunyikan angka nol yang ada di data kita. Tampilan permasalahan

Tidak semua orang suka ya dengan cell yang berisi angka nol, bos juga tidak terlalu suka laporannya muncul angka nol, nah bagaimana triknya? Yang pertama dengan menggunakan Custom Format, ikuti langkahnya ya
1. Letakkan kursor mouse anda pada cell yang akan di format
2. Klik kana mouse anda dan pilih "Format Cells......."

3. Pilih "Custom"

4. Letakkan kursor mouse anda pada isian "Type:" dan isikan 0;-0;;@

5. Last step, mengcopy custom format dengan menggunakan Format Painter terletak di menu "Home"

kemudian and copas ke semua bidang tabel data dan nan ti akan anda perhatikan semua angka nol akan jadi blanks atau hilang ya. Bagaimana dengan conditional formatting atau format bersyarat, contoh di bawah ini menggunakan video.
Link Video-1

Contoh video yang lain
Link Video-2

Convert Text Number to Real Number

Happy weekend ya'll, glad meet you again, mungkin anda pernah menemui kasus seperti ini, mempunyai data yang nampaknya itu angka tetapi bukan karena terformat sebagai teks. Pasti pernah ya, jika anda ragu dengan data anda tersebut anda bisa cek data tersebut dengan menggunakan fungsi ISNUMBER, hasilnya akan TRUE dan FALSE, jika TRUE tidak masalah ya, namun jika FALSE, saya punya trik untuk mengatasinya, langsung tkp

Anda bisa merubahnya tetapi bukan pada cell datanya, anda tahu jika menggunakan rumus, hasilnya pasti di lain cell ya, itu prinsipnya.
Trik 1 adalah dengan ditambah nol
=A2+0
Trik 2 adalah Di kali dengan satu
=A2*1
Trik 3 adalah dengan di bagi satu
=A2/1
Trik 4 adalah dengan dobel negatif
=--A2
Trik terakhir adalah dengan fungsi VALUE
=VALUE(A2)
Setelah itu anda bisa cek lagi dengan fungsi ISNUMBER ya supaya bisa dipastikan bahwa sudah berubah menjadi angka yang sebetulnya. Apakah anda punya cara yang lain? SIlahkan di-share disini ya. Rumus di atas cukup simpel ya dan mudah2xan bisa dipahami. Saya sering ya menggunakan trik ini untuk mengatasi angka2x tetapi teks.

cheers
FILE EXCEL

Aplikasi Lacak Kode Pos dan Lacak KTP

Senang bersua kembali bersama saya di tips rumus excel, agak bombastis ya judulnya, dan yang paling penting bisa berguna bagi yang membutuhkan informasi ini, lanjut ke gambar di bawah :

Jadi saya punya 3 drop down list atau semacam data validation ya, drop down yang pertama akan menampilkan nama propinsi, ente tinggal pilih, drop down list kedua akan muncul nama kabupaten/kota, ini otomatis ya munculnya, Jika nama kabupaten/kota sangat banyak jadi lambat harus sabar apalagi spek kompi anda kurang memadai. Terakhir akan muncul nama2x desa/kelurahan disertai kode pos masing2x, ini lambat juga ya jadi setelah anda ganti kabupaten, jika nama kecamatan/desa sangat banyak hasilnya akan memerlukan beberapa detik untuk bisa muncul, maklum database-nya besar. Drop down list ke-3 adalah pilihan untuk menampilkan nama2x desa/kelurahan atau nama2x kecamatan. Aplikasi ini bisa dipakai belajar buat adik2x di sekolah SD mauppun SMP untuk belajar hapalan nama2x propinsi atau kabupaten/kota.

Yang kedua

adalah pelacak nomor KTP, gunanya apa ya? Entahlah mungkin lebih berguna bagi aparat desa atau kecamatan ya? Jadi nomor KTP anda mengandung informasi mengenai diri anda, untung ya tidak tampil nama dan alamat hihihihihi...................

UNDUH FILE LATIHAN

Menghitung Hari Kerja dengan NETWORKDAYS dan NETWORKDAYS.INTL

Met siang para murid tips rumus excel, saya merasa berdosa tidak selalu dapat update posting, saya bingung materinya kayaknya dah habis xixixi........, but i have a bright idea today, yaitu menghitung hari kerja dengan mengggunakan fungsi NETWORKDAYS dan NETWORKDAYS.INTL, dan juga perbedaannya, semoga jelas pemaparan dari saya.
Kedua fungsi ini secara otomatis tidak menghitung hari "Sabtu" dan "Minggu" karena secara umum ke-2-nya adalah hari libur, contohnya dibawah ini

Ada 2 cell yang saya warnai kuning karena 2 rumus yang berbeda
rumus di A5 (exclude holyday)
=NETWORKDAYS(A2;B2)
rumus di A6 (include holyday/hari libur)
=NETWORKDAYS(A2;B2;E3:E21)

Ada yang beda pada E3:E21 ini adalah kolom untuk hari libur, jadi rumus ke-2 lebih akurat ya karena bisa exclude (tidak dimasukkan) libur nasional, saya letakkan di file latihan tabel libur nasional dan cuti bersama 2016.

Bagaimana dengan NETWORKDAYS.INTL?, fungsi ini lebih canggih daripada NETWORKDAYS dan hanya tersedia di Excel 2010 keatas

Rumus yang pertama (excluded hari libur)
=NETWORKDAYS.INTL(A2;B2;{1;2;3;4;5;6;7})

Kedua (included hari libur)
=NETWORKDAYS.INTL(A2;B2;1;E3:E21)

Apa arti {1;2;3;4;5;6;7} ? saya jelaskan melalui tabel di bawah ini

nah jelas to? jadi angka2x pada {1;2;3;4;5;6;7} mempresentasikan hari, jadi anda bisa memilih hari yang akan dimasukkan atau tidak pada rumus NETWORKDAYS.INTL
Contoh ke-2
=NETWORKDAYS.INTL(A2;B2;1;E3:E21)
yang saya warnai biru maksudnya seperti ini

Jadi saya memilih 1 karena saya ingin exclude weekend yaitu Sabtu dan Minggu dan anda bisa memilih hari yang lain, jadi NETWORKDAYS.INTL sangat fleksibel, Is it enlightened yet? I hope the answer is yes of course. Any question my student? Just try it,  if something goes wrong just tell me.

UNDUH FILE LATIHAN

Mengambil Atau Menghapus Kata Dari Kalimat

Met Siang lama tak bersua rupanya, ya akhir2x ini jadi malas posting, tapi yang penting tetap semangat!, topik kali ini mengambil dan me-remove kata dari suatu kalimat. Contohnya dibawah ini

Mudah2xan cukup jelas gambarnya, rumus2xnya adalah
1. Ambil 1 Kata Pertama
    =LEFT(A1;SEARCH(" ";A1;1)-1) atau
    =TRIM(LEFT(SUBSTITUTE(A1;" ";REPT(" ";100));100))
2. Ambil 2 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1;" ";REPT(" ";100));200)) atau
    =LEFT(A1;(FIND("|";SUBSTITUTE(A1;" ";"|";2)))-1)
3. Ambil 3 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1&" ";" ";REPT(" ";255);3);255))
4. Ambil 4 Kata Pertama
    =TRIM(LEFT(SUBSTITUTE(A1&" ";" ";REPT(" ";255);4);255))
5. Ambil 1 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";255));255))
6. Ambil 2 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";60));120))
7. Ambil 3 Kata Terakhir
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";60));180))
8. Hapus 1 Kata Pertama
    =RIGHT(A1;LEN(A1)-FIND(" ";A1))
9. Hapus 2 Kata Pertama
    =MID(A1;1+FIND("~";SUBSTITUTE(A1;" ";"~";2));255)
10. Hapus 3 Kata Pertama
      =MID(A1;1+FIND("~";SUBSTITUTE(A1;" ";"~";3));255)
11. Hapus Kata Terakhir
     =LEFT(TRIM(A1);FIND("~";SUBSTITUTE(A1;" ";"~";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))-1)

Aplikasi Untuk Kuitansi

Ini posting saya di tahun 2016, happy new year yah semuanya, mudah2xan sukses buat elu2x pade, baiklah kali ini saya membuat aplikasi untuk kuitansi atau semacam aplikasi terbilang ya, hanya saja saya pake rumus dan bisa meng-cover 999.999.999.999.999, sampai triliun, dan untuk diketahui sistem bilangan untuk currency, negara kita Indonesia mengacu pada sistem bilangan yang dipakai di U.S dan tampilannya seperti ini:

dan untuk aplikasi kuitansinya seperti ini:

Rumusnya puanjang ya,

=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));1;1);n_1;0))=0;"";INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));1;1);n_1;0))&" ratus ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1);0);n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1);0);n_1;0))&" puluh ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2));n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2));n_1;0)))&IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));1;1)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;1);0)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));1;3));REPT(0;3));2;2))=0;"";" triliun "));"satu ribu";" seribu ");"satu ratus";" seratus ");"satu puluh";" sepuluh "))&" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));1;1);n_1;0))=0;"";INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));1;1);n_1;0))&" ratus ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1);0);n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1);0);n_1;0))&" puluh ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2));n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2));n_1;0)))&IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));1;1)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;1);0)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));4;3));REPT(0;3));2;2))=0;"";" miliar "));"satu ribu";" seribu ");"satu ratus";" seratus ");"satu puluh";" sepuluh "))&" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));1;1);n_1;0))=0;"";INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));1;1);n_1;0))&" ratus ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1);0);n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1);0);n_1;0))&" puluh ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2));n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2));n_1;0)))&IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));1;1)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;1);0)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));7;3));REPT(0;3));2;2))=0;"";" juta "));"satu ribu";" seribu ");"satu ratus";" seratus ");"satu puluh";" sepuluh "))&" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));1;1);n_1;0))=0;"";INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));1;1);n_1;0))&" ratus ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1);0);n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1);0);n_1;0))&" puluh ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2));n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2));n_1;0)))&IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));1;1)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;1);0)+IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));10;3));REPT(0;3));2;2))=0;"";" ribu "));"satu ribu";" seribu ");"satu ratus";" seratus ");"satu puluh";" sepuluh "))&" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));1;1);n_1;0))=0;"";INDEX(a_1;MATCH(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));1;1);n_1;0))&" ratus ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;1);0);n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;1)>1;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;1);0);n_1;0))&" puluh ")&IF(INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;2));n_1;0))=0;"";INDEX(a_1;MATCH(IF(--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;2)>19;--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));3;1);--MID(TEXT(N(--MID(TEXT($M$17;REPT(0;15));13;3));REPT(0;3));2;2));n_1;0))));"satu ribu";" seribu ");"satu ratus";" seratus ");"satu puluh";" sepuluh ")))&IF(M17=0;"";" Rupiah")

Rumus ini bisa anda terapkan untuk keperluan lain, tidak harus untuk "currency" ya, anda dapat menghilangkan kata "Rupiah" pada rumus, dan referensi cell dapat anda ganti sesuai keperluan dengan menggunakan "Find and Replace".

FILE EXCEL KUITANSI

Dependent Data Validation (Advanced)

Tema kali ini mengenai data validation bersyarat/dependent data validation, langsung saja ya..

KASUS A

a. Yang merupakan dependent data validation adalah yang berwarna biru atau merupakan data validation yang menampilkan data anggota dari "buah" atau "sayur".
b. Bagaimana caranya, pertama-tama buatlah dulu data validation grup (warna kuning) dengan mengambil data pada rentang cell D3:E3.
c. Untuk data validation warna biru, copy-kan rumus ini pada isian "source" data validation =IF(A3="Buah";D4:D8;E4:E8)
d. Klik "ok" dan cobalah untuk melihat hasilnya.

KASUS B

a. Trik ke-2 adalah menampilkan blank ketika merubah grup pada data validation
b. Cara ini berguna untuk mengatasi kelemahan pada contoh dependent data validation nomor 1 diatas, karena data validation member tidak update otomatis ketika anda merubah data validation grup.
c. Saya menggunakan conditional formatting untuk menampilkan blank/kosong yang menggunakan rumus dan diletakkan pada data validation member.
d. Caranya klik tab "Conditional Formatting" pada menu "Home"
e. Pilih "new rule…."
f. Pilih "use a formula to determine which cells to format"
g. Kemudian isikan rumus ini : =ISNA(MATCH(B25;INDEX(D26:E30;;MATCH(A25;D25:E25;0));0))
h. Kemudian klik pada "format", pilihlah font dan background warna biru sesuai warna cell dv member
i. Klik "Ok"
j. Cobalah dengan merubah grup dan member untuk mengetahui perubahannya

KASUS C

a. Langkah pertama buatlah dua buah drop down list dengan como box (form control), dengan asumsi tab "Developer" sudah aktif ya.
b. Kemudian klik kanan pada mouse pada dv grup

c. Klik pada "Format Control", kemudian muncul

d. Anda isikan pada "Input Range", dengan data grup yaitu pada D44:D45 (dicocokkan dengan tabel diatas).

e. Cell Link, pada gambar diisikan cel G44, kenapa demikian? Karena akan digunakan sebagai kriteria dependent data validation list-nya

f. Cell Link ini mengembalikan nomor baris yang dipilih pada dv grup (lihat video atau coba lihatlah pada cell G44 diatas)

g. Isian "Drop down lines" anda sesuaikan dengan jumlah baris data yang ditampilkan

h. Klik "Ok"

i. Selanjutnya tempatkan rumus ini pada G45 kemudian array entered :

 =IFERROR(INDEX($E$45:$F$49;SMALL(IF((INDEX($E$45:$F$49;0;MATCH(INDEX($D$44:$D$45;$G$44);$E$44:$F$44;0))<>"");
MATCH(ROW($E$45:$E$49);ROW($E$45:$E$49));"");ROW(A1));MATCH(INDEX($D$44:$D$45;$G$44);$E$44:$F$44;0));"")

j. copy ke bawah secukupnya

k. Rumus ini akan menampilkan anggota/member dari "Buah" atau "Sayur", saya tempatkan contoh diatas, hasil inilah yang saya pakai sebagai "input Range" pada dv member/anggota, dan kelebihan menggunakan combo box ini, member akan ter-update secara otomatis

Saya sertakan juga video tutorial untuk melengkapi pemahaman tutorial dari saya, mudah2xan paham, mohon maaf jika ada yang kurang jelas.

Download:

Video Tutorial 1

Video Tutorial 2

Video Tutorial 3

Data Validation (Basic)

Hi jumpa lagi bro semuanya, tangan jadi gatal karena lama tak posting, kali ini mengenai Data Validation, mungkin sudah banyak uang bisa ya, tetapi saya akan coba sampaikan teknik-tekniknya mulai dari yang sederhana sampai dependent data validation atau data validasi bersyarat.

1. Data Validation Tanpa Tabel
» Pilih cell B2
» Klik pada menu > Data > Data Validation
» Kemudian copy data di bawah ini (datanya sebagai contoh saja), dan sebagai tanda pemisah menggunakan "titik koma" atau "koma" tergantung sistem di komputer anda.

SDM; Sales; Operasional; Keuangan; Marketing

Contoh:

Link Video Tutorial-1

2. Data Validation Dengan Tabel
» Pilih cell B2
» Klik pada menu > Data > Data Validation
» Kemudian copy data di bawah ini (misalnya tabel anda pada ranges $B$17:$B$21)

Contoh:

it's pretty easy, right?

Link Video Tutorial-2

  

3. Data Validation Dengan Combo Box (Form Control)

Sebelum saya menerangkan tentang combo box, anda harus mengaktifkan tab Developer terlebih dahulu pada, jika sudah aktif tampilannya seperti ini

 bagaimana cara supaya aktif atau muncul? lihat dan pelajari di website ini belajarexcel.info

 Jika sudah aktif klik pada tab Developer kemudian klik "Insert" dan akan muncul

silahkan di-unduh dan dipelajari di link Video Tutorial-3

Saya sertakan juga File Excel Tutorial-nya, untuk pelengkap penjelasan dari video tutorial. --> DISINI

Kalender Berbasis Bulan (Monthly Calendar)

Lagi enak2xan bengong eh...ada ide mampir di pikiran saya, ya itu dia membuat kalender dengan excel, bentuknya seperti ini, jadi tidak model kalender full satu tahun tetapi monthly atau per-bulan, anda hanya tinggal mengganti bulan dan tahunnya saja sebagai kriterianya. Ini tipe Kalender yang sederhana.

Saya sertakan juga kalender yang lebih komplit karena selain penanggalan berdasarkan Masehi, juga penanggalan ijriyah dan Jawa disertai hari pasaran jawa, windu, kurup dan pawukon jawa, tampilan sebagai berikut

Maaf snapshotnya kurang jelas, mudah2xan akurat dan bermanfaat

FILE EXCEL UNDUH DISINI

Kombinasi Angka dan Teks (Combination & Permutation)

Slamat siang jumpa lagi bertemu dengan saya, kali ini posting berkaitan dengan kombinasi data angka dengan angka, teks dengan teks dsb. Bahasa matematika-nya kombinasi dan permutasi untuk mencari probabilitas suatu event. Pengertian "Kombinasi" adalah menggabungkan beberapa objek dari suatu grup tanpa memperhatikan urutan, misal seperti ini urutan seperti {1;2;3} adalah sama dengan {2;3;1} dan {3;1;2}.
Sedangkan "Permutasi" adalah menggabungkan beberapa objek dari suatu grup dengan memperhatikan urutan. Di dalam permutasi, urutan diperhatikan, kebalikan dari kombinasi, {1;2;3} adalah tidak sama dengan {2;3;1} dan {3;1;2}, nah bingung kan.....

Mari kita mulai contoh kasus dan hasil yang diharapkan adalah seperti ini

Datanya satu lajur dan ada berapa kemungkinan kah terjadi kombinasi? Untuk rumus yang tanpa adanya pengulangan huruf dalam kombinasi:
=IFERROR(INDEX($A$2:$A$6&" "&TRANSPOSE($A$2:$A$6);SMALL(IF(ROW($A$2:$A$6)<TRANSPOSE(ROW($A$2:$A$6));ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1);ROW(A1));--RIGHT(SMALL(IF(ROW($A$2:$A$6)<TRANSPOSE(ROW($A$2:$A$6));--((ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1)&TEXT(TRANSPOSE(ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1);"0000")));ROW(A1));4));"") --> Rumus Array

Rumus yang terdapat huruf yang sama di kombinasi:
=IFERROR(INDEX($A$2:$A$6;INT((ROW(A1)-1)/ROWS($A$2:$A$6))+1)&" "&INDEX($A$2:$A$6;MOD(ROW(A1)-1;ROWS($A$2:$A$6))+1);"")

Contoh 2:

Hampir sama dengan contoh 1, tetapi contoh ke-2 ini adalah menggabungkan teks dengan angka
=IF(ROWS($A$1:A1)-ROW($A$1)+1>COUNTA($A$2:$A$4)*COUNTA($B$2:$B$5);"";INDEX($A$2:$A$4;INT((ROWS($A$1:A1)-ROW($A$1))/COUNTA($B$2:$B$5)+1))&INDEX($B$2:$B$5;MOD(ROWS($A$1:A1)-ROW($A$1);COUNTA($B$2:$B$5))+1))

Contoh 3:

Yang ini hasilnya berbentuk horizontal ke kanan
=IF(AND(ROWS(A$2:A2)<=COUNTA($B$2:$B$5);COLUMNS($A2:A2)<=COUNTA($A$2:$A$6));INDEX($A$2:$A$6;COLUMNS($A$2:A2))&INDEX($B$2:$B$5;MOD((INT((ROWS($A$2:A2)-1)*1)*1)+COLUMNS($A2:A2)-1;ROWS($B$2:$B$5))+1);"")

Case 4:
Contoh kali ini menggunakan kriteria

kriteria pada cell A2 untuk memnentukan berapa lajur hasil yang akan ditampilkan, sedangkan B2 adalah total angka yang akan dikombinasikan.
=IF(ROWS($A$1:A1)<=$B$2^$A$2;MMULT(1+MOD(INT((ROW(A1)-1)/($B$2^(COLUMN(OFFSET($A$1;;;;$A$2))-1)));$B$2);10^(ROW(OFFSET($A$1;;;$A$2))-1));"")

Happy learn and develop your skills.......

FILE DOWNLOAD