ПРОСМОТР, ПРОСМОТРX и ГПР в Excel
Четыре функции поиска и честный ответ, что делать, если ПРОСМОТРX у вас нет: она не работает в Excel 2016 и 2019.
Коротко
Функций поиска в Excel четыре, и выбирают между ними не по красоте, а по тому, что у вас установлено.
ПРОСМОТРX — самая удобная: ищет в любую сторону, принимает «что вернуть, если не найдено» четвёртым аргументом и не требует номера столбца. Но её нет в Excel 2016 и Excel 2019 — она доступна только в Microsoft 365 и Excel 2021 и новее. В старых версиях формула с ней покажет #ИМЯ?. Это не мелочь: в организациях 2016-я стоит до сих пор, и файл, отправленный наружу, ломается именно так.
ВПР и ГПР есть везде. Разница между ними только в направлении справочника: ВПР ищет в первом столбце и идёт вправо, ГПР ищет в первой строке и идёт вниз. ПРОСМОТР — самая старая: короткая запись, но требует сортировки по возрастанию и на несортированных данных молча врёт.
Если ПРОСМОТРX недоступна, всё, ради чего её берут, собирается из ИНДЕКС, ПОИСКПОЗ и ЕСЛИОШИБКА. Именно так сделан файл этой страницы: он открывается в любой версии.
То же самое формулой в Excel
=ВПР($H$4;$C$6:$E$10;2;ЛОЖЬ) — ВПР: цена по названию, работает везде=ГПР($H$4;$C$13:$G$14;2;ЛОЖЬ) — ГПР: тот же поиск, когда справочник лежит поперёк=ПРОСМОТР($H$4;$C$6:$C$10;$D$6:$D$10) — ПРОСМОТР: короче всех, но требует сортировки по возрастанию=ИНДЕКС($B$6:$B$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0)) — поиск влево — то, ради чего обычно и берут ПРОСМОТРX=ЕСЛИОШИБКА(ИНДЕКС($D$6:$D$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0));"не найдено") — «если не найдено» без самой ПРОСМОТРX=ПРОСМОТРX($H$4;$C$6:$C$10;$D$6:$D$10;"не найдено") — то же одной функцией — но только Excel 2021, 365 и Google Таблицы=ИНДЕКС($D$6:$D$10;ПОИСКПОЗ(1;ИНДЕКС(($C$6:$C$10=$H$4)*($E$6:$E$10>0);0);0)) — два условия сразу, без ввода массивомФайл с четырьмя способами поиска
Справочник в двух видах — по строкам и поперёк, — чтобы работали и ВПР, и ГПР. Шесть примеров, все открываются в Excel 2016: ПРОСМОТРX в файле нет намеренно.
Файл
prosmotr-sbornik.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Проверьте, есть ли у вас ПРОСМОТРX: начните набирать
=ПРОСМОТРи посмотрите на подсказку. Нет в списке — версия старая, дальше по пункту 4. - Есть — пишите
=ПРОСМОТРX(что;где_искать;что_вернуть;"не найдено"). Номер столбца не нужен, направление любое. - Но помните: файл с ней у коллеги на Excel 2016 покажет
#ИМЯ?. Если книга уйдёт наружу — пункт 4. - Нет ПРОСМОТРX: ответ правее ключа — ВПР, ответ левее — ИНДЕКС с ПОИСКПОЗ, справочник лежит поперёк — ГПР.
- Оберните в ЕСЛИОШИБКА, чтобы вместо
#Н/Дпоявилось понятное слово. - Закрепите диапазоны долларами перед протягиванием.
Где обычно ломается
#ИМЯ?у ПРОСМОТРX. Функции нет в вашей версии — это Excel 2016 или 2019. Не опечатка, не сбой: заменяйте связкой ИНДЕКС и ПОИСКПОЗ.- ПРОСМОТР на несортированных данных. Она ищет приблизительно и всегда: если столбец не отсортирован по возрастанию, вернёт соседнее значение и не покажет ошибки. Это худший вид сбоя — тихий.
- ГПР перепутали с ВПР. Г — горизонтальный: справочник должен лежать поперёк, ключи в первой строке диапазона. Если данные идут столбцами, нужна ВПР.
- Забыли ЛОЖЬ в ВПР и ГПР. Без четвёртого аргумента поиск приблизительный — та же тихая ошибка, что у ПРОСМОТР.
- ПРОСМОТРX в Google Таблицах есть, в Excel 2016 нет. Обратный случай тоже бывает: файл, собранный в Google Таблицах, у коллеги в старом Excel развалится.