Везе у Екцел-у – апсолутне, релативне и мешовите. Грешке при раду са релативним везама у Екцел-у

Да би се олакшали прорачуни користећи формуле у аутоматском режиму, користе се референце на ћелије. У зависности од врсте писања, деле се на три главна типа:

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

Уколико је потребно копирати податке унете формуле, користе се референце са апсолутним и мешовитим адресирањем. Чланак ће на примерима открити како се прорачуни праве коришћењем различитих врста веза.

Релативна референца ћелије у Екцел-у

Ово је скуп знакова који дефинишу локацију ћелије. Везе у програму се аутоматски пишу са релативним адресирањем. На пример: А1, А2, Б1, Б2. Прелазак у други ред или колону мења знакове у формули. На пример, почетна позиција А1. Хоризонтално померање мења слово у Б1, Ц1, Д1 итд. На исти начин промене се дешавају и при кретању по вертикалној линији, само што се у овом случају мења број – А2, А3, А4 итд. Ако је потребно дуплирати прорачун истог типа у суседној ћелији, прорачун се врши коришћењем релативне референце. Да бисте користили ову функцију, следите неколико корака:

1. Чим се подаци унесу у ћелију, померите курсор и кликните мишем. Истицање зеленим правоугаоником указује на активацију ћелије и спремност за даљи рад.
2. Притиском на комбинацију тастера Цтрл + Ц копирамо садржај у међуспремник.
3. Активирамо ћелију у коју желите да пренесете податке или претходно написану формулу.
4. Притиском на комбинацију Цтрл + В преносимо податке сачуване у системској клипборду.

Стручни савети! Да бисте извршили исту врсту прорачуна у табели, користите лајф хак. Изаберите ћелију која садржи претходно унету формулу. Пређите курсором преко малог квадрата који се појављује у доњем десном углу и држите леви тастер миша, превуците до доњег реда или крајње колоне, у зависности од извршене радње. Отпуштањем дугмета миша, прорачун ће се извршити аутоматски. Овај алат се зове маркер за аутоматско попуњавање.

Пример релативног линка

Да би било јасније, размотрите пример израчунавања користећи формулу са релативном референцом. Претпоставимо да власник спортске радње након годину дана рада треба да израчуна профит од продаје.

Редослед акција:

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

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

2. За коначни одговор, притисните "Ентер". Затим морате израчунати укупан износ добити добијене од других врста производа. Па, ако број линија није велики, онда се све манипулације могу извршити ручно. Да бисте попунили велики број редова истовремено у Екцелу, постоји једна корисна функција која омогућава преношење формуле у друге ћелије.
3. Померите курсор преко доњег десног угла правоугаоника са формулом или готовим резултатом. Појава црног крста служи као сигнал да се курсор може повући надоле. Тако се врши аутоматски обрачун добити добијене за сваки производ посебно.
4. Отпуштањем притиснутог дугмета миша добијамо тачне резултате у свим редовима.

Кликом на ћелију Д3, можете видети да су координате ћелије аутоматски промењене и да сада изгледају овако: =Б3 *ЦКСНУМКС. Из тога следи да су везе биле релативне.

Могуће грешке при раду са релативним везама

Без сумње, ова Екцел функција у великој мери поједностављује прорачуне, али у неким случајевима могу настати потешкоће. Хајде да размотримо једноставан пример израчунавања коефицијента профита сваке ставке робе:

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

Разлог за грешку је употреба релативне референце за прорачуне. Као резултат копирања формуле, координате се мењају. То јест, за Е3, формула ће изгледати овако =Д3/ДКСНУМКС. Пошто ћелија Д13 није попуњена и теоретски има нулту вредност, програм ће дати грешку са информацијом да је дељење са нулом немогуће.

Важно! Да бисте исправили грешку, потребно је написати формулу на начин да координате Д13 буду фиксне. Релативно адресирање нема такву функционалност. Да бисте то урадили, постоји још једна врста веза - апсолутна. 

Како направити апсолутну везу у Екцелу

Захваљујући употреби симбола $, постало је могуће поправити координате ћелије. Како ово функционише, размотрићемо даље. Пошто програм подразумевано користи релативно адресирање, да би га учинио апсолутним, мораћете да извршите низ радњи. Хајде да анализирамо решење грешке „Како пронаћи коефицијент од продаје неколико артикала робе”, обављајући прорачун користећи апсолутно адресирање:

1. Кликните на Е2 и унесите координате везе =Д2/ДКСНУМКС. Пошто је веза релативна, мора се поставити симбол да поправи податке.
2. Поправите координате ћелије Д Да бисте извршили ову радњу, претходите слову које означава колону и број реда тако што ћете ставити знак „$“.

Стручни савети! Да бисте олакшали задатак уноса, довољно је активирати ћелију за уређивање формуле и притиснути тастер Ф4 неколико пута. Док не добијете задовољавајуће вредности. Тачна формула је следећа: =Д2/$Д$13.

3. Притисните дугме „Ентер“. Као резултат извршених радњи, требало би да се појави тачан резултат.
4. Превуците маркер до доње линије да бисте копирали формулу.

Због употребе апсолутног адресирања у прорачунима, коначни резултати у преосталим редовима ће бити тачни.

Како ставити мешовиту везу у Екцел

За прорачуне формуле не користе се само релативне и апсолутне референце, већ и мешовите. Њихова карактеристична карактеристика је да фиксирају једну од координата.

• На пример, да бисте променили позицију линије, морате написати знак $ испред ознаке слова.
• Напротив, ако је знак долара написан након ознаке слова, онда ће индикатори у линији остати непромењени.

Из овога произилази да је за решавање претходног проблема са одређивањем коефицијента продаје робе применом мешовитог адресирања потребно фиксирати број реда. То јест, знак $ се поставља после слова колоне, јер се његове координате не мењају чак ни у релативној референци. Узмимо пример:

1. За тачне прорачуне унесите =Д1/$Д$3 и притисните „Ентер“. Програм даје тачан одговор.
2. Да бисте померили формулу у следеће ћелије низ колону и добили тачне резултате, превуците ручицу до доње ћелије.
3. Као резултат, програм ће дати тачне прорачуне.

Пажња! Ако поставите знак $ испред слова, онда ће Екцел дати грешку „#ДИВ/0!“, што ће значити да се ова операција не може извршити.

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

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

1. За исправно извршење свих радњи, у колону Ц унесите следећу формулу: =$Б$2^$Д2. У колону Д уносимо вредност степени.

2. Након што притиснемо дугме „Ентер“ и активирамо формулу, протежемо маркер низ колону.
3. Добијамо тачне резултате.

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

• Копирање формуле из ћелије Ц3 у ћелију Е3, Ф3 или Х3 неће променити резултат. Остаће непромењена – 900.
• Ако треба да убаците нову колону, координате ћелије са формулом ће се променити, али ће и резултат остати непромењен.

Ово је посебност везе „СуперАбсолуте“: ако треба да померите резултат се неће променити. Међутим, постоје ситуације када се подаци убацују из извора трећих страна. Тако се колоне померају у страну, а подаци се постављају на стари начин у колони Б2. Шта се дешава у овом случају? Када се помеша, формула се мења према извршеној радњи, односно више неће показивати на Б2, већ на Ц2. Али пошто је убацивање направљено у Б2, коначни резултат ће бити погрешан.

Референца! Да бисте могли да убаците макрое из извора трећих страна, потребно је да омогућите подешавања програмера (оне су подразумевано онемогућене). Да бисте то урадили, идите на Опције, отворите подешавања траке и означите поље у десној колони насупрот „Програмер“. Након тога, отвориће се приступ многим функцијама које су раније биле скривене од очију просечног корисника.

Ово поставља питање: да ли је могуће модификовати формулу из ћелије Ц2 тако да се оригинални број прикупи из ћелије Б, упркос убацивању нових колона података? Да бисте били сигурни да промене у табели не утичу на одређивање укупног износа приликом инсталирања података из извора треће стране, морате извршити следеће кораке:

1. Уместо координата ћелије Б2 унесите следеће индикаторе: =ИНДИРЕКТНО(“Б2”). Као резултат тога, након померања формулације композиције ће изгледати овако: =ИНДИРЕКТНО(“Б2”)^$Е2.
2. Захваљујући овој функцији, веза увек показује на квадрат са координатама Б2, без обзира да ли се колоне додају или уклањају у табели.

Мора се схватити да ћелија која не садржи никакве податке увек показује вредност „0“.

Zakljucak

Захваљујући употреби три варијанте описаних веза, појављује се много могућности које олакшавају рад са прорачунима у Екцел-у. Стога, пре него што почнете да радите са формулама, прво прочитајте везе и правила за њихову инсталацију.