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

За да се олеснат пресметките со користење на формули во автоматски режим, се користат референци за ќелии. Во зависност од видот на пишувањето, тие се поделени на три главни типа:

1. Релативни врски. Се користи за едноставни пресметки. Копирањето формула подразбира промена на координатите.
2. Апсолутни врски. Ако треба да направите посложени пресметки, оваа опција е погодна. За поправање користете го симболот „$“. Пример: $A$1.
3. мешани врски. Овој тип на адресирање се користи во пресметките кога е неопходно да се поправи колона или линија одделно. Пример: $A1 или A$1.

Доколку е потребно да се копираат податоците од внесената формула, се користат референци со апсолутно и мешано адресирање. Написот ќе открие со примери како се прават пресметките со користење на разни видови врски.

Релативна референца на ќелија во Excel

Ова е збир на знаци кои ја дефинираат локацијата на ќелијата. Врските во програмата автоматски се пишуваат со релативно адресирање. На пример: А1, А2, Б1, Б2. Преместувањето во друг ред или колона ги менува знаците во формулата. На пример, почетна позиција А1. Со хоризонтално движење се менува буквата во B1, C1, D1, итн. На ист начин, промените се случуваат кога се движите по вертикална линија, само во овој случај бројот се менува – A2, A3, A4 итн. Доколку е потребно да се дуплира пресметка од ист тип во соседна ќелија, пресметката се врши со користење на релативна референца. За да ја користите оваа функција, следете неколку чекори:

1. Штом податоците ќе се внесат во ќелијата, поместете го курсорот и кликнете со глувчето. Истакнувањето со зелен правоаголник укажува на активирање на ќелијата и подготвеност за понатамошна работа.
2. Со притискање на комбинацијата на копчиња Ctrl + C, ја копираме содржината на таблата со исечоци.
3. Ја активираме ќелијата во која сакате да префрлите податоци или претходно напишана формула.
4. Со притискање на комбинацијата Ctrl + V ги пренесуваме податоците зачувани во системската таблата со исечоци.

Совет од експерт! За да го извршите истиот тип на пресметки во табелата, користете лајф хак. Изберете ја ќелијата што ја содржи претходно внесената формула. Лебдете го курсорот над мал квадрат што се појавува во долниот десен агол и држете го левото копче на глувчето, повлечете го до долниот ред или екстремната колона, во зависност од извршеното дејство. Со отпуштање на копчето на глувчето, пресметката ќе се изврши автоматски. Оваа алатка се нарекува маркер за автоматско полнење.

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

За да биде појасно, разгледајте пример за пресметка користејќи формула со релативна референца. Да претпоставиме дека сопственикот на спортска продавница по една година работа треба да го пресмета профитот од продажбата.

Редоследот на активности:

1. Примерот покажува дека колоните Б и В биле користени за пополнување на количината на продадена стока и нејзината цена. Според тоа, за да ја напишете формулата и да го добиете одговорот, изберете колона D. Формулата изгледа вака: = Б2 *C

Обрнете внимание! За да го олесните процесот на пишување формула, користете мал трик. Ставете го знакот „=“, кликнете на количината на продадена стока, поставете го знакот „*“ и кликнете на цената на производот. Формулата по знакот за еднаквост ќе се запише автоматски.

2. За конечниот одговор, притиснете „Enter“. Следно, треба да го пресметате вкупниот износ на добивка добиена од други видови производи. Па, ако бројот на линии не е голем, тогаш сите манипулации може да се извршат рачно. За да пополните голем број редови истовремено во Excel, постои една корисна функција што овозможува да се пренесе формулата во други ќелии.
3. Поместете го курсорот преку долниот десен агол на правоаголникот со формулата или готовиот резултат. Појавата на црн крст служи како сигнал дека курсорот може да се повлече надолу. Така се прави автоматска пресметка на добивката добиена за секој производ посебно.
4. Со отпуштање на притиснато копче на глувчето, ги добиваме точните резултати во сите линии.

Со кликнување на ќелијата D3, можете да видите дека координатите на ќелијата се автоматски променети и сега изгледаат вака: =Б3 *C3. Следи дека врските биле релативни.

Можни грешки при работа со релативни врски

Несомнено, оваа функција на Excel во голема мера ги поедноставува пресметките, но во некои случаи може да се појават тешкотии. Ајде да разгледаме едноставен пример за пресметување на коефициентот на профит на секоја ставка на стока:

1. Направете табела и пополнете: А – име на производот; Б – продадена количина; В – цена; D е добиениот износ. Да претпоставиме дека има само 11 ставки во асортиманот. Затоа, земајќи го предвид описот на колоните, се пополнуваат 12 реда и вкупниот износ на добивката е Д
2. Кликнете на ќелијата Е2 и внесете =D2/D13.
3. По притискање на копчето „Enter“, се појавува коефициентот на релативното учество на продажбата на првата ставка.
4. Истегнете ја колоната надолу и почекајте го резултатот. Сепак, системот ја дава грешката „#DIV/0!“

Причината за грешката е употребата на релативна референца за пресметки. Како резултат на копирање на формулата, координатите се менуваат. Тоа е, за Е3, формулата ќе изгледа вака =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!“, што ќе значи дека оваа операција не може да се изврши.

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

На крајот, да погледнеме уште еден пример за апсолутна врска – „СуперАпсолутно“ адресирање. Кои се неговите карактеристики и разлики. Земете го приближниот број 30 и внесете го во ќелијата Б2. Токму овој број ќе биде главниот, неопходно е да се извршат низа дејства со него, на пример, да се подигне на моќ.

1. За правилно извршување на сите дејства, внесете ја следната формула во колоната C: =$B$2^$D2. Во колоната D ја внесуваме вредноста на степените.

2. Откако ќе го притиснете копчето „Enter“ и ќе ја активирате формулата, го истегнуваме маркерот надолу по колоната.
3. Ги добиваме точните резултати.

Во крајна линија е дека сите извршени дејства се однесуваа на една фиксна ќелија Б2, затоа:

• Копирањето формула од ќелијата C3 во ќелијата E3, F3 или H3 нема да го промени резултатот. Ќе остане непроменет – 900.
• Ако треба да вметнете нова колона, координатите на ќелијата со формулата ќе се променат, но резултатот исто така ќе остане непроменет.

Ова е особеноста на врската „СуперАпсолутна“: ако треба да се преместите, резултатот нема да се промени. Сепак, постојат ситуации кога податоците се вметнуваат од извори од трети страни. Така, колоните се поместуваат на страна, а податоците се поставуваат на стариот начин во колоната Б2. Што се случува во овој случај? Кога се меша, формулата се менува според извршеното дејство, односно повеќе нема да покажува на B2, туку на C2. Но, бидејќи вметнувањето е направено во Б2, конечниот резултат ќе биде погрешен.

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

Овде се поставува прашањето: дали е можно да се измени формулата од ќелијата C2 така што оригиналниот број ќе се собере од ќелијата Б, и покрај вметнувањето нови колони со податоци? За да се осигурате дека промените во табелата нема да влијаат на определувањето на вкупниот број при инсталирање податоци од извори од трети страни, мора да ги извршите следните чекори:

1. Наместо координатите на ќелијата B2, внесете ги следните индикатори: =INDIRECT(„B2“). Како резултат на тоа, по преместувањето, составот за формулирање ќе изгледа вака: =INDIRECT(„B2“)^$E2.
2. Благодарение на оваа функција, врската секогаш укажува на квадрат со координати B2, без разлика дали се додаваат или отстранети колоните во табелата.

Мора да се разбере дека ќелијата што не содржи никакви податоци секогаш ја покажува вредноста „0“.

Заклучок

Благодарение на употребата на три варијанти на опишаните врски, се појавуваат многу можности што ја олеснуваат работата со пресметките во Excel. Затоа, пред да започнете да работите со формули, прво прочитајте ги врските и правилата за нивна инсталација.