ВПР в Excel: пошаговая инструкция

Команда УНИВЕР
Развитие 0 4.223 мая 2024

ВПР — это не только экзамен, но и функция Вертикального Просмотра (от англ. Vlookup — Vertical Look Up). Она полезна, если надо использовать данные из разных таблиц.

Например, у тебя есть заказы от клиента с названиями товаров. Нужно сделать расчет суммы, беря цены из прайса. Вручную находить каждую позицию, копировать и переносить в заказ трудоемко. И тут формула ВПР в Эксель очень кстати. Тем более что данные для нее можно брать с других листов и даже файлов.

Как работает функция ВПР

Представь, что тебе нужно найти стихотворение в книге. Содержание читай сверху вниз — нашел нужное, посмотрел, на какой странице — открыл ее, и можно читать. Также ищут себя абитуриенты в списке поступивших после сдачи экзаменов. Вот примерно так и работает ВПР — находит в указанном столбце нужное значение («название стихотворения») — и вставляет в ячейку таблицы «номер страницы». 

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

На первом месте искомое значение. Это может быть название товара из заказа, фамилия исполнителя или другой показатель.

На втором — таблица. Здесь выделяй диапазон — все строки, в которых нужно искать значение, от того столбца, в котором искомое значение, до того, откуда нужно взять значение и вставить в ячейку.

Третий — номер столбца. Не во всей таблице, а в той части, которую мы выделили. Например, если нужные данные находятся в столбце 4(D), но мы взяли со второго (в первом нумерация по порядку, она нам не нужна), то в формуле функции указывай номер 3.

Самое интересное — четвертый аргумент. Он логический — может иметь значение 1(ИСТИНА) или 0(ЛОЖЬ). 0 — значение совпадает точно. Ты уверен, что в ячейки не попало каких-то лишних пробелов, никто не сделал опечатку и не перепутал латинские и русские буквы. 0 — возможны варианты. Дальше рассмотрим пример на такой случай.

Теперь, когда ты ввел функцию, программа в левом столбце, указанного диапазона, ищет нужное значение. Когда находит, сдвигается вправо в той же строке в столбец с заданным номером — и копирует значение оттуда в ячейку. Готово!

Тебе нужно заполнить столбец в одной таблице данными из другой? Делай формулу в ячейке первой строки в списке — для этого можно набрать =ВПР, нажать на значок Fx в строке ссылок, или перейти в меню «Формулы» и воспользоваться «Мастером». В качестве искомого значения ссылаетесь на ячейку. Проверь, что она работает — и копируй, потянув за правый нижний угол рамки ячейки вниз. Вместо длительного просмотра таблиц вручную потрать несколько минут на то, чтобы задать аргументы ВПР, и секунды на ее копирование. Обрати внимание, что обозначения ячеек в диапазоне таблицы должны быть абсолютные — со знаками $ перед буквой столбца и номером строки. Их можно добавить в формулу вручную. Иначе при «протягивании» диапазона он будет смещаться — и результаты исказятся.

Как пользоваться ВПР: руководство с примерами

Рассмотрим некоторые ситуации, в которых пригодится эта функция.

Пример 1

Есть две таблицы — в одной сотрудники в алфавитном порядке с датами рождения, в другой — с должностями. В первой таблице добавляй столбец под переносимые данные. Ставь курсор на первую ячейку в нем и открывай «Мастер формул».

Искомое значение — ячейка с фамилией. Переходи в поле для второго аргумента, переключайся в таблицу с должностями и выделяй нужный диапазон. Вводи номер столбца в третье поле, 0 — в четвертое. Нажимай ОК.

Есть результат. Осталось протянуть формулу по столбцу.

Пример 2

Здесь сразу 3 таблицы: прайс, данные менеджеров, заказы.

Нужно посчитать стоимость каждого заказа — а для этого взять цены из прайса. Для этого используем ВПР (в таблице выделяй диапазон со столбца В, Товар). Тебе нужны точные значения, поэтому последний аргумент — 0.

Теперь в столбце «Сумма заказа» просто с помощью формулы умножай количество на цену.

Дальше нужно указать город — он жестко привязан к менеджерам, поэтому в формуле ВПР первый аргумент берется из столбца А, диапазон — третья таблица («Сотрудники»), номер столбца — 3, совпадение точное — 0.

Функция ВПР для нескольких условий

Здесь можно предложить 2 варианта:

  1. воспользоваться функцией ЕСЛИ — во втором аргументе, то есть таблице;
  2. вставить слева столбец и в него с помощью функции СЦЕПИТЬ внести сведения из тех полей, по которым идет отбор. И потом аналогично сформулировать аргумент для сравнения.

Функция ВПР по частичному совпадению

Что делать, если в строке могут быть, например, лишние пробелы? Или нужна любая строка с каким-то словом? В таком случае используйте в аргументе для сравнения знаки * и ? (любое или конкретное количество случайных знаков).

Если же нужно примерное значение — скажем, округление, в качестве последнего аргумента укажите не 0, а 1 (ИСТИНА). Например, если принята прогрессивная система премирования, расчет можно делать вот по такой формуле — и получать соответствующий результат.

Функция ВПР для нескольких таблиц

Для расчетов можно использовать данные из разных файлов — или листов в одном. Действовать нужно так же, только в формуле в аргументе перед табличным диапазоном будет еще указано, из какого источника они берутся.

Почему не работает ВПР в Excel

Если программа не выдает нужное значение, в первую очередь проверь, правильно ли ты ввел формулу — ошибки могут возникать из-за относительных ссылок, погрешностей в использовании внутри функций ЕСЛИ и других, выбора не тех столбцов для анализа, указания ИСТИНА вместо ЛОЖЬ и наоборот. Возможно, ты пытаешься сравнивать число и текст (не совпадают форматы данных), в ячейках есть непечатаемые символы или нужного значения просто нет во второй таблице.

Аналоги ВПР

Среди функций, которые можно использовать для поиска и переноса данных из одних таблиц в другие: ПРОСМОТРX, ИНДЕКС, ПОИСКПОЗ, СУММЕСЛИ. О них ты можешь узнать из бесплатного курса «Excel 2. Продвинутый уровень» .


Подходящие курсы и проекты
Команда УНИВЕР
Оценить новость