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

or

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

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


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

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

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

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

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

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

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

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

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

  • 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)
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.