TIPS&TRICKS EXCEL : MENGAKALI FORMULA EXCEL YANG KOMPLEKS

MS Excel udah menyediakan buanyak sekalE fungsi2 yang bisa kita pakE dalam formula untuk mempermudah cem-macem penghitungan … lalu bagEmana seandEnya fungsi yang kita butuhkan ndak ada di Excel … yaaaah terpaksa kita harus akal2an manpaatin fungsi2 yang udah ada … dari fungsi2 yang udah ada kita padu-padankan kombinasikan untuk menghasilkan perhitungan sesuE yang kita pEngEn … kalO satu-dua langkah dengan satu-dua kombinasi fungsi sudah bisa kita dapet hasil yang kita pEngEn mungkin kita ndak terlalu pusing … cukup satu cell saja sudah cukup untuk menyusunnya … tapi kalO melibatkan fungsi yang buanyak dan dan harus melewati tahap yang puanjang formula yang kita buat akan menjadi sangat puanjang dan bikin puyeng merunutnya … formula yang puanjang dan melibatkan buanyak kombinasi fungsi Excel ini saya istilah kan sebagE formula kompleks … MDLMDL (MonggoDipunLeresakenMenawiDalemLepat)

contoh dari sebuah formula yang kompleks adalah fungsi terbilang …. namun karena seringnya nongol artikel yang membahas fungsi terbilang saya ndak akan membahasnya … takutnya entar blog ini jadi blog terbilang 🙂 … sebagE gantinya saya akan bahas fungsi UMUR yang pernah saya buat … meski begEtO contoh kasus untuk fungsi TERBILANG akan tetep saya sertakan

fungsi UMUR berguna untuk menghitung berapa umur antara dua tanggal … umur akan dihitung secara rinci berapa tahun … berapa bulan … berapa hari …  saya udah ubek2 excel 2003 ( … hari gene pake office 2003 … ) saya tapi sampE sekarang belOm nemu, ntah lagh kalo ternyata fungsi yang bisa menghasilkan umur secara rinci seperti diatas sudah ada di excel .. . kalO udah ada yang nemu mbok saya di kasi taU yah …

ada beberapa solusi biYar formula kompleks lebih bersahabat dengan processor otak yang tidak terlalu gegas seperti saya …

1. bikin dummy cell

karena ndak ada fungsi bawaan excel yang dapat menghitung secara langsung maka kita dapat menghitungnya secara bertahap dengan formula yang berisi fungsi2 bawaan excel yang udah ada … formula2 tersebut akan di taruh dalam cell2 Excel … naaaagh cell2 yang brisi formula tersebut dinamakan dummy cell … MDLMDL (MonggoDipunLeresakenMenawiDalemLepat)

… banyaknya dummy cell yang diperlukan berbanding terbalik dengan kecerdasan seseorang … artinya semakin pintEr seseorang insan manusia maka akan semakin sedikit dummy cell yang digunakan …

untuk ngitung umur berdasarkan tanggal awal dan tangal akhir saya pakE beberapa fungsi excel, yaitu : IF, DATE, DAY, MONTH dan YEAR … kalO belOm Paham fungsi2 tersebut silahkan dipelajari di MS Excel Help … saya aja dapetnya ilmu juga dari situ … fungsi2 tersebut saya tempel sana-sini hingga dapat menghasilkan umur yang dicari x tahun y bulan z hari

biar ndak makin bingung coba liat contoh formula kompleks yang saya bikin yang ada dalam file formulakompleks.zip silahkan download … tapi kalo pun tambah bingung ya maap

(+) Kelebihan metode ini karena hanya menggunakan fungsi2 bawaan excel yang sudah sangat familiar … tinggal pintar2nya kita merangkainya

(-) Untuk memasukan formula yang komplek dan panjang hanya dalam 1 cell akan sangat susah meskipun masih dimungkinkan … penggunaan dummy cell akan berakibat kita menjadi boros cell … bayangkan untuk menghitung UMUR saya menggunakan 17 dummy cell jika harus menghitung UMUR dari 1.000 pasang tanggal awal dan tanggal akhir maka akan nada 17.000 cummy cell

2 bikin macro

Macro Excel dibuat dalam sebuah modul dengan kode VBA yang dapat disimpan sebagai add in Excel … silahkan baca artikel2 yang terkait dengan macro excel … macro untuk ngitung berdasarkan tanggal awal dan tangal akhir sebagE berikOt:

‘kode untuk menghitung umur
‘oleh MUHAMMAD SYUKRON
‘BISMILLAHIRROHMAANIRROHIIM
‘=================================================================
Public Function UMURTBH(x As Date, y As Date) As String
tawal = x
takhir = y
If x > y Then
takhir = x
tawal = y
End If
h_selisih = Day(takhir) – Day(tawal)
If h_selisih < 0 Then h_selisih = h_selisih + Day(DateSerial(Year(takhir), Month(takhir), 1) – 1)

b_selisih = Month(takhir) – Month(tawal)
If Day(takhir) – Day(tawal) < 0 Then b_selisih = b_selisih – 1
If b_selisih < 0 Then b_selisih2 = b_selisih + 12

t_selisih = Year(takhir) – Year(tawal)
If b_selisih < 0 Then t_selisih = t_selisih – 1

UMURTBH = t_selisih & ” tahun ” & b_selisih2 & ” bulan ” & h_selisih & ” hari”
End Function

function arguments-nya =UMURTBH(tanggal#1;tanggal#2)

(+) anda cukup sekali membuat fungsi ini dan bisa jadikan sebagai add in Excel yang otomatis akan di load tiap Excel anda buka … fungsi ini akan berlaku pada semua worksheet dan semua workbook yang anda buka

(-) meskipun macro dapat direkam namun untuk membuat suatu fungsi yang kompleks anda harus menyusunnya sendiri di VB Editor … anda harus menguasai VBA untuk dapat membuat fungsi2 yang kompleks … dan tidak semua pengguna excel familiar dengan bahasa visual basic … meski VB relative lebih mudah dipahami dibandingkan bahasa lain.

3 define names

cara ini hampir mirip dengan kombinasi fungsi excel … hanya saja fungsi2 tersebut ndak ditaruh di cell melainkan dikasi nama khusus … perhatikan pada sheet dummycell … pada cell2 yang dipake sebagE dummy cell ada comment-nyah … naaaghh comment tersebut akan kita pakE sebagE nama dari fungsi2 yang ada dalam dummy cell tersebut … gimana caranya ???

silahkan lihat sheet definenames … disitu ada instruksinyah 🙂

(+) cukup sekali untuk membuatnya

(-) peletakan cell2 yang menjadi referensi fungsi ini harus sesuai dengan pola pada saat fungsi ini dibuat

silahkan anda donloth file formulakompleks. zip … file2 tersebut saya proteksi sheetnya untuk jaga2 jika terjadi perubahan yang tidak disengaja … mohon tidak mengajukan pertanyaan passwordnya apa … karena saya sudah sediakan add-in unprotect sheet

cara2 diatas biasa saya pakE untuk mengakali formula2 excel yang kompleks … saya sendiri lebih seneng pake macro … lebih fleksibel menurut saya … anda punya cara laEn … silahkan dishare … trimakasih dan sekali lagE … MDLMDL (MonggoDipunLeresakenMenawiDalemLepat)

bingung cara downloadnya ?? Baca aja halaman download dengan klik icon di samping

feed , email and my social media