Сравнение двух таблиц
Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок...), что-то пропало (ежевика, малина...), у каких-то товаров изменилась цена (инжир, дыня...). Нужно быстро найти и вывести все эти изменения.
Для любой задачи в 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 в заданной папке с помощью Power Query
- Как найти совпадения между двумя списками в Excel
- Слияние двух списков без дубликатов
Подскажите пожулуйста, если какой то инструмент или как сделать
1. нужно, что то типо расширенного фильтра, но работающего с несколькими исходными диапазонами желательно на разных листах (расширенный фильтр работает только с одним) или как его заставить работать с несколькими исходными диапазонами
2. как поместить результат расширенного фильтра не вертикально, а горизонтально
нужно для автоматизации сравнения двух таблиц с множеством столбцов
очень кстати полезная штука будет, особенно если можно без всяких макросов и ВБА 8)
Да и в Способе 2. Сравнение таблиц с помощью сводной в 2016 Excel можно в одной сводный таблице получить значения из разных таблиц. При этом не потребуется копировать две таблицы в одну.
Подскажите а если строк 3000 на 2 листах одного документа и я делаю объединение запросов и неожиданно эксель говорит что не хватает ресурсов. 6 гигов оперативы, интел кор i3
В чем дело?
Причем в результирующий запрос рисует почему-то 5млн строк, откуда он взял столько?
Приложите файл.Расскажите, какой тип слияния используете (добавление, объединение). Если объединение, то какой тип (левое внешнее, внутреннее и т.д.)
Всё это лучше на почту или на Форум - тут такие вещи неудобно обсуждать
При объединении запросов, после разворачивания правого столбца, содержащего "микротаблицы" получившаяся таблица содержит в себе две сравниваемые таблицы с весьма странной "синхронизацией". Количество строк в итоговой таблице значительно превышает количество строк в сравниваемых таблицах. Исходные (сравниваемые) таблицы имеют по 5 столбцов каждая и 200 и 220 строк соответственно, а таблица после слияния имеет более 1000 строк. Строки в итоговой таблице дублируются по 6 раз (закономерности не нашел в повторениях). В чем может быть причина такой "синхронизации"? Наименования в строках должны быть уникальными? Без повторений? Т е если есть две и более позиции "лук" с разными ценами в сравниваемых таблицах, то такой способ сравнения нельзя использовать? Извините, если описал сумбурно или вопрос сформулирован слишком "по-дилетантски".
Обработка, сравнение, анализ прайс-листов поставщиков, на предмет меньшей цены.
Ну типа берешь два прайса и он тебе сверяет наименование количество и цену.
Загружаете прайс листы поставщика за 2 разных периода и после их сопоставления
вы увидите все новые позиции.
Заранее благодарю!!!!
В случае с большим количеством столбцов, не совсем удобно в последствии просматривать результат, приходиться крутиь бегунком в лево и право и перепрыгивать фильтром, как вариант можно объединить не только первый со вторым запросои, а так же второй с первым, а после соединить их (добавить), в результате получим по ширине таблицу в два раза меньше, хотя манипуляций больше, но кому как удобнее!