змест
Часта карыстальнікі сутыкаюцца з неабходнасцю замацаваць вочка ў формуле. Напрыклад, гэта адбываецца ў сітуацыях, калі вы хочаце скапіяваць формулу, але так, каб спасылка не перамяшчалася ўверх і ўніз на тую ж колькасць вочак, на якую яна была скапіявана з першапачатковага месца.
У гэтым выпадку вы можаце выправіць спасылку на вочка ў Excel. Прычым зрабіць гэта можна адразу некалькімі спосабамі. Давайце больш падрабязна разгледзім, як дасягнуць гэтай мэты.
Што такое спасылка Excel
Ліст складаецца з вочак. Кожны з іх змяшчае пэўную інфармацыю. Іншыя клеткі могуць выкарыстоўваць яго ў разліках. Але як яны разумеюць, адкуль узяць дадзеныя? Гэта дапамагае ім ствараць спасылкі.
Кожная спасылка пазначае вочка з адной літарай і адной лічбай. Літара ўяўляе слупок, а лічба - радок.
Спасылкі бываюць трох відаў: абсалютныя, адносныя і змешаныя. Другі ўсталяваны па змаўчанні. Абсалютная спасылка - гэта тая, якая мае фіксаваны адрас як слупка, так і слупка. Адпаведна, змешанай лічыцца тая, дзе замацаваны альбо асобны слупок, альбо радок.
Спосаб 1
Каб захаваць адрасы радкоў і слупкоў, выканайце наступныя дзеянні:
- Націсніце на вочка, якая змяшчае формулу.
- Пстрыкаем па радку формул патрэбнай нам вочка.
- Націсніце F4.
Як следства, спасылка на ячэйку зменіцца на абсалютную. Яго можна пазнаць па характэрнаму знаку даляра. Напрыклад, калі вы націснеце на ячэйку B2, а потым на F4, спасылка будзе выглядаць так: $B$2.
Што азначае знак даляра перад часткай адраса ў ячэйцы?
- Калі ён размешчаны перад літарай, гэта азначае, што спасылка на слупок застаецца ранейшай, незалежна ад таго, куды была перамешчана формула.
- Калі знак даляра стаіць перад лікам, гэта азначае, што радок замацаваны.
Спосаб 2
Гэты спосаб практычна не адрозніваецца ад папярэдняга, толькі трэба двойчы націснуць F4. напрыклад, калі ў нас была ячэйка B2, то пасля гэтага яна стане B$2. Прасцей кажучы, такім чынам нам удалося выправіць лінію. У гэтым выпадку літара слупка зменіцца.
Гэта вельмі зручна, напрыклад, у табліцах, дзе трэба вывесці ў ніжнюю вочка змесціва другой зверху ячэйкі. Замест таго, каб выконваць такую формулу шмат разоў, дастаткова выправіць радок і даць змяніцца слупку.
Спосаб 3
Гэта цалкам тое ж самае, што і папярэдні спосаб, толькі трэба тройчы націснуць клавішу F4. Тады толькі спасылка на слупок будзе абсалютнай, а радок застанецца фіксаваным.
Спосаб 4
Дапусцім, у нас ёсць абсалютная спасылка на вочка, але тут трэба было зрабіць яе адноснай. Для гэтага націсніце клавішу F4 столькі разоў, каб у спасылцы не было знакаў $. Тады ён стане адносным, і пры перамяшчэнні або капіраванні формулы зменяцца як адрас слупка, так і адрас радка.
Замацаванне вочак для вялікага дыяпазону
Мы бачым, што вышэйпералічаныя спосабы не ўяўляюць ніякай складанасці ў выкананні. Але задачы канкрэтныя. І, напрыклад, што рабіць, калі ў нас адразу некалькі дзясяткаў формул, спасылкі ў якіх трэба перавесці ў абсалютныя.
На жаль, стандартныя метады Excel не дасягнуць гэтай мэты. Для гэтага вам трэба выкарыстоўваць спецыяльны адон пад назвай VBA-Excel. Ён змяшчае мноства дадатковых функцый, якія дазваляюць вам значна хутчэй выконваць звычайныя задачы з Excel.
Ён уключае больш за сотню функцый, якія вызначаюцца карыстальнікам, і 25 розных макрасаў, а таксама рэгулярна абнаўляецца. Гэта дазваляе палепшыць працу практычна з любым аспектам:
- Клеткі.
- Macro.
- Функцыі розных тыпаў.
- Спасылкі і масівы.
У прыватнасці, гэтая надбудова дазваляе выпраўляць спасылкі ў вялікай колькасці формул адначасова. Для гэтага неабходна выканаць наступныя дзеянні:
- Выберыце дыяпазон.
- Адкрыйце ўкладку VBA-Excel, якая з'явіцца пасля ўстаноўкі.
- Адкрыйце меню «Функцыі», дзе знаходзіцца параметр «Блакіраваць формулы».
6 - Далей з'явіцца дыялогавае акно, у якім неабходна паказаць неабходны параметр. Гэты адон дазваляе замацаваць слупок і слупок паасобку, разам, а таксама выдаліць ужо існуючае замацаванне з пакетам. Пасля выбару неабходнага параметру з дапамогай адпаведнага пераключальніка неабходна пацвердзіць свае дзеянні, націснуўшы «ОК».
Прыклад
Давайце возьмем прыклад, каб было больш зразумела. Дапусцім, у нас ёсць інфармацыя, якая апісвае кошт тавару, яго агульная колькасць і выручку ад рэалізацыі. І перад намі стаіць задача зрабіць так, каб табліца, зыходзячы з колькасці і кошту, аўтаматычна вызначала, колькі грошай удалося зарабіць без выліку страт.
У нашым прыкладзе для гэтага трэба ўвесці формулу =B2*C2. Гэта даволі проста, як бачыце. На яе прыкладзе вельмі лёгка апісаць, як можна выправіць адрас ячэйкі або яе асобнага слупка або радка.
Вядома, у гэтым прыкладзе вы можаце паспрабаваць перацягнуць формулу ўніз з дапамогай маркера аўтазапаўнення, але ў гэтым выпадку вочкі будуць аўтаматычна зменены. Такім чынам, у вочку D3 будзе іншая формула, дзе лічбы будуць заменены, адпаведна, на 3. Далей па схеме - D4 - формула прыме выгляд = B4 * C4, D5 - аналагічна, але з нумар 5 і гэтак далей.
Калі трэба (у большасці выпадкаў так і атрымліваецца), то праблем няма. Але калі трэба замацаваць формулу ў адной вочку, каб яна не змянялася пры перацягванні, то гэта будзе некалькі складаней.
Дапусцім, нам трэба вызначыць даляравую выручку. Давайце змясцім яго ў ячэйку B7. Крыху панастальгуем і пазначым кошт 35 рублёў за даляр. Адпаведна, каб вызначыць выручку ў далярах, неабходна суму ў рублях падзяліць на курс даляра.
Вось як гэта выглядае на нашым прыкладзе.
Калі мы, як і ў папярэднім варыянце, паспрабуем прапісаць формулу, то не атрымаецца. Аналагічным чынам формула зменіцца на адпаведную. У нашым прыкладзе гэта будзе так: =E3*B8. Адсюль мы бачым. што першая частка формулы ператварылася ў E3, і мы ставім перад сабой гэтую задачу, але нам не трэба мяняць другую частку формулы на B8. Такім чынам, нам трэба ператварыць спасылку ў абсалютную. Вы можаце зрабіць гэта без націску клавішы F4, проста паставіўшы знак даляра.
Пасля таго, як мы ператварылі спасылку на другую ячэйку ў абсалютную, яна стала абаронена ад змен. Цяпер вы можаце смела перацягваць яго з дапамогай маркера аўтазапаўнення. Усе фіксаваныя даныя застануцца нязменнымі, незалежна ад пазіцыі формулы, а незамацаваныя даныя будуць гнутка змяняцца. Ва ўсіх вочках выручка ў рублях, апісаная ў гэтым радку, будзе падзелена на аднолькавы курс даляра.
Сама формула будзе выглядаць так:
=D2/$B$7
Увага! Мы пазначылі два знакі даляра. Такім чынам мы паказваем праграме, што трэба выправіць і слупок, і радок.
Спасылкі на вочкі ў макрасах
Макрас - гэта падпраграма, якая дазваляе аўтаматызаваць дзеянні. У адрозненне ад стандартнага функцыяналу Excel, макрас дазваляе адразу задаць пэўную вочка і выканаць пэўныя дзеянні ўсяго за некалькі радкоў кода. Карысна для пакетнай апрацоўкі інфармацыі, напрыклад, калі няма магчымасці ўсталяваць дапаўненні (напрыклад, выкарыстоўваецца службовы, а не асабісты кампутар).
Спачатку вам трэба зразумець, што ключавая канцэпцыя макраса - гэта аб'екты, якія могуць утрымліваць іншыя аб'екты. За электронную кнігу (гэта значыць дакумент) адказвае аб'ект Workbooks. Ён уключае ў сябе аб'ект Sheets, які ўяўляе сабой набор усіх лістоў адкрытага дакумента.
Адпаведна, клеткі з'яўляюцца аб'ектам Cells. Ён змяшчае ўсе вочкі пэўнага ліста.
Кожны аб'ект вызначаецца аргументамі ў дужках. У выпадку з ячэйкамі, яны спасылаюцца ў такім парадку. Нумар радка паказваецца першым, а затым нумар слупка або літара (прымальныя абодва фарматы).
Напрыклад, радок кода са спасылкай на ячэйку C5 будзе выглядаць так:
Рабочыя сшыткі (“Book2.xlsm”). Аркушы (“List2”). Ячэйкі (5, 3)
Рабочыя сшыткі (“Book2.xlsm”). Аркушы (“List2”). Ячэйкі (5, “C”)
Вы таксама можаце атрымаць доступ да ячэйкі з дапамогай аб'екта Ахайны. Увогуле, гэта прызначана для спасылкі на дыяпазон (элементы якога, дарэчы, таксама могуць быць абсалютнымі або адноснымі), але вы можаце проста даць назву ячэйкі ў тым жа фармаце, што і ў дакуменце Excel.
У гэтым выпадку лінія будзе выглядаць так.
Рабочыя сшыткі (“Book2.xlsm”). Аркушы (“List2”). Дыяпазон (“C5”)
Можа здацца, што гэты варыянт больш зручны, але перавага першых двух варыянтаў у тым, што вы можаце выкарыстоўваць зменныя ў дужках і даваць спасылку ўжо не абсалютную, а нешта накшталт адноснай, якая будзе залежаць ад вынікаў разлікі.
Такім чынам, макрасы можна эфектыўна выкарыстоўваць у праграмах. Фактычна, усе спасылкі на ячэйкі або дыяпазоны тут будуць абсалютнымі, і таму іх таксама можна зафіксаваць. Праўда, гэта не так зручна. Выкарыстанне макрасаў можа быць карысным пры напісанні складаных праграм з вялікай колькасцю крокаў у алгарытме. Увогуле, стандартны спосаб выкарыстання абсалютных або адносных спасылак нашмат зручней.
Высновы
Мы разабраліся, што такое спасылка на вочка, як яна працуе, для чаго патрэбна. Мы зразумелі розніцу паміж абсалютнымі і адноснымі спасылкамі і высветлілі, што трэба зрабіць, каб ператварыць адзін тып у іншы (прасцей кажучы, выправіць адрас або адмацаваць яго). Мы высветлілі, як можна зрабіць гэта адразу з вялікай колькасцю значэнняў. Цяпер у вас ёсць магчымасць выкарыстоўваць гэтую функцыю ў патрэбных сітуацыях.