Спасылкі ў 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. З прыкладу відаць, што ў графах В і В запаўняліся колькасць прададзенага тавару і яго кошт. Адпаведна, каб напісаць формулу і атрымаць адказ, вылучыце слупок D. Формула выглядае так: = B2 *C

Звяртаць увагу! Каб палегчыць працэс напісання формулы, скарыстайцеся маленькай хітрасцю. Пастаўце знак «=», націсніце на колькасць прададзенага тавару, пастаўце знак «*» і націсніце на цану тавару. Формула пасля знака роўнасці будзе запісана аўтаматычна.

2. Каб атрымаць канчатковы адказ, націсніце «Enter». Далей трэба падлічыць агульную суму прыбытку, атрыманага ад іншых відаў прадукцыі. Ну а калі колькасць ліній не вялікае, то ўсе маніпуляцыі можна выканаць ўручную. Для адначасовага запаўнення вялікай колькасці радкоў у Excel ёсць адна карысная функцыя, якая дае магчымасць пераносіць формулу ў іншыя вочкі.
3. Навядзіце курсор на правы ніжні кут прамавугольніка з формулай або гатовым вынікам. З'яўленне чорнага крыжыка служыць сігналам таго, што курсор можна перацягнуць ўніз. Такім чынам, вырабляецца аўтаматычны разлік атрыманай прыбытку па кожным прадукце ў асобнасці.
4. Адпусціўшы націснутую кнопку мышы, мы атрымліваем правільныя вынікі ва ўсіх радках.

Націснуўшы на ячэйку D3, вы ўбачыце, што каардынаты ячэйкі былі аўтаматычна зменены і цяпер выглядаюць так: =B3 *C3. З гэтага вынікае, што спасылкі былі адноснымі.

Магчымыя памылкі пры працы з адноснымі спасылкамі

Безумоўна, гэтая функцыя Excel значна спрашчае разлікі, але ў некаторых выпадках могуць узнікнуць складанасці. Разгледзім просты прыклад разліку каэфіцыента прыбытку кожнай таварнай пазіцыі:

1. Стварыце табліцу і запоўніце: А – назву тавару; B – прададзеная колькасць; C – кошт; D - атрыманая сума. Дапусцім, у асартыменце ўсяго 11 найменняў. Такім чынам, з улікам апісання граф запаўняецца 12 радкоў і агульная сума прыбытку D
2. Націсніце на вочка E2 і ўвядзіце =D2/D13.
3. Пасля націску кнопкі «Увод» з'яўляецца каэфіцыент адноснай долі продажаў першага тавару.
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!», Што будзе азначаць, што дадзеную аперацыю выканаць немагчыма.

«Суперабсалютная» адрасацыя

У рэшце рэшт, давайце паглядзім на яшчэ адзін прыклад абсалютнай спасылкі - «SuperAbsolute» адрасаванне. У чым яго асаблівасці і адрозненні. Вазьміце прыблізную лічбу 30 і ўвядзіце яе ў ячэйку B2. Менавіта гэты лік будзе галоўным, з ім неабходна выканаць шэраг дзеянняў, напрыклад, узвесці яго ў ступень.

1. Для карэктнага выканання ўсіх дзеянняў увядзіце ў слупок C наступную формулу: =$B$2^$D2. У графу D ўпісваем значэнне градусаў.

2. Пасля націску кнопкі «Enter» і актывацыі формулы расцягваем маркер ўніз па слупку.
3. Мы атрымліваем правільныя вынікі.

Сутнасць у тым, што ўсе выкананыя дзеянні ставіліся да адной фіксаванай ячэйцы B2, таму:

• Капіраванне формулы з ячэйкі C3 у ячэйку E3, F3 або H3 не зменіць вынік. Яна застанецца нязменнай – 900.
• Пры неабходнасці ўстаўкі новага слупка каардынаты ячэйкі з формулай зменяцца, але вынік таксама застанецца нязменным.

У гэтым і ёсць асаблівасць звяна «СуперАбсалют»: пры неабходнасці рухацца вынік не зменіцца. Аднак бываюць сітуацыі, калі дадзеныя ўстаўляюцца са старонніх крыніц. Такім чынам, слупкі ссоўваюцца ў бок, а ў слупок В2 задаюцца дадзеныя па-старому. Што адбываецца ў гэтым выпадку? Пры змешванні формула змяняецца ў адпаведнасці з выкананым дзеяннем, гэта значыць будзе паказваць ужо не на B2, а на C2. Але паколькі ўстаўка была зроблена ў B2, канчатковы вынік будзе няправільным.

Спасылка! Каб мець магчымасць устаўляць макрасы са старонніх крыніц, неабходна ўключыць налады распрацоўніка (па змаўчанні яны адключаныя). Для гэтага перайдзіце ў Параметры, адкрыйце налады стужкі і пастаўце галачку ў правай калонцы насупраць «Распрацоўшчыка». Пасля гэтага адкрыецца доступ да многіх функцый, якія раней былі схаваныя ад вачэй звычайнага карыстальніка.

Тут узнікае пытанне: ці можна змяніць формулу з ячэйкі C2 так, каб зыходны лік збіраўся з ячэйкі B, нягледзячы на ​​ўстаўку новых слупкоў даных? Каб пераканацца, што змены ў табліцы не ўплываюць на вызначэнне сумы пры ўсталёўцы дадзеных са старонніх крыніц, неабходна выканаць наступныя дзеянні:

1. Замест каардынатаў ячэйкі B2 увядзіце наступныя паказчыкі: =УСКОСНЫ(“В2”). У выніку пасля перамяшчэння рэцэптурны склад будзе выглядаць так: =УСКОСНЫ(“B2”)^$E2.
2. Дзякуючы гэтай функцыі спасылка заўсёды паказвае на квадрат з каардынатамі B2, незалежна ад таго, дададзены або выдалены слупкі ў табліцы.

Трэба разумець, што вочка, якая не змяшчае ніякіх дадзеных, заўсёды паказвае значэнне «0».

заключэнне

Дзякуючы выкарыстанню трох разнавіднасцяў апісаных спасылак з'яўляецца маса магчымасцяў, якія палягчаюць працу з разлікамі ў Excel. Таму перш чым пачаць працаваць з формуламі, спачатку азнаёмцеся са спасылкамі і правіламі іх ўстаноўкі.