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

На даний момент програма Excel за своєюпопулярністю поступається тільки Word. Вона дозволяє з легкістю здійснювати найрізноманітніші економіко-статистичні розрахунки над великою кількістю даних. Для цієї мети в ній передбачена велика кількість вбудованих функцій, в тому числі допоміжних. Деякі з них здатні здійснювати дії, в тому числі над масивами даних. До них відноситься і функція "ІНДЕКС". В Excel вона використовується як окремо, так і з "ПОИСКПОЗ", про яку буде розказано нижче.

опис

Функція "ІНДЕКС" в Excel повертає значення (посилання на значення) вмісту комірки, заданої номерами рядка і стовпця таблиці або пойменованого діапазону.

Її синтаксис нескладний і виглядає наступним чином: ІНДЕКС (масив, № рядка, № стовпчика).

Ця функція може працювати також зєдиною рядком або з єдиним стовпцем. У такому випадку після вказівки одновимірного масиву виставляється одне число. Воно позначає номер рядка, якщо масив являє собою стовпець, і навпаки.

Функція "ІНДЕКС" в Excel іноді видає значення «# посилаючись!». Найчастіше це відбувається, якщо осередок, розташована на перетині зазначених рядка і стовпця, знаходиться поза зазначеного діапазону.

приклади застосування

Розглянемо кілька випадків використання функції "ІНДЕКС" на практиці.

Припустимо, є масив, що складається з 4стовпців і 4 рядків (див. таблицю). Якщо ввести в одну з комірок таблиці розташоване поза діапазону А1: Е5 вираз «= ІНДЕКС (В2: Е5, 2, 3)» (без лапок) і натиснути на «Введення», то у відповідь буде видано значення «бегонія».

Якщо потрібно дізнатися, скільки учнів Групи 2 отримали оцінку «незадовільно», то в відповідному полі слід ввести вираз: ІНДЕКС (С2: С5, 1).

Функція "ПОИСКПОЗ" в Excel

Обидва приклади, наведені вище, не будуть працюватиз великими масивами даних. Справа в тому, що використання функції "ІНДЕКС" в Excel передбачає введення номера рядка і стовпчика не самою таблиці, а масиву даних. Це досить важко зробити, коли мова йде про велику кількість елементів. Вирішити проблему може допомогти ще одна екселевскій функція.

Розглянемо випадок, коли масив складається з єдиного рядка.

Діапазон значень в цьому випадку В3: В6.

Вибираємо осередок в іншому рядку, наприклад D1.Вводимо в неї назва фрукта, позицію якого хочемо знайти, в даному випадку «апельсини». В осередку (Е1), куди хочемо записати номер відповідного рядка, вводимо «= ПОИСКПОЗ (D1; В3: В6; 0)» (див. Таблицю). В результаті там з'являється число 3. Саме такий номер в діапазоні В3: В6 у вираження «апельсини».

Останній 0 означає, що потрібно знайти точний збіг із значенням D1.

Як знайти всі текстові значення, що задовольняють нікому критерієм

У вигляді, представленому вище, функція "ПОИСКПОЗ"повертає тільки одне значення (найперше, т. е. верхнє). Але що робити, якщо в списку є повторення. В такому випадку допомагають формули масиву. Для їх використання слід виділити весь діапазон даних і використовувати поєднання клавіш «Ctrl + Shift + Enter». Однак її розгляд не є предметом даної статті.

Функція "ІНДЕКС" і "ПОИСКПОЗ" в Excel: приклади

Уявіть, що вам потрібно вибрати з доситьвеликого масиву тільки певні дані. Розглянемо для простоти випадок з невеликим числом елементів. Наприклад, у вас є звіт про успішність декількох груп студентів та їх оцінки. Припустимо, ви хочете, щоб в осередку H2 з'явилося число студентів, які отримали оцінку «незадовільно».

Для цього найкраще спільно використовувати обидвіфункції. Щоб дізнатися, що необхідно ввести в H2, спочатку розглянемо найпростіше вираження, яке можна використовувати для цієї мети. Зокрема, дані значення можна отримати, якщо записати в цей осередок «= ІНДЕКС (А2: Е5; 1; 2)». Тут ми використовували варіант з попередніх прикладів, коли номер рядка і стовпця вираховувався вручну. Однак наша мета - автоматизувати цей процес. Для цього слід замість двійки і одиниці, які вказують на шукані рядок і стовпець, в масиві записати відповідні функції "ПОИСКПОЗ", що видають ці номери. Зверніть увагу, що ми шукаємо вираз «уд», розташоване в осередку G2 і «гр. 2 »з H2. Крім того, нам потрібні точні співпадіння, тому в якості останнього, третього, аргументу в обох випадках вказується 0.

Тоді замість 1 у формулі ІНДЕКС (А2: Е5; 1; 2) слід записати: ПОИСКПОЗ (G2; A2: A5; 0), а замість 2 - ПОИСКПОЗ (H2; А2: Е2; 0).

Після підстановки маємо: ІНДЕКС (А2: E5; ПОИСКПОЗ (G2; A2: A5; 0); ПОИСКПОЗ (H2; А2: Е2; 0)). В результаті, натиснувши «Введення», маємо в цьому осередку значення «10».

Як поширити дію отриманої формули на якийсь діапазон

Як відомо, функція "ІНДЕКС" в Excel може бути«Витягнута» на якийсь діапазон. У прикладі, розглянутому вище, це все 4 осередки з H2: J3. У зв'язку з цим необхідно з'ясувати, як зробити так, щоб, «витягнувши» цю формули вправо і вниз, отримати правильні значення.

Головна складність полягає в тому, що масивА2: Е5 має відносний адресу. Щоб виправити це, слід перетворити його в абсолютний. Для цього масив записується у вигляді $ А $ 2: $ Е $ 5. Те ж слід зробити і для обох вбудованих функцій, т. Е. Вони повинні виглядати як ПОИСКПОЗ ($ G $ 2; $ A $ 2: $ A $ 5; 0) і ПОИСКПОЗ ($ H $ 2; А $ 2: $ Е2; 0).

Остаточний вигляд формули буде: ІНДЕКС ($ А $ 2: $ Е $ 5; ПОИСКПОЗ ($ G $ 2; $ А $ 2: А $ 5; 0); ПОИСКПОЗ ($ H $ 2; $ А $ 2: $ Е $ 2; 0)).

В результаті матимемо таблицю, зображену нижче

Для отримання коректного результату требастежити, щоб текстові значення були записані точно, в тому числі не містили помилки і зайвих пробілів. В іншому випадку програма не розглядатиме їх як однакові.

Тепер ви знаєте, як використовується функція"ІНДЕКС" в Excel. Приклади її спільного використання з "ПОИСКПОЗ" вам також відомі, і ви зможете коректно застосовувати їх для вирішення багатьох практичних завдань.