Двостороння наближена відповідність з кількома критеріями
У цьому підручнику ми поговоримо про те, як шукати приблизну відповідність на основі кількох критеріїв, перерахованих як у стовпцях, так і в рядках в таблиці Excel, за допомогою of ІНДЕКС, MATCH та IF функції.
Як виконати двосторонню приблизну відповідність з кількома критеріями в Excel?
Щоб дізнатися, розмір одягу для жінка хто є 165.5см заввишки і важить 55кг відповідно до таблиці вище, ви можете додати до формули дві функції MATCH: одна з функцією IF отримує номер рядка (стать і зріст), інша отримує номер стовпця (вага). Тоді INDEX знайде відповідний розмір відповідно до координат. (Зверніть увагу, що номер рядка стоїть перед номером стовпця у формулі INDEX.)
Родовий синтаксис
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .
- діапазон_повернення: Діапазон, з якого ви хочете, щоб формула комбінації повертала розмір. Тут йдеться про діапазон розмірів.
- пошукове_значення: Значення, яке формула використовувала для визначення позиції відповідного розміру. Тут мається на увазі інформація про дану стать, зріст і вагу.
- пошуковий_масив: Діапазон клітинок зі значеннями для порівняння з lookup_value. Тут йдеться про діапазони статі, зросту та ваги.
- match_type: 1 або -1.
1 або опущено (за замовчуванням) MATCH знайде найбільше значення, яке менше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку зростання.
-1, MATCH знайде найменше значення, яке більше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку спадання.
Щоб дізнатися, розмір одягу для жінка хто є 165.5см заввишки і важить 55кг, будь ласка, скопіюйте або введіть формулу нижче в клітинку M9 і натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у щоб отримати результат:
= ІНДЕКС (D5: J16, MATCH (M6, IF (B5: B16=M5,C5: C16), 1), MATCH (M7,D4: J4, 1))
Пояснення формули
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): Функція IF перевіряє кожне значення B5: B16 якщо вони збігаються зі значенням у комірці M5, жінка. Якщо це так, функція IF збереже відповідні висоти, зазначені в C5: C16; Якщо ні, IF повернеться НЕВІРНІ. Формула поверне такий масив: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- МАТЧ (M6,IF(B5:B16=M5,C5:C16), 1) = МАТЧ (M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): Команда match_type 1 змушує функцію MATCH повертати позицію найбільшого значення, яке менше або дорівнює 165.5 (значення в комірці M6) в масиві. Отже, МАТЧ повернеться 4, положення значення 165.
- МАТЧ (M7, D4: J4,1): Повертається функція MATCH 3, оскільки найбільше значення менше або дорівнює значенню в M7, 55, знаходиться на 3перше положення діапазону D4: J4.
- ІНДЕКС (D5: J16,МАТЧ (M6,IF(B5:B16=M5,C5:C16), 1),МАТЧ (M7, D4: J4,1)) = ІНДЕКС (D5: J16,4,3): Функція INDEX отримує значення на перетині 4й ряд і 3rd стовпець у діапазоні розмірів D5: J16, що є значенням у комірці F8, M.
Супутні функції
Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
Функція IF - одна з найпростіших і найкорисніших функцій у книзі Excel. Він виконує простий логічний тест, який залежно від результату порівняння, і повертає одне значення, якщо результат ІСТИНА, або інше значення, якщо результат ФАЛЬШЕ.
Пов’язані формули
Двосторонній пошук з INDEX та MATCH
Щоб шукати щось у рядках і стовпцях в Excel, або ми кажемо шукати значення на перетині певного рядка і стовпця, ми можемо використовувати функції INDEX і MATCH.
Щоб знайти найближчу відповідність значення пошуку в числовому наборі даних в Excel, можна використовувати разом функції INDEX, MATCH, ABS і MIN.
Шукати значення найближчого відповідності з кількома критеріями
У деяких випадках може знадобитися пошук найближчого або приблизного значення відповідності на основі більш ніж одного критерію. Завдяки поєднанню функцій INDEX, MATCH та IF ви можете швидко це зробити в Excel.
Багатокритеріальний пошук за допомогою INDEX та MATCH
Коли ви маєте справу з великою базою даних у електронній таблиці Excel з кількома стовпцями та підписами рядків, завжди складно знайти щось, що відповідає кільком критеріям. У цьому випадку можна використовувати формулу масиву з функціями INDEX і MATCH.
Приблизний збіг з INDEX та MATCH
Бувають випадки, коли нам потрібно знайти приблизні збіги в Excel, щоб оцінити продуктивність працівників, оцінити результати учнів, обчислити поштові витрати на основі ваги тощо. У цьому підручнику ми поговоримо про те, як використовувати функції INDEX та MATCH для отримання потрібні нам результати.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
