Какво да знаете
- За да изчислите множество елементи въз основа на клетки на друго място и да запазите реда или колоната постоянни, използвайте абсолютна препратка към клетка.
- В това уравнение абсолютната референтна клетка е A$12:=+B2+B2+A$12.
- Символът $ "задържа" реда или колоната константа дори когато копирате или попълвате колона или ред със същата формула.
Тази статия обяснява как да използвате абсолютна препратка към клетка в Excel 2010 и по-нови версии, за да предотвратите автоматичното коригиране на формулите спрямо броя редове или колони, които сте попълнили.
Какво представлява абсолютна препратка към клетка в Excel?
Има два вида начини за препратка към клетка във формула в Excel. Можете да използвате относителна препратка към клетка или абсолютна препратка към клетка.
- Относителна препратка към клетка: Адрес на клетка, който не съдържа символа $ пред координатите на реда или колоната. Тази препратка автоматично ще актуализира колоната или клетката спрямо оригиналната клетка, когато попълвате надолу или напречно.
- Абсолютна референтна клетка: Адрес на клетка, който съдържа символа $ пред координатите на реда или колоната. Това "задържа" референтния ред или колона константа дори когато колона или ред се попълват със същата формула.
Реферирането на клетки може да изглежда като абстрактна концепция за тези, които са нови в нея.
Как да използвате абсолютна препратка към клетка в Excel
Има няколко различни начина за използване на абсолютна препратка в Excel. Методът, който използвате, зависи коя част от препратката искате да запазите постоянна: колоната или реда.
-
Например, вземете електронна таблица, в която искате да изчислите действителния данък върху продажбите за множество артикули, въз основа на референтни клетки на друго място в листа.
Image -
В първата клетка трябва да въведете формулата, като използвате стандартни относителни препратки към клетки за цените на артикулите. Но за да включите правилния данък върху продажбите в изчислението, трябва да използвате абсолютна препратка за реда, но не и за клетката.
Image -
Тази формула препраща към две клетки по различни начини. B2 е клетката отляво на първата клетка за данък върху продажбите спрямо позицията на тази клетка. Референтният номер A$12 сочи към действителния номер за данък върху продажбите, посочен в A12. Символът $ ще запази препратката към ред 12 постоянна, независимо в коя посока запълвате съседните клетки. Попълнете всички клетки под тази първа, за да видите това в действие.
Image -
Можете да видите, че когато попълвате колоната, клетката във втората колона използва относителна референтна клетка за стойностите на разходите в колона B, увеличавайки колоната, за да съответства на колоната за данъка върху продажбите. Въпреки това препратката към A12 остава постоянна поради символа $, който поддържа препратката към реда същата. Сега попълнете колоната D, като започнете от клетка C2.
Image -
Сега можете да видите, че попълването на реда отдясно използва правилния щатски данък върху продажбите (Индиана), тъй като препратката към колоната е относителна (измества с единица надясно точно както клетката, която попълвате). Относителната справка за разходите обаче сега също е изместена надясно, което е неправилно. Можете да коригирате това, като направите оригиналната формула в C2 да препраща към колона B с абсолютна препратка вместо това.
Image -
Поставяйки символ $ пред "B", вие създавате абсолютна препратка за колоната. Сега, когато попълните надясно, ще видите, че препратката към колона B за цена остава същата.
Image -
Сега, когато попълните колоната D надолу, ще видите, че всички относителни и абсолютни препратки към клетки работят точно както сте предвидили.
Image -
Абсолютните препратки към клетки в Excel са критични за контролиране на това кои клетки да се препращат, когато попълвате колони или редове. Използвайте символа $, както е необходимо, в препратката към клетката, за да запазите препратката към реда или колоната постоянна, за да препратите към правилните данни в правилната клетка. Когато комбинирате относителни и абсолютни препратки към клетка по този начин, това се нарича смесена препратка към клетка.
Ако използвате смесена препратка, важно е да подравните колоната или реда на изходните данни с колоната или реда, където въвеждате формулата. Ако сте направили препратката към реда относителна, имайте предвид, че когато попълвате отстрани, номерът на колоната на изходните данни ще се увеличи заедно с колоната на клетката с формула. Това е едно от нещата, които правят относителното и абсолютно адресиране сложно за новите потребители, но след като го научите, ще имате много по-голям контрол върху изтеглянето на клетки с изходни данни в препратките към вашите формули.
Използване на абсолютни препратки към клетка за фиксиране на една препратка към клетка
Друг подход за използване на абсолютно препращане към клетки е като го приложите както към колоната, така и към реда, за да "фиксирате" формулата, за да използвате само една клетка, независимо къде се намира.
Използвайки този подход, можете да попълните отстрани или надолу и референтната клетка винаги ще остане същата.
Използването на абсолютна препратка към клетка в колона и ред е ефективно само ако препращате само към една клетка във всички клетки, които попълвате.
-
Използвайки същата примерна електронна таблица от по-горе, можете да се позовавате само на единната щатска данъчна ставка, като добавите символа $ към препратката към колоната и реда.
Image -
Това прави както колоната "A", така и реда "12" да останат постоянни, независимо в каква посока запълвате клетките. За да видите това в действие, попълнете цялата колона за ME данък върху продажбите, след като сте актуализирали формулата с абсолютната препратка към колона и ред. Можете да видите, че всяка попълнена клетка винаги използва абсолютната препратка $A$12. Нито колоната, нито редът се променят.
Image -
Използвайте същата формула за колоната за данък върху продажбите в Индиана, но този път използвайте абсолютната препратка както за колона, така и за ред. В този случай това е $B$12.
Image -
Попълнете тази колона и отново ще видите, че препратката към B12 не се променя в нито една клетка, благодарение на абсолютната препратка както за колона, така и за ред.
Image -
Както можете да видите, има много начини, по които можете да използвате абсолютни препратки към клетки в Excel, за да изпълнявате подобни задачи. Това, което ви предоставят абсолютните препратки, е гъвкавостта да поддържате постоянни препратки към клетки дори когато попълвате голям брой колони или редове.
Можете да преминете през относителни или абсолютни препратки към клетки, като маркирате препратката и след това натиснете F4 Всеки път, когато натиснете F4, абсолютната препратка ще бъде приложена или към колоната, ред, както колоната, така и клетката, или нито една от тях. Това е лесен начин да промените формулата си, без да се налага да въвеждате символа $.
Кога трябва да използвате абсолютни препратки към клетки
В почти всяка индустрия и област има много причини, поради които може да искате да използвате абсолютни препратки към клетки в Excel.
- Използване на фиксирани множители (като цена за единица) в голям списък от артикули.
- Прилагайте един процент за всяка година, когато планирате годишни цели за печалба.
- Когато създавате фактури, използвайте абсолютни препратки, за да посочите една и съща данъчна ставка за всички елементи.
- Използвайте абсолютни препратки към клетки в управлението на проекти, за да препращате към фиксирани нива на наличност за отделни ресурси.
- Използвайте относителни препратки към колони и абсолютни препратки към редове, за да съпоставите изчисленията на колони във вашите референтни клетки със стойности на колони в друга таблица.
Ако все пак използвате комбинация от относителни и абсолютни препратки за колони или редове, просто трябва да се уверите, че позицията на колоните или редовете с изходни данни съвпада с колоната или реда на целевите клетки (където вие повторно въвеждане на формулата).
