Сравнение Таблиц В Excel
Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия: С ходу видно, что в новом прайсе что-то добавилось (финики, честнок.), что-то пропало (ежевика, малина.), у каких-то товаров изменилась цена (инжир, дыня.). Нужно быстро найти и вывести все эти изменения. Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5).
Надстройка поможет сравнить две таблицы Excel, объединить диапазоны без дубликатов, найти. Методы сравнения таблиц в Microsoft Excel. Обновлено: 06 мая 201706 мая 2017 Автор: Максим Тютюшев. Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий или недостающих элементов. Сравнение таблиц, расположенных на разных листах; сравнение табличных диапазонов в разных файлах. Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel. May 6, 2017 - Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий.
Для нашей проблемы можно использовать много разных подходов:. функцию ВПР (VLOOKUP) - искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия.
Программа для фотошопа. объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия. использовать надстройку Power Query для Excel Давайте разберем их все последовательно. Сравнение таблиц функцией ВПР (VLOOKUP) Если вы совсем не знакомы с этой замечательной функцией, то загляните и почитайте или посмотрите видеоурок по ней - сэкономите себе пару лет жизни.
Обычно эту функцию используют для подтягивания данных из одной таблицы в другую по совпадению какого-либо общего параметра. В данном случае, мы применим ее, чтобы подтянуть старые цены в новый прайс: Те товары, напротив которых получилась ошибка #Н/Д - отсутствуют в старом списке, т.е.
Сравнение Таблиц В Excel 2010
Были добавлены. Изменения цены также хорошо видны. Плюсы этого способа: просто и понятно, 'классика жанра', что называется.
Работает в любой версии Excel. Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. Подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (100 тыс. Строк) все это счастье будет прилично тормозить.
Сравнение таблиц с помощью сводной Скопируем наши таблицы одна под другую, добавив столбец с названием прайс-листа, чтобы потом можно было понять из какого списка какая строка: Теперь на основе созданной таблицы создадим сводную через Вставка - Сводная таблица (Insert - Pivot Table). Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Ц ена в область значений: Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были. Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор - Общие итоги - Отключить для строк и столбцов (Design - Grand Totals).
Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши - Обновить (Referesh). Плюсы: такой подход на порядок быстрее работает с большими таблицами, чем ВПР. Минусы: надо вручную копировать данные друг под друга и добавлять столбец с названием прайс-листа.
Если размеры таблиц изменяются, то придется делать все заново. Сравнение таблиц с помощью Power Query Power Query - это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом.
Сравнение Таблиц В Excel Макрос
В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно и установить - получите новую вкладку 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.
Имена столбцов в исходных данных не должны меняться, иначе получим ошибку 'Столбец такой-то не найден!' При попытке обновить запрос.
Ссылки по теме.