Когато препращате към клетка във формула в Excel, типът препратка по подразбиране е относителна препратка. Тези препратки ще се променят, когато формулата се копира в други клетки въз основа на тяхната относителна колона и ред. Ако искате да запазите препратката постоянна, независимо къде е копирана формулата, трябва да използвате абсолютната препратка.

• Какво е абсолютна справка
• Как да правим абсолютни препратки
• Използвайте абсолютна препратка с примери
• Изчислете процента от общото
• Потърсете стойност и се върнете към съответната съвпадаща стойност
• 2 щраквания, за да направите групови препратки към клетки абсолютни с Kutools
• Относителна препратка и смесена препратка
• Неща, които трябва да запомните

Безплатно изтегляне на примерния файл

Видео: Абсолютна справка

Какво е абсолютна справка

Абсолютната препратка е вид препратка към клетка в Excel.

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

Абсолютна препратка се създава чрез добавяне на знак за долар ($) преди препратките към колони и редове във формулата. Например, за да създадете абсолютна препратка за клетка A1, трябва да я представите като $A$1.

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

Например диапазонът A4:C7 съдържа цените на продуктите и искате да получите дължимия данък за всеки продукт въз основа на данъчната ставка в клетка B2.

Ако използвате относителна препратка във формулата като "=B5*B2", някои грешни резултати се връщат, когато плъзнете манипулатора за автоматично попълване надолу, за да приложите тази формула. Тъй като препратката към клетка B2 ще се промени спрямо клетките във формулата. Сега формулата в клетка C6 е "=B6*B3", а формулата в клетка C7 е "=B7*B4"

Но ако използвате абсолютна препратка към клетка B2 с формулата "=B5*$B$2" ще гарантира, че данъчната ставка остава една и съща за всички клетки, когато формулата се плъзга надолу с помощта на манипулатора за автоматично попълване, резултатите са правилни.

Използване на относителна препратка		Използване на абсолютна препратка

Как да правим абсолютни препратки

За да направите абсолютна препратка в Excel, трябва да добавите знаци за долар ($) преди препратките към колоните и редовете във формулата. Има два начина за създаване на абсолютна препратка:

Добавете ръчно знаци за долар към препратката към клетката

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

Например, ако искате да съберете числата в клетка A1 и B1 и да направите и двете абсолютни, просто въведете формулата като „=$A$1+$B$1“. Това ще гарантира, че препратките към клетките остават постоянни, когато формулата се копира или премести в други клетки.

Или ако искате да промените препратките в съществуваща формула в клетка на абсолютни, можете да изберете клетката, след което да отидете в лентата за формули, за да добавите знаците за долар ($).

Използване на клавишна комбинация F4 за преобразуване на относителна референция в абсолютна

1. Кликнете два пъти върху клетката с формулата, за да влезете в режим на редактиране;
2. Поставете курсора върху препратката към клетката, която искате да направите абсолютна;
3. Натискане F4 клавиш на клавиатурата, за да превключвате типовете препратки, докато знаците за долар се добавят преди препратките към колони и редове;
4. Натискане Въведете клавиш за излизане от режима на редактиране и прилагане на промените.

Клавишът F4 може да превключва препратка между относителна препратка, абсолютна препратка и смесена препратка.

A1 → $A$1 → A$1 → $A1 → A1

Ако искате да направите всички препратки абсолютни във формула, изберете цялата формула в лентата за формули, натиснете F4 клавиш за превключване на типовете препратки, докато знаците за долар не бъдат добавени преди двете препратки към колони и редове.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Използвайте абсолютна препратка с примери

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

Пример 1 Изчислете процента от общото

Да предположим, че имате диапазон от данни (A3:B7), съдържащ продажбите на всеки плод, а клетка B8 съдържа общата сума на продажбите на тези плодове, сега искате да изчислите процента на всяка продажба на плодове от общата сума.

Общата формула за изчисляване на процента от общото:

Percentage = Sale/Amount

Използвайте относителната препратка във формулата, за да получите процента на първия плод по следния начин:

=B4/B8

Когато плъзнете манипулатора за автоматично попълване надолу, за да изчислите процента на други плодове, #DIV/0! грешки ще бъдат върнати.

Тъй като, когато плъзнете манипулатора за автоматично попълване, за да копирате формулата в клетките по-долу, относителната препратка B8 автоматично се настройва към други препратки към клетки (B9, B10, B11) въз основа на техните относителни позиции. И клетките B9, B10 и B11 са празни (нули), когато делителят е нула, формулата се връща към грешка.

За да коригирате грешките, в този случай трябва да направите препратката към клетка B8 абсолютна ($B$8) във формулата, за да не се променя, когато преместите или копирате формулата където и да е. Сега формулата е актуализирана до:

=B4/$B$8

След това плъзнете манипулатора за автоматично попълване надолу, за да изчислите процента на други плодове.

Пример 2 Потърсете стойност и се върнете към съответната съвпадаща стойност

Ако приемем, че искате да потърсите списъка с имена в D4:D5 и да върнете съответните им заплати въз основа на имената на персонала и съответната годишна заплата, предоставени в диапазон (A4:B8).

Общата формула за търсене е:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Ако използвате относителната препратка във формулата, за да търсите стойност и да върнете съответната съвпадаща стойност по този начин:

=VLOOKUP(D4,A4:B8,2,FALSE)

След това плъзнете манипулатора за автоматично попълване надолу, за да потърсите стойността по-долу, ще бъде върната грешка.

Когато плъзнете манипулатора за попълване надолу, за да копирате формулата в клетката по-долу, препратките във формулата автоматично се коригират надолу с един ред. В резултат на това препратката към диапазона на таблицата, A4:B8, става A5:B9. Тъй като „Lisa: не може да бъде намерено в диапазона A5:B9, формулата връща грешка.

За да избегнете грешките, използвайте абсолютна препратка $A$4:$B$8 вместо относителната препратка A4:B8 във формулата:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

След това плъзнете манипулатора за автоматично попълване надолу, за да получите заплатата на Лиза.

2 щраквания, за да направите групови препратки към клетки абсолютни с Kutools

Независимо дали изберете да въвеждате ръчно или да използвате клавишната комбинация F4, можете да променяте само една формула наведнъж в Excel. Ако искате да направите препратките към клетки в стотици формули абсолютни в Excel, Конвертиране на препратки инструмент на Kutools за Excel може да ви помогне да се справите със задачата с 2 кликвания.

Изберете клетките с формули, които искате да направите препратките към клетките абсолютни, щракнете Kutools > Още (fx) > Конвертиране на препратки. След това изберете До абсолютна и натиснете Ok or Добавете Обява. Сега всички препратки към клетки на избраните формули са преобразувани в абсолютни.

Относителна препратка и смесена препратка

Освен абсолютната референция има други два вида референции: относителна референция и смесена референция.

Относителна справка е типът препратки по подразбиране в Excel, който е без знаци за долар ($) преди препратки към редове и колони. И когато формула с относителни препратки се копира или премести в други клетки, препратките автоматично ще се променят въз основа на тяхната относителна позиция.

Например, когато въведете формула в клетка като "=A1+1", след това плъзнете манипулатора за автоматично попълване надолу, за да попълните тази формула в следващата клетка, формулата автоматично ще се промени на "=A2+1".

Смесена препратка се състои както от абсолютна препратка, така и от относителна препратка. С други думи, смесената препратка използва знака за долар ($), за да коригира реда или колоната, когато формула се копира или попълва.

Вземете таблица за умножение като пример, редовете и колоните изброяват числата от 1 до 9, които ще умножите едно с друго.

За да започнете, можете да използвате формулата "=B3*C2" в клетка C3, за да умножите 1 в клетка B3 по числото (1) в първата колона. Въпреки това, когато плъзнете манипулатора за автоматично попълване надясно, за да попълните другите клетки, ще забележите, че всички резултати са неправилни, с изключение на първия.

Това е така, защото когато копирате формулата вдясно, позицията на реда няма да се промени, но позицията на колоната се променя от B3 на C3, D3 и т.н. В резултат на това формулите в десните клетки (D3, E3, и т.н.) променете на "=C3*D2", "=D3*E2" и т.н., когато всъщност искате те да бъдат "=B3*D2", "=B3*E2" и т.н.

В този случай трябва да добавите знак за долар ($), за да заключите референтната колона на „B3“. Използвайте формулата по-долу:

=$B3*C2

Сега, когато плъзнете формулата надясно, резултатите са правилни.

След това трябва да умножите числото 1 в клетка C2 по числата в редовете по-долу.

Когато копирате формулата надолу, позицията на колоната на клетка C2 няма да се промени, но позицията на реда се променя от C2 на C3, C4 и т.н. В резултат на това формулите в клетките по-долу се променят на "=$B4C3", "=$B5C4" и т.н., което ще доведе до неправилни резултати.

За да разрешите този проблем, променете „C2“ на „C$2“, за да не се променя препратката към реда, когато плъзнете манипулатора за автоматично попълване надолу, за да попълните формулите.

=$B3*C$2

Сега можете да плъзнете манипулатора за автоматично попълване надясно или надолу, за да получите всички резултати.

Неща, които трябва да запомните

• Резюме на препратките към клетките

  Тип	Пример	Oбобщение
  Абсолютна справка	$1 $	Никога не променяйте, когато формулата се копира в други клетки
  Относителна справка	A1	Препратката към ред и колона се променя въз основа на относителната позиция, когато формулата се копира в други клетки
  Смесена справка	$A1/A$1	Препратката към ред се променя, когато формулата се копира в други клетки, но препратката към колона е фиксирана/Препратката към колона се променя, когато формулата се копира в други клетки, но препратката към ред е фиксирана;

• По принцип абсолютните препратки никога не се променят, когато формула се премести. Въпреки това, абсолютните препратки ще се коригират автоматично, когато ред или колона се добавят или премахват отгоре или отляво в работния лист. Например във формула "=$A$1+1", когато вмъкнете ред в горната част на листа, формулата автоматично ще се промени на "=$A$2+1".

  

• - F4 клавишът може да превключва между относителна референция, абсолютна референция и смесена референция.