Thursday, June 6, 2013

Fungsi untuk Penomoran Otomatis

Pada posting kali ini kita akan belajar memberikan nomor otomatis ketika kita menuliskan sesuatu. Jika tidak ada isinya, maka tidak akan diberikan nomor. Hal sederhana ini biasanya kita lakukan dengan melalui autofill, tetapi hal ini akan memberikan nomor urut otomatis baik ada maupun tidak ada masukan.

Untuk memudahkan pemahaman, lebih baik langsung kita coba saja. Di sini akan kita gabungkan dua fungsi, yaitu fungsi IF dan fungsi COUNTA. Fungsi IF bertujuan untuk merumuskan pengandaian, sedangkan fungsi COUNTA berfungsi untuk menghitung sel yang ada isinya.

Berikut adalah screenshot-nya:

Sehingga dengan menerapkan fungsi tersebut dapat memudahkan kita untuk secara otomatis menghitung berapa banyak karyawan yang aktif dan yang tidak aktif. Bayangkan saja jika data kita ada ratusan atau bahkan ribuan, jika kita hitung secara manual akan memperbesar kemungkinan terjadinya salah hitung.

Semoga bermanfaat.

Saturday, July 21, 2012

Fungsi Tanggal Tersembunyi


Pada postingan kali ini kita akan mencoba bermain-main dengan fungsi waktu di Excel. Rupanya selain fungsi-fungsi yang ada dalam daftar, Excel juga "menyembunyikan" fungsi-fungsi lain yang bisa kita optimalkan. Salah satu fungsi tersebut yang akan kita coba disini adalah fungsi "=DATEDIF".

Adapun syntax dari fungsi tersebut adalah sebagai berikut:
=DATEDIF(date1,date2,interval)

Dan untuk melengkapi fungsi tersebut, disini saya mencoba mengkombinasikan dengan fungsi "=TEXT" dengan syntax sebagai berikut:
=TEXT(value,format_text)

Baiklah, tanpa berlama-lama untuk memudahkan pemahaman kita, langsung saja kita coba.
Mari kita lihat screenshot dibawah ini.



Langkah-langkahnya adalah:
1.   tuliskan tanggal awal di cell C3
2.   tuliskan fungsi =TEXT di cell B3 dengan syntax sebagai berikut:
=TEXT(C3,”dddd”)

Format text yang bisa kita inputkan diantaranya:
  • “dd” akan menghasilkan tanggal (hasil: 30)
  • “dddd” menghasilkan hari (hasil: Tuesday, tergantung format tanggal di computer Anda)
  • “mm” akan menghasilkan angka bulan (hasil: 4)
  • “mmmm” akan menghasilkan nama bulan (hasil: April)
  • “yy” akan menghasilkan dua digit tahun (hasil: 74)
  • “yyyy” akan menghasilkan empat digit angka tahun (hasil: 1974)

3.   tuliskan tanggal akhir di cell E3, disini saya menggunakan fungsi =NOW dengan syntax sebagai berikut:
=NOW()
catatan: isian di cell tersebut akan selalu berubah tiap hari
4.   tuliskan fungsi =DATEDIF di cell F3, H3 dan J3 dengan syntax sebagai berikut:
=DATEDIF(B3,B4,”Y”) à cell F3
=DATEDIF(B3,B4,”YM”) à cell H3
=DATEDIF(B3,B4,”MD”) à cell J3

Variasi untuk interval yang ada yaitu:
  • “D” menghasilkan selisih jumlah hari
  • “M” menghasilkan selisih jumlah bulan
  • “Y” menghasilkan selisih jumlah tahun
  • “YM” menghasilkan sisa bulan saja tanpa menuliskan selisih jumlah tahunnya
  • “YD” menghasilkan selisih jumlah hari dari sisa selisih tahunnya
  • ”MD” mengasilkan selisih hari diluar selisih bulannya

Semoga bermanfaat… minimal sebagai catatan pribadi… hehehe.. :-) 

Tuesday, March 8, 2011

Fungsi Makro Menghurufkan Angka

Fungsi lain yang disediakan oleh MS Excel untuk kita yang ingin lebih mengoptimalkan Excel adalah dengan membangun fungsi sendiri. Terkadang kita menemukan masalah yang tidak bisa diselesaikan dengan fungsi-fungsi yang sudah disediakan oleh Excel. Salah satunya adalah jika kita bermaksud untuk menghurufkan angka seperti dalam lembar kuitansi.

Setelah kita menuliskan angka tertentu, dalam lembar kuitansi atau invoice suatu transaksi biasanya akan disertai dengan sebutannya dalam tulisan (terbilang). Fungsi ini tentunya tidak disediakan oleh Excel, karena kita bermaksud untuk menuliskannya dalam bahasa Indonesia.

Oleh karena itu, kita perlu membangun fungsi tersebut sendiri. Menu yang dapat kita gunakan adalah fasilitas VBA (Visual Basic Application) yang sudah ada di Excel. Berikut adalah listingnya:

Dim Huruf(0 To 9) As String
Dim ax(0 To 3) As Double

Function INIT_angka()
Huruf(0) = ""
Huruf(1) = "Satu "
Huruf(2) = "Dua "
Huruf(3) = "Tiga "
Huruf(4) = "Empat "
Huruf(5) = "Lima "
Huruf(6) = "Enam "
Huruf(7) = "Tujuh "
Huruf(8) = "Delapan "
Huruf(9) = "Sembilan "
End Function

Function dgratus(angka As Double) As String
 Temp = ""
 INIT_angka
 panjang = Len(Trim(Str(angka)))
 nilai = Right("000", 3 - panjang) + Trim(Str(angka))
 For y = 3 To 1 Step -1
  ax(y) = Mid(nilai, y, 1)
 Next y
 Select Case ax(1)
  Case Is = 1
   Temp = "seratus "
  Case Is > 1
   Temp = Huruf(Val(ax(1))) + "" + "ratus "
  Case Else
   Temp = ""
  End Select

 Select Case ax(2)
  Case Is = 0
   Temp = Temp + Huruf(Val(ax(3)))
  Case Is = 1
 Select Case ax(3)
  Case Is = 1
   Temp = Temp + "sebelas "
  Case Is = 0
   Temp = Temp + "sepuluh "
  Case Else
   Temp = Temp + Huruf(Val(ax(3))) + "belas "
 End Select

 Case Is > 1
  Temp = Temp + Huruf(Val(ax(2))) + "puluh "
  Temp = Temp + " " + Huruf(Val(ax(3)))
 End Select
dgratus = Temp

End Function

Function dghuruf(angka As Double) As String
 Dim ratusan(0 To 6) As String
 Dim sebut(0 To 4) As String
 sebut(1) = " Ribu "
 sebut(2) = " Juta "
 sebut(3) = " Milyar "
 sebut(4) = " Trilyun "
 panjang = Len(Trim(Str(angka)))
 kali = Int(panjang / 3)
 If Int(panjang / 3) * 3 <> panjang Then
  kali = kali + 1
  sisa = panjang - Int(panjang / 3) * 3
  nilai = Right("000", 3 - sisa) + Trim(Str(angka))
 Else
  nilai = Trim(Str(angka))
 End If

 For x = 0 To kali
  ratusan(kali - x) = Mid(nilai, x * 3 + 1, 3)
 Next x

 For y = kali To 1 Step -1
  If y = 2 And Val(ratusan(y)) = 1 Then
   Temp = Temp + "seribu "
  Else
   If Val(ratusan(y)) = 0 Then
      Temp = Temp
   Else
    Temp = Temp + dgratus(Val(ratusan(y)))
    Temp = Temp + sebut(y - 1)
   End If
  End If
 Next y
 dghuruf = Temp
End Function



Berikut adalah screeshot-nya:


Selamat mencoba.

Sunday, March 6, 2011

Simulasi dengan Tombol Combo

Hal yang kadang kala kita jumpai dalam oleh data menggunakan MS Excel diantaranya adalah untuk melakukan simulasi data. Terdapat sekumpulan data yang terdiri dari beberapa set, kemudian kita diminta untuk melakukan simulasi skenario. Bagaimana hasilnya jika input diubah-ubah, tentunya akan mempengaruhi outputnya.

Semisal, kita diminta untuk menunjukkan hasil jika kita akan melakukan pengumpulan massa di tiga kota berbeda dengan ketentuan biayanya masing-masing. Bagaimana jika pertemuan massal tersebut dilakukan di Jakarta, Bandung dan Medan. Mana yang lebih efisien dari segi biayanya?

Berikut adalah contohnya:
Sebagaimana terlihat pada screenshoot di atas, kita dapat menggunakan fasilitas Developer untuk membuat pilihan tombol radio (combo). Jika kita pilih pilihan Jakarta, hasil perhitungannya akan langsung terlihat di sel G22. Demikian pula jika kita ganti pilihannya ke Bandung maupun Medan.

Langkahnya pada dasarnya terdiri dari tiga tahapan, yang pertama adalah pembuatan tombol radio (pilihan Jakarta, Bandung, Medan) dan langkah kedua adalah menyusun rumus/fungsi IF, serta langkah terakhir adalah membuat rumus SUM.

Langkah pertama pembuatan tombol radio dapat kita lakukan dengan mengklik ribbon/menu Developer, pilih Insert dan memilih dan menyeret/drag Option Button ke sel B3 lalu copy-kan ke B4 dan B5. Lalu namai masing-masing button tersebut dengan Jakarta, Bandung, dan Medan.

Langkah berikutnya adalah menuliskan fungsi IF sebagai berikut:
=IF($C$8=$C$3,HLOOKUP(D9,$D$2:$I$5,2,FALSE),IF($C$8=$C$4,HLOOKUP(D9,$D$2:$I$5,3,FALSE),HLOOKUP(D9,$D$2:$I$5,4,FALSE)))
Lalu copy-kan fungsi tersebut ke sel-sel berikutnya.

Langkah terakhir adalah menjumlah (fungsi SUM) dan merapikan tampilannya.
Silakan Anda ubah-ubah pilihannya dari Jakarta, Bandung maupun Medan, hasilnya akan langsung berubah.

Sunday, November 21, 2010

Fungsi SUMIF

Salah satu bentuk lain dari fungsi IF adalah fungsi SUMIF, yaitu suatu fungsi yang digunakan untuk menjumlah beberapa angka jika memenuhi kriteria tertentu yang ditetapkan. Contoh mudahnya dalam kehidupan sehari-hari adalah penjumlahan berat badan anak-anak yang berumur antara 7 - 10 tahun. Atau jumlahkan total komisi dari penjualan di atas Rp 3.000.000,-. Atau jumlahkan total penjualan oleh Amir dalam bulan ini.














Berikut adalah contohnya:
Kita memiliki 3 orang salesman, yaitu Amir, Budi dan Choki yang masing-masing bertugas untuk melakukan penjualan ke warung-warung di seputar kompleks. Hasil penjualan tersebut dikalkukasi setiap minggu untuk mengetahui perolehan penjualan oleh masing-masing salesman.


Fungsi yang dapat kita gunakan adalah:
=SUMIF($R$3:$R$17,T2,$S$3:$S$17)
Jika sel rujukan (R3-R17) sama dengan sel ketentuan (T2:Amir/U2:Budi/V2:Choki), maka Jumlahkan sel tujuan (S3-S17).
Catatan: Sel rujukan dan sel tujuan perlu kita berikan tanda mutlak ($) agar sel tersebut tidak mengalami perubahan ketika fungsi/rumus tersebut kita copy-kan ke kanan/sel lain.

Wednesday, November 17, 2010

Fungsi IF Bertingkat

Setelah kita mengenal penggunaan fungsi IF secara tunggal, sebenarnya fungsi tersebut dapat kita maksimalkan dengan menggunakannya secara bertingkat. Artinya, tidak saja satu fungsi IF yang kita gunakan, tetapi beberapa fungsi IF dalam sekali penggunaan.


Sebagai contoh dalam kahidupan sehari-hari adalah, jika cuaca di kota hujan deras maka kemungkinan lalu lintas akan macet, jika hujan biasa kemungkinan akan padat merayap, jika mendung kemungkinan lalu lintas akan mengalir perlahan, sedangkan jika cuaca cerah maka lalu lintas akan lancar.












Fungsi yang dapat kita gunakan adalah:
=IF(D22=$G$20,$H$20,IF(D22=$G$21,$H$21,IF(D22=$G$22,$H$22,$H$23)))


Sebagai contoh kedua, misalnya dalam pengelompokan Nilai Hasil Ujian. Setiap nilai mahasiswa pada ujian mata kuliah akan dicantumkan dalam kelompok Nilai Huruf, yaitu nilai A, B, C, D dan E. Ketentuan pengelompokan huruf nilai ujian tersebut adalah sebagaimana terlihat pada gambar berikut:












Fungsi yang dapat kita gunakan adalah:
=IF(AND(D5>$G$7,D5<$H$5),$I$6,IF(AND(D5>$G$6,D5<$H$4),$I$5,IF(AND(D5>$G$5,D5<$H$3),$I$4,IF(D5>$G$4,$I$3,$I$7))))


Selain dengan menggunakan urutan menaik, yaitu jika nilainya antara 20-40, maka hurufnya D, jika antara 40-60 hurufnya C, jika antara 60-80 hurufnya B, jika antara 80-100 hurufnya A, jika selainnya (kurang dari 20) hurufnya E.
Pernyataan untuk menyusun fungsi tersebut juga dapat kita susun secara menurun, yaitu jika nilainya antara 100-80 hurufnya A, jika antara 80-60 hurufnya B, jika antara 60-40 hurufnya C, jika antara 40-20 hurufnya D, dan sisanya berhuruf E.
Fungsi yang dapat kita tuliskan adalah:
=IF(AND(M8<=$G$3,M8>=$H$3),$I$3,IF(AND(M8<=$G$4,M8>=$H$4),$I$4,IF(AND(M8<=$G$5,M8>=$H$5),$I$5,IF(AND(M8<=$G$6,M8>=$H$6),$I$6,$I$7))))












Dalam fungsi-fungsi diatas, kita menggunakan fungsi selain IF yaitu fungsi AND. Kita akan bahas pada tulisan berikutnya.
Selamat mencoba...

Tuesday, November 16, 2010

Modifikasi Fungsi IF (Tunggal)

Pada postingan kali ini, saya masih menggunakan fungsi IF tapi dengan sedikit modifikasi. Pada contoh-contoh sebelumnya kita memasukkan nilai jika benar maupun salah secara manual. Hal ini mungkin tidak akan jadi masalah ketika jumlah data (data set) yang kita miliki masih terbatas atau tidak banyak. Namun, kita akan mengalami kesulitan seandainya jumlah data yang ada mencapai ratusan atau bahkan ribuan.
Jika jumlah data sangat banyak dan terjadi perubahan pada prasyarat maupun pernyataan hasil, maka kita akan kerepotan melakukan penyesuaian. Sedikit perubahan pada prasyarat maupun nilai/pernyataan hasil mengharuskan kita untuk mengubahnya satu per satu. Tentunya bukan pekerjaan yang efisien, bukan?
Nah, hal ini akan dapat diatasi seandainya kita menggunakan sel rujukan. Sehingga perubahan cukup kita lakukan pada sel rujukan, dan secara otomatis blok sel hasil akan menyesuaikan dengan perubahan di sel rujukan.

Untuk mudahnya, mari kita andaikan kita memiliki data nilai mahasiswa sebanyak 500 orang. Kita sudah melakukan input nilai hasil ujiannya, dan dosen pengampu menyatakan bahwa nilai minimal kelulusan adalah 55. Dengan mudah kita dapat mengaplikasikan fungsi IF sederhana sebagaimana contoh sebelumnya. Cukup bikin satu fungsi IF dan copy-kan kebawah sebanyak 500 sel. Tidak sampai satu jam kita sudah bisa menyajikan hasilnya, berapa banyak mahasiswa yang Lulus dan yang harus Mengulang.

Mendadak, sehari sebelum pengumuman, dosen tersebut mengubah keputusannya. Bahwa nilai minimal kelulusan dinaikkan menjadi 60 dan keterangannya adalah Lulus dan Tidak Lulus. Bayangkan jika kita harus mengubahnya secara manual sebanyak 500 data...wah, mata bisa jadi keriting, tuch... hehehe.. :-)

Untungnya kita telah memanfaatkan sel referensi, sehingga perubahan tersebut dapat kita penuhi dalam waktu singkat...nggak sampai 5 menit, dech...




Penjelasan Gambar:
Kolom B adalah Nomor Urut
Kolom C adalah Nama Mahasiswa
Kolom D adalah Nilai Ujian
Kolom E adalah Hasil Ujian
=IF(D12>=$J$2, $L$11, $L$12)
Karena kita hanya akan membuat satu fungsi dan kita copy-kan ke bawah, maka sel-sel rujukan kita berikan harga mutlak yaitu diberikan tanda $ (contoh: $J$2) sehingga baik kolom J maupun baris nomor 2 tidak akan berubah saat kita copy-kan ke bawah.
Sehingga dengan menggunakan sel referensi tersebut, perubahan cukup kita lakukan di sel referensi, yaitu di drop-down sel J2 untuk perubahan Nilai Minimal Kelulusan dan sel L11 dan L12 untuk Kriteria Kelulusan.



Catatan:
Untuk membuat drop-down Nilai Minimal Kelulusan, caranya adalah sebagai berikut:
  1. Kita buat dulu Nilai Pilihan Prasyarat, yaitu di sel L2 - L9
  2. Letakkan cursor di sel tujuan, yaitu sel J2
  3. Pilih Tab Data (di bagian atas Jendela Excel), klik Data Validation dan pilih Data Validation...
Dan di jendela Data Validation kita isikan Allow: pilih List, berikan tanda check pada Ignore Blank dan In-dell drop-down. Source: $L$3:$L$9. OK

Dah jadi, dech... selanjutnya kita tinggal mengubah-ubah data di tabel rujukan dan otomatis tabel hasil akan menyesuaikan perubahan nilainya. Selamat mencoba...