← Все курсы · Курс Excel

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

Майкл Носито · Обновлено в августе 2026 г. · Все числа на этой странице были обработаны до их публикации.

Баллы превращаются в оценки. Суммы превращаются в налоговые диапазоны. Размеры заказов превращаются в уровни скидок. Каждое из них представляет собой правило с сокращениями, и почти каждое из них сначала записывается в виде стопки вложенных правил. IFэто то, что никто не может просмотреть.

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

Краткая версия. Нижние границы по возрастанию, пропусков нет. Тогда один поиск выполняет все правило.

Стол группы

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

=INDEX($F$2:$F$6, MATCH(B2, $E$2:$E$6, 1))

Или более старая форма, в которой метка должна располагаться справа от границы:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

тот 1 в MATCH и TRUE в VLOOKUP это одна и та же инструкция: найти наибольшее значение, которое меньше или равно этому, и сообщить мне его позицию.

Работало, включая границу

Ровно 70 баллов дают оценку B. Это решение, а не случайность: при нижних границах значение, равное границе, принадлежит этому диапазону. Если в вашей политике указано, что 70 — это C, граница должна быть 70,01 или лучше, 71, если баллы — целые числа. Запишите политику в ячейку рядом с таблицей, чтобы никому не приходилось перепроектировать ее на основе чисел.

Четыре правила

1. Отсортируйте границы по возрастанию. Тогда защитите это: положите стол на отдельный лист и заприте его.
2. Начните с минимально возможного значения, обычно 0 или большое отрицательное число, если показатель может опускаться ниже нуля. В противном случае возвращаются наименьшие значения. #N/A.
3. Никаких пробелов. Границей каждой строки является место, где заканчивается предыдущая полоса. Полосы, определяемые как «50 к 59», подразумевают значение 59,5, которое никуда не годится.
4. Точное совпадение — это другая функция. Если ключ представляет собой категорию, а не число, вы хотите MATCH(...,0), и их никогда нельзя смешивать в одной книге.

Прогрессивные брекеты – это не та же проблема

Налоговый диапазон, в котором указано «40 процентов для части выше 50 270», — это не поиск, а совокупный расчет. Одно приблизительное совпадение дает вам ставка это применимо сверху; это не дает вам налога. Для этого вам понадобится накопительный столбец в таблице диапазонов:

=cum_tax + (income - lower_bound) * rate

С тремя поисками rate, lower_bound и cum_tax из той же совпадающей строки. Доход в 60 000 дает 7 540 + (60 000 – 50 270) х 40 процентов = 7 540 + 3 892 = 11 432. Эти цифры носят иллюстративный характер; возьмите текущие пороговые значения из налогового органа и сохраните их в таблице, где их можно будет обновлять в одном месте каждый апрель.

Почему не вложенные IF

Сбой перекрывающейся ветки — тот же самый, который появляется в SQL, и работал в СЛУЧАЙ с перекрывающимися условиями. В таблице отсортированных групп такой ошибки быть не может, что является реальным аргументом в пользу чистоты таблицы.

Как применить это в своей работе

1. Найдите самое длинное вложенное IF и посчитаем в нем отсечки. Это количество представляет собой количество строк в таблице замены.
2. Постройте таблицу на отдельном листе, отсортировав ее по возрастанию, начиная с наименьшего возможного значения.
3. Напишите рядом слова граничной политики: увеличивается или уменьшается значение, равное отсечению?
4. Восстановите столбец с помощью INDEX MATCH и согласуйте каждую строку со старой формулой, прежде чем удалять ее.
5. Добавьте строку журнала изменений с датой и указанием того, кто утвердил ограничения. Объединенные правила — это именно то, о чем люди спрашивают шесть месяцев спустя.

Единственная привычка, которую нужно сохранить

Разместите правило там, где его можно будет просмотреть. Отсечение внутри формулы — это решение, которое может видеть только его автор, и человек, которому нужно его проверить, обычно не имеет доступа к строке формул.

Где сейчас находятся границы ваших классов или уровней и кто может их прочитать, не спросив вас?