Приближённое сопоставление для интервальных таблиц

Ступенчатая таблица распределяет непрерывный ряд значений по диапазонам. Примеры: налоговые ставки, стоимость доставки в зависимости от веса, скидки за объём и буквенные оценки в зависимости от балла.

Для каждого возможного значения отдельная строка не нужна: достаточно указать начальный порог каждого диапазона. Для балла 87 нет точной записи, но он попадает в диапазон, начинающийся с 80.

Именно здесь особенно полезен приближённый поиск: он находит нужный диапазон, а не требует точного совпадения.

До этого мы использовали MATCH(value, range, 0) для поиска точного совпадения. Третий аргумент 0 означает «найти это значение точно или вернуть #N/A».

Для диапазонов мы вместо этого используем тип совпадения 1. Он находит наибольшее значение, меньшее или равное искомому. Именно так и должен работать поиск по диапазону.

Есть одно строгое правило: при типе совпадения 1 список порогов должен быть отсортирован в порядке возрастания.

Представьте таблицу оценок. В столбце E хранятся нижние пороги, отсортированные по возрастанию: 0, 60, 70, 80, 90. В столбце F хранятся обозначения: F, D, C, B, A.

Баллы от 0 до 59 соответствуют F, от 60 до 69 — D и так далее. Мы храним только начало каждого диапазона, а не каждый возможный балл.

Наша цель — получить буквенную оценку по баллу, указанному в G1.

Используйте приближённый MATCH, чтобы определить, в какой диапазон попадает балл. Формула MATCH(G1, E2:E6, 1) для балла 87 ищет наибольший порог, не превышающий 87.

Пороги равны 0, 60, 70, 80 и 90. Наибольший из них, не превышающий 87, — это 80, расположенный на позиции 4. Поэтому MATCH возвращает 4.

Эта позиция указывает на правильный диапазон, хотя самого значения 87 в списке нет.

Теперь передайте эту позицию в INDEX для столбца обозначений F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) получает позицию 4 и возвращает четвёртое обозначение — «B».

Таким образом, балл 87 правильно преобразуется в оценку B. Если изменить G1 на 95, MATCH вернёт 5 и результатом будет «A»; если изменить его на 55, MATCH вернёт 1 и результатом будет «F».

Приближённый MATCH (тип 1) требует сортировки по возрастанию в диапазоне поиска. Он предполагает, что значения увеличиваются, и останавливается, как только проходит искомое значение.

Если пороги расположены не по порядку, MATCH может остановиться слишком рано и вернуть неправильную, но незаметно ошибочную позицию — без ошибки, которая предупредила бы Вас. Перед использованием поиска по ступенчатой таблице всегда сортируйте столбец порогов от меньшего к большему.

XLOOKUP также умеет выполнять приближённый поиск. Его пятый аргумент, режим совпадения, принимает значение -1 для варианта «точное совпадение или следующий меньший элемент», который идеально подходит для ступенчатых таблиц.

Функция находит наибольший порог, не превышающий G1, и возвращает соответствующее обозначение без необходимости использовать INDEX. Для поиска по диапазонам такая формула часто читается проще, чем INDEX-MATCH.

Рассмотрим скидку за объём. Пороги в столбце E (заказанное количество): 0, 10, 50, 100. Скидки в столбце F: 0%, 5%, 10%, 15%.

• Заказ 7: наибольший порог, не превышающий 7, равен 0; это позиция 1, поэтому возвращается 0%.
• Заказ 60: наибольший порог, не превышающий 60, равен 50; это позиция 3, поэтому возвращается 10%.
• Заказ 200: наибольший порог, не превышающий 200, равен 100; это позиция 4, поэтому возвращается 15%.

Одна формула обрабатывает любое количество.

Что произойдёт, если значение меньше всех порогов? При приближённом MATCH подходящего значения, меньшего или равного ему, нет, поэтому MATCH возвращает #N/A.

Чтобы этого избежать, убедитесь, что первый порог охватывает нижнюю границу (часто это 0), или оберните формулу в IFERROR, чтобы показывать понятное сообщение, когда входное значение выходит за допустимый диапазон.

Остерегайтесь следующих ловушек при работе со ступенчатыми таблицами:

• Несортированные пороги: главная причина неправильных результатов, которые не сопровождаются ошибкой.
• Использование типа совпадения 0: требует точного совпадения и возвращает #N/A для любого промежуточного значения.
• Сохранение концов диапазонов вместо их начал: MATCH с типом 1 ожидает нижнюю границу каждого диапазона, а не верхнюю.
• Текстовые пороги: числа, сохранённые как текст, нарушают сравнение; храните их как числа.

Можно объединить приближённый поиск с техникой поиска по двум направлениям. Представьте стоимость доставки, зависящую одновременно от весового диапазона (строки) и зоны (столбцы).

Используйте один приближённый MATCH (тип 1), чтобы найти строку веса, и другой — чтобы найти столбец зоны, а затем передайте обе позиции в INDEX. Поскольку пороги по обеим осям отсортированы, каждый MATCH указывает на правильный диапазон.

Так INDEX-MATCH-MATCH объединяется с логикой ступенчатых таблиц для создания подробных таблиц тарифов.

Для поиска по ступенчатым таблицам и диапазонам:

• Храните нижний порог каждого диапазона и сортируйте пороги в порядке возрастания.
• Используйте MATCH(value, thresholds, 1), чтобы найти позицию диапазона (наибольшее значение, не превышающее входное).
• Оберните формулу в INDEX(labels, ...), чтобы вернуть обозначение диапазона, или используйте XLOOKUP(..., -1) для получения того же результата.

Охватите нижнюю границу порогом 0 или используйте IFERROR для входных значений вне диапазона; никогда не оставляйте пороги несортированными.