⚡ ПРОСМОТРХ в Excel: новая эра поиска данных, о которой стоило рассказать раньше. Подробное руководство по функции ПРОСМОТРХ (XLOOKUP) в Excel: синтаксис, примеры, автоматизация, отличия от ВПР и ИНДЕКС/ПОИСКПОЗ. Полная инструкция для аналитиков и пользователей Microsoft 365.
💬 Замечание подписчика
После выхода статьи про ИНДЕКС и ПОИСКПОЗ один из подписчиков справедливо заметил:
«… изучите XПР (новый стандарт поиска) — может, всё же ПРОСМОТРХ?»
Отличный комментарий — и вы абсолютно правы 💪
Я намеренно не стал включать ПРОСМОТРХ в предыдущую статью, чтобы не перегружать материал. Тогда мы разбирали принципы классических функций.
А вот ПРОСМОТРХ — это уже новый уровень, который появился в Microsoft 365 и Excel 2021, и действительно заменяет ВПР, ГПР, ИНДЕКС и ПОИСКПОЗ одновременно.
Сегодня мы подробно разберём, как работает функция ПРОСМОТРХ, почему она быстрее, удобнее и надёжнее старых методов, и как с её помощью можно полностью изменить подход к поиску данных в Excel.
🧩 Что такое ПРОСМОТРХ и почему это важно
Функция ПРОСМОТРХ (XLOOKUP) — это современная альтернатива классическим ВПР и ИНДЕКС/ПОИСКПОЗ.
Она позволяет искать значение в любом направлении (влево, вправо, вверх, вниз), не требует порядковых номеров столбцов и умеет работать с точным и приближённым совпадением, без аргументов 0 или 1.
Синтаксис:
=ПРОСМОТРХ(искомое_значение; массив_поиска; массив_результатов; [если_не_найдено]; [режим_сопоставления]; [режим_поиска])
Разберём аргументы:
- искомое_значение — что ищем (например, фамилию сотрудника);
- массив_поиска — где ищем;
- массив_результатов — из какого диапазона вернуть результат;
- если_не_найдено — необязательный аргумент, показывает сообщение вместо ошибки;
- режим_сопоставления — выбираем точное (0), ближайшее (1 или –1) или подстановочное совпадение;
- режим_поиска — направление поиска: сверху вниз, снизу вверх и даже бинарный поиск по отсортированным данным.
🔧 Простой пример
Допустим, у нас есть таблица:
И мы хотим узнать регион по фамилии:
=ПРОСМОТРХ("Петров"; A2:A10; C2:C10)
Результат — СПб.
Всё просто: не нужно указывать номер столбца, не важно, где находится искомое значение.
Если бы колонка «Регион» стояла слева от «ФИО» — формула всё равно работала бы.
⚙️ Пример с текстом и ошибками
Если фамилии нет, Excel больше не покажет #Н/Д — можно задать сообщение:
=ПРОСМОТРХ("Иванова"; A2:A10; C2:C10; "Нет данных")
Теперь в ячейке отобразится «Нет данных», что делает отчёт аккуратным и понятным.
💡 Пример с динамическим диапазоном
Функция ПРОСМОТРХ отлично работает с динамическими диапазонами.
Представим, что на листе Продажи есть таблица:
А на листе Справочник хранятся регионы:
Задача: автоматически подставить регион напротив каждого менеджера.
Формула:
=ПРОСМОТРХ(D2; Справочник!A2:A50; Справочник!B2:B50; "Нет данных")
Теперь при добавлении новых строк данные обновятся мгновенно.
🔍 Почему ПРОСМОТРХ лучше ВПР
⚙️ Продвинутый пример: поиск по двум условиям
Допустим, есть таблица продаж по регионам и месяцам:
МенеджерЯнвФевМарИванов120140130Петров100110105Сидоров9095100
Мы хотим найти продажи Петрова за Февраль.
=ПРОСМОТРХ("Петров"; A2:A4; ПРОСМОТРХ("Фев"; B1:D1; B2:D4))
Что делает эта формула:
- Внутренний ПРОСМОТРХ ищет столбец «Фев» и возвращает весь диапазон.
- Внешний ищет строку с фамилией «Петров» и подставляет нужное значение.
Это своего рода двумерный поиск, но всего одной функцией.
🧮 Использование ПРОСМОТРХ с приближённым совпадением
Если вам нужно найти диапазон значений (например, скидку по сумме), используйте режим сопоставления:
=ПРОСМОТРХ(B2; A2:A6; C2:C6; ; -1)
Аргумент –1 означает, что Excel найдёт наибольшее значение, не превышающее искомое.
Это аналог режима ИСТИНА в ВПР, но теперь реализовано удобнее и понятнее.
🔧 Пример: таблица с баллами и оценками
Формула:
=ПРОСМОТРХ(77; A2:A5; B2:B5; ; -1)
Результат — 4, так как 77 находится между 70 и 85.
💻 VBA: автоматизация вставки формул ПРОСМОТРХ
Для больших таблиц можно применить макрос, который автоматически проставит формулы.
Sub АвтоПросмотрХ()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow As Long
Set ws1 = ThisWorkbook.Sheets("Продажи")
Set ws2 = ThisWorkbook.Sheets("Справочник")
lastRow = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row
ws1.Range("E2:E" & lastRow).FormulaLocal = "=ПРОСМОТРХ(D2;Справочник!A:A;Справочник!B:B;""Нет данных"")"
MsgBox ""Формулы ПРОСМОТРХ вставлены успешно!"", vbInformation
End Sub
Теперь не нужно вручную прописывать десятки формул — всё автоматизируется за секунды.
🧠 Полезные приёмы
1. ПРОСМОТРХ внутри ПРОСМОТРХ
Можно объединять их для многомерного поиска (например, менеджер + месяц).
2. Работа с подстановочными знаками
=ПРОСМОТРХ("Петр"; A2:A10; C2:C10)* — найдёт «Петров» и «Петренко».
3. Поиск снизу вверх
Добавьте аргумент -1 в режим_поиска:
=ПРОСМОТРХ("Петров"; A2:A100; C2:C100; ; 0; -1)
📊 Пример: поиск последней продажи
Формула:
=ПРОСМОТРХ("Иванов"; A2:A100; B2:B100; ; 0; -1)
Результат — 1400, то есть последняя продажа Иванова.
⚡ Производительность и сравнение с ИНДЕКС + ПОИСКПОЗ
🧱 Как перейти с ВПР на ПРОСМОТРХ
Переход занимает пару минут — синтаксис логичный и понятный даже новичкам.
🔔 Подписка и комментарии
Если вы хотите идти в ногу с современным Excel, где функции умнее, быстрее и удобнее — обязательно освойте ПРОСМОТРХ.
Он уже стал стандартом в Microsoft 365 и заменил ВПР окончательно.
🔥 Подпишитесь на наш Телеграм и Дзен — там я выкладываю готовые шаблоны, VBA-макросы и таблицы для практики.
💬 Напишите в комментариях: А вы уже перешли на ПРОСМОТРХ или пока пользуетесь ВПР?
📘 Вывод
Функция ПРОСМОТРХ (XLOOKUP) — это новая эпоха в работе с данными в Excel.
Она сочетает гибкость ИНДЕКС + ПОИСКПОЗ, простоту ВПР и надёжность современных технологий.
Теперь всё, что раньше делали тремя формулами — можно решить одной.
А если добавить макросы, получится настоящая автоматизация.
Используйте ПРОСМОТРХ в своих проектах — и Excel начнёт работать в вашу пользу, а не наоборот.