Semua formula yang kita pelajari setakat ini dikenakan kepada data di dalam Excel table yang sama. Di dalam pelajaran ini kita akan pelajari bagaimana hendak mencipta formula dengan merujuk kepada data di luar Excel table. Kita akan menyambung projek Salary Calculator yang dimulakan dalam pelajaran lepas dan gunakan projek ini untuk pelajari kaedah mencipta formula baru menerusi satu siri tugasan – tugasan.

Tugasan #1: Formula Pendapatan OT

Dalam tugasan pertama ini kita akan mencipta formula untuk mengira pendapatan yang diperoleh dari setiap kadar OT (Over Time) yakni kerja lebih masa. Seperti yang kita lihat dalam projek Salary Calculator, kerja OT punya tiga kadar iaitu kadar 1.5, 2 dan 3. Kadar – kadar ini mungkin berbeza dari satu majikan ke majikan lain namun kita gunakan kadar yang lazim di sini.

Formula untuk mengira jumlah pendapatan bagi sesuatu kadar kerja OT adalah:-

Kadar Sejam X Kadar OT X Jumlah Jam Kerja

Dalam konteks workbook Salary Calculator, ianya adalah:

(Hourly Rate) X (OT Rate) X Hours

Ikuti langkah – langkah dalam animasi di bawah untuk mencipta formula ini dalam lajur Total Excel table OT kita.

pel-23-01-ot-formula-1
Formula Total OT

Arahannya:-

  1. Tekan cell pertama lajur Totaldi Excel table OT sebanyak dua kali perlahan - lahan.
  2. Tekan Edit pada bar terapung.
  3. Taipkan =.
  4. Tekan pada cell yang mengandungi data Hourly Rateyakni cell B3. 
  5. Taipkan *yakni simbol bagi darab.
  6. Tekan pada cell pertama lajur OT Rate
  7. Taipkan *
  8. Tekan pada cell pertama lajur Hours.
  9. Tekan butang hijau betul.

Seperti yang dapat anda lihat dalam mencipta formula di atas kita telah merujuk kepada data Hourly Rate yang terletak di luar Excel table OT. Ini adalah kali pertama kita melakukannya.

Setelah formula di cipta anda akan perasan bahawa Excel memberikan keputusan pelik pada cell – cell kadar 2 dan 3 seperti yang digambarkan di bawah.

error-in-ot-formula

Sesetengah dari hasil formula itu menunjukkan RM0.00 dan yang lain memberikan ralat #VALUE. Perkara ini terjadi disebabkan oleh alamat relative cell dalam formula yang digunakan. Mari kita kaji perkara ini dengan lebih mendalam.

Alamat Relative & Absolute

Apabila kita mencipta formula dalam Excel table, Excel akan secara automatik mengisi cell – cell di bawahnya dengan formula. Dalam mengisi cell – cell lain ini, Excel akan cuba mengagak lokasi cell – cell dari mana data akan digunakan. Excel lazimnya menggunakan kaedah alamat relatif bagi mengagak lokasi cell data. Alamat relatif ini berfungsi dengan baik sekiranya semua data dalam formula di ambil dari Excel table yang sama. Namun jika data di ambil dari luar Excel table, ianya mungkin menimbulkan ralat.

Perkara ini boleh kita perhatikan dengan menganalisa formula – formula dalam lajur Total Excel table OT kita seperti yang digambarkan dalam animasi di bawah ini.

ot-total-formula-cell-1

Dalam cell pertama ini, formulanya adalah betul kerana ianya dicipta secara manual oleh kita.

ot-total-formula-cell-2

Dalam cell kedua formula Total (di lokasi C7) hasilnya menjadi RM0.00. Formula di cell ini dicipta secara automatik oleh Excel menggunakan kaedah alamat relatif. Di sebabkan cell ini adalah satu kotak ke bawah dari cell asal, maka Excel merujuk Hourly Rate satu kotak ke bawah dari Hourly Rate yang sebenar, iaitu di cell B4 yang tidak mengandungi sebarang data. Ini menghasilkan nilai RM0.00.

ot-total-formula-cell-3

Dalam cell ketiga formula Total (di lokasi C8) hasilnya menjadi #VALUE. Formula di cell ini dicipta secara automatik oleh Excel menggunakan kaedah alamat relatif. Di sebabkan cell ini adalah dua kotak ke bawah dari cell asal, maka Excel merujuk Hourly Rate dua kotak ke bawah dari Hourly Rate yang sebenar, iaitu di cell B5 yang  mengandungi teks Hours. Ini menghasilkan ralat #VALUE disebabkan formula cuba mendarab dengan teks.

Tugasan #2: Formula Absolute Address

Kesilapan pada hasil formula di atas disebabkan oleh Excel menggunakan alamat relatif bagi cell Hourly Rate. Untuk mendapatkan jawapan yang betul, semua formula pada lajur Total Excel table OT mestilah merujuk kepada cell Hourly Rate yang sama iaitu cell B3. Ini boleh dicapai dengan menukar rujukan cell B3 dari alamat cell relatif kepada alamat cell absolute. Alamat cell absolute adalah alamat yang tidak akan berubah walaupun formula itu berubah.

Ikuti langkah – langkah dalam animasi di bawah untuk mengubah alamat cell relatif B3 dalam formula kepada alamat cell absolute.

ot-formula-3
Formula Absolute Address

Arahannya:-

  1. Tekan cell pertama lajur Totaldi Excel table OT.
  2. Tekan pada rujukan B3 pada baris formula di bahagian atas worksheet. Tekan perlahan - lahan sebanyak dua kali.
  3. Tekan butang Reference Typepada toolbar terapung yang muncul.
  4. Tekan pada pilihan pertama $A$1yang melambangkan alamat cell absolute. 
  5. Perhatikan rujukan cell B3 di bar formula berubah menjadi rujukan alamat absolute $B$3.
  6. Tekan butang hijau betul.
  7. Perhatikan bagaimana semua hasil formula pada lajur Total kini ada jawapan yang betul.

Periksa Formula Absolute Address

Jika kita sekarang memeriksa formula pada setiap cell di lajur Total, kita akan dapat lihat bahawa semuanya menggunakan alamat cell $B$3 pada Hourly Rate. Ini adalah alamat cell absolute dan ianya memastikan setiap formula merujuk kepada cell yang sama. Ini digambarkan dalam animasi di bawah. Alamat ini berubah dari alamat sebelumnya yang ditulis sebagai B3 sahaja yang merupakan alamat cell relatif dan diubah oleh Excel bagi setiap formula.

Penutup

Dalam pelajaran ini anda telah belajar bagaimana merujuk kepada cell di luar Excel table dalam mencipta formula. Anda juga sudah ketahui bagaimana alamat cell relatif tidak selalunya sesuai digunakan dalam formula Excel dan bagaimana kadangkala alamat cell relatif perlu ditukar kepada alamat cell absolute untuk menghasilkan jawapan yang betul. Anda kini sudah tahu bagaimana hendak melakukan semua ini.

Tugasan

Sebagai tugasan bagi pelajaran ini, sila hantar fail Excel Salary Calculator kepada pensyarah supaya beliau boleh memeriksa kerja anda.

Tagged