Tautan di Excel – mutlak, relatif, dan campuran. Kesalahan saat bekerja dengan tautan relatif di Excel

Untuk memfasilitasi perhitungan menggunakan rumus dalam mode otomatis, referensi ke sel digunakan. Tergantung pada jenis tulisan, mereka dibagi menjadi tiga jenis utama:

1. Tautan relatif. Digunakan untuk perhitungan sederhana. Menyalin rumus berarti mengubah koordinat.
2. Tautan mutlak. Jika Anda perlu menghasilkan perhitungan yang lebih kompleks, opsi ini cocok. Untuk memperbaiki gunakan simbol "$". Contoh: $A$1.
3. link campuran. Jenis pengalamatan ini digunakan dalam perhitungan ketika diperlukan untuk memperbaiki kolom atau baris secara terpisah. Contoh: $A1 atau A$1.

Jika perlu menyalin data dari rumus yang dimasukkan, referensi dengan pengalamatan absolut dan campuran digunakan. Artikel ini akan mengungkapkan dengan contoh bagaimana perhitungan dibuat menggunakan berbagai jenis tautan.

Referensi sel relatif di Excel

Ini adalah kumpulan karakter yang menentukan lokasi sel. Tautan dalam program secara otomatis ditulis dengan pengalamatan relatif. Contoh: A1, A2, B1, B2. Pindah ke baris atau kolom yang berbeda akan mengubah karakter dalam rumus. Misalnya, posisi awal A1. Bergerak secara horizontal mengubah huruf menjadi B1, C1, D1, dll. Dengan cara yang sama, perubahan terjadi ketika bergerak di sepanjang garis vertikal, hanya dalam hal ini angkanya berubah – A2, A3, A4, dll. Jika perlu untuk menduplikasi perhitungan dari jenis yang sama dalam sel yang berdekatan, perhitungan dilakukan dengan menggunakan referensi relatif. Untuk menggunakan fitur ini, ikuti beberapa langkah:

1. Segera setelah data dimasukkan ke dalam sel, gerakkan kursor dan klik dengan mouse. Menyorot dengan persegi panjang hijau menunjukkan aktivasi sel dan kesiapan untuk pekerjaan lebih lanjut.
2. Dengan menekan kombinasi tombol Ctrl + C, kami menyalin konten ke clipboard.
3. Kami mengaktifkan sel tempat Anda ingin mentransfer data atau formula yang ditulis sebelumnya.
4. Dengan menekan kombinasi Ctrl + V kami mentransfer data yang disimpan di clipboard sistem.

Saran ahli! Untuk melakukan jenis perhitungan yang sama dalam tabel, gunakan life hack. Pilih sel yang berisi rumus yang dimasukkan sebelumnya. Arahkan kursor ke kotak kecil yang muncul di sudut kanan bawah, dan tahan tombol kiri mouse, seret ke baris bawah atau kolom ekstrem, tergantung pada tindakan yang dilakukan. Dengan melepaskan tombol mouse, perhitungan akan dilakukan secara otomatis. Alat ini disebut penanda pengisian otomatis.

Contoh tautan relatif

Agar lebih jelas, perhatikan contoh perhitungan menggunakan rumus dengan referensi relatif. Misalkan pemilik toko olahraga setelah satu tahun bekerja perlu menghitung keuntungan dari penjualan.

Urutan tindakan:

1. Contoh menunjukkan bahwa kolom B dan C digunakan untuk mengisi jumlah barang yang dijual dan harganya. Dengan demikian, untuk menulis rumus dan mendapatkan jawabannya, pilih kolom D. Rumusnya terlihat seperti ini: = B2*C

Perhatikan! Untuk memudahkan proses penulisan rumus, gunakan sedikit trik. Beri tanda “=”, klik jumlah barang yang dijual, atur tanda “*” dan klik harga produk. Rumus setelah tanda sama dengan akan ditulis secara otomatis.

2. Untuk jawaban akhir, tekan "Enter". Selanjutnya, Anda perlu menghitung jumlah total keuntungan yang diterima dari jenis produk lain. Nah, jika jumlah barisnya tidak banyak, maka semua manipulasi bisa dilakukan secara manual. Untuk mengisi banyak baris sekaligus di Excel, ada satu fungsi berguna yang memungkinkan untuk mentransfer rumus ke sel lain.
3. Pindahkan kursor ke sudut kanan bawah persegi panjang dengan rumus atau hasil akhir. Munculnya salib hitam berfungsi sebagai sinyal bahwa kursor dapat diseret ke bawah. Dengan demikian, perhitungan otomatis keuntungan yang diterima untuk setiap produk secara terpisah dibuat.
4. Dengan melepaskan tombol mouse yang ditekan, kami mendapatkan hasil yang benar di semua lini.

Dengan mengklik sel D3, Anda dapat melihat bahwa koordinat sel telah diubah secara otomatis, dan sekarang terlihat seperti ini: =B3*C3. Oleh karena itu, tautannya relatif.

Kemungkinan kesalahan saat bekerja dengan tautan relatif

Tidak diragukan lagi, fungsi Excel ini sangat menyederhanakan perhitungan, tetapi dalam beberapa kasus mungkin timbul kesulitan. Mari kita pertimbangkan contoh sederhana untuk menghitung koefisien keuntungan dari setiap item barang:

1. Buat tabel dan isi: A – nama produk; B – jumlah yang terjual; C – biaya; D adalah jumlah yang diterima. Misalkan hanya ada 11 item dalam bermacam-macam. Oleh karena itu, dengan mempertimbangkan deskripsi kolom, diisi 12 baris dan jumlah total keuntungan adalah D
2. Klik pada sel E2 dan masukkan =D2 /D13.
3. Setelah menekan tombol "Enter", koefisien bagian relatif dari penjualan barang pertama muncul.
4. Regangkan kolom ke bawah dan tunggu hasilnya. Namun, sistem memberikan kesalahan "#DIV/0!"

Alasan kesalahan adalah penggunaan referensi relatif untuk perhitungan. Sebagai hasil dari menyalin rumus, koordinat berubah. Artinya, untuk E3, rumusnya akan terlihat seperti ini =D3 /D13. Karena sel D13 tidak terisi dan secara teoritis memiliki nilai nol, program akan memberikan kesalahan dengan informasi bahwa pembagian dengan nol tidak mungkin dilakukan.

Penting! Untuk memperbaiki kesalahan, perlu untuk menulis rumus sedemikian rupa sehingga koordinat D13 diperbaiki. Pengalamatan relatif tidak memiliki fungsi seperti itu. Untuk melakukan ini, ada jenis tautan lain – mutlak. 

Bagaimana Anda membuat tautan absolut di Excel

Berkat penggunaan simbol $, menjadi mungkin untuk memperbaiki koordinat sel. Bagaimana ini bekerja, kami akan mempertimbangkan lebih lanjut. Karena program menggunakan pengalamatan relatif secara default, oleh karena itu, untuk menjadikannya absolut, Anda perlu melakukan sejumlah tindakan. Mari kita menganalisis solusi untuk kesalahan "Cara menemukan koefisien dari penjualan beberapa item barang", melakukan perhitungan menggunakan pengalamatan absolut:

1. Klik E2 dan masukkan koordinat tautan =D2 /D13. Karena tautannya relatif, simbol harus ditetapkan untuk memperbaiki data.
2. Perbaiki koordinat sel D Untuk melakukan tindakan ini, awali huruf yang menunjukkan kolom dan nomor baris dengan menempatkan tanda “$”.

Saran ahli! Untuk memudahkan tugas masuk, cukup aktifkan sel untuk mengedit rumus dan tekan tombol F4 beberapa kali. Sampai Anda mendapatkan nilai yang memuaskan. Rumus yang benar adalah sebagai berikut: =H2/$D$13.

3. Tekan tombol "Enter". Sebagai hasil dari tindakan yang dilakukan, hasil yang benar akan muncul.
4. Seret penanda ke garis bawah untuk menyalin rumus.

Karena penggunaan pengalamatan absolut dalam perhitungan, hasil akhir pada baris yang tersisa akan benar.

Cara menempatkan tautan campuran di Excel

Untuk perhitungan rumus, tidak hanya referensi relatif dan absolut yang digunakan, tetapi juga referensi campuran. Ciri khas mereka adalah mereka memperbaiki salah satu koordinat.

• Misalnya, untuk mengubah posisi garis, Anda harus menulis tanda $ di depan penunjukan huruf.
• Sebaliknya, jika tanda dolar ditulis setelah penunjukan huruf, maka indikator di garis akan tetap tidak berubah.

Dari sini dapat disimpulkan bahwa untuk menyelesaikan masalah sebelumnya dengan menentukan koefisien penjualan barang menggunakan pengalamatan campuran, perlu untuk memperbaiki nomor baris. Artinya, tanda $ ditempatkan setelah huruf kolom, karena koordinatnya tidak berubah bahkan dalam referensi relatif. Mari kita ambil contoh:

1. Untuk perhitungan yang akurat, masukkan =H1/$D$3 dan tekan "Enter". Program ini memberikan jawaban yang tepat.
2. Untuk memindahkan rumus ke sel berikutnya ke bawah kolom dan mendapatkan hasil yang akurat, seret pegangan ke sel bawah.
3. Akibatnya, program akan memberikan perhitungan yang benar.

Perhatian! Jika Anda mengatur tanda $ di depan huruf, maka Excel akan memberikan kesalahan "#DIV/0!", yang berarti bahwa operasi ini tidak dapat dilakukan.

Pengalamatan “SuperAbsolute”

Pada akhirnya, mari kita lihat contoh lain dari tautan absolut – pengalamatan “SuperAbsolute”. Apa saja fitur dan perbedaannya. Ambil perkiraan angka 30 dan masukkan di sel B2. Nomor inilah yang akan menjadi yang utama, perlu untuk melakukan serangkaian tindakan dengannya, misalnya, untuk meningkatkannya menjadi kekuatan.

1. Untuk eksekusi yang benar dari semua tindakan, masukkan rumus berikut di kolom C: =$B$2^$D2. Di kolom D kita masukkan nilai derajat.

2. Setelah menekan tombol "Enter" dan mengaktifkan rumus, kami merentangkan penanda ke bawah kolom.
3. Kami mendapatkan hasil yang benar.

Intinya adalah bahwa semua tindakan yang dilakukan mengacu pada satu sel tetap B2, oleh karena itu:

• Menyalin rumus dari sel C3 ke sel E3, F3, atau H3 tidak akan mengubah hasilnya. Itu akan tetap tidak berubah - 900.
• Jika Anda perlu memasukkan kolom baru, koordinat sel dengan rumus akan berubah, tetapi hasilnya juga tidak akan berubah.

Ini adalah kekhasan tautan "SuperAbsolute": jika Anda perlu memindahkan hasilnya tidak akan berubah. Namun, ada situasi ketika data dimasukkan dari sumber pihak ketiga. Dengan demikian, kolom digeser ke samping, dan data diatur dengan cara lama di kolom B2. Apa yang terjadi dalam kasus ini? Ketika dicampur, rumus berubah sesuai dengan tindakan yang dilakukan, yaitu tidak lagi mengarah ke B2, tetapi ke C2. Tapi karena penyisipan dilakukan di B2, hasil akhirnya akan salah.

Referensi! Untuk dapat menyisipkan makro dari sumber pihak ketiga, Anda harus mengaktifkan pengaturan pengembang (mereka dinonaktifkan secara default). Untuk melakukan ini, buka Opsi, buka pengaturan pita dan centang kotak di kolom kanan di seberang "Pengembang". Setelah itu, akses ke banyak fungsi yang sebelumnya tersembunyi dari mata rata-rata pengguna akan terbuka.

Ini menimbulkan pertanyaan: apakah mungkin untuk mengubah rumus dari sel C2 sehingga nomor asli dikumpulkan dari sel B, meskipun ada penyisipan kolom data baru? Untuk memastikan bahwa perubahan dalam tabel tidak mempengaruhi penentuan total saat menginstal data dari sumber pihak ketiga, Anda harus melakukan langkah-langkah berikut:

1. Alih-alih koordinat sel B2, masukkan indikator berikut: =TIDAK LANGSUNG("B2"). Hasilnya, setelah dipindahkan, komposisi formulasi akan terlihat seperti ini: =LANGSUNG(“B2”)^$E2.
2. Berkat fungsi ini, tautan selalu menunjuk ke kotak dengan koordinat B2, terlepas dari apakah kolom ditambahkan atau dihapus dalam tabel.

Harus dipahami bahwa sel yang tidak berisi data apa pun selalu menunjukkan nilai “0”.

Kesimpulan

Berkat penggunaan tiga jenis tautan yang dijelaskan, banyak peluang muncul yang membuatnya lebih mudah untuk bekerja dengan perhitungan di Excel. Oleh karena itu, sebelum Anda mulai bekerja dengan rumus, baca dulu tautan dan aturan pemasangannya.