Абсалютная адрасаванне ў Excel: 2 правераных спосабу

Як вядома, у табліцах Excel існуе два віды адрасавання: адносная і абсалютная. У першым выпадку спасылка змяняецца па кірунку капіявання на адносную велічыню зруху, а ў другім - з'яўляецца фіксаванай і пры капіяванні застаецца нязменнай. Але па змаўчанні ўсе адрасы ў Эксэля з'яўляюцца абсалютнымі. У той жа час, даволі часта прысутнічае неабходнасць выкарыстоўваць абсалютную (фіксаваную) адрасаванне. Давайце даведаемся, якімі спосабамі гэта можна ажыццявіць.

Прымяненне абсалютнай адрасавання

Абсалютная адрасаванне нам можа спатрэбіцца, напрыклад, у тым выпадку, калі мы капіяваны формулу, адна частка якой складаецца з зменнай, якая адлюстроўваецца ў шэрагу лікаў, а другая мае сталае значэнне. Гэта значыць, дадзены лік гуляе ролю нязменнага каэфіцыента, з якім трэба правесці пэўную аперацыю (множанне, дзяленне і г.д.) ўсім шэрагу зменных лікаў.

У Excel існуе два спосабу задаць фіксаваную адрасаванне: шляхам фарміравання абсалютнай спасылкі і з дапамогай функцыі ДВССЫЛ. Давайце разгледзім кожны з названых спосабаў падрабязна.

Спосаб 1: абсалютная спасылка

Безумоўна, самым вядомым і часта ўжываюцца спосабам стварыць абсалютную адрасаванне з'яўляецца прымяненне абсалютных спасылак. Абсалютныя спасылкі маюць адрозненне не толькі функцыянальнае, але і сінтаксічнае. Адносны адрас мае такі сінтаксіс:

= A1

У фіксаванага адрасы перад значэннем каардынатаў усталёўваецца знак даляра:

= $ A $ 1

Знак даляра можна ўвесці ўручную. Для гэтага трэба ўсталяваць курсор перад першым значэннем каардынатаў адрасы (па гарызанталі), які знаходзіцца ў вочку або ў радку формул. Далей, у англамоўнай раскладцы клавіятуры варта клікнуць па клавішы «4» ў верхнім рэгістры (з заціснутай клавішай "Shift"). Менавіта там размешчаны сімвал даляра. Затым трэба тую ж працэдуру прарабіць і з каардынатамі па вертыкалі.

Існуе і больш хуткі спосаб. Трэба ўсталяваць курсор у вочка, у якой знаходзіцца адрас, і пстрыкнуць па функцыянальнай клавішы F4. Пасля гэтага знак даляра маментальна з'явіцца адначасова перад каардынатамі па гарызанталі і вертыкалі дадзенага адрасы.

Зараз давайце разгледзім, як прымяняецца на практыцы абсалютная адрасаванне шляхам выкарыстання абсалютных спасылак.

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

1. Такім чынам, у першую вочка слупка «Заработная плата» ўводзім формулу множання стаўкі адпаведнага работніка на каэфіцыент. У нашым выпадку гэтая формула мае такі выгляд:

   = C4 * G3



2. Каб разлічыць гатовы вынік, пстрыкаем па клавішы Enter на клавіятуры. Вынік выводзіцца ў вочка, якая змяшчае формулу.



3. Мы разлічылі значэнне зарплаты для першага супрацоўніка. Цяпер нам трэба гэта зрабіць для ўсіх астатніх радкоў. Вядома, аперацыю можна запісаць у кожнае вочка слупка «Заработная плата» ўручную, уводзячы аналагічную формулу з папраўкай на зрушэнне, але ў нас стаіць задача, як мага хутчэй выканаць вылічэнні, а ручной ўвод зойме вялікая колькасць часу. Ды і навошта марнаваць намаганні на ручной ўвод, калі формулу можна папросту скапіяваць у іншыя вочкі?

   Для капіявання формулы выкарыстоўваецца і ў дачыненні такі інструмент, як маркер запаўнення. Становімся курсорам у ніжні правы кут ячэйкі, дзе яна ўтрымліваецца. Пры гэтым сам курсор павінен пераўтварыцца ў гэты самы маркер запаўнення ў выглядзе крыжыка. Зацісканы левую кнопку мышы і цягнем курсор ўніз да канца табліцы.



4. Але, як бачым, замест карэктнага разліку заработнай платы для астатніх супрацоўнікаў, мы атрымалі адны нулі.



5. Глядзім, у чым прычына такога выніку. Для гэтага вылучаем другую вочка ў слупку «Заработная плата». У радку формул адлюстроўваецца адпаведнае дадзенай вочку выраз. Як бачым, першы множнік (C5) адпавядае стаўцы таго работніка, зарплату якога мы разлічваем. Зрушэнне каардынатаў у параўнанні з папярэдняй ячэйкай адбылося з-за ўласцівасці адноснасці. Зрэшты, у канкрэтна дадзеным выпадку гэта нам і трэба. Дзякуючы гэтаму першым множнікам стала стаўка менавіта патрэбнага нам работніка. Але зрушэнне каардынатаў адбылося і з другім множнікам. І цяпер яго адрас спасылаецца ня на каэфіцыент (1,28), а на пустую ячэйку, размешчаную ніжэй.

   

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

6. Для выпраўлення сітуацыі нам трэба змяніць адрасаванне другога множніка з адноснай на фіксаваную. Для гэтага вяртаемся да першай вочку слупка «Заработная плата», вылучыўшы яе. Далей перамяшчаемся ў радок формул, дзе адлюстравалася патрэбнае нам выраз. Вылучаем курсорам другі множнік (G3) і ціснем на функцыянальную клавішу на клавіятуры.



7. Як бачым, каля каардынатаў другога множніка з'явіўся знак даляра, а гэта, як мы памятаем, з'яўляецца атрыбутам абсалютнай адрасавання. Каб вывесці вынік на экран ціснем на клавішу Enter.



8. Цяпер, як і раней выклікаем маркер запаўнення, усталяваўшы курсор у правы ніжні кут першага элемента слупка «Заработная плата». Зацісканы левую кнопку мышы і цягнем яго ўніз.



9. Як бачым, у дадзеным выпадку разлік быў праведзены дакладна і сума заработнай платы для ўсіх работнікаў прадпрыемства разлічана карэктна.



10. Праверым, як была скапіяваная формула. Для гэтага вылучаем другі элемент слупка «Заработная плата». Глядзім на выраз, якое размешчана ў радку формул. Як бачым, каардынаты першага множніка (C5), які па-ранейшаму з'яўляецца адносным, ссунуліся ў параўнанні з папярэдняй ячэйкай на адзін пункт ўніз па вертыкалі. Затое другі множнік ($ G $ 3), адрасаванне у якім мы зрабілі фіксаванай, застаўся нязменным.

У Эксэля таксама прымяняецца, так званая змешаная адрасаванне. У гэтым выпадку ў адрасе элемента фіксуецца небудзь слупок, альбо радок. Дасягаецца гэта такім чынам, што знак даляра ставіцца толькі перад адным з каардынатаў адрасы. Вось прыклад тыповай змяшанай спасылкі:

= A $ 1

Гэты адрас таксама лічыцца змяшаным:

= $ A1

Гэта значыць, абсалютная адрасаванне ў змяшанай спасылцы выкарыстоўваецца толькі для аднаго са значэнняў каардынатаў з двух.

Паглядзім, як такую ​​змешаную спасылку можна прымяніць на практыцы на прыкладзе ўсё той жа табліцы заработнай платы супрацоўнікаў прадпрыемства.

1. Як бачым, раней мы зрабілі так, што ўсе каардынаты другога множніка маюць абсалютную адрасаванне. Але давайце разбярэмся, ці абавязкова ў гэтым выпадку абодва значэнні павінны быць фіксаванымі? Як бачым, пры капіяванні адбываецца зрушэнне па вертыкалі, а па гарызанталі каардынаты застаюцца нязменнымі. Таму цалкам можна ўжыць абсалютную адрасаванне толькі да каардынатах радкі, а каардынаты слупка пакінуць такімі, якімі яны з'яўляюцца па змаўчанні - адноснымі.

   Вылучаем першы элемент слупка «Заработная плата» і ў радку формул выконваем вышэйпаказаную маніпуляцыю. Атрымліваем формулу наступнага выгляду:

   = C4 * G $ 3

   Як бачым, фіксаваная адрасаванне ў другім множнік ўжываецца толькі ў адносінах да каардынатах радка. Для вываду выніку ў вочка пстрыкаем па кнопцы Enter.



2. Пасля гэтага з дапамогай маркера запаўнення капіюем дадзеную формулу на дыяпазон вочак, які размешчаны ніжэй. Як бачым, разлік заработнай платы па ўсім супрацоўнікам выкананы карэктна.



3. Глядзім, як адлюстроўваецца скапіяваць формула ў другой вочку слупка, над якім мы выконвалі маніпуляцыю. Як можна назіраць у радку формул, пасля выдзялення гэтага элемента ліста, нягледзячы на ​​тое, што абсалютную адрасаванне ў другога множніка мелі толькі каардынаты радкоў, зрушэнне каардынатаў слупка не адбылося. Гэта звязана з тым, што мы выконвалі капіяванне ня па гарызанталі, а па вертыкалі. Калі б мы выканалі капіраванне па гарызанталі, то ў аналагічным выпадку, наадварот, прыйшлося б рабіць фіксаваную адрасаванне каардынатаў слупкоў, а для радкоў гэтая працэдура была б неабавязковай.

Урок: Абсалютныя і адносныя спасылкі ў Эксэля

Спосаб 2: функцыя ДВССЫЛ

Другім спосабам арганізаваць абсалютную адрасаванне ў табліцы Excel з'яўляецца прымяненне аператара ДВССЫЛ. Указаная функцыя ставіцца да групы ўбудаваных аператараў «Спасылкі і масівы». Яе задачай з'яўляецца фарміраванне спасылкі на паказаную вочка з высновай выніку ў той элемент ліста, у якім знаходзіцца сам аператар. Пры гэтым спасылка прымацоўваецца да каардынатах яшчэ мацней, чым пры выкарыстанні знака даляра. Таму часам прынята называць спасылкі з выкарыстаннем ДВССЫЛ «суперабсолютными». Гэты аператар мае наступны сінтаксіс:

= ДВССЫЛ (ссылка_на_ячейку; [a1])

Функцыя мае ў наяўнасці два аргументу, першы з якіх мае абавязковы статус, а другі - не.

Аргумент «Спасылка на вочка» з'яўляецца спасылкай на элемент ліста Excel ў тэкставым выглядзе. Гэта значыць, гэта звычайная спасылка, але складзеная ў двукоссі. Менавіта гэта і дазваляе забяспечыць ўласцівасці абсалютнай адрасавання.

Аргумент «a1» - неабавязковы і выкарыстоўваецца ў рэдкіх выпадках. Яго ўжыванне неабходна толькі тады, калі карыстальнік выбірае альтэрнатыўны варыянт адрасавання, а не звычайнае выкарыстанне каардынатаў па тыпу «A1» (слупкі маюць літарнае пазначэнне, а радкі - лічбавае). Альтэрнатыўны варыянт мае на ўвазе выкарыстанне стылю «R1C1», у якім слупкі, як і радкі, абазначаюцца лічбамі. Пераключыцца ў дадзены рэжым працы можна праз акно параметраў Excel. Тады, ужываючы аператар ДВССЫЛ, у якасці аргументу «a1" неабходна ўказаць значэнне «ХЛУСНЯ». Калі вы працуе ў звычайным рэжыме адлюстравання спасылак, як і большасць іншых карыстальнікаў, то ў якасці аргументу «a1» можна паказаць значэнне «ПРАЎДА». Зрэшты, дадзенае значэнне маецца на ўвазе па змаўчанні, таму нашмат прасцей наогул у дадзеным выпадку аргумент «a1» не паказваць.

Зірнем, як будзе працаваць абсалютная адрасаванне, арганізаваная пры дапамозе функцыі ДВССЫЛ, на прыкладзе нашай табліцы заработнай платы.

1. Вырабляем вылучэнне першага элемента слупка «Заработная плата». Ставім знак «=». Як памятаем, першы множнік ў названай формуле вылічэнні зарплаты павінен быць прадстаўлены адносным адрасам. Таму проста клікаем на вочка, якая змяшчае адпаведнае значэнне акладу (C4). Услед за тым, як яе адрас адлюстраваўся ў элеменце для вываду выніку, ціснем на кнопку «памножыць» (*) на клавіятуры. Затым нам трэба перайсці да выкарыстання аператара ДВССЫЛ. Выконваем пстрычка па абразку «Уставіць функцыю».



2. У якое адкрылася акне Майстры функцый пераходзім у катэгорыю «Спасылкі і масівы». Сярод прадстаўленага спісу назваў вылучаем найменне «ДВССЫЛ». Затым пстрыкаем па кнопцы «OK».



3. Вырабляецца актывацыя акенца аргументаў аператара ДВССЫЛ. Яно складаецца з двух палёў, якія адпавядаюць аргументаў гэтай функцыі.

   Ставім курсор у полі «Спасылка на вочка». Проста клікаем па тым элементу ліста, у якім знаходзіцца каэфіцыент для разліку зарплаты (G3). Адрас тут жа з'явіцца ў поле акна аргументаў. Калі б мы мелі справу з звычайнай функцыяй, то на гэтым ўвядзенне адрасы можна было б лічыць завершаным, але мы выкарыстоўваем функцыю ДВССЫЛ. Як мы памятаем, адрасы ў ёй павінны мець выгляд тэксту. Таму абгортваюцца каардынаты, якая размешчана ў поле акна, двукоссямі.

   Так як мы працуем у стандартным рэжыме адлюстравання каардынатаў, то полі «A1» пакідаем незапоўненым. Пстрыкаем па кнопцы «OK».



4. Дадатак выконвае вылічэнне і выводзіць вынік у элемент ліста, які змяшчае формулу.



5. Цяпер вырабляем капіраванне дадзенай формулы ва ўсе астатнія вочкі слупка «Заработная плата» з дапамогай маркера запаўнення, як мы гэта рабілі раней. Як бачым, усе вынікі былі разлічаны дакладна.



6. Паглядзім, як адлюстроўваецца формула ў адной з вочак, куды яна была скапіяваная. Вылучаем другі элемент слупка і глядзім на радок формул. Як бачым, першы множнік, які з'яўляецца адноснай спасылкай, змяніў свае каардынаты. У той жа час, аргумент другога множніка, які прадстаўлены функцыяй ДВССЫЛ, застаўся нязменным. У дадзеным выпадку была выкарыстаная методыка фіксаванай адрасавання.

Урок: Аператар ДВССЫЛ ў Эксэля

Абсалютную адрасаванне ў табліцах Excel можна забяспечыць двума спосабамі: выкарыстанне функцыі ДВССЫЛ і прымяненне абсалютных спасылак. Пры гэтым функцыя забяспечвае больш жорсткую прывязку да адрасу. Часткова абсалютную адрасаванне можна таксама ўжываць пры выкарыстанні змешаных спасылак.