Cara Mencari Rujukan Pekeliling Dalam Excel

Apabila bekerja dengan Microsoft Excel , anda mungkin kadangkala menghadapi apa yang dikenali sebagai rujukan bulat. Ini adalah apabila formula dalam sel merujuk semula kepada dirinya sendiri, menyebabkan gelung yang tidak berkesudahan dalam proses pengiraan.

Untuk mencari rujukan bulat dalam Excel, gunakan alat "Pemeriksaan Ralat" di bawah tab "Formula" untuk mengenal pasti sel dengan pergantungan bulat.

Artikel ini menunjukkan kepada anda cara mencari rujukan bulat dalam Excel dan membetulkannya. Anda juga akan belajar cara menghalangnya apabila bekerja dengan lembaran kerja yang besar atau kompleks.

Isi kandungan

• Apakah Rujukan Pekeliling
• Tetingkap Mesej Ralat Rujukan Pekeliling
• Cara Mencari Rujukan Pekeliling dalam Excel
• Cara Mencari Rujukan Pekeliling Dengan Bar Status Excel
• Cara Mencari Rujukan Pekeliling Tersembunyi Dalam Excel
  • Cara Menggunakan Alat Bergantung Trace
  • Alat Trace Precedents
• 2 Cara Cara Buang Rujukan Pekeliling Dalam Excel
  • 2. Gunakan Formula Alternatif
• 5 Petua Untuk Mencegah Rujukan Pekeliling
  • 1. Berhati-hati Semasa Memilih Julat
  • 2. Rancang formula anda dengan teliti
  • 3. Gunakan formula alternatif
  • 4. Pecahkan formula kompleks kepada bahagian yang lebih kecil
  • 5. Selesaikan masalah semasa anda pergi
• Bagaimana untuk Mendayakan Pengiraan Berulang & Mengapa Bersusah payah?

Apakah Rujukan Pekeliling

Rujukan pekeliling berlaku dalam Excel apabila pengiraan formula dalam sel bergantung pada nilai sel itu sendiri. Ini mewujudkan gelung dan membawa kepada keputusan yang salah atau tidak dapat diramalkan.

Ini lebih mudah difahami dengan contoh mudah.

Katakan anda mempunyai dua nombor dalam sel A1 dan B1. Anda ingin menggunakan formula dalam sel A3 yang menambah dua nombor bersama-sama. Formula dalam sel A3 sepatutnya kelihatan seperti ini:

=A1 + B1

Walau bagaimanapun, jika anda membuat kesilapan dan memasukkan C1 dalam pengiraan, anda kini mempunyai rujukan pekeliling:

=A1 + B1 + C1

Ini mungkin masalah yang jelas untuk dielakkan apabila berurusan dengan dua sel. Walau bagaimanapun, apabila anda bekerja dengan julat sel yang besar, kesilapan menjadi lebih berkemungkinan.

Syukurlah, Excel direka untuk mengendalikan situasi sedemikian dengan memaparkan mesej amaran apabila rujukan pekeliling dikesan.

Walau bagaimanapun, dalam beberapa kes, anda mungkin menghadapi rujukan bulat tanpa menerima sebarang makluman. Oleh itu, adalah penting untuk mengetahui cara mencari dan menyelesaikan isu ini secara manual.

Tetingkap Mesej Ralat Rujukan Pekeliling

Secara lalai, Excel memantau rujukan bulat semasa anda memasukkan formula ke dalam sel. Apabila anda menekan enter dalam sel dengan masalah, tetingkap pop timbul akan memaparkan mesej ralat rujukan bulat.

Ini adalah teks penjelasan:

Terdapat satu atau lebih rujukan pekeliling di mana formula merujuk kepada selnya sendiri sama ada secara langsung atau tidak langsung. Ini mungkin menyebabkan mereka tersalah mengira. Cuba alih keluar atau tukar rujukan ini atau alihkan formula ke sel yang berbeza.

Apabila anda melihat kotak dialog ini, anda harus mengklik "OK" dan menggunakan kaedah dalam artikel ini untuk menyelesaikan masalah.

Walau bagaimanapun, anda tidak perlu membetulkan ralat dengan segera. Anda boleh terus bekerja dengan hamparan dengan niat untuk menyelesaikan masalah itu kemudian.

Dalam kes itu, anda perlu tahu cara mencari rujukan pekeliling dalam Excel selepas anda mengetepikan kotak amaran.

Cara Mencari Rujukan Pekeliling dalam Excel

Excel mempunyai alat semakan ralat terbina dalam yang menemui beberapa jenis ralat, termasuk rujukan bulat. Anda mengakses alat pada tab Formula.

Gambar ini menunjukkan lokasi dalam kumpulan Pengauditan Formula.

Kini anda tahu cara mencari alat tersebut, berikut ialah langkah terperinci untuk menggunakannya:

1. Pergi ke tab Formula.

1. Klik pada menu lungsur Semakan Ralat dalam kumpulan Pengauditan Formula.

2. Pilih "Rujukan Pekeliling" daripada senarai juntai bawah.

3. Klik pautan untuk melompat kursor anda ke sel pertama dengan isu.

Gambar di bawah menunjukkan bahawa alat telah menemui ralat bulat dalam sel G1. Sel yang dipaparkan ialah hiperpautan.

Apabila anda mengklik pautan, kursor anda bergerak ke sel. Ini sangat membantu dengan jumlah data yang besar.

Sebaik sahaja anda menyelesaikan rujukan pekeliling pertama, anda boleh kembali ke alat Penyemakan Ralat untuk mencari rujukan seterusnya pada helaian.

Gunakan langkah-langkah dalam bahagian kemudian artikel ini untuk menyelesaikan isu dan gunakan alat untuk pergi ke masalah seterusnya.

Jika tiada lagi isu pekeliling, item menu "Rujukan Pekeliling" dalam menu lungsur turun akan dikelabukan. Anda tiada kerja lagi untuk dilakukan.

Cara Mencari Rujukan Pekeliling Dengan Bar Status Excel

Satu lagi cara untuk melihat rujukan bulat ialah melihat bar status di bahagian bawah lembaran kerja Excel.

Apabila terdapat satu atau lebih isu dalam lembaran kerja, bar status memaparkan lokasi satu rujukan sel masalah.

Sebagai contoh, jika terdapat rujukan bulat dalam sel C1 dan tiada isu lain dengan hamparan, bar status akan berbunyi: "Rujukan Pekeliling: C1".

Gambar ini menunjukkan kepada anda tempat untuk melihat paparan.

Cara Mencari Rujukan Pekeliling Tersembunyi Dalam Excel

Rujukan pekeliling tersembunyi dalam Excel ialah rujukan pekeliling yang tidak dikesan serta-merta oleh ciri ralat terbina. Dengan kata lain, Excel tidak memaparkan amaran standard.

Ia berlaku apabila sel merujuk sel lain secara tidak langsung, melalui satu siri sel perantaraan, dan akhirnya merujuk kembali kepada sel asal.

Excel mempunyai dua alatan yang membantu anda mengesan perhubungan dan menyiasat isu ini:

1. Alat Tanggungan Jejak

2. Alat Trace Precedents

Cara Menggunakan Alat Bergantung Trace

Pengguna Excel yang berpengalaman memanfaatkan alat Trace Dependents untuk menyiasat isu ini dan membetulkan rujukan pekeliling. Dengan memilih satu atau lebih sel dan menggunakan alat ini, Excel akan menunjukkan kepada anda sel bergantung.

Alat ini tersedia dalam bahagian Pengauditan Formula pada tab Formula.

Untuk menggunakan alat, ikuti langkah berikut:

1. Klik pada sel atau sel yang anda ingin kesan tanggungan.

2. Pergi ke tab 'Formula' dalam reben Excel.

3. Klik pada butang 'Trace Dependents' dalam kumpulan 'Formula Auditing'.

4. Semak anak panah pengesan atau anak panah yang menunjukkan kebergantungan.

Excel menunjukkan anak panah daripada nilai sel aktif kepada mana-mana sel yang bergantung padanya. Anak panah biru dalam gambar di bawah menunjukkan bahawa sel D6 mempunyai kebergantungan pada sel B2.

Alat Trace Precedents

Preseden adalah bertentangan dengan tanggungan. Alat Trace Precedents membolehkan anda memilih sel dengan formula dan melihat semua sel yang bergantung padanya iaitu preseden.

Alat ini tersedia dalam bahagian Pengauditan Formula pada tab Formula.

Untuk menggunakan alat, ikuti langkah berikut:

1. Klik pada sel yang mengandungi formula yang anda ingin siasat.

2. Pergi ke tab Formula dalam reben Excel.

3. Klik pada butang 'Trace Precedents' dalam kumpulan 'Formula Auditing'.

4. Semak anak panah jejak yang dipaparkan oleh Excel.

Anak panah biru dalam gambar di bawah menunjukkan kepada anda bahawa sel D6 bergantung pada julat sel A2:B5.

Menggunakan kedua-dua alatan membantu anda mencari rujukan pekeliling Excel yang tidak kelihatan serta-merta. Langkah seterusnya ialah menyelesaikan masalah dalam sel yang dipilih. Anda kemudian boleh terus mencari masalah yang sama dalam sel lain.

2 Cara Cara Buang Rujukan Pekeliling Dalam Excel

Terdapat dua kaedah utama untuk menyelesaikan rujukan pekeliling:

• melanggar rujukan pekeliling

• menggunakan formula alternatif

Mari kita jelaskan kedua-dua cara.

1. Memecah Rujukan Pekeliling

Memecahkan rujukan bulat dalam Excel ialah cara yang cekap untuk menyelesaikan masalah. Ini memerlukan anda untuk mengenal pasti sel yang terlibat dalam rujukan bulat dan mengubah suai formulanya dengan sewajarnya.

Untuk melakukan ini, ikuti langkah yang diterangkan sebelum ini untuk mencari alamat sel.

Anda kemudian harus memeriksa formula dalam Formula Bar. Cari rujukan kepada sel itu sendiri.

Dalam contoh pertama rujukan pekeliling kami, formula penambahan dalam sel C1 termasuk rujukan kepada sel itu sendiri:

=A1 + B1 + C1

Ini boleh diperbaiki dengan mengalih keluar tambahan terakhir. Setelah masalah ini diselesaikan, anda teruskan mencari rujukan pekeliling lain dan menanganinya.

2. Gunakan Formula Alternatif

Antara punca rujukan pekeliling yang paling biasa ialah apabila menjumlahkan atau purata sel. Kesilapannya ialah memasukkan sel hasil dalam formula.

Dalam contoh kami sebelum ini, formula menggunakan operator penambahan untuk mengira jumlah julat sel. Kaedah alternatif ialah menggunakan fungsi SUM Excel.

Untuk membetulkan contoh, anda menggantikan formula dengan:

=SUM(A1:B1)

Begitu juga, anda boleh purata julat dengan formula seperti =(A1+B1)/2. Walau bagaimanapun, fungsi AVERAGE kurang berkemungkinan membawa kepada kesilapan.

=PURATA(A1:B1)

5 Petua Untuk Mencegah Rujukan Pekeliling

Untuk mengelakkan rujukan bulat dalam Microsoft Excel, adalah penting untuk mempunyai pemahaman yang jelas tentang cara formula anda berinteraksi antara satu sama lain dalam helaian Excel anda.

Dengan mengenal pasti hubungan antara sel dan mengetahui cara sambungan ini berfungsi bersama, anda boleh mengelak daripada mencipta formula bulat.

Berikut ialah lima petua terbaik kami untuk membantu anda mencegah rujukan pekeliling.

1. Berhati-hati Semasa Memilih Julat

Mengikut pengalaman saya, salah satu punca rujukan pekeliling yang paling biasa ialah apabila pengguna dengan cepat memilih julat untuk disertakan dalam formula mereka.

Apabila meleret secara menegak dan/atau mendatar untuk mengumpulkan sel, mudah untuk memilih satu lagi sel yang bersebelahan dengan julat yang betul. Jika sel itu juga merupakan sel hasil, anda telah mencipta rujukan bulat secara tidak sengaja.

Kesilapan ini biasanya dibuat apabila bekerja dengan set data yang besar, seperti menjalankan ujian khi kuasa dua. Penyelesaiannya adalah berhati-hati dengan pemilihan julat anda.

2. Rancang formula anda dengan teliti

Sebelum memasukkan formula, fikirkan tentang logik di sebalik pengiraan anda dan bagaimana ia harus mengalir merentasi sel.

Anda boleh memetakan pengiraan terlebih dahulu menggunakan alat gambar rajah atau papan putih.

Ini boleh membantu anda mengenal pasti sebarang kemungkinan gelung yang mungkin membawa kepada rujukan bulat.

3. Gunakan formula alternatif

Fungsi tertentu, seperti INDIRECT atau OFFSET , secara tidak sengaja boleh membuat rujukan bulat apabila digunakan secara tidak wajar.

Pertimbangkan untuk menggunakan fungsi alternatif yang menyediakan kefungsian serupa tanpa risiko rujukan pekeliling.

4. Pecahkan formula kompleks kepada bahagian yang lebih kecil

Daripada mencipta formula kompleks tunggal dengan berbilang fungsi bersarang, pertimbangkan untuk membahagikan pengiraan anda kepada bahagian yang lebih kecil dan lebih mudah diurus.

Ini bukan sahaja menjadikan buku kerja anda lebih mudah difahami dan diselenggara tetapi juga mengurangkan peluang untuk memperkenalkan rujukan pekeliling.

5. Selesaikan masalah semasa anda pergi

Apabila bekerja dengan buku kerja yang besar atau rumit, adalah penting untuk memantau rujukan pekeliling. Daripada mengabaikan isu sehingga satu set data dan formula yang besar telah dipasang, lebih baik untuk membetulkan setiap ralat apabila ia timbul.

Anda boleh memilih untuk mengabaikan mesej amaran rujukan bulat untuk seketika. Walau bagaimanapun, anda harus menggunakan ciri Semakan Ralat untuk mengejar rujukan pekeliling sedia ada sebelum terlalu lama.

Ini menjadikan mereka lebih mudah untuk membetulkan dan menghalang kesannya pada buku kerja anda.

Bagaimana untuk Mendayakan Pengiraan Berulang & Mengapa Bersusah payah?

Anda biasanya ingin mengelakkan rujukan bulat dalam lembaran kerja anda. Walau bagaimanapun, terdapat beberapa senario di mana ia perlu.

Sebagai contoh, beberapa pengiraan kejuruteraan atau saintifik perlu menggunakan hasil pengiraan sebelumnya. Ini dikenali sebagai penyelesaian berulang.

Setiap gelung memperhalusi hasil pengiraan sehingga ambang atau sasaran tertentu dicapai.

Dalam kes ini, adalah penting untuk mendayakan Pengiraan Berulang dalam tetapan Excel untuk membenarkan rujukan pekeliling yang dimaksudkan. Ini membolehkan perisian untuk mengulangi pengiraan beberapa kali tertentu, menumpu kepada penyelesaian.

Ikuti langkah-langkah ini untuk membuat rujukan pekeliling berfungsi dalam fail Excel anda:

1. Klik tab Fail.

2. Pilih Pilihan.

3. Pilih kategori Formula.

4. Di bawah bahagian 'Pilihan pengiraan', tandai kotak untuk 'Dayakan pengiraan berulang'.

5. Tukar nombor dalam kotak lelaran maksimum atau biarkan sebagai lalai 100.

6. Gunakan kotak perubahan maksimum untuk mengurangkan lelaran mengikut keperluan anda.