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