Цей підручник навчить вас використовувати комбінацію INDEX & MATCH для виконання пошуку в Excel і Google Таблицях.
INDEX & MATCH, Ідеальна пара
Давайте детальніше розглянемо деякі способи поєднання функцій INDEX та MATCH. Функція MATCH призначена для повернення відносного положення елемента в масиві, тоді як функція INDEX може отримати елемент з масиву з певною позицією. Ця синергія між ними дозволяє виконувати практично будь -який тип пошуку, який вам може знадобитися.
Комбінація INDEX / MATCH історично використовувалася як заміна функції VLOOKUP. Однією з основних причин є можливість робити пошук ліворуч (див. Наступний розділ).
Примітка: нова функція XLOOKUP тепер може виконувати пошук уліво.
Пошук ліворуч
Давайте скористаємось цією таблицею статистики баскетболу:
Ми хочемо знайти гравця Боба #. Оскільки гравець № знаходиться ліворуч від стовпця імені, ми не можемо використовувати VLOOKUP.
Натомість ми могли б виконати базовий запит MATCH для обчислення рядка Боба
= MATCH (H2, B2: B5, 0)
Це буде шукати точну відповідність слова "Боб", і тому наша функція поверне число 2, оскільки "Боб" знаходиться у 2nd положення.
Далі ми можемо використовувати функцію INDEX для повернення гравця #, відповідного рядку. Поки що давайте просто вручну введемо «2» у функцію:
= ІНДЕКС (A2: A5, 2)
Тут INDEX посилатиметься на A3, оскільки це 2nd клітинку в діапазоні A2: A5 і повернути результат 42. Для нашої загальної мети ми можемо потім об’єднати ці дві частини у:
= ІНДЕКС (A2: A5, MATCH (H2, B2: B5, 0))
Перевага тут у тому, що нам вдалося повернути результат зі стовпця ліворуч від місця, де ми шукали.
Двовимірний пошук
Давайте подивимось на нашу таблицю від раніше:
Цього разу ми хочемо отримати конкретну статистику. Ми оголосили, що хочемо шукати відскоки у клітині Н1. Замість того, щоб писати кілька операторів IF, щоб визначити, з якого стовпця отримати результат, ви можете знову використовувати функцію MATCH. Функція INDEX дозволяє вказати значення рядка та значення стовпця. Ми збираємось додати сюди ще одну функцію MATCH, щоб визначити, який стовпець ми хочемо. Це буде виглядати так
= MATCH (H1, A1: E1, 0)
Наша клітинка в H1 - це випадаючий список, який дозволяє нам вибрати, яку категорію ми хочемо шукати, а потім наш MATCH визначає, до якого стовпця в таблиці належить. Давайте додамо цей новий фрагмент до нашої попередньої формули. Зауважте, що нам потрібно налаштувати перший аргумент на два виміри, оскільки нам більше не потрібен результат із стовпця А.
= ІНДЕКС (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
У нашому прикладі ми хочемо знайти відскоки для Чарлі. Наша формула буде оцінювати це так:
= INDEX (A2: E5, MATCH ("Чарлі", B2: B5, 0), MATCH ("Відскоки", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Тепер ми створили гнучку установку, яка дозволяє користувачеві отримувати будь -яке значення з нашої таблиці без необхідності писати декілька формул або розгалужених операторів IF.
Кілька розділів
Він використовується не часто, але INDEX має п'ятий аргумент, який можна надати, щоб визначити, який площі в межах аргументу один для використання. Це означає, що нам потрібен спосіб передати кілька областей у перший аргумент. Ви можете зробити це, використовуючи додатковий набір дужок. Цей приклад ілюструє, як можна отримати результати з різних таблиць на аркуші за допомогою INDEX.
Ось макет, який ми будемо використовувати. У нас є статистика за три різні чверті гри.
У клітинках H1: H3 ми створили розкривні списки перевірки даних для різних варіантів вибору. Випадаючий список за квартал надходить з J2: J4. Ми будемо використовувати це для іншого оператора MATCH, щоб визначити, яку область використовувати. Наша формула в H4 буде виглядати так:
= ІНДЕКС ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Ми вже обговорювали, як працюють дві внутрішні функції MATCH, тому давайте зосередимось на першому та останньому аргументах:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Ми надали функції INDEX кілька масивів у першому аргументі, уклавши їх усі в дужки. Інший спосіб зробити це за допомогою формул - Визначте ім'я. Ви можете визначити назву під назвою «Мої таблиці» з визначенням
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Повернемося до всього висловлювання. Наші різні функції MATCH скажуть функції INDEX, де саме шукати. Спочатку ми визначимо, що «Чарлі» - це 3rd рядок. Далі, ми хочемо "Відскоків", який є 4го стовпчик. Нарешті, ми визначили, що хочемо результату від 2nd таблиці. Формула буде оцінюватись так:
= ІНДЕКС ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Як ми згадували на початку цього прикладу, ви обмежуєтесь тим, що таблиці будуть на одному робочому аркуші. Якщо ви можете виписати правильні способи повідомити вашому INDEX, з якого рядка, стовпця та/або області ви хочете отримати дані, INDEX буде дуже корисним для вас.
Google Таблиці -ІНДЕКС & МАТЧ
Усі наведені вище приклади працюють точно так само в Google Таблицях, як і в Excel.
