Содержание:
У нас есть две таблицы (например, старая и новая версии прайс-листа), которые нам нужно сравнить и быстро найти различия:
Сразу видно, что что-то добавилось в новый прайс-лист (финики, чеснок…), что-то исчезло (ежевика, малина…), изменились цены на некоторые товары (инжир, дыни…). Вам необходимо быстро найти и отобразить все эти изменения.
Для любой задачи в Excel почти всегда существует более одного решения (обычно 4-5). Для нашей задачи можно использовать множество разных подходов:
- функция ВПР (ВПР) — ищите названия товаров из нового прайс-листа в старом и отображайте старую цену рядом с новой, а затем ловите различия
- объединить два списка в один и затем построить на его основе сводную таблицу, где будут хорошо видны различия
- используйте надстройку Power Query для Excel
Давайте разберем их все по порядку.
Способ 1. Сравнение таблиц с помощью функции ВПР
Если вы совершенно не знакомы с этой замечательной функцией, то сначала загляните сюда и прочитайте или посмотрите видеоурок по ней – сэкономьте себе пару лет жизни.
Обычно эта функция используется для переноса данных из одной таблицы в другую путем сопоставления некоторого общего параметра. В этом случае мы будем использовать его, чтобы переместить старые цены в новую цену:
Те товары, в отношении которых возникла ошибка #Н/Д, отсутствуют в старом списке, т.е. были добавлены. Изменения цен также хорошо видны.
Плюсы этот метод: простой и понятный, «классика жанра», как говорится. Работает в любой версии Excel.
Минусы тоже есть. Для поиска товаров, добавленных в новый прайс-лист, вам придется проделать ту же процедуру в обратном направлении, т.е. подтянуть новые цены к старой с помощью ВПР. Если завтра размеры таблиц изменятся, то формулы придется корректировать. Ну, а на действительно больших таблицах (>100 тысяч строк) всё это счастье будет прилично тормозить.
Способ 2. Сравнение таблиц с помощью сводной таблицы.
Скопируем наши таблицы одну под другую, добавив столбец с названием прайс-листа, чтобы потом было понятно, из какого списка какая строка:
Теперь на основе созданной таблицы создадим сводку через Вставка — сводная таблица (Вставка — сводная таблица). Давайте бросим поле Продукт в область линий, полей Цена к области столбца и полю ЦENA в диапазон:
Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторов!) и отсортирует товары по алфавиту. Вы можете четко видеть добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если таковые имеются.
Общие итоги в такой таблице не имеют смысла, и их можно отключить на вкладке Конструктор – Общие итоги – Отключить для строк и столбцов (Дизайн — Общие итоги).
Если меняются цены (но не количество товаров!), то достаточно просто обновить созданную сводку, щелкнув по ней правой кнопкой мыши – обновление.
Плюсы: Этот подход на порядок быстрее работает с большими таблицами, чем VLOOKUP.
Минусы: нужно вручную скопировать данные друг под друга и добавить столбец с названием прайс-листа. Если размеры таблиц изменятся, то придется все делать заново.
Способ 3. Сравнение таблиц с помощью Power Query
Power Query — бесплатная надстройка для Microsoft Excel, которая позволяет загружать данные в Excel практически из любого источника, а затем преобразовывать эти данные любым желаемым способом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Данные), а для Excel 2010-2013 его нужно скачать отдельно с сайта Microsoft и установить – получите новую вкладку Power Query.
Прежде чем загружать наши прайс-листы в Power Query, их необходимо сначала преобразовать в интеллектуальные таблицы. Для этого выделите диапазон с данными и нажмите комбинацию на клавиатуре Ctrl+T или выберите вкладку на ленте Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу). Названия созданных таблиц можно исправить на вкладке Конструктор (оставлю стандартный Таблица 1 и Таблица 2, которые получаются по умолчанию).
Загрузите старую цену в Power Query с помощью кнопки Из таблицы/диапазона (Из таблицы/диапазона) на вкладке Данные (Дата) или из вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить – Закрыть и загрузить… (Закрыть и загрузить — Закрыть и загрузить в…):
… и в появившемся окне выберите Просто создайте соединение (Только подключение).
Повторите то же самое с новым прайс-листом.
Теперь давайте создадим третий запрос, который будет объединять и сравнивать данные из двух предыдущих. Для этого выберите в Excel на вкладке Данные – Получить данные – Объединить запросы – Объединить (Данные — Получить данные — Объединить запросы — Объединить) или нажмите кнопку Сочетать (Объединить) таб Power Query.
В окне объединения в выпадающих списках выбираем наши таблицы, выделяем столбцы с наименованиями товаров в них и внизу задаем метод объединения – Полный внешний (Полный внешний):
После нажатия на OK должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в заголовке:
В результате получаем объединение данных из обеих таблиц:
Лучше, конечно, переименовать названия столбцов в шапке, дважды щелкнув по более понятным:
А теперь самое интересное. Перейти на вкладку Добавить столбец (Добавить столбец) и нажмите на кнопку Условный столбец (Условный столбец). А затем в открывшемся окне введите несколько условий тестирования с соответствующими им выходными значениями:
Осталось нажать OK и той же кнопкой загрузите полученный отчет в Excel закрыть и скачать (Закрыть и загрузить) таб Главная (Главная):
Красота.
Причём, если в будущем в прайс-листах произойдут какие-либо изменения (добавятся или удалятся строки, изменятся цены и т.п.), то достаточно будет просто обновить наши запросы сочетанием клавиш. Ctrl+другой+F5 или по кнопке Обновить все (Обновить все) таб Данные (Дата).
Плюсы: Пожалуй, самый красивый и удобный способ из всех. Умно работает с большими таблицами. Не требует ручной правки при изменении размеров таблиц.
Минусы: требуется установка надстройки Power Query (в Excel 2010–2013) или Excel 2016. Имена столбцов в исходных данных менять нельзя, иначе мы получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.
- Как собрать данные из всех файлов Excel в определенной папке с помощью Power Query
- Как найти совпадения между двумя списками в Excel
- Объединение двух списков без дубликатов