Menyingkat IF yang puanjuaaaang buanget

---

Muatan :

• Kilasan tentang formula pengkondisian
• Konsep tentang pengkondisian berdasar rentang nilai
• Rentang nilai berdasar satu kriteria
• Rentang nilai berdasar banyak kriteria

---

Kilasan

Sering kali ditemui dalam pekerjaan sehari-hari yang membutuhkan suatu komputasi berdasar suatu interval nilai tertentu untuk mendapatkan nilai output yang diinginkan. Misalnya seperti penentuan persentase bonus berdasar total nilai penjualan yang dicapai seseorang. Maka akan muncul banyak kondisi, seperti jika nilai sales lebih dari sama dengan sekian akan mendapat bonus sekian. Jika lebih dari atau sama dengan nilai tertentu tapi belum mencapai nilai batasan tertentu akan mendapat bonus tertentu, dan seterusnya. Umumnya, penyelesaian kasus seperti ini menggunakan fungsi IF bersarang atau IF di dalam IF sesuai banyaknya kondisi yang harus diperiksa. Hasilnya, formula akan menjadi sangat panjang ketika rentang-rentang nilai pembatas setiap persen bonus yang harus didapatkan relatif rapat.

Masalah akan menjadi lebih terasa menyebalkan ketika penentuan bonus tersebut bukan berdasar total nilai penjualan yang dicapai saja, tetapi juga harus mempertimbangkan produk yang terjual. Setiap produk memiliki rentang nilai penjualan yang berbeda untuk setiap nilai persentase bonusnya. Andaikan ada 5 produk saja yang dipasarkan dan masing-masing produk memiliki 4 rentang nilai penjualan per produk untuk mendapatkan nilai persentase bonus, maka sudah relatif sulit membayangkan bentuk formula IF yang harus dibuat. Bagaimana jika ternyata suatu saat nanti akan mempertimbangkan faktor cabang perusahaan ? Bisa jadi, setiap cabang akan memiliki rentang nilai penjualan per produk yang berbeda karena kelakuan pasar terhadap setiap produk di setiap cabang akan berbeda-beda, sedangkan penentuan bonus harus diusahakan memiliki keadilan antara daerah cabang yang respon pasarnya baik dengan daerah cabang yang relatif sulit untuk menguasai pasar.

Pembahasan kali ini adalah tentang penyederhanaan kasus-kasus yang seperti ini.

---

Konsep tentang pengkondisian berdasar rentang nilai

Pada dasarnya, proses komputasi berdasar suatu kondisi adalah proses me-lookup suatu nilai kriteria ke suatu tabel kriteria untuk mengambil suatu nilai output dikolom tertentu berdasar nilai kriteria pada kolom kriteria dalam tabel kriteria. Komputasi dengan kondisi yang berupa rentang nilai (dari suatu nilai sampai nilai tertentu dan seterusnya) adalah salah satu yang pasti bisa memenuhi konsep lookup data. Pada kondisi yang berupa rentang nilai yang dibutuhkan adalah sebuah tabel kriteria yang tersusun terurut menaik setiap kolom-kolom kunci kriterianya. Jika kolom kunci kriteria berupa suatu rentang nilai tertentu, maka yang dibutuhkan adalah nilai batas bawah setiap rentang yang ada. Tabel kriteria juga harus tersusun sebagai tabel berkaidah database. Tidak ada cell kosong dalam tabel kriteria, apapun alasannya.

---

Rentang nilai berdasar satu kriteria

Pada kasus yang kondisinya adalah berdasar sebuah kolom kriteria, ketika nilai-nilai kriteria berupa suatu rentang nilai tertentu, maka dibutuhkan suatu tabel kriteria yang terurut menaik berisi nilai-nilai batas bawah setiap rentangnya. Contohnya pada kasus berikut :

Sebuah perusahaan memiliki kebijakan dalam penetapan besarnya persentase bonus setiap karyawan bagian penjualan berdasarkan pencapaian total nilai penjualan tertentu. Kebijakan tersebut adalah :

Biasanya, kasus seperti ini akan segera dikerjakan dengan formula IF berdasar setiap kalimat dalam kebijakan tersebut. Misalkan saja ada data penjualan, maka kolom persentase bonus akan diisi dengan formula IF bersarang. Contohnya seperti data penjualan yang ada di range B10:D17 pada gambar berikut :

Cell D11 (pada gambar diatas) akan diisi dengan formula IF yang berbunyi :

=IF(C11>=10000000,10%,IF(C11>=5000000,5%,IF(C11>=2000000,2%,0)))

dengan bagian :

• C11>=10000000,10% untuk kalimat kebijakan berbunyi Sales >= 10,000,000; Bonus 10%
• C11>=5000000,5% untuk kalimat kebijakan berbunyi 5,000,000 <= Sales < 10,000,000; Bonus 5%
• C11>=2000000,2% untuk kalimat kebijakan berbunyi 2,000,000 <= Sales < 5,000,000; Bonus 2%
• ,0 untuk mewakili kondisi tersisa, yaitu untuk kalimat kebijakan berbunyi Sales < 2,000,000; Bonus 0%

Penggunaan konsep lookup pada kasus seperti ini akan didahului dengan proses menyusun tabel kriteria berdasar kalimat-kalimat kebijakan yang ada. Untuk contoh kasus ini, kalimat kebijakan pada gambar diatas akan membentuk sebuah tabel kriteria seperti pada range C21:D25 yang ada digambar berikut :

Tampak pada gambar tabel kriteria tersebut bahwa setiap kalimat kebijakan didefinisikan berdasar nilai batas bawahnya dan disusun terurut menaik (sorted ascending). Dari kalimat kebijakan dapat diketahui bahwa nilai-nilai batas bawahnya adalah 0;2,000,000;5,000,000;10,000,000. Setiap nilai batas bawah akan memiliki nilai persentase bonus yang ditetapkan oleh kalimat kebijakan yang ada. Tabel kriteria pada kolom 'Batas bawah interval Sales' adalah kolom kunci untuk bisa mendapatkan nilai persentase bonus di kolom 'Persen Bonus'.

Penyusunan kolom kunci yang terurut menaik membuka peluang untuk bisa menggunakan fungsi-fungsi dalam kelompok Lookup and Reference seperti fungsi vLookUp, LookUp, atau formula Index Match. Pada penggunaan fungsi vLookUp, ketika bekerja dengan nilai rentang, maka opsi Range_Lookup harus diisi TRUE. Ketika menggunakan formula Index Match, maka opsi Match_Type pada sisi fungsi Match harus diisi 1.

Misalkan saja data penjualan diletakkan pada area range G21:I28 seperti pada gambar berikut :

Maka pada tabel data di kolom 'Bonus %' untuk record pertama, yaitu pada cell I22, bisa diberi formula :

=LOOKUP( H22 , $C$22:$C$25 , $D$22:$D$25 )

yang bisa diartikan bahwa mencari nilai dikolom 'Batas bawah interval Sales' ($C$22:$C$25) pada tabel kriteria, yang terakhir kali sama atau terlampaui oleh nilai penjualan (H22) di tabel data. Jika sudah menemukan barisnya di tabel kriteria, maka nilai dikolom 'Persen Bonus' ($D$22:$D$25) di tabel kriteria akan diambil sebagai nilai output formula.

Jadi, saat nilai data penjualan sebesar 1,000,000 nilai terakhir di $C$22:$C$25 yang terakhir kali sama atau terlampaui adalah nilai 0. Nilai penjualan tidak sama dan tidak bisa melampaui nilai 2,000,000 di $C$22:$C$25. Setelah diketahui bahwa nilai penjualan sebesar 1,000,000 tersebut akan mendapatkan nilai kolom kunci dari tabel kriteria pada nilai 0 yang ada di baris 22, maka nilai tabel kriteria pada kolom D (Persen Bonus) di baris 22 yang bernilai 0% akan diambil sebagai nilai output formula. Akhirnya, cell I22 akan berisi 0%.

---

Rentang nilai berdasar banyak kriteria

Seringkali ditemui kebutuhan untuk mengkomputasi berdasar suatu rentang nilai tertentu yang juga berdasar suatu kriteria lainnya. Kasus seperti ini pada dasarnya adalah lookup dengan banyak kriteria. Contohnya seperti kisah berikut :

Sebuah perusahaan memiliki kebijakan untuk menentukan persentase bonus berdasar nilai penjualan per produk. Kebijakan tersebut adalah sebagai berikut :

Kasus seperti ini jika diselesaikan dengan IF, tentu akan menghasilkan suatu formula IF bersarang yang sangat panjang dan sulit dilacak ketika terjadi kesalahan dalam formula. Konsep dasar kasus seperti ini yang sejatinya adalah proses lookup dengan banyak kriteria, maka yang dibutuhkan pertama kali adalah tabel kriteria sesuai dengan seluruh kalimat kebijakan yang ada.

Tabel kriteria dari serangkaian kalimat kebijakan diatas tadi akan berisi kolom Produk karena ada faktor per produk. Juga akan berisi kolom nilai-nilai batas bawah interval sales. Nilai output berupa persen bonus juga harus ada dalam tabel kriteria tersebut. Tabel kriteria harus berkaidah tabel database. Setidaknya, tidak ada cell kosong dalam tabel kriteria. Jadi, meskipun data dalam kolom itu harus berulang-ulang ditulispun maka tetaplah ditulis dan tidak dikosongkan dengan anggapan bahwa kalau kosong berarti nilainya sama dengan atasnya. Tabel kriteria tersebut akan berbentuk seperti tabel di range K3:M12 digambar berikut :

Misalkan pada range O3:R14 adalah data penjualan seperti pada gambar berikut :

maka nilai 'Bonus %' bisa dihitung dengan fungsi LookUp. Contohnya seperti pada cell R5 yang berisi formula :

=LOOKUP( Q5 , $L$4:$L$12 / ($K$4:$K$12=P5) , $M$4:$M$12 )

Proses lookup pada formula tersebut tidak secara langsung dilakukan terhadap tabel kriteria. Proses lookup dilakukan terhadap suatu data array yang disusun dari tabel kriteria. Artinya, semua data dalam tabel kriteria disusun agar yang sesuai kebutuhan, yaitu memiliki produk tertentu berupa nilai batas bawah interval yang ada dalam tabel kriteria. Sedangkan data tabel kriteria yang tidak sesuai dengan produk tertentu tidak menampilkan nilai batas bawah interval. Proses pada formula di range R5 tersebut dapat dijabarkan seperti gambar berikut :

Secara singkat, prosesnya adalah : (berdasar nomor step pada gambar)

1. Menyimpan nilai produk dari tabel penjualan data di baris 5, yaitu merujuk ke cell P5. Produk tersebut adalah produk 'B'.
2. Kemudian, data produk dalam tabel kriteria secara berurutan dari record pertamanya, diperiksa kesesuaiannya dengan hasil step 1. Maka didapatlah kondisi setiap baris tabel kriteria yang berisi FALSE ketika produk di tabel kriteria tidak sama dengan nilai produk dari tabel penjualan. Sedangkan yang sesuai akan bernilai TRUE.
3. Data nilai batas bawah interval sales dari tabel kriteria diambil secara berurutan dari record pertama tabel kriteria juga, karena proses step 2 mengambil data dari tabel kriteria juga berurutan dari record pertamanya.
4. Menyusun array untuk area lookup dengan ketentuan, setiap hasil pemeriksaan di step 2 yang bernilai TRUE akan menampilkan nilai batas bawah interval sales dari tabel kriteria yang ber-datatype numerik. Sedangkan yang FALSE harus menampilkan nilai dalam datatype selain numerik, karena datatype numerik sudah digunakan oleh yang bernilai TRUE.
   
   Proses yang memungkinkan untuk memenuhi ketentuan ini adalah membagi setiap nilai batas bawah interval sales (hasil step 3) dengan hasil pemeriksaan oleh step 2. Nilai TRUE akan setara 1 dan nilai FALSE setara 0. Jadi, ketika nilai batas bawah interval sales dibagi dengan yang TRUE akan menghasilkan nilai batas bawah sales, karena dibagi dengan nilai 1 yang setara TRUE. Sedangkan ketika hasil pemeriksaan adalah FALSE akan membuat hasil bagi berupa error value #DIV/0! yang tentu saja datatype-nya bukan numerik.
   
   Hasilnya, seluruh data produk dari tabel kriteria yang tidak sesuai dengan produk di tabel penjualan baris 5 akan berupa error value. Sedangkan yang sesuai akan berupa nilai batas bawah interval sales. Array yang tercipta ini bisa digunakan sebagai area lookup nantinya.
5. Mengambil nilai sales dari tabel data penjualan, yaitu merujuk ke cell Q5 yang bernilai 150,000,000. Nilai ini adalah nilai yang akan di-lookup ke area lookup yang berupa array hasil step 4.
6. Mengambil nilai-nilai output dari tabel kriteria secara berurutan dari record pertama, karena bagian lain dari formula yang mengambil data dari tabel kriteria juga melakukannya secara berurutan dari record pertama tabel kriteria. Nilai output tersebut adalah nilai dari kolom 'Persen Bonus' dari tabel kriteria. Nilai-nilai inilah yang akan diambil untuk menjadi nilai hasil akhir formula.
7. Proses lookup data nilai sales dari tabel data penjualan (Q5) ke array nilai batas bawah interval sales terpilih untuk mengambil nilai persen bonus dari tabel kriteria. Karena nilai yang di-lookup, yaitu nilai penjualan dari tabel penjualan memiliki datatype numerik, maka seluruh elemen array hasil step 4 yang bukan ber-type numerik akan diabaikan. Artinya, proses lookup akan terjadi pada nilai batas bawah interval sales hasil step 4 yang sesuai produknya. Hasil akhir dari formula di range R5 ini adalah 20% karena nilai 150,000,000 (hasil step 5) yang terakhir bernilai sama atau terlampui ditemukan pada elemen array (hasil step 4) bernilai 100,000,000 yang memiliki nilai persen bonus sebesar 20%.

---

Closing :

Beberapa hal yang perlu digarisbawahi adalah :

• Pengkondisian banyak kondisi dapat diselesaikan dengan proses me-lookup data ketika memiliki tabel referensi lookup yang baik dan sesuai ketentuan dari setiap kondisi atau mencerminkan setiap ketentuan kondisi yang ada.
• Pengkondisian dengan banyak kriteria adalah proses lookup dengan banyak kriteria.
• Tabel referensi lookup (dalam kasus diatas disebut tabel kriteria) harus berkaidah tabel database, minimal tidak ada cell yang kosong dan terurut secara menaik (sorted ascending).
• Data lookup yang digunakan pada setiap proses lookup hanyalah yang memiliki datatype yang sama dengan nilai yang di-lookup. Hal ini adalah kunci utama dalam melakukan kegiatan lookup.
• Pada kondisi yang berupa rentang-rentang nilai, akan menbuat tabel kriteria harus memiliki kolom yang berisi nilai batas bawah dari setiap rentang yang diminta oleh ketentuan kondisi yang ada.
• Pada lookup berdasar 1 (satu) kriteria terhadap nilai-nilai rentang, dapat dikerjakan dengan fungsi LookUp, vLookUp, maupun formula Index Match.
• Penggunaan fungsi vLookUp dan formula Index Match harus menggunakan opsi Match_Type bernilai 1 atau Range_Lookup bernilai TRUE.

---

File(s) :

IF dengan lookup

---

Coretan terkait :

• Array Formulas. Kenalan yuuk !
• LookUp ke kiri atau ke kanan untuk satu kriteria
• LookUp banyak kriteria

---