Функция INDEX в Microsoft Excel

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

Използване на функцията INDEX

Операторът INDEX се отнася до група от функции от категорията "Референции и масиви" . Има две разновидности: за масиви и за справки.

Вариантът за масива има следния синтаксис:

=ИНДЕКС(массив;номер_строки;номер_столбца)

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

Синтаксисът за референтния вариант изглежда така:

=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])

Можете да използвате само един от два аргумента тук: "Номер на ред" или "Номер на колона" . Аргументът "Номер на района" по принцип е незадължителен и се прилага само когато са включени няколко диапазона в операцията.

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

Метод 1: използване на INDEX оператора за масиви

На първо място, нека да разгледаме най-простия пример на алгоритъм за използване на INDEX оператора за масиви.

Имаме таблица за заплатите. В първата колона се показват имената на служителите, във втората - датата на плащане, а в третата - размерът на приходите. Трябва да изведем името на служителя на третия ред.

1. Изберете клетката, в която ще се покаже резултатът от обработката. Кликваме върху иконата "Вмъкване на функция" , която се поставя непосредствено вляво от формулата.
2. Има процедура за активиране на съветника за функции . В категорията "Референции и масиви" на този инструмент или "Пълен азбучен списък" търсим името "INDEX" . След като намерите този оператор, изберете го и кликнете върху бутона "OK" , който се намира в долната част на прозореца.
3. Отваря се малък прозорец, в който избирате един от типовете функции: "Array" или "Link" . Нуждаем се от опцията "Array" . Той се намира първо и е маркиран по подразбиране. Така че просто трябва да кликнете върху бутона "OK" .
4. Отваря прозореца с аргументи на функцията INDEX . Както бе споменато по-горе, той има три аргумента и съответно три полета за попълване.

   В полето "Array" трябва да посочите адреса на обработения обхват от данни. Тя може да бъде изкормена на ръка. Но за да опростим задачата, ние се придържаме по различен начин. Поставете курсора в съответното поле и след това кръговете целия диапазон от таблични данни в листа. След това адреса на диапазона веднага ще се появи на полето.

   В полето "Номер на ред" поставяме числото "3" , защото при условие, че трябва да определим третото име в списъка. В полето "Column Number" задайте номер "1" , тъй като колоната с имената е първата в избрания диапазон.

   След като направите всички настройки, кликнете върху бутона "OK" .

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

Дезамонтирахме използването на функцията INDEX в многомерен масив (няколко колони и редове). Ако диапазонът е едноизмерен, попълването на данните в прозореца на аргументите би било още по-лесно. В полето "Array" същият метод, както по-горе, посочваме адреса му. В този случай диапазонът от данни се състои само от стойности в една колона "Име" . В полето "Номер на линия" посочваме стойността "3" , защото трябва да знаем данните от третия ред. Полето "Номер на колоната" може изобщо да остане празно, тъй като имаме едномерен диапазон, в който се използва само една колона. Кликнете върху бутона "OK" .

Резултатът ще бъде точно същият като по-горе.

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

Урокът: Съветник за функции в Excel

Метод 2: прилагане във връзка с оператора MATCH

На практика функцията INDEX често се използва във връзка с аргумента MATCH . Bundle INDEX - MATCHING е мощен инструмент при работа в Excel, който е по-гъвкав от неговата функционалност от най-близкия аналог - операторът на VPR .

Основната функция на функцията MATCH е да посочи номера по ред на определена стойност в избрания диапазон.

Синтаксисът на израза POSITION е:

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

• Желаната стойност е стойността, чиято позиция в обхвата, който търсим;
• Сканираният масив е диапазонът, в който се намира тази стойност;
• Типът на картографиране е незадължителен параметър, който определя дали да се намерят точно или приблизително. Ще търсим точни стойности, така че този аргумент не се използва.

С този инструмент можете автоматично да въведете номерата "Номер на линия" и "Номер на колоната" във функцията INDEX .

Да видим как това може да се направи с конкретен пример. Работим със същата таблица, както е описано по-горе. Отделно, имаме две допълнителни полета - "Име" и "Сума". Необходимо е да се направи така, че при въвеждане на името на работника сумата от спечелените от него пари се показва автоматично. Да видим как това може да се приложи на практика, като се използват функциите на INDEX и MATCH .

1. На първо място откриваме какъв вид заплата получава един служител на ДП "Парфеньов". Въвеждаме неговото име в съответната област.
2. Изберете клетката в полето "Сума" , в която ще се покаже окончателният резултат. Стартираме прозореца с аргументи на функцията INDEX за масиви.

   В полето "Array" въвеждаме координатите на колоната, в която се намират сумите от заплатите на служителите.

   Полето "Номер на колоната" остава празно, защото използваме едномерния диапазон като пример.

   Но в полето "Номер на линия" просто трябва да напишем функцията MATCH . За неговото записване се придържаме към описания по-горе синтаксис. Веднага в полето въведете името на оператора "SEARCH" без кавички. След това веднага отворете скобите и задайте координатите на желаната стойност. Това са координатите на клетката, в която имаме отделно записано името на работника Parfenov. Поставете точка и запетая и задайте координатите на диапазона, който гледате. В нашия случай това е адресът на колоната с имената на служителите. След това затваряме скобата.

   След като въведете всички стойности, кликнете върху бутона "OK" .

3. Резултатът от размера на приходите Parfenov DF след обработката се показва в полето "Сума".
4. Сега, ако в полето "Име" променим съдържанието от "Parfenov DF" , например "Попова MD" , тогава стойността на заплатите в полето "Sum" ще се промени автоматично.

Метод 3: Обработване на множество таблици

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

Имаме три маси. Всяка таблица показва заплатите на служителите за един месец. Нашата задача е да установим заплатата (третата колона) на втория служител (втори ред) за третия месец (третата част).

1. Изберете клетката, в която ще се изведе резултата, и отворете функционалността на помощника по обичайния начин, но когато изберете типа оператор, изберете референтния изглед. Нуждаем се от това, защото този тип поддържа работата с аргумента "Номер на района" .
2. Отваря прозореца с аргументи. В полето "Линк" трябва да посочим адресите на трите диапазона. За да направите това, поставете курсора в полето и изберете първия обхват с натиснат левия бутон на мишката. След това поставяме точка и запетая. Това е много важно, тъй като ако веднага преминете към избора на следващия масив, неговият адрес просто ще замести координатите на предишния. Така че, след въвеждането на точка и запетая, ние избираме следния диапазон. След това отново поставете точка и запетая и изберете последния масив. Целият израз, който е в полето "Референтно", е в скоби.

   В полето "Номер на линия" показваме числото "2" , тъй като търсим второто фамилно име в списъка.

   В полето "Номер на колоната" показваме цифрата "3" , тъй като колоната за заплатата е третата колона във всяка таблица.

   В полето "Номер на района" поставяме числото "3" , тъй като трябва да намерим данните в третата таблица, която съдържа информация за заплатите за третия месец.

   След като въведете всички данни, кликнете върху бутона "OK" .

3. След това резултатите от изчислението се показват в предварително избраната клетка. Показва размера на заплатите на втория служител (Safronov VM) за третия месец.

Метод 4: Изчисляване на сумата

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

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

=СУММ(адрес_массива)

В нашия конкретен случай размерът на доходите на всички служители на месец може да се изчисли, като се използва следната формула:

=СУММ(C4:C9)

Но можете да го промените малко, като използвате функцията INDEX . Тогава ще изглежда така:

=СУММ(C4:ИНДЕКС(C4:C9;6))

В този случай в координатите на началото на масива започва клетката, с която започва. Но в координатите на посочването на края на масива се използва операторът INDEX . В този случай първият аргумент на INDEX оператора сочи към диапазона, а вторият - към последния - шестият.

Урокът: Полезни функции на Excel

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

• ← Функцията MATCH в Microsoft Excel
• Изтегляне на данни от работната книга на Excel в програмата 1C →