Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як використовувати INDEX і MATCH разом в Excel

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

Що роблять функції INDEX і MATCH в Excel

Як використовувати функції INDEX і MATCH разом


Що роблять функції INDEX і MATCH в Excel

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

Використання функції INDEX в Excel

повне г, повне г,, показали, від, номер, XNUMX ІНДЕКС функція в Excel повертає значення в заданому місці в певному діапазоні. Синтаксис функції INDEX такий:

=INDEX(array, row_num, [column_num])
  • масив (обов’язковий) відноситься до діапазону, з якого потрібно повернути значення.
  • номер_рядка (обов'язковий, якщо не присутній column_num) відноситься до номера рядка масиву.
  • номер_столбця (необов’язковий, але обов’язковий, якщо номер_рядка опущено) посилається на номер стовпця масиву.

Наприклад, знати підсумковий іспит Джеффа, 6-й студент у списку, ви можете використовувати функцію INDEX так:

=ІНДЕКС(E2:E11, 6) >>> повертається 60

збіг індексу Excel 01

√ Примітка: діапазон E2: E11 є місце, де вказано підсумковий іспит, а номер 6 знаходить екзаменаційний бал 6й студент.

Ось давайте зробимо невеличкий тест. Для формули =ІНДЕКС(B2:E2,3), яке значення він поверне? ---Так, воно повернеться Китай, то 3rd значення в заданому діапазоні.

Тепер ми повинні знати, що функція INDEX може ідеально працювати з горизонтальними або вертикальними діапазонами. Але що, якщо нам потрібно повернути значення в більшому діапазоні з кількома рядками і стовпцями? Ну, в цьому випадку ми повинні застосувати і номер рядка, і номер стовпця. Наприклад, щоб дізнатися країна, з якої родом Емілі за допомогою INDEX ми можемо знайти значення з номером рядка 8 і номером стовпця 3 в клітинках від B2 до E11 так:

=ІНДЕКС(B2:E11,8,3) >>> повертається Китай

збіг індексу Excel 02

Згідно з наведеними вище прикладами, про функцію INDEX в Excel, ви повинні знати, що:

  • Функція INDEX може працювати з вертикальними та горизонтальними діапазонами.
  • Функція INDEX не чутлива до регістру.
  • Номер рядка передує номеру стовпця (якщо потрібні обидва числа) у формулі INDEX.

Однак для дійсно великої бази даних із кількома рядками та стовпцями нам, звичайно, не зручно застосовувати формулу з точним номером рядка та стовпця. І це саме тоді, коли ми повинні комбінувати використання функції MATCH.

Тепер давайте спочатку дізнаємося про основи функції MATCH.


Використання функції MATCH в Excel

Функція MATCH в Excel повертає числове значення, розташування певного елемента в заданому діапазоні. Синтаксис функції MATCH виглядає наступним чином:

=MATCH(lookup_value, lookup_array, [match_type])
  • пошуковий_масив (обов’язковий) відноситься до діапазону комірок, у яких потрібно шукати MATCH.
  • match_type (необов'язково), 1, 0 or -1:
  • 1(за замовчуванням) MATCH знайде найбільше значення, яке менше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку зростання.
  • 0, MATCH знайде перше значення, яке точно дорівнює lookup_value. Значення в пошуковий_масив може бути в будь-якому порядку. (У випадках, коли тип відповідності встановлено на 0, ви можете використовувати символи підстановки.)
  • -1, MATCH знайде найменше значення, яке більше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку спадання.

Наприклад, знати місце Віри в іменному списку, ви можете використовувати формулу MATCH так:

= MATCH ("віра", C2: C11,0) >>> повертається 4

збіг індексу Excel 03

√ Примітка: функція MATCH не чутлива до регістру. Результат «4» свідчить про те, що ім’я «Віра» знаходиться на 4-й позиції списку. «0» у формулі – це тип відповідності, який знайде перше значення в масиві пошуку, яке точно дорівнює значенню пошуку «Vera».

Знати позиція оцінки «96» у рядку від В2 до Е2, ви можете використовувати MATCH так:

=СПІВНИК(96,B2:E2,0) >>> повертається 4

збіг індексу Excel 04

☞ Що ми повинні знати про функцію MATCH в Excel:

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

Тепер, коли ми знаємо про основні способи використання функцій INDEX і MATCH в Excel, давайте засукаємо рукави та приготуємося до поєднання двох функцій.


Як використовувати функції INDEX і MATCH разом

У цій частині ми поговоримо про різні обставини використання функцій INDEX і MATCH для задоволення різних потреб.

Приклад поєднання INDEX і MATCH

Перегляньте приклад нижче, щоб зрозуміти, як ми можемо поєднати функції INDEX і MATCH:

Наприклад, знати Оцінка підсумкового іспиту Евелін, ми повинні використовувати формулу:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> повертається 90

збіг індексу Excel 05

Ну, оскільки формула може виглядати складною, давайте розглянемо кожну її частину.

збіг індексу Excel 06

Як бачите вище, великий ІНДЕКС формула містить три аргументи:

  • масив: A2: D11 вказує INDEX повернути відповідне значення з комірок через A2 до D11.
  • номер_рядка: MATCH("evelyn",B2:B11,0) повідомляє INDEX точний рядок значення.
  • Що стосується формули MATCH, то ми можемо пояснити це так: повернути позицію першого значення, що точно дорівнює “evelyn” у клітинках від B2 до B11 у числовому значенні, яке 5.
  • номер_столбця: MATCH("підсумковий іспит",A1:D1,0) повідомляє INDEX точний стовпець значення.
  • Щодо формули MATCH, ми можемо пояснити це так: повернути позицію першого значення, яка точно дорівнює “випускному іспиту” у клітинках від A1 до D1 у числовому значенні, що 4.

Отже, ви можете побачити велику формулу таку ж просту, як та, яку ми показали нижче:

= ІНДЕКС (A2: D11,5,4)

У прикладі ми використали жорстко закодовані значення, «evelyn» та «final exam». Однак у такій великій формулі нам не потрібні жорстко закодовані значення, оскільки нам доведеться їх змінювати щоразу, коли ми збираємося шукати щось нове. За таких обставин ми можемо використовувати посилання на клітинки, щоб зробити формулу динамічною так:

= ІНДЕКС (A2: D11,МАТЧ (G2,B2:B11,0),МАТЧ (F3,A1:D1,0))

збіг індексу Excel 07


INDEX і MATCH, щоб застосувати пошук зліва

Тепер, припустимо, вам потрібно знати клас Евелін, як ми можемо використовувати INDEX і MATCH, щоб дізнатися відповідь? Якщо ви звернули увагу, ви повинні помітити, що стовпець класу знаходиться в лівій частині стовпця імені, і це поза межами можливостей іншої потужної функції пошуку Excel, VLOOKUP.

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

Знати Клас Евелін, все, що вам потрібно зробити, це змінити значення в клітинці F3 на «Клас» і використати ту саму формулу, що показано вище, функції INDEX і MATCH відразу покажуть вам відповідь:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> повертається A

збіг індексу Excel 08

Якщо у вас є інсталяціяпризвело Kutools для Excel, професійний додаток Excel, розроблений нашою командою, ви також можете застосувати пошук зліва для вказаних значень за допомогою його ПЕРЕГЛЯД справа наліво функція в кілька кліків. Щоб реалізувати цю функцію, перейдіть до Кутулс вкладку у вашому Excel, знайдіть Formula групу та натисніть ПЕРЕГЛЯД справа наліво у випадаючому списку Супер ПОГЛЯД. Ви побачите спливаюче діалогове вікно такого типу:

збіг індексу Excel 09

Натисніть тут, щоб переглянути конкретні кроки, щоб застосувати функцію пошуку ліворуч за допомогою Kutools for Excel.


INDEX і MATCH, щоб застосувати двосторонній пошук

Тепер чи можете ви створити формулу комбінації INDEX і MATCH із значеннями динамічного пошуку для застосування двостороннього пошуку? Давайте потренуємося складати формули в клітинках G3, G4 і G5, як показано нижче:

збіг індексу Excel 10

Ось відповіді:

Клітинка G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Клітинка G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Клітинка G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Примітка: після застосування формул ви можете легко отримати інформацію про будь-яких учнів, змінивши ім’я в клітинці G2.


INDEX і MATCH, щоб застосувати пошук з урахуванням регістру

З наведених вище прикладів ми знаємо, що функції INDEX та MATCH не чутливі до регістру. Однак у випадках, коли вам потрібна формула для розрізнення символів верхнього та нижнього регістру, ви можете додати EXACT функціонувати до ваших формул так:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Enter. Тоді в рядку формул з’явиться пара фігурних дужок.

Наприклад, знати Оцінка екзамену ДЖІММІ, використовуйте такі функції:

збіг індексу Excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> повертається 86

Або ви можете використовувати посилання на клітинки:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> повертається 86
Примітка: не забудьте ввести з Ctrl + Shift + Enter.


INDEX і MATCH, щоб застосувати пошук за кількома критеріями

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

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Примітка: це формула масиву, яка вимагає введення Ctrl + Shift + Enter. Тоді в рядку формул з’явиться пара фігурних дужок.

Наприклад, щоб знайти підсумковий іспит Коко з класу А, яка родом з Індії, формула виглядає так:

збіг індексу Excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> повертається 88
√ Примітка: не забудьте ввести з Ctrl + Shift + Enter.

Ну а що робити, якщо постійно забуваєш використовувати Ctrl + Shift + Enter щоб завершити формулу так, щоб формула повернула неправильні результати? Тут у нас є більш складна формула, за допомогою якої ви можете просто завершити одну просту вводити ключ:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Для того ж прикладу вище, щоб знайти підсумковий іспит Коко з класу А, яка родом з Індії, формула, яка потребує лише звичайного вводити удар виглядає наступним чином:

збіг індексу Excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> повертається 88

Тут ми не будемо використовувати жорстко закодовані значення, оскільки нам потрібна універсальна формула у випадку з кількома критеріями. Тільки таким чином ми зможемо легко отримати потрібний результат, змінивши значення в клітинках G2, G3, G4 у прикладі вище.

з Kutools для Excel"s Функція пошуку за кількома умовами, за кілька кліків можна шукати певні значення з кількома критеріями. Щоб реалізувати цю функцію, перейдіть до Кутулс вкладку у вашому Excel, знайдіть Formula групу та натисніть Пошук кількох умов у випадаючому списку Супер ПОГЛЯД. Після цього ви побачите спливаюче діалогове вікно, як показано нижче:

збіг індексу Excel 14

Натисніть тут, щоб переглянути конкретні кроки, щоб застосувати функцію пошуку за багатьма умовами за допомогою Kutools for Excel.


INDEX і MATCH, щоб застосувати пошук у кількох стовпцях

Якщо у нас є електронна таблиця Excel з різними стовпцями з одним підписом, як показано нижче, як ми можемо зіставити ім’я кожного учня з його/її класом за допомогою INDEX і MATCH?

збіг індексу Excel 15

Тут дозвольте мені показати вам спосіб виконання завдання за допомогою нашого професійного інструменту Kutools для Excel. З його Помічник формул, Ви можете швидко зіставити учнів з їхніми класами, як показано нижче:

1. Виберіть клітинку призначення, до якої потрібно застосувати функцію.

2 Під Кутулс вкладка, перейдіть до Помічник формули, Натисніть Помічник формули у випадаючому списку.

збіг індексу Excel 16

3. вибирати Пошук із типу формули, потім натисніть на Вказівник та збіг у кількох стовпцях.

збіг індексу Excel 17

4. а. Натисніть 1-й значок відповідності індексу Excelкнопка з правого боку Пошук_кол щоб вибрати клітинки, з яких потрібно повернути значення, тобто імена класів. (Тут можна вибрати лише один стовпець або рядок.)
    б. Натисніть 2-й значок відповідності індексу Excelкнопка з правого боку Table_rng щоб вибрати клітинки, які відповідають значенням у вибраному Пошук_кол, тобто імена учнів.
    c. Натисніть 3-й значок відповідності індексу Excelкнопка з правого боку Пошук_значення щоб вибрати клітинку для пошуку, тобто ім’я учня, яке ви хочете зіставити з його/її класом.

збіг індексу Excel 18

5. Натисніть Ok, ви побачите ім'я класу Джиммі, яке з'явиться в клітинці призначення.

збіг індексу Excel 19

6. Тепер ви можете перетягнути маркер заповнення вниз, щоб заповнити класи інших учнів.

збіг індексу Excel 20

Натисніть, щоб завантажити Kutools для Excel для 30-денної безкоштовної пробної версії.



Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.