16 November 2015

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:

8 November 2015

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?
  
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

23 September 2015

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

26 Agustus 2015

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

24 Agustus 2015

Konverter Penanggalan Masehi, Hijriyah dan Jawa

Hi, baru hari ini sempat posting lagi, masih berkisar tentang tanggal, seperti diketahui ada beberapa kalender yang dipakai secara luas di masyarakat Indonesia tercinta ini. Yaitu konverter penanggalan / kalender Masehi, Hijriyah dan Jawa. Agak lama saya mengerjakannya karena kesibukan di pekerjaan. Langsung ya guys and girls, konverter ini tinggal pakai dan mudan2xan cukup akurat, silahkan dilihat tampilannya di bawah ini :

Jadi ada 3 drop down list dan 1 text box, yang pertama untuk memilih format penanggalan yang ingin anda konversi, sisanya adalah Tgl, Bulan dan Tahun unntuk input, khusus tahun (text box) anda harus mengisi secara manual (diketik). Kemudian hasilnya ada dibawah, selain tanggal hasil konversi saya tambahkan untuk misalnya tanggal yang diinput adalah tanggal lahir seseorang, sudah lama saya kepengen buat konverter tanggal ini, Alhamdulillah jadi.

Tidak terlupa selain konverter tanggal ini saya tempatkan juga Tabel Bantu saya letakkan pada sheet lain, ada 3 sheet yang digunakan untuk tabel bantu, mohon tidak diutak atik untuk menghindari error atau kesalahan rumus.


Dan terakhir ini adalah file dengan macro enabled so silahkan diaktifkan waktu buka filenya jika ingin menggunakannya dengan cara meng-klik "Enable" dari konfirmasi yang muncul oleh Excel.

KLIK UNTUK DISINI DOWNLOAD FILE

23 Juli 2015

Menghitung Tanggal Selamatan/Peringatan/Tahlilan Orang Meninggal

Met siang readers, saya ini orang jawa jadi tidak heran tradisi jawa lama kelamaan menjadi pusat perhatian saya di tempat saya tinggal, terutama mengenai hal yang satu ini, yaitu mendoakan orang meninggal pada rentang hari-hari tertentu. Di kampung saya namanya tahlilan atau kadang disebut juga yasinan, biasanya diadakan pada hari ke 3, 7, 40, 100, 1 Tahun, 2 Tahun dan terakhir 1000. Tradisi ini sebetulnya tidak milik yang beragama muslim, tetapi bagi yang non-muslim juga, dan di agama islam sebetulnya tidak ada, ada yang pro dan ada yang kontra mengenai tradisi ini dari sisi agama islam, tetapi mari tinggalkanlah itu semua, mari dilihat dari sisi budayanya saja, okee guys? Saya lebih tertarik cara menghitungnya, anda bisa seacrhing di google.com tentang perhitungan ini. Nah saya buatkan untuk anda semua.

Bentuknya


Saya berikan keterangan singkat ya, anda tinggal input tanggal wafat/meninggal dan memilih dari drop down list waktu meninggalnya, kenapa begitu? karena hari berganti menurut tanggal jawa bukan pada pukul 12:00 malam tetapi pada maghrib menjelang, penandanya ya adzan maghrib itu, hal ini sama dengan Kalender Hijriyah, karena sebetulnya Kalender Jawa agak mirip2x dengannya.
Contoh ya, misalnya bapak si fulan meninggal pada hari Sabtu pukul 18:30, maka itu sudah masuk hari Minggu untuk menghitung hari atau tanggal selamatan/tahlil untuk yang meninggal.. Clear nggih.... , untuk waktu sebelum maghrib tertulis pada drop down list adalah "Qobla Maghrib" dan sesudah maghrib "Ba'da Maghrib".
Mudah2xan akurat rumusnya.........

Dan anda bisa mencek dengan menggunakan tabel manual dengan berdasar pada hari dan pasaran jawa































































FILE LATIHAN DOWNLOAD/UNDUH

22 Juni 2015

Konversi Suhu, Pecahan dan Desimal

Selamat malam semuanya, pada kesempatan kali ini materinya yang ringan-ringan saja, saya coba membuat konverter suhu, jadi teringat pelajaran waktu kita SD ya atau pelajaran SMP yah? Misalnya dari Celcius ke Fahrenheit, Kelvin dan Reamur dan sebaliknya. Konversi yang lain dari waktu ke desimal dan sebaliknya dan terakhir konversi angka pecahan ke angka desimal begitupun sebaliknya.
Adapaun konverter suhu tinggal dipakai saja, saya tidak tulis rumusnya (puanjang soale), image-nya seperti ini:














Image untuk konversi yang lainnya:




















A. Konversi Desimal ke Waktu
     Bentuk format jam dan menit
     =TEXT(ABS(A2/24);"h:mm")
    Bentuk format total menit
    =TEXT(ABS(A2)/24;"[m]:ss")

B. Konversi Waktu ke Desimal
    =A7*24
    atau
    =HOUR(A7) + MINUTE(A7)/60+SECOND(A7)/3600

C. Konversi Pecahan ke Desimal
     =VALUE(A11)

D. Konversi Desimal ke Pecahan
     =TEXT(VALUE(A15);IF(ABS(VALUE(A15))<1;"?/?";"0 ?/?")

File Latihan