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

Використання функції ІНДЕКС

Оператор ІНДЕКС відноситься до групи функцій з категорії «Посилання та масиви». Він має два різновиди: для масивів і для посилань.

Варіант для масивів має наступний синтаксис:

=ИНДЕКС(массив;номер_строки;номер_столбца)

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

Синтаксис для посилального варіанту виглядає так:

=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])

Тут точно так само можна використовувати тільки один аргумент з двох: «Номер рядка» або «Номер стовпця». Аргумент «Номер області» взагалі є необов'язковим і він застосовується тільки тоді, коли в операції беруть участь кілька діапазонів.

Таким чином, оператор шукає дані в установленому діапазоні при вказівці рядки або стовпці. Ця функція своїми можливостями дуже схожа на оператора ВВР , Але на відміну від нього може виробляти пошук практично скрізь, а не тільки в крайньому лівому стовпчику таблиці.

Спосіб 1: використання оператора ІНДЕКС для масивів

Давайте, перш за все, розберемо на простому прикладі алгоритм використання оператора ІНДЕКС для масивів.

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

  1. Виділяємо осередок, в якій буде виводитися результат обробки. Кількома по значку «Вставити функцію», який розміщений відразу зліва від рядка формул.
  2. Перехід в Майстер функцій в Microsoft Excel

  3. Відбувається процедура активації Майстра функцій. У категорії «Посилання та масиви» даного інструменту або «Повний алфавітний перелік» шукаємо найменування «ІНДЕКС». Після того, як знайшли цього оператора, виділяємо його і клацаємо по кнопці «OK», яка розміщується в нижній частині вікна.
  4. Майстер функцій в Microsoft Excel

  5. Відкривається невелике віконце, в якому потрібно вибрати один з типів функції: «Масив» або «Посилання». Потрібний нам варіант «Масив». Він розташований першим і за замовчуванням виділено. Тому нам залишається просто натиснути на кнопку «OK».
  6. Вибір типу функції ІНДЕКС в Microsoft Excel

  7. Відкривається вікно аргументів функції ІНДЕКС. Як зазначалося вище, у неї є три аргументи, а відповідно і три поля для заповнення.

    В поле «Масив» потрібно вказати адресу оброблюваного діапазону даних. Його можна вбити вручну. Але для полегшення завдання ми вчинимо інакше. Ставимо курсор у відповідне поле, а потім обводить весь діапазон табличних даних на аркуші. Після цього адресу діапазону тут же відобразиться в поле.

    В поле «Номер рядка» ставимо цифру «3», так як за умовою нам потрібно визначити третє ім'я в списку. В поле «Номер стовпця» встановлюємо число «1», так як колонка з іменами є першою в виділеному діапазоні.

    Після того, як всі зазначені налаштування здійснені, клацаємо по кнопці «OK».

  8. Вікно аргументів функції ІНДЕКС в Microsoft Excel

  9. Результат обробки виводиться в клітинку, яка була вказана в першому пункті даної інструкції. Саме виведена прізвище є третьою в списку в виділеному діапазоні даних.

Результат обробки функції ІНДЕКС в Microsoft Excel

Ми розібрали застосування функції ІНДЕКС в багатовимірному масиві (декілька стовпців і рядків). Якби діапазон був одновимірним, то заповнення даних у вікні аргументів було б ще простіше. В поле «Масив» тим же методом, що і вище, ми вказуємо його адресу. В даному випадку діапазон даних складається тільки з значень в одній колонці «Ім'я». В поле «Номер рядка» вказуємо значення «3», так як потрібно дізнатися дані з третього рядка. Поле «Номер стовпця» взагалі можна залишити порожнім, так як у нас одновимірний діапазон, в якому використовується тільки один стовпець. Тиснемо на кнопку «OK».

Вікно аргументів функції ІНДЕКС для одновимірного масиву в Microsoft Excel

Результат буде такий самий, що й вище.

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

Це був найпростіший приклад, щоб ви побачили, як працює ця функція, але на практиці подібний варіант її використання застосовується все-таки рідко.

урок: Майстер функцій в Ексель

Спосіб 2: застосування в комплексі з оператором ПОИСКПОЗ

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

Основним завданням функції ПОИСКПОЗ є зазначення номера по порядку певного значення в виділеному діапазоні.

Синтаксис оператора ПОИСКПОЗ такий:

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

  • Шукане значення - це значення, позицію якого в діапазоні ми шукаємо;
  • Проглядається масив - це діапазон, в якому знаходиться це значення;
  • Тип зіставлення - це необов'язковий параметр, який визначає, точно або приблизно шукати значення. Ми будемо шукати точні значення, тому даний аргумент не використовується.

За допомогою цього інструменту можна автоматизувати введення аргументів «Номер рядка» і «Номер стовпця» в функцію ІНДЕКС.

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

  1. Перш за все, дізнаємося, яку заробітну плату отримує працівник Парфьонов Д. Ф. Вписуємо його ім'я у відповідне поле.
  2. Ім'я вписано в поле в Microsoft Excel

  3. Виділяємо осередок в поле «Сума», в якій буде виводитися підсумковий результат. Запускаємо вікно аргументів функції ІНДЕКС для масивів.

    В поле «Масив» вносимо координати стовпчика, в якому знаходяться суми заробітних плат працівників.

    Поле «Номер стовпця» залишаємо порожнім, так як ми використовуємо для прикладу одновимірний діапазон.

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

    Після того, як всі значення внесені, тиснемо на кнопку «OK».

  4. Вікно аргументів функції ІНДЕКС в комбінації з оператором ПОИСКПОЗ в Microsoft Excel

  5. Результат кількості заробітку Парфьонова Д. Ф. після обробки виводиться в поле «Сума».
  6. Результат обробки функції ІНДЕКС в комбінації з оператором ПОИСКПОЗ в Microsoft Excel

  7. Тепер, якщо в поле «Ім'я» ми змінимо вміст з «Парфьонов Д.Ф.», на, наприклад, «Попова М. Д.», то автоматично зміниться і значення заробітної плати в полі «Сума».

Зміна значень при використанні функції ІНДЕКС в комбінації з оператором ПОИСКПОЗ в Microsoft Excel

Спосіб 3: обробка декількох таблиць

Тепер подивимося, як за допомогою оператора ІНДЕКС можна обробити кілька таблиць. Для цих цілей буде застосовуватися додатковий аргумент «Номер області».

Маємо три таблиці. У кожній таблиці відображена заробітна плата працівників за окремий місяць. Нашим завданням є дізнатися заробітну плату (третій стовпець) другого працівника (другий рядок) за третій місяць (третя область).

  1. Виділяємо осередок, в якій буде проводитися висновок результату і звичайним способом відкриваємо Майстер функцій, але при виборі типу оператора вибираємо контрольний вид. Це нам потрібно тому, що саме цей тип підтримує роботу з аргументом «Номер області».
  2. Вибір довідкового виду функції ІНДЕКС в Microsoft Excel

  3. Відкривається вікно аргументів. В поле «Посилання» нам потрібно вказати адреси всіх трьох діапазонів. Для цього встановлюємо курсор в поле і виділяємо перший діапазон з затиснутою лівою кнопкою миші. Потім ставимо крапку з комою. Це дуже важливо, тому що якщо ви відразу перейдете до виділення наступного масиву, то його адреса просто замінить координати попереднього. Отже, після введення крапки з комою виділяємо наступний діапазон. Потім знову ставимо крапку з комою і виділяємо останній масив. Всі вираз, яке знаходиться в полі «Посилання» беремо в дужки.

    В поле «Номер рядка» вказуємо цифру «2», так як шукаємо інше прізвище в списку.

    В поле «Номер стовпця» вказуємо цифру «3», так як колонка з зарплатою є третьою за рахунком в кожній таблиці.

    В поле «Номер області» ставимо цифру «3», так як нам потрібно знайти дані в третій таблиці, в якій міститься інформація про заробітну плату за третій місяць.

    Після того, як всі дані введені, клацаємо по кнопці «OK».

  4. Вікно аргументів функції ІНДЕКС при роботі з трьома областями в Microsoft Excel

  5. Після цього в попередньо виділену клітинку виводяться результати обчислення. Там відображається сума заробітної плати другого за рахунком працівника (Сафронова В. М.) за третій місяць.

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

Спосіб 4: обчислення суми

Посилальна форма не так часто застосовується, як форма масиву, але її можна використовувати не тільки при роботі з декількома діапазонами, а й для інших потреб. Наприклад, її можна застосовувати для розрахунку суми в комбінації з оператором СУМ.

При додаванні суми СУММ має наступний синтаксис:

=СУММ(адрес_массива)

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

=СУММ(C4:C9)

Результат функції СУММ в Microsoft Excel

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

=СУММ(C4:ИНДЕКС(C4:C9;6))

Результат комбінації функції СУММ і ІНДЕКС в Microsoft Excel

В цьому випадку в координатах початку масиву вказується осередок, з якою він починається. А ось в координатах вказівки закінчення масиву використовується оператор ІНДЕКС. В даному випадку перший аргумент оператора ІНДЕКС вказує на діапазон, а другий - на останню його осередок - шосту.

урок: Корисні функції Excel

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