Функція індекс (англ. Index) в excel з прикладами

Про те як працює функція ІНДЕКС (Англ. INDEX) ми вже писали в окремій статті, але в чистому вигляді як правило дана функція застосовується не так часто. Нагадаємо, що функція ІНДЕКС повертає значення на перетині зазначеного рядка і стовпця певного діапазону.

Давайте згадаємо як працює ця функція, а після цього розглянемо роботу даної функції спільно з функцією ПОИСКПОЗ (Англ. MATCH)

Подивіться на ось цей приклад

Відео: Пошук відповідності в таблиці на основі значення з одного стовпчика ІНДЕКС та ПОИСКПОЗ

Є таблиця з продажами різних фруктів в різних магазинах - це область A2: F10

Нагадаю синтаксис функції ІНДЕКС:

= ІНДЕКС (масив- номер рядка- номер_стовпчика)

де масив - це наша таблиця A2: F10

номер рядка - як випливає з опису - це номер рядка нашого масиву (таблиці), Зверніть увагу! Номер рядка необхідно вважати саме на нашу вказаною масиву, а не взагалі з першого рядка. Наша таблиця починається з другого рядка.

номер_стовпчика - це номер стовпця вказаного масиву. У нашому випадку перший стовпець збігається з першим стовпцем нашого масиву.

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

= ІНДЕКС (A2: F10-5-3) - дивіться малюнок вище

Ще раз зверніть увагу, слива знаходиться в 6-му рядку на даному аркуші, але якщо розглядати саме наш масив A2: F10, то видно, що Слива розташована на 5-му рядку цієї таблиці. Як бачите, все дуже просто, але в такому вигляді формула не має застосування, так як номер рядка і номер стовпця ми вважали усно і без формули. Якщо нам необхідно знайти дані по іншим товарам нам так само доведеться все вважати усно і вказувати номери рядків і номера стовпців. Тому, в більшості випадках функцію ІНДЕКС використовують спільно з іншими функціями, часто з функцією ПОИСКПОЗ, яка дозволяє знайти номер рядка і стовпця автоматично.

Функція ІНДЕКС в Excel з функцією ПОИСКПОЗ

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

І ось з цього звіту нам необхідно витягти певні дані. Наприклад, продажу тільки Груш, Злив і Ківі в магазині Перехрестя і Стрічка - права таблиця з жовтими осередками.

Звичайно, в даному випадку можна використовувати функцію ВПР, при цьому номер стовпчика нам потрібно буде вважати вручну. Перехрестя це 3-й стовпець, а Стрічка це 4-й стовпець. Але уявімо, що кількість магазинів буде дуже багато, до того ж даний звіт нам надсилають щомісяця і магазини можуть бути в різних стовпчиках, крім того, можуть додаватися нові магазини. В даному випадку нам буде незручно використовувати ВПР, так як номер стовпчика нам в кожному випадку доведеться знаходити заново.

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

[Ads]

Отже, давайте для наочності, щоб ви бачили послідовність дій, спочатку пропишемо функцію ІНДЕКС в чистому вигляді. В осередку L4 нам необхідно знайти з таблиці A2: F10 продажу Груш в Перехресті. пропишемо формулу

= ІНДЕКС (A2: F10-3-3) - груша знаходиться в третьому рядку таблиці A2: F10, а Перехрестя в третьому стовпці. Відмінно, а тепер пропишемо формулу, щоб номер рядка і номер стовпця вважався автоматично.

Щоб знайти номер рядка використовуємо функцію ПОИСКПОЗ - пошук позиції. Синтаксис функції:

= ПОИСКПОЗ (шукане_значення, просматріваемий_массів, [тіп_сопоставленія])

шукане_значення - нашому випадку, в осередку L4 ми шукаємо груші, тому шукане значення у нас буде K4

проглядається масив - нашому прикладі нам необхідно знайти груші з стовпці з фруктами - це діапазон A2: A10

тіп_сопоставленія - вказуємо 0, так як ми шукаємо повний збіг.

Формула буде мати наступний вигляд:

Відео: Функція ІНДЕКС в Excel

= ПОИСКПОЗ (K4-A2: A10-0) - підсумком цієї формули буде позиція 3 в діапазоні A2: A10

аналогічно, тільки в горизонтальному вигляді знаходимо номер стовпчика.

шукане_значення - магазин перехрестя або осередок L3

проглядається масив - магазин ми знаходимо в рядку з магазинами - це діапазон A2: F2

тіп_сопоставленія - вказуємо 0, так як ми шукаємо точний збіг.

в результаті отримуємо формулу:

= ПОИСКПОЗ (L3-A2: F2-0) - підсумком цієї формули буде позиція 3 в діапазоні A2: F2

Тепер в нашу формулу = ІНДЕКС (A2: F10-3,3) Замість номера рядка і номера стовпчика пропишемо раноценние значення, але у вигляді формул:

Відео: Функції ІНДЕКС та ПОИСКПОЗ в Excel

= ІНДЕКС (A2: F10-ПОИСКПОЗ (K4-A2: A10-0)-ПОИСКПОЗ (L3-A2: F2-0))

Наша формула практично готова. У нас автоматично знаходиться номер рядка і номер стовпця. Залишилося тільки доробити формулу, щоб її можна було протягнути вниз на усі товари і право на всі магазини. Якщо ми зробимо це зараз, то розрахунок буде невірним так як:

наш масив A2: F10 має відносний адреса, тому при протягуванні формули вниз і право діапазон так же буде зсуватися, а він у нас постійний, тому пропишемо перетворимо його в абсолютну адресу, для цього пропишемо знаки доларів перед стовпцями і рядки (можна виділити даний діапазон у формулі і натиснути клавішу F4).

A2: F10 → $ A $ 2: $ F $ 10

Далі йде номер рядка з формулою ПОИСКПОЗ (K4-A2: A10-0), при протягуванні вниз у нас автоматично K4 (Груші) зміниться на K5 (Зливи), що нам і потрібно, але діапазон, А2: A10 у нас постійний, тому пропишемо його в абсолютному вигляді А2: A10 → $ А $ 2: $ A $ 10

Все відмінно, але коли ми будемо протягувати формулу вправо, то K4 (Груші) автоматично зміниться на L4, нам же необхідно, щоб при протягуванні право K4 не змінювалося. Але ми пам`ятаємо, що в той же час нам необхідно, щоб K4 змінювалося при протягуванні вниз. Тому нам необхідно закріпити тільки стовпець (K), а рядок повинна змінюватися. Пропишемо знак долар тільки перед стовпцем К4 → $K4

В результаті формула пошуку номера рядка буде виглядати ПОИСКПОЗ ($K4- $ A $ 2: $ A $ 10-0)

Аналогічно з номером стовпця, діапазон повинен бути повністю закріплений, при протягуванні вправо стовпець повинен змінюватися, а при протягуванні вниз номер рядка (рядок з магазинами) не повинен змінюватися. Для цього пропишемо знак долара тільки перед номером рядка L3 → L $ 3

В результаті формула пошуку номера стовпчика буде виглядати ПОИСКПОЗ (L$3 $ A $ 2: $ F $ 2-0)

Підсумкова формула, яку ви можете протягнути вниз і вправо

= ІНДЕКС ($A $ 2: $ F $ 10-ПОИСКПОЗ ($ K4- $ A $ 2: $ A $ 10-0)-ПОИСКПОЗ (L $ 3- $ A $ 2: $ F $ 2-0))

= INDEX ($ A $ 2: $ F $ 10-MATCH ($ K4- $ A $ 2: $ A $ 10-0) -MATCH (L $ 3- $ A $ 2: $ F $ 2-0)) - англ. версії

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

З цією формулою ви можете міняти місцями назви стовпців, додавати інші магазини і фрукти - формула все одно буде працювати, але не забудьте так само міняти діапазони. Можна заздалегідь взяти з великим запасом діапазони, якщо ви знаєте, що дані будуть додаватися.

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

Завантажити приклад файлу - Функція-ІНДЕКС-і-ПОІСКПОЗ.xlsx