Урок 7 из 8 · 25 мин · обновлено

Поиск по справочнику: ВПР, ПРОСМОТРX и работа с текстом

Как подтянуть данные из другой таблицы: функция ВПР и её четыре аргумента, точное совпадение, ПРОСМОТРX, ошибка #Н/Д и ЕСЛИОШИБКА, склейка текста знаком & и лишние пробелы СЖПРОБЕЛЫ.

Справочник и зачем в нём искать

В учебной таблице у каждого заказа есть товар, но нет категории. Писать категорию руками в каждой строке — долго и рискованно: ошибиться в одной строке легко, заметить трудно. Правильнее завести справочник — маленькую таблицу «товар → категория» — и подтягивать данные из неё формулой.

Создайте второй лист «Товары»:

A B
1 Товар Категория
2 Блокнот Бумажные
3 Ежедневник Бумажные
4 Маркер Письменные
5 Ручка Письменные

Если категория товара изменится, править придётся одну ячейку в справочнике, и все заказы увидят новое значение.

ВПР: четыре аргумента

ВПР — вертикальный просмотр. Функция ищет значение в первом столбце диапазона и возвращает значение из нужного столбца той же строки. На листе «Заказы» в J1 напишем «Категория», а в J2:

Формула
=ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ)
Результат
Бумажные

Разберём аргументы, если в C2 — блокнот:

  1. C2 — что ищем: название товара из заказа.
  2. Товары!$A$2:$B$5 — где ищем: диапазон справочника на листе «Товары». Восклицательный знак отделяет имя листа от адреса.
  3. 2 — что вернуть: номер столбца внутри диапазона. Первый — товары, второй — категории.
  4. ЛОЖЬ — как искать: только точное совпадение.

Протяните формулу до J11 — каждая строка найдёт категорию своего товара. Если вы сортировали список в прошлом уроке, строки идут в другом порядке — это не важно: формула в каждой строке смотрит на свой товар.

Три правила, которые экономят часы:

  • Справочник закрепляют знаками $. Без них при копировании вниз диапазон съедет на A3:B6, A4:B7 — и последние товары перестанут находиться.
  • Последний аргумент — ЛОЖЬ (или 0). Без него ВПР ищет приблизительное совпадение, и на несортированном справочнике может молча вернуть категорию соседнего товара.
  • ВПР смотрит только вправо. Искать он умеет лишь в первом столбце диапазона. Если нужно найти товар по категории, а категория правее, переставьте столбцы справочника или используйте ПРОСМОТРX.

ПРОСМОТРX: поиск без ограничений

В Excel 2021, Excel для Microsoft 365, веб-версии Excel и в Google Таблицах есть функция ПРОСМОТРX. В старых версиях Excel, вроде 2016 и 2019, её нет. Где искать и откуда брать ответ, в ней задают отдельными диапазонами:

Формула
=ПРОСМОТРX(C2;Товары!$A$2:$A$5;Товары!$B$2:$B$5;"нет в справочнике")
Результат
Бумажные

Отсюда три преимущества: столбец ответа может стоять где угодно, хоть левее; номер столбца не нужно считать; четвёртый аргумент — что показать, если ничего не найдено. Буква X в названии латинская: если набрать русскую «Х», таблица не узнает функцию.

Когда ничего не найдено

Допустим, в заказ попал товар «Степлер», которого нет в справочнике. ВПР вернёт #Н/Д — «нет данных», а в Google Таблицах ошибка может выглядеть как #N/A. Это честный сигнал, но в отчёте он выглядит пугающе. Функция ЕСЛИОШИБКА заменит ошибку понятным текстом:

Формула
=ЕСЛИОШИБКА(ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ);"нет в справочнике")
Результат
нет в справочнике

Помните: ЕСЛИОШИБКА не исправляет данные, а только меняет то, что видно. Товар всё равно нужно добавить в справочник.

Работа с текстом

Самая частая причина #Н/Д при товаре, который точно есть в справочнике, — лишний пробел. «Ручка » с пробелом в конце и «Ручка» для таблицы разные значения, хотя на экране не отличаются. Функция СЖПРОБЕЛЫ убирает пробелы по краям и двойные пробелы внутри:

Формула
=ВПР(СЖПРОБЕЛЫ(C2);Товары!$A$2:$B$5;2;ЛОЖЬ)

Лишние пробелы часто приезжают вместе с выгрузками из других систем и с копированием из почты.

Склеить текст из нескольких ячеек помогает знак &. Разделители — пробел, двоеточие, слово — пишут в кавычках. Для заказа Ольги на пять блокнотов получится:

Формула
=B2&": "&C2&" × "&D2
Результат
Ольга: Блокнот × 5

Так собирают подписи, описания для отчёта, текст для писем. Есть и функции с тем же смыслом — СЦЕПИТЬ и СЦЕП, — но знак & работает везде одинаково.

Попробуйте сами. Создайте лист «Товары» со справочником из урока. На листе «Заказы» заполните столбец J «Категория» через ВПР. Затем в любом заказе временно замените товар на «Степлер» и сделайте так, чтобы вместо ошибки появилось «нет в справочнике».

Решение
  1. Создайте лист «Товары» и введите справочник: шапка в строке 1, четыре товара в строках 2–5.
  2. На листе «Заказы» в J1 — «Категория», в J2:
Формула
=ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ)
  1. Протяните J2 до J11. Бумажных заказов получится пять — блокноты и ежедневники, письменных тоже пять.
  2. Замените товар в одной строке на «Степлер» — в J появится #Н/Д.
  3. Оберните формулу в ЕСЛИОШИБКА и протяните заново:
Формула
=ЕСЛИОШИБКА(ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ);"нет в справочнике")
Результат
нет в справочнике
  1. Верните товар на место — категория появится снова.

Если у вас новая версия Excel или Google Таблицы, попробуйте ту же задачу через ПРОСМОТРX: четвёртый аргумент заменит ЕСЛИОШИБКА.

Мини-тест: проверьте себя

Закрепите урок: несколько вопросов, разбор ответов — в конце.

  1. В формуле =ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ) что означает 2?

  2. ВПР вернул #Н/Д, хотя товар «Ручка» в справочнике есть. Какая причина вероятнее всего?

  3. Зачем в ВПР писать последним аргументом ЛОЖЬ?

  4. Как склеить имя менеджера из B2 и товар из C2 через двоеточие и пробел?

  5. Что делает =ЕСЛИОШИБКА(ВПР(C2;Товары!$A$2:$B$5;2;ЛОЖЬ);"нет в справочнике")?