За да направите точни изчисления в Excel, е важно да разберете как работят различните видове препратки към клетки.
A1 срещу R1C1 Препратки
Работните листове на Excel съдържат много клетки и (по подразбиране) всяка клетка се идентифицира по своя колонна буква последвано от номера на неговия ред. Това е известно като препратки в стил А1. Примери: A1, B4, C6
Референтен стил А1
По желание можете да превключите на Референтен режим R1C1 за препратка към ред на клетка & номер на колона. Вместо да се позовавате на клетка A1, ще се обърнете към R1C1 (ред 1, колона 1). Клетка С4 ще се отнася до R4C3.
R1C1 референтен стил
Препратките в стил R1C1 са изключително необичайни в Excel. Освен ако нямате основателна причина, вероятно трябва да се придържате към стандартния режим на справка в стил А1. Ако обаче използвате VBA, вероятно ще срещнете този референтен стил.
Превключете към референтен стил R1C1
За да превключите референтния стил, отидете на Файл> Опция> Формула. Поставете отметка в квадратчето до R1C1 референтен стил.
Назовани диапазони
Една от най-слабо използваните функции на Excel е функцията Named Ranges. Вместо да се позовавате на клетка (или група от клетки) по нейното местоположение (например B3 или R3C2), можете да дадете име на този диапазон и просто да посочите името на диапазона във формулите си.
За да дадете име на диапазон:
- Изберете клетката или клетките, които искате да кръстите
- Щракнете вътре в полето Име на диапазона
- Въведете желаното от вас име
- Натиснете Enter
Сега можете да посочите клетка A1, като вместо това напишете = range_name1. Това е много полезно при работа с големи работни книги с множество работни листове.
Обхват на клетките
Когато използвате вградените функции на Excel, може да се наложи да се позовавате на диапазони от клетки. Клетъчните диапазони изглеждат така „A1: B3“. Тази справка се отнася за всички клетки между А1 и В3: клетки А1, А2, А3, В1, В2, В3.
За да изберете диапазон от клетки при въвеждане на формула:
- Въведете диапазона (разделете началния и крайния диапазон с точка и запетая)
- Използвайте мишката, за да щракнете върху първата препратка към клетка, задръжте натиснат бутона на мишката и плъзнете желания диапазон.
- Задръжте клавиша Shift и навигирайте със стрелките, за да изберете обхвата си
Абсолютни (замразени) и относителни препратки
Когато въвеждате препратки към клетки във формули, можете да използвате относителни или абсолютни (замразени) препратки. Относителните препратки ще се движат пропорционално, когато копирате формулата в нова клетка. Абсолютните препратки ще останат непроменени. Нека разгледаме някои примери:
Относителна справка
Относителна препратка в Excel изглежда така
= А1
Когато копирате и поставите формула с относителни препратки, относителните препратки ще се движат пропорционално. Освен ако не посочите друго, вашите препратки към клетки ще бъдат относителни (незамразени) по подразбиране.
Пример: Ако копирате „= A1“ надолу по един ред, препратката се променя на „= A2“.
Абсолютни (замразени) препратки към клетки
Ако не искате вашите препратки към клетки да се преместват, когато копирате формула, можете да „замразите“ препратките си към клетки, като добавите знаци за долар ($ s) пред препратката, която искате да бъде замразена. Сега, когато копирате и поставите формулата, препратката към клетката ще остане непроменена. Можете да изберете да замразите референтния ред, препратката към колоната или и двете.
A1: Нищо не е замразено
$ A1: Колоната е замразена, но редът не е замразен
A $ 1: Редът е замразен, но колоната не е замразена
$ A $ 1: И редът, и колоната са замразени
Абсолютен референтен пряк път
Ръчното добавяне на знаци за долар ($ s) във формулите ви не е много практично. Вместо това, докато създавате формулата си, използвайте клавиша F4, за да превключвате между абсолютни/относителни препратки към клетки.
Пример за абсолютна клетъчна справка
Кога всъщност ще трябва да замразите препратка към клетка? Един често срещан пример е, когато имате входни клетки, на които се позовава често. В примера по -долу искаме да изчислим данъка върху продажбите за всяко количество елементи от менюто. Данъкът върху продажбите е постоянен за всички артикули, така че ще се позоваваме на клетката за данък върху продажбите многократно.
За да намерите общия данък върху продажбите, въведете формулата „= (B3*C3)*$ C $ 1“ в колона D и копирайте формулата надолу.
Смесена справка
Може би сте чували за смесени препратки към клетки. Смесена препратка е, когато препратката към ред или колона е заключена (но не и двете).
Смесена справка
Не забравяйте, че с помощта на клавиша „F4“ можете да преминете през относителните си абсолютни препратки към клетки.
Препратки към клетки - Вмъкване и изтриване на редове/колони
Може би се чудите какво се случва с вашите препратки към клетки, когато вмъквате или изтривате редове/колони?
Препратката към клетката ще се актуализира автоматично, за да се отнесе към оригиналната клетка. Това е така, независимо дали препратката към клетката е замразена.
3D препратки
Понякога може да се наложи да работите с няколко работни листа с идентични модели данни. Excel ви позволява да препращате към няколко листа наведнъж, без да се налага ръчно да въвеждате всеки работен лист. Можете да се позовавате на диапазон от листове, подобен на този, който би се позовал на диапазон от клетки. Пример „Sheet1: Sheet5! A1“ ще се позовава на клетки A1 на всички листове от Sheet1 до Sheet5.
Нека преминем през пример:
Искате да съберете общите продадени единици за всеки продукт във всички магазини. Всеки магазин има свой собствен работен лист и всички работни листове имат идентичен формат. Можете да създадете формула, подобна на тази:
Това не е твърде трудно само с четири работни листа, но какво ще стане, ако имате 40 работни листа? Наистина ли искате да добавите ръчно всяка препратка към клетка?
Вместо това можете да използвате 3D справка, за да препращате с лекота към няколко листа наведнъж (подобно на начина, по който можете да се позовавате на диапазон от клетки).
Бъди внимателен! Редът на работните ви листове има значение. Ако преместите друг лист между референтните листове (StoreA и StoreD), този лист ще бъде включен. И обратно, ако преместите лист извън обхвата на листове (преди StoreA или след StoreD), той вече няма да бъде включен.
Кръгова клетъчна справка
Кръгла референтна клетка е, когато клетката се връща към себе си. Например, ако резултатът от клетка В1 се използва като вход за клетка В1, тогава се създава кръгова справка. Не е необходимо клетката да се отнася директно към себе си. Може да има междинни стъпки.
Пример:
В този случай формулата за клетка B2 е „A2+A3+B2“. Тъй като сте в клетка В2, не можете да използвате В2 в уравнението. Това ще задейства кръгова справка и стойността в клетка „B2“ автоматично ще бъде зададена на „0“.
Обикновено кръговите препратки са резултат от грешка на потребителя, но при някои обстоятелства може да искате да използвате кръгова справка. Основният пример за използване на кръгова справка е да се изчисляват стойностите итеративно. За да направите това, трябва да отидете на Файл> Опции> Формули и Активирано итеративно изчисление:
Външни справки
Понякога при изчисляване на данни може да се наложи да се позовавате на данни извън работната си книга. Това се нарича външна справка (връзка).
За да изберете външна справка, докато създавате формула, отидете до външната работна книга и изберете препратката си, както обикновено.
За да преминете към другата работна книга, можете да използвате клавишната комбинация CTRL + TAB или да отидете на Изглед> Превключване на Windows.
След като изберете клетката, ще видите, че външна препратка, която изглежда така:
Забележете, че името на работната книга е затворено в скоби [].
След като затворите посочената работна книга, препратката ще покаже местоположението на файла:
Когато отворите отново работната книга, съдържаща външната връзка, ще бъдете подканени да разрешите автоматично актуализиране на връзките. Ако направите това, Excel ще отвори референтната стойност с текущата стойност на работната книга. Дори да е затворен! Бъди внимателен! Можете или не искате това.
Именовани диапазони и външни справки
Какво се случва с вашата външна препратка към клетка, когато редовете / колоните се добавят или изтрият от справочната работна книга? Ако и двете работни книги са отворени, препратките към клетки се актуализират автоматично. Ако обаче двете работни книги не са отворени, препратките към клетки няма да се актуализират и вече няма да са валидни. Това е огромно безпокойство при свързване с външни работни книги. Много грешки са причинени от това.
Ако правите връзка към външни работни книги, трябва да дадете име на препратката към клетка с именуван диапазон (вижте предишния раздел за повече информация). Сега вашата формула ще се позовава на посочения диапазон, без значение какви промени се случват във външната работна книга.
