Pautan dalam Excel – mutlak, relatif dan bercampur. Ralat semasa bekerja dengan pautan relatif dalam Excel

Untuk memudahkan pengiraan menggunakan formula dalam mod automatik, rujukan kepada sel digunakan. Bergantung kepada jenis penulisan, mereka dibahagikan kepada tiga jenis utama:

1. Pautan relatif. Digunakan untuk pengiraan mudah. Menyalin formula memerlukan perubahan koordinat.
2. Pautan mutlak. Jika anda perlu menghasilkan pengiraan yang lebih kompleks, pilihan ini sesuai. Untuk membetulkan gunakan simbol "$". Contoh: $A$1.
3. pautan bercampur. Jenis pengalamatan ini digunakan dalam pengiraan apabila perlu untuk menetapkan lajur atau baris secara berasingan. Contoh: $A1 atau A$1.

Sekiranya perlu untuk menyalin data formula yang dimasukkan, rujukan dengan pengalamatan mutlak dan campuran digunakan. Artikel itu akan mendedahkan dengan contoh cara pengiraan dibuat menggunakan pelbagai jenis pautan.

Rujukan sel relatif dalam Excel

Ini ialah set aksara yang menentukan lokasi sel. Pautan dalam program ditulis secara automatik dengan pengalamatan relatif. Contohnya: A1, A2, B1, B2. Beralih ke baris atau lajur yang berbeza menukar aksara dalam formula. Sebagai contoh, kedudukan permulaan A1. Bergerak secara mendatar menukar huruf kepada B1, C1, D1, dsb. Dengan cara yang sama, perubahan berlaku apabila bergerak di sepanjang garis menegak, hanya dalam kes ini nombor berubah - A2, A3, A4, dll. Jika perlu untuk menduplikasi pengiraan jenis yang sama dalam sel bersebelahan, pengiraan dilakukan menggunakan rujukan relatif. Untuk menggunakan ciri ini, ikuti beberapa langkah:

1. Sebaik sahaja data dimasukkan ke dalam sel, gerakkan kursor dan klik dengan tetikus. Menyerlahkan dengan segi empat tepat hijau menunjukkan pengaktifan sel dan kesediaan untuk kerja selanjutnya.
2. Dengan menekan kombinasi kekunci Ctrl + C, kami menyalin kandungan ke papan keratan.
3. Kami mengaktifkan sel di mana anda ingin memindahkan data atau formula yang ditulis sebelum ini.
4. Dengan menekan kombinasi Ctrl + V kami memindahkan data yang disimpan dalam papan keratan sistem.

Nasihat pakar! Untuk menjalankan jenis pengiraan yang sama dalam jadual, gunakan penggodaman hayat. Pilih sel yang mengandungi formula yang dimasukkan sebelum ini. Menuding kursor di atas petak kecil yang muncul di sudut kanan bawah dan menahan butang tetikus kiri, seret ke baris bawah atau lajur melampau, bergantung pada tindakan yang dilakukan. Dengan melepaskan butang tetikus, pengiraan akan dilakukan secara automatik. Alat ini dipanggil penanda isian automatik.

Contoh pautan relatif

Untuk menjadikannya lebih jelas, pertimbangkan contoh pengiraan menggunakan formula dengan rujukan relatif. Katakan pemilik kedai sukan selepas setahun bekerja perlu mengira keuntungan daripada jualan.

Urutan tindakan:

1. Contoh menunjukkan lajur B dan C digunakan untuk mengisi kuantiti barang yang dijual dan harganya. Sehubungan itu, untuk menulis formula dan mendapatkan jawapan, pilih lajur D. Formula kelihatan seperti ini: = B2 *C

Beri perhatian! Untuk memudahkan proses menulis formula, gunakan sedikit muslihat. Letakkan tanda “=”, klik pada kuantiti barang yang dijual, tetapkan tanda “*” dan klik pada harga produk. Formula selepas tanda sama akan ditulis secara automatik.

2. Untuk jawapan akhir, tekan “Enter”. Seterusnya, anda perlu mengira jumlah keuntungan yang diterima daripada jenis produk lain. Nah, jika bilangan baris tidak besar, maka semua manipulasi boleh dilakukan secara manual. Untuk mengisi sejumlah besar baris pada masa yang sama dalam Excel, terdapat satu fungsi berguna yang memungkinkan untuk memindahkan formula ke sel lain.
3. Gerakkan kursor ke atas sudut kanan bawah segi empat tepat dengan formula atau hasil selesai. Kemunculan salib hitam berfungsi sebagai isyarat bahawa kursor boleh diseret ke bawah. Oleh itu, pengiraan automatik keuntungan yang diterima untuk setiap produk secara berasingan dibuat.
4. Dengan melepaskan butang tetikus yang ditekan, kami mendapat hasil yang betul dalam semua baris.

Dengan mengklik pada sel D3, anda boleh melihat bahawa koordinat sel telah ditukar secara automatik, dan kini kelihatan seperti ini: =B3 *C3. Ia berikutan bahawa pautan adalah relatif.

Kemungkinan ralat apabila bekerja dengan pautan relatif

Tidak dinafikan, fungsi Excel ini sangat memudahkan pengiraan, tetapi dalam beberapa kes kesukaran mungkin timbul. Mari kita pertimbangkan contoh mudah pengiraan pekali keuntungan bagi setiap item barang:

1. Buat jadual dan isikan: A – nama produk; B – kuantiti dijual; C – kos; D ialah jumlah yang diterima. Katakan terdapat hanya 11 item dalam pelbagai. Oleh itu, dengan mengambil kira perihalan lajur, 12 baris diisi dan jumlah keuntungan ialah D
2. Klik pada sel E2 dan masukkan =D2/D13.
3. Selepas menekan butang "Enter", pekali bahagian relatif jualan item pertama muncul.
4. Regangkan lajur ke bawah dan tunggu hasilnya. Walau bagaimanapun, sistem memberikan ralat "#DIV/0!"

Sebab ralat adalah penggunaan rujukan relatif untuk pengiraan. Hasil daripada menyalin formula, koordinat berubah. Iaitu, untuk E3, formula akan kelihatan seperti ini =D3/D13. Oleh kerana sel D13 tidak diisi dan secara teorinya mempunyai nilai sifar, program akan memberikan ralat dengan maklumat bahawa pembahagian dengan sifar adalah mustahil.

Penting! Untuk membetulkan ralat, perlu menulis formula sedemikian rupa sehingga koordinat D13 ditetapkan. Pengalamatan relatif tidak mempunyai fungsi sedemikian. Untuk melakukan ini, terdapat satu lagi jenis pautan - mutlak. 

Bagaimana anda membuat pautan mutlak dalam Excel

Terima kasih kepada penggunaan simbol $, ia menjadi mungkin untuk menetapkan koordinat sel. Bagaimana ini berfungsi, kami akan mempertimbangkan lebih lanjut. Memandangkan program menggunakan pengalamatan relatif secara lalai, oleh itu, untuk menjadikannya mutlak, anda perlu melakukan beberapa tindakan. Mari kita analisa penyelesaian kepada ralat "Bagaimana untuk mencari pekali daripada penjualan beberapa item barangan", melakukan pengiraan menggunakan pengalamatan mutlak:

1. Klik pada E2 dan masukkan koordinat pautan =D2/D13. Memandangkan pautan adalah relatif, simbol mesti ditetapkan untuk membetulkan data.
2. Betulkan koordinat sel D Untuk melakukan tindakan ini, awali huruf yang menunjukkan lajur dan nombor baris dengan meletakkan tanda “$”.

Nasihat pakar! Untuk memudahkan tugas memasuki, cukup untuk mengaktifkan sel untuk mengedit formula dan tekan kekunci F4 beberapa kali. Sehingga anda mendapat nilai yang memuaskan. Formula yang betul adalah seperti berikut: =D2/$D$13.

3. Tekan butang “Enter”. Hasil daripada tindakan yang dilakukan, hasil yang betul akan muncul.
4. Seret penanda ke baris bawah untuk menyalin formula.

Oleh kerana penggunaan pengalamatan mutlak dalam pengiraan, keputusan akhir dalam baris yang tinggal adalah betul.

Bagaimana untuk meletakkan pautan bercampur dalam Excel

Untuk pengiraan formula, bukan sahaja rujukan relatif dan mutlak digunakan, tetapi juga rujukan bercampur. Ciri tersendiri mereka ialah mereka menetapkan salah satu koordinat.

• Sebagai contoh, untuk menukar kedudukan baris, anda mesti menulis tanda $ di hadapan penetapan huruf.
• Sebaliknya, jika tanda dolar ditulis selepas penetapan huruf, maka penunjuk dalam baris akan kekal tidak berubah.

Dari sini ia mengikuti bahawa untuk menyelesaikan masalah sebelumnya dengan menentukan pekali jualan barangan menggunakan pengalamatan bercampur, adalah perlu untuk menetapkan nombor talian. Iaitu, tanda $ diletakkan selepas huruf lajur, kerana koordinatnya tidak berubah walaupun dalam rujukan relatif. Mari kita ambil contoh:

1. Untuk pengiraan yang tepat, masukkan =D1/$D$3 dan tekan “Enter”. Program ini memberikan jawapan yang tepat.
2. Untuk mengalihkan formula ke sel berikutnya ke bawah lajur dan mendapatkan hasil yang tepat, seret pemegang ke sel bawah.
3. Hasilnya, program akan memberikan pengiraan yang betul.

Perhatian! Jika anda menetapkan tanda $ di hadapan huruf, maka Excel akan memberikan ralat "#DIV/0!", yang bermaksud bahawa operasi ini tidak boleh dilakukan.

Pengalamatan "SuperAbsolute".

Pada akhirnya, mari kita lihat satu lagi contoh pautan mutlak – pengalamatan “SuperAbsolute”. Apakah ciri dan perbezaannya. Ambil anggaran nombor 30 dan masukkannya dalam sel B2. Ia adalah nombor yang akan menjadi yang utama, adalah perlu untuk melakukan satu siri tindakan dengannya, sebagai contoh, untuk meningkatkannya kepada kuasa.

1. Untuk pelaksanaan yang betul bagi semua tindakan, masukkan formula berikut dalam lajur C: =$B$2^$D2. Dalam lajur D kita masukkan nilai darjah.

2. Selepas menekan butang "Enter" dan mengaktifkan formula, kami meregangkan penanda ke bawah lajur.
3. Kami mendapat keputusan yang betul.

Intinya ialah semua tindakan yang dilakukan merujuk kepada satu sel B2 tetap, oleh itu:

• Menyalin formula dari sel C3 ke sel E3, F3 atau H3 tidak akan mengubah hasilnya. Ia akan kekal tidak berubah – 900.
• Jika anda perlu memasukkan lajur baharu, koordinat sel dengan formula akan berubah, tetapi hasilnya juga akan kekal tidak berubah.

Ini adalah keistimewaan pautan "SuperAbsolute": jika anda perlu memindahkan hasilnya tidak akan berubah. Walau bagaimanapun, terdapat situasi apabila data dimasukkan daripada sumber pihak ketiga. Oleh itu, lajur dialihkan ke sisi, dan data ditetapkan dengan cara lama dalam lajur B2. Apa yang berlaku dalam kes ini? Apabila dicampur, formula berubah mengikut tindakan yang dilakukan, iaitu, ia tidak lagi akan menghala ke B2, tetapi ke C2. Tetapi kerana sisipan dibuat dalam B2, keputusan akhir akan menjadi salah.

Rujukan! Untuk dapat memasukkan makro daripada sumber pihak ketiga, anda perlu mendayakan tetapan pembangun (ia dilumpuhkan secara lalai). Untuk melakukan ini, pergi ke Pilihan, buka tetapan reben dan tandai kotak di lajur kanan bertentangan dengan "Pembangun". Selepas itu, akses kepada banyak fungsi yang sebelum ini tersembunyi dari mata rata-rata pengguna akan terbuka.

Ini menimbulkan persoalan: adakah mungkin untuk mengubah suai formula daripada sel C2 supaya nombor asal dikumpul daripada sel B, walaupun terdapat sisipan lajur data baharu? Untuk memastikan bahawa perubahan dalam jadual tidak menjejaskan penentuan jumlah apabila memasang data daripada sumber pihak ketiga, anda mesti melakukan langkah berikut:

1. Daripada koordinat sel B2, masukkan penunjuk berikut: =TIDAK LANGSUNG(“B2”). Akibatnya, selepas memindahkan komposisi perumus akan kelihatan seperti ini: =TIDAK LANGSUNG(“B2”)^$E2.
2. Terima kasih kepada fungsi ini, pautan sentiasa menghala ke segi empat sama dengan koordinat B2, tidak kira sama ada lajur ditambah atau dialih keluar dalam jadual.

Perlu difahami bahawa sel yang tidak mengandungi sebarang data sentiasa menunjukkan nilai "0".

Kesimpulan

Terima kasih kepada penggunaan tiga jenis pautan yang diterangkan, banyak peluang muncul yang memudahkan untuk bekerja dengan pengiraan dalam Excel. Oleh itu, sebelum anda mula bekerja dengan formula, baca dahulu pautan dan peraturan untuk pemasangannya.