Урок 6. Поиск в таблице: ВПР, ПРОСМОТРX, ИНДЕКС и ПОИСКПОЗ
Частая задача: есть прайс-лист, а в заказе — только артикулы. Нужно подставить к каждому артикулу название и цену из прайса. Искать глазами в сотне строк долго и ненадёжно — это работа для функций поиска.
Пример для всего урока — прайс в A1:C5 и заказ рядом:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Заказ | Цена | |
| 2 | 1001 | Тетрадь | 1,20 | 1003 | ||
| 3 | 1002 | Ручка | 0,85 | |||
| 4 | 1003 | Карандаш | 0,40 | |||
| 5 | 1004 | Ластик | 0,55 |
ВПР
ВПР (вертикальный просмотр) ищет значение в первом столбце таблицы и возвращает значение из той же строки в нужном столбце:
=ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)Цена для артикула из E2 — в F2:
=ВПР(E2;$A$2:$C$5;3;ЛОЖЬ)| Аргумент | Здесь | Что значит |
|---|---|---|
| искомое_значение | E2 | что ищем: артикул 1003 |
| таблица | $A$2:$C$5 | где ищем; искомое — в её первом столбце |
| номер_столбца | 3 | из какого столбца таблицы взять ответ: 3 — «Цена» |
| интервальный_просмотр | ЛОЖЬ | искать точное совпадение |
Результат — 0,40. Если заменить 3 на 2, формула вернёт «Карандаш».
Таблица закреплена знаками $: формулу будут копировать вниз по заказу, а прайс должен оставаться на месте. Прайс на другом листе указывается так же, с именем листа: Прайс!$A$2:$C$100.
Последний аргумент пишите всегда. Если его пропустить, ВПР ищет приблизительное совпадение и на несортированных данных молча возвращает чужую строку. ЛОЖЬ (или 0) — точный поиск.
Если не нашлось. Введите в E2 артикул 1007, которого нет в прайсе, — ВПР вернёт #Н/Д («нет данных»). Вместо ошибки можно показать свой текст функцией ЕСНД:
=ЕСНД(ВПР(E2;$A$2:$C$5;3;ЛОЖЬ);"нет в прайсе")Ограничения ВПР
- Искать можно только в первом столбце таблицы, а ответ брать только правее. Найти артикул по названию товара
ВПРне умеет: артикул левее названия. - Номер столбца — просто число. Вставите в прайс столбец между артикулом и ценой —
3начнёт указывать на другой столбец, и формула тихо вернёт не то. - Номер больше числа столбцов таблицы даёт ошибку
#ССЫЛКА!.
ПРОСМОТРX
В Excel 2021, Excel 2024 и Microsoft 365 есть функция без этих недостатков — ПРОСМОТРX. В Excel 2016 и 2019 её нет: формула покажет ошибку #ИМЯ?. В LibreOffice Calc она появилась в версии 24.8.
=ПРОСМОТРX(искомое_значение;просматриваемый_массив;возвращаемый_массив;если_ничего_не_найдено)Буква X в названии латинская. Проще всего набрать =ПРОСМ и выбрать функцию из подсказки клавишей Tab.
Та же цена:
=ПРОСМОТРX(E2;$A$2:$A$5;$C$2:$C$5;"нет в прайсе")Вместо таблицы и номера столбца — два отдельных столбца: где искать и откуда брать ответ. Точное совпадение включено по умолчанию, а четвёртый аргумент сразу задаёт ответ на случай, когда ничего не нашлось.
Искать можно в любую сторону. Артикул по названию — столбец ответа левее столбца поиска:
=ПРОСМОТРX("Ластик";$B$2:$B$5;$A$2:$A$5)| Формула | Результат |
|---|---|
=ПРОСМОТРX(E2;$A$2:$A$5;$C$2:$C$5;"нет в прайсе") | 0,40 |
=ПРОСМОТРX(1007;$A$2:$A$5;$C$2:$C$5;"нет в прайсе") | нет в прайсе |
=ПРОСМОТРX("Ластик";$B$2:$B$5;$A$2:$A$5) | 1004 |
Есть ещё два необязательных аргумента: режим сопоставления (0 — точно, −1 — точно или ближайшее меньшее, 1 — точно или ближайшее большее, 2 — с подстановочными знаками * и ?) и режим поиска (1 — сверху вниз, −1 — снизу вверх, то есть найти последнее совпадение).
ИНДЕКС и ПОИСКПОЗ
Связка двух функций работает в любой версии Excel и в LibreOffice Calc и умеет искать в любую сторону.
ПОИСКПОЗ находит номер позиции значения в столбце, ИНДЕКС возвращает значение по номеру позиции:
=ПОИСКПОЗ("Ластик";$B$2:$B$5;0)
=ИНДЕКС($A$2:$A$5;4)Первая формула вернёт 4: «Ластик» — четвёртый в B2:B5. Ноль в конце — точное совпадение, как ЛОЖЬ в ВПР. Вторая вернёт 1004 — четвёртое значение в A2:A5. Вместе — артикул по названию:
=ИНДЕКС($A$2:$A$5;ПОИСКПОЗ("Ластик";$B$2:$B$5;0))Результат — 1004. Читается так: «возьми из столбца артикулов значение в той позиции, где в столбце названий стоит "Ластик"». Столбцы можно переставлять и вставлять новые — формула не сломается.
Почему #Н/Д, хотя значение есть
- Число и текст. В прайсе артикул 1003 — число, а в заказе — текст «1003» (прижат влево, часто после копирования из другой программы). Для Excel это разные значения. Приведите оба столбца к одному виду — см. Формула не считается.
- Лишние пробелы. «Ластик » с пробелом в конце не равен «Ластик». Найти и убрать их помогает функция
СЖПРОБЕЛЫ:=СЖПРОБЕЛЫ(B2). - Сползла таблица. Формулу скопировали вниз без
$, и диапазон поиска сдвинулся: в нижних строках он уже не захватывает начало прайса.
Поиск по шкале
Приблизительный поиск полезен, когда ищут не точное значение, а интервал: скидка зависит от суммы заказа. Шкала должна быть отсортирована по возрастанию:
| H | I | |
|---|---|---|
| 1 | Сумма от | Скидка |
| 2 | 0 | 0% |
| 3 | 100 | 5% |
| 4 | 500 | 10% |
=ВПР(320;$H$2:$I$4;2;ИСТИНА)Результат — 5 %: ВПР берёт наибольшее значение, не превышающее 320, то есть строку «100».
Коротко
ВПР(что;таблица;номер_столбца;ЛОЖЬ)ищет в первом столбце и берёт ответ правее;ЛОЖЬ— обязательно.ПРОСМОТРX(что;где_искать;откуда_брать;"если нет")— Excel 2021, 2024 и 365, ищет в любую сторону, точно по умолчанию.ИНДЕКС(откуда_брать;ПОИСКПОЗ(что;где_искать;0))— то же в любой версии.#Н/Д— значение не найдено: проверьте число/текст, пробелы и закреплённый$диапазон.ЕСНДзаменяет#Н/Дсвоим текстом.