Note: The other languages of the website are Google-translated. Back to English

INDEX і MATCH у кількох стовпцях

Щоб знайти значення шляхом зіставлення в кількох стовпцях, формула масиву на основі ІНДЕКС і MATCH функції, що включає ММУЛЬТ, ТРАНЗОЗУВАТИ і КОЛОНКА зробить тобі послугу.

індекс відповідає кільком стовпцям 1

Як знайти значення, зіставляючи кілька стовпців?

Щоб заповнити відповідний клас кожного учня як показано у таблиці вище, де інформація наведена в кількох стовпцях, ви можете спочатку скористатися трюком функцій 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)))

√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, що означає, що назви та діапазони класів у формулі не зміняться, коли ви переміщуєте чи копіюєте формулу в інші клітинки. Зауважте, що ви не повинні додавати знаки долара до посилання на клітинку, яка представляє значення пошуку, оскільки ви хочете, щоб воно було відносним, коли ви копіюєте його в інші клітинки. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок.

індекс відповідає кільком стовпцям 2

Пояснення формули

=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 {}.
  • TRANSPOSE(СТОВПЕЦЬ($C$5:$E$7)^0) = TRANSPOSE(3,4,5 {}^0): Після підвищення ступеня до 0 всі числа в масиві {3,4,5} будуть перетворені в 1: {1,1,1}. Потім функція TRANSPOSE перетворює масив стовпців у масив рядків так: {1; 1; 1}.
  • MMULT(---($C$5:$E$7=G5),TRANSPOSE(СТОВПЕЦЬ($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),TRANSPOSE(СТОВПЕЦЬ($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),TRANSPOSE(СТОВПЕЦЬ($C$5:$E$7)^0)), 0))) = ІНДЕКС ($ B $ 5: $ B $ 7,1): Функція INDEX повертає 1st значення в діапазоні класів $ B $ 5: $ B $ 7, Яка є A.

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


Супутні функції

Функція Excel INDEX

Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.

Функція Excel MATCH

Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.

Функція Excel MMULT

Функція Excel MMULT повертає матричний добуток двох масивів. Результат масиву має таку саму кількість рядків, що й масив1, і ту ж кількість стовпців, що й масив2.

Функція Excel TRANSPOSE

Функція Excel TRANSPOSE повертає орієнтацію діапазону або масиву. Наприклад, він може обертати таблицю, розташовану горизонтально в рядках, на вертикальну в стовпцях або навпаки.

Функція Стовпець Excel

Функція COLUMN повертає номер стовпця, у якому з’являється формула, або повертає номер стовпця даного посилання. Наприклад, формула =COLUMN(BD) повертає 56.


Пов’язані формули

Пошук за кількома критеріями за допомогою INDEX та MATCH

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

Двосторонній пошук з INDEX та MATCH

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

Шукати значення найближчого відповідності з кількома критеріями

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


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

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

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

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL