Kebanyakan orang menggunakan fungsi VLookUp hanya untuk melakukan fungsi cari-dan-ganti yang berdasarkan kepada jadual. Dalam pelajaran ini kita akan bawa kemahiran VLookUp anda ke tahap advanced dengan mengajar bagaimana anda boleh mencipta flexi – formula dengan VLookUp. Flexi – formula adalah sejenis formula berubah – ubah yang menarik sebahagian dari datanya dari jadual VLookUp.

excel vlookup flexi-formula

Untuk memberikan gambaran jelas, mari lihat projek yang akan anda bina dalam pelajaran ini. Rujuk gambar di atas.

Bayangkan satu perniagaan yang mempunyai sebilangan agen jualan. Perniagaan ini hendak memberikan komisen kepada agennya berdasarkan jualan yang dilakukan bagi bulan tersebut. Nilai komisen ini akan ditambahkan dengan gaji pokok bagi menghasilkan gaji kasar agen – agen. Ini digambarkan oleh Excel table berwarna biru dalam gambar di atas.

Bayaran sekiranya perniagaan hendak berikan jumlah komisen yang berbeza – beza berdasarkan kepada jumlah jualan. Lebih tinggi jualan, maka lebih tinggi lagi komisen. Komisen pula dikira sebagai peratusan dari jumlah jualan. Sebagai contoh, jika agen membuat jualan sebanyak RM5 ribu, dia layak menerima komisen sebanyak 5% dari nilai RM5 ribu itu. Ini dapat anda lihat dalam lajur Sales dan Commision % dalam Excel table biru di atas.

Scenario sedemikian tidak boleh dicipta menggunakan Excel formula biasa. Ini kerana kadar komisen akan berbeza – beza dari agen ke agen dan dari satu bulan ke bulan seterusnya.

Namun dengan bantuan VLookUp, kita boleh menghasilkan satu formula yang flexible atau flexi – formula yang boleh mengira kadar komisen secara automatik setiap bulan.

Proses melakukan ini akan melalui 4 peringkat. Setiap peringkat akan mempunyai beberapa langkah yang akan kami jelaskan dalam animasi.

Peringkat #1: Download Worksheet Projek

Untuk projek ini kita akan menggunakan workbook Excel yang telah disiapkan oleh pensyarah. Ini adalah supaya semua pelajar akan mengerjakan data yang sama. Untuk itu silalah tekan butang Download di bawah untuk download workbook Excel terus ke alatan anda.

PENTING: Setelah download, jangan terus Open/Buka Excel workbook berkenaan. Sila ikuti langkah – langkah dalam animasi di bawah untuk kaedah download dan membuka Excel workbook dari pensyarah anda.

excel download lecturer file
Cara betul download fail pensyarah.

Arahannya:-

  1. Tekan butang fail Xdi atas.
  2. Fail akan download ke alatan anda.
  3. Tekan butang Xdi notis di kaki skrin anda. Pastikan anda tidak menekan butang Open.
  4. Beralih ke aplikasi Excel anda.
  5. Tekan butang Open dalam Excel.
  6. Di skrin Places, tekan This device
  7. Di skrin This device, tekan Download.
  8. Akhir sekali tekan pada nama fail VLOOKUP-FlexiFormula.xlsx
  9. Fail akan dibuka dalam Excel.

Download

Tekan di sini untuk download fail dari pensyarah anda.

Peringkat #2: Cipta Formula VLookUp

Dalam peringkat kedua ini kita akan mencipta formula VLookUp bagi mengira peratusan komisen berdasar pada jualan.

Jumlah jualan akan dirujuk dari lajur Sales table biru sementara peratusan komisen akan dimasukkan ke dalam lajur Commision % table yang sama.

Formula VLookup akan merujuk Excel table berwarna oren seperti digambarkan di sebelah / atas. Excel table ini mengandungi jualan (Sales) yang disusun secara menaik (ascending) dan lajur peratusan commision di sebelahnya.

Memahami VLookUp Komisen

Excel table VLookUp yang akan digunakan bagi menentukan peratusan komisen agen berdasarkan pada jualan bagi bulan berkenaan.
Sebagai contoh, bagi jualan antara RM1000 hingga RM2000, komisennya ialah 2%.
Bagi jualan antara RM2000 hingga RM4000, komisennya ialah 5% dan begitulah seterusnya.

Langkah #1: Buka Fungsi VLookUp

excel apply-vlookup-function

Dalam langkah pertama ini kita akan membuat fungsi VLookUp. Formula VLookUp akan dimasukkan pada cell pertama lajur Commision % . Ikuti langkah - langkah dalam animasi di bawah untuk melakukannya.

Langkah #2: Cipta Formula VLookUp

excel insert-vlookup-formula

Dalam langkah kedua ini kita akan masukkan semua parameter formula VLookUp supaya ianya menghasilkan peratusan komisen yang betul. Jangan lupa anda perlu masukkan tiga parameter wajib formula VLookUp iaitu lookup_value, table_array dan col_num_index . Ikuti langkah - langkah dalam animasi di bawah untuk semua parameter ini dengan betul.

Peringkat #3: Formula Commision RM

Dalam peringkat ketiga ini kita akan mencipta formula untuk menukar peratusan komisen dari peringat 1 ke dalam nilai mata wang RM.

Formula ini akan kita masukkan di lajur Commision RM dan mengambil bentuk

Commision RM = [Base Salary]*[Commision %]

Ikuti langkah - langkah dalam animasi di sebelah / atas untuk mencipta formula ini dengan betul.

Peringkat #4: Formula Total Salary

Dalam peringkat keempat ini kita akan mencipta formula untuk mengira jumlah gaji kasar agen.

Formula ini akan kita masukkan di lajur Total Salary dan mengambil bentuk

Total Salary = [Base Salary] + [Commision RM]

 

excel calculate-salary+commision

Ikuti langkah - langkah dalam animasi di atas untuk mencipta formula ini dengan betul.
PENTING: Anda mungkin perlu memegang handset anda secara melintang untuk melihat animasi dengan baik dan melakukan praktikal ini.

Penutup

Dalam pelajaran ini anda telah belajar bagaimana hendak mengembangkan fungsi VLookUp dari hanya satu fungsi cari dan ganti kepada satu fungsi yang menjadikan sebarang jenis formula satu formula yang fleksibel dan dinamik. Projek di atas boleh digabungkan dengan Project Salary Calculator yang anda lakukan sebelum ini menggunakan formula 3D untuk mencipta sejenis worksheet Excel yang berkuasa, dinamik dan mudah dikemaskinikan.

Tugasan

Sebagai tugasan bagi pelajaran ini, sila hantar Excel table biru yang kerjakan tadi kepada pensyarah supaya beliau boleh memeriksa kerja anda. Pastikan keseluruhan table  kelihatan dengan jelas. Anda mungkin perlu condongkan handset anda secara melintang untuk mengambil gambar screenshot seperti di sebelah / atas.

Tugasan Pelajaran