Почему иногда выгоднее использовать ВПР с приближённым совпадением — объясняю на примерах. Разбираем продвинутый приём ВПР в Excel: как и зачем использовать четвёртый аргумент ИСТИНА (1). Примеры, сравнение с точным поиском, оптимизация работы с большими таблицами и советы для аналитиков.
💬 Замечание подписчика
После выхода прошлой статьи один из наших подписчиков написал в комментариях:
«Я работаю с большими массивами и в 99% случаев использую ВПР через ИСТИНУ — это экономит массу времени».
И это отличное замечание.
В первой части мы говорили о точном совпадении (0) — потому что для большинства задач это безопасный и универсальный вариант.
Но есть ситуации, где аргумент ИСТИНА (1) действительно работает быстрее и эффективнее.
Сегодня я покажу, почему это так, и как им пользоваться профессионально — без ошибок и потери данных.
🧩 Что делает аргумент ИСТИНА в ВПР
Напомним синтаксис:
=ВПР(искомое_значение; таблица; номер_столбца; [интервал_просмотра])
Если последний аргумент:
- 0 (ЛОЖЬ) — Excel ищет точное совпадение.
- 1 (ИСТИНА) — ищет приближённое совпадение, но только при условии, что первый столбец таблицы отсортирован по возрастанию.
Пример:
=ВПР(500; A2:B1000; 2; 1)
Excel найдёт наибольшее значение, не превышающее 500 — и подставит соответствующий результат.
⚙️ Когда приближённый ВПР незаменим
1. Работа с тарифами, диапазонами и категориями
Например, у нас есть таблица ставок налога:
Нужно определить ставку для дохода в 730000.
Формула:
=ВПР(730000; A2:B5; 2; 1)
Excel пройдёт по диапазону, найдёт последнюю строку, где значение ≤ 730000 (т.е. 500000), и вернёт 15%.
Это и есть приближённое совпадение — точного числа нет, но подставляется ближайшее по логике диапазона.
2. Градация по категориям
Допустим, вы присваиваете оценку по баллам:
Формула:
=ВПР(C2; A2:B5; 2; 1)
Если в ячейке C2 = 77, Excel подставит «4».
Потому что 77 больше 70, но меньше 85 — ближайшее подходящее значение.
3. Скорость работы на больших массивах
Для больших файлов (десятки тысяч строк) приближённый поиск работает быстрее, потому что Excel использует бинарный поиск — то есть делит диапазон пополам, а не перебирает построчно.
Если у вас мощная таблица с миллионом строк, где данные упорядочены, ВПР с ИСТИНА может сэкономить секунды, а то и минуты при каждом пересчёте.
⚠️ Главные правила, чтобы не получить ошибку
- Обязательно сортируйте первый столбец от меньшего к большему.
Без сортировки формула может вернуть неверный результат. - Нельзя использовать текстовые значения, если порядок неочевиден.
Для текста Excel сортирует по алфавиту, но это часто приводит к ошибкам. - Не путайте с округлением.
Приближённое совпадение не округляет, а ищет ближайшее значение меньше или равное. - Проверяйте граничные значения.
Если искомое значение меньше самого первого в диапазоне — формула вернёт ошибку #Н/Д.
💡 Пример из практики: расчёт скидок
Таблица:
Формула:
=ВПР(B2; A2:B6; 2; 1)
Если клиент купил на 12 000 ₽, Excel найдёт диапазон 10 000 ₽ и вернёт 5%.
Если на 58 000 ₽ — ближайшее значение 50 000 ₽, скидка 15%.
Таким образом, вы можете автоматизировать систему бонусов или налогов без вложенных ЕСЛИ — всего одной функцией.
⚙️ Как ускорить расчёты в больших таблицах
Вот пример кода VBA, который применяет приближённый ВПР для массового расчёта:
Sub АвтоВПР_ИСТИНА()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Скидки")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("C2:C" & lastRow).FormulaLocal = "=ВПР(B2;$E$2:$F$10;2;1)"
End Sub
Этот макрос автоматически вставит формулу ВПР с ИСТИНА в нужный столбец, рассчитав скидку по диапазону.
🚀 Почему Excel работает быстрее с аргументом ИСТИНА
Когда вы используете 0 (ЛОЖЬ), Excel проверяет каждую строку подряд, пока не найдёт точное совпадение.
А вот 1 (ИСТИНА) использует бинарный поиск — алгоритм, где каждая итерация делит диапазон пополам.
Простыми словами:
- При 1 000 000 строк Excel с ИСТИНА делает около 20 проверок,
- А при ЛОЖЬ — до миллиона.
Поэтому при анализе данных, особенно числовых, это действительно «ускоритель».
🔧 Сравнение ВПР с ИСТИНА и ЛОЖЬ
🧠 Комбинируем с другими функциями
Чтобы сделать приближённый поиск безопаснее, используйте ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(B2; A2:B100; 2; 1); "Нет данных")
Или объедините с ОКРУГЛ(), если работаете с дробными числами:
=ВПР(ОКРУГЛ(B2;0); A2:B100; 2; 1)
📊 Сравнение скорости на практике
Тест: две таблицы по 100 000 строк.
- ВПР с 0 (ЛОЖЬ) — среднее время пересчёта 1,8 с
- ВПР с 1 (ИСТИНА) — 0,3 с
При работе с 10 таблицами — выигрыш более 15 секунд.
Для аналитиков, автоматизирующих отчёты, это действительно разница.
🔍 Как визуально контролировать результат
Добавьте условное форматирование:
- Выделите столбец с результатами.
- Формула: =ЕОШИБКА(A2)
- Установите цвет — красный.
Так вы сразу увидите строки, где ВПР не нашёл подходящее значение.
🧮 Дополнительный приём: ВПР внутри ВПР
Если таблиц несколько (например, тарифы и регионы), можно вложить одну формулу в другую:
=ВПР(ВПР(B2; Тарифы!A2:B100; 2; 1); Регионы!A2:B50; 2; 0)
В первом ВПР ищется диапазон по приближённому совпадению, во втором — точное соответствие региона.
💬 Итоги
- Приближённое совпадение — мощный инструмент, если данные отсортированы и числовые.
- Это ускоряет вычисления и заменяет десятки условий.
- Но важно помнить: для текстовых и несортированных таблиц — используйте 0 (ЛОЖЬ).
🔔 Что дальше
В следующей статье мы разберём, как объединить ВПР + ПОИСКПОЗ + ИНДЕКС и создать гибридную формулу, которая заменяет обе функции и работает быстрее XПР. А пока подпишитесь на наш Телеграм-канал и скачайте файл-шаблон и гайд по этой теме.