Artikel ini membahas circular reference di Excel, yaitu definisi, bagaimana mengenali, cara menghindari, serta mengatasinya.
Untuk kita yang sering bekerja dengan rumus Excel, sesekali pasti pernah ketemu dengan yang namanya circular reference.
Circular reference atau referensi melingkar mudah dideteksi dan diatasi, tapi tetap merupakan error yang sering membuat bingung.
Apa Itu Circular Reference Excel?
Sebenarnya, apa itu circular reference Excel?
Circular reference adalah kondisi di mana sebuah referensi mengacu pada dirinya sendiri, secara langsung atau pun tidak langsung.
Rumus pada gambar di atas adalah contoh circular reference, di mana rumus pada cell B1 mengacu pada B1 itu sendiri (narsi, menurut kami).
Logika berputarnya gini: rumus B1 mencoba menjumlahkan nilai A1 terhadap dirinya sendiri. Tapi, nilai B1 itu sendiri apa? Nilai B1, ya, penjumlahan A1 dan B1. Iya, tapi nilai B1 sendiri apa? Ya, penjumlahan A1 dan B1.
Terus begitu. Referensi mbulet, kata teman kami.
Saat kita menekan Enter untuk menyelesaikan rumus, Excel akan langsung menampilkan notifikasi bahwa ada rumus yang mengandung circular reference, seperti terlihat pada Gambar 01.
Bila kita tutup notifikasi dengan klik OK atau tanda silang di kanan atas, notifikasi akan hilang dan Excel akan menempatkan nilai 0 (nol) di B1.
Setelah itu, Excel tetap akan memberi tahu bahwa worksheet kita masih memiliki circular reference lewat status bar.
Circular reference sering terjadi karena kita lupa alur referensi rumus. Kadang, terjadi karena salah pilih range atau cell.
Pada gambar di atas, kami mencoba menjumlahkan penjualan semua sales tapi kelebihan drag dan memasukkan cell B8, di mana rumus ditulis. Kondisi ini juga menyebabkan circular reference.
Cara Mencari Circular Reference Pada Excel
Mencari cell dengan circular reference di Excel cukup mudah. Cukup gunakan perintah Circular References di tab Formulas.
(Demo 01) Berikut cara mencari circular reference pada Excel.
- Klik tab Formulas → Kelompok perintah Formula Auditing → Error Checking.
- Klik salah satu cell yang ditampilkan oleh perintah Circular References.
Mengklik cell yang ditampilkan oleh Circular Reference akan mengaktifkan cell bersangkutan.
Cara Menggunakan Trace Precedents dan Trace Dependents
Setelah Excel mengaktifkan cell di mana circular reference terjadi, langkah berikutnya adalah mencari bagaimana circular reference terjadi.
Bila rumus cukup sederhana dengan sedikit referensi, mungkin bisa langsung ketemu. Tapi bila kompleks dan menggunakan banyak referensi, pertimbangkan menggunakan perintah Trace Precedents atau Trace Dependents.
Kedua perintah tersebut berada di tab Formulas pada kelompok perintah Formula Auditing.
Cara Menggunakan Trace Precedents di Excel
Trace Precedents adalah perintah untuk memperlihatkan jejak cell-cell yang menjadi penyedia nilai bagi rumus pada cell aktif.
Kita bisa baca Trace Precedents sebagai siapa aja, sih, yang saya butuhkan.
(Demo 02) Cara menggunakan Trace Precedents di Excel.
- Aktifkan cell di mana rumus berada, baik dengan klik langsung atau pun dengan perintah Circular References.
- Pada tab Formulas → Kelompok perintah Formula Auditing, klik Trace Precedents (Shortcut: ALT,M,P).
Hasilnya bisa dilihat pada gambar di bawah ini.
Pada gambar 05.A, dengan cell aktif di B8, kami menjalankan Trace Precedents. Setelahnya, muncul panah memanjang dari B3 ke B8 dengan kepala panah di B8 dan persegi panjang biru dari B3 ke B8. Artinya, rumus di B8 menggunakan referensi dari cell-cell B3:B8.
Bandingkan dengan Gambar 05.B di mana persegi panjang biru tidak menutupi B8. Cell-cell referensi di rumus B8 di sini tidak mengikutkan dirinya sendiri.
Persegi panjang biru yang mencakup B8 adalah penanda bahwa rumus B8 memiliki circular reference dengan dirinya sendiri sebagai referensi.
Cara Menggunakan Trace Dependents di Excel
Trace Dependents adalah kebalikan dari Trace Precedents.
Fungsi Trace Dependents adalah memperlihatkan rumus mana saja yang menggunakan nilai dari suatu cell aktif.
Kita bisa baca Trace Dependents sebagai siapa aja, sih, yang butuh saya.
(Demo 03) Berikut cara menggunakan Trace Dependents di Excel.
- Pilih cell yang akan diperiksa.
- Pada tab Formulas → Kelompok perintah Formula Auditing, klik Trace Dependents (Shortcut: ALT,M,D).
Hasilnya bisa dilihat pada gambar di bawah ini.
Pada gambar di atas, terlihat bahwa cell aktif B3 digunakan rumus di D3 dan B8.
Untuk menonaktifkan panah-panah Trace Dependents dan Trace Precedents, gunakan perintah Remove Arrows.
Cara Menemukan Circular Reference di Excel dengan Show Formulas
Contoh-contoh di atas adalah circular reference secara langsung, mudah dilihat.
Tapi, circular reference tidak selalu jelas.
Perhatikan gambar di bawah.
Pada Gambar 08.A, tidak terlihat ada circular reference di rumus B7. Tapi, Excel memberikan nilai 0 (nol) sebagai hasil rumus, yang menandakan rumus tersebut mungkin memiliki referensi melingkar-lingkar.
Pada Gambar 08.B, kami menggunakan Show Formulas untuk menunjukkan semua rumus pada worksheet. Di situ, terlihat bahwa cell B2 juga berupa rumus.
Dengan menjalankan Trace Precedents pada cell B7 dan Trace Dependents di cell B2, kita mendapatkan hasil seperti pada gambar di bawah ini.
Terlihat panah dari B2 ke B7 dan sebaliknya. Panah bolak-balik tersebut berarti terjadi circular reference karena Bonus Sales (B2) diambil sebesar 5% dari Penjualan Bersih. Sementara itu, Penjualan Bersih sendiri didapat dengan mengeluarkan biaya-biaya di mana Bonus Sales adalah salah satunya.
Contoh pada Gambar o8 adalah contoh circular reference tidak langsung, yaitu di mana kita tidak melihat suatu rumus mengacu pada dirinya sendiri, tapi lewat putaran dari satu cell ke cell lain.
Cara Menghilangkan Circular Reference di Excel
Bila punya tujuan tertentu menggunakan circular reference, teman-teman mungkin ingin menghilangkan pesan kesalahan yang muncul.
(Demo 04) Cara menghilangkan circular reference di Excel.
- Klik tab File di Ribbon.
- Pada panel navigasi di sebelah kiri, klik Options untuk menampilkan kotak dialog Excel Options.
- Klik Enable iterative calculation untuk mengaktifkan pengulangan perhitungan saat terjadi circular reference.
- Tentukan jumlah maksimal pengulangan di Maximum Iterations.
- Pada Maximum Change, tentukan perubahan yang diinginkan agar pengulangan terus berlanjut.
- Klik OK.
Perhatian. Enable iterative calculation tidak aktif secara default.
Bila aktif, maka makin tinggi nilai Maximum Iterations dan semakin rendah Maximum Changes, makin lama waktu yang dibutuhkan untuk menyelesaikan perhitungan saat terjadi circular reference.
Cara Mengatasi Circular Reference di Excel
Cara mengatasi circular reference di Excel adalah dengan memperbaiki referensi yang digunakan pada suatu rumus.
Selain itu, tidak ada salahnya mengubah desain worksheet demi menghindari circular reference.
Gambar 11 adalah modifikasi dari data Gambar 08. Kami memindahkan Bonus Sales di bawah Penjualan Sebelum Bonus.
Desain seperti Gambar 08 memang terlihat lebih mulus dan rapi ketimbang Gambar 11. Tapi, gak ada gunanya bila makan waktu dan tenaga berhari-hari.
Catatan: Teman kami memang menghabiskan waktu berhari-hari mempertahankan dan mengakali desain seperti Gambar 08. Padahal dia tau persis lebih sulit karena ada circular reference.
Mengapa Perlu Menghindari Circular Reference
Sampai sini teman-teman mungkin sudah bisa berpikir bahwa circular reference perlu dihindari.
Hal lain yang perlu teman-teman tahu adalah circular reference bisa memperlambat kerja komputer karena perhitungan yang terus berulang (banyaknya perulangan ditentukan oleh Maximum Iterations dan akurasi ditentukan oleh Maximum Change).
Pada kondisi tertentu saat data sungguh besar, Excel bahkan bisa hang atau crash.
Selain itu, nilai 0 (nol) yang diberikan sebagai hasil circular reference bisa membuat hasil perhitungan jadi salah (karena kita sungguh menganggap bahwa hasilnya adalah nol).
Penutup
Demikian bahasan kami mengenai circular reference di Excel.
Bila teman-teman ada pertanyaan, pendapat, atau masukan lain, monggo layangkan ke kolom komentar di bawah atau lewat halaman Hubungi Kami.
Terima kasih.