Referensi relatif dan absolut · Excel

Pahami kenapa rumus yang di-fill bergeser, kunci referensi dengan $ supaya selalu menunjuk sel yang sama, dan gunakan referensi campuran seperti $A2 dan B$1 untuk mengisi seluruh tabel dari satu rumus.

Idenya

Di modul 2 kamu sudah melihat bahwa saat =B2*C2 diisi ke bawah, rumusnya berubah menjadi =B3*C3, =B4*C4, dan seterusnya. Itu terjadi karena Excel sebenarnya tidak mengingat "B2". Yang diingat adalah letak B2 dibandingkan dengan rumusnya: "sel dua kolom di sebelah kiriku, di baris yang sama denganku". Saat rumusnya turun satu baris, "baris yang sama denganku" ikut turun. Referensi seperti ini disebut relatif, dan hampir selalu memang itu yang kamu mau, karena setiap baris seharusnya memakai harga dan jumlahnya sendiri.

Tapi ada angka yang tidak per baris. Contohnya tarif pajak. Hanya ada satu tarif untuk seluruh toko, diketik sekali di bagian atas sheet, dan pajak setiap produk seharusnya memakai sel yang sama itu. Kalau referensinya bergeser saat rumus diisi ke bawah, baris 3 akan mencari tarifnya satu baris lebih bawah, menemukan sel kosong, dan hasilnya salah. Untuk kasus seperti ini kamu mengunci referensinya dengan tanda dolar, sehingga menjadi absolut: referensinya menunjuk sel yang sama di mana pun rumusnya berada.

Analogi

Referensi relatif adalah arah ("rumah sebelah"); kalau kamu pindah, ia menunjuk tempat baru. Referensi absolut adalah alamat jalan ("Jalan Sudirman 12"); artinya tetap sama di mana pun kamu berdiri.

Masalahnya

Mari lihat masalah itu terjadi sungguhan. Sheet modul ini kembali berisi daftar produk kafe, dengan tarif pajak 11% di F1 yang seharusnya dipakai oleh setiap produk:

	A	B	C	D	E	F
1	Product	Price	Qty		Tax rate	11%
2	Kopi Susu	25000	12

Pajak sebuah produk adalah harganya dikali tarif. Lihat apa yang terjadi saat rumus yang paling jelas diisi ke bawah:

D1: Tax
D2:D6: =B2*F1

D1: Tax
D2: 2750
D3: 0
D4: 0
D5: 0
D6: 0

Hanya baris pertama yang benar. Pajak Kopi Susu adalah 25000 × 11% = 2750, tapi setiap produk setelahnya menampilkan 0. Klik D3 untuk melihat penyebabnya: isinya =B3*F2. Pengisian tadi menggeser kedua referensi satu baris ke bawah. Menggeser B2 ke B3 sudah benar, karena itu harga Teh Tarik. Tapi menggeser F1 ke F2 membuat rumusnya menunjuk sel kosong, dan apa pun dikali sel kosong hasilnya 0.

Yang membuat kesalahan ini licik adalah baris pertamanya terlihat sempurna. Kalau kamu hanya mengecek bagian atas kolom, kamu tidak akan tahu sisanya salah.

Referensi absolut: $

Solusinya adalah memberi tahu Excel bahwa F1 tidak boleh bergeser. Caranya dengan menaruh $ di depan bagian yang ingin kamu kunci. $ di depan huruf kolom mengunci kolomnya, dan $ di depan nomor baris mengunci barisnya. $F$1 mengunci keduanya, jadi tetap di F1 ke mana pun rumusnya pergi:

D1: Tax
D2:D6: =B2*$F$1

D1: Tax
D2: 2750
D3: 1980
D4: 2475
D5: 3850
D6: 1320

Sekarang D3 berisi =B3*$F$1, D4 berisi =B4*$F$1, dan seterusnya. Referensi harganya tetap bergeser ke setiap baris, tapi tarifnya diam di tempat. Dan karena setiap baris membaca tarif dari sel yang sama, mengubah F1 menjadi 12% langsung memperbarui semua pajak di kolom itu sekaligus.

Tips

Saat mengedit rumus di Excel, letakkan kursor pada sebuah referensi lalu tekan F4 (Fn + F4, atau ⌘ + T di sebagian Mac). Setiap tekanan berganti F1 → $F$1 → F$1 → $F1 → F1.

Porsi dari total

Tarif pajak bukan satu-satunya angka yang dipakai bersama. Satu lagi yang akan sering kamu temui adalah total. Misalkan kamu ingin tahu berapa porsi setiap produk dari semua cangkir yang terjual. Kamu menghitung totalnya sekali, di C7, lalu setiap baris membagi jumlahnya sendiri dengan total yang sama itu. Jadi referensi jumlahnya harus bergeser, dan totalnya harus dikunci:

# Total jumlah, yang menjadi pembagi setiap porsi
C7: =C2+C3+C4+C5+C6
G1: Share
G2:G6: =C2/$C$7

C7: 48
G1: Share
G2: 0.25
G3: 0.1875
G4: 0.1458333333
G5: 0.1041666667
G6: 0.3125

Totalnya 48 barang terjual, dan 12 milik Kopi Susu adalah 0,25, seperempat dari semua penjualan. Pisang Goreng paling laris dengan 0,3125. Tanpa tanda dolar, G3 akan membagi dengan C8, bukan C7. C8 kosong, jadi mulai baris kedua kamu akan mendapat #DIV/0!.

Referensi campuran

Sejauh ini kamu mengunci kolom dan baris sekaligus. Tapi kamu juga bisa mengunci salah satunya saja, yang terdengar aneh sampai kamu benar-benar membutuhkannya:

Referensi	Saat diisi ke bawah	Saat diisi ke kanan
`B2`	baris berubah	kolom berubah
`$B$2`	tetap	tetap
`$B2`	baris berubah	kolom tetap
`B$2`	baris tetap	kolom berubah

Referensi campuran berguna untuk tabel dua arah, di mana ada nilai yang berjajar ke bawah di sisi kiri dan nilai yang berjajar ke samping di bagian atas, dan setiap sel di tengahnya menggabungkan satu dari masing-masing. Tabel harga dengan produk di sisi kiri dan jumlah pembelian di bagian atas adalah contoh yang umum. Versi paling sederhananya adalah tabel perkalian: angka ke bawah di kolom H, angka melintang di baris 1, dan setiap sel mengalikan "angka di awal barisku" dengan "angka di atas kolomku":

H2: 1
H3: 2
H4: 3
I1: 10
J1: 20
K1: 30
I2:K4: =$H2*I$1

H2: 1
H3: 2
H4: 3
I1: 10
J1: 20
K1: 30
I2: 10
J2: 20
K2: 30
I3: 20
J3: 40
K3: 60
I4: 30
J4: 60
K4: 90

Triknya, setiap referensi hanya mengunci bagian yang harus tetap. $H2 mengunci kolomnya, jadi selalu melihat kolom H, tapi barisnya bebas berubah, jadi setiap baris menemukan angkanya sendiri. I$1 mengunci barisnya, jadi selalu melihat baris 1, tapi kolomnya bebas, jadi setiap kolom menemukan angkanya sendiri di bagian atas. Satu rumus mengisi seluruh kisi 3 × 3. Klik K4 dan kamu akan melihat rumusnya menjadi =$H4*K$1: angka baris 4 dikali angka kolom K, 3 × 30 = 90.

Awas

Kalau rumus yang di-fill benar di sel pertama tapi menghasilkan angka aneh atau nol di sel lainnya, ada referensi yang seharusnya dikunci tapi belum. Klik sel kedua dan baca rumusnya; referensi yang melenceng akan langsung kelihatan.

Contoh lengkap: komisi penjualan

Staf mendapat komisi dari penjualannya, dan tarifnya disimpan di satu sel supaya manajer bisa mengubahnya. Di sini tarifnya ada di F3, di sebelah labelnya. Setiap komisi memakai tarif yang sama (absolut) dan penjualan di barisnya sendiri (relatif):

E3: Commission rate
F3: 2.5%
H1: Staff
H2: Ayu
H3: Budi
I1: Sales
I2: 4200000
I3: 3150000
J1: Commission
J2:J3: =I2*$F$3

E3: Commission rate
F3: 2.5%
H1: Staff
H2: Ayu
H3: Budi
I1: Sales
I2: 4200000
I3: 3150000
J1: Commission
J2: 105000
J3: 78750

Ayu menjual 4200000, jadi komisinya 4200000 × 2,5% = 105000. Komisi Budi 78750. Saat manajer memutuskan menaikkan tarifnya menjadi 3%, cukup satu sel yang diubah dan semua komisi ikut diperbarui. Itulah alasan sebenarnya referensi absolut penting: setiap angka yang dipakai bersama tinggal di tepat satu tempat. Kalau tarifnya diketik langsung ke dalam 200 rumus, mengubahnya berarti mencari dan mengedit 200 rumus itu, dan terlewat satu saja berarti ada orang yang gajinya salah.

Kode bersih

Beri input bersama sel sendiri yang diberi label jelas, di dekat bagian atas sheet atau di sheet "Pengaturan" terpisah. Rumus seperti =I2*$F$3 dengan label di sebelah F3 menjelaskan dirinya sendiri; =I2*0.025 menyembunyikan aturan bisnis di dalam rumus.

Menyalin vs memindahkan, sekali lagi

Semua yang ada di modul ini juga berlaku untuk copy dan paste, bukan hanya fill handle. Saat kamu menyalin rumus lalu menempelkannya di tempat lain, referensi relatifnya bergeser sejauh tempat kamu menempelkannya, dan referensi absolutnya tetap persis seperti semula. Memindahkan itu berbeda: cut dan paste, atau menyeret garis tepi pilihan, tidak pernah mengubah referensi apa pun, karena Excel menganggapnya sebagai memindahkan data, bukan membuat salinan baru.

Rangkuman

Referensi bersifat relatif secara bawaan; fill atau copy menggesernya.
$ mengunci yang ada di belakangnya: $F$1 sepenuhnya absolut.
Kunci input bersama (tarif, total) supaya setiap baris memakai sel yang sama.
Referensi campuran ($H2, I$1) hanya mengunci kolom atau hanya baris; cocok untuk tabel dua arah.
F4 berganti-ganti di antara keempat jenis referensi.