Salah satu peringatan kesalahan paling umum yang ditemui pengguna di Excel adalah 'Referensi Melingkar'. Ribuan pengguna memiliki masalah yang sama, dan itu terjadi ketika rumus merujuk kembali ke selnya sendiri secara langsung atau tidak langsung, menyebabkan penghitungan berulang tanpa akhir.
Misalnya, Anda memiliki dua nilai di sel B1 dan B2. Ketika rumus =B1+B2 dimasukkan ke dalam B2, itu membuat referensi melingkar; rumus di B2 berulang kali menghitung ulang sendiri karena setiap kali menghitung, nilai B2 berubah.
Kebanyakan referensi melingkar adalah kesalahan yang tidak disengaja; Excel akan memperingatkan Anda tentang ini. Namun, ada juga referensi melingkar yang dimaksudkan, yang digunakan untuk membuat perhitungan berulang. Referensi melingkar yang tidak diinginkan di lembar kerja Anda mungkin menyebabkan rumus Anda salah menghitung.
Oleh karena itu, dalam artikel ini, kami akan menjelaskan semua yang perlu Anda ketahui tentang referensi melingkar, dan juga cara menemukan, memperbaiki, menghapus, dan menggunakan referensi melingkar di Excel.
Cara Menemukan dan Menangani Referensi Edaran di Excel
Saat bekerja dengan Excel, terkadang kami menemukan kesalahan referensi melingkar yang terjadi saat Anda memasukkan rumus yang menyertakan sel tempat rumus Anda berada. Pada dasarnya, ini terjadi ketika rumus Anda mencoba menghitung sendiri.
Misalnya, Anda memiliki kolom angka di sel A1:A4 dan Anda menggunakan fungsi SUM (=SUM(A1:A5)) di sel A5. Sel A5 secara langsung merujuk ke selnya sendiri, yang salah. Karenanya, Anda akan mendapatkan peringatan referensi melingkar berikut:
Setelah Anda mendapatkan pesan peringatan di atas, Anda dapat mengklik tombol 'Bantuan' untuk mengetahui lebih banyak tentang kesalahan, atau menutup jendela pesan kesalahan dengan mengklik tombol 'OK' atau 'X' dan mendapatkan '0' sebagai hasilnya.
Terkadang loop referensi melingkar dapat menyebabkan perhitungan Anda macet atau memperlambat kinerja lembar kerja Anda. Referensi melingkar juga dapat menyebabkan sejumlah masalah lain, yang tidak akan segera terlihat. Jadi, yang terbaik adalah menghindari ini.
Referensi Edaran Langsung dan Tidak Langsung
Referensi Edaran dapat dikategorikan menjadi dua jenis: Referensi Edaran Langsung dan Referensi Edaran Tidak Langsung.
Referensi Langsung
Referensi melingkar langsung cukup sederhana. Pesan peringatan referensi melingkar langsung muncul ketika rumus merujuk kembali ke selnya sendiri secara langsung.
Dalam contoh di bawah ini, rumus di sel A2 secara langsung merujuk ke selnya sendiri (A2).
Setelah pesan peringatan muncul, Anda dapat mengklik 'OK', tetapi itu hanya akan menghasilkan '0'.
Referensi Edaran Tidak Langsung
Referensi melingkar tidak langsung di Excel terjadi saat nilai dalam rumus merujuk kembali ke selnya sendiri, tetapi tidak secara langsung. Dengan kata lain, referensi melingkar dapat dibentuk oleh dua sel yang saling merujuk.
Mari kita jelaskan dengan contoh sederhana ini.
Sekarang nilainya dimulai dari A1 yang memiliki nilai 20.
Selanjutnya, sel C3 merujuk ke sel A1.
Kemudian, sel A5 merujuk ke sel C3.
Sekarang ganti nilai 20 di sel A1 dengan rumus seperti gambar di bawah ini. Setiap sel lain bergantung pada sel A1. Saat Anda menggunakan referensi sel rumus sebelumnya lainnya di A1, itu akan menyebabkan peringatan referensi melingkar. Karena, rumus di A1 merujuk ke sel A5, yang merujuk ke C3, dan sel C3 merujuk kembali ke A1, oleh karena itu referensi melingkar.
Saat Anda mengklik 'OK', itu menghasilkan nilai 0 di sel A1 dan Excel membuat garis tertaut yang menunjukkan Trace Precedents dan Trace Dependents seperti yang ditunjukkan di bawah ini. Kita dapat menggunakan fitur ini untuk dengan mudah menemukan dan memperbaiki/menghapus referensi melingkar.
Cara Mengaktifkan / Menonaktifkan Referensi Edaran di Excel
Secara default, perhitungan berulang dimatikan (dinonaktifkan) di Excel. Perhitungan iteratif adalah perhitungan yang berulang-ulang sampai memenuhi suatu kondisi tertentu. Saat dinonaktifkan, Excel memperlihatkan pesan Referensi Melingkar dan mengembalikan 0 sebagai hasilnya.
Namun, terkadang referensi melingkar diperlukan untuk menghitung loop. Untuk menggunakan referensi melingkar, Anda harus mengaktifkan perhitungan berulang di Excel Anda dan itu akan memungkinkan Anda untuk melakukan perhitungan Anda. Sekarang, izinkan kami menunjukkan kepada Anda bagaimana Anda dapat mengaktifkan atau menonaktifkan perhitungan berulang.
Di Excel 2010, Excel 2013, Excel 2016, Excel 2019, dan Microsoft 365, buka tab 'File' di sudut kiri atas Excel, lalu klik 'Opsi' di panel kiri.
Pada jendela Opsi Excel, buka tab 'Rumus' dan centang kotak 'Aktifkan penghitungan berulang' di bawah bagian 'Opsi penghitungan'. Kemudian klik 'OK' untuk menyimpan perubahan.
Ini akan memungkinkan perhitungan berulang dan dengan demikian memungkinkan referensi melingkar.
Untuk mencapai ini di versi Excel sebelumnya, ikuti langkah-langkah berikut:
- Di Excel 2007, klik tombol Office > Opsi Excel > Rumus > Area iterasi.
- Di Excel 2003 dan versi yang lebih lama, Anda harus masuk ke Menu > Alat > Opsi > tab Perhitungan.
Iterasi Maksimum & Parameter Perubahan Maksimum
Setelah Anda mengaktifkan perhitungan berulang, Anda dapat mengontrol perhitungan berulang, dengan menentukan dua opsi yang tersedia di bawah bagian Aktifkan perhitungan berulang seperti yang ditunjukkan pada gambar di bawah.
- Iterasi Maksimum – Angka ini menentukan berapa kali rumus harus dihitung ulang sebelum memberi Anda hasil akhir. Nilai defaultnya adalah 100. Jika Anda mengubahnya menjadi '50', Excel akan mengulangi perhitungannya sebanyak 50 kali sebelum memberikan hasil akhir kepada Anda. Ingat bahwa semakin tinggi jumlah iterasi, semakin banyak sumber daya dan waktu yang dibutuhkan untuk menghitung.
- Perubahan Maksimum – Ini menentukan perubahan maksimum antara hasil perhitungan. Nilai ini menentukan keakuratan hasil. Semakin kecil angkanya, semakin akurat hasilnya dan semakin lama waktu yang dibutuhkan untuk menghitung lembar kerja.
Jika opsi penghitungan berulang diaktifkan, Anda tidak akan mendapatkan peringatan apa pun setiap kali ada referensi melingkar di lembar kerja Anda. Hanya aktifkan penghitungan interaktif jika benar-benar diperlukan.
Temukan Referensi Edaran di Excel
Misalkan Anda memiliki kumpulan data yang besar dan Anda mendapat peringatan referensi melingkar, Anda masih perlu mencari tahu di mana (di sel mana) kesalahan telah terjadi untuk memperbaikinya. Untuk menemukan referensi melingkar di Excel, ikuti langkah-langkah berikut:
Menggunakan Alat Pemeriksaan Kesalahan
Pertama, buka lembar kerja di mana referensi melingkar telah terjadi. Buka tab 'Rumus', klik panah di sebelah alat 'Pemeriksaan Kesalahan'. Kemudian arahkan kursor ke opsi 'Referensi Melingkar', Excel akan menampilkan daftar semua sel yang terlibat dalam referensi melingkar seperti yang ditunjukkan di bawah ini.
Klik pada alamat sel mana pun yang Anda inginkan dalam daftar dan itu akan membawa Anda ke alamat sel itu untuk menyelesaikan masalah.
Menggunakan Bilah Status
Anda juga dapat menemukan referensi melingkar di bilah Status. Pada bilah status Excel, ini akan menunjukkan kepada Anda alamat sel terbaru dengan referensi melingkar, seperti 'Referensi Melingkar: B6' (lihat tangkapan layar di bawah).
Ada beberapa hal yang harus Anda ketahui saat menangani referensi melingkar:
- Bilah status tidak akan memperlihatkan alamat sel referensi melingkar saat opsi Perhitungan Iteratif diaktifkan, jadi Anda harus menonaktifkannya sebelum mulai melihat buku kerja untuk referensi melingkar.
- Jika referensi melingkar tidak ditemukan di lembar aktif, bilah status hanya menampilkan 'Referensi Melingkar' tanpa alamat sel.
- Anda hanya akan mendapatkan prompt referensi melingkar sekali dan setelah Anda mengklik 'OK', itu tidak akan menampilkan prompt lagi di lain waktu.
- Jika buku kerja Anda memiliki referensi melingkar, itu akan memperlihatkan kepada Anda perintah setiap kali Anda membukanya hingga Anda menyelesaikan referensi melingkar atau hingga Anda mengaktifkan penghitungan berulang.
Hapus Referensi Edaran di Excel
Menemukan referensi Edaran itu mudah tetapi memperbaikinya tidak sesederhana itu. Sayangnya, tidak ada opsi di Excel yang memungkinkan Anda menghapus semua referensi melingkar sekaligus.
Untuk memperbaiki referensi melingkar, Anda harus menemukan setiap referensi melingkar satu per satu dan mencoba memodifikasinya, menghapus rumus melingkar sama sekali, atau menggantinya dengan yang lain.
Terkadang, dalam rumus sederhana, yang perlu Anda lakukan hanyalah menyesuaikan kembali parameter rumus sehingga tidak merujuk kembali ke dirinya sendiri. Misalnya, ubah rumus di B6 menjadi =SUM(B1:B5)*A5 (mengubah B6 menjadi B5).
Ini akan mengembalikan hasil perhitungan sebagai '756'.
Jika referensi melingkar Excel sulit ditemukan, Anda bisa menggunakan fitur Trace Precedents dan Trace Dependents untuk melacaknya kembali ke sumber dan menyelesaikannya satu per satu. Panah menunjukkan sel mana yang dipengaruhi oleh sel aktif.
Ada dua metode penelusuran yang dapat membantu Anda menghapus referensi melingkar dengan memperlihatkan hubungan antara rumus dan sel.
Untuk mengakses metode penelusuran, buka tab 'Rumus', lalu klik 'Trace Precedents' atau 'Trace Dependents' di grup Formula Auditing.
Jejak Preseden
Saat Anda memilih opsi ini, ini melacak kembali sel yang memengaruhi nilai sel aktif. Ini menarik garis biru yang menunjukkan sel mana yang memengaruhi sel saat ini. Tombol pintas untuk menggunakan jejak preseden adalah Alt + T U T.
Pada contoh di bawah, panah biru menunjukkan sel yang mempengaruhi nilai B6 adalah B1:B6 dan A5. Seperti yang Anda lihat di bawah, sel B6 juga merupakan bagian dari rumus, yang menjadikannya referensi melingkar dan menyebabkan rumus mengembalikan '0' sebagai hasilnya.
Ini dapat dengan mudah diperbaiki dengan mengganti B6 dengan B5 dalam argumen SUM: =SUM(B1:B5).
Melacak Tanggungan
Fitur trace dependen melacak sel yang bergantung pada sel yang dipilih. Fitur ini menarik garis biru yang menunjukkan sel mana yang terpengaruh oleh sel yang dipilih. Artinya, ini menampilkan sel mana yang berisi rumus yang mereferensikan sel aktif. Tombol pintas untuk menggunakan tanggungan adalah Alt + T U D.
Dalam contoh berikut, sel D3 dipengaruhi oleh B4. Hal ini tergantung pada B4 untuk nilainya untuk menghasilkan hasil. Oleh karena itu, trace dependent menarik garis biru dari B4 ke D3, yang menunjukkan bahwa D3 bergantung pada B4.
Sengaja Menggunakan Referensi Melingkar di Excel
Menggunakan referensi melingkar dengan sengaja tidak disarankan, tetapi mungkin ada beberapa kasus yang jarang terjadi di mana Anda memerlukan referensi melingkar sehingga Anda bisa mendapatkan output yang Anda inginkan.
Mari kita jelaskan hal itu dengan menggunakan sebuah contoh.
Untuk memulainya, aktifkan 'Perhitungan Iteratif' di buku kerja Excel Anda. Setelah Anda mengaktifkan Perhitungan Iteratif, Anda dapat mulai menggunakan referensi melingkar untuk keuntungan Anda.
Mari kita asumsikan Anda membeli rumah dan Anda ingin memberikan komisi 2% dari total biaya rumah kepada agen Anda. Total biaya akan dihitung di sel B6 dan persentase komisi (biaya agen) dihitung di B4. Komisi dihitung dari total biaya dan total biaya sudah termasuk komisi. Karena sel B4 dan B6 saling bergantung, itu membuat referensi melingkar.
Masukkan rumus untuk menghitung total biaya di sel B6:
=SUM(B1:B4)
Karena total biaya termasuk biaya agen, kami memasukkan B4 dalam rumus di atas.
Untuk menghitung Biaya Agen 2%, masukkan rumus ini di B4:
=B6*2%Sekarang rumus di sel B4 tergantung pada nilai B6 untuk menghitung 2% dari total biaya dan rumus di B6 tergantung pada B4 untuk menghitung total biaya (termasuk biaya agen), maka referensi melingkar.
Jika penghitungan berulang diaktifkan, Excel tidak akan memberi Anda peringatan atau 0 dalam hasilnya. Sebagai gantinya, hasil sel B6 dan B4 akan dihitung seperti yang ditunjukkan di atas.
Opsi penghitungan berulang biasanya dinonaktifkan secara default. Jika Anda tidak menyalakannya dan ketika Anda memasukkan rumus di B4 yang akan membuat referensi melingkar. Excel akan mengeluarkan peringatan dan ketika Anda mengklik 'OK', panah pelacak akan ditampilkan.
Itu dia. Ini semua yang perlu Anda ketahui tentang referensi melingkar di Excel.