TL;DR
Absolute reference Excel ditulis dengan tanda dollar, misalnya $B$2, dan gunanya mengunci alamat sel supaya gak ikut bergeser waktu rumusnya di-copy ke sel lain. Tanpa dollar, Excel pakai relative reference yang alamatnya nyesuain posisi. Tekan F4 di formula bar buat muter antara keempat mode: B2, $B$2, B$2, dan $B2.
Absolute reference Excel adalah alamat sel yang dikunci pakai tanda dollar, misalnya $B$2, biar gak ikut bergeser waktu rumusnya di-copy ke sel lain.
Ini penyebab nomor satu rumus yang "tadinya bener terus rusak sendiri" waktu ditarik ke bawah. Bukan Excel-nya yang aneh. Alamatnya emang bergeser, dan itu perilaku default.
Di bawah ini kamu bakal tau tiga tipe reference, trik F4, contoh tabel harga UMKM yang bikin orang pusing, dan kesalahan yang paling sering ngerusak laporan.
Absolute reference adalah cara menulis alamat sel dengan tanda dollar di depan huruf kolom dan angka baris, misalnya $B$2. Tanda dollar ngunci bagian yang ada di sebelah kanannya, jadi waktu rumusnya di-copy ke sel mana pun, alamat itu tetap nunjuk ke B2.
Tanda dollar di sini gak ada hubungannya sama uang. Anggap aja simbol jangkar.
| Penulisan | Nama | Yang terjadi waktu di-copy |
|---|---|---|
B2 | Relative | Kolom dan baris ikut geser |
$B$2 | Absolute | Gak geser sama sekali |
B$2 | Mixed | Baris terkunci, kolom bebas geser |
$B2 | Mixed | Kolom terkunci, baris bebas geser |
Cara ngingetnya: dollar selalu ngunci apa yang ada persis di kanannya. $B ngunci kolom B. $2 ngunci baris 2.
=A2 * B2.$B$2.B$2, terus $B2, terus balik ke B2.Di Mac, kalau F4 gak nurut, pakai Command + T. Di laptop yang tombol F-nya diatur buat volume dan brightness, tahan Fn dulu baru tekan F4.
Kamu juga bisa blok beberapa alamat sekaligus di formula bar lalu tekan F4. Semuanya kena.
Tiga situasi yang paling sering.
Nilai konstan yang dipakai semua baris. Tarif PPN 11% di sel H1, kurs dollar di sel H2, target bulanan di sel H3. Semua baris ngacu ke sel yang sama.
Range tabel di VLOOKUP atau XLOOKUP. Ini penyebab error paling umum. Tabel referensinya harus tetap di posisi yang sama waktu rumus ditarik ke bawah.
Range di SUMIF, COUNTIF, dan kawan-kawan. Range yang di-scan harus konstan, cuma kriterianya yang berubah per baris.
Kalau nilai yang kamu tunjuk berubah tiap baris (misalnya harga di baris itu sendiri), biarkan relative. Jangan asal kunci semuanya.
Dataset toko_berkah punya sheet daftar harga 240 produk UMKM. Kolomnya: kode di A, harga_dasar di B, dan tarif PPN 11% ditaruh di sel H1.
Rumus yang salah, dan ini yang paling sering ditulis orang.
C2: =B2 * (1 + H1)Kelihatan bener di baris 2. Copy ke bawah, dan lihat apa yang terjadi.
C3: =B3 * (1 + H2) ← H2 kosong
C4: =B4 * (1 + H3) ← H3 kosong
C5: =B5 * (1 + H4) ← H4 kosongMulai baris 3, PPN-nya jadi nol. Harga jual sama dengan harga dasar. Gak ada error merah, gak ada peringatan. Angkanya cuma salah diam-diam.
Versi bener: kunci H1.
C2: =B2 * (1 + $H$1)Copy ke bawah, dan semua baris tetap ngacu ke H1. B2 tetap relative karena harganya emang beda per baris.
Kesalahan ini pernah bikin selisih Rp 14,3 juta di laporan bulanan salah satu UMKM yang datanya dipakai di dataset Ngulik Data. Ketahuan setelah tiga bulan, waktu angka pajak di laporan gak cocok sama setoran.
Kasus kedua, VLOOKUP yang bocor.
Salah: =VLOOKUP(A2, E2:F241, 2, 0)
Bener: =VLOOKUP(A2, $E$2:$F$241, 2, 0)Versi salah bikin range-nya nyusut tiap turun satu baris. Baris ke-100 cuma nge-scan sampai F340 yang udah lewat batas, dan produk yang ada di baris bawah tabel jadi #N/A. Kalau kamu pakai XLOOKUP, masalahnya sama persis.
Mixed reference kepakai waktu kamu bikin matriks, misalnya tabel simulasi harga dengan beberapa tingkat diskon.
Layoutnya gini: kode produk di kolom A (baris 2 ke bawah), persentase diskon di baris 1 (kolom B ke kanan), dan kamu mau isi seluruh grid.
B2: =$A2 * (1 - B$1)Baca dollar-nya. $A2 ngunci kolom A tapi barisnya bebas, jadi waktu turun dia ikut ke A3, A4. B$1 ngunci baris 1 tapi kolomnya bebas, jadi waktu ke kanan dia ikut ke C1, D1.
Satu rumus. Copy ke seluruh grid. Selesai.
Tanpa mixed reference, kamu harus nulis rumus beda buat tiap kolom. Sepuluh kolom diskon berarti sepuluh rumus.
Ada yang F4 semua alamat karena takut salah. Hasilnya =$B$2 * $H$1 yang di-copy ke bawah nampilin angka yang sama persis di semua baris. Kunci cuma yang emang harus konstan.
Reference yang geser jarang bikin #REF!. Dia cuma ngambil sel kosong dan ngasih hasil nol. Laporan tetap kelihatan normal. Ini kenapa cek manual satu baris di tengah dan satu baris paling bawah itu wajib.
Dua-duanya ngegeser relative reference dengan cara yang sama. Yang gak ngegeser cuma Cut (Ctrl+X). Jadi jangan mikir dragging lebih aman dari copy.
Kalau data kamu diformat jadi Excel Table (Ctrl+T), referensinya pakai nama kolom kayak Tabel1[harga] dan otomatis stabil. Kamu tetap butuh dollar buat sel tunggal di luar tabel, tapi jauh lebih sedikit. Penjelasan resminya ada di halaman Microsoft tentang relative, absolute, dan mixed references.
Taruh tarif PPN, kurs, dan target di area terpisah, biasanya di atas atau di sheet parameter. Kalau ditaruh nyempil di antara data, cepat atau lambat ada yang nyisipin baris dan referensinya berantakan. Cara yang lebih rapi: kasih Named Range, jadi rumusnya jadi =B2 * (1 + tarif_ppn) dan gak perlu dollar sama sekali.
Tanda dollar ngunci bagian alamat sel yang ada persis di kanannya. Di $B$2, dollar pertama ngunci kolom B dan dollar kedua ngunci baris 2. Gak ada hubungannya sama mata uang.
Klik di formula bar tepat di alamat sel yang mau dikunci, tekan F4. Sekali jadi $B$2, dua kali B$2, tiga kali $B2, empat kali balik ke B2. Di Mac pakai Command + T.
Relative (B2) geser ngikutin arah copy. Absolute ($B$2) gak geser sama sekali. Mixed ngunci salah satu: B$2 ngunci baris, $B2 ngunci kolom. Mixed paling kepakai buat matriks.
Hampir pasti karena range tabelnya gak dikunci. Range E2:F50 bakal geser jadi E3:F51 dan seterusnya, jadi baris bawah tabelnya kepotong. Ganti jadi $E$2:$F$50.
Sebagian besar iya. Referensi Excel Table pakai nama kolom dan udah stabil. Tapi kamu masih butuh dollar buat sel tunggal di luar tabel kayak tarif PPN.
Dollar ngunci apa yang ada di kanannya, jadi baca $B$2 sebagai kunci-kolom kunci-baris. Kesalahan reference biasanya gak nampilin error, cuma angka nol yang diam-diam salah. Dan F4 itu tombol yang bakal paling sering kamu pakai seumur hidup pakai Excel.
Buka file laporan kamu, cari satu rumus yang ngacu ke sel konstan, terus cek baris paling bawah. Kalau hasilnya nol, kamu baru nemu bug yang udah lama nginep di situ.
Kalau rumus kamu udah panjang dan penuh dollar, mampir ke panduan rumus LET Excel buat merapikannya jadi variabel bernama.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.