Функция "INDEX" в Excel: описание, приложение и примери

В момента Excel е на второ място след Word. Тя ви позволява лесно да извършвате най-разнообразните икономически и статистически изчисления върху голямо количество данни. За тази цел той осигурява голям брой вградени функции, включително спомагателни. Някои от тях са способни да извършват действия, включително и над масиви от данни. Те включват функцията "INDEX". В Excel тя се използва както поотделно, така и с „MATCH“, което ще бъде разгледано по-долу.


Описание

Функцията "INDEX" в Excel връща стойността (позоваване на стойността) на съдържанието на клетката, определено от номерата на редовете и колоните на колоните от посочения диапазон. Синтаксисът му е прост и изглежда така: INDEX (масив, номер на ред, номер на колона). Тази функция може да работи и с една линия или с една колона. В този случай, след задаване на едномерен масив, се показва един номер. Той представлява номера на реда, ако масивът представлява колона, и обратно. Функцията "INDEX" в Excel понякога издава стойността "#REF!". Това най-често се случва, ако клетката, намираща се в пресечната точка на посочения ред и колоната е извън посочения диапазон.

Примери за приложение

Разгледайте няколко случая на използване на функцията INDEX на практика. Например, има масив, състоящ се от 4 колони и 4 реда (виж таблицата). Ако въведете в таблица клетки извън диапазона A1: E5, изразът «= INDEX (B2: E523)» (без кавички) и натиснете «Enter», след тована отговора ще бъде даден смисълът на "бегония".











А

C



D



Д



1

40)
DK



1



2



3



4



2



1



мак 70)


роза



жасмин



лайка



3



2



хризантема



нарцис


101) бегонии



хортензии



4



3


)
лале



виолетово







5



4






141) астра



божур



лилия



карамфил

За да разберете колко студенти от група 2 са получили "незадоволителен" рейтинг, трябва да въведете израза в съответната клетка: INDEX (C2: C5 1).





А



Б



В



D



Е



1







група 1



групи 2



група 3






2



"незадоволително"



5
215)

4



2



4



3



"удовлетворително"



12



10



) 25

"добър"







260)
7

(2 64) 265 8 266 267 268 269 8 270 270 277 272 273 10 274 ​​275 276 277 5 278 279 )

"excellent"



1



3



5



4

Функция MATCH в Excel

Двата примера по-горе няма да работят с големи масиви данни. Факт е, че използването на функцията "INDEX" в Excel включва въвеждане на номера на реда и колоната не е самата таблица, а масив от данни. Това е доста трудно да се направи, когато става въпрос за голям брой елементи. Още една функция excel може да помогне за решаването на проблема. Помислете за случая, когато масивът се състои от един ред.


C












)

D



1



продукти



2



Зеленчуци



Плодове



Подправки




)


3



домати



круши

















ябълки



чушки







5



) пипер



портокали



джинджифил








Канела


6



Моркови



426)




Диапазонът на стойностите в този случай е B3: B6. Изберете клетка на различен ред, например D1. Въвеждаме в него името на плода, позицията на която искаме да намерим, в този случай, "портокали". В клетката (E1), където искаме да запишем номера на съответния низ, въведете "= MATCH (D1; B3: B6; 0)" (виж таблицата). В резултат на това се появява номер 3. Това е номерът вобхвата на B3: B6 в израза "портокали".



C







)


D



E



1



463)










Портокали







2



Зеленчуци



плодове















3




домати











527)


4



картофи



ябълки










506)


5



моркови



портокали















6


582) пипер



банани










598)

Последното 0 означава, че трябва да намериш точното съвпадение със стойността на D1.

Как да намерим текстови стойности, които отговарят на всеки критерий

В показаната по-горе форма функцията "MATCH" връща една стойност (първо, тоест горната). Но какво да направя, ако има повторение в списъка. В този случай формулите на масивите помагат. За да ги използвате, трябва да изберете целия диапазон от данни и да използвате клавишната комбинация "Ctrl + Shift + Enter". Разглеждането му обаче не е предмет на тази статия.

INDEX и MATCH функция в Excel: примери

Представете си, че трябва да изберете само определени данни от доста голям масив. Обмислете за простота случая с малък брой елементи. Например, имате доклад за товауспеха на няколко студентски групи и тяхната оценка. Например, да предположим, че искате броят на учениците, които са получили оценка "незадоволително" в H2 клетката.


C




B








G


D








H



J



1



N



gr. 1



гр. 2



гр. 3



гр.



гр. 2



гр.

2



"незадоволително"



5


3



1



2






713)
udd











3



ud



14



10



(

















4





8



9



10



8

789)













4

























)














, За да разберете какво трябва да въведете във H2, първо нека погледнем най-простия израз, който може да се използва за тази цел. По-специално, желаната стойност може да бъде получена чрез записване в клетката "= INDEX (A2: E5; 1; 2)". тукизползвахме опция от предишни примери, когато номерът на реда и номера на колоната бяха изчислени ръчно. Нашата цел обаче е да автоматизираме този процес. За да направите това, вместо двете и единиците, които сочат към желания ред и колона, в масива, напишете съответните функции "MATCH", издаващи тези числа. Моля, обърнете внимание, че търсим израза "ud", разположен в клетката G2 и "gr." 2 "от H2. В допълнение, ние се нуждаем от точни съвпадения, така че последният и третият аргумент в двата случая е 0. След това вместо 1 във формулата INDEX (A2: E5; 1; 2) трябва да напишете: MATCH (G2; A2: A5; 0) и вместо 2 - MATCH (Н2; А2: Е2; 0). След заместването имаме: INDEX (A2: E5; MATCH (G2; А2: А5; 0); МАТЧ (H2; А2: Е2; 0)). В резултат на това с натискане на "Enter" имаме "10" в тази клетка.

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

Както знаете, функцията "INDEX" в Excel може да бъде "удължена" до определен диапазон. В примера по-горе, това са всички 4 клетки с H2: J3. В тази връзка е необходимо да разберете как да го направите така, че "издърпвайки" тази формула надясно и надолу, да получите правилните стойности.
Основната трудност е, че масивът А2: Е5 има относителен адрес. За да поправите това, трябва да го конвертирате в абсолютен. За тази цел масивът се записва като $ A $ 2: $ E $ 5. Същото трябва да се направи и за двете вградени функции, т.е. те трябва да изглеждат като MATCH ($ G $ 2; $ A $ 2: $ A $ 5; 0) и MATCH ($ H $ 2; $ 2: $ E2; 0). Окончателната форма на формулата е: INDEX ($ A $ 2: $ E $ 5; MATCH ($ G $ 2; $ A $ 2: A $ 5; 0); MATCH ($ H $ 2; $ A $ 2: $ E $ 2; 0)). В резултат на това ще имаме таблица, изобразена по-долу





A



B



862) C



D








(




898) гр. 1



гр. 2



гр. 3



гр. 4 911 912 913 914 915 916 917 918 919 920 921 922 2



гр.



5






)
3



1 947 948 949 950 2 951 952 953 954 955 956 957)
"ud"



10



12



3








14



10




"excellent"















"добри"







8



10



8




)

























отлично



5



3


















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

Свързани публикации