Membuat Formula dari Referensi Sel Relatif dan Absolut

Pada aplikasi microsoft excel, dikenal dengan referensi sel relatif dan referensi sel absolut. Kedua istilah ini mengacu pada sifat sel yang digunakan untuk membuat formula. Biasanya, ketika kamu copy dan paste sebuah sel, terjadi perubahan pada formula yang telah kamu buat. Keadaan ini karena sel bersifat relatif sehingga mudah berganti. Namun, bisa jadi sel yang kamu copy kemudian paste tidak berubah. Sel seperti ini memiliki sifat absolut atau mutlak. Secara lebih lengkap, berikut ini akan dibahas mengenai referensi sel relatif dan absolut serta cara membuat formulanya.

Referensi Sel Relatif

Secara default, semua referensi sel bersifat relatif, yaitu ketika disalin (copy) di beberapa sel akan berubah berdasarkan posisi relatif baris dan kolom. Misalnya, jika kamu menyalin rumus =A1+B1 dari baris 1 ke baris 2, maka rumus akan menjadi =A2+B2. Referensi sel relatif berguna jika kamu ingin mengulang perhitungan yang sama pada beberapa sel.

Pada contoh berikut ini, akan dibuat formula yang melipatgandakan harga setiap item berdasarkan kuantitas. Agar lebih muda dan efektif, daripada membuat formula baru untuk setiap baris, kamu bisa membuat formula tunggal dalam satu sel kemudian menyalin ke baris lainnya. Cara melakukannya, kamu bisa melakukan seperti contoh berikut.

  • Isi data yang sesuai seperti gambar.

  • Letakkan kursor pada sel D3

  • Buat formula perkalian dari harga unit dengan quantity, yaitu =B3*C3

  • Tekan enter, maka pada sel D3 akan tampak hasil perkalian dari formula yang telah dibuat, yaitu Rp84.000,00

  • Salin formula yang telah dibuat dengan mengarahkan kembali kursor pada sel D3, kemudian tarik fill handle hingga sel D8 kemudian lepaskan.

Formula pertama yang kamu buat adalah =B2*C2. Namun, setelah kamu copy dan pastekan pada sel di bawahnya, setiap sel memiliki formula yang berbeda. Kamu bisa melihat sendiri, dengan mengklik dua kali pada sel-sel tersebut. Misalnya pada sel D7, maka formula menjadi =B7*C7.

Karena formula berubah setelah dicopy dan dipaste, maka setiap referensi sel tersebut dinamakan sebagai referensi sel relatif.

Referensi Sel Absolut (Mutlak)

Setelah mengetahui cara membuat formula pada referensi sel relatif, sekarang kamu bisa mencoba untuk membuat formula referensi sel absolut atau mutlak. Berbeda dengan referensi sel relatif, referensi sel ini tidak akan berubah meskipun berkali-kali dicopy dan dipaste pada sel lain.

Sebagai contoh, sama seperti data sebelumnya, yaitu penjualan unit baju sesuai dengan ukuran, namun untuk saat ini diberlakukan discount atau potongan harga 10%. Tahap-tahap dalam membuatnya yaitu:

  • Isikan data seperti gambar berikut

  • Letakkan kursor pada sel D3 untuk menuliskan formula dan menampilkan nilai.

  • Buat formula untuk menghitung total harga, yatu harga unit dikali quantity dikurang 10% dari total harga sebelum diskon. Formula bisa kamu buat seperti =B2*C2-B2*C2*D1.

  • Jika kamu tekan enter, maka akan tampil hasilnya yaitu Rp75.600,00. Tapi, untuk membuat referensi sel absolut, kamu tidak bisa langsung tekan enter, melainkan memberi tanda $ terlebih dahulu sebelum D dan sebelum 1 sehingga formula menjadi =B2*C2-B2*C2*$D$1.

  • Selanjutnya, kamu bisa tekan enter dan lihat hasil nilai dari formula yang kamu buat, yaitu Rp75.600,00.

  • Copy dan paste sel D3 dengan mengarahkan kembali kursor pada sel D3 dan tarik fill handle hingga sel D8 kemudian lepaskan.

Setelah selesai, maka kamu akan mendapatkan hasil perhitungan dari formula yang telah kamu buat telah dipastekan pada sel-sel lain di bawahnya. Jika kamu klik setiap sel pada kolom D, maka akan didapati sel $D$1 pada setiap formula sama seperti formula pertama yang kamu buat pada sel D3. Contohnya pada sel D5 berikut ini.

Pada sel D5 terdapat $D$1 yang sama dengan sel-sel lainnya. Karena $D$1 tidak pernah berubah, maka sel D1 dianggap sebagai referensi sel absolut atau referensi sel mutlak. Kunci dari membuat sel absolut adalah dengan memberikan tanda $ sebelum karakter pada sel.

Menggunakan Referensi Sel pada Beberapa Lembar Kerja

Salah satu keunggulan dari referensi sel absolut adalah bisa dicopy dan dipastekan untuk lembar kerja yang lain. Kamu bisa merujuk pada setiap sel lembar kerja untuk mereferensikan nilai tertentu dari satu lembar kerja ke lembar kerja yang lainnya sehingga tidak repot membuat formula yang baru. Untuk melakukannya, kamu hanya perlu memulai referensi sel dengan nama lembar kerja, diikuti oleh tanda seru (!). Misalnya, jika kamu ingin mereferensi sel D9 pada Sheet1, maka referensi sel tersebut akan menjadi Sheet1!D9.

Perhatikan jika nama worksheet memiliki spasi, kamu harus menyertakan tanda kutip tunggal (‘ ‘) di antara nama. Misalnya, kalau kamu ingin mereferensi sel D9 pada lembar kerja bernama Ukuran Baju, maka referensi sel tersebut akan menjadi ‘Ukuran Baju’’!D9.

Sebagai contoh, masih menggunakan data di atas, kamu bisa ikuti langkah-langkah berikut ini.

  • Hitung total harga pada sel D9 dengan membuat formula =SUM(D3:D8).

  • Tekan Enter, maka akan tampil hasilnya.

  • Ganti nama worksheet dengan mengklik kanan sheet 1 dan plih Rename.

  • Ketikkan ““Ukuran Baju” tanpa tanda petik untuk nama sheet yang akan direferensikan.

  • Beralih pada Sheet2 atau lembar kerja yang ingin direferensikan.

  • Arahkan kursor pada sel yang ingin direferensikan untuk membuat formula. Misalnya pada sel C4 seperti gambar.

  • Buat formula dengan mengetik tanda sama dengan (=), nama sheet, diikuti oleh tanda seru (!), dan alamat sel. Dalam contoh ini, kamu bisa mengetik formula = ‘Ukuran Baju’!D9.

  • Tekan enter untuk menampilkan hasilnya.

Kalau kamu suatu ketika ingin mengubah nama lembar kerja, maka referensi sel akan diperbarui secara otomatis yang mencerminkan nama lembar kerja yang baru kamu buat. Selain itu, jika kamu mengubah data pada referensi sel, maka nilai pada sel yang direferensikan juga akan berubah secara otomatis.

Kendala dalam Membuat Formula

Jika kamu memasukkan nama lembar kerja yang salah, maka akan muncul kode kesalahan #REF! dalam sel. Misalnya, saat mereferensikan sel ke sheet lain kamu salah mengetik nama lembar kerja. Untuk mengatasinya, kamu bisa klik tombol Error di samping sel dan pilih opsi dari menu yang tersedia.

Itulah cara membuat formula dari referensi sel relatif dan referensi sel absolut. Dengan mengetahui perbedaan dan fungsi dari masing-masing referensi sel, maka kamu akan semakin mudah menentukan sifat data yang akan kamu olah. Selain itu, kamu tidak akan terlalu kesulitan untuk mengelompokkan data karena bisa mereferensikan sel ke lembar kerja (sheet) yang lainnya.

Setiap data yang akan diolah tentu berbeda dalam membuat formulanya. Maka dari itu, kamu harus bisa memastikan untuk menggunakan sel relatif atau sel absolut. Melalui contoh di atas, selanjutnya kamu bisa membuat sendiri dan melakukan beberapa latihan membuat formula referensi sel relatif dan referensi sel absolut.

Leave a Reply