Tutorial Excel • rumus yang tidak bergeser saat disalin
Cara Mengunci Rumus Excel dengan Tanda $ (Referensi Absolut)
Kamu sudah menulis satu rumus yang benar, lalu menariknya ke bawah untuk baris-baris berikutnya — dan hasilnya tiba-tiba kacau. Baris pertama benar, baris kedua meleset, baris ketiga entah berapa. Bukan rumusnya yang salah, dan bukan datanya yang rusak: Excel menggeser referensi sel di dalam rumus setiap kali rumus itu disalin. Itulah perilaku bawaan referensi relatif, dan ia baru terasa saat rumusmu harus mengacu ke satu sel tetap, misalnya satu tarif PPN untuk semua barang.
Jawaban singkat: tambahkan tanda $ di depan kolom dan baris sel yang harus tetap, misalnya =B2*(1+$B$1) untuk harga di B2 dikali tarif PPN di B1. Tanda $ mengunci referensinya, sehingga rumus yang ditarik ke bawah tetap mengacu ke B1. Di bawah ini ada contoh lengkap yang hasilnya sudah dihitung manual, jadi kamu bisa membuktikan sendiri bahwa rumusnya benar — termasuk contoh apa yang terjadi kalau tanda $ itu lupa dipasang.
Konsep: referensi relatif bergeser, referensi absolut diam
Setiap referensi sel di Excel punya dua bagian: kolom (huruf) dan baris (angka). Tanpa tanda $, keduanya relatif: saat rumus disalin satu baris ke bawah, semua nomor baris ikut turun satu; saat disalin satu kolom ke kanan, semua huruf kolom ikut geser satu. Tanda $ di depan sebuah bagian mematikan pergeseran bagian itu saja. Karena itu ada empat bentuk referensi, bukan dua:
| Bentuk | Nama | Saat disalin |
|---|---|---|
B1 | Relatif | Kolom dan baris sama-sama bergeser |
$B$1 | Absolut | Tidak bergeser sama sekali |
$B1 | Campuran, kolom terkunci | Kolom diam, baris bergeser |
B$1 | Campuran, baris terkunci | Baris diam, kolom bergeser |
Cara tercepat memasang tanda $ adalah tombol F4: letakkan kursor di dalam referensi pada formula bar, lalu tekan F4 berulang kali. Urutannya adalah B1, lalu $B$1, lalu B$1, lalu $B1, lalu kembali ke B1. Di Mac, pakai Fn + F4 atau Command + T, tergantung pengaturan keyboard. Satu hal yang wajib diluruskan sejak awal: tanda $ di sini bukan soal uang dan bukan soal proteksi. Tanda $ hanya menahan referensi agar tidak bergeser saat disalin — ia tidak mencegah orang lain mengubah isi sel itu. Kalau yang kamu butuhkan adalah melindungi rumus dari ketikan yang tidak sengaja, itu pekerjaan Protect Sheet, seperti di cara mengunci cell Excel agar rumus tidak tertimpa.
Praktik: harga + PPN 11% dalam 4 langkah
Buat workbook baru. Tulis tarif PPN sekali saja di B1, lalu daftar harga di kolom B mulai baris 2. Rumus di kolom C mengalikan tiap harga dengan satu ditambah tarif, dan tarifnya dikunci dengan $ agar tidak ikut bergeser saat rumus ditarik ke bawah.
| Sel | Isi | Keterangan |
|---|---|---|
| B1 | 0,11 | Tarif PPN 11%, ditulis sekali di sini |
| A2 / B2 | Buku Tulis / 10000 | Barang pertama dan harganya |
| A3 / B3 | Pulpen / 5000 | Barang kedua dan harganya |
| A4 / B4 | Penghapus / 3000 | Barang ketiga dan harganya |
- Tulis tarifnya sekali, di satu sel. Ketik 0,11 di B1 dan format sebagai persentase bila mau — tampilannya menjadi 11%, nilainya tetap 0,11. Jangan menulis angka 0,11 di dalam tiap rumus: sekali tarif berubah, kamu harus mengedit semua rumus satu per satu.
- Tulis satu rumus dengan tarif yang dikunci. Di C2 ketik
=B2*(1+$B$1). B2 dibiarkan relatif karena tiap baris memang memakai harganya sendiri, sementara $B$1 dikunci penuh karena semua baris memakai tarif yang sama. - Tarik ke bawah dan cocokkan hasilnya. Tarik C2 ke C4. Hasil yang benar: C2 = 10000 x 1,11 = 11100, C3 = 5000 x 1,11 = 5550, C4 = 3000 x 1,11 = 3330. Jumlah ketiganya: 11100 + 5550 + 3330 = 19980, sama persis dengan total harga 18000 dikali 1,11 = 19980. Kalau total kolom C tidak sama dengan total kolom B dikali 1,11, ada rumus yang referensinya bergeser.
- Uji kenapa satu sel lebih baik. Ubah B1 menjadi 0,10. Semua hasil harus ikut berubah tanpa kamu menyentuh satu pun rumus: 11000, 5500, dan 3300, total 19800. Kembalikan B1 ke 0,11 sesudahnya. Inilah gunanya mengunci satu sel acuan, bukan menulis angkanya di mana-mana.
Catatan locale: rumus di artikel ini tidak memakai pemisah argumen, jadi langsung jalan di Excel berbahasa Indonesia maupun Inggris. Begitu kamu menggabungkannya dengan fungsi yang punya beberapa argumen, misalnya SUM, pemisahnya mengikuti bahasa Excel: koma di Excel berbahasa Inggris, titik koma di Excel berbahasa Indonesia.
3 jebakan umum dan cara memperbaikinya
- Lupa $, hasil baris kedua meledak. Tanpa kunci, rumus di C2 adalah =B2*(1+B1). Ditarik ke C3, ia menjadi =B3*(1+B2) — tarifnya kini membaca harga baris pertama, 10000, sehingga hasilnya 5000 x 10001 = 50005000. Ditarik ke C4, ia menjadi =B4*(1+B3), hasilnya 3000 x 5001 = 15003000. Gejalanya khas: baris pertama benar, baris berikutnya makin tidak masuk akal. Perbaikannya: kunci sel acuan dengan $ sebelum menarik rumus, seperti pada langkah 2.
- Mengunci semuanya, termasuk yang harus bergerak. Kalau rumus ditulis =B$2*(1+$B$1) lalu ditarik ke bawah, semua baris memakai harga B2 — hasilnya 11100 tiga kali. Aturannya: kunci hanya sel yang memang harus tetap. Harga tiap baris harus tetap relatif, karena tiap baris memang memakai harganya sendiri.
- Mengira $ melindungi sel dari edit. Tanda $ tidak menghalangi siapa pun mengetik di atas sel B1 atau mengubah rumus di kolom C. Kalau file dibagikan dan yang ditakutkan adalah rumus tertimpa ketikan, pakai Protect Sheet seperti di cara mengunci cell Excel agar rumus tidak tertimpa. Tanda $ dan Protect Sheet menyelesaikan dua masalah yang berbeda, dan sering dibutuhkan bersamaan.
Cara cek cepat sebelum memakai tabel untuk laporan: klik satu rumus hasil tarikan dan baca formula bar-nya. Sel acuan yang dikunci harus tampil persis sama seperti di rumus pertama ($B$1 tetap $B$1), sementara referensi barisnya harus sudah berpindah ke barisnya sendiri (B3 di baris 3, B4 di baris 4). Kalau keduanya bergeser, atau keduanya diam, salah satu kuncinya keliru.
Lanjutan: referensi campuran untuk tabel dua arah
Referensi campuran berguna saat satu rumus harus disalin ke dua arah sekaligus: ke bawah dan ke kanan. Contohnya tabel perkalian kecil: angka 2, 3, dan 4 di A2:A4, pengali 10, 20, dan 30 di B1:D1. Satu rumus di B2, =$A2*B$1, cukup untuk seluruh tabel: $A mengunci kolom angka, B$1 mengunci baris pengali, sementara bagian yang tidak dikunci ikut bergerak ke arah salinannya.
| B (10) | C (20) | D (30) | |
|---|---|---|---|
| A2 (2) | 20 | 40 | 60 |
| A3 (3) | 30 | 60 | 90 |
| A4 (4) | 40 | 80 | 120 |
Periksa satu sel sebelum percaya pada tabelnya: C3 harus berisi =$A3*C$1 = 3 x 20 = 60. Kolomnya tetap A karena $A dikunci, baris pengalinya tetap 1 karena $1 dikunci, dan angka yang bergerak adalah baris A3 dan kolom C — tepat dua bagian yang memang harus bergerak.
Latihan: ubah tarif, tambah satu baris tabel
- Di tabel PPN, ubah B1 menjadi 0,10 tanpa menyentuh kolom C. Hasil yang benar: 11000, 5500, dan 3300, total 19800 — sama dengan 18000 x 1,10. Kalau satu baris tidak berubah, rumus baris itu tidak mengacu ke $B$1.
- Di tabel perkalian, tambahkan angka 5 di A5 dan tarik rumus dari baris 4 ke baris 5. Hasil yang benar: 50, 100, dan 150. Kalau kolom pertama benar tapi kolom berikutnya salah, kunci kolomnya ($A) yang hilang; kalau barisnya salah semua, kunci baris pengalinya ($1) yang hilang.
Setelah referensi absolut ini beres, masalah berikutnya yang sering muncul adalah total yang harus memilah kategori — lihat cara memakai SUMIF per kategori — atau kembali ke hub tutorial Excel untuk memilih masalah berikutnya.