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

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

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

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

Menghitung Frekuensi Kemunculan Huruf dan Angka

Ada reader yang pernah menanyakan kepada saya cara menghitung frekuensi pemunculan karakter huruf ataupun angka dari suatu kalimat, kasusnya seperti dibawah ini:

Rumus untuk menghitung frejuensi dari 1 karakter huruf:
=LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2);LOWER(A5);""))
Rumus untuk menghitung frejuensi kata:
=(LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2);LOWER(A8);"")))/LEN(LOWER(A8))

Kasus 2 agak berbeda ya dimana yang dihitung semua angka dan semua huruf
Rumus menghjitung frekuensi semua Huruf:
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A11;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;""))
Rumus untuk menghitung frekuensi semua Angka:
=LEN(A11)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A11;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;""))
atau
=SUM(LEN(A11)-LEN(SUBSTITUTE(A11;{1;2;3;4;5;6;7;8;9;0};"")))

FILE LATIHAN

Mengambil Data Unik (Extract Unique Values)

Selamat siang everybody, it's nice to see you again..........kali ini tulisan saya bertemakan mengambil data unik, sepert biasa saja lihat kasus kit hari ini........

1. Mengambil data Unik Tanpa Kriteria

Kasus diatas jika table tidak ada yang kosong atau blank:
Rumus 1 (Array Formula):
=IFERROR(INDEX($A$3:$A$11; MATCH(0;COUNTIF($C$2:C2;$A$3:$A$11);0));"")
Rumus 2 (Non Array Formula):
=LOOKUP(REPT("Z";255);CHOOSE({1;2};"";INDEX($A$3:$A$11;MATCH(TRUE;INDEX(ISNA(MATCH($A$3:$A$11;$E$2:$E2;0));0);0))))
Rumus 3 (Non Array Formula):
=IFERROR(INDEX($A$3:$A$11;MATCH(0;INDEX(COUNTIF($F$2:F2;$A$3:$A$11);0;0);0));"")

Jika tabel terdapat data yang kosong/blank, maka rumus diatas akan gagal kalkulasinya:
Rumus 1 (Array Formula):
=IFERROR(INDEX($A$16:$A$24; MATCH(0;COUNTIF($C$15:C15;$A$16:$A$24&"");0));"")
Rumus 2 (Array Formula):
=IFERROR(INDEX($A$16:$A$24; MATCH(0;IF(ISBLANK($A$16:$A$24);1;COUNTIF($E$15:E15; $A$16:$A$24)); 0));"")
Rumus 3 (Array Formula):
=IFERROR(INDEX($A$16:$A$24;SMALL(IF(FREQUENCY(IF($A$16:$A$24<>"";MATCH("~"&$A$16:$A$24&"";$A$16:$A$24&"";0));ROW($A$16:$A$24)-ROW($A$16)+1);ROW($A$16:$A$24)-ROW($A$16)+1);ROWS($A$1:A1)));"")
Rumus 4 (Array Formula):
=IFERROR(INDEX($A$16:$A$24;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$16:$A$24<>"";$A$16:$A$24);$A$16:$A$24;0);"");ROW($A$16:$A$24)-ROW($A$16)+1);ROW($A$16:$A$24)-ROW($A$16)+1);ROWS($A$1:A1)));"")

2. Mengambil data Unik Dengan Kriteria

 Nah Kasus ke-2 ini mengambil data unik dengan kriteria, misalnya contoh di atas kita ingin mengambil produk PT. ABC, dan di tabel terdapat data duplikat atau yang sama, dan itu tidak diinginkan, hanya data unik saja yang diambil.

Rumusnya:

=IFERROR(INDEX($B$2:$B$11;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$2:$A$11=$A$14;$B$2:$B$11);$B$2:$B$11;0);"");ROW($B$2:$B$11)-ROW($B$2)+1);ROW($B$2:$B$11)-ROW($B$2)+1);ROWS($A$1:A1)));"")

FILE LATIHAN

Tentang Fungsi RAND dan RANDBETWEEN

Selamat Pagi Semuanya

Maaf sebelumnya jika saya semakin jarang posting atau menulis di blog ini, sekali lagi karena waktu dan kemampuan saya yang terbatas, mohon doanya untuk saya selalu produktif menulis. Tema kali ini adalah mengenai fungsi RAND dan RANDBETWEEN,fungsi ini berguna untuk mengacak angka dan juga bisa mengacak teks. Keperluannya biasanya untuk bidang statistik dan matematika, atau penelitian yang berhubungan dengan pemilihan sampel secara random. Tetapi disini saya mebahasnya dari sisi membuat rumusnya saja ya.

1. Fungsi RAND akan mengembalikan angka desimal secara acak, penulisannya :

Sedangkan Fungsi RANDBETWEEN :

Fungsi RANDBETWEEN ini akan mengembalikan bilangan bulat, pada contoh diatas rumus akan mengembalikan angka 1 sampai 10. Dan bagaimana cara mengacaknya? Cukup tekan tombol F9 saja. Kelemahan fungsi ini angkanya akan selalu berubah tidak bisa di-save, kecuali and copy kemudian di-paste ke cell yang lain kemudian paste as value. Anda juga bisa menerapkan kedua fungsi ini ke dalam beberapa cell. Seperti contoh ini :

Kelemahan yang lain dari RAND dan RANDBETWEEN adalah kemungkinan akan muncul angka yang sama, dengan kasus diatas.

PENERAPAN YANG LAIN:
2. Acak Angka Antara 10 dan 50
    =10+RAND()*40 atau =RANDBETWEEN(10;50)

3. Acak Angka Antara 2 dan 4 (1 desimal)
    =RANDBETWEEN(20;40)/10 atau =(20+INT(RAND()*41))/10

4. Acak Angka Kelipatan 10
    =ROUND(RANDBETWEEN(100;10000)/100;0)*10 atau =RANDBETWEEN(1;100)*10

5. Acak teks
    =CHOOSE(RANDBETWEEN(1;3);"Ayam Goreng";"Soto Kudus";"Rawon Setan")
    atau
    =INDEX({"Ayam Goreng";"Soto Kudus";"Rawon Setan"};RANDBETWEEN(1;3))
    Mohon diperhatikan ya angka 3 pada RANDBETWEEN harus disesuaikan dengan jumlah teksnya

6. Acak Teks Dengan Tabel

rumusnya =INDEX(A23:A27;RANDBETWEEN(1;5))

7. Acak angka Antara 1 Sampai 5 Tanpa Ada Duplikasi

Rumusnya (rumus array) :
=LARGE(ROW($1:$6)*NOT(COUNTIF($A$30:A30;ROW($1:$6)));RANDBETWEEN(1;7-ROW(A1)))

Mohon diperhatikan, pada ROW($1:$6), angka 6 harus disesuaikan dengan angka yang ingin berapa kali ditampilkan, kemudian pada RANDBETWEEN(1:7), angka 7 dari angka 6+1, jadi untuk contoh yang lain jika anda ingin menampilkan angka 10 x nerarti 10+1 = 11, jadi RANDBETWEEN(1;11-ROW($1:$10).

8. Acak Teks Tanpa Ada Duplikasi

Rumus Dengan Tabel (rumus array) :
=INDEX($A$40:$A$44;LARGE(MATCH(ROW($A$40:$A$44);ROW($A$40:$A$44))*NOT(COUNTIF($A$45:A45;$A$40:$A$44));RANDBETWEEN(1;ROWS($A$40:$A$44)-ROW(A1)+1)))
Rumus Tanpa Tabel:
=INDEX({"Fitzgerald";"Robert";"Caroline";"Patrick";"Mark"};LARGE(MATCH(ROW($A$7:$A$11);ROW($A$7:$A$11))*NOT(COUNTIF($C$45:C45;{"Fitzgerald";"Robert";"Caroline";"Patrick";"Mark"}));RANDBETWEEN(1;ROWS($A$7:$A$11)-ROW(A1)+1)))

9. Membuat ID/PIN dan Password
Nah ini yang menarik, ya membuat password, ya tentu bisa, tetapi sebelum itu coba perhatikan rumus dasar dibawah ini:
a.Menghasilkan Huruf Besar Secara Acak
   Semua Huruf
   =CHAR(RANDBETWEEN(65;90))  atau
   =CHAR(INT(RAND()* 25 + 65))  atau
   =CHAR(65+INT(RAND()*26))
   Hanya Huruf Hidup/Vokal
   =CHAR(CHOOSE(RANDBETWEEN(1;5);65;69;73;79;85))

b.Menghasilkan Huruf Kecil Secara Acak
   Semua Huruf
   =CHAR(RANDBETWEEN(97;122))  atau
   =LOWER(CHAR(INT(RAND()*25+65)))  atau
   =LOWER(CHAR(65+INT(RAND()*26)))
   Hanya Huruf Hidup/Vokal
   =LOWER(CHAR(CHOOSE(RANDBETWEEN(1;5);65;69;73;79;85)))

c.Menghasilkan Karakter Simbol Secara Acak
   =CHAR(RANDBETWEEN(33;47))

d.Menghasilkan Semua Huruf dan Simbol Secara Acak
=CHAR(TRUNC(RAND()*90+33))

e.Membuat ID/PIN atau Password Secara Acak

Rumus 1:
=RANDBETWEEN(0;5)&CHAR(45)&RANDBETWEEN(10;100)&CHAR(45)&RANDBETWEEN(101;1000)
Rumus 2:
=CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(1;10000)
Rumus 3:
=RANDBETWEEN(1;2000)&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))
Rumus 4:
=RANDBETWEEN(0;9)&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(33;47))&RANDBETWEEN(0;9)&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(33;47))

 Sekian dari saya, Wass.Wr.Wb.

File LATIHAN

Pengulangan Data (Repeated Cell Contents)

Happy weekend.....
Salam semuanya, di hari minggu yng cerah ini saya akan memberikan materi berupa mengulang data atau values sesuai kriteria, supaya lebih jelas silahkan diikuti tulisan ini.

Kasus A

Kemudian hasil yang diinginkan untuk pengulangan data seperti ini :

Rumus untuk seluruh data "Nama Buah" diulang 3 kali (sesuai kriteria pada cell C2:
=IF(ROWS(D$2:D2)>$C$2*ROWS($A$3:$A$6);"";INDEX($A$3:$A$6;MOD(ROWS(D$2:D2)-1;ROWS($A$3:$A$6))+1))

Rumus untuk masing2x nama buah diulang 3 kali:
=IFERROR(INDEX($A$3:$A$6;ROUNDUP(ROWS(A$1:A1)/$C$2;0));"")

Kasus B

Kasus B berbeda tetapi mirip, hanya kriteria ada di kolom B dan bervariasi angkanya, dan hasil yang diinginkan

Rumusnya
=LOOKUP(ROW(A1);SUMIF(OFFSET(B$1;;;ROW($1:$99););"<>")+1;A$2:A$99)


KASUS C

Kasus ketiga ini tabelnya mempunyai area baris yang kosong, well tidak bisa pake rumus yg diatas karena blank rows ini harus dikondisikan tidak berpengaruh kepada hasilnya nanti, hasil yang ingin ditampilkan seperti ini

so rumus seperti ini
1. Rumus untuk seluruh data "Nama" diulang 3 kali (sesuai kriteria pada cell B2 (rumus array): =IF(ROWS(A$1:A1)>$B$2*COUNTIF($A$2:$A$11;"="&"?*");"";INDEX($A$2:$A$11;SMALL(IF($A$2:$A$11<>"";ROW($A$2:$A$11)-ROW($A$2)+1);MOD(ROWS(A$1:A1)-1;COUNTIF($A$2:$A$11;"="&"?*"))+1)))
2. Rumus untuk masing2x nama diulang 3 kali (rumus array):
=IFERROR(INDEX($A$2:$A$11;SMALL(IF($A$2:$A$11<>"";ROW($A$2:$A$11)-ROW($A$2)+1);ROUNDUP(ROWS(A$1:A1)/$B$2;0)));"")

Happy day to all of you, here you go to file link download

Sort by Sales (Mengurutkan Berdasarkan Penjualan)

Hi readers tercinta.................. lama tak sapa anda, maaf ya, posting saya hari ini masih mengupas tentang penjualan/sales, hanya kali ini saya coba untuk mengurutkan data penjualan dari yang terbesar ke yang terkecil, kebetulan saya ada 2 kasus yang mudah2x-an menarik para pembaca.

KASUS A

Rumus untuk Nama yang berurutan berdasar total sales terbesar (letakkan di cell A12 sesuai gambar) :
=IFERROR(INDEX($A$2:$A$8;MATCH(1;INDEX(($B$2:$B$8=LARGE($B$2:$B$8;ROWS($A$11:A11)))*(COUNTIF($A$11:A11;$A$2:$A$8)=0););0));"")

Rumus untuk Total Sales  (B12) :
=IFERROR(VLOOKUP($A12;$A$2:$B$8;2;FALSE);"")

2 rumus diatas bukan rumus array ya, rumus reguler aja.............

KASUS B

Nah yang ini agak berbeda, kita coba untuk sort berdasarkan rekap penjualan contoh diatas misalnya selama 1 hari
Rumusnya:
=IFERROR(INDEX(CHOOSE({2\1};SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8);$A$2:$A$8);MATCH(LARGE(IF(FREQUENCY(MATCH(SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)&$A$2:$A$8;SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)&$A$2:$A$8;);ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1);SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)+ROW($A$2:$A$8)/10000);ROWS(A$1:A1));SUMIF($A$2:$A$8;$A$2:$A$8;$B$2:$B$8)+ROW($A$2:$A$8)/10000;);COLUMNS($A1:A1));"")

rumus ini diletakkan pada cell A12 dan jangan lupa ini rumus array ya, jadi setelah diletakkan pada cell A12 kemudian tekan tombol F2 supaya terlihat isi rumusnya kemudian tekan tombol CTRL+SHIFT+ENTER secara bersamaan, kalau berhasil akan muncul tanda kurung kurawal seperti ini {"rumus"}, setelah itu bisa dicopy kebawah dan kesamping.

BONUS KASUS

Nah anda punya data penjualan seperti diatas dan disuruh bos anda membuat laporan penjualan dengan format seperti ini:

Mengapa saya 4 warnai? karena rumusnya beda2x.............
1.Rumus "Salesman+Produk" (rumus array)
=IFERROR(INDEX($B$3:$B$12&"-"&$C$3:$C$12;SMALL(IF(FREQUENCY(IF($B$3:$B$12&"-"&$C$3:$C$12<>"";MATCH("~"&$B$3:$B$12&"-"&$C$3:$C$12;$B$3:$B$12&"-"&$C$3:$C$12&"";0));ROW($B$3:$B$12)-ROW($B$3)+1);ROW($B$3:$B$12)-ROW($B$3)+1);ROWS(A$16:A16)));"")

2. Rumus  "Berapa kali jualan" (rumus reguler)
=IF(SUMPRODUCT(--($A16=$B$3:$B$12&"-"&$C$3:$C$12))=0;"";SUMPRODUCT(--($A16=$B$3:$B$12&"-"&$C$3:$C$12)))

3. Rumus "Rincian Sales" (rumus array)
=IFERROR(INDEX($D$3:$D$12;SMALL(IF($B$3:$B$12&"-"&$C$3:$C$12<>"";IF($B$3:$B$12&"-"&$C$3:$C$12=$A16;ROW($A$3:$A$12)-ROW($A$3)+1));COLUMNS($D16:D16)));"")

4. Rumus "Total" (rumus reguler)
=IF(SUM(D16:F16)=0;"";SUM(D16:F16))

Best regards
File Latihan

Menghitung Data Unik (Count Unique Values)

Judul Inggrisnya "count unique values", ya kali ini saya akan berikan contoh rumus untuk menghitung data yang unik, data yang dihitung banyak mengandung duplikasi. Seperti ini contohnya

Cobalah diperhatikan nama2x yang ada di dalam tabel terdapat banyak pengulangan atau duplikasi, padahal kita ingin menghitung tanpa menghitung duplikatnya. Jadi kalau dihitung data yang benar2x unik adalah 3, yaitu "Sam", "Joko", dan "Halim".
Rumus:
=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7;A2:A7&""))
atau
=SUM(1/COUNTIF(A2:A7;A2:A7)) (array formula)

atau

=SUMPRODUCT(1/COUNTIF(A2:A7;A2:A7))

Kemudian bagaimana menghitung dengan kriteria?

Perhatikan gambar, pada kolom nama dan properti terdapat data dobel/duplikasi, nah kasusnya kita ingin menghitung properti berdasar nama, pada contoh yang dihitung adalah "Yuna" dan hasilnya adalah 2, yaitu "Rumah" dan "Motor".
Rumus:
=SUM(IF(FREQUENCY(IF(A17:A24<>"";IF(A17:A24=A27;MATCH("~"&B17:B24;B17:B24&"";0)));ROW(B17:B24)-ROW(B17)+1);1))
atau
=SUM((A17:A24=A27)*(A17:A24<>"")/COUNTIFS(A17:A24;A17:A24;B17:B24;B17:B24))

Dua rumus ini semuanya array formula (tekan tombol CTRL+SHIFT+ENTER secara bersamaan

atau rumus reguler

=SUM(IF(FREQUENCY(IF((A3:A10=D2)*(B3:B10<>"")*(A3:A10<>""); MATCH(B3:B10;B3:B10;0));ROW(B3:B10)-ROW(A3:A10)+1);1))

Sekian dari saya, salam semuanya.......................

Menampilkan Nama Sheet

Mungkin ada kalanya kita ingin mengetahui nama tab sheet yang terdapat di dalam worksheet tanpa harus repot menggeser ke kiri atau ke kanan nama sheetnya ( biasanya kalau sudah buanyak sheet-nya).

Apa sih nama sheet itu?

Nah ini dia, saya pakai contoh file saya sendiri xixi..... ada "2015", "2016" dan "2017", nah ini lah yang pengen kita tarik menggunakan rumus.

1. Menampilkan letak (path) dari sheet dan nama sheet:

Rumusnya : =CELL("filename";A1)

2. Menampilan letak (path) dari file excel ini:

Rumusnya: =LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1)

3. Menampilkan nama file excel ini:

Rumusnya:
=MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1)
atau
=REPLACE(LEFT(CELL("filename";A1);FIND("]";CELL("filename";A1))-1);1;FIND("[";CELL("filename";A1));"")

4. Menampilkan nama sheet yang aktif::

Rumusnya:
=TRIM(RIGHT(SUBSTITUTE(CELL("filename";A1);"]";REPT(" ";99));99))
atau
=MID(Sheets;FIND("]";Sheets)+1;255)

*Note: Rumus akan bekerja jika workbook/file excel tersebut sudah di save terlebih dahulu, jika belum rumus akan menampilkan #NAME? alias tidak dikenali.

Trik Lainnya, Menampilan List dari semua sheet yang ada:

Langkah sebelum rumus:
1. Klik "Define Name" pada tab formula
2. Ketiklah nama misalnya "Sheets"
3. Copy-lah kode ini ke dalamnya =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
4. Enter "Ok"
5. Kemudian simpanlah file anda sebagai "Excel Macro Enabled Worksheet"

Barulah ketikkan rumus ini:
=IFERROR(INDEX(MID(Sheets;FIND("]";Sheets)+1;255);ROW(A1);1);"")
kemudian copas kebawah sampai hasilnya blank.

FILE LATIHAN