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 + 12t_selisih = Year(takhir) – Year(tawal)
If b_selisih < 0 Then t_selisih = t_selisih – 1UMURTBH = 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![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Lengkap sekali,dan penjelasannya
selamat anda pertamax
mas saya baru belajar excel dan lamban pahamnya jadi mohon kode unprotecnya mas
Assalamu’alaikum. Sae pisan euy, tapi sayang saya gak tau macro. Kata orang tua MACRO iku mahluk opoo to….?
Mas kalo saya pake formula DATEDIF.
=DATEDIF(tgl akhir;tgl akhir;”format”). Dimana format “Y” untuk Tahun,”YM” untuk bulan dan “MD” untuk hari. Mekaten Mas. Saya juga MDLML. Punten upami lepat bhs jawana,sebab saya orang sunda yg bernama jawa.
setuju dengan pak parno … seperti yang saya tulis dalam artikel ini
http://norkuys.wordpress.com/2010/12/23/excel-tips-tricks-hitung-umur-dengan-fungsi-datedif/
🙂
Assalamu’alaikum. Selamat pagi mas. Aduh abdi isin pisan mas,ternyata setelah buka arsip saya, lhaaa kok rumus itupun saya dapet dari panjenengan beberapa waktu yang lalu.
Maaf ya mas bro…!
Wassalam.
Alaikum salam … laaaah kok malah mintak maap dan isin-isin … biasa aja …dalam excel ini saya jugak masih IJO banget … banyak yang belom ngerti … makanya sampE sekarang pun saya masih terus belajar 🙂 🙂