Самая популярная задача в Excel: Как перестать копировать вручную и начать жить

kirilljsx

Если бы за каждый запрос «как перенести цену из одного прайса в другой» платили по доллару, мы бы давно обогнали Илона Маска. Это - «Святой Грааль» Excel, функция, знание которой отделяет новичка от уверенного пользователя.

Речь идет о функции ВПР (VLOOKUP) и её современной, более мощной версии ПРОСМОТРX (XLOOKUP). Разберем раз и навсегда, как это работает, на примере реальной задачи.

Задача: «Найти цену по артикулу»

Предположим: у вас есть Заказ клиента (Список А) с артикулами товаров, но без цен. И есть огромный Прайс-лист поставщика (Список Б), где есть и артикулы, и актуальные цены.
Вам нужно проставить цены в заказ. Копировать вручную 100 позиций - долго и чревато ошибками.

Способ 1. Классика: ВПР (VLOOKUP)
Эту функцию используют 90% офисных сотрудников. Она ищет значение (артикул) в первом столбце таблицы и возвращает значение из соседнего столбца (цену).

Формула:
=ВПР(Что_ищем; Где_ищем; Номер_столбца; 0)

Что ищем: Ячейка с артикулом в вашем заказе (например, A2).
Где ищем: Выделяем всю таблицу прайс-листа (например, D:E). Важно: артикул должен быть в первом выделенном столбце.
Номер столбца: Порядковый номер столбца в прайсе, откуда нужно забрать цену (например, 2).
0: Это команда искать «точное совпадение» (иначе Excel может подтянуть цену похожего товара, что нам не нужно).

Итог: =ВПР(A2; D:E; 2; 0)

Минус: Если вы добавите новый столбец в прайс-лист, формула может сломаться, так как «2-й столбец» станет «3-м».

Способ 2. Современность: ПРОСМОТРX (XLOOKUP)

Если у вас Excel 2021 или Office 365, забудьте про ВПР. Функция ПРОСМОТРX делает то же самое, но проще, быстрее и надежнее.

Формула:
=ПРОСМОТРX(Что_ищем; Где_ищем_артикулы; Откуда_берем_цены)

Что ищем: Артикул (A2).
Где ищем артикулы: Столбец с артикулами в прайсе (D:D).
Откуда берем цены: Столбец с ценами в прайсе (E:E).

Итог: =ПРОСМОТРX(A2; D:D; E:E)

Почему это круче:

Не нужно считать номер столбца.
Можно искать данные слева от артикула (ВПР так не умеет).
Если артикул не найден, можно сразу написать текст ошибки: =ПРОСМОТРX(A2; D:D; E:E; “Нет в наличии”)

Бонус: Как быстро посчитать итоги? (Сводные таблицы)

Когда данных тысячи строк (например, отгрузки металлопроката за год по разным менеджерам), формулы становятся тормозом. Здесь королем становится Сводная таблица.

Как сделать:

Выделите любую ячейку в вашей таблице продаж.
Нажмите Вставка -> Сводная таблица.
В появившемся меню перетащите:
- Менеджер -> в поле «Строки».
- Сумма сделки -> в поле «Значения».

Excel мгновенно сгруппирует всех менеджеров и посчитает общую сумму продаж по каждому. Без единой формулы.

Резюмируем

Если вы хотите освоить Excel для работы, начните с этих двух инструментов:

ВПР/ПРОСМОТРX — чтобы связывать данные из разных таблиц.
Сводные таблицы — чтобы быстро делать отчеты и аналитику.

Этого набора достаточно для решения 80% ежедневных бизнес-задач.