Сравнение двух таблиц

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

Поиск отличий в двух таблицах в Excel

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

Для любой задачи в 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. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку "Столбец такой-то не найден!" при попытке обновить запрос.

Ссылки по теме



05.12.2017 08:33:37
Вариант с Power Query просто шикарен.... Спасибо. Творческих успехов.
05.12.2017 12:45:21
Николай, спасибо за очередной познавательный пост!

Очень рад что Вы вновь затронули мою любимую тему в Excel – Power Query!
Соглашусь 3 вариант с функцией «объединить» (слияние) в Power Query самый красивый и функциональный…
Кстати другие виды соединения в функции Объединить в Power Query так же очень интересные!
Я тут как-то писал статью + делал видеоурок про расширенный функционал функции Объединить (слияние) в Power Query:

http://biweb.ru/sql_join_power_query.html

Если кому-то интересно! Добро пожаловать на мой блог! :)
05.12.2017 13:08:07
Спасибо большое, Сергей, за такую качественную ссылку на статью!
05.12.2017 13:07:17
Можно с помощью Power Query собрать (склеить) две таблицы разместив их одну под другой, а потом построить сводную... ну,. как вариант...
06.12.2017 13:29:04
Николай, Вы обладаете не только аналитическим умом, но и даром преподавания. Спасибо за изящное решение в PQ.
08.12.2017 12:09:37
Очень интересное решение. А можно ли данным способом 3 и более списка?
09.01.2018 21:46:10
Последовательно попарно - почему нет?
21.12.2017 06:40:34
Хотел спросить, а как сделать наоборот, имеется к примеру список (все уникальные), а проверить надо 2 такой же список, но там нет 1 или 2, 3 уникальных значений, и надо их вытащить в отдельном списке, например дежурство, в этом месяце не участвуют вот эти ФИО, в этом месяце не задействованы вот эти значения из таблицы 1 и т.д.
очень кстати полезная штука будет, особенно если можно без всяких макросов и ВБА 8)
09.01.2018 21:45:44
То же самое, но тип соединения таблиц при слиянии надо будет выбрать "Анти-Соединение слева" ;)
20.01.2018 08:20:57
а можно ли сделать так, чтобы функция ВПР когда не находит данные не писала #Н\Д а любые произвольные слова,буквы или просто пустую ячейку?
30.03.2018 17:47:40
=ЕСЛИОШИБКА(
22.12.2017 06:06:47
very good
27.12.2017 13:35:58
C Power Query Это здорово
Да и в Способе 2. Сравнение таблиц с помощью сводной в 2016 Excel можно в одной сводный таблице получить значения из разных таблиц. При этом не потребуется копировать две таблицы в одну.
09.01.2018 21:44:50
Вы имеете ввиду построение сводной через старый Мастер Сводных таблиц по нескольким диапазонам консолидации? Да, это хороший вариант - спасибо за уточнение. И, кстати, он в любой версии Excel работает, не только в 2016.
12.01.2018 15:59:30
Да именно это - построение сводной через старый Мастер Сводных таблиц по нескольким диапазонам консолидации
02.01.2018 10:29:19
Как в Power Query проанализировать сложную таблицу с огромным количеством строк и столбцов на наличие задвоений строк сразу по нескольким наборам условий (например, поиск задвоений по содержимому в стобцах 1, 2 и 4; и ещё по столбцам 1, 2 и 7)?
09.01.2018 21:37:06
Тут все зависит от того, что вы хотите на выходе. Можно выделить (с Ctrl) столбцы 1,2,4 и воспользоваться командой "Удалить дубликаты", например.
10.01.2018 06:00:40
Нет, я хочу отделить одну строку из пары задвоенных, поместив, например, на второй лист, и оставить уникальные строки на первом листе. Потерять строки в итоге нельзя.
У меня в PQ, встроенной в Excel 2016, при слиянии двух запросов отсутствует "Тип соединения"((( Поэтому выбрать "Полное внешнее соединение" и создать единую таблицу без потери строк не получается ((( Пыталась переустановить PQ, программа не дала. Можно что то с этим сделать?
22.06.2018 13:14:29
У вас старая версия Power Query (еще 2017 года, скорее всего). С тех пор вышло много обновлений, в одном из которых и было добавлено поле Тип соединения. Обновитесь и будет вам счастье :)
22.06.2018 12:28:28
У меня нет пункта добавить условный столбец
22.06.2018 13:13:27
Андрей, у вас старая версия Power Query. Обновите Office :)
29.07.2018 16:02:12
С помощью Power Query сначала сравниваю две таблицы, потом с помощью того же Power Query сравниваю результат с третьей таблицей. Подскажите как сделать чтобы при нажатии "обновить все" запросы обрабатывались последовательно, а не одновременно?