Шукати значення найближчого відповідності з кількома критеріями
У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі кількох критеріїв. З поєднанням ІНДЕКС, 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 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.