Ms. Excel Mega Formula


Terkadang lembar kerja yang kita buat memerlukan intermediate formula (formula perantara) untuk menghasilkan keluaran tertentu. Misalnya untuk menghasilkan nilai Z, kita harus membuat formula dengan nilai A yang menghasilkan nilai B, lalu kita buat formula lagi dengan nilai B yang menghasilkan nilai C, dan begitu seterusnya hingga menghasilkan nilai Z. Dengan kata lain, sebuah formula terkadang bergantung kepada hasil dari formula lain. Setelah kita—mungkin—merasa yakin bahwa seluruh formula yang kita buat benar, kita bisa saja menghilangkan semua intermediate formula dan menciptakan satu megaformula, yaitu satu formula besar yang merupakan gabungan dari banyak formula. Keuntungannya ? Kita bisa menghemat jumlah cell yang terpakai, dan perhitungan (calculation) yang dilakukan oleh Excel akan berjalan lebih cepat. Lagipula, tentu orang lain akan kagum terhadap kemampuan membuat formula yang kita miliki. Kerugiannya ? Formulanya menjadi rumit dan sulit untuk dimengerti dan diubah.

Perhatikan contoh berikut: Bayangkan kita memiliki worksheet yang berisi nama-nama orang lalu kita diminta untuk menghilangkan nama tengah masing-masing orang. Dengan mengubah satu persatu cell tentunya akan memakan waktu banyak, dan penyelesaian satu-satunya adalah dengan menggunakan formula. Dan formula yang dibutuhkan melibatkan beberapa intermediate formula. Coba lihat worksheet di bawah ini yang terdiri dari 6 intermediate formula (kolom B sampai kolom G). Nama awalnya terdapat di kolom A dan hasil akhirnya terdapat di kolom H.

A B C D E F G H
1 Sofyan Nur Ardian Sofyan Nur Ardian 7 11 11 Sofyan Ardian Sofyan Ardian
2 Adi Kristanto Adi Budi Kristanto 4 #VALUE! 4 Adi Kristanto Adi Kristanto
3 Kiki Darmanto Kiki Darmanto 5 #VALUE! 5 Kiki Darmanto Kiki Darmanto
4 Abdul Q. Hakim Abdul Q. Hakim 6 9 9 Abdul Hakim Abdul Hakim
5 Denny Pra Tama Denny Pra Tama 6 10 10 Denny Tama Denny Tama
Intermediate Formula yang ditulis pada baris pertama
Cell Intermediate Formula Maksud
B1 =TRIM(A1) Menghilangkan spasi lebih
C1 =FIND(“ ”,B1,1) Mencari spasi pertama
D1 =FIND(“ ”,B1,C1+1) Mencari spasi kedua
E1 =IF(ISERROR(D1),C1,D1) Gunakan spasi pertama jika tidak ada spasi kedua
F1 =LEFT(B1,C1) Ambil nama depan
G1 =RIGHT(B1,LEN(B1)-E1) Ambil nama belakang
H1 =F1 & G1 Gabungkan kedua nama

Sebenarnya kita bisa saja menghilangkan seluruh intermediate formula lalu menciptakan megaformula yang langsung menghasilkan nilai akhir yang diinginkan sebagai berikut :

= LEFT(TRIM(A1), FIND(“ ”, TRIM(A1), 1)) & RIGHT(TRIM(A1), LEN(TRIM(A1)) – IF(ISERROR(FIND(“ ”,

TRIM(A1), FIND(“ ”, TRIM(A1), 1) + 1)), FIND(“ ”, TRIM(A1), 1), FIND(“ ”, TRIM(A1),

FIND(“ ”, TRIM(A1), 1) + 1)))

Ketika kita sudah puas dan yakin bahwa megaformula yang kita ciptakan berjalan dengan baik, kita bisa menghapus semua intermediate formula yang ada karena sudah tidak digunakan lagi.

Megaformula di atas melakukan hal yang sama dengan semua intermediate formula yang ada—walaupun terlihat sangat sulit sekali dipahami maksudnya. Untuk lebih amannya, copy-lah seluruh intermediate formula ke tempat lain sehingga jika ada perubahan atau terdapat kesalahan pada formula yang kita susun, kita dapat dengan mudah mengubahnya.

 CATATAN …

Untuk menciptakan megaformula, mulailah dari formula yang terakhir lalu ubahlah setiap parameter yang ada menjadi urutan formula sebelumnya dan begitu seterusnya hingga dicapai cell yang berisi nilai awal atau nilai yang tidak bisa diurai lagi. Keterbatasan megaformula adalah bahwa formula dalam Excel hanya mampu menampung 1024 karakter.

Mungkin kita beranggapan bahwa penggunaan megaformula akan memperlambat proses perhitungan yang dilakukan oleh Excel, karena megaformula terlihat sangat kompleks. Sebenarnya tidak juga. Untuk lebih jelasnya lihatlah perbandingan pada tabel di bawah ini yang memperbandingkan worksheet yang menggunakan megaformula sebanyak 65,536 kali, worksheet yang menggunakan 6 intermediate formula, dan yang menggunakan fungsi VBA.

Intermediate Formula versus Megaformula
Metode Waktu perhitungan Ukuran file
Intermediate formula 7 detik 23.2 MB
Megaformula 5 detik 8.2 MB
fungsi di VBA 66 detik 800 MB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s