Cum să înghețe o celulă într-o formulă Excel

Adesea, utilizatorii se confruntă cu necesitatea de a fixa o celulă într-o formulă. De exemplu, apare în situațiile în care doriți să copiați o formulă, dar astfel încât linkul să nu se miște în sus și în jos cu același număr de celule precum a fost copiat din locația sa inițială.

În acest caz, puteți repara referința celulei în Excel. Și acest lucru se poate face în mai multe moduri simultan. Să aruncăm o privire mai atentă la cum să atingem acest obiectiv.

Ce este un link Excel

Foaia este formată din celule. Fiecare dintre ele conține informații specifice. Alte celule îl pot folosi în calcule. Dar cum înțeleg ei de unde să obțină datele? Îi ajută să facă linkuri.

Fiecare link desemnează o celulă cu o literă și un număr. O literă reprezintă o coloană, iar un număr reprezintă un rând. 

Există trei tipuri de legături: absolute, relative și mixte. Al doilea este setat implicit. O referință absolută este una care are o adresă fixă ​​atât a unei coloane, cât și a unei coloane. În consecință, mixt este cel în care este fixată fie o coloană separată, fie un rând.

Metoda 1

Pentru a salva adresele de coloană și rând, urmați acești pași:

1. Faceți clic pe celula care conține formula.
2. Faceți clic pe bara de formule pentru celula de care avem nevoie.
3. Apăsați F4.

În consecință, referința celulei se va schimba în absolută. Poate fi recunoscut după semnul dolar caracteristic. De exemplu, dacă faceți clic pe celula B2 și apoi faceți clic pe F4, linkul va arăta astfel: $B$2.

Ce înseamnă semnul dolarului înaintea părții din adresă per celulă?

1. Dacă este plasat în fața unei litere, indică faptul că referința coloanei rămâne aceeași, indiferent unde a fost mutată formula.
2. Dacă semnul dolarului este în fața numărului, indică faptul că șirul este fixat. 

Metoda 2

Această metodă este aproape aceeași cu cea anterioară, doar că trebuie să apăsați F4 de două ori. de exemplu, dacă am avut celula B2, atunci va deveni B$2. Cu cuvinte simple, în acest fel am reușit să fixăm linia. În acest caz, litera coloanei se va schimba.

Este foarte convenabil, de exemplu, în tabelele în care trebuie să afișați conținutul celei de-a doua celule de sus în celula de jos. În loc să faci o astfel de formulă de multe ori, este suficient să repari rândul și să lași coloana să se schimbe.

Metoda 3

Aceasta este complet aceeași cu metoda anterioară, doar că trebuie să apăsați tasta F4 de trei ori. Atunci doar referința la coloană va fi absolută, iar rândul va rămâne fix.

Metoda 4

Să presupunem că avem o referință absolută la o celulă, dar aici a fost necesar să o facem relativă. Pentru a face acest lucru, apăsați tasta F4 de atâtea ori încât nu există semne $ în link. Apoi va deveni relativă, iar când mutați sau copiați formula, atât adresa coloanei, cât și adresa rândului se vor schimba.

Fixarea celulelor pentru o gamă largă

Vedem că metodele de mai sus nu prezintă deloc dificultăți de executat. Dar sarcinile sunt specifice. Și, de exemplu, ce să facem dacă avem câteva zeci de formule deodată, legăturile în care trebuie transformate în absolute. 

Din păcate, metodele standard Excel nu vor atinge acest obiectiv. Pentru a face acest lucru, trebuie să utilizați un supliment special numit VBA-Excel. Conține multe caracteristici suplimentare care vă permit să efectuați sarcini obișnuite cu Excel mult mai rapid.

Include mai mult de o sută de funcții definite de utilizator și 25 de macrocomenzi diferite și este, de asemenea, actualizat în mod regulat. Vă permite să îmbunătățiți munca cu aproape orice aspect:

1. Celulele.
2. Macro.
3. Funcții de diferite tipuri.
4. Legături și matrice.

În special, acest program de completare vă permite să remediați legăturile într-un număr mare de formule simultan. Pentru a face acest lucru, trebuie să efectuați următorii pași:

1. Selectați un interval.
2. Deschideți fila VBA-Excel care va apărea după instalare. 
3. Deschideți meniul „Funcții”, unde se află opțiunea „Blocare formule”.
   

   

4. În continuare, va apărea o casetă de dialog în care trebuie să specificați parametrul necesar. Acest addon vă permite să fixați o coloană și o coloană separat, împreună și, de asemenea, să eliminați o fixare deja existentă cu un pachet. După ce parametrul necesar este selectat utilizând butonul radio corespunzător, trebuie să vă confirmați acțiunile făcând clic pe „OK”.

Exemplu

Să luăm un exemplu pentru a fi mai clar. Să presupunem că avem informații care descriu costul mărfurilor, cantitatea totală și veniturile din vânzări. Și ne confruntăm cu sarcina de a face ca tabelul, pe baza cantității și a costului, să determine automat câți bani am reușit să câștigăm fără a deduce pierderile.

În exemplul nostru, pentru aceasta trebuie să introduceți formula =B2*C2. Este destul de simplu, după cum puteți vedea. Este foarte ușor să folosiți exemplul ei pentru a descrie modul în care puteți remedia adresa unei celule sau a unei coloane sau rânduri individuale. 

Desigur, în acest exemplu, puteți încerca să trageți formula în jos folosind marcatorul de completare automată, dar în acest caz, celulele vor fi modificate automat. Deci, în celula D3 va exista o altă formulă, unde numerele vor fi înlocuite, respectiv, cu 3. În continuare, conform schemei – D4 – formula va lua forma = B4 * C4, D5 – în mod similar, dar cu numărul 5 și așa mai departe.

Dacă este necesar (în majoritatea cazurilor se dovedește), atunci nu există probleme. Dar dacă trebuie să fixați formula într-o singură celulă, astfel încât să nu se schimbe la tragerea, atunci acest lucru va fi oarecum mai dificil. 

Să presupunem că trebuie să determinăm venitul în dolari. Să-l punem în celula B7. Să fim puțin nostalgici și să indicăm costul de 35 de ruble pe dolar. În consecință, pentru a determina veniturile în dolari, este necesar să se împartă suma în ruble la cursul de schimb al dolarului.

Iată cum arată în exemplul nostru.

Dacă noi, în mod similar cu versiunea anterioară, încercăm să prescriem o formulă, atunci vom eșua. În mod similar, formula se va schimba în cea corespunzătoare. În exemplul nostru, va fi așa: =E3*B8. De aici putem vedea. că prima parte a formulei s-a transformat în E3 și ne-am propus această sarcină, dar nu trebuie să schimbăm a doua parte a formulei la B8. Prin urmare, trebuie să transformăm referința într-una absolută. Puteți face acest lucru fără să apăsați tasta F4, doar punând semnul dolarului.

După ce am transformat referința la a doua celulă într-una absolută, aceasta a devenit protejată de schimbări. Acum îl puteți trage în siguranță folosind mânerul de completare automată. Toate datele fixe vor rămâne aceleași, indiferent de poziția formulei, iar datele necommitate se vor schimba în mod flexibil. În toate celulele, venitul în ruble descris în acest rând va fi împărțit la același curs de schimb al dolarului.

Formula în sine va arăta astfel:

=D2/$B$7

Atenție! Am indicat două semne de dolar. În acest fel, arătăm programului că atât coloana, cât și rândul trebuie să fie fixate.

Referințe de celule în macrocomenzi

O macrocomandă este o subrutină care vă permite să automatizați acțiuni. Spre deosebire de funcționalitatea standard a Excel, o macrocomandă vă permite să setați imediat o anumită celulă și să efectuați anumite acțiuni în doar câteva rânduri de cod. Util pentru procesarea în serie a informațiilor, de exemplu, dacă nu există nicio modalitate de a instala suplimente (de exemplu, se folosește un computer al companiei, nu unul personal).

Mai întâi trebuie să înțelegeți că conceptul cheie al unei macrocomenzi este obiectele care pot conține alte obiecte. Obiectul Workbooks este responsabil pentru cartea electronică (adică documentul). Include obiectul Sheets, care este o colecție de toate foile unui document deschis. 

În consecință, celulele sunt un obiect Cells. Conține toate celulele unei anumite foi.

Fiecare obiect este calificat cu argumente între paranteze. În cazul celulelor, acestea sunt referite în această ordine. Numărul rândului este afișat primul, urmat de numărul coloanei sau litera (ambele formate sunt acceptabile).

De exemplu, o linie de cod care conține o referință la celula C5 ar arăta astfel:

Caiete de lucru(„Cartea 2.xlsm”). Foi de calcul(„Lista 2”). Celule (5, 3)

Caiete de lucru(„Cartea 2.xlsm”). Foi („Lista 2”). Celulele (5, „C”)

De asemenea, puteți accesa o celulă folosind un obiect Ordonat. În general, se dorește să dea o referință la un interval (ale cărui elemente, de altfel, pot fi și absolute sau relative), dar pur și simplu poți da un nume de celulă, în același format ca într-un document Excel.

În acest caz, linia va arăta astfel.

Caiete de lucru(„Cartea2.xlsm”). Foi de calcul(„Lista2”). Interval(„C5”)

Poate părea că această opțiune este mai convenabilă, dar avantajul primelor două opțiuni este că puteți folosi variabile între paranteze și puteți da o legătură care nu mai este absolută, ci ceva de genul relativ, care va depinde de rezultatele calculele.

Astfel, macrocomenzile pot fi utilizate eficient în programe. De fapt, toate referințele la celule sau intervale de aici vor fi absolute și, prin urmare, pot fi, de asemenea, fixate cu ele. Adevărat, nu este atât de convenabil. Utilizarea macrocomenzilor poate fi utilă atunci când scrieți programe complexe cu un număr mare de pași în algoritm. În general, modul standard de utilizare a referințelor absolute sau relative este mult mai convenabil. 

Concluzii

Ne-am dat seama ce este o referință de celulă, cum funcționează, pentru ce este. Am înțeles diferența dintre referințele absolute și relative și ne-am dat seama ce trebuie făcut pentru a transforma un tip în altul (în cuvinte simple, remediați adresa sau anulați fixarea). Ne-am dat seama cum o puteți face imediat cu un număr mare de valori. Acum aveți flexibilitatea de a utiliza această funcție în situațiile potrivite.