Сравнение таблиц эксель через впр

Сравнение таблиц эксель через впр

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

Самая простая функция ВПР означает следующее:

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).

Используйте функцию ВПР для поиска значения в таблице.

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

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР ("Смит"; a1: B100; 2; ложь).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — "C" и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.

Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .

Читайте также:  Зарядное устройство для автомобильного аккумулятора большой емкости

Для каждой из перечисленных связей обратите внимание на следующее:

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца ( col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; "Юристы"! А:Д, 4, ложь).

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

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:

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

Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

  • функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
  • объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
  • использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

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

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

Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

Плюсы этого способа: просто и понятно, "классика жанра", что называется. Работает в любой версии Excel.

Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (>100 тыс. строк) все это счастье будет прилично тормозить.

Способ 2. Сравнение таблиц с помощью сводной

Скопируем наши таблицы одна под другую, добавив столбец с названием прайс-листа, чтобы потом можно было понять из какого списка какая строка:

Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .

Читайте также:  Драйвер для сд карты на ноутбук

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

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

Минусы : надо вручную копировать данные друг под друга и добавлять столбец с названием прайс-листа. Если размеры таблиц изменяются, то придется делать все заново.

Способ 3. Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :

. и в появившемся затем окне выбрем Только создать подключение (Connection Only) .

Повторите то же самое с новым прайс-листом.

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :

После нажатия на ОК должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в шапке:

В итоге получим слияние данных из обеих таблиц:

Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl + Alt + F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data) .

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

Минусы : Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку "Столбец такой-то не найден!" при попытке обновить запрос.

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

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

Читайте также:  Windows не видит жесткий диск seagate

Как работает функция ВПР в Excel: пример

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

  1. Переходим в ячейку второй таблицы под названием столбца «Цена».
  2. Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
  3. Заполняем аргументы функции.

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.

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

Функция ВПР в Excel и две таблицы

Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:

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

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

  1. Исходное значение: G3.
  2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
  3. Номер столбца: <3;4;5>. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
  4. Интервальный просмотр: ЛОЖЬ.
  5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;<3;4;5>;ЛОЖЬ)).
  6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «<>», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.

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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

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

Ссылка на основную публикацию
Adblock detector