Функция на INDIRECT в Microsoft Excel
Една от вградените функции на програмата Excel е INDIVIDUAL . Неговата задача е да се върне към елемента на листа, където се намира съдържанието на клетката, което е означено като аргумент на връзката в текстов формат.
Изглежда, че няма нищо особено, тъй като е възможно да се показва съдържанието на една клетка в друга по по-прости начини. Но, както се оказа, някои нюанси се свързват с използването на този оператор, което го прави уникален. В някои случаи тази формула е способна да реши такива проблеми, които другите методи просто не могат да се справят или ще бъде много по-трудно да се направи. Да разгледаме по-подробно какъв е операторът на DVSSYL и как може да се използва на практика.
съдържание
Прилагане на формулата
Самото име на този оператор DVSSYL означава "двойна справка" . Всъщност това показва неговата цел - да извежда данни чрез връзката от една клетка в друга. И за разлика от повечето други функции, които работят с връзки, тя трябва да бъде уточнена в текстов формат, т.е. тя се разпределя от двете страни с кавички.
Този оператор принадлежи към категорията функции "Референции и масиви" и има следния синтаксис:
=ДВССЫЛ(ссылка_на_ячейку;[a1])
По този начин формулата има само два аргумента.
Аргументът "Връзка към клетка" се представя като връзка към листовия елемент, съдържащите се в него данни трябва да се показват. В този случай посочената връзка трябва да има текстова форма, т.е. да бъде "обвита" в кавички.
Аргументът "А1" не е задължителен и в по-голямата част от случаите не е необходимо да се уточнява изобщо. Тя може да има две значения: "TRUE" и "FALSE" . В първия случай операторът определя връзки в стила на "A1" , а именно този стил е включен в Excel по подразбиране. Ако стойността на аргумента не е посочена изобщо, тогава то ще се счита за точно "TRUE" . Във втория случай референциите се дефинират в стила на "R1C1" . Този стил на връзките трябва да бъде специално включен в настройките на Excel.
За да го кажем просто, DVSSYL е нещо като еквивалентно свързване на една клетка с друга след знака "равен". Например, в повечето случаи изразът
=ДВССЫЛ("A1")
ще бъде еквивалентно на израза
=A1
Но за разлика от израза "= A1", операторът не е определен за конкретна клетка, а за координатите на елемента на листа.
Нека помислим какво означава това в най-простия пример. В клетките B8 и B9, съответно , се записва формулата, записана с "=" и функцията е ALTERNATE . И двете формули се отнасят до елемент B4 и показват съдържанието му на лист. Естествено, това съдържание е същото.
Добавяме още един празен елемент към таблицата. Както можете да видите, линиите се изместиха. Във формулата с "равно" стойността остава същата, тъй като се отнася до крайната клетка, дори ако нейните координати са се променили, но изходът на данните от оператора се е променил. Това се дължи на факта, че не се отнася за листовия елемент, а за координатите. След добавяне на линия адресът B4 съдържа друг елемент от листа. Съдържанието му сега е формула и се показва на листа.
Този оператор може да изведе в друга клетка не само числа, но и текст, резултат от изчисляване на формули и други стойности, които се намират в избрания елемент на листа. Но на практика тази функция рядко се използва самостоятелно, но много по-често тя е неразделна част от сложните формули.
Следва да се отбележи, че операторът е приложим към връзки към други листове и дори към съдържанието на други книги на Excel, но в този случай те трябва да бъдат стартирани.
Сега нека да разгледаме конкретни примери за приложението на оператора.
Пример 1: Единично приложение на оператор
Първо, нека разгледаме най-простия пример, в който функцията INDEX изпълнява сама, за да разберете същността на нейната работа.
Имаме произволна таблица. Задачата е да се покажат данните от първата клетка от първата колона в първия елемент на отделна колона, като се използва формулата, която се изследва.
- Изберете първия празен елемент на колоната, където планираме да вмъкнем формулата. Кликваме върху иконата "Вмъкване на функция" .
- Отваря се прозорецът " Съветник за опциите". Преминаваме в категорията "Референции и масиви" . От списъка изберете "ALBUM" . Кликваме върху бутона "OK" .
- Отваря се прозорецът за аргументите на конкретния оператор. В полето "Връзка към клетка" трябва да посочите адреса на този елемент в листа, чието съдържание ще покажем. Разбира се, той може да бъде въведен ръчно, но ще бъде много по-практично и удобно да направите следното. Задайте курсора в полето и след това кликнете с левия бутон на мишката върху съответния елемент на листа. Както можете да видите, веднага след това адреса му бе показан на полето. След това от двете страни изберете връзката с кавички. Както си спомняме, това е функция на работата с аргумента на тази формула.
В полето "А1" , тъй като работим в обичайния тип координати, можем да поставим стойността "TRUE" и можете да го оставите празно изобщо, което ще направим. Това ще бъдат еквивалентни действия.
След това кликнете върху бутона "OK" .
- Както можете да видите, съдържанието на първата клетка от първата колона на таблицата се показва в този елемент на листа, в който се намира формулата " INDIRECT" .
- Ако искаме да приложим тази функция в клетките, които са разположени по-долу, тогава в този случай ще трябва да въведем формулата във всеки елемент отделно. Ако се опитаме да го копираме с помощта на маркер за запълване или друг метод за копиране, тогава всички елементи на колоната ще показват същото име. Въпросът е, че, както си спомняме, връзката действа като аргумент в текстова форма (увит в кавички) и следователно не може да бъде относим.
Урокът: Съветник за функции в Excel
Пример 2: използване на оператора в сложната формула
И сега нека разгледаме един пример за много по-често използване на оператора DVSSYL , когато той е неразделна част от сложната формула.
Имаме месечна таблица на доходите на предприятието. Трябва да изчислим размера на дохода за определен период от време, например март-май или юни-ноември. Разбира се, можете да използвате формулата за сумиране, за да направите това, но в този случай, ако трябва да изчислите общия резултат за всеки период, винаги ще трябва да променим тази формула. Но с помощта на функцията EVALUATE ще бъде възможно да направите промяна в сумирания обхват, просто в отделни клетки, показващи съответния месец. Ще се опитаме да използваме този вариант на практика за да изчислим сумата за периода от март до май. В този случай ще се използва формула с комбинация от SUM и SAS оператори .
- На първо място, в отделни елементи на листа се въвеждат имената на месеците от началото и края на периода, за който ще бъде направено изчислението, съответно "март" и "май" .
- Сега задайте име на всички клетки в колоната "Приходи" , което ще бъде подобно на името на съответния месец. Това означава, че първият елемент в колоната "Приходи" , който съдържа размера на приходите, трябва да се нарече "януари" , вторият - "февруари" и т.н.
За да наименувате първия елемент на колоната, го изберете и щракнете с десния бутон на мишката. Отваря контекстното меню. Изберете елемента "Задаване на име ..." .
- Появява се прозорецът за създаване на име. В полето "Име" въвеждаме името "Януари" . Не е нужно да правите повече промени в прозореца, въпреки че само за всеки случай можете да проверите дали координатите в полето "Range" съответстват на адреса на клетката, съдържащ размера на приходите за януари. След това кликнете върху бутона "OK" .
- Както можете да видите, сега, когато изберете този елемент в прозореца с имена, той не показва адреса му, а името, което му дадем. Извършваме подобна операция с всички останали елементи на колоната "Приходи" , като ги разпределяме последователно на имената "февруари" , "март" , "април" и т.н. до декември включително.
- Изберете клетката, в която ще се покаже сумата от стойностите на зададения интервал, и я изберете. След това кликнете върху иконата "Вмъкване на функция" . Той се намира отляво на формулата и отдясно на полето, където се показва името на клетките.
- В активирания прозорец на Wizard на функциите се преместваме в категорията "Математически" . Там избираме името "SUMM" . Кликваме върху бутона "OK" .
- След изпълнението на това действие се стартира прозорецът с аргументи на оператора SUMM , чиято единствена задача е сумирането на посочените стойности. Синтаксисът на тази функция е много прост:
=СУММ(число1;число2;…)Като цяло, броят на аргументите може да достигне 255 . Но всички тези аргументи са хомогенни. Те представляват броя или координатите на клетката, в която се съдържа този номер. Те могат също да действат като вградена формула, която изчислява желания брой или посочва адреса на листовия елемент, където е поставен. В този смисъл на вградената функция ще използваме оператора DVSSYL в този случай.
Задайте курсора в полето "Номер 1" . След това кликнете върху иконата под формата на обърнат триъгълник вдясно от полето за диапазона. Показва се списъкът с последните използвани функции. Ако между тях има име "DVSSYL" , веднага кликнете върху него, за да отидете в прозореца с аргументи на тази функция. Но може би е добре да не го намерите в този списък. В този случай кликнете върху името "Други функции ..." в долната част на списъка.
- Прозорецът на " Магьосника" от функции, които вече ни е известен, започва. Преминаваме към секцията "Референции и масиви" и избираме името на оператора DVSSYL . След това действие кликнете върху бутона "OK" в долната част на прозореца.
- Показва се прозорецът с аргументи на оператора INDEX . В полето "Връзка към клетката" посочваме адреса на листовия елемент, който съдържа името на началния месец от диапазона на сумата, предназначена за изчисление. Имайте предвид, че само в този случай не е необходимо да се свързвате с кавички, тъй като в този случай адресът няма да бъде координатите на клетката, а съдържанието й, което вече има текстов формат (думата "март" ). Полето "А1" остава празно, тъй като използваме стандартния тип координатно обозначение.
След като адресът се появи в полето, не бързайте да натискате бутона "OK" , тъй като това е вградена функция и действията с нея са различни от обичайния алгоритъм. Кликваме върху името "SUMM" в лентата за формули.
- След това се връщаме в прозореца SUMM аргументи. Както можете да видите, полето "Номер 1" вече е показало оператора DVSYL със съдържанието му. Задайте курсора в едно и също поле непосредствено след последния знак в записа. Поставете двоеточие ( :) . Този символ показва знака за адреса на диапазона клетки. След това, без да премахвате курсора от полето, отново кликнете върху иконата под формата на триъгълник, за да изберете функциите. Този път в списъка с наскоро използваните оператори името "DVSSYL" трябва да бъде точно налице, тъй като наскоро използвахме тази функция. Кликваме върху името.
- Прозорецът на аргументите на INDEX оператора отново се отваря. Въвеждаме адреса на елемента в листа в полето "Клетка за справка" , където се намира името на месеца, което завършва периода на изчисление. Отново координатите трябва да бъдат вписани без кавичките. Полето "А1" отново е празно. След това кликнете върху бутона "OK" .
- Както можете да видите, след тези действия програмата изчислява и показва резултата от добавянето на дохода на предприятието за конкретния период (март - май) към предварително избрания елемент на листа, в който се намира самата формула.
- Ако променим клетките, в които са въведени имената на месеците от началото и края на периода на фактуриране, например за "юни" и "ноември" , тогава резултатът ще се промени съответно. Ще бъде добавен размерът на приходите за определения период от време.
Урокът: Как да изчислите сумата в Excel
Както виждате, независимо от факта, че функцията не е сред най-популярните сред потребителите, тя все пак помага за решаването на задачи с различни сложности в Excel много по-лесно, отколкото би могло да се направи с помощта на други инструменти. Най-вече този оператор е полезен в сложни формули, в които той е неразделна част от израза. Но все пак трябва да се отбележи, че всички възможности на оператора DVSSYL са доста трудни за разбиране. Това обяснява ниската популярност на тази полезна функция за потребителите.