Cara mengatasi rumus yang kena circular references

Rekan marketing saya sedang menyusun penawaran di Excel. Ada satu baris yang nilainya dihitung dari jumlah baris-baris di atasnya, jadi dia isi rumus SUM biasa. Rumusnya pendek, bentuknya benar, dan saya sudah sering memakai yang seperti itu.

Begitu tekan Enter, hasilnya tidak keluar. Selnya cuma menampilkan tanda strip, dan sepanjang baris itu melintang panah biru dengan titik di kedua ujungnya, menembus sampai ke luar tabel.

Dia bertanya bagaimana cara mengatasinya. Ternyata yang dia temui namanya circular reference, dan cara mengatasinya saya tulis ulang di sini sekalian, siapa tahu Anda sedang menatap layar yang sama. Mari mulai.

Nama masalahnya circular references, dan rumusnya sendiri memang tidak ada yang salah. Itu yang bikin lama ketahuan.

Rumusnya benar, posisinya yang keliru

Ini rumus yang dipakai:

Rumus itu duduk di sel L130, dan rentang yang dijumlahnya berakhir di baris 130 juga. Di situlah masalahnya.

Di tabel, hasil L130 mengalir ke beberapa sel lain di baris yang sama, dan ujungnya mendarat di H130. Padahal H130 ikut dijumlah oleh L130. Jadi Excel diminta menghitung L130 memakai angka yang baru lahir setelah L130 selesai dihitung. Ular makan ekornya sendiri, dan Excel memilih berhenti daripada berputar selamanya.

Rantainya begini, dan semua sel ini ada di baris yang sama:

Petunjuk circular references ada di pojok kiri bawah

Excel sebenarnya memberi tahu, cuma tempatnya tidak mencolok. Lihat status bar di kiri bawah jendela. Di situ tertulis Circular References beserta alamat selnya.

Kalau tulisan itu tidak muncul, atau Anda ingin daftar lengkapnya, ada cara yang lebih pasti.

Cara memastikannya lewat Error Checking

Buka tab Formulas di ribbon atas.

Klik tombol Error Checking, lalu arahkan ke Circular References. Daftar sel yang terlibat langsung muncul di sebelahnya.

Klik salah satu alamat di daftar itu, dan Excel akan melompat ke selnya. Dari situ Anda bisa menelusuri satu per satu, sel ini mengambil dari mana, sampai ketemu titik yang menutup lingkarannya.

Aturannya satu kalimat

Setelah saya runut, intinya sesederhana ini:

Hasil sebuah rumus tidak boleh ikut masuk ke dalam angka yang dijumlah oleh rumus itu sendiri.

Satu hal yang penting supaya tidak salah paham. Yang terlarang bukan “baris yang sama”, tapi baris mana pun yang ikut dijumlah. Contohnya, jika isi rumusnya menjumlahkan dari rentang H35 sampai H130, rumus tersebut tidak boleh ditulis di kolom mana pun pada baris 35 sampai 130. Ditaruh di baris 60 pun tetap akan kena circular references.

Cara memperbaikinya

Kabar baiknya, circular references hampir tidak pernah menuntut rumusnya diubah. Di kasus kami rumusnya memang harus tetap =(SUM(H35:H130)*0,85), karena itu kesepakatan hitungan yang sudah dipakai di berkas yang sama. Jadi yang saya pindah bukan rentang di dalam rumusnya, tapi baris tempat rumus itu ditulis.

Posisi rumus saya turunkan satu baris, ke baris 131. Rentang H35 sampai H130 berhenti di 130, jadi baris 131 sudah di luar jangkauannya. Circular references berhasil diatasi, dan rumusnya sendiri tidak perlu dirombak, yang berpindah cuma tempat duduknya.

Tiga hal yang perlu diperhatikan waktu memindahkannya:

• Pakai Cut, jangan Copy. Cut memindahkan rumus apa adanya. Copy akan menggeser rentangnya jadi H36 sampai H131, dan lingkarannya balik lagi. Kalau ragu, ketik ulang saja rumusnya di tempat baru.
• Jangan menyisipkan baris baru di dalam rentang. Excel otomatis memanjangkan rentangnya mengikuti sisipan, dan pos itu bisa ikut terjaring lagi tanpa Anda sadari. Menyisipkan di bawah ujung rentang aman.
• Cek baris total di bawahnya. Kalau total besar Anda menjumlah sampai baris tertentu saja, pastikan baris baru tadi masih ikut terhitung. Di berkas itu kebetulan sudah tercakup, jadi tidak ada yang perlu diubah.

Kalau lokasi penulisan rumusnya memang tidak boleh pindah

Tidak semua tabel bisa digeser begitu saja. Kalau posisi tempat menulis rumusnya yang justru tidak boleh digeser, maka ada dua pilihan cara perbaikannya:

1. Persempit rentangnya satu baris. Contoh: Jika rumusnya tetap harus ditulis di L130, maka persempit rentang SUM-nya menjadi dari yang awalnya H35 sampai H130 jadi H35 sampai H129.
2. Pisahkan subtotalnya ke sel tersendiri. Taruh penjumlahan dasarnya di satu sel di luar rentang, lalu rumus Anda cukup mengalikan sel itu. Dua rumus pendek lebih gampang diperiksa orang lain daripada satu rumus panjang yang ujungnya mepet.

Saran: Jangan lakukan iterative calculation

Saran yang paling sering muncul kalau Anda mencari solusinya adalah menyalakan Enable iterative calculation di File, Options, Formulas. Microsoft sendiri menjelaskan setelan ini di halaman bantuan resminya, dan di situ juga disebut bahwa menyalakannya berarti Excel menghitung berulang sampai angkanya dianggap cukup dekat.

Untuk dokumen harga, saya memilih tidak memakainya. Tiga alasannya:

• Peringatannya hilang, lingkarannya tidak. Yang berubah cuma Excel berhenti protes. Kesalahannya tetap di tempat.
• Angkanya jadi hasil hitungan bertahap, bukan hitungan pasti. Nilainya bisa bergeser hanya karena berkasnya dibuka ulang atau ditekan F9.
• Setelannya tersimpan di dalam berkas. Jadi ikut terbawa ke siapa pun yang Anda kirimi berkas itu, dan mereka tidak akan tahu angkanya sedang berputar.

Ada satu cara cepat untuk menguji apakah lingkaran Anda disengaja atau kecelakaan. Hitung kasar berapa hasilnya kalau lingkarannya benar-benar dibiarkan berputar, lalu bandingkan dengan nilai pekerjaan di sekitarnya. Di kasus kami angkanya langsung melambung jauh di luar akal sehat, dan itu saja sudah cukup membuktikan lingkarannya tidak disengaja.

Kebiasaan yang saya bawa setelah itu: setiap kali menulis SUM, saya lihat dulu baris terakhir rentangnya, lalu saya tanya satu hal. Apakah baris itu bisa, langsung atau lewat jalan memutar, menerima hasil dari rumus yang sedang saya tulis. Kalau jawabannya mungkin, rentangnya saya pendekkan satu baris sejak awal.