Формула не сломалась. Сломался номер столбца.
Типичная история: отчёт на 40 формул ВПР работал полгода. Кто-то вставил один столбец в исходную таблицу — и половина значений поехала на соседний показатель. Ошибки нет, #Н/Д нет, цифры просто неправильные.
Это худший тип поломки: Excel не ругается, отчёт выглядит рабочим, а решения принимаются по чужим данным. Причина — в том, как устроена сама функция.
ВПР ссылается на порядковый номер столбца внутри диапазона, а не на сам столбец. Диапазон сдвинулся — номер остался прежним. Формула честно вернёт данные не оттуда.
Четыре аргумента, три ловушки
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Функция берёт значение, ищет его в первом столбце указанного диапазона и возвращает то, что лежит правее — на столько столбцов, сколько вы зашили числом. Отсюда три ограничения.
Четвёртый аргумент — интервальный_просмотр. Если забыть его или поставить ИСТИНА на неотсортированных данных, ВПР вернёт «ближайшее подходящее» вместо точного совпадения. Для точного поиска всегда пишите 0 или ЛОЖЬ.
ИНДЕКС+ПОИСКПОЗ: две функции, одна задача
Связка разделяет работу. ПОИСКПОЗ отвечает на вопрос «в какой строке лежит ключ», ИНДЕКС — «дай значение из такой-то строки нужного столбца». Никаких порядковых номеров.
=ИНДЕКС(что_вернуть; ПОИСКПОЗ(искомое; где_искать; 0))
Как читать формулу вслух
- ПОИСКПОЗ($A2; Прайс!$A:$A; 0)Находит артикул из A2 в столбце артикулов и возвращает номер строки — например, 148.
- ИНДЕКС(Прайс!$C:$C; 148)Берёт 148-ю строку в столбце цен и возвращает значение. Всё.
ВПР · ячейка B7
- ×Ключ обязан стоять слева от результата
- ×Вставка столбца ломает результат молча
- ×Перенос таблицы требует пересчёта номеров вручную
- ×В расчёт втягивается весь диапазон
ИНДЕКС+ПОИСКПОЗ · ячейка C7
- ✓Ищет в любую сторону — влево тоже
- ✓Вставка столбцов не ломает ничего
- ✓Один ПОИСКПОЗ можно переиспользовать для 10 столбцов
- ✓Работает только с двумя нужными столбцами
Что реально быстрее — и когда это вообще заметно
Короткий ответ: на таблице в 2 000 строк вы не увидите разницы никогда. Спор о скорости начинается там, где формул тысячи, а строк — десятки тысяч.
Механика такая. При точном поиске (0) обе конструкции последовательно просматривают столбец до первого совпадения. Но ВПР при этом держит в расчёте весь прямоугольник таблицы, а ПОИСКПОЗ — только один столбец. Чем шире исходная таблица, тем сильнее расходятся затраты.
A:AИСТИНА идёт двоичным поискомСтрока 5 — единственный случай, где ВПР честно обгоняет связку, причём с большим отрывом. Плата за это — данные обязаны быть отсортированы по возрастанию, иначе функция вернёт неверное значение и не предупредит. На практике риск почти всегда перевешивает выигрыш.
Приём, который ускоряет любой вариант
Если по одному ключу вы достаёте несколько полей, не повторяйте поиск в каждой формуле. Посчитайте строку один раз и переиспользуйте её.
D2 =ПОИСКПОЗ($A2; Прайс!$A:$A; 0) ← ищем один раз
E2 =ИНДЕКС(Прайс!C:C; $D2) ← дальше только берём
F2 =ИНДЕКС(Прайс!D:D; $D2)
G2 =ИНДЕКС(Прайс!E:E; $D2)
Формулы — это 20% курса. Остальное — то, что с ними делать.
В модуле «Продвинутые навыки» разбираем ВПР, ИНДЕКС+ПОИСКПОЗ и сводные на сквозном проекте с реальными данными — не на учебных трёх строчках.
Открыть доступ на 48 часов →Не верьте статьям — замерьте на своих данных
Любые чужие цифры бенчмарков бесполезны: у вас другой объём, другая ширина таблицы, другой процессор и другая версия Excel. Замер занимает две минуты.
- Отключите автопересчётФормулы → Параметры вычислений → Вручную. Иначе Excel пересчитает файл раньше, чем вы засечёте время.
- Продублируйте листНа одном — вариант с ВПР, на втором — с ИНДЕКС+ПОИСКПОЗ. Одинаковое число формул, одинаковые данные.
- Засеките F9Нажмите F9 и замерьте пересчёт секундомером телефона. Повторите три раза, возьмите среднее.
- Сравните по-честномуЕсли разница в пределах погрешности — выбирайте не по скорости, а по надёжности. Так будет в большинстве отчётов.
Строка состояния внизу окна показывает, что Excel пересчитывает файл. Если при обычной работе там мелькает «Вычисление: 4 процессора», ваш файл уже упёрся в производительность — и дело почти всегда не в выборе функции, а в ссылках на целые столбцы и лишних волатильных формулах вроде СМЕЩ и ДВССЫЛ.
ХПР: если у вас свежая версия — спор окончен
В Microsoft 365 и Excel 2021+ появилась ХПР (XLOOKUP). Она закрывает обе проблемы разом: ищет в любую сторону, по умолчанию делает точный поиск и умеет сама обрабатывать «не найдено».
=ХПР($A2; Прайс!$A:$A; Прайс!$C:$C; "не найдено")
ХПР не откроется в Excel 2019 и старше — вместо значений коллеги увидят #ИМЯ?. Если файл уходит наружу или в компанию со старым парком, безопаснее оставить ИНДЕКС+ПОИСКПОЗ: она работает во всех версиях.
Что выбрать: короткая таблица решений
Главный вывод не про скорость. ВПР проигрывает не потому, что медленная, а потому что хрупкая: она привязана к порядку столбцов, который вы не контролируете. Связка ИНДЕКС+ПОИСКПОЗ стоит на настоящих ссылках — и поэтому переживает жизнь файла.
Разворачиваете строку — читаете ответ
1ВПР вернула #Н/Д, хотя значение точно есть. Почему?−
СЖПРОБЕЛЫ.2Можно ли искать сразу по двум условиям?+
=ИНДЕКС(C:C; ПОИСКПОЗ(A2&B2; D:D&E:E; 0)). В старых версиях формулу нужно вводить как массивную — Ctrl+Shift+Enter. ВПР так не умеет без служебного столбца.3Почему файл тормозит даже после перехода на ИНДЕКС+ПОИСКПОЗ?+
СМЕЩ и ДВССЫЛ, условное форматирование на весь лист. Часто правильный ответ — вообще не формулы, а сводная таблица или Power Query.