Работен лист в Excel се състои от клетки. Тези клетки могат да бъдат препратени чрез посочване на стойността на реда и стойността на колоната.
Например, A1 ще се позовава на първия ред (посочен като 1) и първата колона (посочен като A). По същия начин B3 ще бъде третият ред и втората колона.
Силата на Excel се крие във факта, че можете да използвате тези препратки към клетки в други клетки при създаването на формули.
Сега има три вида препратки към клетки, които можете да използвате в Excel:
- Относителни препратки към клетки
- Абсолютни препратки към клетки
- Препратки към смесени клетки
Разбирането на тези различни типове препратки към клетки ще ви помогне да работите с формули и да спестите време (особено при формули за копиране).
Какво представляват относителните препратки към клетки в Excel?
Нека да взема един прост пример, за да обясня концепцията за относителни препратки към клетки в Excel.
Да предположим, че имам набор от данни, показан по -долу:
За да изчислим общата сума за всеки артикул, трябва да умножим цената на всеки артикул с количеството на този артикул.
За първия елемент формулата в клетка D2 ще бъде B2* C2 (както е показано по -долу):
Сега, вместо да въвеждате формулата за всички клетки една по една, можете просто да копирате клетка D2 и да я поставите във всички останали клетки (D3: D8). Когато го направите, ще забележите, че препратката към клетката автоматично се настройва, за да се позовава на съответния ред. Например формулата в клетка D3 става B3*C3 и формулата в D4 става B4*C4.
Извикват се тези препратки към клетки, които се коригират, когато клетката се копира относителни препратки към клетки в Excel.
Кога да използвате относителни препратки към клетки в Excel?
Относителните препратки към клетки са полезни, когато трябва да създадете формула за диапазон от клетки и формулата трябва да се позовава на относителна препратка към клетка.
В такива случаи можете да създадете формулата за една клетка и да я копирате-поставите във всички клетки.
Какво представляват абсолютните препратки към клетки в Excel?
За разлика от относителните препратки към клетки, абсолютните препратки към клетки не се променят, когато копирате формулата в други клетки.
Да предположим например, че имате набора от данни, както е показано по -долу, където трябва да изчислите комисионната за общите продажби на всеки артикул.
Комисионната е 20% и е посочена в клетка G1.
За да получите размера на комисионната за всяка продажба на артикул, използвайте следната формула в клетка E2 и копирайте за всички клетки:
= D2*$ G $ 1
Имайте предвид, че има два знака за долар ($) в препратката към клетката, която има комисионна - $G$2.
Какво прави знакът долар ($)?
Символ за долар, когато се добави пред номера на реда и колоната, го прави абсолютен (т.е. спира номера на реда и колоната да се променя, когато се копира в други клетки).
Например, в горния случай, когато копирам формулата от клетка E2 в E3, тя се променя от = D2*$ G $ 1 на = D3*$ G $ 1.
Обърнете внимание, че докато D2 се променя на D3, $ G $ 1 не се променя.
Тъй като сме добавили символ за долар пред „G“ и „1“ в G1, той няма да позволи на препратката към клетката да се промени, когато се копира.
Следователно това прави референтната клетка абсолютна.
Кога да използвате абсолютни препратки към клетки в Excel?
Абсолютните препратки към клетки са полезни, когато не искате препратката към клетката да се променя, докато копирате формули. Това може да е случаят, когато имате фиксирана стойност, която трябва да използвате във формулата (като данъчна ставка, комисионна, брой месеци и т.н.)
Въпреки че можете също така да кодирате твърдо тази стойност във формулата (т.е. използвайте 20% вместо $ G $ 2), като я имате в клетка и след това използвате препратката към клетката, ви позволява да я промените на бъдеща дата.
Например, ако структурата на комисионната ви се промени и сега плащате 25% вместо 20%, можете просто да промените стойността в клетка G2 и всички формули автоматично ще се актуализират.
Какво представляват препратките за смесени клетки в Excel?
Смесените препратки към клетки са малко по -трудни от абсолютните и относителните препратки към клетки.
Може да има два вида смесени клетъчни препратки:
- Редът е заключен, докато колоната се променя, когато формулата се копира.
- Колоната е заключена, докато редът се променя, когато формулата се копира.
Нека да видим как работи с помощта на пример.
По -долу е набор от данни, където трябва да изчислите трите нива на комисионна въз основа на процентната стойност в клетка E2, F2 и G2.
Сега можете да използвате силата на смесена справка, за да изчислите всички тези комисионни само с една формула.
Въведете формулата по -долу в клетка E4 и копирайте за всички клетки.
= $ B4*$ C4*E $ 2
Горната формула използва и двата вида препратки към смесени клетки (един, където редът е заключен и един, където колоната е заключена).
Нека анализираме всяка референтна клетка и да разберем как работи:
- $ B4 (и $ C4) - В тази справка знакът за долар е точно пред нотацията в колоната, но не и пред номера на реда. Това означава, че когато копирате формулата в клетките вдясно, препратката ще остане същата като фиксираната колона. Например, ако копирате формулата от E4 в F4, тази справка няма да се промени. Когато обаче го копирате, номерът на реда ще се промени, тъй като не е заключен.
- E $ 2 - В тази справка знакът за долар е точно пред номера на реда, а обозначението в колоната няма знак за долар. Това означава, че когато копирате формулата надолу по клетките, препратката няма да се промени, тъй като номерът на реда е заключен. Ако обаче копирате формулата вдясно, азбуката на колоната ще се промени, тъй като не е заключена.
Как да промените препратката от относителна на абсолютна (или смесена)?
За да промените препратката от относителна към абсолютна, трябва да добавите знака за долар преди нотацията на колоната и номера на реда.
Например, A1 е относителна референтна клетка и ще стане абсолютна, когато го направите $ A $ 1.
Ако имате само няколко препратки за промяна, може да ви е лесно да промените тези препратки ръчно. Така че можете да отидете в лентата с формули и да редактирате формулата (или да изберете клетката, да натиснете F2 и след това да я промените).
По -бързият начин да направите това обаче е като използвате клавишната комбинация - F4.
Когато изберете препратка към клетка (в лентата с формули или в клетката в режим на редактиране) и натиснете F4, тя променя препратката.
Да предположим, че имате препратка = A1 в клетка.
Ето какво се случва, когато изберете препратката и натиснете клавиша F4.
- Натиснете клавиша F4 веднъж: Референцията на клетката се променя от A1 на $ A $ 1 (става „абсолютна“ от „относителна“).
- Натиснете клавиша F4 два пъти: Референцията на клетката се променя от A1 на A $ 1 (променя се на смесена справка, където редът е заключен).
- Натиснете клавиша F4 три пъти: Референцията на клетката се променя от A1 на $ A1 (променя се на смесена справка, където колоната е заключена).
- Натиснете клавиша F4 четири пъти: Референцията на клетката отново става A1.
