Функція "індекс" в 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. Приклади її спільного використання з "ПОИСКПОЗ" вам також відомі, і ви зможете коректно застосовувати їх для вирішення багатьох практичних завдань.