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

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

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


Як шукати значення найближчого збігу за кількома критеріями?

Як показано на скріншоті нижче, вам потрібно знайти відповідну людину для роботи на основі двох критеріїв: комп'ютер"Та" досвід роботи є 15 років ”.

примітки: Щоб це працювало коректно, якщо там є дублікати спеціальностей, досвід роботи цих дублікатів спеціальностей слід сортувати за зростанням.

1. Виділіть порожню комірку для виведення результату, скопіюйте в неї формулу нижче та натисніть Ctrl + Shift + вводити клавіші, щоб отримати результат.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Примітки: у цій формулі:

  • D3: D8 - діапазон стовпців містить результат, який ви шукаєте;
  • G5 містить другий критерій (досвід No 15), за яким ви шукаєте значення;
  • G4 містить перші критерії (комп’ютер), за якими ви шукаєте значення;
  • B3: B8 - діапазон комірок, що відповідає першим критеріям;
  • C3: C8 - діапазон комірок, що відповідає другим критеріям;
  • Номер 1 є приблизним пошуком, що означає, що якщо точного значення не вдається знайти, він знайде найбільше значення, яке менше значення пошуку;
  • Цю формулу потрібно вводити як формулу масиву з Ctrl + Shift + вводити ключі.

Як працює ця формула

Цю формулу можна розбити на кілька компонентів:

  • IF(B3:B8=G4,C3:C8): функція IF тут повертає результат як {9;13;FALSE;FALSE;FALSE;FALSE}, який походить від тестування значень у B3: B8, щоб перевірити, чи відповідають вони значенням у G4. Якщо є збіг, тоді повертається відповідне значення, інакше повертає FALSE. Тут знаходять два збіги та чотири невідповідності.
  • Формула масиву =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): функція MATCH знаходить положення числа 15 (значення в G5) в діапазоні C3: C8. Оскільки число 15 неможливо знайти, воно відповідає наступному найменшому значенню 13. Отже, результат тут 2.
  • і =INDEX(D3:D8,2): Функція INDEX повертає значення другої комірки в діапазоні D3: D8. Тож кінцевий результат - Емі.

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

Функція Excel IF
Функція IF - одна з найпростіших і найкорисніших функцій у книзі Excel. Він виконує простий логічний тест, який залежно від результату порівняння, і повертає одне значення, якщо результат ІСТИНА, або інше значення, якщо результат ФАЛЬШЕ.

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

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


Статті по темі

Середнє значення клітинок на основі кількох критеріїв
У Excel більшість з нас можуть бути знайомі з функціями COUNTIF і SUMIF, вони можуть допомогти нам підрахувати або підсумувати значення на основі критеріїв. Але чи намагались ви коли-небудь розрахувати середнє значення на основі одного чи кількох критеріїв у Excel? Цей посібник містить приклади та детальні формули, які допоможуть легко це зробити.
Натисніть, щоб дізнатись більше ...

Підрахувати клітинки, якщо відповідає одному з кількох критеріїв
Цей підручник розповідає про способи підрахунку клітинок, якщо вони містять X, Y або Z ... тощо в 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