Kompleksitas Offset Function [Lanjutan]

Masih ingat artikel mengenai “Trik Membuat Dropdown Menu Pada Microsoft Excel” dan “Trik Membuat Grafik Menggunakan VBA Excel”?. Nah pembahasan Offset Function kali ini masih erat kaitannya dengan tema tersebut. Dropdown menu digunakan sebagai tampilan utama aplikasi VBA Excel yang saya buat sebelumnya. Fungsinya untuk memudahkan user menampilkan informasi sesuai dengan kebutuhan. User cukup memilih salah satu pilihan data di masing-masing dropdown menu, kemudian menekan tombol refresh^[1] sebagai tanda bahwa syntax siap dieksekusi. System akan memproses lebih lanjut perintah tersebut, dan akan menampilkan informasi/grafik (sesuai dengan pilihan menu awal) di cell/range yang sudah kita tentukan. Jika kita ingin menampilkan data berupa grafik, maka system akan memanggil dan mengeksekusi syntax grafik secara otomatis, jika data yang ingin kita tampilkan berupa table, maka system akan memanggil dan mengeksekusi syntax table secara otomatis dan seterusnya.

Pada kesempatan kali ini saya ingin menampilkan dua buah contoh penggunaan Offset Function yang lebih kompleks. Contoh pertama berkaitan dengan “automatic blocking record”. Yakni, setiap kali ada penambahan record di database kita, maka system akan memblock data secara otomatis mulai record awal hingga record akhir. Pada pembahasan sebelumnya, syntax kita tuliskan melalui cell yang tersedia di worksheet. Namun kali ini saya akan menuliskan syntax tersebut pada jendela “Name Manager”. Perhatikan contoh berikut:

Langkah pertama yang perlu kita lakukan adalah sebagai berikut; klik menu Formulas, Define Name, Name Manager atau cukup tekan Ctrl+F3 pada keyboard anda. Pilihlah New pada jendela name Manager kemudian ketik syntax berikut pada option Refers to; =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1), dan “Kabupaten” pada option Name. Bagan di sebelah kiri menunjukkan database sebelum terjadi penambahan record. Ketika syntax diatas kita jalankan, maka record yang akan terblocking adalah range A2:A10. Bagaimana jika record kita tambah (i.e Subang dan Kuningan)?, Benar, jumlah record yang terblocking pun akan ikut bertambah (secara otomatis) sebanyak record yang ditambahkan (lihat bagan sebelah kanan, red). Jika demikian, apa fungsi dari COUNTA (pada syntax) diatas?. COUNTA akan menghitung jumlah record terbaru yang ada di database sepanjang range Sheet1!$A:$A secara terus menerus. Sudah banyak contoh-contoh serupa di buku atau internet, salah satunya di buku yang berjudul “Membuat Aplikasi Penjualan Menggunakan Macro Excel”, terbitan PT. Elex Media Komputindo. Perhatikan contoh selanjutnya;

Contoh kedua kali ini sedikit lebih kompleks (dari contoh yang pertama), dan belum pernah saya temukan di referensi-referensi Microsoft Excel manapun yang pernah saya baca, baik di buku maupun internet (kurang tahu kalau sekarang, CMIIW). Yang membuat kompleks adalah kombinasi antara dropdown menu, Offset Function, Index Function dan Match Function. Index dan Match Function sejatinya indentik dengan fungsi Lookup lainnya semisal VLOOKUP, HLOOKUP, LOOKUP dan lain-lain (silahkan browsing di internet atau bisa juga dicari di Help, red). Skenario sederhananya adalah sebagai berikut; Kategori terdiri dari banyak option begitu pula dengan Mitra AD. Health_LatePayment_2 dan PT. Pariwara Jejaring Usaha saya ambil sebagai sample. Ketika kita memilih option “Health_LatePayment_2” dan “PT. Pariwara Jejaring Usaha”, maka secara otomatis system harus mencari dan memblock informasi yang sesuai dengan pilihan tersebut. Selanjutnya ketika tombol refresh ditekan, maka table dan grafik (untuk kategori diatas) akan terbuat^[2]. Bagaimana dengan syntaxnya? Let’s check this out guys;

=OFFSET(INDEX('KPI Mitra AD'!$HJ$108:$HJ$121,MATCH('KPI Mitra AD'!$C$8,'KPI Mitra AD'!$HJ$108:$HJ$121,0),1),0,0,1,COUNTA('KPI Mitra AD'!$107:$107)-1)

Ketikan syntax diatas pada jendela Name Manager i.e pada option Refers to dan “HLP2” pada Name sebagaimana contoh sebelumnya. Hasil akhir dari fungsi MATCH adalah raws (yang berupa bilangan bulat), yakni posisi baris dimana data yang akan kita cari itu berada. Jika syntax MATCH adalah (lookup_value, lookup_array, [match_type]) dengan lookup_value = 'KPI Mitra AD'!$C$8 (cell option pada dropdown menu Mitra AD, red), maka nilai MATCH-nya menjadi 110. Bagaimana dengan INDEX? Hasil akhir dari fungsi ini adalah “address”, yakni alamat dari cell yang kita cari. Jika syntax INDEX adalah INDEX(array, row_num, [column_num]) dengan array = 'KPI Mitra AD'!$HJ$108:$HJ$121, maka alamat yang kita cari adalah HJ110. Range Offset akan berubah-ubah secara dinamis mengikuti pilihan MITRA AD pada drop down menu. Perubahan inilah yang menyebabkan table dan grafik yang ditampilkan juga ikut berubah tatkala tombol refresh ditekan.

Bagaimana, sederhana kan?. Mudah-mudahan bisa dipahami.

__________________

Footnote;

[1]. Tombol Refresh saya buat menggunakan Command Button (ActiveX Control) (i.e di Menu Developer, Controls, Insert Controls)

[2]. Table dan grafik akan dieksekusi oleh syntax lain pada aplikasi tersebut i.e syntax VBA. Sedangkan Offset hanya berfungsi mengambil data terpilih yang dijadikan source pembuatan table dan grafik