Cara membekukan sel dalam rumus Excel

Seringkali, pengguna dihadapkan pada kebutuhan untuk menyematkan sel dalam formula. Misalnya, ini terjadi dalam situasi di mana Anda ingin menyalin rumus, tetapi agar tautan tidak bergerak ke atas dan ke bawah dengan jumlah sel yang sama seperti yang disalin dari lokasi aslinya.

Dalam hal ini, Anda dapat memperbaiki referensi sel di Excel. Dan ini bisa dilakukan dengan beberapa cara sekaligus. Mari kita lihat lebih dekat bagaimana mencapai tujuan ini.

Apa itu tautan Excel

Lembaran itu terdiri dari sel-sel. Masing-masing berisi informasi spesifik. Sel lain dapat menggunakannya dalam perhitungan. Tetapi bagaimana mereka memahami dari mana mendapatkan data? Ini membantu mereka membuat tautan.

Setiap tautan menunjuk sel dengan satu huruf dan satu angka. Huruf mewakili kolom dan angka mewakili baris. 

Ada tiga jenis tautan: absolut, relatif, dan campuran. Yang kedua diatur secara default. Referensi absolut adalah referensi yang memiliki alamat tetap kolom dan kolom. Dengan demikian, campuran adalah yang di mana kolom atau baris terpisah diperbaiki.

Metode 1

Untuk menyimpan alamat kolom dan baris, ikuti langkah-langkah berikut:

1. Klik pada sel yang berisi rumus.
2. Klik pada bilah rumus untuk sel yang kita butuhkan.
3. Tekan F4.

Akibatnya, referensi sel akan berubah menjadi absolut. Itu dapat dikenali dari tanda dolar yang khas. Misalnya, jika Anda mengklik sel B2 dan kemudian mengklik F4, tautannya akan terlihat seperti ini: $B$2.

Apa arti tanda dolar sebelum bagian alamat per sel?

1. Jika diletakkan di depan huruf, itu menunjukkan bahwa referensi kolom tetap sama, ke mana pun rumusnya dipindahkan.
2. Jika tanda dolar berada di depan angka, itu menunjukkan bahwa string disematkan. 

Metode 2

Cara ini hampir sama dengan cara sebelumnya, hanya perlu menekan F4 dua kali. misalnya, jika kita memiliki sel B2, maka setelah itu akan menjadi B$2. Dengan kata sederhana, dengan cara ini kami berhasil memperbaiki saluran. Dalam hal ini, huruf kolom akan berubah.

Sangat nyaman, misalnya, dalam tabel di mana Anda perlu menampilkan konten sel kedua dari atas ke sel bawah. Alih-alih melakukan rumus seperti itu berkali-kali, cukup dengan memperbaiki baris dan membiarkan kolom berubah.

Metode 3

Ini benar-benar sama dengan cara sebelumnya, hanya Anda perlu menekan tombol F4 tiga kali. Maka hanya referensi ke kolom yang akan menjadi absolut, dan baris akan tetap.

Metode 4

Misalkan kita memiliki referensi absolut ke sel, tetapi di sini perlu untuk membuatnya relatif. Untuk melakukan ini, tekan tombol F4 berkali-kali sehingga tidak ada tanda $ di tautan. Maka itu akan menjadi relatif, dan ketika Anda memindahkan atau menyalin rumus, alamat kolom dan alamat baris akan berubah.

Menyematkan sel untuk rentang besar

Kami melihat bahwa metode di atas tidak menimbulkan kesulitan sama sekali untuk dilakukan. Tapi tugasnya spesifik. Dan, misalnya, apa yang harus dilakukan jika kita memiliki beberapa lusin formula sekaligus, tautan yang perlu diubah menjadi yang absolut. 

Sayangnya, metode Excel standar tidak akan mencapai tujuan ini. Untuk melakukan ini, Anda perlu menggunakan addon khusus yang disebut VBA-Excel. Ini berisi banyak fitur tambahan yang memungkinkan Anda melakukan tugas umum dengan Excel lebih cepat.

Ini mencakup lebih dari seratus fungsi yang ditentukan pengguna dan 25 makro yang berbeda, dan juga diperbarui secara berkala. Ini memungkinkan Anda untuk meningkatkan pekerjaan dengan hampir semua aspek:

1. Sel.
2. Makro
3. Fungsi dari berbagai jenis.
4. Link dan array.

Secara khusus, add-in ini memungkinkan Anda untuk memperbaiki tautan dalam sejumlah besar rumus sekaligus. Untuk melakukan ini, Anda harus melakukan langkah-langkah berikut:

1. Pilih rentang.
2. Buka tab VBA-Excel yang akan muncul setelah instalasi. 
3. Buka menu "Fungsi", di mana opsi "Kunci rumus" berada.
   

   

4. Selanjutnya, kotak dialog akan muncul di mana Anda perlu menentukan parameter yang diperlukan. Addon ini memungkinkan Anda untuk menyematkan kolom dan kolom secara terpisah, bersama-sama, dan juga menghapus penyematan yang sudah ada dengan sebuah paket. Setelah parameter yang diperlukan dipilih menggunakan tombol radio yang sesuai, Anda perlu mengonfirmasi tindakan Anda dengan mengklik "OK".

Contoh

Mari kita ambil contoh untuk membuatnya lebih jelas. Katakanlah kita memiliki informasi yang menjelaskan harga pokok barang, jumlah totalnya, dan pendapatan penjualannya. Dan kita dihadapkan pada tugas membuat tabel, berdasarkan kuantitas dan biaya, secara otomatis menentukan berapa banyak uang yang berhasil kita peroleh tanpa mengurangi kerugian.

Dalam contoh kami, untuk ini Anda harus memasukkan rumus =B2*C2. Ini cukup sederhana, seperti yang Anda lihat. Sangat mudah untuk menggunakan contohnya untuk menjelaskan bagaimana Anda dapat memperbaiki alamat sel atau kolom atau baris individualnya. 

Tentu saja, dalam contoh ini, Anda dapat mencoba menyeret rumus ke bawah menggunakan penanda IsiOtomatis, tetapi dalam kasus ini, sel akan diubah secara otomatis. Jadi, di sel D3 akan ada rumus lain, di mana angka akan diganti, masing-masing, dengan 3. Selanjutnya, sesuai dengan skema – D4 – rumus akan berbentuk = B4 * C4, D5 – sama, tetapi dengan nomor 5 dan seterusnya.

Jika perlu (dalam banyak kasus ternyata), maka tidak ada masalah. Tetapi jika Anda perlu memperbaiki rumus dalam satu sel agar tidak berubah saat menyeret, maka ini akan agak lebih sulit. 

Misalkan kita perlu menentukan pendapatan dolar. Mari kita taruh di sel B7. Mari kita sedikit bernostalgia dan menunjukkan biaya 35 rubel per dolar. Dengan demikian, untuk menentukan pendapatan dalam dolar, perlu untuk membagi jumlah dalam rubel dengan nilai tukar dolar.

Inilah yang tampak seperti dalam contoh kita.

Jika kita, seperti versi sebelumnya, mencoba meresepkan formula, maka kita akan gagal. Demikian pula, rumus akan berubah menjadi yang sesuai. Dalam contoh kita, itu akan menjadi seperti ini: =E3*B8. Dari sini kita bisa melihat. bahwa bagian pertama dari rumus telah berubah menjadi E3, dan kami menetapkan tugas ini sendiri, tetapi kami tidak perlu mengubah bagian kedua dari rumus menjadi B8. Oleh karena itu, kita perlu mengubah referensi menjadi referensi yang mutlak. Anda dapat melakukan ini tanpa menekan tombol F4, hanya dengan meletakkan tanda dolar.

Setelah kami mengubah referensi ke sel kedua menjadi sel mutlak, itu menjadi terlindung dari perubahan. Sekarang Anda dapat menyeretnya dengan aman menggunakan pegangan isi otomatis. Semua data tetap akan tetap sama, terlepas dari posisi rumus, dan data yang tidak terikat akan berubah secara fleksibel. Di semua sel, pendapatan dalam rubel yang dijelaskan dalam baris ini akan dibagi dengan nilai tukar dolar yang sama.

Rumusnya sendiri akan terlihat seperti ini:

=D2/$B$7

Perhatian! Kami telah menunjukkan dua tanda dolar. Dengan cara ini, kami menunjukkan program bahwa kolom dan baris perlu diperbaiki.

Referensi sel di makro

Makro adalah subrutin yang memungkinkan Anda mengotomatiskan tindakan. Berbeda dengan fungsionalitas standar Excel, makro memungkinkan Anda untuk segera mengatur sel tertentu dan melakukan tindakan tertentu hanya dalam beberapa baris kode. Berguna untuk pemrosesan informasi secara batch, misalnya, jika tidak ada cara untuk menginstal add-on (misalnya, komputer perusahaan digunakan, bukan komputer pribadi).

Pertama, Anda perlu memahami bahwa konsep kunci makro adalah objek yang dapat berisi objek lain. Objek Workbooks bertanggung jawab atas buku elektronik (yaitu, dokumen). Ini termasuk objek Sheets, yang merupakan kumpulan semua lembar dokumen yang terbuka. 

Dengan demikian, sel adalah objek Sel. Ini berisi semua sel dari lembar tertentu.

Setiap objek dikualifikasikan dengan argumen dalam kurung. Dalam kasus sel, mereka direferensikan dalam urutan ini. Nomor baris dicantumkan terlebih dahulu, diikuti dengan nomor kolom atau huruf (kedua format dapat diterima).

Misalnya, baris kode yang berisi referensi ke sel C5 akan terlihat seperti ini:

Workbooks("Book2.xlsm").Sheets("List2").Sel(5, 3)

Workbooks("Book2.xlsm").Sheets("List2").Sel(5, "C")

Anda juga dapat mengakses sel menggunakan objek Rapi. Secara umum, ini dimaksudkan untuk memberikan referensi ke rentang (yang elemennya, omong-omong, juga bisa absolut atau relatif), tetapi Anda cukup memberikan nama sel, dalam format yang sama seperti dalam dokumen Excel.

Dalam hal ini, garis akan terlihat seperti ini.

Workbooks("Book2.xlsm").Sheets("List2").Range("C5")

Tampaknya opsi ini lebih nyaman, tetapi keuntungan dari dua opsi pertama adalah Anda dapat menggunakan variabel dalam tanda kurung dan memberikan tautan yang tidak lagi mutlak, tetapi sesuatu seperti yang relatif, yang akan bergantung pada hasil perhitungan.

Dengan demikian, makro dapat digunakan secara efektif dalam program. Faktanya, semua referensi ke sel atau rentang di sini akan menjadi absolut, dan karena itu juga dapat diperbaiki dengannya. Benar, itu tidak begitu nyaman. Penggunaan makro dapat berguna saat menulis program kompleks dengan sejumlah besar langkah dalam algoritme. Secara umum, cara standar menggunakan referensi absolut atau relatif jauh lebih nyaman. 

Kesimpulan

Kami menemukan apa itu referensi sel, cara kerjanya, untuk apa. Kami memahami perbedaan antara referensi absolut dan relatif dan menemukan apa yang perlu dilakukan untuk mengubah satu jenis menjadi yang lain (dengan kata sederhana, perbaiki alamat atau lepaskan pin). Kami menemukan bagaimana Anda dapat melakukannya segera dengan sejumlah besar nilai. Sekarang Anda memiliki fleksibilitas untuk menggunakan fitur ini dalam situasi yang tepat.