Pusing dengan Rumus yang Panjang?

June 7, 2009 at 2:48 pm 2 comments

Ya ….. judul di atas memang sering dialami juga saya maupun teman-teman dan mungkin juga Anda. Belajar rumus excel kebanyakan memang tidak mudah. Yang paling sering kita gunakan adalah sum, max, min, average, dan if. Kalau anda disuruh membaca lagi aturan untuk menggunakan vlookup misalnya, mungkin masih perlu lagi waktu untuk mencernanya. Belum lagi keterbaatasan Ms Excel yang paling terkenal adalah bahwa Anda tidak dapat “menggabungkan” atau “menumpuk” rumus dalam satu sel lebih dari 7 secara ”berjenjang”untuk Ms Excel 2003 ke bawah. Misalnya, rumus berikut ini akan menghasilkan IF berikut akan menghasilkan error. (Walaupun hal ini tidak masalah buat Ms Excel 2007)

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

Aturan umum, jika Anda memiliki rumus dengan lebih dari 7 jenjang/tingkatan pernyataan, Anda harus menggunakan fungsi VBA. Selain itu jika rumusnya panjang sekali … (walaupun bisa di Excel 2007), tapi sangat tidak praktis jika kita menelusuri dinama letak salahnya rumus.

Jika Anda tidak ingin menggunakan VBA, maka untuk mengatasi batasan ini dapat kita gunakan define name untuk merujuk ke bagian dari rumus. Karena rumus terdefinisi (selnya diberi nama dengan define name) akan dievaluasi secara terpisah, maka Anda dapat membuat satu atau lebih rumus merupakan bagian dari rumus besar, dan menggabungkan ini menjadi rumus ”utama”.

Misal kita akan membuat rumus berjenjang dengan IF:

IF A4 = 1 Then 11
Else If   A4 = 2 Then 22
Else If   A4 = 3 Then 33
Else If   A4 = 4 Then 44

Else If   A4 = 13 Then 130  Else “Tdk Ketemu”

dan seterusnya.  Tentu saja, kalau mau praktis, kita dapat menggunakan fungsi VLOOKUP (wah… belajar lagi deh), akan tetapi untuk menggambarkan cara kerja fungsi berjenjang tersebut di atas. Pertama kita membuat rumus yang diberinama SatuSampaiEnam, dengan rumus sebagai berikut:

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,
IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

Kemudian kita buat lagi rumus dengan nama TujuhSampaiTigaBelas, dengan rumus sbb:

=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,
IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,
IF(Sheet1!$A$4=13,130,”tidak ketemu”)))))))

Yang terakhir di sel yang kita tuju kita masukkan rumus “utama” sebagai berikut:

=IF(SatuSampaiEnam,SatuSampaiEnam,TujuhSampaiTigaBelas)

Langkah-langkah di atas akan mengatasi keterbatasan fungsi berjenjang, karena tidak ada fungsi yang melebihi batasan fungsi berjenjang.

Anda dapat menggunakan teknik ini jika mengalami permasalahan yang serupa.

Entry filed under: Ms Excel, Tips, Tutorial. Tags: , , , , , .

Bagaimana menggunakan Named Range? Hello world!

2 Comments Add your own

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

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 94,725 hits

Twitter Updates

Iklan


%d bloggers like this: