Excel 2016 - Pelajaran 15: Rujukan sel relatif dan mutlak

• Excel 2016 (Bahagian 12): Format halaman dan cetak hamparan
• Excel 2016 (Bahagian 13): Pengenalan kepada formula
• Excel 2016 (Bahagian 14): Cipta formula kompleks

Dalam Excel terdapat dua jenis rujukan sel: relatif dan mutlak . Rujukan relatif dan mutlak berkelakuan berbeza apabila menyalin dan mengisi sel secara berbeza. Rujukan relatif berubah apabila formula disalin ke dalam sel lain. Walau bagaimanapun, rujukan mutlak kekal tidak berubah tidak kira di mana ia disalin.

Sila sertai LuckyTemplates dalam artikel ini dengan arahan tentang rujukan sel relatif dan mutlak dalam Excel 2016 !

Rujukan sel relatif dan mutlak dalam Excel

• Rujukan relatif
  • Cipta dan salin formula yang menggunakan rujukan relatif
• Rujukan mutlak
  • Cipta dan salin formula dengan rujukan mutlak
• Gunakan rujukan sel dengan berbilang lembaran kerja
  • Sel rujukan pada lembaran kerja

Tonton video di bawah untuk mengetahui lebih lanjut tentang rujukan sel:

Rujukan relatif

Secara lalai, semua rujukan sel adalah rujukan relatif. Apabila disalin merentas berbilang sel, ia berubah berdasarkan kedudukan relatif baris dan lajur. Sebagai contoh, jika anda menyalin formula =A1+B1 dari baris 1 ke baris 2, formula menjadi =A2+B2 . Rujukan relatif amat mudah apabila anda perlu mengulangi pengiraan yang sama merentas berbilang baris atau lajur.

Cipta dan salin formula yang menggunakan rujukan relatif

Dalam contoh di bawah, kami ingin mencipta formula yang akan mendarabkan harga setiap item dengan kuantiti. Daripada mencipta formula baharu untuk setiap item, kita boleh mencipta formula tunggal dalam sel D2 dan kemudian menyalinnya ke baris lain. Kami akan menggunakan rujukan relatif untuk mengira jumlah bagi setiap item dengan tepat.

1. Pilih sel yang mengandungi formula. Dalam contoh, kami akan memilih sel D4 .

2. Masukkan formula untuk mengira nilai yang dikehendaki. Dalam contoh, kami akan menaip =B4*C4 .

3. Tekan Enter pada papan kekunci. Formula akan dikira dan hasilnya akan dipaparkan dalam sel.

4. Cari pemegang isian di sudut kanan bawah sel yang dikehendaki. Dalam contoh, kami akan mencari pemegang isian untuk sel D4.

5. Klik dan seret pemegang isian ke sel yang ingin anda isi. Dalam contoh, kami akan memilih sel D5:D13 .

6. Lepaskan tetikus. Formula akan disalin ke dalam sel yang dipilih dengan rujukan relatif, memaparkan keputusan dalam setiap sel.

Anda boleh klik dua kali pada sel yang diisi untuk menyemak formula tepatnya. Rujukan relatif untuk setiap sel harus berbeza, bergantung pada baris.

Rujukan mutlak

Kadangkala anda mungkin tidak mahu menukar rujukan sel semasa menyalin ke sel lain. Tidak seperti rujukan relatif, rujukan mutlak tidak berubah apabila disalin atau diisi. Anda boleh menggunakan rujukan mutlak untuk memastikan baris dan/atau lajur berturut-turut.

Rujukan mutlak dinyatakan dalam formula dengan menambahkan tanda dolar ($) . Ia boleh mendahului rujukan lajur, rujukan baris atau kedua-duanya.

Anda biasanya akan menggunakan format $A$2 apabila mencipta formula yang mengandungi rujukan mutlak. Dua format lain digunakan kurang kerap.

Apabila menulis formula, anda boleh menekan kekunci F4 pada papan kekunci anda untuk bertukar antara rujukan sel relatif dan rujukan sel mutlak, seperti yang ditunjukkan dalam video di bawah. Ini adalah cara mudah untuk memasukkan rujukan mutlak dengan cepat.

Cipta dan salin formula dengan rujukan mutlak

Dalam contoh di bawah, kami akan menggunakan sel E2 (yang mempunyai kadar cukai 7.5%) untuk mengira cukai jualan bagi setiap item dalam lajur D. Untuk memastikan rujukan kepada kadar cukai kekal malar - walaupun formula disalin dan diisi ke dalam sel lain - kita perlu menjadikan sel $E$2 sebagai rujukan mutlak.

1. Pilih sel yang mengandungi formula. Dalam contoh, kami akan memilih sel D4 .

2. Masukkan formula untuk mengira nilai yang dikehendaki. Dalam contoh, kami akan memasukkan =(B4*C4)*$E$2 , menjadikan $E$2 sebagai rujukan mutlak.

3. Tekan Enter pada papan kekunci. Formula akan mengira dan hasilnya akan dipaparkan dalam sel.

4. Cari pemegang isian di sudut kanan bawah sel yang dikehendaki. Dalam contoh, kami akan mencari pemegang isian untuk sel D4.

5. Klik dan seret pemegang isian ke sel yang anda ingin isi (sel D5:D13 dalam contoh).

6. Lepaskan tetikus. Formula akan disalin ke sel yang dipilih dengan rujukan mutlak, dan nilai akan dikira dalam setiap sel.

Anda boleh klik dua kali pada sel yang diisi untuk menyemak formula tepatnya. Rujukan mutlak dalam setiap sel hendaklah sama, manakala rujukan lain adalah relatif kepada baris sel.

Pastikan anda memasukkan tanda dolar ($) setiap kali anda membuat rujukan mutlak merentas berbilang sel. Tanda dolar telah ditinggalkan dalam contoh di bawah. Ini menyebabkan Excel mentafsirnya sebagai rujukan relatif, menghasilkan hasil yang salah apabila menyalin ke sel lain.

Gunakan rujukan sel dengan berbilang lembaran kerja

Excel membolehkan anda merujuk kepada mana-mana sel pada lembaran kerja, yang boleh sangat berguna jika anda ingin merujuk kepada nilai tertentu daripada lembaran kerja lain. Untuk melakukan ini, anda hanya mulakan rujukan sel dengan nama lembaran kerja diikuti dengan tanda seru (!) . Sebagai contoh, jika anda ingin merujuk kepada sel A1 pada Helaian1 , rujukan selnya ialah Helaian1!A1 .

Ambil perhatian bahawa jika nama lembaran kerja mengandungi ruang , anda perlu memasukkan kurungan (' ') di sekeliling nama itu. Sebagai contoh, jika anda ingin merujuk sel A1 pada lembaran kerja yang dipanggil Belanjawan Julai , rujukan selnya ialah 'Belanjawan Julai'!A1 .

Sel rujukan pada lembaran kerja

Dalam contoh di bawah, kami akan merujuk kepada sel dengan nilai yang dikira antara dua jadual. Ini akan membolehkan kami menggunakan nilai tepat pada dua hamparan berbeza tanpa menulis semula formula atau menduplikasi data.

1. Cari sel yang anda ingin rujuk dan catatkan lembaran kerjanya. Dalam contoh kami, kami ingin merujuk sel E14 pada lembaran kerja Pesanan Menu .

2. Navigasi ke hamparan yang dikehendaki. Dalam contoh kami, kami akan memilih hamparan invois Katering .

3. Cari dan pilih sel di mana anda mahu nilai dipaparkan. Dalam contoh, kami akan memilih sel C4 .

4. Taipkan tanda sama dengan (=) , nama jadual diikuti dengan tanda seru (!) dan alamat sel. Dalam contoh, kami akan menaip ='MenuOrder'!E14 .

5. Tekan Enter pada papan kekunci. Nilai sel yang dirujuk muncul. Sekarang, jika nilai sel E14 berubah pada lembaran kerja Pesanan Menu, ia akan dikemas kini secara automatik pada lembaran kerja invois Katering.

Jika anda menamakan semula lembaran kerja anda kemudian, rujukan sel akan dikemas kini secara automatik untuk menggambarkan nama lembaran kerja baharu.

Jika anda memasukkan nama jadual yang salah, #REF! akan muncul di dalam kotak. Dalam contoh, kami mengelirukan nama lembaran kerja. Untuk mengedit, mengabaikan atau menyiasat ralat, klik butang Ralat di sebelah sel dan pilih pilihan daripada menu.

Rujuk beberapa artikel lagi:

• Excel 2016 (Bahagian 3): Cara membuat hamparan baharu dan membuka hamparan sedia ada
• Excel 2016 (Bahagian 4): Cara menyimpan dan berkongsi hamparan
• Excel 2016 (Bahagian 5): Asas sel dan julat

Bersenang-senang!