Релативна и апсолутна референца ћелије и обликовање

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

СЦХООЛ НАВИГАТИОН

1. Зашто су вам потребне формуле и функције?
2. Дефинисање и креирање формуле
3. Релативна и апсолутна референца ћелије и обликовање
4. Корисне функције које бисте требали знати
5. Прегледи, графикони, статистика и обртне табеле

Белешка: претпоставићемо да већ знате да је ћелија један од квадрата у табели, распоређених у колоне и редове на које се позивају слова и бројеви који се приказују хоризонтално и вертикално.

Шта је референца на ћелије?

"Референца ћелије" означава ћелију на коју се односи друга ћелија. На пример, ако у ћелији А1 имате = А2. Онда А1 означава А2.

Хајде да размотримо оно што смо рекли у Лекцији 2 о редовима и колонама тако да можемо даље истраживати референце ћелија.

Ћелије у прорачунској табели односе се на редове и колоне. Колоне су вертикалне и означене словима. Редови су хоризонтални и означени бројевима.

Прва ћелија у табели је А1, што значи колона А, ред 1, Б3 се односи на ћелију лоцирану у другој колони, трећи ред, и тако даље.

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

Врсте референци ћелија

Постоје три типа референци ћелија.

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

Релативно - Релативно референцирање значи да се адреса ћелије мења док је копирате или померате; тј. референца ћелије је у односу на њену локацију.

Мијешано - То значи да можете одабрати сидрење било ретка или ступца када копирате или премјештате ћелију, тако да се једна мијења, а друга не. На пример, можете везати референцу реда, а затим померити ћелију у два реда и преко четири колоне, а референца реда остаје иста. Ово ћемо објаснити у наставку.

Релативе Референце

Хајде да погледамо тај ранији пример - претпоставимо да у ћелији А1 имамо формулу која једноставно каже = А2. То значи да је Екцел излаз у ћелији А1 све што је унето у ћелију А2. У ћелији А2 укуцали смо “А2” тако да Екцел у целици А1 приказује вредност “А2”.

Сада, претпоставимо да треба да направимо места у нашој табели за више података. Морамо додати колоне изнад и редове улево, тако да морамо да померимо ћелију доле и десно да направимо места.

Како померате ћелију удесно, број ступца се повећава. Док је померате, број реда се повећава. Ћелија на коју показује, референца ћелије, такође се мења. Ово је илустровано испод:

Настављајући са нашим примером и гледајући доњу слику, ако копирате садржај ћелије А1 два десно и четири доле, преместили сте је у ћелију Ц5.

Копирали смо две колоне десно и четири доле. То значи да смо променили ћелију, а односи се на два и четири. А1 = А2 је сада Ц5 = Ц6. Уместо да се односи на А2, сада се ћелија Ц5 односи на ћелију Ц6.

Приказана вредност је 0 јер је ћелија Ц6 празна. У ћелији Ц6 куцамо “Ја сам Ц6” и сада Ц5 приказује “Ја сам Ц6”.

Пример: Текстуална формула

Хајде да пробамо други пример. Запамтите из Лекције 2 где смо морали поделити пуно име и презиме? Шта се дешава када копирамо ову формулу?

Напишите формулу = ДЕСНО (А3, ЛЕН (А3) - ФИНД (“,”, А3) - 1) или копирајте текст у ћелију Ц3. Не копирајте стварну ћелију, само текст, копирајте текст, у супротном ће ажурирати референцу.

Садржај ћелије на врху табеле можете уредити у пољу поред места где пише „фк“. Тај оквир је дужи од ћелије у широком опсегу, тако да је лакше уређивати.

Сада имамо:

Ништа компликовано, управо смо написали нову формулу у ћелију Ц3. Сада копирајте Ц3 у ћелије Ц2 и Ц4. Обратите пажњу на доле наведене резултате:

Сада имамо имена Алекандер Хамилтон и Тхомаса Јефферсона.

Користите курсор да означите ћелије Ц2, Ц3 и Ц4. Усмерите курсор на ћелију Б2 и налепите садржај. Погледајте шта се десило - добили смо грешку: "#РЕФ." Зашто је ово?

Када смо копирали ћелије из колоне Ц у колону Б, он је ажурирао референтни ступац улево = ДЕСНО (А2, ЛЕН (А2) - ФИНД (“,”, А2) - 1).

Променила је сваку референцу на А2 на колону лево од А, али нема колоне са леве стране колоне А. Дакле, рачунар не зна шта мислите.

Нова формула у Б2 је, на пример, = РИГХТ (#РЕФ!, ЛЕН (#РЕФ!) - ФИНД (“,”, # РЕФ!) - 1) и резултат је #РЕФ:

Копирање формуле у опсег ћелија

Копирање ћелија је веома згодно јер можете написати једну формулу и копирати је на великом подручју и референца се ажурира. Тиме се избегава да се свака ћелија уређује како би се осигурало да показује тачно место.

Под "опсегом" подразумевамо више од једне ћелије. На пример, (Ц1: Ц10) означава све ћелије од ћелије Ц1 до ћелије Ц10. Дакле, то је колона ћелија. Други пример (А1: АЗ1) је горњи ред од колоне А до колоне АЗ.

Ако опсег прелази пет колона и десет редова, онда означавате опсег писањем горње леве ћелије и доње десне, нпр. А1: Е10. Ово је квадратна површина која прелази редове и колоне, а не само део колоне или дела реда.

Ево примера који илуструје како копирати једну ћелију на више локација. Претпоставимо да желимо да прикажемо наше планиране трошкове за месец у табели како бисмо могли да направимо буџет. Направимо табелу овако:

Сада копирајте формулу у ћелији Ц3 (= Б3 + Ц2) у остатак колоне да бисте добили текући биланс за наш буџет. Екцел ажурира референцу ћелије док је копирате. Резултат је приказан испод:

Као што видите, свака нова ћелија се ажурира у односу на нову локацију, тако да ћелија Ц4 ажурира своју формулу на = Б4 + Ц3:

Ћелија Ц5 се ажурира на = Б5 + Ц4, и тако даље:

Абсолуте Референцес

Апсолутна референца се не мења када преместите или копирате ћелију. Користимо $ знак да бисмо направили апсолутну референцу - да се сетимо тога, мислимо на знак долара као сидро.

На пример, унесите формулу = $ А $ 1 у било коју ћелију. $ Испред колоне А значи да не мењају колону, $ испред реда 1 значи да не мењају колону када копирате или преместите ћелију у било коју другу ћелију.

Као што можете видети у примеру испод, у ћелији Б1 имамо релативну референцу = А1. Када копирамо Б1 у четири ћелије испод ње, релативна референца = А1 се мења у ћелију лево, тако да Б2 постаје А2, Б3 постају А3, итд. Те ћелије очигледно немају унету вредност, тако да је излаз нула.

Међутим, ако користимо = $ А1 $ 1, као што је у Ц1 и копирамо га у четири ћелије испод њега, референца је апсолутна, тако да се никада не мења и излаз је увек једнак вредности у ћелији А1.

Претпоставимо да пратите свој интерес, као у примјеру испод. Формула у Ц4 = Б4 * Б1 је "каматна стопа" * "равнотежа" = "камата годишње".

Сада сте променили буџет и уштедели додатних 2.000 долара за куповину заједничког фонда. Претпоставимо да је то фонд са фиксном каматном стопом и да плаћа исту каматну стопу. Унесите нови рачун и стање у табелу, а затим копирајте формулу = Б4 * Б1 из ћелије Ц4 у ћелију Ц5.

Нови буџет изгледа овако:

Нови узајамни фонд зарађује $ 0 камате годишње, што не може бити тачно јер је каматна стопа јасно 5%.

Екцел наглашава ћелије на које упућује формула. Видите изнад да се референца на каматну стопу (Б1) помера у празну ћелију Б2. Требало је да направимо референцу на Б1 апсолутно тако што ћемо написати $ Б $ 1 користећи знак долара да бисмо усидрили референцу реда и колоне.

Поново напишите први израчун у Ц4 да бисте прочитали = Б4 * $ Б $ 1 као што је приказано испод:

Затим копирајте ту формулу из Ц4 у Ц5. Табела сада изгледа овако:

Пошто смо копирали формулу једне ћелије доле, тј. Повећали ред за један, нова формула је = Б5 * $ Б $ 1. Каматна стопа узајамног фонда се сада правилно обрачунава, јер је каматна стопа усидрена у ћелију Б1.

Ово је добар примјер када можете користити “име” за упућивање на ћелију. Име је апсолутна референца. На пример, да бисте додели име "каматна стопа" ћелији Б1, кликните десним тастером миша на ћелију, а затим изаберите "дефине наме."

Имена се могу односити на једну ћелију или опсег, а име можете користити у формули, на пример = интерест_рате * 8 је иста ствар као и за писање = $ Б $ 1 * 8.

Микед Референцес

Мјешовите референце су када било ред или колона је усидрена.

На пример, претпоставимо да сте пољопривредник који прави буџет. Ви такође поседујете складиште хране и продајете семе. Садите биљку кукуруза, соје и луцерке. Табела испод приказује цену по јутру. "Цена по хектару" = "цена по фунти" * "килограма семена по хектару" - то ће вас коштати да посадите хектар.

Унесите цену по јутру као = $ Б2 * Ц2 у ћелији Д2. Кажете да желите да усидрите цену по килограму фунте. Затим копирајте ту формулу у друге редове у истој колони:

Сада желите знати вриједност вашег инвентара сјемена. Потребна вам је цена по фунти и број фунти у инвентару да бисте знали вредност инвентара.

Додамо две колоне: "фунта семена у инвентару" и затим "вредност инвентара". Сада копирајте ћелију Д2 у Ф4 и имајте на уму да се референца реда у првом делу оригиналне формуле ($ Б2) ажурира у ред 4 али ступац остаје фиксиран зато што га $ сидри на "Б."

Ово је мешовита референца јер је колона апсолутна и ред је релативан.

Цирцулар Референцес

Кружна референца је када се формула односи на себе.

На пример, не можете да пишете ц3 = ц3 + 1. Ова врста прорачуна се назива “итерација”, што значи да се понавља. Екцел не подржава итерацију зато што све израчунава само једном.

Ако покушате да урадите ово тако што ћете откуцати СУМ (Б1: Б5) у ћелији Б5:

Појављује се екран упозорења:

Екцел вам само каже да имате кружну референцу на дну екрана тако да је можда не приметите. Ако имате кружну референцу и затворите табелу и поново је отворите, Екцел ће вам у искачућем прозору рећи да имате кружну референцу.

Ако имате кружну референцу, сваки пут када отворите табелу, Екцел ће вам са тим искачућим прозорима рећи да имате кружну референцу.

Референце на друге радне листове

“Радна свеска” је скуп “радних листова”. Једноставно речено, то значи да можете имати више табела (радних листова) у истом Екцел фајлу (радној књизи). Као што можете видети у доле наведеном примеру, наш пример радне свеске има много радних листова (у црвеној боји).

Радни листови се подразумевано називају Схеет1, Схеет2, и тако даље. Креирате нови кликом на “+” на дну екрана у Екцел-у.

Име радног листа можете променити у нешто корисно као што је "зајам" или "буџет" тако што ћете десним тастером миша кликнути на картицу радног листа која се приказује на дну екрана програма Екцел, изабрати преименовање и куцати ново име.

Или једноставно можете двапут кликнути на картицу и преименовати је.

Синтакса за референцу радног листа је = радни лист! Целл. Можете користити ову врсту референце када се иста вредност користи у два радна листа, примери тога могу бити:

• Данашњи датум
• Конверзије валута од долара до еура
• Све што је релевантно за све радне листове радне свеске

У наставку је приказан пример "интересовања" радног листа који се односи на радни лист "зајам", ћелија Б1.

Ако погледамо “кредит” радни лист, можемо видјети референцу на износ кредита:

Долази следећи…

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

То је за данашњу лекцију, у Лекцији 4, ми ћемо разговарати о неким корисним функцијама које бисте можда желели да знате за свакодневну употребу Екцела.