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

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

Также рекомендуем потом прочитать про функцию ГПР, по смыслу очень похожую и не менее часто используемую.

Функция ВПР для чайников

Функция ВПР применяется для совмещения данных из разных таблиц.

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

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами».

Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию.

Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Синтаксис функции ВПР

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

ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Важно! Параметр 2 (таблица) нужно указывать так, чтобы в диапазон ячеек не попадали заголовки таблицы, если они есть. Нужны только сами данные.

В простом примере ниже эти аргументы рассмотрены на практике.

Как сравнить две таблицы в Excel функции ЕСЛИ

Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));””;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

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

Итак, изначально у нас есть две таблицы, которые Вы можете видеть на скриншоте ниже. Общим столбцом для обеих таблиц является «Должность».

Цель: добавить в первую (верхнюю) таблицу в столбец «Зарплата» данные из аналогичного столбца второй (нижней) таблицы.

Если Вы подумали, что это можно сделать и вручную, то это большая ошибка, поскольку строк в таблицах может быть многие тысячи, а порядок их следования в обеих таблицах вовсе не обязан совпадать!

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

Функция ВПР, инструкция

• Добавим ВПР в ячейку. Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.
• Указываем параметр «Искомое значение» В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).
• Указываем параметр «Таблица». Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12». Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.
• Указываем параметр «Номер столбца». Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).
• Указываем параметр «Интервальный просмотр». В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.

Обратите внимание на символ «$» перед номерами строк в диапазоне (аргумент ВПР номер 2). Это нужно для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки.

В результате для ячейки C4 мы получим следующую формулу: =ВПР(B4; A$10:B$12; 2; 0)

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

В нашем примере функция ВПР в Эксель для ячейки C4 работает следующим образом. Excel ищет слово «Директор» в колонке «Должность» второй таблицы (как и было указано ранее, поиск выполняется в первом столбце указанного диапазона). Искомое значение найдено во второй строке второй таблицы (заголовки не считаем).

Поскольку в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в результате то, что указано в колонке «Зарплата».

Это значение и будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше Вы можете видеть, что значение «40 000» вписано в ячейку C4 первой таблицы.

Скачать файл Excel (*.xlsx) с этим примером Вы можете после статьи.

Заполним аналогичными формулами остальные ячейки

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

В данном случае лишь необходимо учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен меняться. Именно поэтому перед номерами строк в диапазоне поставлены символы «$». Для первого аргумента (искомое значение) это, напротив, не нужно, поскольку для каждой строки первой таблицы искомое значение будет в разной ячейке (в нашем примере: A4, A5, A6).

Читайте также: Как разбить новый жесткий диск на разделы быстро и правильно. Простая и доступная инструкция.

Простой способ, как сравнить две таблицы в Excel

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

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

• Не указали символ доллара в диапазоне (аргумент 2). В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
• Неверно указан диапазон поиска. Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
• Неверно указан номер столбца (аргумент 3). Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

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

Сравнить две таблицы с помощью макроса VBA

[expert_bq id=»1570″]Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

Как пользоваться функцией ВПР в Excel: понятная инструкция для чайников, видео, сравнение двух таблиц, выпадающий список, примеры. Формула функции ВПР в Excel: примеры, несколько условий, расшифровка. Что такое, что позволяет делать функция ВПР в Excel? Как работает функция ВПР в Excel: инструкция для новичков, примеры использования. Статья-инструкция: Функция ВПР в Excel для чайников.

• Добавим ВПР в ячейку. Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.
• Указываем параметр «Искомое значение» В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).
• Указываем параметр «Таблица». Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12». Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.
• Указываем параметр «Номер столбца». Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).
• Указываем параметр «Интервальный просмотр». В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.

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

Как Сравнить Две Таблицы в Excel Впр

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

Синтаксис

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев)

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра

— номер столбца в диапазоне, из которого будет возвращено значение

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, то мы указываем значение ИСТИНА (TRUE) или 1 , в противном случае ЛОЖЬ (FALSE) или 0 . В случае, если данный параметр опущен, то он по умолчанию принимается равным 1 .

Держу пари, что многие из тех, кто знают функцию ВПР , как облупленную, прочтя описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0), либо же о диапазонном просмотре (ИСТИНА или 1).

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

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I:

1. Формулы можно использовать для распределения значений по диапазонам.
2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
4. Данный вид вернёт ошибку #Н/Д только, если не найдёт значения меньше или равного искомому.
5. Понять, что формула возвращает неправильные значения, в случае, если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II:

Производительность работы функции ВПР

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ ! Данный факт я проверил вполне надёжно.

Недостатки формулы

Реальное применение формулы

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть 2 символа подстановки: » ? » — заменяющий любой один символ в текстовой строке, и » * » — заменяющая любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому, если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

= ВПР(— D7 ; Продукты!$A$2:$C$5 ; 3; 0) — если A7 содержит текст, а таблица числа

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Если ВПР вернёт код ошибки #Н/Д , то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР , вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной и при протягивания массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5 .

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем Код ), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

[expert_bq id=»1570″]сравнение списков, находящихся на одном листе; сравнение таблиц, расположенных на разных листах; сравнение табличных диапазонов в разных файлах. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] При помощи же функции ВПР мы можем использовать Excel для совмещения двух таблиц, причём очень быстро. Начнём с ячейки C4 в первой таблице и на её примере покажем простую инструкцию по вставке функции ВПР.

Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

1. Формулы можно использовать для распределения значений по диапазонам.
2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
4. Данный вид вернёт ошибку #Н/Д только, если не найдёт значения меньше или равного искомому.
5. Понять, что формула возвращает неправильные значения, в случае, если ваш массив не отсортирован, довольно затруднительно.

Если Вы подумали, что это можно сделать и вручную, то это большая ошибка, поскольку строк в таблицах может быть многие тысячи, а порядок их следования в обеих таблицах вовсе не обязан совпадать!

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

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

Функция ВПР в Excel: инструкция для чайников

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

В другой таблице мы имеем тот же самый список с указанием стоимости этих материалов:

То есть, в первой таблице мы имеем количество материалов, а во второй – стоимость за штуку (руб/кв.м). Наша задача, вычислить общую стоимость всех материалов, которые были завезены на фабрику. Для этого нам нужно перенести показатели со второй таблицы в первую и при помощи умножения найти ответ.

• В первой таблице нам не хватает двух столбцов – «Цена» (то есть стоимость за 1 кв.м) и «Стоимость» (то есть общая стоимость завезенного материала). Добавим эти столбцы. Теперь выделите в столбце «Цена» верхнюю первую ячейку, запустите «Мастер функций» (нажать одновременно «F3» и «Shift»), во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР».

Запустите «Мастер функций», во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР»

• Далее в новом открывшемся окне напротив пункта «Искомое значение» наблюдаем следующие показатели: A1:А15. То есть программа фиксирует диапазон наименований материалов в соответствующем столбе «Материалы». То же самое программа должна показать и во второй таблице.

• Теперь разберемся со вторым пунктом – «Таблица» (вторая таблица со стоимостью материалов). Нажмите на этот пункт, затем выделите во второй таблице диапазон наименований материалов вместе с ценами. В итоге, результат должен быть следующим.

• Далее выделите результаты в пункте «Таблица» и нажмите на «F4», после чего в них появится новый символ «$» — таким образом программа будет ссылаться на показатели в «Таблица».

Выделите результаты в пункте «Таблица» и нажмите на «F4»

• Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».

В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ»

Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз

Все, мы связали обе наши таблицы. Вычислить общую стоимость материалов будет проще простого. Теперь нужно учитывать, что если прайс-лист изменится, то изменятся и полученные нами результаты. Поэтому нам нужно избежать этого при помощи следующих действий:

• Выделите все показатели в столбце «Цена», нажмите на нем правой кнопкой мышки и далее – на «Копировать».
• Снова нажмите правой кнопкой мышки по уже выделенным ценам и затем – на «Специальная вставка».
• В открывшемся окошке поставьте галку, как показано на скриншоте, и нажмите на «ОК»

Сравниваем две таблицы при помощи функции ВПР в Excel

Предположим, что прайс-лист изменился, и нам необходимо сравнить две таблицы – новые и старые цены:

Для этого воспользуемся соответствующей функцией в «Excel»:

• Далее повторяем действия, которые мы совершали выше – выделяем первую верхнюю ячейку, выбираем «ВПР», в пункте «Таблица» получаем результаты и нажимаем на «F4».

Работаем с несколькими условиями при помощи функция ВПР в Excel

Выше мы работали с одним условием – наименованием материалов. Но в реальности обстоятельства могут сложиться иначе. Мы можем получить таблицу с двумя и более условиями, например, как с наименованием материалов, так и с наименованием их поставщиков:

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

1 – объект поиска; 2 – место поиска; 3 – взятые нами данные

Работаем с выпадающим списком

Предположим, что у нас определенные данные (например, «Материалы») показываются в раскрывающимся списке. Наша задача, чтобы при этом было доступно видеть еще и стоимость.

[expert_bq id=»1570″]Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E1500:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Excel сравнение двух таблиц из разных файлов — Вэб-шпаргалка для интернет предпринимателей!

• Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».

При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра «Повторяющиеся» следует выбрать параметр «Уникальные». После этого нажать на кнопку «OK».