Category Archives: Featured

Mencari Hari Pasaran Jawa Dengan Excel

Hari pasaran merupakan hari dalam kalender jawa dimana dalam satu minggu terdiri dari 5 hari berbeda dengan kalender masehi dimana hari minggu terdapat 7 hari. Urutan hari dalam hari pasaran terdiri dari Pahing, Pon, Wage, Kliwon dan Legi, dengan menggunakan excel kita bisa melakukaan pencarian hari pasaran dengan menggunakan fungsi lookup dan mod. Tentang hari pasaran perhitungan manualnya bisa anda baca di “Blog Spektrum Pemikiranku

Untuk jelasnya perhatikan gambar dibawah ini :

Pada gambar diatas hari pasaran disimpan pada awal hari dengan no index array 0 (bukan 1 !) dimulai dengan nama hari pasaran kliwon. Setelah anda membuat data seperti pada tampilan diatas blok cell A1 sampai dengan F2 dan beri nama dengan nama array_hari menggunakan fasilitas insert name define.

Jika anda ingin mengetahui hari pasaran dan hari kalender masehi , maka cukup memasukan pada kolom tanggal saja, tanggal berapa yang akan dicari hari pasarannya dan hari kalender masehi-nya.

Untuk mengetahui hari kalender masehi (cell B5) gunakan formula :

=TEXT(B4;”ddd”)

Rumus diatas merubah Cell B4 dengan fungsi Text dengan format ddd (coba anda ganti format nya dengan single d atau double d, apa yang terjadi ? )

Sedangkan untuk mencari hari pasaran pada cell B6 adalah :

=HLOOKUP(MOD(B4;5);array_hari;2)

Formula diatas mencari hasil sisa pembagian (modulus) tanggal dengan fungsi mod dimana nilai pembagi nya adalah 5 (sesuai jumlah hari pasaran dalam satu minggu nya) dan hasil pembagi tersebut dicocokan ke tabel array dengan fungsi HLOOKUP.

Sebagai latihan silahkan downoad file kerja artikel ini.

DOWNLOAD

Konversi Tipe Date Ke Text Untuk Program BIO UN SMA/SMK

Contoh kasus kali ini merupakan bentuk bantuan kepada rekan kerja yang bingung mengkonversi tanggal ke format text ketika rekan kerja sedang mengis laporan Biodata Peserta Ujian Nasioan SMK/SMA. Program yang digunakan dikirim langsung oleh pusat dibangun menggunakan visual foxpro.

Ada satu kejanggalan ketika penulis membongkar database dari program BIO UN buatan Puspendik dimana seluruh field tanggal baik tanggal lahir, maupun tanggal lainnya tidak bertipe date tetapi bertipe character dengan lebar 6 character (entah apa maksud nya programmer BIO UN menggunakan tipe ini ?) sehingga ketika seorang siswa lahir tanggal 19/03/1996 maka data diisi ke field tanggal lahir dengan format 190392, perhatikan struktur table dari database Bio UN

Program tersebut menyediakan utilitas impor dari excel masalahnya operator sekolah terlanjur mengetik tanggal lahir dan tanggal lainnya dengan format yang seharusnya yaitu format date, sehingga ketika dilakukan proses impor ke dalam program dari file excel selalu gagal ! Mau tidak mau maka tanggal harus diformat ke tipe data yang diinginkan oleh program yaitu tipe karakter dengan lebar 6, masalahnya excel tidak memiliki fungsi untuk merubah tanggal ke tipe text kalaupun menggunakan fungsi =text(value) maka akan hasilnya tidak berupa tanggal tetapi bilangan integer, perhatikan gambar dibawah bagaimana ketika tanggal dikonversi ke text dengan fungsi =text() pada excel

Sedangkan program Bio UN menginginkan kolom tanggal tampak seperti pada kolom B pada gambar dibawah ini, maka tentu tidak ada fungsi khusus yang bisa melakukan selain kita membuat formula sendiri untuk merubahnya !

Nah bagaimana membuat formulanya ?? berikut penulis ambil contoh untuk merubah tanggal tipe date pada cell A2 menjadi tipe text dengan lebar enam digit pada cell B2

=CONCATENATE((IF(LEN(DAY(A2))=1;CONCATENATE(“0″;DAY(A2));DAY(A2)));(IF(LEN(MONTH(A2))=1;CONCATENATE(“0″;MONTH(A2));MONTH(A2)));(RIGHT(YEAR(A2);2)))

Wow !! panjang ?? bagaimana logikanya ?? berikut penjelasannya :

  1. Formula diatas intinya mengambil tanggal dengan fungsi day, mengambil bulan dengan fungsi month dan mengambil year dengan fungsi year.
  2. Melakukan pengecekan panjang (LEN) karakter hasil fungsi day dan month jika day dan mont panjangnya hanya satu maka akan digabung dengan angka 0 dengan fungsi CONCATENATE jadi ketika bulan januari akan menghasilkan angka 1 dengan fungsi month karena panjangnya hanya 1 karakter maka diawalnya ditambahkan angka 0 dengan fungsi CONCATENATE begitu pula untuk fungsi day !
  3. Pada bagian akhir menggunakan fungsi year hasilnya akan berupa text sebanyak empat digit tahun namun kita ambil digit ke-3 dan ke-4 menggunakan fungsi right !
  4. Hasil fungsi day akan menghasilkan text antara 01 – 12, sedangkan fungsi month akan menghasilkan text antara 01 -31 dan year berupa tahun dengan format 2 digit digabung seluruhnya dengan fungsi CONCATENATE sehingga hasil akhir pada Cell B2 adalah 190380

Untuk bahan latihan silahkan download file contoh artikel ini !

DOWNLOAD

Membuat Fungsi Sendiri Di Excel

Hampir sebagian besar fungsi terdapat dalam excel seperti fungsi logika (if), fungsi lookup, fungsi trigonometri, fungsi tanggal dan fungsi lainnya. Namun ternyata kadang kita memiliki rumus sendiri yang tidak terdapat dalam excel. Sebagai contoh untuk menghitung luas segitiga atau luas persegi panjang atau juga mengkonversi suhu dari celcius ke kelvin. Rumusan-rumusan diatas bisa kita buat dengan formula matematika biasa, namun bisa pula kita buat fungsi sendiri sebagai contoh, ketika kita akan membuat menghitung luas segitiga cara biasa adalah membuat formula sebagai berikut :

Luas Segitiga = 1/2 x alas x tinggi

maka formula di excel

=0.5*10*5

Ada cara lain yaitu dengan membuat fungsi matematika untuk menghitung luas, sehingga untuk menghitungluas kita menggunakan fungsi sebagai berikut (misal)

=luas_segitiga(alas;tinggi)

maka fungsi nya :

=luas_segitiga(10;5)

Nah pada contoh kasus kali ini kita akan membuat fungsi sendiri, dalam contoh kali ini kita akan membuat fungsi untuk
menghitung :
luas segitiga, dengan fungsi luas_segitiga(alas;tinggi)
luas persegi panjang, dengan fungsi luas_persegipanjang(panjang;tinggi)
konversi suhu celcius ke kelvin dengan fungsi celcius_ke_kelvin(celcius)

Pembuatan fungsi sendiri (User Defined Function) dilakukan dengan menggunkan bahasa pemrogaman Visual Basic Appliaction dalam hal ini Macro untuk excel. Adapun langkah-langkahnya adalah :

  1. Buka jendela macro editor dengan menekan tombol ALT+F11
  2. Pada Project browser klik kanan ThisWorksheet klik insert kemudian pilih Module

  3. pada jendela module ketik fungsi fungsi seperti tampak pada gambar berikut

    dari tiga contoh diatas maka format penulisan nya adalah :
    Function nama_fungsi(parameter_1,parameter_2,parameter_n)
    nama_fungsi=formula
    End Function

  4. Tutup jendela macro editor dan kembali ke lembar kerja excel, jika terdapat kotak dialog seperti tampak pada gambar dibawah klik tombol No

  5. Simpan file excel dengan memilih filetype nya Excel 97-2003 workbook (*.xls) atau Excel Macro-enabled workbook (*.xlsm)

  6. Tutup file
  7. Buka kembali, dan aktifkan macro dengan cara menglik tombol option pada section security option diatas formula bar

  8. Pilih enabled this content klik ok

  9. Saatnya menguji apakah fungsi berjalan atau tidak
  10. Buatlah table data seperti gambar dibawah ini, pada Cell D3 untuk luas segitiga panggil fungsi sbb :
    =luas_segitiga(B3;C3) tekan enter maka hasil perhitungan akan ditampilkan.

  11. Pada cell D6 hitung luas persegi panjang, panggil fungi luas_persegi()
    =luas_segitiga(B3;C3) tekan enter maka hasil perhitungan akan ditampilkan

  12. Dan untuk menghitung konversi dari celcius ke kelvin gunakan fungsi celcius_ke_kelvin()
    misal untuk meng-konversi cell B9 ke kelvin
    =celcius_ke_kelvin(B9)

Sebagai contoh silahkan download file contoh diatas !


DOWNLOAD