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

2024 Автор: Geoffrey Carr | [email protected]. Последно модифициран: 2023-12-17 11:00

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

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

Защо се нуждаете от формули и функции?
Дефиниране и създаване на формула
Относителна и абсолютна клетка и форматиране
Полезни функции, които трябва да знаете
Търсене, графики, статистически данни и таблици за завъртане

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

Какво представлява референтната клетка?

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

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

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

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

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

Видове референтни клетки

Има три вида референтни клетки.

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

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

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

Относителни референции

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

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

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

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

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

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

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

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

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

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

Сега имаме:

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

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

Когато копирахме клетките от колона С в колона Б, тя актуализира еталонната колона отляво = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

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

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

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

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

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

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

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

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

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

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

Абсолютни референции

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

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

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

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

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

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

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

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

Excel подчертава клетките, за които се отнася формулата. Можете да видите по-горе, че препратката към лихвения процент (B1) се премества в празната клетка B2. Трябваше да направим абсолютно абсолютно абсолютно позоваване на 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. Казвате, че искате да закрепите колоната за цената на паунд. След това копирайте формулата до останалите редове в същата колона:

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

Добавяме две колони: "килограм семена в инвентара" и след това "стойност на инвентара". Сега копирайте клетката D2 до F4 и отбележете, че референтната стойност на реда в първата част на оригиналната формула ($ B2) се актуализира до ред 4, но колоната остава фиксирана, защото $ я закотви към "Б".

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

Циркулярни препратки

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

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

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

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

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

Препратки към други работни листове

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

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

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

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

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