Referensi Cell dalam Ms Excel: Absolute, Relatif, dan Campuran

Referensi Cell dalam Ms Excel: Absolute, Relatif, dan Campuran

Dalam Ms Excel, istilah referensi merujuk pada alamat cell atau beberapa cell (di sebut Range). Contoh alamat cell misalnya A1, B3, dan B5. A1 maksudnya adalah cell yang ada kolom A baris 1, B3 adalah cell di kolom B baris 3, dan seterusnya. Sedangkan contoh RANGE misalnya A1:D10, artinya semua cell yang terletak mulai dari kolom A sampai D, dan mulai dari baris 1 sampai 10 (berbentuk table persegi Panjang). Referensi biasanya dipakai untuk membangun formula (rumus) dan argument pada fungsi.

Saat menggunakan bekerja formula dan fungsi, terdapat 3 jenis referensi: Relatif, Absolute, dan  Campuran.

  • Relatif, nilai baris dan kolom dapat berubah ketika formula dicopy atau dipindahkan ke cell lain, ini karena referensi jenis ini pada dasarnya mengacu pada offset dari baris dan kolom yang dipakai. Contoh: A1, B5, D100, dan seterusnya.
  • Absolute, nilai baris dan kolom tidak berubah ketika formula dicopy atau dipindahkan ke cell lain, ini karena referensi jenis ini mengacu pada alamat cell yang actual (bukan offset). Untuk membuat referensi menjadi absolut, cukup ditambahkan tanda $ didepan kolom atau baris. Misal: $A$1, $B$5, $D$100, dan seterusnya
  • Campuran, salah satu dari alamat cell (kolom atau baris) adalah relative dan yang satunya adalah absolut. Misal: $A1 (kolom absolut, baris relative), atau A$1 (baris relative dan kolom absolut).

Sedangkan berdasarkan sumber lokasi sumber file-nya, dapat dibedakan menjadi:

  • referensi internal, yaitu mengacu pada sheet yang ada di Workbook (file) yang sama
  • referensi eksternal, yaitu mengacu pada lokasi yang ada di Workbook (file) yang lain.

Untuk lebih memahami tentang konsep ini, mari kita ikuti contoh-contoh berikut ini.

Referensi Relatif

  1. Misalnya kita memiliki table gaji pada Microsoft Excel sebagai berikut.
  2. contoh-referensi-relatif-ms-excel-1
  3. Kemudian kolom L3 kita isi dengan rumus =G3+H3+I3+J3+K3 yang artinya menjumlahkan semua komponen gaji. Total penjumlahan pada kolom JUMLAH.
  4. contoh-referensi-relatif-ms-excel-2
  5. 1.      Copy rumus tersebut dengan cara tempatkan mouse pada ujung kanan bawah cell L3, maka pointer mouse berubah menjadi (+), kemudian kita klik dan drag (tarik) bawah
  6. ms excel copy rumus dengan drag mouse
  7. Maka total akan dari setiap baris akan muncul seperti gambar di bawah.
  8. contoh-referensi-relatif-ms-excel-4
  9. Jika kita cek satu per satu cell pada kolom Jumlah (kolom L), maka rumus yang dibuat oleh Ms Excel secara otomatis pada saat men-drag ke bawah adalah seperti berikut:
  10. contoh-referensi-relatif-ms-excel-5
  11. Inilah yang dimaksud REFERENSI RELATIF, dimana nomor baris akan berubah secara otomatis dari 3 ke 4 ke 5 ke 6, dan seterusnya pada saat rumus di copy.

 

Referensi Absolut

Misalnya terdapat table penjualan dari SalesRep seperti bawah ini. Ada komisi sebesar 5% untuk setiap hasil penjualan, maka rumus Excel  yang kita masukan ke C4 adalah =B5*B2, sebagai komisi untuk Sales 1.

contoh-referensi-absolute-ms-excel-1

 

Jika rumus tersebut kita drag ke bawah (seperti pada contoh di seksi sebelumnya), maka hasilnya seperti berikut

contoh-referensi-absolute-ms-excel-2

 

Mengapa terjadi kesalahan?

Cell C7 akan berisi rumus yang salah. Yang kita inginkan adalah adalah =B7*B2, sedangkan isinya adalah =B7*B4. B2 menjadi B4 karena kita memperlakukan B2 sebagai referensi relative.

Untuk mengunci referensi B2 agar tidak berubah saat kita menyalin (men-drag) ke bawah, gunakan tanda $ (dollar) di depan baris dan kolomnya. Inilah yang disebut Referensi Absolute.

Rumus di C5 kita ganti menjadi

= B5*$B$2

Referensi $B$2 artinya kita mengunci kolom B dan baris 2.

Sehingga hasilnya menjadi seperti berikut

contoh-referensi-absolute-ms-excel-3

Bisa kita lihat, bagian $B$2 tidak berubah dengan drag/copy rumus ke bawah.

Referensi Campuran
Selain dengan pada C5 menggunakan rumus

 = B5*$B$2

kita juga bisa menggunakan rumus

= B5*B$2

yang akan memberikan hasil yang sama.

Mengapa? Karena pada dasarnya, jika kita mencopy rumus secara vertical, maka yang perlu di kunci adalah nomor baris (dalam hal ini nilai 2). Sedangkan jika kita membutuhkan rumus untuk dicopy secara horizontal, maka yang perlu dikunci adalah nomor kolom. Referensi seperti B$2 ini yang disebut Referensi Campuran.

 

Referensi Internal dan Eksternal

Semua contoh yang sudah kita lihat di atas (baik referensi relative, absolut, maupun campuran) secara lokasi dikategorikan pada referensi internal. Yaitu referensi pada Workbook (file) yang sama.

Ms Excel memiliki fitur untuk me-referensi pada cell yang berlokasi di Workbook (file) yang lain, atau yang disebut referensi eksternal. Jenis referensi ini, bisa dikatakan membuat link antara satu Workbook dengan Workbook yang lain. Keuntungannya, jika kita mengupdate Workbook yang menjadi referensi, maka nilai/isi formula pada Workbook yang satunya akan ikut berubah..

Sintaknya untuk referensi eksternal adalah sebagai berikut:

=‘C:\Latihan Excel\[latihan-excel-referensi-absolute1.xlsx]Sheet1’!$A$1

dimana

C:\Latihan Excel\ Folder kerja
[latihan-excel-referensi-absolute1.xlsx] File Workbook yang menjadi referensi
Sheet1 Nama Sheet dari Workbook
$A$1 Referensi cell

 

 

Leave a Reply

Close Menu