Функція Excel INDEX (приклади + відео)
Коли використовувати функцію Excel INDEX
Функцію Excel INDEX можна використовувати, коли потрібно отримати значення з табличних даних, і у вас є номер рядка та номер стовпця точки даних. Наприклад, у наведеному нижче прикладі ви можете скористатися функцією INDEX, щоб отримати позначки "Том" у Фізиці, коли вам відомий номер рядка та номер стовпця в наборі даних.
Що повертає
Він повертає значення з таблиці для зазначеного номера рядка та номера стовпця.
Синтаксис
= ІНДЕКС (масив, номер рядка, [номер_кол.])
= ІНДЕКС (масив, номер рядка, [номер_кол.], [Номер_ області])
Функція INDEX має 2 синтаксису. У більшості випадків використовується перший, однак у разі тристороннього пошуку використовується другий (розглянуто у прикладі 5).
Аргументи введення
- масив - а діапазон комірок або константа масиву.
- рядок_число - номер рядка, з якого потрібно отримати значення.
- [col_num] - номер стовпця, з якого потрібно отримати значення. Хоча це необов’язковий аргумент, але якщо номер_строки не вказано, його потрібно надати.
- [номер_площі] - (Необов’язково) Якщо аргумент масиву складається з декількох діапазонів, це число буде використовуватися для вибору посилання з усіх діапазонів.
Додаткові примітки (нудні речі… але важливо знати)
- Якщо номер рядка або номер стовпця дорівнює 0, він повертає значення всього рядка або стовпця відповідно.
- Якщо функція INDEX використовується перед посиланням на комірку (наприклад, A1 :), вона повертає посилання на клітинку замість значення (див. Приклади нижче).
- Найбільш широко використовується разом з функцією MATCH.
- На відміну від VLOOKUP, функція INDEX може повертати значення зліва від значення пошуку.
- Функція INDEX має дві форми - форму масиву та форму посилання
- "Форма масиву" - це місце, де ви отримуєте значення на основі номера рядка та стовпця з даної таблиці.
- "Форма посилання" - це місце, де є кілька таблиць, і ви використовуєте аргумент area_num для вибору таблиці, а потім отримуєте значення всередині неї, використовуючи номер рядка та стовпця (див. Приклад нижче).
Функція Excel INDEX - Приклади
Ось шість прикладів використання функції Excel INDEX.
Приклад 1 - Пошук знаків Тома у фізиці (двосторонній пошук)
Припустимо, у вас є набір даних, як показано нижче:
Щоб знайти оцінки Тома у фізиці, використовуйте формулу нижче:
= ІНДЕКС ($ B $ 3: $ E $ 10,3,2)
Ця формула INDEX визначає масив як $ B $ 3: $ E $ 10, який має позначки для всіх предметів. Потім він використовує номер рядка (3) та номер стовпця (2), щоб отримати позначки Тома у фізиці.
Приклад 2 - Створення динамічного значення LOOKUP за допомогою функції MATCH
Можливо, не завжди можна вручну вказати номер рядка та номер стовпця. Можливо, у вас є величезний набір даних, або ви можете зробити його динамічним, щоб він автоматично визначав ім’я та/або тему, зазначену у клітинках, і давав правильний результат.
Щось, як показано нижче:
Це можна зробити за допомогою комбінації INDEX та функції MATCH.
Ось формула, яка зробить значення пошуку динамічними:
= ІНДЕКС ($ B $ 3: $ E $ 10, МАТЧ ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
У наведеній вище формулі замість жорсткого кодування номера рядка та номера стовпця функція MATCH використовується, щоб зробити її динамічною.
- Динамічний номер рядка задається наступною частиною формули - МАТЧ ($ G $ 5, $ A $ 3: $ A $ 10,0). Він сканує імена студентів та визначає значення пошуку (у цьому випадку 5 доларів США). Потім він повертає номер рядка значення пошуку в наборі даних. Наприклад, якщо значенням пошуку є Метт, він поверне 1, якщо це Боб, він поверне 2 і так далі.
- Номер динамічного стовпця задається наступною частиною формули - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Він сканує назви предметів та визначає значення пошуку (у цьому випадку $ H $ 4). Потім він повертає номер стовпця значення пошуку в наборі даних. Наприклад, якщо значення підстановки Math, воно поверне 1, якщо це фізика, воно поверне 2 і так далі.
Приклад 3 - Використання розкривних списків як значень пошуку
У наведеному вище прикладі нам потрібно вручну ввести дані. Це може зайняти багато часу і викликати помилки, особливо якщо у вас величезний список значень пошуку.
Гарна ідея в таких випадках - створити розкривний список значень пошуку (у цьому випадку це можуть бути імена студентів та предмети), а потім просто вибрати зі списку. Виходячи з вибору, формула автоматично оновить результат.
Щось, як показано нижче:
Це робить хороший компонент інформаційної панелі, оскільки ви можете мати величезний набір даних із сотнями студентів на задній частині, але кінцевий користувач (скажімо, вчитель) може швидко отримати оцінки учня з предмета, просто зробивши вибір з спадне меню.
Як це зробити:
Формула, використана в цьому випадку, така ж, як і у прикладі 2.
= ІНДЕКС ($ B $ 3: $ E $ 10, МАТЧ ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Значення пошуку перетворено у випадаючі списки.
Нижче наведено кроки для створення розкривного списку Excel:
- Виберіть у розкривному списку клітинку, у якій ви хочете. У цьому прикладі в G4 нам потрібні імена студентів.
- Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
- У діалоговому вікні Перевірка даних на вкладці налаштувань виберіть Список зі спадного меню Дозволити.
- У джерелі виберіть $ A $ 3: $ A $ 10
- Натисніть OK.
Тепер у клітині G5 буде випадаючий список. Аналогічно, ви можете створити один у H4 для суб'єктів.
Приклад 4 - Повертаються значення з цілого рядка/стовпця
У наведених вище прикладах ми використовували функцію Excel INDEX для двостороннього пошуку та отримання єдиного значення.
Що робити, якщо ви хочете отримати всі оцінки студента. Це може допомогти вам знайти максимальний/мінімальний бал цього учня або загальну оцінку з усіх предметів.
Простою англійською мовою ви спочатку хочете отримати весь ряд балів для учня (скажімо, Боб), а потім у межах цих значень визначити найвищий бал або загальну суму всіх балів.
Ось трюк.
У функції INDEX Excel під час введення номер стовпця як 0, він поверне значення всього цього рядка.
Отже, формула для цього буде такою:
= ІНДЕКС ($ B $ 3: $ E $ 10, МАТЧ ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Тепер ця формула. якщо використовується як є, повертає #VALUE! помилка. Хоча він відображає помилку, у бекенді він повертає масив, який містить усі бали для Тома - {57,77,91,91}.
Якщо вибрати формулу в режимі редагування і натиснути F9, ви зможете побачити масив, який вона повертає (як показано нижче): 
Аналогічно, на основі того, що таке значення пошуку, коли номер стовпця вказано як 0 (або залишено пустим), він повертає всі значення в рядку для значення пошуку
Тепер для обчислення загального балу, отриманого Томом, ми можемо просто скористатися наведеною вище формулою у функції SUM.
= SUM (ІНДЕКС ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
У подібних рядках для обчислення найвищого балу ми можемо використовувати MAX/LARGE, а для обчислення мінімуму - MIN/SMALL.
Приклад 5 - Тристоронній пошук за допомогою INDEX/MATCH
Функція Excel INDEX створена для обробки тристороннього пошуку.
Що таке тристоронній пошук?
У наведених вище прикладах ми використовували одну таблицю з оцінками для студентів з різних предметів. Це приклад двостороннього пошуку, оскільки ми використовуємо дві змінні для отримання балів (ім’я учня та предмет).
Припустимо, що через рік студент має три різні рівні іспитів, одиничний тест, проміжний і підсумковий іспит (це те, що я мав, коли був студентом).
Тристоронній пошук-це можливість отримувати оцінки студента за певний предмет із зазначеного рівня іспиту. Це зробить тристоронній пошук, оскільки є три змінні (ім’я студента, ім’я суб’єкта та рівень іспиту).
Ось приклад тристороннього пошуку:
У наведеному вище прикладі, окрім вибору імені студента та назви предмета, можна також вибрати рівень іспиту. На основі рівня іспиту він повертає відповідне значення з однієї з трьох таблиць.
Ось формула, яка використовується у клітинці H4:
= ІНДЕКС (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Unit test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
Давайте розберемо цю формулу, щоб зрозуміти, як вона працює.
Ця формула бере чотири аргументи. INDEX - одна з тих функцій Excel, яка має більше одного синтаксису.
= ІНДЕКС (масив, номер рядка, [номер_кол.])
= ІНДЕКС (масив, номер рядка, [номер_кол.], [Номер_області])
Поки що у всіх наведених вище прикладах ми використовували перший синтаксис, але для тристороннього пошуку нам потрібно використовувати другий синтаксис.
Тепер давайте розглянемо кожну частину формули на основі другого синтаксису.
- масив - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): замість використання одного масиву, у цьому випадку ми використовували три масиви в дужках.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): Функція MATCH використовується для пошуку позиції імені студента у клітинці $ G $ 4 у списку імен студента.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): функція MATCH використовується для пошуку позиції імені суб’єкта в комірці $ H $ 3 у списку імен суб’єкта.
- [area_num] - IF ($ H $ 2 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)): Значення номера області вказує функції INDEX, який масив вибрати. У цьому прикладі ми маємо три масиви в першому аргументі. Якщо у спадному меню вибрати «Unit Test», функція IF повертає 1, а функції INDEX вибирають 1-й масив з трьох масивів (це $ B $ 3: $ E $ 7).
Приклад 6 - Створення посилання за допомогою функції INDEX (динамічні іменовані діапазони)
Це одне дике використання функції Excel INDEX.
Візьмемо простий приклад.
У мене є список імен, як показано нижче:
Тепер я можу використовувати просту функцію INDEX, щоб отримати прізвище у списку.
Ось формула:
= ІНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ця функція просто підраховує кількість порожніх клітинок і повертає останній елемент із цього списку (вона працює лише тоді, коли у списку немає порожніх місць).
Тепер у чому тут магія.
Якщо ви поставите формулу перед посиланням на клітинку, формула поверне посилання на клітинку відповідного значення (замість самого значення).
= A2: ІНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ви б очікували, що наведена вище формула поверне = A2: "Джош" (де Джош - це останнє значення у списку). Однак він повертає = A2: A9 і, отже, ви отримуєте масив імен, як показано нижче:
Один із практичних прикладів, де ця техніка може бути корисною, - це створення динамічних іменованих діапазонів.
Ось і все в цьому підручнику. Я намагався охопити основні приклади використання функції Excel INDEX. Якщо ви хочете побачити більше прикладів, доданих до цього списку, дайте мені знати в розділі коментарів.
Примітка: Я доклав максимум зусиль, щоб прочитати цей підручник, але у разі виявлення помилок чи орфографічних помилок, будь ласка, дайте мені знати 🙂
Функція Excel INDEX - Відеопосібник
- Функція Excel VLOOKUP.
- Функція Excel HLOOKUP.
- Непряма функція Excel.
- Функція Excel MATCH.
- Функція Excel OFFSET.
Вам також можуть сподобатися такі підручники Excel:
- VLOOKUP Vs. ІНДЕКС/МАТЧ
- Збіг індексів Excel
- Значення пошуку та повернення у цілому рядку/стовпці.
