Підручник Excel: Двосторонній пошук із наближеними INDEX та MATCH

Зміст

Пов’язані ярлики

У цьому відео ми розглянемо, як створити двосторонній пошук за допомогою INDEX та MATCH, використовуючи приблизний збіг.

Тут ми маємо простий калькулятор витрат, який визначає вартість на основі ширини та висоти матеріалу. Матч повинен бути приблизним. Наприклад, якщо ширина 250, а висота 325, то правильний результат - 1800 доларів.

Якщо ширина 450, а висота залишається 325, то правильний результат - 3600 доларів.

Ми можемо створити формулу, яка виконує цей пошук, використовуючи INDEX та MATCH.

По-перше, давайте змусимо INDEX працювати як доказ концепції шляхом жорсткого кодування частини формули. Це чудовий спосіб переконатись, що у вас є правильна ідея, перш ніж почати.

Отже, з даними в нашій таблиці як масивом, а також шириною 450 і висотою 325, INDEX знадобиться номер рядка 3 і номер стовпця 4, щоб отримати правильне значення в таблиці . Це працює нормально, але, звичайно, це не зміниться, оскільки значення жорстко закодовані.

Отже, давайте налаштуємо функції MATCH, які нам потрібні для обчислення цих значень.

Щоб отримати значення ширини, яке є номером рядка в INDEX, ми використаємо значення пошуку з M7 та значення у стовпці B як масив пошуку. Для типу збігу ми хочемо використовувати 1 для приблизного збігу, оскільки значення сортуються за зростанням. Результат - 4.

Щоб отримати висоту, яка є стовпцем усередині INDEX, ми знову використовуємо MATCH зі значенням від M8, значення висот з рядка 6. Знову тип збігу знову встановлюється на 1 для приблизного збігу. Результат - 3.

Тепер, якщо я зміню ширину на 350 та висоту на 550, ми отримаємо новий набір результатів.

Ці значення - саме те, що нам потрібно для INDEX. Тож тепер я просто скопіюю та вставте функції MATCH у вихідну формулу INDEX.

Ширина йде на номер рядка.

І висота входить до номера стовпця.

Тепер у нас є динамічний пошук, який правильно розраховує вартість на основі ширини та висоти, складаючи приблизну відповідність.