Membuat Mega Formula di Excel

Tip berikut menggambarkan cara menggabungkan beberapa formula menjadi satu formula yang panjang (Mega Formula). Jika kita sering bekerja dengan Excel, kita sering melihat beberapa rumus yang cukup panjang dan sulit untuk dipahami jalan ceritanya. Berikut adalah contoh membuat Mega Formula


Tujuan dari tip ini adalah membuat satu rumus (tanpa sel bantuan) untuk membuangnama tengah dari sebuah nama yang terdiri dari 3 kata. Tabel berikut menunjukkan beberapa nama yang diikuti dengan rumus-rumus perantara yang pada akhirnya dibuat untuk mengambil nama depan dan nama belakang saja. 

Rumus yang digunakan di baris yang kedua adalah sebagai berikut:

Dengan sedikit usaha, kita bisa menggabung rumus-rumus di atas menjadi satu rumus yang agak panjang :
LEFT(TRIM(B2),FIND(" ",TRIM(B2),1))&RIGHT(TRIM(B2),LEN(TRIM(B2))-IFERROR(FIND(" ",TRIM(B2),FIND(" ",TRIM(B2),1)+1),FIND(" ",TRIM(B2),1)))


Berikut langkah-langkahnya:

• Periksa sel I2. Di sel I2 mengandung rumus =G2&H2.
• Aktifkan sel H2. Kopikan seluruh rumus di sel H2 (kecuali tanda sama dengan).
• Aktifkan sel I2, gantilah tulisan H2 dengan mem-paste-kan rumus yang sudah dikopi. Sehingga di sel I2 sekarang tertulis: 

=G2&RIGHT(C2,LEN(C2)-F2)

• Aktifkan sel G2, dan kopikan rumus di sel G2 kecuali tanda =. 
• Aktifkan sel I2, ganti tulisan G2 dengan mem-paste-kan rumus yang sudah dikopi (Ctrl+V). Sekarang di sel I2 tertulis: 

=LEFT(C2,D2)&RIGHT(C2,LEN(C2)-F2)

• Dengan cara yang sama, gantilah reference F2 di sel I2 dengan cara mengkopi dari sel F2, sehingga rumusnya sekarang menjadi: 

=LEFT(C2,D2)&RIGHT(C2,LEN(C2)-IFERROR(E2,D2))

• Gantilah reference E2 di sel I2 dengan mengkopi dari sel E2. Sehingga rumusnya menjadi: 

=LEFT(C2,D2)&RIGHT(C2,LEN(C2)-IFERROR(FIND(" ",C2,D2+1),D2))

• Gantilah tiga reference D2 di sel I2 dengan mengkopi dari sel D2. Sehingga rumusnya menjadi: 

=LEFT(C2,FIND(" ",C2,1))&RIGHT(C2,LEN(C2)-IFERROR(FIND(" ",C2,FIND(" ",C2,1)+1),FIND(" ",C2,1)))

• Akhirnya, Gantilah tujuh reference C2 di sel I2 dengan mengkopi dari sel C2. Sehingga rumusnya menjadi: 

=LEFT(TRIM(B2),FIND(" ",TRIM(B2),1))&RIGHT(TRIM(B2),LEN(TRIM(B2))-IFERROR(FIND(" ",TRIM(B2),FIND(" ",TRIM(B2),1)+1),FIND(" ",TRIM(B2),1)))

Kita lihat bahwa reference rumus di sel I2 hanya mengarah pada sel B2 saja. Sehingga kita men-delete sel C2 sampai H2. Mega Formula yang baru saja kita buat fungsinya sama dengan beberapa formula bantuan di sel C2 sampai H2. Dan perlu juga dipahami bahwa dalam beberapa kasus, penggunaan Mega Formula membuat Exel lebih cepat dalam proses kalkulasinya dibandingkan dengan penggunaan beberapa sel bantuan.


Perhatian...! Jika setelah mengkopi dari suatu sel, hasilnya keliru. Maka kita UNDO pekerjaan kita, lalu sebelum dikopi, sel tersebut diberi tanda kurung di awal dan di akhir rumus. Baru kita kopikan lagi ke rumus induk.

Sumber: John WalkenBach, Microsoft Excel 2010: Tips & Tricks,Wiley Publishing, 2010

Artikel Terkait: