Връзки в Excel – абсолютни, относителни и смесени. Грешки при работа с относителни връзки в Excel

За да се улеснят изчисленията с помощта на формули в автоматичен режим, се използват препратки към клетки. В зависимост от вида на писането те се разделят на три основни вида:

1. Относителни връзки. Използва се за прости изчисления. Копирането на формула води до промяна на координатите.
2. Абсолютни връзки. Ако трябва да направите по-сложни изчисления, тази опция е подходяща. За фиксиране използвайте символа “$”. Пример: $A$1.
3. смесени връзки. Този тип адресиране се използва при изчисления, когато е необходимо да се фиксира колона или ред отделно. Пример: $A1 или A$1.

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

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

Това е набор от знаци, които определят местоположението на клетка. Връзките в програмата се записват автоматично с относително адресиране. Например: A1, A2, B1, B2. Преминаването към друг ред или колона променя знаците във формулата. Например начална позиция А1. Хоризонталното преместване променя буквата на B1, C1, D1 и т.н. По същия начин стават промени при движение по вертикална линия, само че в този случай се променя числото - A2, A3, A4 и т.н. Ако е необходимо да се дублира изчисление от същия тип в съседна клетка, изчислението се извършва с помощта на относителна препратка. За да използвате тази функция, следвайте няколко стъпки:

1. Веднага щом данните бъдат въведени в клетката, преместете курсора и щракнете с мишката. Маркирането със зелен правоъгълник показва активирането на клетката и готовността за по-нататъшна работа.
2. Чрез натискане на клавишната комбинация Ctrl + C копираме съдържанието в клипборда.
3. Активираме клетката, в която искате да прехвърлите данни или предварително написана формула.
4. Чрез натискане на комбинацията Ctrl + V прехвърляме данните, записани в системния клипборд.

Експертен съвет! За да извършите същия тип изчисления в таблицата, използвайте лайфхак. Изберете клетката, съдържаща въведената преди това формула. Задръжте курсора върху малък квадрат, който се появява в долния десен ъгъл и задържите левия бутон на мишката, плъзнете до долния ред или крайната колона, в зависимост от извършеното действие. При отпускане на бутона на мишката изчислението ще се извърши автоматично. Този инструмент се нарича маркер за автоматично попълване.

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

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

Редът на действията:

1. Примерът показва, че колони B и C са използвани за попълване на количеството продадена стока и нейната цена. Съответно, за да напишете формулата и да получите отговора, изберете колона D. Формулата изглежда така: = B2 *C

Обърнете внимание! За да улесните процеса на писане на формула, използвайте малък трик. Поставете знака “=”, щракнете върху количеството продадена стока, поставете знака “*” и кликнете върху цената на продукта. Формулата след знака за равенство ще бъде написана автоматично.

2. За окончателен отговор натиснете „Enter“. След това трябва да изчислите общата сума на печалбата, получена от други видове продукти. Е, ако броят на редовете не е голям, тогава всички манипулации могат да се извършват ръчно. За да попълните голям брой редове едновременно в Excel, има една полезна функция, която ви позволява да прехвърлите формулата в други клетки.
3. Преместете курсора върху долния десен ъгъл на правоъгълника с формулата или готовия резултат. Появата на черен кръст служи като сигнал, че курсорът може да бъде плъзнат надолу. Така се прави автоматична калкулация на получената печалба за всеки продукт поотделно.
4. Отпускайки натиснатия бутон на мишката, получаваме правилните резултати във всички редове.

Като щракнете върху клетка D3, можете да видите, че координатите на клетката са променени автоматично и сега изглеждат така: =B3 *C3. От това следва, че връзките са относителни.

Възможни грешки при работа с относителни връзки

Несъмнено тази функция на Excel значително опростява изчисленията, но в някои случаи могат да възникнат трудности. Нека разгледаме прост пример за изчисляване на коефициента на печалба за всяка стока:

1. Създайте таблица и попълнете: A – име на продукт; B – продадено количество; C – себестойност; D е получената сума. Да предположим, че има само 11 артикула в асортимента. Следователно, като се вземе предвид описанието на колоните, се попълват 12 реда и общата сума на печалбата е D
2. Кликнете върху клетка E2 и въведете =D2 /D13.
3. След натискане на бутона “Enter” се появява коефициентът на относителен дял от продажбите на първия артикул.
4. Опънете колоната надолу и изчакайте резултата. Системата обаче дава грешка "#DIV/0!"

Причината за грешката е използването на относителна справка за изчисления. В резултат на копирането на формулата координатите се променят. Тоест за E3 формулата ще изглежда така =D3 /D13. Тъй като клетка D13 не е попълнена и теоретично има нулева стойност, програмата ще даде грешка с информация, че деленето на нула е невъзможно.

Важно! За да коригирате грешката, е необходимо да напишете формулата по такъв начин, че координатите D13 да са фиксирани. Относителното адресиране няма такава функционалност. За целта има друг вид връзки – абсолютни. 

Как се прави абсолютна връзка в Excel

Благодарение на използването на символа $ стана възможно да се фиксират координатите на клетката. Как работи това, ще разгледаме допълнително. Тъй като програмата използва относително адресиране по подразбиране, съответно, за да го направите абсолютно, ще трябва да извършите редица действия. Нека анализираме решението на грешката „Как да намерим коефициента от продажбата на няколко артикула стоки“, извършвайки изчислението с помощта на абсолютно адресиране:

1. Кликнете върху E2 и въведете координатите на връзката =D2 /D13. Тъй като връзката е относителна, трябва да се зададе символ, за да се коригират данните.
2. Фиксирайте координатите на клетка D. За да извършите това действие, пред буквата, обозначаваща колоната и номера на реда, поставете знак "$".

Експертен съвет! За да улесните задачата за въвеждане, достатъчно е да активирате клетката за редактиране на формулата и да натиснете клавиша F4 няколко пъти. Докато получите задоволителни стойности. Правилната формула е следната: =D2/$D$13.

3. Натиснете бутона "Enter". В резултат на извършените действия трябва да се появи правилният резултат.
4. Плъзнете маркера до долния ред, за да копирате формулата.

Поради използването на абсолютно адресиране в изчисленията, крайните резултати в останалите редове ще бъдат правилни.

Как да поставите смесена връзка в Excel

За формулни изчисления се използват не само относителни и абсолютни препратки, но и смесени. Тяхната отличителна черта е, че те фиксират една от координатите.

• Например, за да промените позицията на ред, трябва да напишете знака $ пред обозначението на буквата.
• Напротив, ако знакът за долар е написан след обозначението на буквата, тогава индикаторите в реда ще останат непроменени.

От това следва, че за да се реши предишната задача с определяне на коефициента на продажби на стоки чрез смесено адресиране, е необходимо да се фиксира номерът на реда. Тоест знакът $ се поставя след буквата на колоната, тъй като нейните координати не се променят дори при относителна препратка. Да вземем пример:

1. За точни изчисления въведете =D1/$D$3 и натиснете "Enter". Програмата дава точния отговор.
2. За да преместите формулата в следващите клетки надолу по колоната и да получите точни резултати, плъзнете манипулатора до долната клетка.
3. В резултат на това програмата ще даде правилните изчисления.

Внимание! Ако зададете знака $ пред буквата, тогава Excel ще даде грешка "#DIV/0!", Което ще означава, че тази операция не може да бъде изпълнена.

„Суперабсолютно“ адресиране

Накрая, нека разгледаме още един пример за абсолютна връзка – “SuperAbsolute” адресиране. Какви са неговите характеристики и разлики. Вземете приблизителното число 30 и го въведете в клетка B2. Това число ще бъде основното, с него е необходимо да извършите поредица от действия, например да го повишите до степен.

1. За правилното изпълнение на всички действия, въведете следната формула в колона C: =$B$2^$D2. В колона D въвеждаме стойността на градусите.

2. След като натиснете бутона „Enter“ и активирате формулата, разтягаме маркера надолу по колоната.
3. Получаваме правилните резултати.

Долната линия е, че всички извършени действия се отнасят до една фиксирана клетка B2, следователно:

• Копирането на формула от клетка C3 в клетка E3, F3 или H3 няма да промени резултата. Ще остане без промяна – 900.
• Ако трябва да вмъкнете нова колона, координатите на клетката с формулата ще се променят, но резултатът също ще остане непроменен.

Това е особеността на връзката „SuperAbsolute“: ако трябва да преместите, резултатът няма да се промени. Има обаче ситуации, когато се вмъкват данни от източници на трети страни. По този начин колоните се изместват настрани и данните се задават по стария начин в колона B2. Какво се случва в този случай? Когато се смеси, формулата се променя според извършеното действие, тоест вече няма да сочи към B2, а към C2. Но тъй като вмъкването е направено в B2, крайният резултат ще бъде грешен.

Справка! За да можете да вмъквате макроси от източници на трети страни, трябва да активирате настройките за програмисти (те са деактивирани по подразбиране). За да направите това, отидете на Опции, отворете настройките на лентата и поставете отметка в квадратчето в дясната колона срещу „Програмист“. След това ще се отвори достъп до много функции, които преди това са били скрити от очите на обикновения потребител.

Това повдига въпроса: възможно ли е да се модифицира формулата от клетка C2, така че оригиналното число да се събира от клетка B, въпреки вмъкването на нови колони с данни? За да сте сигурни, че промените в таблицата не засягат определянето на общата сума при инсталиране на данни от източници на трети страни, трябва да изпълните следните стъпки:

1. Вместо координатите на клетка B2 въведете следните индикатори: =INDIRECT(“B2”). В резултат на това след преместване формулиращият състав ще изглежда така: =INDIRECT(“B2”)^$E2.
2. Благодарение на тази функция връзката винаги сочи към квадрат с координати B2, независимо дали се добавят или премахват колони в таблицата.

Трябва да се разбере, че клетка, която не съдържа никакви данни, винаги показва стойността „0“.

Заключение

Благодарение на използването на три разновидности на описаните връзки се появяват много възможности, които улесняват работата с изчисления в Excel. Ето защо, преди да започнете да работите с формули, първо прочетете връзките и правилата за тяхното инсталиране.