Excel

Методи за абсолютно адресиране в Microsoft Excel

Както знаете, в таблиците в Excel има два вида адреси: относителни и абсолютни. В първия случай връзката се променя в посоката на копиране от относителната сума на смяната, докато във втория случай тя се фиксира и остава непроменена по време на копиране. Но по подразбиране всички адреси в Excel са абсолютни. В същото време, доста често се налага да се използва абсолютно (фиксирано) адресиране. Нека да разберем по какъв начин може да бъде направено.

Прилагане на абсолютно адресиране

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

В Excel има два начина за задаване на фиксирано адресиране: чрез формиране на абсолютна референция и използване на функцията INDIRECT. Нека разгледаме подробно всеки от тези методи.

Метод 1: абсолютна референция

Разбира се, най-известният и често използван начин за създаване на абсолютно адресиране е да се използват абсолютни референции. Абсолютните връзки имат различия не само функционални, но и синтактични. Относителният адрес има този синтаксис:

=A1

На фиксиран адрес знакът за долар е зададен преди стойността на координатите:

=$A$1

Абсолютна справка в Microsoft Excel

Знакът за долар може да бъде въведен ръчно. За да направите това, трябва да поставите курсора преди първата стойност на координатите на адреса (хоризонтално), която е в клетката или във формулата. Освен това в клавиатурата на английски клавиатурата кликнете върху бутона "4" в горната част (с натиснат клавиш "Shift" ). Именно там се намира символът на долара. Тогава трябва да направите същата процедура с вертикалните координати.

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

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

Вземете таблицата, която изчислява заплатите на служителите. Изчислението се извършва чрез умножаване на стойността на личната им заплата с фиксирано съотношение, което е еднакво за всички служители. Самият коефициент се намира в отделна листова клетка. Ние сме изправени пред задачата да изчислим заплатите на всички работници по най-бързия възможен начин.

Таблица на изчисленията на заплатите на служителите в Microsoft Excel

  1. Така че в първата клетка на колоната "Заплати" въвеждаме формулата за умножаване на коефициента на съответния служител. В нашия случай тази формула има следната форма:

    =C4*G3

    2. Формулата за изчисляване на заплатите в Microsoft Excel

  2. За да изчислите крайния резултат, кликнете върху клавиша Enter на клавиатурата. Резултатът се показва в клетката, съдържаща формулата.

    3. Резултат от изчисляването на заплатата за първия служител в Microsoft Excel

  3. Ние изчислихме заплатата на първия служител. Сега трябва да направим това за всички останали редове. Разбира се, операцията може да бъде написана ръчно на всяка клетка от колоната "Заплати" , като въведете същата формула с корекцията за отместването, но ние имаме задачата да извършим изчисления колкото е възможно по-бързо и ръчното въвеждане ще отнеме много време. И защо губите усилия при ръчно въвеждане, ако формулата просто може да бъде копирана в други клетки?

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

    4. Филтър в Microsoft Excel

  4. Но, както виждаме, вместо правилно изчисляване на заплатите за други служители, ние получихме една нула.

    5. Нули за изчисляване на заплатите в Microsoft Excel

  5. Ние гледаме, в каква причина за такъв резултат. За да направите това, изберете втората клетка в графата "Заплати" . Изразът, съответстващ на клетката, се показва в лентата за формули. Както можете да видите, първият множител ( С5 ) съответства на процента на служителя, чиято заплата очакваме. Изместването на координатите в сравнение с предишната клетка се дължи на свойството на относителността. Но в този конкретен случай това е, от което се нуждаем. Благодарение на това първият мултипликатор беше нивото на конкретния служител, от който имахме нужда. Но координатната промяна също се случи с втория фактор. И сега неговият адрес не се отнася до коефициента ( 1,28 ), а до празната клетка по-долу.

    Копираната формула в Microsoft Excel

    Това е причината, поради която изчисляването на заплатите на следващите служители от списъка се оказа невярно.

  6. За да коригираме ситуацията, трябва да променим адреса на втория фактор от относителната към фиксираната. За да направите това, върнете се към първата клетка в колоната "Заплати" , като я изберете. След това се преместваме в лентата за формули, където се показва израза, който се нуждаем. Изберете втория коефициент ( G3 ) с курсора и натиснете функционалния клавиш на клавиатурата.

    7. Преобразуване на втората мултипликационна връзка от относителна към абсолютна в Microsoft Excel

  7. Както можете да видите, около координатите на втория множител се появи знак за долар и това, както си спомняме, е атрибут на абсолютно адресиране. За да покажете резултата на екрана, натиснете клавиша Enter .

    8. Вторият множител има абсолютно адресиране в Microsoft Excel

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

    9. Извикване на дръжката за запълване в Microsoft Excel

  9. Както виждаме, в този случай изчислението е извършено правилно и размерът на заплатите за всички служители на предприятието е изчислен правилно.

    10. Заплатата се изчислява правилно в Microsoft Excel

  10. Нека проверим как е била копирана формулата. За да направите това, изберете втория елемент на графата "Заплати" . Погледнете израза във формулата. Както можете да видите, координатите на първия фактор ( С5 ), който все още е относителен, са изместили една вертикална вертикална точка в сравнение с предишната клетка. Но вторият фактор ( $ G $ 3 ), чието адресиране, което определихме, остана непроменено.

Копираната формула в Microsoft Excel

В Excel се използва така нареченото смесено адресиране. В този случай колоната или редът се фиксират в адреса на елемента. Това се постига по такъв начин, че знакът за долар да се постави точно пред една от координатите на адреса. Ето един пример за типична смесена връзка:

=A$1

Смесена връзка в Microsoft Excel

Този адрес също се смята за смесен:

=$A1

Смесена връзка в Microsoft Excel

Тоест абсолютното адресиране в смесената връзка се използва само за една от координатните стойности на двете.

Нека видим как такава смесена връзка може да се приложи на практика с примера на една и съща таблица на заплатите за служителите на предприятието.

  1. Както можете да видите, по-рано направихме така, че всички координати на втория множител да имат абсолютно адресиране. Но нека да видим, дали в този случай е необходимо и двете стойности да бъдат фиксирани? Както можете да видите, при копиране се получава вертикално отместване и хоризонтално координатите остават непроменени. Ето защо е възможно да се приложи абсолютно адресиране само на координатите на низа и да се оставят координатите на колоните по подразбиране - относителни.

    Изберете първия елемент от графата "Заплати" и изпълнете горната манипулация в реда на формулата. Получаваме следната формула:

    =C4*G$3

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

    2. Абсолютното адресиране се отнася само за координатите на линии в Microsoft Excel

  2. След това, като използвате маркера за попълване, копирайте тази формула до областта от клетки, която е разположена по-долу. Както можете да видите, заплатата за всички служители е правилно изпълнена.

    3. Изчисляването на заплатите на служителите бе извършено правилно, използвайки смесена справка в Microsoft Excel

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

Копирана формула със смесена връзка в Microsoft Excel

Урокът: Абсолютни и относителни препратки в Excel

Метод 2: Функцията на Alpha

Вторият начин за организиране на абсолютно адресиране в електронна таблица в Excel е да използвате INDEX оператора. Посочената функция принадлежи към групата вградени оператори "Референции и масиви" . Неговата задача е да се направи препратка към определената клетка с изхода на резултата към този елемент на листа, в който се намира операторът. В този случай връзката е прикрепена към координатите дори по-силна, отколкото при използване на знака за долар. Поради това понякога е обичайно да се отнасят към връзки, използващи DVSSYL като " superabsolute ". Това изявление има следния синтаксис:

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

Функцията има два аргумента, първият от които е задължителен, а вторият - не.

Аргументът "Връзка към клетка" е връзка към елемент от лист Excel в текстова форма. Това означава, че това е обикновено позоваване, но е затворено в кавички. Това е, което прави възможно да се осигурят свойствата на абсолютно адресиране.

Аргументът "a1" е по избор и се използва в редки случаи. Неговата употреба е необходима, само ако потребителят избере алтернативна опция за адресиране, а не обичайната употреба на типовете координати "А1" (колоните имат азбучно обозначение и линиите са цифрови). Алтернативата е да се използва стила R1C1 , в който колоните, подобно на редовете, се обозначават с номера. Превключете към този режим на работа през прозореца на опциите на Excel. След това, като използвате оператора INDEX , използвайте стойността "FALSE" като аргумент "a1 " . Ако работите в нормален режим на показване на връзката, като повечето други потребители, можете да посочите стойността "TRUE" като аргумент "a1 " . Тази стойност обаче е подразбирана по подразбиране, така че е много по-лесно да не посочите аргумент "a1" в този случай.

Нека да разгледаме как абсолютното адресиране, организирано с помощта на функцията DVSSYL , ще работи върху примера на нашата таблица на заплатите.

  1. Избираме първия елемент от графата "Заплати" . Поставете знака "=" . Както помните, първият фактор в горната формула за изчисляване на заплатата трябва да бъде представен от относителен адрес. Затова просто кликнете върху клетката, съдържаща съответната стойност на заплатата ( C4 ). След като адреса му бе показан в елемента за изхода на резултата, кликнете върху бутона "умножаване" ( * ) на клавиатурата. Тогава трябва да продължим към използването на оператора INDIVIDUAL . Кликнете върху иконата "Вмъкване на функция" .

    2. Отидете в съветника за функции в Microsoft Excel

  2. В отворения прозорец на функциите на магьосниците отидете в категорията "Референции и масиви" . Сред представения списък от имена ние различаваме името "DVSSYL" . След това кликнете върху бутона "OK" .

    3. Преминаване към прозореца за аргументи на функцията INDIRECT в Microsoft Excel

  3. Активирането на прозореца на аргументите на оператора DVSSYL се извършва . Състои се от две полета, които съответстват на аргументите на тази функция.

    Поставете курсора в полето "Cell reference" . Просто кликнете върху този елемент на листа, в който се намира коефициентът за изчисляване на заплатата ( G3 ). Адресът ще се появи веднага в полето на прозореца с аргументи. Ако имахме работа с обикновена функция, тогава въвеждането на адреса може да се счита за пълно, но използваме функцията " ALGORITHM" . Както си спомняме, адресите в него трябва да имат формата на текст. Следователно ние увиваме координатите, които се намират в полето на прозореца, с кавички.

    Тъй като работим в стандартния режим на показване на координатите, полето "А1" остава празно. Кликваме върху бутона "OK" .

    4. Прозорецът на аргументите на функцията INDIRECT в Microsoft Excel

  4. Приложението изчислява и показва резултата в листов елемент, съдържащ формулата.

    5. Резултатът от изчисляването на формулата с функцията Alpha в Microsoft Excel

  5. Сега копираме тази формула във всички останали клетки в колоната "Заплати" , като използваме маркера за попълване, както направихме преди. Както можете да видите, всички резултати са изчислени правилно.

    6. Резултатът от цялата колона се изчислява с помощта на формулата с функцията Alpha в Microsoft Excel

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

Показване на копирана формула с функция INDIRECT в Microsoft Excel

Урокът: Оператор на ИНДУСТРИЯ в Excel

Абсолютно адресиране в таблици в Excel може да бъде осигурено по два начина: чрез използване на функцията INDEX и използване на абсолютни препратки. В този случай функцията осигурява по-твърдо свързване към адреса. Частично абсолютно адресиране може да се използва и при използване на смесени връзки.