На даний момент програма Excel за своєю популярністю поступається тільки Word. Вона дозволяє з легкістю здійснювати найрізноманітніші економіко-статистичні розрахунки над великою кількістю даних. Для цієї мети в ній передбачено велику кількість вбудованих функцій, у тому числі допоміжних. Деякі з них здатні здійснювати дії, у тому числі над масивами даних. До них відноситься і функція "ІНДЕКС". В Excel вона використовується як окремо, так і з "MATCH", про яку буде розказано нижче.
Опис
Функція "ІНДЕКС" в Excel повертає значення (посилання на значення) вміст клітинки, заданої номерами рядка і стовпця таблиці або пойменованого діапазону. Її синтаксис нескладний і виглядає наступним чином: ІНДЕКС (масив, № рядка, № стовпця). Ця функція може працювати також з єдиною рядком або з єдиним стовпцем. У такому випадку після вказівки одновимірного масиву виставляється одне число. Воно позначає номер рядка, якщо масив являє собою стовпець, і навпаки. Функція "ІНДЕКС" в Excel іноді видає значення «#REF!». Найчастіше це відбувається, якщо комірка, розташована на перетині зазначених рядка і стовпця знаходиться за межами вказаного діапазону.
Приклади застосування
Розглянемо кілька випадків використання функції "ІНДЕКС" на практиці. Наприклад, є масив, що складається з 4 колонок і 4 рядків (див. таблицю). Якщо ввести в одну з комірок таблиці розташоване поза діапазону А1:Е5 вираз «=ІНДЕКС (В2:Е523)» (без лапок) і натиснути на «Enter», то відповідь буде видано значення «бегонія».
A
B
C
D
E
1
N/N
1
2
3
4
2
1
мак
роза
жасмин
ромашка
3
2
хризантема
нарцис
бегонія
гортензія
4
3
тюльпан
фіалка
пролісок
гладіолус
5
4
астра
півонія
лілія
гвоздика
Якщо потрібно дізнатися, скільки учнів Групи 2 отримали оцінку «незадовільно», то у відповідну комірку потрібно ввести вираз: ІНДЕКС (С2:С5 1).
A
B
C
D
E
1
N/N
Група 1
Груп 2
Група 3
Група 4
2
«незадовільно»
5
4
2
4
3
«задовільно»
12
10
13
11
4
«добре»
7
8
8
10
5
«відмінно»
1
3
5
4
Функція "MATCH" в Excel
Обидва прикладу, наведені вище, не будуть працювати з великими масивами даних. Справа в тому, що використання функції "ІНДЕКС" в Excel передбачає введення номера рядка і стовпця не самої таблиці, а масиву даних. Це досить важко зробити, коли мова йде про великому числі елементів. Вирішити проблему може допомогти ще одна екселевская функція. Розглянемо випадок, коли масив складається з єдиної рядки.
A
B
C
D
1
товари
2
овочі
фрукти
спеції
3
помідори
груші
сіль
4
огірки
яблука
перець
5
перці
апельсини
імбир
6
моркоь
банани
кориця
Діапазон значень в цьому випадку В3:В6. Вибираємо комірку в іншому рядку, наприклад D1. Вводимо в неї назву фрукта, позицію якого хочемо знайти, в даному випадку «апельсини». У клітинці (Е1), куди хочемо записати номер відповідного рядка, вводимо «= MATCH(D1;В3:В6;0)» (див. таблицю). В результаті там з'являється число 3. Саме такий номер в діапазоні В3:В6 у вираження «апельсини».
A
B
C
D
E
1
апельсини
3
2
овочі
фрукти
3
помідори
груші
4
картопля
яблука
5
морква
апельсини
6
перець
банани
Останній 0 означає, що потрібно знайти точне співпадання зі значенням D1.
Як знайти текстові значення, що задовольняють нікому критерієм
У вигляді, наведеному вище, функція "MATCH" повертає одне значення (найперше, тобто верхнє). Але що робити, якщо в списку є повторення. В такому випадку допомагають формули масиву. Для їх використання слід виділити весь діапазон даних і використовувати поєднання клавіш «Ctrl+Shift+Enter. Однак її розгляд не є предметом даної статті.
Функція "ІНДЕКС" і "MATCH" в Excel: приклади
Уявіть, що вам потрібно вибрати з досить великого масиву тільки певні дані. Розглянемо для простоти випадок з невеликим числом елементів. Наприклад, у вас є звіт про успішність декількох груп студентів та їх оцінки. Припустимо, ви хочете, щоб в комірці H2 з'явилося число студентів, які отримали оцінку «незадовільно».
A
B
C
D
E
F
G
H
J
1
N/N
гр. 1
гр. 2
гр. 3
гр. 4
гр. 2
гр. 4
2
«незадовільно»
5
3
1
2
«уд»
3
«уд»
14
10
14
12
«відмінно»
4
«добре»
8
9
10
8
5
«відмінно»
4
6
5
3
Для цього краще всього використовувати обидві функції. Щоб дізнатися, що необхідно ввести в H2 спочатку розглянемо найпростіше вираження, яке можна використати для цієї мети. Зокрема, шукане значення можна отримати, якщо записати в клітинку «=ІНДЕКС(А2:Е5;1;2)». Тут ми використовували варіант з попередніх прикладів, коли номер рядка та номер стовпця обчислювався вручну. Однак наша мета - автоматизувати цей процес. Для цього слід замість двійки й одиниці, які вказують на шукані рядок і стовпець, у масиві записати відповідні функції "MATCH", що видають ці номери. Зверніть увагу, що ми шукаємо вираз «уд», розташоване в комірці G2 і «гр. 2» з H2. Крім того, нам потрібні точні збіги, тому в якості останнього, третього, аргумент в обох випадках вказується 0. Тоді замість 1 у формулі ІНДЕКС(А2:Е5;1;2) слід записати: MATCH(G2;A2:A5;0), а замість 2 — MATCH(H2; А2:Е2;0). Після підстановки маємо: ІНДЕКС(А2:E5; MATCH(G2;A2:A5;0); MATCH(H2; А2:Е2;0)). В результаті, натиснувши «Enter», маємо у цій клітинці значення «10».
Як поширити дію отриманої формули на якийсь діапазон
Як відомо, функція "ІНДЕКС" в Excel може бути «витягнута» на якийсь діапазон. У прикладі, розглянутому вище, це все 4 клітинки з H2:J3. У зв'язку з цим необхідно з'ясувати, як зробити так, щоб, «витягнувши» цю формули вправо і вниз, отримати правильні значення.
Головна складність полягає в тому, що масив А2:Е5 має відносний адресу. Щоб виправити це, слід перетворити його в абсолютний. Для цього масив записується у вигляді $А$2:$Е$5. Те ж саме слід зробити і для обох вбудованих функцій, тобто вони повинні виглядати як MATCH($G$2;$A$2:$A$5;0) і MATCH($H$2;$2:$Е2;0). Остаточний вигляд формули: ІНДЕКС($А$2:$Е$5; MATCH($G$2;$А$2:А$5;0); MATCH($H$2; $А$2:$Е$2;0)). В результаті будемо мати таблицю, зображену нижче
A
B
C
D
E
F
G
H
J
1
N/N
гр. 1
гр. 2
гр. 3
гр. 4
гр. 2
гр. 4
2
«незадовільно»
5
3
1
2
«уд»
10
12
3
«уд»
14
10
14
12
«відмінно»
6
3
4
«добре»
8
9
10
8
5
«відмінно»
4
6
5
3
Для отримання коректного результату треба стежити, щоб текстові значення були записані точно, в тому числі не містили опечатков і зайвих пробілів. В іншому випадку програма не буде розглядати їх як однакові.
Тепер ви знаєте, як використовується функція "ІНДЕКС" в Excel. Приклади її спільного використання з "MATCH" вам також відомі, і ви зможете коректно застосовувати їх для вирішення багатьох практичних завдань.