Функція ІНДЕКС в програмі Microsoft Excel
Однією з найбільш корисних функцій програми Ексель є оператор ІНДЕКС. Він здійснює пошук даних в діапазоні на перетині зазначених рядка і стовпця, повертаючи результат в заздалегідь позначену осередок. Але повністю можливості цієї функції розкриваються при використанні її в складних формулах в комбінації з іншими операторами. Давайте розглянемо різні варіанти її застосування.
зміст
Використання функції ІНДЕКС
Оператор ІНДЕКС відноситься до групи функцій з категорії «Посилання та масиви». Він має два різновиди: для масивів і для посилань.
Варіант для масивів має наступний синтаксис:
=ИНДЕКС(массив;номер_строки;номер_столбца)
При цьому два останніх аргументу у формулі можна використовувати, як разом, так і будь-який один з них, якщо масив одновимірний. При багатовимірному діапазоні слід застосовувати обидва значення. Потрібно також врахувати, що під номером рядка і стовпця розуміється не номер на координатах листа, а порядок всередині самого зазначеного масиву.
Синтаксис для посилального варіанту виглядає так:
=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])
Тут точно так само можна використовувати тільки один аргумент з двох: «Номер рядка» або «Номер стовпця». Аргумент «Номер області» взагалі є необов'язковим і він застосовується тільки тоді, коли в операції беруть участь кілька діапазонів.
Таким чином, оператор шукає дані в установленому діапазоні при вказівці рядки або стовпці. Ця функція своїми можливостями дуже схожа на оператора ВВР , Але на відміну від нього може виробляти пошук практично скрізь, а не тільки в крайньому лівому стовпчику таблиці.
Спосіб 1: використання оператора ІНДЕКС для масивів
Давайте, перш за все, розберемо на простому прикладі алгоритм використання оператора ІНДЕКС для масивів.
Маємо таблицю зарплат. У першому її стовпці відображені прізвища працівників, у другому - дата виплати, а в третьому - величина суми заробітку. Нам потрібно вивести ім'я працівника в третьому рядку.
- Виділяємо осередок, в якій буде виводитися результат обробки. Кількома по значку «Вставити функцію», який розміщений відразу зліва від рядка формул.
- Відбувається процедура активації Майстра функцій. У категорії «Посилання та масиви» даного інструменту або «Повний алфавітний перелік» шукаємо найменування «ІНДЕКС». Після того, як знайшли цього оператора, виділяємо його і клацаємо по кнопці «OK», яка розміщується в нижній частині вікна.
- Відкривається невелике віконце, в якому потрібно вибрати один з типів функції: «Масив» або «Посилання». Потрібний нам варіант «Масив». Він розташований першим і за замовчуванням виділено. Тому нам залишається просто натиснути на кнопку «OK».
- Відкривається вікно аргументів функції ІНДЕКС. Як зазначалося вище, у неї є три аргументи, а відповідно і три поля для заповнення.
В поле «Масив» потрібно вказати адресу оброблюваного діапазону даних. Його можна вбити вручну. Але для полегшення завдання ми вчинимо інакше. Ставимо курсор у відповідне поле, а потім обводить весь діапазон табличних даних на аркуші. Після цього адресу діапазону тут же відобразиться в поле.
В поле «Номер рядка» ставимо цифру «3», так як за умовою нам потрібно визначити третє ім'я в списку. В поле «Номер стовпця» встановлюємо число «1», так як колонка з іменами є першою в виділеному діапазоні.
Після того, як всі зазначені налаштування здійснені, клацаємо по кнопці «OK».
- Результат обробки виводиться в клітинку, яка була вказана в першому пункті даної інструкції. Саме виведена прізвище є третьою в списку в виділеному діапазоні даних.
Ми розібрали застосування функції ІНДЕКС в багатовимірному масиві (декілька стовпців і рядків). Якби діапазон був одновимірним, то заповнення даних у вікні аргументів було б ще простіше. В поле «Масив» тим же методом, що і вище, ми вказуємо його адресу. В даному випадку діапазон даних складається тільки з значень в одній колонці «Ім'я». В поле «Номер рядка» вказуємо значення «3», так як потрібно дізнатися дані з третього рядка. Поле «Номер стовпця» взагалі можна залишити порожнім, так як у нас одновимірний діапазон, в якому використовується тільки один стовпець. Тиснемо на кнопку «OK».
Результат буде такий самий, що й вище.
Це був найпростіший приклад, щоб ви побачили, як працює ця функція, але на практиці подібний варіант її використання застосовується все-таки рідко.
урок: Майстер функцій в Ексель
Спосіб 2: застосування в комплексі з оператором ПОИСКПОЗ
На практиці функція ІНДЕКС найчастіше застосовується разом з аргументом ПОИСКПОЗ. Зв'язка ІНДЕКС - ПОИСКПОЗ є найпотужнішим інструментом при роботі в Ексель, який за своїм функціоналом більш гнучкий, ніж його найближчий аналог - оператор ВВР.
Основним завданням функції ПОИСКПОЗ є зазначення номера по порядку певного значення в виділеному діапазоні.
Синтаксис оператора ПОИСКПОЗ такий:
=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Шукане значення - це значення, позицію якого в діапазоні ми шукаємо;
- Проглядається масив - це діапазон, в якому знаходиться це значення;
- Тип зіставлення - це необов'язковий параметр, який визначає, точно або приблизно шукати значення. Ми будемо шукати точні значення, тому даний аргумент не використовується.
За допомогою цього інструменту можна автоматизувати введення аргументів «Номер рядка» і «Номер стовпця» в функцію ІНДЕКС.
Подивимося, як це можна зробити на конкретному прикладі. Працюємо все з тієї ж таблицею, про яку йшла мова вище. Окремо у нас є два додаткових поля - «Ім'я» і «Сума». Потрібно зробити так, що при введенні імені працівника автоматично відображалася сума зароблених ним грошей. Подивимося, як це можна втілити на практиці, застосувавши функції ІНДЕКС та ПОИСКПОЗ.
- Перш за все, дізнаємося, яку заробітну плату отримує працівник Парфьонов Д. Ф. Вписуємо його ім'я у відповідне поле.
- Виділяємо осередок в поле «Сума», в якій буде виводитися підсумковий результат. Запускаємо вікно аргументів функції ІНДЕКС для масивів.
В поле «Масив» вносимо координати стовпчика, в якому знаходяться суми заробітних плат працівників.
Поле «Номер стовпця» залишаємо порожнім, так як ми використовуємо для прикладу одновимірний діапазон.
А ось в поле «Номер рядка» нам якраз потрібно буде записати функцію ПОИСКПОЗ. Для її записи дотримуємося того синтаксису, про який йшла мова вище. Відразу в поле вписуємо найменування самого оператора «ПОИСКПОЗ» без лапок. Потім відразу ж відкриваємо дужку і вказуємо координати шуканого значення. Це координати того осередку, в яку ми окремо записали прізвище працівника Парфьонова. Ставимо крапку з комою і вказуємо координати проглядається діапазону. У нашому випадку це адреса стовпця з іменами співробітників. Після цього закриваємо дужку.
Після того, як всі значення внесені, тиснемо на кнопку «OK».
- Результат кількості заробітку Парфьонова Д. Ф. після обробки виводиться в поле «Сума».
- Тепер, якщо в поле «Ім'я» ми змінимо вміст з «Парфьонов Д.Ф.», на, наприклад, «Попова М. Д.», то автоматично зміниться і значення заробітної плати в полі «Сума».
Спосіб 3: обробка декількох таблиць
Тепер подивимося, як за допомогою оператора ІНДЕКС можна обробити кілька таблиць. Для цих цілей буде застосовуватися додатковий аргумент «Номер області».
Маємо три таблиці. У кожній таблиці відображена заробітна плата працівників за окремий місяць. Нашим завданням є дізнатися заробітну плату (третій стовпець) другого працівника (другий рядок) за третій місяць (третя область).
- Виділяємо осередок, в якій буде проводитися висновок результату і звичайним способом відкриваємо Майстер функцій, але при виборі типу оператора вибираємо контрольний вид. Це нам потрібно тому, що саме цей тип підтримує роботу з аргументом «Номер області».
- Відкривається вікно аргументів. В поле «Посилання» нам потрібно вказати адреси всіх трьох діапазонів. Для цього встановлюємо курсор в поле і виділяємо перший діапазон з затиснутою лівою кнопкою миші. Потім ставимо крапку з комою. Це дуже важливо, тому що якщо ви відразу перейдете до виділення наступного масиву, то його адреса просто замінить координати попереднього. Отже, після введення крапки з комою виділяємо наступний діапазон. Потім знову ставимо крапку з комою і виділяємо останній масив. Всі вираз, яке знаходиться в полі «Посилання» беремо в дужки.
В поле «Номер рядка» вказуємо цифру «2», так як шукаємо інше прізвище в списку.
В поле «Номер стовпця» вказуємо цифру «3», так як колонка з зарплатою є третьою за рахунком в кожній таблиці.
В поле «Номер області» ставимо цифру «3», так як нам потрібно знайти дані в третій таблиці, в якій міститься інформація про заробітну плату за третій місяць.
Після того, як всі дані введені, клацаємо по кнопці «OK».
- Після цього в попередньо виділену клітинку виводяться результати обчислення. Там відображається сума заробітної плати другого за рахунком працівника (Сафронова В. М.) за третій місяць.
Спосіб 4: обчислення суми
Посилальна форма не так часто застосовується, як форма масиву, але її можна використовувати не тільки при роботі з декількома діапазонами, а й для інших потреб. Наприклад, її можна застосовувати для розрахунку суми в комбінації з оператором СУМ.
При додаванні суми СУММ має наступний синтаксис:
=СУММ(адрес_массива)
У нашому конкретному випадку суму заробітку всіх працівників за місяць можна обчислити за допомогою такої формули:
=СУММ(C4:C9)
Але можна її трохи модифікувати, використавши функцію ІНДЕКС. Тоді вона буде мати наступний вигляд:
=СУММ(C4:ИНДЕКС(C4:C9;6))
В цьому випадку в координатах початку масиву вказується осередок, з якою він починається. А ось в координатах вказівки закінчення масиву використовується оператор ІНДЕКС. В даному випадку перший аргумент оператора ІНДЕКС вказує на діапазон, а другий - на останню його осередок - шосту.
Як бачимо, функцію ІНДЕКС можна використовувати в Ексель для вирішення досить різнопланових завдань. Хоча ми розглянули далеко не всі можливі варіанти її застосування, а тільки найбільш затребувані. Існує два типи цієї функції: контрольний і для масивів. Найбільш ефективно її можна застосовувати в комбінації з іншими операторами. Створені таким способом формули зможуть вирішувати найскладніші завдання.