Skip to content

Як використовувати функцію INDEX та MATCH у програмі Excel

27 de Липень de 2021

Що знати

  • Функцію INDEX можна використовувати окремо, але вкладання в неї функції MATCH створює розширений пошук.
  • Ця вкладена функція є більш гнучкою, ніж VLOOKUP, і може приносити результати швидше.

У цій статті пояснюється, як використовувати функції INDEX та MATCH разом у всіх версіях Excel, включаючи Excel 2019 та Microsoft 365.

Що таке функції INDEX та MATCH?

INDEX та MATCH – це функції пошуку Excel. Хоча це дві цілком окремі функції, які можна використовувати самостійно, їх також можна комбінувати для створення розширених формул. Функція INDEX повертає значення або посилання на значення з певного виділення. Наприклад, його можна використовувати для пошуку значення у другому рядку набору даних, або у п’ятому рядку та третьому стовпці. Хоча INDEX цілком можна використовувати окремо, вкладання MATCH у формулу робить його трохи кориснішим. Функція MATCH шукає вказаний елемент у діапазоні комірок, а потім повертає відносне положення елемента в діапазоні. Наприклад, за його допомогою можна визначити, що конкретна назва є третім елементом у списку імен.

INDEX та MATCH Синтаксис та аргументи

Ось як потрібно писати обидві функції, щоб Excel їх розумів:

= ІНДЕКС(масив, номер_ряду, [column_num])

  • масив – діапазон комірок, які буде використовувати формула. Це може бути один або кілька рядків і стовпців, наприклад A1: D5. Це потрібно.
  • номер_ряду – це рядок у масиві, з якого потрібно повернути значення, наприклад 2 або 18. Це потрібно, якщо номер_столбця присутній.
  • номер_столбця – це стовпець у масиві, з якого потрібно повернути значення, таке як 1 або 9. Це необов’язково.

= МАТЧ(lookup_value, масив пошуку, [match_type])

  • lookup_value – це значення, з яким потрібно зіставити масив пошуку. Це може бути число, текст або логічне значення, яке вводиться вручну або на яке посилається за допомогою посилання на клітинку. Це потрібно.
  • масив пошуку – діапазон клітин, які потрібно переглянути. Це може бути один рядок або один стовпець, наприклад A2: D2 або G1: G45. Це потрібно.
  • тип_збігу може бути -1, 0, або 1. Він визначає, як lookup_value відповідає значенням у масив пошуку (Дивись нижче). 1 – значення за замовчуванням, якщо цей аргумент пропущено.
Який тип відповідності використовувати
Тип відповідності Що це робить Правило Приклад
1 Знаходить найбільше значення, яке менше або дорівнює lookup_value. масив пошуку значення слід розміщувати у порядку зростання (наприклад, -2, -1, 0, 1, 2; або AZ ;, або FALSE, TRUE. lookup_value становить 25, але його немає масив пошуку, тож замість цього повертається позиція наступного найменшого числа, наприклад 22.
0 Знаходить перше значення, якому точно дорівнює lookup_value. масив пошуку значення можуть бути в будь-якому порядку. lookup_value дорівнює 25, тож повертає позицію 25.
-1 Знаходить найменше значення, яке більше або дорівнює lookup_value. масив пошуку значення слід розміщувати у порядку зменшення (наприклад, 2, 1, 0, -1, -2). lookup_value становить 25, але його немає масив пошуку, отже, замість цього повертається позиція наступного найбільшого числа, наприклад 34.

Використовуйте 1 або -1 для випадків, коли вам потрібно виконати приблизний пошук за шкалою, наприклад, коли йдеться про числа, і коли апроксимації в порядку. Але пам’ятайте, що якщо ви не вкажете тип_збігу, 1 буде типовим, що може спотворити результати, якщо ви дійсно хочете точно збігтися.

Приклади формул INDEX та MATCH

Перш ніж ми розглянемо, як поєднати INDEX і MATCH в одну формулу, нам слід зрозуміти, як ці функції працюють самі по собі.

ПРИКЛАДИ INDEX

= ІНДЕКС (A1: B2,2,2)
= ІНДЕКС (A1: B1,1)
= ІНДЕКС (2: 2,1)
= ІНДЕКС (B1: B2,1)

INDEX Приклади формул Excel

У цьому першому прикладі є чотири формули INDEX, які ми можемо використовувати для отримання різних значень:

  • = ІНДЕКС (A1: B2,2,2) переглядає A1: B2, щоб знайти значення у другому стовпці та другому рядку, що є Стейсі.
  • = ІНДЕКС (A1: B1,1) переглядає A1: B1, щоб знайти значення в першому стовпці, тобто Джон.
  • = ІНДЕКС (2: 2,1) переглядає все у другому рядку, щоб знайти значення в першому стовпці, тобто Тім.
  • = ІНДЕКС (B1: B2,1) переглядає B1: B2, щоб знайти значення в першому рядку, тобто Емі.

ПРИКЛАДИ МАТЧ

= МАТЧ (“Стейсі”, A2: D2,0)
= МАТЧ (14, D1: D2)
= МАТЧ (14, D1: D2, -1)
= МАТЧ (13, A1: D1,0)

Приклади функцій MATCH в Excel

Ось чотири простих приклади функції MATCH:

  • = МАТЧ (“Стейсі”, A2: D2,0) шукає Стейсі в діапазоні A2: D2 і повертається 3 як результат.
  • = МАТЧ (14, D1: D2) шукає 14 в діапазоні D1: D2, але оскільки його немає в таблиці, MATCH знаходить наступний найбільше значення, яке менше або дорівнює 14, що в даному випадку є 13, який знаходиться в положенні 1 з масив пошуку.
  • = МАТЧ (14, D1: D2, -1) ідентична формулі над ним, але оскільки масив не в порядку зменшення, як -1 вимагає, ми отримуємо помилку.
  • = МАТЧ (13, A1: D1,0) шукає 13 у першому рядку аркуша, який повертається 4 оскільки це четвертий елемент у цьому масиві.

ПРИКЛАДИ ІНДЕКС-МАТЧ

Ось два приклади, коли ми можемо поєднати INDEX та MATCH в одній формулі:

Знайдіть посилання на комірку в таблиці

= ІНДЕКС (B2: B5, MATCH (F1, A2: A5))

Функції MATCH та INDEX вкладені в одну формулу

У цьому прикладі вкладено формулу MATCH у формулу INDEX. Мета – визначити колір товару за номером товару. Якщо ви подивитеся на зображення, то в рядках “Розділені” ви побачите, як формули писались би самі по собі, але оскільки ми їх вкладаємо, відбувається ось що:

  • МАТЧ (F1, A2: A5) шукає F1 значення (8795) у наборі даних A2: A5. Якщо відрахувати стовпець, ми можемо побачити його 2, отже, ось що саме з’ясувала функція MATCH.
  • Масив INDEX становить В2: В5 оскільки ми в кінцевому рахунку шукаємо значення в цьому стовпці.
  • Функція INDEX тепер може бути переписана таким чином з тих пір 2 ось що знайшов MATCH: ІНДЕКС (B2: B5, 2, [column_num]).
  • Оскільки номер_столбця необов’язково, ми можемо видалити те, що залишиться з цим: ІНДЕКС (B2: B5,2).
  • Отже, це як звичайна формула INDEX, де ми знаходимо значення другого елемента в В2: В5, який є червоний.

Пошук за заголовками рядків і стовпців

= ІНДЕКС (B2: E13, MATCH (G1, A2: A13,0), MATCH (G2, B1: E1,0))

Приклад вкладеності INDEX та MATCH в Excel

У цьому прикладі MATCH та INDEX ми виконуємо двосторонній пошук. Ідея полягає в тому, щоб побачити, скільки грошей ми заробили Зелений пункти в Може. Це дійсно схоже на приклад вище, але додаткова формула MATCH вкладена в INDEX.

  • МАТЧ (G1, A2: A13,0) є першим пунктом, вирішеним у цій формулі. Це шукає G1 (слово “травень”) в A2: A13 щоб отримати певне значення. Ми цього не бачимо тут, але це так 5.
  • МАТЧ (G2, B1: E1,0) є другою формулою MATCH, і вона насправді схожа на першу, але замість неї шукається G2 (слово “Зелений”) у заголовках стовпців на B1: E1. Цей вирішує 3.
  • Тепер ми можемо переписати формулу INDEX так, щоб візуалізувати, що відбувається: = ІНДЕКС (B2: E13,5,3). Це дивиться в цілу таблицю, B2: E13, для п’ятого рядка і третього стовпця, який повертається 180 доларів.

Правила відповідності та індексу

При написанні формул із цими функціями слід пам’ятати декілька речей:

  • MATCH не чутливий до регістру, тому великі та малі літери обробляються однаково при збігу текстових значень.
  • MATCH повертається # Не застосовується з багатьох причин: якщо тип_збігу є 0 і lookup_value не знайдено, якщо тип_збігу є -1 і масив пошуку не в порядку зменшення, якщо тип_збігу є 1 і масив пошуку не у порядку зростання, і якщо масив пошуку не є одним рядком або стовпцем.
  • Ви можете використовувати символ підстановки в lookup_value аргумент if тип_збігу є 0 і lookup_value є текстовим рядком. Знак питання відповідає будь-якому окремому символу, а зірочка відповідає будь-якій послідовності символів (наприклад, = MATCH (“Jo *”, 1: 1,0)). Щоб за допомогою функції MATCH знайти справжній знак запитання чи зірочку, введіть ~ спочатку.
  • INDEX повертається #REF! якщо номер_ряду і номер_столбця не вказуйте на клітинку в масиві.

Пов’язані функції Excel

Функція MATCH схожа на LOOKUP, але MATCH повертає положення товару замість самого предмета. VLOOKUP – це ще одна функція пошуку, яку ви можете використовувати в Excel, але на відміну від MATCH, яка вимагає INDEX для розширених пошуків, формулам VLOOKUP потрібна лише одна функція. Більше від tebapit

  • Бізнесмен, використовуючи ноутбук в офісі зустрічі

    Як створити формулу пошуку Excel з кількома критеріями

  • ПЕРЕГЛЯД Функції Excel Приклади

    Як використовувати функцію LOOKUP у програмі Excel

  • Хтось працює над електронною таблицею Excel на портативному комп’ютері.

    INDEX-MATCH проти VLOOKUP в Excel

  • Microsoft Excel на екрані комп'ютера.

    Як користуватися функцією Excel INDEX

  • Стовпчаста діаграма ілюструє описову статистику.  З’ясуйте, чим вони відрізняються від статистичних даних.

    Як використовувати функцію збігу Excel для пошуку значення

  • Аналітик даних за допомогою ноутбука для перегляду електронних таблиць

    Як використовувати функцію пошуку в Excel для пошуку інформації

  • Приклади функцій IF у програмі Excel

    Як використовувати функцію IF у програмі Excel

  • Таблиці Google, що працюють на ноутбуці

    Як використовувати ARRAYFORMULA в Google Sheets

  • Ілюстрація людини, що використовує Excel для поєднання функцій Round і Sum

    Як поєднати функції ROUND та SUM у програмі Excel

  • Безпосередньо над пострілом лупи на столі

    Швидко знаходьте кілька полів даних за допомогою функції VLOOKUP Excel

  • Зображення пошукового скла над електронною таблицею

    Як користуватися функцією XLOOKUP в Excel

  • Студент, що навчається на своєму ноутбуку в домашньому офісі

    Як використовувати абсолютне посилання на клітинку в Excel

  • Дані в аркуші Excel

    Знайдіть найбільше від’ємне чи додатне число в Excel

  • Функція VLOOKUP в Google Sheets

    Як використовувати VLOOKUP в Google Таблицях

  • Використання функції ROUNDUP в Excel заощаджує час.

    Як створити базу даних в Excel

  • Логотип Microsoft Excel

    Як створити формулу лівого пошуку в Excel за допомогою VLOOKUP

tebapit

Слідуй за нами

  • Facebook

  • Про нас
  • Рекламуйте
  • Політика конфіденційності
  • Політика щодо файлів cookie
  • Кар’єра
  • Редакційні вказівки
  • Зв’язок
  • Умови використання
  • Конфіденційність ЄС
  • Повідомлення про конфіденційність у Каліфорнії
  • ДОВІРЯ



Ваші права на конфіденційність

tebapit та наші сторонні партнери використовують файли cookie та обробляють особисті дані, такі як унікальні ідентифікатори, на основі вашої згоди зберігати та / або отримувати доступ до інформації на пристрої, показувати персоналізовану рекламу та для вимірювання вмісту, аналізу аудиторії та розробки продукту. Щоб змінити або скасувати вибір вашої згоди для tebapit.com, включаючи ваше право на заперечення, де використовується законний інтерес, натисніть нижче. Ви можете будь-коли оновити свої налаштування за посиланням “Конфіденційність ЄС” внизу будь-якої сторінки. Цей вибір буде повідомлений нашим партнерам у всьому світі і не впливатиме на дані веб-перегляду. Список партнерів (постачальників)

Ми та наші партнери обробляємо дані для:

Активно скануйте характеристики пристрою для ідентифікації. Використовуйте точні дані геолокації. Зберігайте та / або отримуйте доступ до інформації на пристрої. Виберіть персоналізований вміст. Створіть персоналізований профіль вмісту. Вимірюйте ефективність реклами. Виберіть основні оголошення. Створіть профіль персоналізованої реклами. Виберіть персоналізовану рекламу. Застосовуйте дослідження ринку, щоб генерувати уявлення про аудиторію. Виміряйте продуктивність вмісту. Розробляти та вдосконалювати продукти. Список партнерів (постачальників)