Salah satu amaran ralat yang paling biasa dihadapi pengguna dalam Excel ialah 'Rujukan Pekeliling'. Beribu-ribu pengguna mempunyai masalah yang sama, dan ia berlaku apabila formula merujuk kembali ke selnya sendiri secara langsung atau tidak langsung, menyebabkan gelung pengiraan yang tidak berkesudahan.
Sebagai contoh, anda mempunyai dua nilai dalam sel B1 dan B2. Apabila formula =B1+B2 dimasukkan ke dalam B2, ia mewujudkan rujukan bulat; formula dalam B2 berulang kali mengira semula dirinya kerana setiap kali ia mengira, nilai B2 telah berubah.
Kebanyakan rujukan pekeliling adalah kesilapan yang tidak disengajakan; Excel akan memberi amaran kepada anda tentang perkara ini. Walau bagaimanapun, terdapat juga rujukan pekeliling yang dimaksudkan, yang digunakan untuk membuat pengiraan berulang. Rujukan pekeliling yang tidak diingini dalam lembaran kerja anda mungkin menyebabkan formula anda tersalah kira.
Oleh itu, dalam artikel ini, kami akan menerangkan semua yang anda perlu ketahui tentang rujukan bulatan, dan juga cara mencari, membetulkan, mengalih keluar dan menggunakan rujukan bulat dalam Excel.
Cara Mencari dan Mengendalikan Rujukan Pekeliling dalam Excel
Apabila bekerja dengan Excel, kadangkala kami menghadapi ralat rujukan bulat yang berlaku apabila anda memasukkan formula yang merangkumi sel tempat formula anda berada. Pada asasnya, ia berlaku apabila formula anda cuba mengira sendiri.
Sebagai contoh, anda mempunyai lajur nombor dalam sel A1:A4 dan anda menggunakan fungsi SUM (=SUM(A1:A5)) dalam sel A5. Sel A5 secara langsung merujuk kepada selnya sendiri, yang salah. Oleh itu, anda akan mendapat amaran rujukan pekeliling berikut:
Sebaik sahaja anda mendapat mesej amaran di atas, anda boleh mengklik butang 'Bantuan' untuk mengetahui lebih lanjut tentang ralat, atau menutup tetingkap mesej ralat dengan mengklik sama ada butang 'OK' atau 'X' dan dapatkan '0' sebagai hasilnya.
Kadangkala gelung rujukan bulat boleh menyebabkan pengiraan anda ranap atau melambatkan prestasi lembaran kerja anda. Rujukan pekeliling juga boleh membawa kepada beberapa isu lain, yang tidak akan nyata dengan serta-merta. Oleh itu, adalah lebih baik untuk mengelakkan perkara ini.
Rujukan Pekeliling Langsung dan Tidak Langsung
Rujukan Pekeliling boleh dikategorikan kepada dua jenis: Rujukan Pekeliling Langsung dan Rujukan Pekeliling Tidak Langsung.
Rujukan Langsung
Rujukan pekeliling langsung agak mudah. Mesej amaran rujukan bulat langsung muncul apabila formula merujuk kembali ke selnya sendiri secara langsung.
Dalam contoh di bawah, formula dalam sel A2 secara langsung merujuk kepada selnya sendiri (A2).
Sebaik sahaja mesej amaran muncul, anda boleh mengklik pada 'OK', tetapi ia hanya akan menghasilkan '0'.
Rujukan Pekeliling Tidak Langsung
Rujukan pekeliling tidak langsung dalam Excel berlaku apabila nilai dalam formula merujuk kembali kepada selnya sendiri, tetapi tidak secara langsung. Dalam erti kata lain, rujukan bulat boleh dibentuk oleh dua sel yang merujuk satu sama lain.
Mari kita jelaskan dengan contoh mudah ini.
Sekarang nilainya bermula dari A1 yang mempunyai nilai 20.
Seterusnya, sel C3 merujuk kepada sel A1.
Kemudian, sel A5 merujuk kepada sel C3.
Sekarang gantikan nilai 20 dalam sel A1 dengan formula seperti yang ditunjukkan di bawah. Setiap sel lain bergantung kepada sel A1. Apabila anda menggunakan rujukan mana-mana sel formula sebelumnya yang lain dalam A1, ia akan menyebabkan amaran rujukan bulat. Kerana, formula dalam A1 merujuk kepada sel A5, yang merujuk kepada C3, dan sel C3 merujuk kembali kepada A1, oleh itu rujukan bulat.
Apabila anda mengklik 'OK', ia menghasilkan nilai 0 dalam sel A1 dan Excel mencipta baris terpaut yang menunjukkan Precedent Trace dan Trace Dependents seperti yang ditunjukkan di bawah. Kami boleh menggunakan ciri ini untuk mencari dan membetulkan/mengalih keluar rujukan pekeliling dengan mudah.
Cara Mendayakan / Melumpuhkan Rujukan Pekeliling dalam Excel
Secara lalai, pengiraan lelaran dimatikan (dilumpuhkan) dalam Excel. Pengiraan berulang ialah pengiraan berulang sehingga ia memenuhi syarat tertentu. Apabila ia dilumpuhkan, Excel menunjukkan mesej Rujukan Pekeliling dan mengembalikan 0 sebagai hasilnya.
Walau bagaimanapun, kadangkala rujukan bulat diperlukan untuk mengira gelung. Untuk menggunakan rujukan bulat, anda mesti mendayakan pengiraan berulang dalam Excel anda dan ia akan membolehkan anda melakukan pengiraan anda. Sekarang, izinkan kami menunjukkan kepada anda cara anda boleh mendayakan atau melumpuhkan pengiraan berulang.
Dalam Excel 2010, Excel 2013, Excel 2016, Excel 2019 dan Microsoft 365, pergi ke tab 'Fail' di penjuru kiri sebelah atas Excel, kemudian klik 'Pilihan' dalam anak tetingkap kiri.
Pada tetingkap Excel Options, pergi ke tab 'Formula' dan tandakan kotak semak 'Dayakan pengiraan lelaran' di bawah bahagian 'Pilihan pengiraan'. Kemudian klik 'OK' untuk menyimpan perubahan.
Ini akan membolehkan pengiraan berulang dan dengan itu membenarkan rujukan bulat.
Untuk mencapai ini dalam versi Excel yang biasa ikuti langkah berikut:
- Dalam Excel 2007, klik pada butang Office > Pilihan Excel > Formula > Kawasan Lelaran.
- Dalam Excel 2003 dan versi terdahulu, anda perlu pergi ke Menu > Alat > Pilihan > tab Pengiraan.
Lelaran Maksimum & Parameter Perubahan Maksimum
Sebaik sahaja anda mendayakan pengiraan lelaran, anda boleh mengawal pengiraan lelaran, dengan menyatakan dua pilihan yang tersedia di bawah bahagian Dayakan pengiraan lelaran seperti yang ditunjukkan dalam tangkapan skrin di bawah.
- Lelaran Maksimum – Nombor ini menentukan berapa kali formula perlu dikira semula sebelum memberikan anda hasil akhir. Nilai lalai ialah 100. Jika anda menukarnya kepada '50', Excel akan mengulangi pengiraan sebanyak 50 kali sebelum memberikan anda hasil akhir. Ingat bahawa semakin tinggi bilangan lelaran, lebih banyak sumber dan masa yang diperlukan untuk mengira.
- Perubahan Maksimum – Ia menentukan perubahan maksimum antara hasil pengiraan. Nilai ini menentukan ketepatan keputusan. Lebih kecil bilangannya, lebih tepat hasil keputusannya dan lebih lama masa yang diperlukan untuk mengira lembaran kerja.
Jika pilihan pengiraan berulang didayakan, anda tidak akan mendapat sebarang amaran apabila terdapat rujukan bulat dalam lembaran kerja anda. Hanya dayakan pengiraan interaktif apabila ia benar-benar perlu.
Cari Rujukan Pekeliling dalam Excel
Katakan anda mempunyai set data yang besar dan anda mendapat amaran rujukan bulat, anda masih perlu mengetahui di mana (dalam sel mana) ralat telah berlaku untuk membetulkannya. Untuk mencari rujukan bulat dalam Excel, ikuti langkah berikut:
Menggunakan Alat Semak Ralat
Mula-mula, buka lembaran kerja di mana rujukan pekeliling telah berlaku. Pergi ke tab 'Formula', klik pada anak panah di sebelah alat 'Ralat Semakan'. Kemudian hanya tuding kursor pada pilihan 'Rujukan Pekeliling', Excel akan menunjukkan kepada anda senarai semua sel yang terlibat dalam rujukan bulat seperti yang ditunjukkan di bawah.
Klik pada mana-mana alamat sel yang anda mahu dalam senarai dan ia akan membawa anda ke alamat sel tersebut untuk menyelesaikan isu tersebut.
Menggunakan Bar Status
Anda juga boleh mencari rujukan pekeliling di bar Status. Pada bar status Excel, ia akan menunjukkan kepada anda alamat sel terkini dengan rujukan bulat, seperti 'Rujukan Pekeliling: B6' (lihat tangkapan skrin di bawah).
Terdapat beberapa perkara yang perlu anda ketahui semasa mengendalikan rujukan pekeliling:
- Bar status tidak akan menunjukkan alamat sel rujukan bulat apabila pilihan Pengiraan Berulang didayakan, jadi anda perlu melumpuhkannya sebelum anda mula melihat buku kerja untuk rujukan bulat.
- Sekiranya rujukan pekeliling tidak ditemui dalam helaian aktif, bar status hanya memaparkan 'Rujukan Pekeliling' tanpa alamat sel.
- Anda hanya akan mendapat gesaan rujukan pekeliling sekali dan selepas anda mengklik 'OK', gesaan itu tidak akan dipaparkan lagi pada kali seterusnya.
- Jika buku kerja anda mempunyai rujukan bulat, ia akan menunjukkan kepada anda gesaan setiap kali anda membukanya sehingga anda menyelesaikan rujukan bulat atau sehingga anda menghidupkan pengiraan berulang.
Alih keluar Rujukan Pekeliling dalam Excel
Mencari rujukan Pekeliling adalah mudah tetapi membetulkannya tidak semudah itu. Malangnya, tiada pilihan dalam Excel yang membolehkan anda mengalih keluar semua rujukan bulatan sekaligus.
Untuk membetulkan rujukan pekeliling, anda perlu mencari setiap rujukan pekeliling secara individu dan cuba mengubah suainya, mengalih keluar formula pekeliling sama sekali atau menggantikannya dengan yang lain.
Kadangkala, dalam formula mudah, anda hanya perlu melaraskan semula parameter formula supaya ia tidak merujuk semula kepada dirinya sendiri. Sebagai contoh, tukar formula dalam B6 kepada =SUM(B1:B5)*A5 (menukar B6 kepada B5).
Ia akan mengembalikan hasil pengiraan sebagai '756'.
Dalam kes apabila rujukan pekeliling Excel sukar dicari, anda boleh menggunakan ciri Precedent Trace dan Trace Dependents untuk mengesannya kembali ke sumber dan menyelesaikannya satu demi satu. Anak panah menunjukkan sel mana yang dipengaruhi oleh sel aktif.
Terdapat dua kaedah pengesanan yang boleh membantu anda memadamkan rujukan bulat dengan menunjukkan hubungan antara formula dan sel.
Untuk mengakses kaedah pengesanan, pergi ke tab 'Formula', kemudian klik sama ada 'Trace Precedents' atau 'Trace Dependents' dalam kumpulan Pengauditan Formula.
Jejak Preseden
Apabila anda memilih pilihan ini, ia menjejaki kembali sel yang mempengaruhi nilai sel aktif. Ia melukis garis biru yang menunjukkan sel mana yang mempengaruhi sel semasa. Kekunci pintasan untuk menggunakan preseden surih ialah Alt + T U T.
Dalam contoh di bawah, anak panah biru menunjukkan sel yang mempengaruhi nilai B6 ialah B1:B6 dan A5. Seperti yang anda lihat di bawah, sel B6 juga merupakan sebahagian daripada formula, yang menjadikannya rujukan bulat dan menyebabkan formula mengembalikan '0' sebagai hasilnya.
Ini boleh diperbaiki dengan mudah dengan menggantikan B6 dengan B5 dalam hujah SUM: =SUM(B1:B5).
Jejak Tanggungan
Ciri tanggungan surih mengesan sel yang bergantung pada sel yang dipilih. Ciri ini melukis garis biru yang menunjukkan sel mana yang dipengaruhi oleh sel yang dipilih. Maksudnya, ia memaparkan sel yang mengandungi formula yang merujuk kepada sel aktif. Kekunci pintasan untuk menggunakan tanggungan ialah Alt + T U D.
Dalam contoh berikut, sel D3 dipengaruhi oleh B4. Ia bergantung kepada B4 untuk nilainya untuk menghasilkan keputusan. Oleh itu, bergantung surih melukis garis biru dari B4 ke D3, menunjukkan bahawa D3 bergantung kepada B4.
Sengaja Menggunakan Rujukan Pekeliling dalam Excel
Menggunakan rujukan Pekeliling dengan sengaja tidak disyorkan, tetapi mungkin terdapat beberapa kes yang jarang berlaku di mana anda memerlukan rujukan pekeliling supaya anda boleh mendapatkan output yang anda inginkan.
Mari kita jelaskan dengan menggunakan contoh.
Untuk bermula, dayakan 'Pengiraan Berulang' dalam buku kerja Excel anda. Sebaik sahaja anda telah mendayakan Pengiraan Berulang, anda boleh mula menggunakan rujukan bulat untuk kelebihan anda.
Katakan anda membeli rumah dan anda ingin memberikan komisen 2% daripada jumlah kos rumah kepada ejen anda. Jumlah kos akan dikira dalam sel B6 dan peratusan komisen (yuran ejen) dikira dalam B4. Komisen dikira daripada jumlah kos dan jumlah kos termasuk komisen. Oleh kerana sel B4 dan B6 bergantung antara satu sama lain, ia mewujudkan rujukan bulat.
Masukkan formula untuk mengira jumlah kos dalam sel B6:
=SUM(B1:B4)
Oleh kerana jumlah kos termasuk yuran ejen, kami memasukkan B4 dalam formula di atas.
Untuk mengira Yuran Ejen sebanyak 2%, masukkan formula ini dalam B4:
=B6*2%Sekarang formula dalam sel B4 bergantung pada nilai B6 untuk mengira 2% daripada jumlah yuran dan formula dalam B6 bergantung pada B4 untuk mengira jumlah kos (termasuk yuran ejen), maka rujukan pekeliling.
Jika pengiraan berulang didayakan, Excel tidak akan memberi anda amaran atau 0 dalam hasilnya. Sebaliknya, hasil sel B6 dan B4 akan dikira seperti yang ditunjukkan di atas.
Pilihan pengiraan berulang biasanya dilumpuhkan secara lalai. Jika anda tidak menghidupkannya dan apabila anda memasukkan formula dalam B4 yang akan mencipta rujukan bulat. Excel akan mengeluarkan amaran dan apabila anda mengklik 'OK', anak panah pengesan akan ditunjukkan.
itu sahaja. Ini sahaja yang anda perlu tahu tentang rujukan bulat dalam Excel.