Как да замразим клетка във формула на Excel

Често потребителите се сблъскват с необходимостта да закачат клетка във формула. Например, възниква в ситуации, в които искате да копирате формула, но така че връзката да не се движи нагоре и надолу със същия брой клетки, както е била копирана от първоначалното си местоположение.

В този случай можете да коригирате препратката към клетка в Excel. И това може да стане по няколко начина едновременно. Нека разгледаме по-отблизо как да постигнем тази цел.

Какво е връзка към Excel

Листът е изграден от клетки. Всеки от тях съдържа специфична информация. Други клетки могат да го използват при изчисления. Но как разбират откъде да вземат данните? Помага им да създават връзки.

Всяка връзка обозначава клетка с една буква и едно число. Буквата представлява колона, а числото представлява ред. 

Има три вида връзки: абсолютни, относителни и смесени. Вторият е зададен по подразбиране. Абсолютна препратка е тази, която има фиксиран адрес както на колона, така и на колона. Съответно, смесен е този, при който е фиксирана или отделна колона, или ред.

Методът 1

За да запазите адресите на колони и редове, изпълнете следните стъпки:

1. Кликнете върху клетката, съдържаща формулата.
2. Кликнете върху лентата с формули за клетката, от която се нуждаем.
3. Натиснете F4.

В резултат на това препратката към клетката ще се промени на абсолютна. Може да се разпознае по характерния знак за долар. Например, ако щракнете върху клетка B2 и след това върху F4, връзката ще изглежда така: $B$2.

Какво означава знакът за долар преди частта от адреса на клетка?

1. Ако се постави пред буква, това показва, че препратката към колоната остава същата, независимо къде е преместена формулата.
2. Ако знакът за долар е пред числото, това означава, че низът е фиксиран. 

Методът 2

Този метод е почти същият като предишния, само че трябва да натиснете F4 два пъти. например, ако имаме клетка B2, след това тя ще стане B$2. С прости думи, по този начин успяхме да оправим линията. В този случай буквата на колоната ще се промени.

Много е удобно, например, в таблици, където трябва да покажете съдържанието на втората клетка отгоре в долната клетка. Вместо да правите такава формула много пъти, достатъчно е да коригирате реда и да оставите колоната да се промени.

Методът 3

Това е напълно същото като предишния метод, само че трябва да натиснете клавиша F4 три пъти. Тогава само препратката към колоната ще бъде абсолютна, а редът ще остане фиксиран.

Методът 4

Да предположим, че имаме абсолютна препратка към клетка, но тук беше необходимо да я направим относителна. За да направите това, натиснете клавиша F4 толкова пъти, че да няма знаци $ във връзката. Тогава тя ще стане относителна и когато преместите или копирате формулата, адресът на колоната и адресът на реда ще се променят.

Закрепване на клетки за голям диапазон

Виждаме, че горните методи не представляват никаква трудност за изпълнение. Но задачите са конкретни. И например какво да правим, ако имаме няколко десетки формули наведнъж, връзките в които трябва да се превърнат в абсолютни. 

За съжаление стандартните методи на Excel няма да постигнат тази цел. За да направите това, трябва да използвате специална добавка, наречена VBA-Excel. Той съдържа много допълнителни функции, които ви позволяват да изпълнявате общи задачи с Excel много по-бързо.

Той включва повече от сто дефинирани от потребителя функции и 25 различни макроса и също така се актуализира редовно. Позволява ви да подобрите работата с почти всеки аспект:

1. Клетки.
2. Macro.
3. Различни видове функции.
4. Връзки и масиви.

По-специално, тази добавка ви позволява да коригирате връзки в голям брой формули наведнъж. За да направите това, трябва да изпълните следните стъпки:

1. Изберете диапазон.
2. Отворете раздела VBA-Excel, който ще се появи след инсталирането. 
3. Отворете менюто „Функции“, където се намира опцията „Заключване на формули“.
   

   

4. След това ще се появи диалогов прозорец, в който трябва да посочите необходимия параметър. Тази добавка ви позволява да закачите колона и колона отделно, заедно, както и да премахнете вече съществуващо закрепване с пакет. След като изберете необходимия параметър с помощта на съответния бутон за избор, трябва да потвърдите действията си, като щракнете върху „OK“.

Пример

Нека вземем пример, за да стане по-ясно. Да приемем, че имаме информация, която описва себестойността на стоките, общото им количество и приходите от продажби. И ние сме изправени пред задачата таблицата, въз основа на количеството и цената, автоматично да определи колко пари сме успели да спечелим, без да приспадаме загубите.

В нашия пример за това трябва да въведете формулата =B2*C2. Съвсем просто е, както виждате. Много е лесно да използвате нейния пример, за да опишете как можете да коригирате адреса на клетка или нейната отделна колона или ред. 

Разбира се, в този пример можете да опитате да плъзнете формулата надолу с помощта на маркера за автоматично попълване, но в този случай клетките ще бъдат автоматично променени. Така че в клетка D3 ще има друга формула, където числата ще бъдат заменени съответно с 3. Освен това, според схемата - D4 - формулата ще приеме формата = B4 * C4, D5 - подобно, но с номер 5 и така нататък.

Ако е необходимо (в повечето случаи се оказва), тогава няма проблеми. Но ако трябва да коригирате формулата в една клетка, така че да не се променя при плъзгане, тогава това ще бъде малко по-трудно. 

Да предположим, че трябва да определим приходите в долари. Нека го поставим в клетка B7. Нека получим малко носталгия и посочим цената от 35 рубли за долар. Съответно, за да се определи приходите в долари, е необходимо сумата в рубли да се раздели на курса на долара.

Ето как изглежда в нашия пример.

Ако ние, подобно на предишната версия, се опитаме да предпишем формула, тогава ще се провалим. По същия начин формулата ще се промени на подходящата. В нашия пример ще бъде така: =E3*B8. От тук можем да видим. че първата част на формулата се е превърнала в E3 и ние си поставихме тази задача, но не е необходимо да променяме втората част на формулата на B8. Следователно трябва да превърнем препратката в абсолютна. Можете да направите това без да натискате клавиша F4, просто като поставите знак за долар.

След като превърнахме препратката към втората клетка в абсолютна, тя стана защитена от промени. Сега можете безопасно да го плъзнете с помощта на манипулатора за автоматично попълване. Всички фиксирани данни ще останат същите, независимо от позицията на формулата, а необвързаните данни ще се променят гъвкаво. Във всички клетки приходите в рубли, описани в този ред, ще бъдат разделени на същия обменен курс на долара.

Самата формула ще изглежда така:

=D2/$B$7

Внимание! Посочили сме два знака за долар. По този начин показваме на програмата, че и колоната, и редът трябва да бъдат коригирани.

Препратки към клетки в макроси

Макросът е подпрограма, която ви позволява да автоматизирате действия. За разлика от стандартната функционалност на Excel, макросът ви позволява незабавно да зададете конкретна клетка и да извършите определени действия само с няколко реда код. Полезно за пакетна обработка на информация, например, ако няма начин да се инсталират добавки (например се използва фирмен компютър, а не личен).

Първо трябва да разберете, че ключовата концепция на макроса са обекти, които могат да съдържат други обекти. Обектът Workbooks отговаря за електронната книга (т.е. документа). Той включва обекта Sheets, който е колекция от всички листове на отворен документ. 

Съответно клетките са обект Cells. Той съдържа всички клетки на определен лист.

Всеки обект се квалифицира с аргументи в скоби. В случай на клетки, те са посочени в този ред. Номерът на реда е посочен първи, последван от номера на колоната или буквата (и двата формата са приемливи).

Например ред от код, съдържащ препратка към клетка C5, ще изглежда така:

Работни книги („Book2.xlsm“). Листове („List2“). Клетки (5, 3)

Работни книги („Book2.xlsm“). Листове („List2“). Клетки (5, „C“)

Можете също да получите достъп до клетка с помощта на обект Подредено. По принцип има за цел да даде препратка към диапазон (чиито елементи, между другото, също могат да бъдат абсолютни или относителни), но можете просто да дадете име на клетка в същия формат като в документ на Excel.

В този случай линията ще изглежда така.

Работни книги (“Book2.xlsm”). Листове (“Списък2”).Обхват (“C5”)

Може да изглежда, че тази опция е по-удобна, но предимството на първите две опции е, че можете да използвате променливи в скоби и да дадете връзка, която вече не е абсолютна, а нещо като относителна, която ще зависи от резултатите на изчисленията.

По този начин макросите могат да се използват ефективно в програми. Всъщност всички препратки към клетки или диапазони тук ще бъдат абсолютни и следователно могат да бъдат фиксирани с тях. Вярно, че не е толкова удобно. Използването на макроси може да бъде полезно при писане на сложни програми с голям брой стъпки в алгоритъма. Като цяло, стандартният начин за използване на абсолютни или относителни препратки е много по-удобен. 

Заключения

Разбрахме какво е препратка към клетка, как работи, за какво служи. Разбрахме разликата между абсолютни и относителни препратки и разбрахме какво трябва да се направи, за да превърнем един тип в друг (с прости думи, коригирайте адреса или го откачете). Разбрахме как можете да го направите незабавно с голям брой стойности. Сега имате гъвкавостта да използвате тази функция в правилните ситуации.