Referensi Absolut dan Relatif di Excel: Kapan Pakai Tanda $

Kamu menulis rumus di baris pertama, hasilnya benar. Lalu rumusnya kamu tarik ke bawah, dan tiba-tiba baris kedua dan seterusnya menampilkan angka nol, angka aneh, atau error. Kalau ini pernah terjadi, besar kemungkinan penyebabnya satu hal: referensi sel yang ikut bergeser padahal seharusnya diam.

Memahami referensi relatif dan absolut adalah salah satu lompatan terbesar dalam belajar Excel. Setelah paham, kamu bisa menulis satu rumus dan menyalinnya ke ribuan sel dengan yakin hasilnya benar semua. Artikel ini membahasnya dari awal, lengkap dengan contoh kasus yang sering muncul di kantor.

Referensi relatif: alamat yang ikut bergeser

Secara default, setiap alamat sel di dalam rumus adalah relatif. Excel sebenarnya tidak mengingat "sel B2", tetapi mengingat posisinya relatif terhadap sel rumus, misalnya "dua kolom di kiri, di baris yang sama".

Contohnya tabel pesanan berikut, dengan rumus subtotal di kolom D:

	A	B	C	D
1	Produk	Jumlah	Harga	Subtotal
2	Kaos	3	75.000	=B2*C2
3	Topi	5	40.000	=B3*C3
4	Tas	2	120.000	=B4*C4

Kamu cukup menulis =B2*C2 di D2, lalu menyalinnya ke bawah. Excel otomatis mengubahnya jadi =B3*C3 di D3 dan =B4*C4 di D4. Inilah perilaku yang kamu inginkan hampir setiap saat, dan alasan kenapa Excel jauh lebih cepat dari kalkulator.

Masalahnya: sel yang harus tetap

Sekarang tambahkan PPN 11% yang tarifnya ditulis di sel F1. Di E2 kamu menulis:

=D2*F1

Hasil di E2 benar. Tapi saat disalin ke bawah, E3 berisi =D3*F2 dan E4 berisi =D4*F3. Sel F2 dan F3 kosong, sehingga hasilnya nol. Referensi ke F1 ikut turun, padahal tarif PPN hanya ada di F1.

Solusinya adalah mengunci referensi itu.

Referensi absolut: tanda $ untuk mengunci

Tanda dolar $ di depan huruf kolom atau angka baris membuat bagian itu tidak berubah saat disalin. Tulis rumus PPN menjadi:

=D2*$F$1

Sekarang saat disalin ke bawah, hasilnya =D3*$F$1, =D4*$F$1, dan seterusnya. D bergeser seperti seharusnya, F1 tetap di tempat.

Kamu tidak perlu mengetik tanda dolar satu per satu. Saat mengetik atau mengedit rumus, taruh kursor di alamat sel lalu tekan F4. Setiap tekanan mengubah bentuknya secara bergiliran:

Bentuk	Kolom	Baris	Sebutan
F1	bergeser	bergeser	relatif
$F$1	terkunci	terkunci	absolut
F$1	bergeser	terkunci	campuran
$F1	terkunci	bergeser	campuran

Di Excel untuk Mac, gunakan Cmd + T sebagai pengganti F4. Cara membaca tanda dolar: $ mengunci bagian yang ada tepat di belakangnya. $F mengunci kolom F, $1 mengunci baris 1.

Referensi campuran: saat hanya satu arah yang dikunci

Referensi campuran terasa abstrak sampai kamu butuh menyalin rumus ke dua arah sekaligus, ke bawah dan ke samping. Contoh klasiknya tabel perkalian. Angka 1 sampai 9 ditulis di kolom A (A2:A10) dan di baris 1 (B1:J1). Di B2, tulis:

=$A2*B$1

Lalu salin ke seluruh area B2:J10. $A2 selalu mengambil angka dari kolom A, tapi barisnya mengikuti. B$1 selalu mengambil angka dari baris 1, tapi kolomnya mengikuti. Satu rumus mengisi 81 sel dengan benar.

Di kantor, pola yang sama muncul pada tabel harga per paket dan durasi, tabel target per bulan dan per cabang, atau simulasi cicilan dengan berbagai tenor dan bunga.

Contoh kasus yang sering dipakai

Persentase terhadap total

Total penjualan ada di B10, dan kamu ingin tahu kontribusi setiap produk:

=B2/$B$10

Tanpa tanda dolar, baris berikutnya membagi dengan B11, B12, dan seterusnya yang kosong, dan hasilnya error #DIV/0!.

Konversi kurs

Kurs dolar ditaruh di satu sel, misalnya H1. Harga dalam rupiah: =C2*$H$1. Saat kurs berubah, cukup ganti H1, dan seluruh kolom ikut menyesuaikan.

Rentang tabel di rumus pencarian

Ini sumber error paling umum di VLOOKUP: =VLOOKUP(A2, E2:G5, 3, FALSE) disalin ke bawah membuat rentang tabelnya ikut turun jadi E3:G6, E4:G7, dan seterusnya. Baris-baris terakhir mulai menghasilkan #N/A. Kunci rentangnya: $E$2:$G$5. Pembahasan lengkap soal rumus pencarian ada di VLOOKUP vs XLOOKUP. Hal yang sama berlaku untuk rentang di SUMIF dan COUNTIF.

Nomor urut dan baris acuan

Referensi campuran juga berguna untuk membuat total berjalan (running total), misalnya saldo kas yang terus bertambah. Di C2 tulis =SUM($B$2:B2), lalu salin ke bawah. Awal rentang terkunci di B2, sedangkan ujungnya ikut turun: C3 menjadi =SUM($B$2:B3), C4 menjadi =SUM($B$2:B4), dan seterusnya. Setiap baris menampilkan jumlah dari baris pertama sampai baris itu. Pola yang sama dipakai untuk menghitung urutan kemunculan sebuah nama: =COUNTIF($A$2:A2, A2) menghasilkan 1 untuk kemunculan pertama, 2 untuk kemunculan kedua, dan seterusnya. Ini sangat berguna untuk menandai data dobel.

Referensi ke sheet lain

Sel yang dikunci sering berada di sheet terpisah, misalnya sheet "Pengaturan" yang berisi tarif dan kurs. Formatnya nama sheet, tanda seru, lalu alamat sel:

=D2*Pengaturan!$B$1

Kalau nama sheet mengandung spasi, bungkus dengan tanda kutip tunggal: ='Data Penjualan'!$B$1. Cara termudah membuat referensi seperti ini adalah tidak mengetiknya. Saat menulis rumus, klik saja tab sheet tujuan lalu klik selnya, dan Excel menuliskan alamatnya untukmu. Setelah itu tekan F4 untuk menguncinya.

Alternatif yang lebih mudah dibaca

Nama (Named Range)

Klik sel F1, lalu ketik PPN di Name Box (kotak di kiri Formula Bar) dan tekan Enter. Sekarang rumusmu bisa ditulis =D2*PPN. Nama selalu bersifat absolut, jadi tidak perlu tanda dolar, dan rumusnya jauh lebih mudah dipahami orang lain.

Excel Table

Kalau datamu sudah diubah jadi Table dengan Ctrl + T, rumus di dalam tabel memakai nama kolom, misalnya =[@Jumlah]*[@Harga]. Rumus seperti ini otomatis berlaku untuk seluruh kolom dan tidak terpengaruh masalah pergeseran.

Kesalahan yang sering terjadi

• Mengunci semuanya. Sebagian orang yang baru kenal tanda $ menaruhnya di mana-mana. Akibatnya rumus tidak lagi ikut bergeser saat disalin, dan semua baris menampilkan hasil yang sama persis. Kunci hanya sel yang memang harus tetap.
• Mengunci baris padahal yang perlu kolom, atau sebaliknya. Kalau rumus disalin ke bawah, yang berubah adalah baris. Kalau disalin ke samping, yang berubah adalah kolom. Tanyakan: ke arah mana rumus ini disalin, dan bagian mana yang tidak boleh ikut berubah?
• Memindahkan sel dengan cut-paste. Kabar baiknya, memindahkan sel dengan cut-paste atau drag tidak merusak referensi. Excel menyesuaikannya otomatis. Yang mengubah referensi hanya menyalin (copy).

Cara memeriksa rumus yang sudah disalin

Klik sel hasil salinan lalu tekan F2. Excel mewarnai setiap referensi dan memberi kotak berwarna yang sama di lembar kerja, jadi kamu bisa langsung melihat apakah rumus menunjuk ke sel yang benar. Untuk melihat semua rumus sekaligus, tekan Ctrl + `. Tekan lagi untuk kembali ke tampilan hasil.

Di tab Formulas juga ada Trace Precedents yang menggambar panah dari sel-sel yang dipakai rumus. Fitur ini membantu saat menelusuri rumus buatan orang lain.

Referensi relatif dan absolut adalah bagian dari fondasi yang dibahas di Belajar Excel dari Nol. Di level Beginner Jago Excel, keduanya punya pelajaran sendiri, ditutup studi kasus anggaran bulanan yang memakai tanda $. Kamu bisa langsung mencobanya di lembar kerja, gratis.