Misal ada data seperti gambar dibawah.
Berapakah diskon untuk masing-masing penjualan. Untuk menyelesaikannya digunakan fungsi VLOOKUP.
Letakkan kursor dicell H3.
Ketik formula berikut:
=VLOOKUP(G3,$A$3:$C$4,3,FALSE)
Kopi cell H3 kemudian paste dirange H3:H8. Hasilnya seperti gambar dibawah.
Diskon masing-masing penjualan diketahui.
Jika ada data penjualan baru, diskon bisa dihitung dengan mengkopi formula yang ada diatasnya. Masalah muncul jika ada penjualan dengan kategori yang belum terdaftar diskonnya.
Diskon penjualan untuk kategori baru menghasilkan error #N/A, menambah data diskon untuk kategori baru juga tidak menyelesaikan masalah, karena table_array formula mengarah kerange $A$3:$C$4, sedangkan informasi diskon untuk kategori baru berada diluar range tersebut.
Solusinya adalah dengan mengubah data table_array supaya diskon untuk kategori baru masuk didalamnya.
Edit formula dicell H3, ubah formula menjadi:
=VLOOKUP(G3,$A$3:$C$5,3,FALSE)
Kopi cell H3 kemudian paste dirange H3:H10. Hasilnya seperti gambar dibawah.
Diskon penjualan untuk kategori baru bisa diketahui.
Permasalahan ini akan selalu muncul jika ada penjualan untuk kategori baru. Setiap ada diskon untuk kategori baru selalu harus mengubah formula.
Ada cara untuk mencegah mengubah formula setiap ada diskon untuk kategori baru, yaitu dengan menggunakan dynamic named range.
Solusi ini akan mengubah data table_array menjadi dinamis. Jika data diskon hanya ada 2 maka range hanya mempunyai dua baris. Jika ada penambahan data diskon maka range akan berubah jumlah barisnya menyesuaikan dengan jumlah data baru yang dimasukkan.
Solusi ini menggunakan bantuan Fungsi OFFSET dan Fungsi COUNTA. Fungsi OFFSET digunakan untuk membentuk sebuah range. Fungsi COUNTA bertugas menjadikan range menjadi dinamis dengan mencek apakah ada penambahan data diskon atau tidak.
Dynamic Named Range
Berikut langkah untuk membuat Dynamic Named Range
Buat Range A3:C5
Beri Nama Range
Caranya dengan mengklik name box yang ada dipojok kiri atas, kemudian ketik TABDISKON, kemudian tekan tombol ENTER.
Hasilnya adalah sebuah range dengan nama TABDISKON. Jika nama range digunakan didalam formula maka akan sama dengan range A3:C5. Jika ada penambahan data dimensi range masih tetap, untuk menjadikan dinamis digunakan fungsi OFFSET dan Fungsi COUNTA.
Edit range TABDISKON
Caranya Dengan menekan tombol CTRL+F3, muncul dialog name manager. Menampilkan semua nama range yang pernah dibuat.
Pilih range TABDISKON kemudian klik edit, muncul dialog edit name.
Untuk menjadikan range dinamis ubah bagian refres to dengan fungsi OFFSET dan COUNTA.
=OFFSET(‘Fungsi VLOOKUP’!$A$3,0,0,COUNTA(‘Fungsi VLOOKUP’!$A:$A)-2,3)
*’Fungsi VLOOKUP’ adalah nama worksheet, bisa berbeda-beda untuk masing-masing file.
Klik tombol OK. Jadilah sebuah range dinamis, jika ada penambahan data otomatis dimensi range akan berubah.
Untuk menggunakan didalam formula edit cell H3, ubah formula menjadi:
=VLOOKUP(G3,TABDISKON,3,FALSE)
Kopi cell H3 kemudian paste dirange H3:H10.
Jika ada penambahan data diskon bisa langsung diketahui dengan menambah data diskon kategori dan mengkopi formula diatasnya, tanpa harus mengubah formula.
