Pemberian Nomor dengan Fungsi ROW

Dalam bekerja dengan tabel berisi sekumpulan data, terkadang kita dihadapkan pada nomor urut yang berubah-ubah ketika terjadi perubahan urutan data. Padahal sebenarnya kita berharap nomor urut tersebut tetap meskipun kita melakukan perubahan urutan data.

Semisal ada sekumpulan data warga RW di kampung kita yang terdiri dari Nama, Alamat, Umur, No. KK, No. KTP, Status, RT, Keterangan. Terkadang kita menginginkan data tersebut diurutkan berdasar umur, dilain waktu ada permintaan untuk diurutkan berdasar RT, atau yang lainnya. Secara manual, nomor urut akan ikut berubah menjadi tidak beraturan jika kita mengubah urutan data tersebut.

Salah satu fungsi di Excel yang bisa kita manfaatkan agar nomor urut tidak berubah adalah fungsi =ROW. Sintaksis penulisannya adalah =ROW([reference]).

Berikut ini adalah contoh penggunaannya:

Karena nomor urut akan kita mulai dari baris ke-empat, maka fungsi yang kita masukkan di sel B5 adalah:

=ROW()-3

Yang artinya adalah No diisi dengan rumusan: nomor baris dimana sel tersebut berada dikurangi 3. Jika yang disorot adalah sel B12 artinya kolom No. diisi dengan 12 - 3 = 9.
Kemudian fungsi tersebut kita drag atau copy-kan ke bawah sejumlah yang diperlukan.

Dengan menggunakan fungsi tersebut, kolom No. tidak akan berubah meskipun kita melakukan filtering pada kolom-kolom yang lain. Semisal pada awalnya kita memasukkan data secara acak, kemudian kita mengurutkannya berdasarkan abjad nama. Atau kemudian ada permintaan untuk mengubah urutannya berdasarkan propinsi.

Selamat mencoba...

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.

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.. :-) 

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.

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.

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.

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...