Bagaimana untuk membekukan sel dalam formula Excel

Selalunya, pengguna berhadapan dengan keperluan untuk menyematkan sel dalam formula. Sebagai contoh, ia berlaku dalam situasi di mana anda ingin menyalin formula, tetapi supaya pautan tidak bergerak ke atas dan ke bawah bilangan sel yang sama seperti ia disalin dari lokasi asalnya.

Dalam kes ini, anda boleh membetulkan rujukan sel dalam Excel. Dan ini boleh dilakukan dalam beberapa cara sekaligus. Mari kita lihat lebih dekat bagaimana untuk mencapai matlamat ini.

Apakah pautan Excel

Lembaran terdiri daripada sel. Setiap daripada mereka mengandungi maklumat khusus. Sel lain boleh menggunakannya dalam pengiraan. Tetapi bagaimana mereka memahami dari mana untuk mendapatkan data? Ia membantu mereka membuat pautan.

Setiap pautan menetapkan sel dengan satu huruf dan satu nombor. Huruf mewakili lajur dan nombor mewakili baris. 

Terdapat tiga jenis pautan: mutlak, relatif dan campuran. Yang kedua ditetapkan secara lalai. Rujukan mutlak ialah rujukan yang mempunyai alamat tetap bagi kedua-dua lajur dan lajur. Sehubungan itu, bercampur ialah yang sama ada lajur atau baris yang berasingan ditetapkan.

Kaedah 1

Untuk menyimpan alamat lajur dan baris, ikut langkah berikut:

1. Klik pada sel yang mengandungi formula.
2. Klik pada bar formula untuk sel yang kita perlukan.
3. Tekan F4.

Akibatnya, rujukan sel akan berubah kepada mutlak. Ia boleh dikenali dengan ciri tanda dolar. Sebagai contoh, jika anda mengklik pada sel B2 dan kemudian klik pada F4, pautan akan kelihatan seperti ini: $B$2.

Apakah yang dimaksudkan dengan tanda dolar sebelum bahagian alamat setiap sel?

1. Jika ia diletakkan di hadapan surat, ia menunjukkan bahawa rujukan lajur tetap sama, tidak kira ke mana formula telah dialihkan.
2. Jika tanda dolar berada di hadapan nombor, ia menunjukkan bahawa rentetan itu disematkan. 

Kaedah 2

Kaedah ini hampir sama dengan yang sebelumnya, cuma anda perlu menekan F4 dua kali. sebagai contoh, jika kita mempunyai sel B2, maka selepas itu ia akan menjadi B$2. Dengan kata mudah, dengan cara ini kami berjaya membetulkan talian. Dalam kes ini, huruf lajur akan berubah.

Ia sangat mudah, sebagai contoh, dalam jadual di mana anda perlu memaparkan kandungan sel kedua dari atas di sel bawah. Daripada melakukan formula sedemikian berkali-kali, cukup untuk membetulkan baris dan membiarkan lajur berubah.

Kaedah 3

Ini sama sekali dengan kaedah sebelumnya, cuma anda perlu menekan kekunci F4 tiga kali. Kemudian hanya rujukan kepada lajur akan menjadi mutlak, dan baris akan kekal tetap.

Kaedah 4

Katakan kita mempunyai rujukan mutlak kepada sel, tetapi di sini adalah perlu untuk menjadikannya relatif. Untuk melakukan ini, tekan kekunci F4 berkali-kali sehingga tiada tanda $ dalam pautan. Kemudian ia akan menjadi relatif, dan apabila anda memindahkan atau menyalin formula, kedua-dua alamat lajur dan alamat baris akan berubah.

Menyemat sel untuk julat yang besar

Kami melihat bahawa kaedah di atas tidak memberikan sebarang kesukaran sama sekali untuk dilakukan. Tetapi tugas adalah khusus. Dan, sebagai contoh, apa yang perlu dilakukan jika kita mempunyai beberapa dozen formula sekaligus, pautan yang perlu diubah menjadi yang mutlak. 

Malangnya, kaedah Excel standard tidak akan mencapai matlamat ini. Untuk melakukan ini, anda perlu menggunakan addon khas yang dipanggil VBA-Excel. Ia mengandungi banyak ciri tambahan yang membolehkan anda melaksanakan tugas biasa dengan Excel dengan lebih pantas.

Ia termasuk lebih daripada seratus fungsi yang ditentukan pengguna dan 25 makro yang berbeza, dan ia juga dikemas kini dengan kerap. Ia membolehkan anda menambah baik kerja dengan hampir semua aspek:

1. Sel.
2. Makro
3. Fungsi pelbagai jenis.
4. Pautan dan tatasusunan.

Khususnya, tambahan ini membolehkan anda membetulkan pautan dalam sejumlah besar formula sekaligus. Untuk melakukan ini, anda mesti melakukan langkah berikut:

1. Pilih julat.
2. Buka tab VBA-Excel yang akan muncul selepas pemasangan. 
3. Buka menu "Fungsi", di mana pilihan "Formula kunci" terletak.
   

   

4. Seterusnya, kotak dialog akan muncul di mana anda perlu menentukan parameter yang diperlukan. Tambahan ini membolehkan anda menyemat lajur dan lajur secara berasingan, bersama-sama, dan juga mengalih keluar penyematan sedia ada dengan pakej. Selepas parameter yang diperlukan dipilih menggunakan butang radio yang sepadan, anda perlu mengesahkan tindakan anda dengan mengklik "OK".

Contoh

Mari kita ambil contoh untuk menjadikannya lebih jelas. Katakan kami mempunyai maklumat yang menerangkan kos barangan, jumlah kuantiti dan hasil jualannya. Dan kita berhadapan dengan tugas membuat jadual, berdasarkan kuantiti dan kos, secara automatik menentukan berapa banyak wang yang kita berjaya perolehi tanpa menolak kerugian.

Dalam contoh kami, untuk ini anda perlu memasukkan formula =B2*C2. Ia agak mudah, seperti yang anda lihat. Sangat mudah untuk menggunakan contoh beliau untuk menerangkan cara anda boleh membetulkan alamat sel atau lajur atau baris individunya. 

Sudah tentu, dalam contoh ini, anda boleh cuba menyeret formula ke bawah menggunakan penanda autolengkap, tetapi dalam kes ini, sel akan ditukar secara automatik. Jadi, dalam sel D3 akan ada formula lain, di mana nombor akan diganti, masing-masing, dengan 3. Selanjutnya, mengikut skema - D4 - formula akan mengambil bentuk = B4 * C4, D5 - sama, tetapi dengan nombor 5 dan seterusnya.

Sekiranya perlu (dalam kebanyakan kes ternyata), maka tidak ada masalah. Tetapi jika anda perlu membetulkan formula dalam satu sel supaya ia tidak berubah apabila menyeret, maka ini akan menjadi lebih sukar. 

Katakan kita perlu menentukan pendapatan dolar. Mari letakkan dalam sel B7. Mari kita nostalgia sedikit dan nyatakan kos 35 rubel setiap dolar. Oleh itu, untuk menentukan hasil dalam dolar, adalah perlu untuk membahagikan jumlah dalam rubel dengan kadar pertukaran dolar.

Begini rupanya dalam contoh kami.

Jika kita, sama seperti versi sebelumnya, cuba menetapkan formula, maka kita akan gagal. Begitu juga, formula akan berubah kepada yang sesuai. Dalam contoh kami, ia akan menjadi seperti ini: =E3*B8. Dari sini kita boleh lihat. bahawa bahagian pertama formula telah bertukar menjadi E3, dan kami menetapkan sendiri tugas ini, tetapi kami tidak perlu menukar bahagian kedua formula kepada B8. Oleh itu, kita perlu mengubah rujukan menjadi rujukan mutlak. Anda boleh melakukan ini tanpa menekan kekunci F4, hanya dengan meletakkan tanda dolar.

Selepas kami menukar rujukan kepada sel kedua menjadi satu mutlak, ia menjadi dilindungi daripada perubahan. Kini anda boleh menyeretnya dengan selamat menggunakan pemegang autolengkap. Semua data tetap akan kekal sama, tanpa mengira kedudukan formula, dan data yang tidak terikat akan berubah secara fleksibel. Dalam semua sel, hasil dalam rubel yang diterangkan dalam baris ini akan dibahagikan dengan kadar pertukaran dolar yang sama.

Formula itu sendiri akan kelihatan seperti ini:

=D2/$B$7

Perhatian! Kami telah menunjukkan dua tanda dolar. Dengan cara ini, kami menunjukkan program bahawa kedua-dua lajur dan baris perlu diperbaiki.

Rujukan sel dalam makro

Makro ialah subrutin yang membolehkan anda mengautomasikan tindakan. Tidak seperti fungsi standard Excel, makro membolehkan anda menetapkan sel tertentu dengan segera dan melakukan tindakan tertentu hanya dalam beberapa baris kod. Berguna untuk pemprosesan kumpulan maklumat, contohnya, jika tiada cara untuk memasang alat tambah (contohnya, komputer syarikat digunakan, bukan komputer peribadi).

Mula-mula anda perlu memahami bahawa konsep utama makro ialah objek yang boleh mengandungi objek lain. Objek Buku Kerja bertanggungjawab untuk buku elektronik (iaitu, dokumen). Ia termasuk objek Helaian, yang merupakan koleksi semua helaian dokumen terbuka. 

Sehubungan itu, sel adalah objek Sel. Ia mengandungi semua sel helaian tertentu.

Setiap objek layak dengan hujah dalam kurungan. Dalam kes sel, ia dirujuk dalam susunan ini. Nombor baris disenaraikan dahulu, diikuti dengan nombor lajur atau huruf (kedua-dua format boleh diterima).

Sebagai contoh, baris kod yang mengandungi rujukan kepada sel C5 akan kelihatan seperti ini:

Buku Kerja (“Book2.xlsm”). Helaian (“Senarai2”). Sel(5, 3)

Buku Kerja (“Book2.xlsm”). Helaian (“Senarai2”). Sel(5, “C”)

Anda juga boleh mengakses sel menggunakan objek Kemas. Secara umum, ia bertujuan untuk memberi rujukan kepada julat (yang unsur-unsurnya, dengan cara itu, juga boleh menjadi mutlak atau relatif), tetapi anda hanya boleh memberikan nama sel, dalam format yang sama seperti dalam dokumen Excel.

Dalam kes ini, garisan akan kelihatan seperti ini.

Buku Kerja (“Book2.xlsm”). Helaian (“Senarai2”). Julat (“C5”)

Nampaknya pilihan ini lebih mudah, tetapi kelebihan dua pilihan pertama ialah anda boleh menggunakan pembolehubah dalam kurungan dan memberikan pautan yang tidak lagi mutlak, tetapi sesuatu seperti yang relatif, yang akan bergantung pada keputusan pengiraan.

Oleh itu, makro boleh digunakan dengan berkesan dalam program. Malah, semua rujukan kepada sel atau julat di sini adalah mutlak, dan oleh itu juga boleh dibetulkan dengannya. Benar, ia tidak begitu mudah. Penggunaan makro boleh berguna apabila menulis atur cara yang kompleks dengan sejumlah besar langkah dalam algoritma. Secara umum, cara standard menggunakan rujukan mutlak atau relatif adalah lebih mudah. 

kesimpulan

Kami mengetahui apakah rujukan sel, cara ia berfungsi, untuk apa ia. Kami memahami perbezaan antara rujukan mutlak dan relatif dan mengetahui perkara yang perlu dilakukan untuk menukar satu jenis kepada yang lain (dengan kata mudah, betulkan alamat atau nyahpinnya). Kami mengetahui cara anda boleh melakukannya dengan segera dengan sejumlah besar nilai. Kini anda mempunyai fleksibiliti untuk menggunakan ciri ini dalam situasi yang betul.