INDEX і MATCH у кількох стовпцях
Щоб знайти значення шляхом зіставлення в кількох стовпцях, формула масиву на основі ІНДЕКС та MATCH функції, що включає ММУЛЬТ, ТРАНЗОЗУВАТИ та КОЛОНКА зробить тобі послугу.
Як знайти значення, зіставляючи кілька стовпців?
Щоб заповнити відповідний клас кожного учня як показано у таблиці вище, де інформація наведена в кількох стовпцях, ви можете спочатку скористатися трюком функцій MMULT, TRANSPOSE і COLUMN, щоб створити масив матриць. Тоді функція MATCH дасть вам позицію вашого значення пошуку, яке буде подано в INDEX для отримання значення, яке ви шукаєте в масиві.
Родовий синтаксис
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .
- діапазон_повернення: Діапазон, з якого ви хочете, щоб формула повертала інформацію про клас. Тут йдеться про діапазон класів.
- пошукове_значення: Значення, використане формулою для пошуку відповідної інформації про клас. Тут йдеться про дане ім'я.
- пошуковий_масив: Діапазон клітинок, де lookup_value перераховано; Діапазон зі значеннями для порівняння з lookup_value. Тут йдеться про діапазон імен.
- match_type 0: Змушує MATCH знайти перше значення, яке точно дорівнює lookup_value.
Щоб знайти клас Джиммі, скопіюйте або введіть формулу нижче в клітинку Н5 і натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у щоб отримати результат:
= ІНДЕКС ($ B $ 5: $ B $ 7,(СПІВНИК(1,МУЛЬТИ(--($ C $ 5: $ e $ 7=G5),ТРАНСПОЗУВАННЯ(СТОПЦЕЦЬ($ C $ 5: $ e $ 7)^0), 0))
√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, що означає, що назви та діапазони класів у формулі не зміняться, коли ви переміщуєте чи копіюєте формулу в інші клітинки. Зауважте, що ви не повинні додавати знаки долара до посилання на клітинку, яка представляє значення пошуку, оскільки ви хочете, щоб воно було відносним, коли ви копіюєте його в інші клітинки. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.
Пояснення формули
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- ---($C$5:$E$7=G5): Цей сегмент перевіряє кожне значення в діапазоні $ C $ 5: $ e $ 7 якщо вони дорівнюють значенню в комірці G5, і генерує масив TRUE та FALSE, наприклад:
{TRUE, FALSE, FALSE; FALSE, FALSE, FALSE; FALSE, FALSE, FALSE}.
Подвійний негатив потім перетворить значення TRUE і FALSE в 1s і 0s, щоб отримати такий масив:
{1,0,0; 0,0,0; 0,0,0}. - СТОВПЕЦЬ($C$5:$E$7): Функція COLUMN повертає номери стовпців для діапазону $ C $ 5: $ e $ 7 в такому масиві: 3,4,5 {}.
- Перекласти (СТОВПЕЦЬ($C$5:$E$7)^0) = Перекласти (3,4,5 {}^0): Після підвищення ступеня до 0 всі числа в масиві {3,4,5} будуть перетворені в 1: {1,1,1}. Потім функція TRANSPOSE перетворює масив стовпців у масив рядків так: {1; 1; 1}.
- Mmult (---($C$5:$E$7=G5),Перекласти (СТОВПЕЦЬ($C$5:$E$7)^0)) = Mmult ({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Функція MMULT повертає матричний добуток двох масивів таким чином: {1; 0; 0}.
- МАТЧ (1,Mmult (---($C$5:$E$7=G5),Перекласти (СТОВПЕЦЬ($C$5:$E$7)^0)), 0) = МАТЧ (1,{1; 0; 0}, 0): Тип збігу 0 змушує функцію MATCH повертати позицію першого збігу 1 в масиві {1; 0; 0}, Яка є 1.
- ІНДЕКС ($ B $ 5: $ B $ 7,(СПІВНИК(1,Mmult (---($C$5:$E$7=G5),Перекласти (СТОВПЕЦЬ($C$5:$E$7)^0)), 0))) = ІНДЕКС ($ B $ 5: $ B $ 7,1): Функція INDEX повертає 1st значення в діапазоні класів $ B $ 5: $ B $ 7, Яка є A.
Щоб легко знайти значення шляхом зіставлення в кількох стовпцях, ви також можете використовувати нашу професійну надбудову Excel Kutools для Excel. Перегляньте інструкцію тут, щоб виконати місію.
Супутні функції
Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
Функція Excel MMULT повертає матричний добуток двох масивів. Результат масиву має таку саму кількість рядків, що й масив1, і ту ж кількість стовпців, що й масив2.
Функція Excel TRANSPOSE повертає орієнтацію діапазону або масиву. Наприклад, він може обертати таблицю, розташовану горизонтально в рядках, на вертикальну в стовпцях або навпаки.
Функція COLUMN повертає номер стовпця, у якому з’являється формула, або повертає номер стовпця даного посилання. Наприклад, формула =COLUMN(BD) повертає 56.
Пов’язані формули
Пошук за кількома критеріями за допомогою INDEX та MATCH
Коли ви маєте справу з великою базою даних у електронній таблиці Excel з кількома стовпцями та підписами рядків, завжди складно знайти щось, що відповідає кільком критеріям. У цьому випадку можна використовувати формулу масиву з функціями INDEX і MATCH.
Двосторонній пошук з INDEX та MATCH
Щоб шукати щось у рядках і стовпцях в Excel, або ми кажемо шукати значення на перетині певного рядка і стовпця, ми можемо використовувати функції INDEX і MATCH.
Шукати значення найближчого відповідності з кількома критеріями
У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі більш ніж одного критерію. Завдяки поєднанню функцій INDEX, MATCH та IF ви можете швидко це зробити в Excel.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
