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

Menghitung Kelompok Umur 2

Ass.Wr.Wb

Senang rasanya setiap kali bisa memberikan sesuatu kepada para pembaca sekalian, nah apakah anda pernah membaca posting pertama saya? Disini Menghitung Kelompok Umur , sebetulnya saua mau update postingan tersebut, tetapi saya pikir ulang lebih saya buat yang baru dengan penyempurnaan atau tambahan. Serta untuk mengakomodasi beberapa pertanyaan yang ingin contoh menghitung dengan beberapa kriteria.

Tidak jauh berbeda dengan tabel kasus yang pertama

Menghitung Umur dengan kriteria (Age Grouping)
1. Kurang dari 30 Tahun
2. 30 - 40 Tahun
3. 41 - 50 Tahun
4. Lebih dari 50 Tahun

Kemudian ada tambahan kriteria, yaitu gender grouping, dihitung berdasarkan jenis kelamin Laki-laki dan Perempuan. Nah pembahasannya satu2x dan saya buatkan rumus untuk 3 kasus.  

KASUS 1 (Age Grouping saja):
a. Kurang dari 30 Tahun =COUNT(IF(C3:C12<30;C3:C12))
b. 30 - 40 Tahun =COUNT(IF(C3:C12>=30;IF(C3:C12<=40;C3:C12)))
c. 41 - 50 Tahun =COUNT(IF(C3:C12>=41;IF(C3:C12<=50;C3:C12)))
d. Lebih dari 50 Tahun =COUNT(IF(C3:C12>50;C3:C12))
  
 KASUS 2 (Age dan Gender Grouping):
a. Kurang dari 30 Tahun
=COUNT(IF(C3:C12<30;IF(B3:B12="Laki-laki";C3:C12)))
=COUNT(IF(C3:C12<30;IF(B3:B12="Perempuan";C3:C12)))
b. 30 - 40 Tahun
=COUNT(IF(C3:C12>=30;IF(C3:C12<=40;IF(B3:B12="Laki-laki";C3:C12))))
=COUNT(IF(C3:C12>=30;IF(C3:C12<=40;IF(B3:B12="Perempuan";C3:C12))))
c. 41 - 50 Tahun
=COUNT(IF(C3:C12>=41;IF(C3:C12<=50;IF(B3:B12="Laki-laki";C3:C12))))
=COUNT(IF(C3:C12>=41;IF(C3:C12<=50;IF(B3:B12="Perempuan";C3:C12))))
d. Lebih dari 50 Tahun
=COUNT(IF(C3:C12>50;IF(B3:B12="Laki-laki";C3:C12)))
=COUNT(IF(C3:C12>50;IF(B3:B12="Perempuan";C3:C12)))

Nah rumus yang saya tampilkan disini adalah rumus array (harus tekan tombol CTRL+SHIFT+ENTER secara bersamaan), tetapi jangan kuatir, pada file latihan saya, ada banyak contoh rumus menggunakan SUM+IF, SUMPRODUCT dan COUNTIFS.

KASUS 3:

Nah yang ini kita ingin mengelompokkan umur berdasarkan kriteria yang kita buat, hasil atau rumus yang ingin dibuat adalah pada kolom warna kuning (Kolom D).
Rumus:
=IF(OR(C3="";ISTEXT(C3));"";LOOKUP(C3;{0;30;41;51};{"<30 Tahun";"30-40 Tahun";"41-50 Tahun";">50 Tahun"}))

FILE LATIHAN

KONVERSI HIJRIYAH KE MASEHI DAN SEBALIKNYA

Halo and Greetings guys........................

Saya berusaha membuat konverter tanggal hijriyah ke masehi dengan mencari informasi di internet mengenai cara perhitungannya, tetapi selalu gagal, karena banyak penjelasan yang kurang lengkap dan memadai. Kemudian saya menemukan disini:

repo.unnes.ac.id dan dibuat oleh Andi Pangerang, beliau memberikan step by step kalkulasinya, salut kepada bapak Andi Pangerang. Kemudian saya mencoba meringkasnya tanpa menggunakan banyak tabel lookup, tetapi berdampak rumusnya panjang.. yah ndak papalah. Saya juga membuatkan konversi dari Masehi ke Hijriyah dengan memanfaatkan fasilitas dari MS Excel.

Inilah image dari konverter tersebut:
1. KONVERSI HIJRIYAH KE MASEHI

2. KONVERSI MASEHI KE HIJRIYAH

Silahkan anda DOWNLOAD FILE

Deal with Dates Before 1900 (Menghitung Umur Sebelum Tahun 1900)

Ass.Wr.Wb.

Salam semuanya, tulisan saya kali ini masih berkaitan dengan tanggal, mudah2xan tidak membuat bosan :) , belum lama ini saya mencoba untuk mencari cara untuk membuat rumus menghitung umur di postingan saya di "menghitung umur tanpa fungsi datedif", bisa bekerja untuk kalkulasi tahun sebelum 1900. Entah kenapa Microsoft tidak mendukungnya di MS Excel, pasti error jika menggunakan rumus. Saya coba cek tanggal yang seperti ini misal 8/5/1456 , saya test menggunakan =ISNUMBER

dan hasilnya:

Ya hasilnya FALSE, berarti MS Excel tidak mengenali tanggal tersebut karena tahunnya sebelum 1900, dengan kata lain anda mengetikkan tanggal "1456" maka tanggal tersebut terformat sebagai text bukan angka. Saya coba eksperimen selalu gagal akhirnya ketemu, yaitu dengan menambahkan angka tahun dengan jumlah angka tertentu supaya ter-konversi menjadi angka. Saya coba angka2x kecil +200, +300, +400, tetapi belum benar hasilnya. Akhirnya ketemu angka 7000, saya coba tambahkan angka diatas itu sudah tidak bisa alias error. Nah kita buka rumusnya dengan 2 kasus. Saya memakai contoh menghitung umur dari tanggal lahir (start date) dan meninggal (end date), anda bisa menggunakan istilah lain, mungkin end date bisa diganti dengan =TODAY untuk menghitung umur sampai hari ini

Kasus 1

Mungkin tidak semua orang butuh ya, kenapa harus menghitung umur pada abad 1? Ya untuk membuktikan rumus berhasil. Dan rumus ini juga bisa digunakan untuk tahun setelah 1900.
Rumus:
=DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"y")&" Tahun"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM")-1)&" Bulan"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")+31)&" Hari"

Kasus 2

Untuk kasus ke-2 ini rumusnya sangat puanjaang...... sehingga tidak saya tulis disini, tetapi saya sediakan di file exercise, rumusnya mudah2xan akurat, bisa digunakan untuk semua tahun ya, baik sebelum maupun setelah tahun 1900.

FILE LATIHAN

Merubah Desimal ke Tahun dan Bulan

Selamat pagi readers...bahagia bisa posting lagi, kali ini tentang desimal untuk di konversi ke "Tahun" dan "Bulan". Misal seperti ini di cell A1=1, maka Excel menerjemahkannya sebagai 1 Tahun, kemudian 1,5 diterjemahkan menjadi 1 Tahun 6 Bulan.

Langkahnya:
A. TAHUN
Anda bisa menggunakan fungsi INT atau TRUNC untuk mengambil tahun, ke-2 fungsi ini mengambil angka di depan koma pada desimal. Kasusnya dan langkah penyelesaiannya silahkan diikuti dibawah ini:

Asumsi di cell A1 ada angka 1,5.
a.=INT(A1) atau =TRUNC(A1;0)
b. Tambahkan dengan teks "Tahun", menjadi =INT(A1)&" Tahun" atau =TRUNC(A1;0)&" Tahun ", ada spasi ya diantara kata "Tahun".
c. Untuk menghindari muncul "0 Tahun" jika kriteria di A1 dibawah 1, misal 0,5, maka "0 Tahun" ditampilkan sebagai blank atau teks kosong, secara rumus: =IF(INT(A1);INT(A1)&" Tahun";"") atau =IF(TRUNC(A1;0);TRUNC(A1;0)&" Tahun ";"")

B. BULAN
 Nah untuk bulan, angka yang diambil dari desimal adalah angka yang dibelakang koma,
a. Rumusnya =INT((A1-INT(A1))*12) atau =TRUNC((A1-INT(A1))*12), hasilnya "6", artinya 6 Bulan, silahkan ditambahkan saja dengan teks "Bulan" dibelakangnya.
b. Ditambahkan kondisi menampilkan teks kosong atau blank jika krierianya bukan desimal, misal angka 1,2 dst. Rumusnya menjadi =IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"") atau =IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Nah semuanya ketemu baik "Tahun" dan "Bulan", finalisasi rumus menjadi:
1. =IF(INT(A1);INT(A1)&" tahun ";"")&IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"")
2. =IF(TRUNC(A1;0);TRUNC(A1;0)&" tahun ";"")&IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Sekian dari saya
Salam semuanya
Rizky

Mengetahui Zodiak, Weton Dan Shio Dari Tanggal

Ass.Wr.Wb.

Salam semuanya dan senang menjumpai anda semuanya kali ini, tulisan saya sekarang ini sebetulnya perbaikan postingan saya yang berjudul "Kumpulan Trik Rumus untuk Tanggal", jadi saya hapus dan digantingan dengan perbaikan serta tambahan di tulisan ini.

Postingan membahas tentang mengetahui zodiak/bintang, weton dan shio dari tanggal lahir anda, hanya sebatas untuk pengetahuan saja, tidak usah mempercayai ramalannya ya...
Tidak lupa juga rumus untuk konversi tanggal Masehi ke tanggal Hijriyah.

1. ZODIAK

Hasil yang diinginkan

Rumus yang menggunakan tabel data zodiak ada 2:
1. =IF(E2="";"-";INDEX($A$4:$A$15;MATCH(1;IF((DATE(2000;MONTH(E2);DAY(E2))>=  DATE(2000;MONTH($B$4:$B$15);DAY($B$4:$B$15)));1;0);1)))
2. =IF(E2="";"-";INDEX(A$3:A$15;MATCH(1;IF(--TEXT(E2;"dmmm")>=--TEXT(B$3:B$15; "dmmm");IF(--TEXT(E2;"dmmm")<=--TEXT(C$3:C$13;"dmmm");1));1)))

Semuanya formula array, jadi jangan lupa tekan tombol CTRL+SHIFT+ENTER bersamaan.

Rumus tanpa menggunakan tabel ada 2 juga:
1. =IF(E7="";"-";LOOKUP(--TEXT(E7;"mdd");{101;121;220;321;421;521;622;723;824;923;1023;1123;1222};{"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo";"Virgo";"Libra" ;"Scorpio";"Sagittarius";"Capricorn"}))
2. =IF(E7="";"-";INDEX({"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo" ;"Virgo";"Libra";"Scorpio";"Sagittarius";"Capricorn"};MATCH(MONTH(E7)*100+DAY(E7);{0;121 ;220;321;421;522;622;723;823;924;1024;1123;1222})))
Wah panjangnya.....

2. WETON

Weton adalah perhitungan jawa untuk pernikahan atau nasib berdasar hari kelahirannya dan merupakan peninggalan budaya jawa.

Rumus menggunakan tabel ada 2:
1. =VLOOKUP(MOD(E2;7);A3:B9;2) &" " &VLOOKUP(MOD(E2;5);A3:C9;3)
 2. =INDEX(B3:B9;MATCH(MOD(E2;7);A3:A9;0))&" "&INDEX(C3:C7;MATCH(MOD(E2;5); A3:A7;0))

Rumus tanpa tabel lookup ada 2:
1. =TEXT(E7;"dddd")&" "&CHOOSE(MOD(E7;5)+1;"Kliwon";"Legi";"Pahing";"Pon";"Wage")
2. =TEXT(WEEKDAY(E7);"dddd")&" "&CHOOSE({1\2\3\4\5};INDEX({"Kliwon";"Legi";"Pahing" ;"Pon";"Wage"};MOD(E7;5)+1))

3. SHIO

Rumus menggunakan tabel lookup:
1. Shio =IF(D10="";"-";VLOOKUP(MOD((YEAR(D10)-4);12);A3:B14;2))
2. Elemen =IF(D10="";"-";VLOOKUP(INT(MOD((YEAR(D10)-4);10)/2);D3:E7;2))

Rumus tanpa tabel lookup:
1. Shio
=IF(D13="";"";INDEX({"Tikus";"Kerbau";"Macan";"Kelinci";"Naga";"Ular";"Kuda";"Kambing"; "Monyet";"Ayam";"Anjing";"Babi"};MATCH(MOD((YEAR(D13)-4);12);({0;1;2;3;4;5;6;7;8;9;10;11}) ;0)))
2. Elemen  
=IF(D13="";"";LOOKUP(INT(MOD((YEAR(D13)-4);10)/2);{0;1;2;3;4;5;6;7;8;9;10;11};{"Kayu";"Api" ;"Tanah";"Logam";"Air"}))

4. KONVERSI TANGGAL MASEHI KE HIJRIYAH

Sebetulnya pada Excel sudah dibekali kode untuk konversi tanggal masehi atau gregorian date ke hijriyah yaitu "b2" yang digabungkan dengan Fungsi TEXT. Tetapi tidak ada salahnya saya memberikan contohkan dengan menggunakan tabel.

Rumus dengan tabel lookup:
=TEXT(C2-1;"b2d")&" "&INDEX(A2:A13;TEXT(C2-1;"b2m"))&" "&TEXT(C2-1;"b2yyyy")

Rumus tanpa tabel, ada 2:
1. =TEXT(C5-1;"b2d ")&INDEX({"Muharram";"Shafar";"Rabi'ul Awwal";"Rabi’ul Akhir";"Jumadil Awwal";"Jumadil Akhir";"Rajab";"Sya'ban";"Ramadhan";"Syawwal";"Dzulqa'dah";"Dzulhijjah"} ;TEXT(C5-1;"b2m"))&" "&TEXT(C5-1;"b2yyyy")
2. =TEXT(C5-1;"b2mmm d"&" ,yyy")

Sudah capek? beluuum dong...... pengennya nambahin lagi, ada tambahan trik rumus untuk tanggal, tetapi tidak saya tulis disini tetapi di FILE LATIHAN, silahkan di download dan di pelajari. Mudah2xan ada manfaatnya.

Wass.Wr.Wb.

Download File Latihan