Функція "ІНДЕКС" у Excel: опис, застосування і приклади

На даний момент програма 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" вам також відомі, і ви зможете коректно застосовувати їх для вирішення багатьох практичних завдань.