функція індекс в програмі Microsoft Excel

Зміст

• Використання функції Індекс
  • Спосіб 1: використання оператора індекс для масивів
  • Спосіб 2: Застосування в комплексі з оператором ПОИСКПОЗ
  • Спосіб 3: обробка декількох таблиць
  • Спосіб 4: обчислення суми
• Питання та відповіді

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

Використання функції Індекс

Оператор Індекс відноситься до групи функцій з категорії " посилання та масиви» . Він має два різновиди: для масивів і для посилань.

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

=Індекс (масив; номер_строки; номер_стовпця)

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

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

=Індекс (посилання; номер_строки; номер_стовпця; [номер_області])

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

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

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

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

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

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

2. Відбувається процедура активації майстри функцій . В категорії " посилання та масиви» даного інструменту або " повний алфавітний список» шукаємо найменування " Індекс» . Після того, як знайшли цього оператора, виділяємо його і клацаємо по кнопці «OK» , яка розміщується в нижній частині вікна.

3. Відкривається невелике віконце, в якому потрібно вибрати один з типів функції: " масив» або »посилання" . Потрібний нам варіант " масив» . Він розташований першим і за замовчуванням виділений. Тому нам залишається просто натиснути на кнопку «OK» .

4. Відкривається вікно аргументів функції Індекс . Як вище говорилося, у неї є три аргументи, а відповідно і три поля для заповнення.

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

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

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


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

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

Результат буде точно таким же, як і вище.

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

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

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

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

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

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

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

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

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

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

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

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

   У полі " масив» вносимо координати стовпця, в якому знаходяться суми заробітних плат працівників.

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

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

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


3. Результат кількості заробітку Парфьонова Д. ф. після обробки виводиться в поле «сума».

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

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

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

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

1. Виділяємо осередок, в якій буде проводитися висновок результату і звичайним способом відкриваємо Майстер функцій , але при виборі типу оператора вибираємо посилальний вид. Це нам потрібно тому, що саме цей тип підтримує роботу з аргументом " номер області» .

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

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

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

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

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


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

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

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

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

=sum (адреса_масиву)

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

=sum (C4: C9)

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

=sum (C4:Індекс(C4: C9;6))

У цьому випадку координати початку масиву вказують клітинку, з якої він починається. А ось в координатах вказівки закінчення масиву використовується оператор Індекс . В даному випадку перший аргумент оператора Індекс вказує на діапазон, а другий – на останню його клітинку – шосту.

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

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