Lompat ke konten
Anda disini: M Jurnal » Rumus Excel » Wajib Tahu !! Cell Reference Pada Fungsi dan Rumus Excel

Wajib Tahu !! Cell Reference Pada Fungsi dan Rumus Excel

Wajib Tahu !! Cell Reference Relative dan Absolute atau Lambang Dollar ( $ ) sebelum menggunakan rumus atau fungsi Excel

Pernahkan Anda menemukan Cell Reference seperti ini: =A1+A6 atau =$A$1+$A$6 atau seperti ini =A1+5-$A$6 ? Argument-argumen seperti inilah yang disebut dengan cell reference.

Rumus / Fungi Excel dan Cell Reference memiliki hubungan yang erat. Ketika Anda menggunakan Rumus / Fungsi Excel, Anda akan menggunakan Cell Reference sebagai rujukan rumus / fungsi.

Jika Anda memasukkan nilai secara manual pada rumus seperti =5+6. Maka Anda tidak menggunakan cell reference.

Namun apakah Anda selalu memasukkan rumus secara manual ? Apakah Anda tidak ingin menjumlahkan atau menghitung antar cell ?

Disinilah peran Cell Reference sangat penting. Seberapa pentingkah sehingga Anda harus mempelajarinya ? Silahkan lihat jawaban Saya di bawah.

Apa itu Cell Reference Excel ?

Cell Reference Excel adalah cell rujukan yang digunakan pada Formula / Rumus / Fungsi. Tujuan menggunakan Cell Reference untuk memberitahu Excel bahwa Formula / Fungsi / Rumus menggunakan data pada cell tersebut.

Karena setiap orang memiliki tujuan yang berbeda-beda, maka Excel memberikan 2 type referensi cell yaitu Relative dan Absolute.

Kedua tipe referensi ini bisa Anda gabungkan ataupun tidak untuk mendapatkan hasil yang maksimal. Seperti apa itu ? Mari simak contoh-contoh berikut:

Sebagai bahan latihan Excel kali ini, Silahkan download file Excel berikut:

Relative Cell Reference

Relative Reference pada Excel adalah tipe referensi yang dapat menyesuaikan perubahan posisi rumus berada.

Dengan kata lain, rumus / fungsi yang menggunakan relative reference akan berubah jika Anda menyalin (Copy-Paste) rumus ke cell lain atau jika Anda menggunakan fitur AutoFill Excel.

Note: Jika Anda menggunakan Cut kemudian Paste, Maka Excel tidak akan menyesuaikan rumus.

#1 Perubahan Cell Reference Pada Baris

Biar lebih paham, Silahkan buka Sheet Relative pada file Excel sebelumnya. Saya akan menjumlahkan data dengan kriteria sebagai berikut:

  1. Kolom E = Nilai 1 + Nilai 2
  2. Kolom F = Nilai 2 + Nilai 3
  3. Baris 7 merupakan jumlah dari semua data pada masing-masing kolom.

Saya asumsikan Anda sudah tahu cara memasukkan rumus / fungsi excel. Selanjutnya silahkan ikuti tahap-tahap berikut:

diskon-hosting

Referensi Sel Relatif Microsoft Excel
  1. Ketik rumus =B2+C2 pada Cell E2 untuk menghitung nilai pada kolom E. kemudian tekan Enter.
  2. Lakukan AutoFill Excel ke bawah untuk menghitung nilai pada Cell E3, E4, E5, dan E6. Berikut hasilnya:
Relative Cell Reference Excel

Anda bisa lihat pada gambar tersebut atau file Excel sebelumnya, Rumus pada setiap Cell berubah menjadi E2: =B2+C2, E3: =B3+C3, E4: =B4+C4, E5: =B5+C5 dan E6: =B6+C6.

Perubahan rumus hanya terjadi pada nomor baris (Row). Lihat rumus yang Saya beri tanda warna kuning.

Perubahan Referensi Cell akan disesuaikan dengan seberapa jauh Anda melakukan AutoFill atau Copy-Paste Rumus. Misalnya, coba Anda Copy Cell E2 ke Cell E24 (Ingat, Cell E2 berisi Rumus =B2+C2).

Penting! Ada banyak pilihan paste Excel seperti Formulas, Keep Source Formatting, Linked Picture dll, Saya harap Anda sudah memahami fitur Paste Special. Jika belum, silahkan kunjungi Panduan 15+ Contoh Copy-Paste Special Excel

Secara otomatis, Rumus pada Cell E24 akan berubah menjadi =B24+C24. Perubahan Relative Cell Reference ini akan mempermudah pekerjaa Anda.

#2 Perubahan Cell Reference Pada Kolom

Lalu gimana jika Anda ingin lakukan AutoFill atau Copy-Paste ke kanan / kekiri (berdasarkan kolom) ? Konsepnya sama saja. Namun yang berubah adalah Nama Kolom seperti: C2 menjadi D2

Contohnya, Silahkan lakukan AutoFill / copy-paste Range E2:E6 ke Range F2:F6. Secara otomatis, Excel akan merubah cell referensi seperti gambar berikut:

Penting! Jika Anda belum tahu apa itu Range, silahkan kunjungi Panduan Range, Cell, Column, dan Row Excel M Jurnal

Copy paste Excel

Terlihat, perubahan cell reference terjadi pada nama kolom saja menjadi Cell F2: =C2+D2, F3: =C3+D3, F4: =C4+D4, F5: =C5+D5 dan F6: =C6+D6.

Kesimpulan: Dengan Relative Cell Reference, Excel akan menyesuaikan rumus / fungsi / formula ketika Anda copy ataupun AutoFilll Cell.

Absolute Cell Reference ( Menggunakan Tanda Dollar $ )

Absolute Cell Reference adalah type referensi sel tetap (terkunci) yang ditandai dengan lambang dollar ($) pada sebelum nama column ($A1) dan atau sebelum nama Row (A$1) atau Kedua nya ($A$1).

Jika hanya ada 1 lambang dollar $ (baik pada Column atau Row), maka biasa disebut Semi Abloute Cell Reference. Jika nilai terdapat 2 lambang dollar $, maka disebut Absolute Cell Reference.

Trus buat apa sih gunanya ? Mari pelajari satu per satu:

#1 Semi Absolute Cell Reference untuk Row

Apa Anda ingat pengertian Absolute Cell Reference yang Saya sebutkan sebelumnya ? Gapapa baca lagi sebentar keatas.

Absolute ini adalah referensi tetap. Excel akan mengunci kolom atau baris atau kedua-dua nya sesuai dimana Anda meletakkan nilai Absolute. Apakah sebelum nama kolom atau sebelum nama baris.

Maksud dari mengunci kolom atau baris adalah Excel tidak akan mengubah referensi sel absolut jika Anda menyalin (copy-paste) atau AutoFill Cell ke cell lainnya.

Coba kita masuk ke contoh. Silahkan buka Sheet Semi Absolute. Saya akan menghitung Jumlah Nilai 1 dan 2 dengan Syarat: Nilai 1 memiliki bobot 40% dan Nilai 2 memiliki bobot 60%.

Lihat Kolom B (Nilai 1). Data tersusun rapi secara vertikal, sementara Data Bobot Nilai 1 hanya terdapat pada 1 cell yaitu C8. Begitu juga dengan data kolom C (Nilai 2) dengan bobot terdapat pada cell C9.

Karena data bobot masing-masing nilai hanya terdapat pada 1 cell, Anda harus menggunakan Reference Abolute agar rumus bisa disalin (copy-paste) atau AutoFill. Silahkan ikuti tahap-tahap berikut:

Semi Absolute Cell Reference Microsoft Excel
  1. Ketik rumus berikut pada Cell D2: =B2* . FYI, tanda Bintang * digunakan untuk rumus perkalian.
  2. Klik Cell C8. Kemudian tekan tombol F4 pada keyboard sebanyak 2x sampai lambang dollar ($) tersisip sebelum nama Row (baris). Sehingga Rumus akan menjadi seperti ini: =B2*C$8 .
    Jika Anda tidak bisa menggunakan tombol F4, silahkan ketik lambang $ secara manual sebelum nama baris.
  3. Ketik tanda tambah + , dan klik Cell C2. Kemudian ketik tanda bintang * seperti tahap 1.
  4. Klik Cell C9 dan berikan lambang dollar $ sebelum nama baris seperti tahap 2. Kemudian tekan Enter.
  5. Silahkan copy atau AutoFill rumus ini untuk menghitung nilai pada Cell D3 sampai D6. Berikut hasilnya:
Semi Absolute ( lambang dollar $ ) sel referensi microsoft excel

Ingat! Rumus yang Anda masukkan pada Cell D2 seperti ini: =B2*C$8+C2*C$9. Selanjutnya, lihat rumus pada Cell D3 sampai D6.

Apa yang tidak berubah ? Ya Anda benar! Excel tidak merubah referensi Row yang diberikan lambang dollar $.

#2 Semi Absolute Cell Reference untuk Column

Sebelumnya, kita menggunakan Nilai Absolute untuk mengunci referensi Row. Dengan menyisipkan lambang dollar $ sebelum nama baris C$9 dan C$10, Excel hanya akan mengunci referensi baris.

Coba Anda copy cell D2 ke Cell E2. Otomatis Rumus akan berubah menjadi =C2*D$8+D2*D$9. Kenapa demikian ? Karena nama kolom tidak dikunci menggunakan nilai Absolute.

Untuk kasus ini, Silahkan lihat Table ke dua pada Sheet yang sama (Semi Absolute). Data nya sama, hanya saja posisi data Saya Transpose dari Vertikal menjadi Horizontal.

Penting! Jika Anda ingin belajar cara Copy Transpose, Silahkan kunjungi Panduan Copy Transpose Excel M Jurnal

Selanjutnya, Silahkan ubah posisi referensi Absolute ke sebelum nama kolom. Sehingga rumus berubah menjadi =G2*$M5+G3*$N5.

Kemudian silahkan Copy / AutoFill Cell G3 ke Cell H3, I3, J3, dan K3. Anda pasti menemukan hasil yang pas. Nama kolom pada Cell M5 dan N5 pada rumus tidak akan berubah. seperti gambar berikut:

Semi Absolute Referensi Cell untuk rumus dan fungsi excel

#3 Absolute Cell Reference Row & Column

Jika Anda ingin mengunci reference kolom dan baris sekaligus, silahkan sisipkan lambang dollar $ ke sebelum nama kolom dan nama baris (Contoh $A$1)

Dengan cara ini, kemanapun Anda menyalin rumus, referensi cell (kolom dan baris) tidak akan berubah.

Silahkan lihat Sheet Absolute pada file Excel. Saya ingin setiap nilai pada kolom B dan C dikali dengan 2. Angka 2 Saya letakkan pada Cell B8.

Dengan menggunakan Absolute Cell Reference, Anda hanya perlu memasukkan rumus pada 1 Cell saja. Yang lainnya tinggal copy-paste atau AutoFill aja.

Contohnya ? Coba Anda masukkan Rumus =B2*$B$8 ke Cell D2. Kemudian silahkan AutoFill ke Cell lainnya. Hasilnya akan sama seperti gambar berikut:

Absolute ( lambang dollar $ ) cell reference Microsoft Excel untuk mengunci kolom dan baris

Bener toh ? Absolute Cell Reference mengunci cell B8. Sehingga ke cell manapun Anda menyalin rumus ini, Excel tetap menggunakan Cell B8 sebagai patokan rumus.

Anda Sudah Lulus Latihan Panduan ini. Lalu Apa Selanjutnya ?

Silahkan gunakan Pintasan Panduan Cell Reference Excel berikut untuk mempelajari semua tentang Cell Reference. Atau kunjungi Pusat Panduan Excel M Jurnal untuk mempelajari semua tentang Excel. 🙂

Pintasan Panduan Cell Reference Excel

Bab sebelumnya: Rumus & Fungsi.

  1. Cell References: (Anda Disini).
  2. 3D-Reference: Cara menggunakan Rumus / Fungsi untuk Beda Sheet serta 5+ Contoh Mendalam (Bonus Studi Kasus membuat Rekap Data Antar Sheet).
  3. Structured References: Referensi terstruktur pada Tabel untuk membuat rumus / fungsi lebih mudah dipahami.
  4. Union & Intersect: Belajar Operator Rumus / Fungsi Excel. Seperti koma (,) atau titik koma (;), dan spasi ( ).
  5. External References: Cara menggunakan Rumus / Fungsi pada File Excel (Workbook) berbeda.
  6. Hyperlinks: Cara membuat hyperlink antar sheet, beda file Excel, ke file lain (seperti Gambar, PDF, Word dll), hingga trik membuat design hyperlink menggunakan tombol.

Bab berikutnya: COUNT & SUM.

Komentar Anda:

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *