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

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

Поиск отличий в двух таблицах в 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 13:07:17
Можно с помощью Power Query собрать (склеить) две таблицы разместив их одну под другой, а потом построить сводную... ну,. как вариант...
05.12.2017 13:08:07
Спасибо большое, Сергей, за такую качественную ссылку на статью!
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 сравниваю результат с третьей таблицей. Подскажите как сделать чтобы при нажатии "обновить все" запросы обрабатывались последовательно, а не одновременно?
24.03.2019 09:55:01
При нажатии "Обновить все" Power Query, по идее, автоматически определяет зависимости между запросами и должен обновлять их сам в правильном порядке (включая сводные таблицы). Если у вас не так, то попробуйте обновить Power Query, для начала - возможно, дело в устаревшей версии.
07.01.2019 15:07:42
А почему  Power Query не находит совпадения, если в обоих строках только цифры (артикул производиетля). Если есть вначале артикула буква, все отлично, находится совпадение, но если цифры - нет. Как быть?
18.02.2019 20:43:31
Скорее всего потому, что Power Query считает число и число-как-текст разными значениями. Установите для обоих сравниваемых столбцов текстовый формат.
15.01.2019 14:36:17
Мне в силу работы необходимо сравнивать диапазоны с “Большими” текстовыми данными (названия договоров например и пояснения к ним), которые представлены как отдельные ячейки в таблице. В этих же таблицах и цены по ним. Самая большая сложность, что все на 30-40 листах книги. PQ хорошо помогает собирать эти листы, но к сожалению не может досконально сравнить текстовые ячейки (например добавилось ли какое то слово в названии или наоборот было удалено). Вот здесь и пришлось написать код VBA, который сравнивает два диапазона и подчеркивает разницу в каждой ячейке. Было бы неплохо Вам сюда добавить для сравнения и метод VBA.
21.03.2019 02:36:57
Доброго дня всем!
Подскажите а если строк 3000 на 2 листах одного документа и я делаю объединение запросов и неожиданно эксель говорит что не хватает ресурсов. 6 гигов оперативы, интел кор i3
В чем дело?
Причем в результирующий запрос рисует почему-то 5млн строк, откуда он взял столько?
24.03.2019 09:50:11
Сергей, нужно больше подробностей, чтобы ответить.
Приложите файл.Расскажите, какой тип слияния используете (добавление, объединение). Если объединение, то какой тип (левое внешнее, внутреннее и т.д.)
Всё это лучше на почту или на Форум - тут такие вещи неудобно обсуждать :)
27.03.2019 13:01:44
Спасибо за ответ написал вам)
12.04.2019 12:45:46
Есть два списка в одном 169 sku(строк наименований) В другом 161,но выгружается только 162 что делаю не так help?
14.04.2019 16:13:19
Эдуард, ну как вам помочь не видя ваших списков???
29.04.2019 18:45:29
Спасибо,разобрался)).Отличный сайт кстати!
Наверх