Panduan Lengkap Referensi Cell

Referensi Cell di Excel
Excel menyediakan 3 pilihan untuk mengatur referensi cell, yaitu Relative Reference, Absolute Reference dan Mixed Reference.

Relative Cell Reference

Misal ada data seperti gambar dibawah. Fulan adalah orang yang amat sangat baik sekali 🙂 . Dia membelikan 2 iPhoneX untuk kedua orang tuanya, 1 Galaxy S9+ untuk istrinya, 3 Moto G6 untuk adik-adiknya dan 4 Honor 7X untuk teman-temannya. Berapakah uang yang harus dibayarkan untuk membeli semua hadiah tersebut?

Referensi Cell di Excel

Untuk mengetahui berapa total uang yang harus dibayarkan. Pertama harus dihitung dahulu berapa subtotal untuk masing-masing hadiah. Subtotal dihitung dengan mengalikan harga dan jumlahnya.

Berikut langkah-langkah untuk menghitung subtotal.

  1. Letakkan kursor di cell E2
  2. Ketik sama dengan “=”
  3. Arahkan kursor ke cell C2
  4. Ketik tanda asterisk “*”
  5. Arahkan kursor ke cell D2
  6. Tekan tombol ENTER

Referensi Cell di Excel

Hasilnya adalah harga yang harus dibayar untuk 2 iPhoneX 256GB. Bagaimana dengan subtotal item lainnya?, apakah harus mengetik formula satu persatu untuk setiap item?, tentu tidak. Jika ada banyak item maka akan sangat membuang-buang waktu.

Solusinya sederhana lakukan kopi di cell E2 (CTRL+C) yang sudah berisi formula kemudian paste (CTRL+V) di range E3:E5. Hasilnya seperti gambar dibawah.

Referensi Cell di Excel

Subtotal setiap item berbeda dengan subtotal dibaris pertama, kenapa ini bisa terjadi, padahal kita melakukan kopi data yang ada dicell E2, kenapa data dirange E3:E5 tidak sama dengan data dicell E2?.

Setiap cell yang berisi formula jika dikopi maka yang dikopi adalah formulanya, bukan datanya. Artinya Excel mengkopi formula =C2*D2. Jika formula diletakkan 1 baris dibawah cell E2 maka formula akan berubah menjadi =C3*D3 (bergeser 1 baris kebawah), jika diletakkan 2 baris dibawahnya formula menjadi =C4*D4 (bergeser 2 baris kebawah) dan seterusnya.

Itulah yang dinamakan relative cell reference. Berterimakasihlah kepada excel yang telah membuat relative cell reference. Dengan ini tidak perlu membuat formula satu persatu untuk setiap item yang ada.

Jadi relative cell reference adalah alamat cell yang akan berubah menyesuaikan lokasi cell yang baru ketika dikopi. Relative cell reference merupakan perlakuan default Excel untuk semua formula yang ada.

Referensi Cell di Excel

Untuk menghitung total yang harus dibayarkan menggunakan fungsi SUM. Silahkan baca artikel berikut untuk penjelasan lebih detail tentang fungsi SUM dan bagaimana menggunakan rumus SUM.

Referensi Cell di Excel

Absolute Cell Reference

Misal ada data seperti gambar dibawah. Data yang mirip dengan contoh sebelumnya dengan penambahan data diskon. Berapakah diskon untuk masing-masing item?

Formula untuk menghitung diskon adalah formula perkalian antara data subtotal1 dengan data diskon yang ada dicell C2.

  1. Letakkan kursor di cell F5
  2. Ketik tanda sama dengan “=”
  3. Arahkan kursor ke cell E5
  4. Ketik tanda asterisk “*”
  5. Arahkan kursor kecell C2
  6. Tekan tombol ENTER

Hasilnya seperti gambar dibawah

Referensi Cell di Excel

Bagimana dengan diskon lainnya?, cukup lakukan kopi dicell F5 kemudian paste dirange F6:F8. Hasilnya seperti gambar dibawah?

Referensi Cell di Excel

Kenapa hasilnya tidak sesuai dengan yang diharapkan?

Cek formula di cell F5, data subtotal mengarah ke cell E5, benar dan diskon mengarah ke cell C2, benar.

Referensi Cell di Excel

 

Cek formula di cell F6, data subtotal mengarah ke cell E6, benar dan diskon mengarah ke cell C3, salah.

Referensi Cell di Excel

 

Cek formula dicell F7, data subtotal mengarah ke cell E7, benar dan diskon mengarah ke cell C4 salah.

Referensi Cell di Excel

 

Cek formula dicell F8, data subtotal mengarah ke cell E8, benar dan diskon mengarah ke cell C5, salah.

Referensi Cell di Excel

Formula yang salah adalah formula dibagian data diskon, ketika dikopi kebawah alamat cell yang mengarah ke data diskon ikut bergeser kebawah. Seharusnya tetap mengarah ke cell C2.

Inilah saatnya menggunakan absolute cell reference, supaya ketika alamat cell dikopi dan diletakkan ditempat baru tidak berubah dan tetap mengarah kealamat yang sama.

  1. Letakkan kursor dicell F5
  2. Lakukan edit dengan menekan tombol F2
  3. Blok alamat cell C2
  4. Tekan tombol F4 sekali, alamat cell C2 berubah menjadi $C$2, ada tanda dolor didepan masing masing huruf kolom dan angka baris. Ini pertanda bahwa cell C2 menggunakan absolute cell reference
  5. Tekan tombol ENTER
  6. Kopi cell F5 (CTRL+C) kemudian paste (CTRL+V) di range F6:F8

Hasilnya seperti gambar dibawah, alamat cell C2 tidak berubah walaupun dikopi ke bawah.

Referensi Cell di Excel

Subtotal2 dihitung menggunakan rumus pengurangan, dengan mengurangi data subtotal1 dengan data diskon

Referensi Cell di Excel

Absolute cell reference adalah kebalikan dari relative cell reference. Jika formula menggunakan relative cell reference dikopi dan diletakkan ditempat baru maka alamat cell yang ada didalam formula akan berubah menyesuaikan dengan lokasi baru.

Alamat cell formula dengan absolute reference tidak akan berubah ketika dikopi dan diletakkan ditempat yang baru.

Absolute reference ditanda dengan adanya tanda $ yang ada didepan huruf kolom dan nomor baris. Untuk menggunakan absolute reference bisa dengan mengetik langsung tanda $ tersebut atau bisa juga dengan menekan tombol F4 sekali.

Absolute vs Relative Cell Reference

Apa perbedaan antara absolute dan relative cell reference?. Seperti sudah dijelaskan diatas alamat cell dengan relative reference akan berubah ketika dikopi dan ditempatkan ditempat baru sedangkan alamat cell dengan absolute reference tidak berubah ketika dikopi dan ditempatkan ditempat baru. Penggunaannya menyesuaikan dengan kebutuhan dan kondisi.

Mixed Cell Reference

Misal ada data seperti gambar dibawah. Data yang mirip dengan contoh sebelumnya dengan penambahan data sales tax. Berapakah sales tax untuk masing-masing item? Bisakah formula untuk menghitung diskon digunakan juga untuk menghitung sales tax?

Referensi Cell di Excel

  1. Pilih range F5:F8 kemudian lakukan copy (CTRL+C)
  2. Letakkan kursor di cell G5
  3. Lakukan paste (CTRL+V)

Hasilnya seperti gambar dibawah.

Referensi Cell di Excel

Angka yang dihasilkan tidak sesuai, kenapa bisa seperti ini?.

Referensi Cell di Excel

Cek formula di cell G5. Referensi cell subtotal1 seharusnya tetap mengarah kecell E5 ternyata bergeser 1 kolom kekanan mengarah kecell F5, sedangkan referensi cell data sales tax yang seharusnya mengarah ke cell D2, bergeser 1 kolom kekanan tetap mengarah kecell C2.

Untuk referensi cell subtotal1, ketika dikopi ke kanan, dari kolom F (Diskon) ke kolom G (Sales Tax) posisi kolom tidak berubah, tetap di kolom D. Sebaliknya jika dikopi ke bawah dari baris 5 sampai baris 8, posisi baris harus berubah.

Jadi penulisan referensi cell subtotal1 adalah $E5, kolom menggunakan absolute reference, ditandai dengan tanda $ dan baris menggunakan relative reference.

Untuk referensi cell diskon, ketika dikopi ke kanan, posisi kolom harus berubah. Sebaliknya jika dikopi ke bawah posisi baris tidak berubah.

Jadi penulisan referensi cell diskon adalah C$2, kolom menggunakan relative reference dan baris menggunakan absolute reference, ditandai dengan tanda $.

Edit formula di cell F5. Blok cell E5 kemudian tekan tombol F4 3 kali, Blok cell C2 kemudian tekan tombol F4 2 kali. Formulanya menjadi =$E5*C$2, kemudian tekan tombol ENTER.

Untuk menghitung diskon item lainnya. Lakukan kopi (CTRL+C) dicell F5, kemudian lakukan paste (CTRL+V) dirange F6:F8. Untuk menghitung sales tax, lakukan lakukan paste (CTRL+V) dirange G5:G8.

Hasilnya seperti gambar dibawah.

Referensi Cell di Excel

Cek cell F5, referensi cell subtotal1 mengarah ke cell E5 dan referensi cell diskon mengarah ke cell C2.

Referensi Cell di Excel

Cek cell G5, referensi cell subtotal1 tetap mengarah kecell E5 dan referensi cell sales tax bergeser 1 kolom kekanan ke cell C3.

Referensi Cell di Excel

Subtotal2 dihitung dengan menggunakan rumus pengurangan dan rumus penjumlahan, data subtotal1 dikurangi data diskon kemudian ditambah dengan data sales tax.

Referensi Cell di Excel

Dengan memahami perbedaan relative reference, absolute reference dan mixed reference penulisan formula bisa dilakukan hanya sekali untuk berbagai keperluan. Untuk kasus di atas satu formula bisa digunakan untuk menghitung diskon dan sales tax sekaligus.

***

Jadi mixed cell reference adalah perpaduan antara relative cell reference dan absolute cell reference. Saat menggunakan relative cell reference maka semuanya relative baik kolom ataupun baris begitu juga ketika menggunakan absolute cell reference semuanya menjadi absolute.

Mixed cell reference bisa memadukan keduanya. Relative secara kolom absolute secara baris atau sebaliknya absolute secara kolom dan relative secara baris.

Untuk menggunakan mixed reference bisa menggunakan tanda dolar “$” didepan huruf kolom atau nomor baris. Penempatan tanda dolar menentukan kolom/baris mana yang akan menjadi absolute.

Alamat cell $A1 menunjukkan kolom A menggunakan absolute cell reference sedangkan baris 1 menggunakan relative cell reference, mixed reference ini bisa dibuat dengan menekan tombol F4 3 kali.

Alamat A$1 menunjukkan sebaliknya kolom menggunakan relative cell reference dan baris menggunakan absolute cell reference, mixed reference ini bisa dibuat dengan menekan tombol F4 2 kali.

3D Cell Reference

Misal ada data seperti gambar dibawah, ada 3 worksheet. Setiap worksheet mempunyai struktur data yang sama. 3 kolom dengan 8 cellphone dan data penjualannya. 2 worksheet berisi data penjualan untuk sebuah are dan 1 worksheet berisi total penjualan untuk semua area. Bagaimanakah formula untuk menghitung total penjualan semua area?.

Referensi Cell di Excel

Referensi Cell di Excel

Referensi Cell di Excel

Solusi dengan Rumus Penjumlahan

Berikut langkah-langkah untuk membuat formula dengan menggunakan tanda “+”

  1. Letakkan kursor di cell C2, worksheet “Total”
  2. Ketik =
  3. Klik worksheet “West”, kemudian klik cell C2
  4. Ketik +
  5. Klik worksheet “East”, kemudian klik cell C2
  6. Tekan tombol ENTER

Hasil formula di cell B2 adalah

=West!C2 +East!B2

Untuk menghitung total omset cellphone lainnya. Lakukan kopi (CTRL+C) dicell C2, kemudian lakukan paste (CTRL+V) dirange C3:C9. Hasilnya seperti gambar dibawah.

Referensi Cell di Excel

Cara seperti ini mudah karena jumlah worksheet sedikit. Jika jumlah worksheet semakin banyak maka akan memakan waktu dan memperbesar kemungkinan kesalahan. Selain itu jika ada penambahan worksheet (area baru), formula di atas harus diedit untuk memasukkan omset dari worksheet baru.

Masalah di atas bisa diselesaikan dengan 3D Reference. Fungsi SUM akan digunakan untuk menghitung total omset.

Solusi dengan 3D Reference

Berikut langkah-langkah untuk membuat formula dengan menggunakan 3D reference.

  1. Letakkan kursor di cell C2, worksheet “Total “
  2. Ketik =SUM(
  3. Klik worksheet “West”
  4. Tekan tombol SHIFT dan tahan
  5. Klik worksheet “East”, kemudian lepas tombol SHIFT
  6. Klik cell C2
  7. Ketik )
  8. Tekan tombol ENTER

Hasil formula di cell C2 adalah

=SUM(West:East!C2)

Untuk menghitung total omset cellphone lainnya. Lakukan kopi (CTRL+C) dicell C2, kemudian lakukan paste (CTRL+V) di range B3:B7. Hasilnya seperti gambar di bawah.

Referensi Cell di Excel

Tidak ada perbedaan hasil dengan cara sebelumnya. Perbedaan baru muncul jika ada penambahan worksheet baru.

Referensi Cell di Excel

Penambahan worksheet baru akan secara otomatis mengubah total omset tanpa mengubah formula. Sebagai catatan worksheet baru yang ditambahkan harus berada di antara worksheet “West” dan “East”.

Jadi, 3D cell reference adalah referensi cell yang digunakan dibeberapa worksheet dalam satu workbook yang mempunyai struktur data yang sama.

External Cell Reference

Misal ada 2 area penjualan (west, east,), data setiap area disimpan dalam sebuah file Excel, kemudian ada satu file Excel yang berisi total omset penjualan semua area.

Berikut langkah-langkah untuk membuat external reference.

  1. Buka semua file (west.xlsx, east.xlsx dan total.xlsx)
  2. Pilih workbook total.xlsx, letakkan kursor di cell C2
  3. Ketik =
  4. Switch windows (CTRL+F6), pilih west.xlsx, klik cell C2
  5. Ketik +
  6. Switch windows (CTRL+F6), pilih east.xlsx, klik cell C2
  7. Tekan tombol ENTER

Hasil formula di cell C2 adalah

=[west.xlsx]West!$C$2+[east.xlsx]East!$C$2

Ada tanda $ di depan alamat cell untuk masing-masing workbook. Ini menunjukkan formula di atas menggunakan absolute reference. Supaya formula di atas bisa dikopi, edit dan hilangkan tanda $ dimasing-masing alamat cell.

Untuk menghitung total omset cellphone lainnya. Lakukan kopi (CTRL+C) dicell C2, kemudian lakukan paste di range C3:C9. Hasilnya seperti gambar di bawah.

Referensi Cell di Excel

Jika data omset salah area diupdate, maka data total omset di workbook total.xlsx akan langsung terupdate.

Jadi, external cell reference adalah referensi cell yang digunakan untuk data yang mengarah ke file excel (workbook) lain.

File Latihan