Перейти до основного матеріалу

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

У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі кількох критеріїв. З поєднанням ІНДЕКС, 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 - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

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


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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (1)
Rated 0.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Keep yrs at "15" and switch major to "science"...formula busts. This is not a robust formula...
Rated 0.5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations