Относителна и абсолютна референтна клетка и форматиране

В този урок обсъждаме клетъчни препратки, как да копираме или преместваме формула, и да форматираме клетки. Да започнем с това, да изясним какво имаме предвид под клетъчните препратки, които са в основата на силата и гъвкавостта на формули и функции. Конкретно разбиране за това как работят клетъчните препратки ще ви позволи да извлечете максимума от своите електронни таблици в Excel!

НАВИГАЦИЯ НА УЧИЛИЩАТА

1. Защо ви трябват формули и функции?
2. Дефиниране и създаване на формула
3. Относителна и абсолютна референтна клетка и форматиране
4. Полезни функции, които трябва да знаете
5. Показвания, графики, статистически данни и обобщени таблици

Забележка: просто ще приемем, че вече знаете, че клетката е един от квадратите в електронната таблица, подредени в колони и редове, които се отнасят с букви и цифри, които се изпълняват хоризонтално и вертикално.

Какво е препратка към клетка?

"Референция на клетка" означава клетката, към която се отнася друга клетка. Например, ако в клетка A1 имате = A2. Тогава А1 се отнася до А2.

Нека разгледаме това, което казахме в Урок 2 за редове и колони, за да можем по-нататък да изследваме позоваванията на клетките.

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

Първата клетка в електронната таблица е А1, което означава колона А, ред 1, В3 се отнася за клетката, разположена във втората колона, трети ред и т.н..

За учебни цели относно препратките към клетките понякога ще ги пишем като ред, колона, това не е валидна нотация в електронната таблица и е просто предназначена да направи нещата по-ясни.

Видове клетъчни препратки

Има три типа препратки към клетки.

Абсолютно - това означава, че препратката в клетката остава същата, ако копирате или премествате клетката в друга клетка. Това става чрез закотвяне на реда и колоната, така че да не се променя при копиране или преместване.

Относително - относителното рефериране означава, че адресът на клетката се променя, докато го копирате или премествате; референтната клетка е по отношение на нейното местоположение.

Смесен - Това означава, че можете да изберете да закрепите или реда или колоната, когато копирате или премествате клетката, така че да се променя, а другата не. Например, можете да закрепите референцията на редовете, след това да преместите клетка надолу по два реда и в четири колони, а препратката на редовете остава същата. Ще обясним това по-долу.

Относителни препратки

Нека се позовем на този по-ранен пример - да предположим, че в клетка A1 имаме формула, която просто казва = A2. Това означава, че изходът на Excel в клетка A1 е каквото е въведено в клетка A2. В клетка A2 сме набрали “A2”, така че Excel показва стойността “A2” в клетка A1.

Сега, да предположим, че трябва да направим място в нашата електронна таблица за повече данни. Трябва да добавим колони отгоре и редове наляво, така че трябва да преместим клетката надолу и надясно, за да направим място.

Когато преместите клетката надясно, номерът на колоната се увеличава. Когато я преместите надолу, номерът на реда се увеличава. Клетката, върху която сочи, препраща към клетката, също се променя. Това е илюстрирано по-долу:

Продължавайки с нашия пример и разглеждайки графиката по-долу, ако копирате съдържанието на клетка A1 две надясно и четири надолу, преместете го в клетка C5.

Ние копирахме клетката две колони надясно и четири надолу. Това означава, че сме сменили клетката, в която се отнасят две и четири надолу. А1 = А2 сега е C5 = C6. Вместо да се позовава на А2, сега клетка С5 се отнася до клетка С6.

Показаната стойност е 0, защото клетка C6 е празна. В клетка C6 пишем “Аз съм C6” и сега C5 показва “Аз съм C6”.

Пример: Формула за текст

Да опитаме друг пример. Помниш ли от урок 2, където трябваше да разделим пълното име на името и фамилията? Какво става, когато копираме тази формула?

Напишете формулата = RIGHT (A3, LEN (A3) - FIND (“,”, A3) - 1) или копирайте текста в клетка C3. Не копирайте действителната клетка, а само текста, копирайте текста, в противен случай ще актуализира препратката.

Можете да редактирате съдържанието на клетка в горната част на електронната таблица в полето до мястото, където се казва „fx“. Това поле е по-дълго от клетката, която е широка, така че е по-лесно да се редактира.

Сега имаме:

Нищо сложно, току-що написахме нова формула в клетка C3. Сега копирайте С3 в клетки С2 и С4. Спазвайте резултатите по-долу:

Сега имаме имената на Александър Хамилтън и Томас Джеферсън.

Използвайте курсора, за да маркирате клетки C2, C3 и C4. Насочете курсора към клетка B2 и поставете съдържанието. Вижте какво се е случило - получаваме грешка: "#REF". Защо е така?

Когато копирахме клетките от колона В в колона Б, тя актуализира референтната колона наляво = НАДЯСНО (А2, ЛЕН (А2) - НАМЕРИ (“,”, А2) - 1).

Промениха всяко позоваване на А2 към колоната вляво от А, но вляво от колона няма колона, така че компютърът не знае какво имаш предвид.

Новата формула в B2 например е = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) и резултатът е #REF:

Копиране на формула в диапазон от клетки

Копирането на клетките е много удобно, защото можете да напишете една формула и да я копирате в голяма област и референцията се актуализира. Така се избягва необходимостта от редактиране на всяка клетка, за да се гарантира, че тя сочи към правилното място.

Под "диапазон" имаме предвид повече от една клетка. Например (С1: С10) означава всички клетки от клетка С1 до клетка С10. Така че това е колона от клетки. Друг пример (A1: AZ1) е най-горния ред от колона А до колона AZ.

Ако диапазон пресече пет колони и десет реда, тогава ще посочите обхвата, като напишете горната лява клетка и долната дясна, например A1: E10. Това е квадратна площ, която пресича редове и колони, а не само част от колона или част от ред.

Ето един пример, който илюстрира как да копирате една клетка на няколко места. Да предположим, че искаме да показваме прогнозните разходи за месеца в електронна таблица, за да можем да направим бюджет. Ние създаваме електронна таблица по следния начин:

Сега копирайте формулата в клетка C3 (= B3 + C2) към останалата част от колоната, за да дадете текущ баланс на нашия бюджет. Excel актуализира референтната клетка, докато го копирате. Резултатът е показан по-долу:

Както виждате, всяка нова клетка се актуализира относителен към новото местоположение, така че клетката C4 актуализира своята формула до = B4 + C3:

Cell C5 се актуализира с = B5 + C4 и т.н .:

Абсолютни позовавания

Абсолютното позоваване не се променя при преместване или копиране на клетка. Използваме знака $, за да направим абсолютна препратка - да си припомним това, да мислим за знак за долар като котва.

Въведете например формулата = $ A $ 1 във всяка клетка. $ Пред колоната А означава, че не променяте колоната, а $ пред реда 1 означава, че не променяте колоната, когато копирате или премествате клетката в друга клетка..

Както можете да видите в примера по-долу, в клетка B1 имаме относителна референция = A1. Когато копираме B1 в четирите клетки под него, относителната референтна стойност = A1 се променя в клетката вляво, така че B2 става A2, B3 Тези клетки очевидно нямат въведена стойност, така че изходът е нула.

Въпреки това, ако използваме = $ A1 $ 1, като например в C1 и го копираме в четирите клетки под него, референцията е абсолютна, така че никога не се променя и изходът винаги е равен на стойността в клетка A1.

Да предположим, че следите интереса си, като например в примера по-долу. Формулата в C4 = B4 * B1 е "лихвеният процент" * "баланс" = "интерес за година."

Сега сте променили бюджета си и сте спестили още 2 000 долара за закупуване на взаимен фонд. Да предположим, че е фонд с фиксиран лихвен процент и той плаща същия лихвен процент. Въведете новия профил и баланса в електронната таблица и след това копирайте формулата = B4 * B1 от клетка C4 в клетка C5.

Новият бюджет изглежда така:

Новият взаимен фонд печели $ 0 в лихва годишно, което не може да е правилно, тъй като лихвеният процент е очевидно 5%.

Excel подчертава клетките, към които се позовава формула. Можете да видите по-горе, че препратката към лихвения процент (В1) се премества в празната клетка В2. Трябваше да направим препратката към абсолютната стойност на B1, като напишем $ B $ 1, използвайки знака за долара, за да закрепим позоваването на ред и колона.

Презапишете първото изчисление в C4, за да прочетете = B4 * $ B $ 1, както е показано по-долу:

След това копирайте тази формула от C4 на C5. Електронната таблица сега изглежда така:

Тъй като копирахме клетката с една формула надолу, т.е. увеличихме реда с един, новата формула е = B5 * $ B $ 1. Лихвеният процент на взаимните фондове се изчислява правилно сега, тъй като лихвеният процент е прикрепен към клетка B1.

Това е добър пример за това, когато можете да използвате „име“, за да се позовете на клетка. Името е абсолютна препратка. Например, за да зададете името "лихвен процент" на клетка B1, щракнете с десния бутон върху клетката и след това изберете "define name."

Имена могат да се отнасят за една клетка или диапазон и можете да използвате име във формула, например = interest_rate * 8 е същото като писането = $ B $ 1 * 8.

Смесени препратки

Смесени препратки са кога един редът или колоната е закотвена.

Да предположим например, че сте земеделски производител, който прави бюджет. Вие също притежавате магазин за храни и продавате семена. Ще засадят царевица, соя и люцерна. Електронната таблица по-долу показва цената на акър. "Цената на акър" = "цена на паунд" * "паунда на семена на акър" - това ще ви струва да засадите акър.

Въведете стойността на акър като = $ B2 * C2 в клетка D2. Вие казвате, че искате да закрепите цената за колона на паунд. След това копирайте тази формула в другите редове в същата колона:

Сега искате да знаете стойността на инвентара си на семена. Нуждаете се от цената на килограм и броя на паунда в инвентара, за да знаете стойността на инвентара.

Добавяме две колони: „паунд семена в инвентара“ и след това „стойност на инвентара“. 4, но колоната остава фиксирана, защото $ я закрепва към „B.“

Това е смесена референция, защото колоната е абсолютна и редът е относителен.

Циклични препратки

Кръговата справка е, когато формулата се отнася за себе си.

Например, не можете да напишете c3 = c3 + 1. Този вид изчисление се нарича „итерация“, което означава, че се повтаря. Excel не поддържа итерация, защото изчислява всичко само веднъж.

Ако опитате да направите това, като напишете SUM (B1: B5) в клетка B5:

Появява се предупредителен екран:

Excel само ви казва, че имате кръгова препратка в долната част на екрана, така че може да не я забележите. Ако имате кръгова справка и затворите електронна таблица и я отворите отново, Excel ще ви покаже в изскачащ прозорец, че имате кръгова препратка.

Ако имате кръгова справка, всеки път, когато отворите електронната таблица, Excel ще ви каже с изскачащия прозорец, че имате кръгова препратка.

Позовавания на други работни листове

"Работна книга" е сбор от "работни листове". Просто казано, това означава, че можете да имате няколко електронни таблици (работни листове) в един и същ файл на Excel (работна книга). Както можете да видите в примера по-долу, нашата примерна работна книга има много работни листове (в червено).

Работните листове по подразбиране се наричат ​​Sheet1, Sheet2 и т.н. Създавате нов, като щракнете върху “+” в долната част на екрана на Excel.

Можете да промените името на работния лист на нещо полезно като „заем“ или „бюджет“, като щракнете с десен бутон върху раздела на работния лист в долната част на екрана на програмата на Excel, изберете преименуване и въведете ново име.

Или просто можете да кликнете два пъти върху раздела и да го преименувате.

Синтаксисът за позоваване на работен лист е = работен лист! Клетка. Можете да използвате този вид справка, когато една и съща стойност се използва в два работни листа, като примери за това могат да бъдат:

• Днешната дата
• Курс за конвертиране на валута от долари към евро
• Всичко, което е от значение за всички работни листове в работната книга

По-долу е даден пример за „интерес“ на листа, отнасящ се до работен лист „заем“, клетка В1.

Ако погледнем работния лист „заем“, можем да видим позоваването на сумата на кредита:

Следва…

Надяваме се, че сега имате твърдо разбиране за клетъчни препратки, включително относителни, абсолютни и смесени. Със сигурност има много.

Това е за днешния урок, в урок 4 ще обсъдим някои полезни функции, които бихте искали да знаете за ежедневна употреба на Excel.