Как сравнить две таблицы в эксель

Сравнить файлы(таблицы) Excel и CSV и найти разницу онлайн

Хотите сравнить файлы или таблицы Excel онлайн? На сайте xlCompare.com Вы можете сравнить два файла(таблицы) Excel, выделить отличия цветом, и сохранить их в третьем файле. xlCompare это лучшее средство для сравнения и объединения таблиц Microsoft Excel. xlCompare включает в себя онлайн сервис для сравнения таблиц Excel и настольное приложение для сравнения файлов Excel. Вам больше не нужно учить функционал Excel. Просто откройте две таблицы и получите различия между ними на Вашем экране.

Click to choose Excel or CSV file on your computer
Click to choose Excel or CSV file on your computer
Compare Tables
Merge Tables
Common Rows
Please, help us to improve our application
Rate quality of our service
Last ratings and reviews from our users

Пошаговая инструкция как сравнить две таблицы Excel (CSV) онлайн

Сайт xlCompare.com это лучшее средство для поиска отличий в таблицах Excel. На нашем сайте Вы сможете открыть и сравнить 2 таблицы Excel, выделить отличия, перенести общие или добавленные строки в другой файл. Если Вам нужно быстро увидеть отличия в двух таблицах Excel, выбор сайта xlCompare — это правильное решение.

Как сравнить две таблицы Excel онлайн?

Что может этот онлайн сервис сравнения файлов Excel?
Онлайн сервис поиска отличий в файлах Excel это простое средство сравнения которое позволит Вам:

1. Визуально увидеть отличия между двумя таблицами Excel .
2. Фильтровать новые, измененные, и общие строки между двумя таблицами Excel.
3. Экспортировать видимые строки в приложение Excel используя кнопку Копировать.

Кнопка Копировать экспортирует видимые строки в Буфер Обмена в формате HTML, который можно вставить в файл Excel, документ Word или в почтовое сообщение.

Как сравнить два файла Excel онлайн шаг за шагом

Онлайн сервис повторяет часть функций приложения xlCompare на веб странице. Чтобы сравнить 2 файла Excel сделайте следующее:

1. Откройте Ваши файлы в панелях ФАЙЛ1 и ФАЙЛ2.
2. Выберите таблицы для сравнения, если файлы содержат несколько таблиц.
3. Укажите ключевые столбцы и заголовок , если Ваш файл имеет табличную структуру.
4. Кликните на кнопке Сравнить Таблицы и получите отчет о сравнении файлов Excel на этой странице.

Результаты сравнения появятся в отдельной панели ниже сравниваемых таблиц.
Что делает кнопка ВПР?

ВПР это функция сравнения выбранных столбцов в двух таблицах Excel онлайн, которая подобна использованию функции ВПР в Excel.

Обратитесь к разделу ниже за детальной информацией по использованию функции ВПР.
Как выбрать ключевой столбец в таблице Excel?
Ключевые Столбцы это важная часть сравнения таблиц Excel.
xlCompare позволяет выбрать следующие комбинации Ключевых Столбцов:

1. Без Ключей
2. A
3. A, B
4. A, B, C
5. A, B, C, D

Для выбора Ключевых Столбцов служит выпадающий список, расположенный над таблицей.

Если Вы хотите установить ключом только столбец C и оставить столбцы A и B как данные — Вам нужно установить и использовать наше приложение для сравнения файлов xlCompare.

Приложение xlCompare позволяет задавать любую комбинацию Ключевых Столбцов.
Как выбрать строки заголовка в таблице Excel?
xlCompare позволяет выбирать строки заголовка таблицы среди первых 5 строк.

Чтобы выбрать Строки Заголовка используйте выпадающий список расположенный выше таблицы после списка выбора Ключевых Столбцов.

Если Ваша таблица Excel содержит более широкий заголовок, для которого не достаточно 5 строк Вам следует установить приложение xlCompare.

Приложение xlCompare позволяет выбрать любое количество строк заголовка.
Как найти общие строки в двух таблицах Excel?

Онлайн сервис xlCompare позволяет находить общие строки между двумя таблицами Excel. Если у Вас есть 2 списка данных в которых Вы хотите найти общие записи — вы получите их всего за несколько секунд.

Пошаговая инструкция получения общих записей в двух таблицах Excel:

1. Откройте Ваши файлы Excel на этой странице в панелях ФАЙЛ1 и ФАЙЛ2
2. Выберите ключевые столбцы и строки заголовка , если таковые присутствуют в Ваших таблицах.
3. Кликните на кнопке Сравнить Таблицы для получения отчета о сравнении двух выбранных таблиц Excel
4. Примените фильтр Общие строки в списке выбора фильтров.
5. Чтобы скопировать видимые на экране строки в приложение Excel или Numbers используйте кнопку Копировать в окне отчета.

Как найти одинаковые данные в двух таблицах Excel?
Сайт xlCompare позволяет найди одинаковые строки на двух таблицах Excel.
Используйте данную инструкцию для поиска совпадающих строк в файлах Excel:

1. Выберите Ваши файлы Excel на этой странице
2. Если Ваша таблица имеет заголовок — укажите ключевые столбцы и строки заголовка
3. Сравните 2 выбранных таблицы Excel с помощью кнопки Сравнить Таблицы
4. Примените фильтр совпадающие строки в списке выбора фильтра
5. В панели результатов будут отображены только строки, ячейки которых совпадают в обеих таблицах
6. Если Вы хотите сохранить эти данные на диске — кликните на кнопке Копировать в окне отчета. Она скопирует ячейки в Буфер Обмена и Вы сможете вставить их в Excel

Как выделить цветом отличия в 2-х файлах Excel?

Предположим, Вам нужно выделить цветом отличия в двух таблицах Excel и отправить этот отчет коллеге по электронной почте.

Сделайте следующее:

1. Выберите файлы которые Вы хотели бы сравнить на этой стринице
2. Если Ваша таблица имеет заголовок — укажите ключевые столбцы и строки заголовка
3. Сравните 2 выбранных таблицы Excel с помощью кнопки Сравнить Таблицы
4. Сайт xlCompare выделит все измененные ячейки оранжевым цветом
5. Для экспорта этих данных включая форматирование кликните на кнопке Копировать которая находится над отчетом
6. Теперь Вы можете вставить эту таблицу из Буфера Обмена в письмо, сохранив форматирование

Как сравнить две таблицы на разных листах в Excel?
Воспользуйтесь пошаговой инструкцией:

1. Выберите один и тот же файл в панели ФАЙЛ1 и панели ФАЙЛ2
2. Выберите разные тиблицы используя выпадающий список под таблицей
3. Сравните листы с помощью кнопки Сравнить Таблицы

ВАЖНО: Не забудьте выбрать ключи и строки заголовка.
Есть ли альтернативные методы сравнения файлов Excel?
Да конечно. Есть еще несколько методов сравнения таблиц Excel и все они хорошо описаны в Интернете:

1. Условное Форматирование
2. Функции EQUAL, ВПР, XLOOKUP
3. VBA макросы

Условное форматирование и функции Excel отлично работают если таблицы имеют одинаковую структуру. Если порядок строк хотя бы в одном месте не совпал — метод работать не будет.

Для написания VBA макросов нужно быть знакомым с программированием на VBA.

Для использования функций типа XLOOKUP Вам прийдется потратить время чтобы научиться ими пользоваться.

Вывод — отложите эти методы. Сайт xlCompare.com дает возможность легко и бесплатно получить тот же результат. Просто перетащите Ваши файлы на эту станицу и получите результат.

Если Вы хотели бы получить результат в другом виде — мы будем рады услышать и внедрить Ваши пожелания.

Общие Вопросы

Что такое Ключевой Столбец (Первичный Ключ)?
Первичный Ключ это такой столбец в таблице, который содержит уникальные значения в каждой строке.

В теории реляционных баз данных Ключ должен содержать уникальные значения, но на практике это встречается не всегда, поэтому xlCompare допускает дублирующиеся значения в ключевых столбцах.

Примеры ключевых столбцов — Номер Плательщика Налогов, номер телефона, идентификатор транспортного средства (VIN), .

Что такое Строка Заголовка?
Строка Заголовка в таблице как правило это верхняя строка, содержащая названия столбцов в таблице.
Все вместе строки заголовка определяют структуру таблицы.
В большинстве своем таблицы баз данных экспортируются вместе с названиями полей — заголовком.

Для сравнения таблиц сроки заголовка очень важны, поскольку позволяют определить соответствующие столбцы.

Если Ваша таблица содержит заголовок, его обязательно нужно указать перед сравнением.
Этот сервис бесплатен?

Онлайн сервис по сравнению файлов Excel на сайте xlCompare бесплатен. Вы ничего не платите за пользование им.

Почему тогда на сайте бесплатного сервиса есть страница КУПИТЬ?

Приложение xlCompare которое Вы можете установить на свой компьютер не бесплатно. Приложение распространяется по бессрочной лицензии с единоразовой оплатой. Цена и прочая информация для покупателей находится на странице КУПИТЬ.

Загружаете ли Вы мои файлы на свой сайт?

Нет. Вам не о чем беспокоиться.
Мы уважаем Вашу приватность.

Все операции (сравнение, фильтрация, и т.д.) выполняются в Вашем броузере на Вашем компьютере. Spreadsheet Tools не имеет доступа к Вашим файлам. Этот онлайн сервис абсолютно безопасен.

Вы можете выключить Интернет соединение и продолжать пользоваться нашим онлайн сервисом по сравнению файлов.

Могут ли третьи лица получить доступ к моим данным?

Нет. Это не возможно.

Этот онлайн сервис не передает Ваши файлы и другие данные в сеть. Все манипуляции с данными проводятся на Вашем компьютере.

Ваши файлы не выходят за пределы Вашего компьютера.
Какие форматы файлов Вы поддерживаете?

Наш онлайн сервис работает с XLSX, XLSM и CSV файлами. Вы можете открыть любой из этих файлов и получить детальный отчет о сравнении.

Как сравнивать файлы в других форматах?

Наш сервис позволяет сравнивать таблицы в любом формате. Используйте пошаговую инструкцию написанную ниже:

1. Откройте файл в приложении для его редактирования (Excel, Numbers, и т.д.) и скопируйте таблицу в Буфер Обмена
2. Вставьте ячейки на эту страницу с помощью комады Вставить
3. Сравните вставленные ячейки и получите отчет о различиях.
4. Примените фильтр к ячейкам (общие, измененные, уникальные) и скопируйте его в Буфер Обмена
5. Вставьте ячейки обратно в приложение Excel (Numbers, . ) с этой страницы.

Могу ли я сравнить формулы в моих файлах Excel?

Онлайн сервис сравнивает только значения ячеек. Формулы и форматирование не сравниваются.

Чтобы сравнивать формулы в таблицах Excel — установите приложение xlCompare на Ваш компьютер со страницы: СКАЧАТЬ.

Могу ли я открыть и сравнить два больших файла Excel?

Да, конечно. Размеры таблиц ограничены только возможностями Вашего броузера и памятью установленной на компьютере. Тем не менее если Ваш документ очень большой — мы советуем установить приложение xlCompare на Ваш компьютер. Оно способно открывать файлы очень большого размера.

У меня есть вопросы. Как связаться с Вами?
Используйте форму на странице СВЯЗАТЬСЯ С НАМИ для отправки сообщений нам.
Также мы будем рады прочитать Ваш отзыв в комментариях внизу этой страницы.

Как сравнить две таблицы в Excel на совпадения ВПР?

ВПР: Как это работает?
Как сравнить столбцы в таблицах командой ВПР(VLOOKUP)?
Если кратко, то команда ВПР проверяет существование значения из ФАЙЛ2 в ФАЙЛ1 .

Используйте эту команду, когда Вам нужно сравнить значения в двух столбцах на совпадения или наличие новых значений. Ячейки в остальных столбцах не участвуют в сравнении.

Таблицы ФАЙЛ1 и ФАЙЛ2 могут иметь разную структуру.

1. Команда ВПР в xlCompare работает подобно функции ВПР в Excel.
2. ФАЙЛ1 интерпретируется как словарь.
3. ФАЙЛ2 считается файлом данных.
4. ВПР берет значение из выбранного столбца в ФАЙЛ2 и ищет его в выбранном столбце в ФАЙЛ1.
5. Строки со значениями которые не найдены выделяются красным цветом в панели результатов.
6. Все остальные ячейки не участвуют в сравнении и не выделяются цветом.
7. Команда ВПР сравнивает ТОЛЬКО два выбранных столбца!

Пример использования команды ВПР

У Вас есть список допустимых номеров SKU#, которые зарегистрированы в Вашей базе данных. Нужно проверить есть ли новые значения SKU# в полученной от клиентов таблице.

Команда ВПР идеальное решение для этого случая.
Как выбрать столбец для команды ВПР?
Кликните на заголовке столбца в таблице.
Область заголовка выбранного столбца будет отмечена синим цветом.
Можно ли выбрать больше одного столбца?
Нет. Команда ВПР допускает выбор только одного столбца.
После того как Вы выберете другой столбец — предыдущий выбор будет аннулирован.
Как отменить выбор столбца?
Повторный клик на заголовке отменяет выбор столбца.
Как в Excel найти все одинаковые значения в разных листах?
Как сравнивать столбцы двух таблиц Excel используя ВПР:

1. Выберите таблицы для сравнения на этой странице
2. Выберите столбцы для сравнения кликнув на их заголовок. Два столбца можно сравнить только если они выбраны!
3. Сравните две таблицы кликнув на кнопке ВПР
4. xlCompare отметит отсутствующие в словаре (первой таблице) записи красным цветом.
5. Выберите нужный Вам Фильтр чтобы показать только новые значения или только совпадающие.
6. Экспортируйте эту таблицу обратно в Excel через Буфер Обмена используя кнопку Копировать .

Как получить значения из ФАЙЛ1 которых нет в ФАЙЛ2?
ВПР дает Вам значения из ФАЙЛ2 которых которых нет в ФАЙЛ1.
Как получить список значений из словаря, которых нет в файле данных?
Поменяйте местами ФАЙЛ1 и ФАЙЛ2, выберите столбцы для сравнения и примените ВПР снова.

Все что нужно это поменять местами словарь и таблицу с данными. Это очень простой и быстрый метод получения результата.

Работают ли Копировать и Фильтр при сравнении столбцов ВПР?
Да, обе команды применимы к функции ВПР.
Фильтр в данном случае имеет упрощенный набор опций — только новые и совпадающие значения.
При сравнении двух столбцов не может быть измененных ячеек.

Команда Копировать работает также как и при сравнении таблиц. Она копирует содержимое отчета в Буфер Обмена.

Как извлечь данные в другой Excel файл?

Скопировать отчет о сравнении в другой файл
Как экспортировать отчет о сравнении в XLSX файл и сохранить его на диске?

Часто увидеть отличия в двух таблицах Excel недостаточно, например как отправить отчет с несовпадающимим записями коллеге по e-mail?

Используйте кнопку Копировать на панели результатов.

Кнопка Копировать поместит таблицу из панели результатов в Буфер Обмена. После того как данные попадут в Буфер Обмена их можно вставить в:

1. Новый файл Excel
2. Документ Word
3. Письмо в формате HTML
4. Блокнот, и т.д.

Копирование в буфер обмена это идеальный метод для экспорта данных.
Как фильтровать данные в отчете?
Как увидеть только совпадающие или несовпадающие записи в таблицах?

Отчет о сравнении содержит все ячейки которые есть в обоих таблицах. Как отделить несовпадающие данные и перенести их в другой файл?

Используйте выпадающий список Фильтр для этого.
Фильтр включает в себя следующие режимы:

1. Без Фильтра (по умолчанию)
2. Добавленные Строки
3. Удаленные Строки
4. Совпадающие Строки
5. Измененные Строки
6. Общие Строки

В чем разница между Совпадающими, Измененными и Общими строками?
Совпадающие — полностью совпадающие строки в двух таблицах Excel. Все ячейки совпадают.

Измененные — строка содержит хотя бы одну измененную ячейку, которая имеет разные значения в ФАЙЛ1 и ФАЙЛ2.

Общие — комбинация равных и удаленных строк — это множество общих строк в двух файлах Excel.
Сравнить два файла Excel и извлечь совпадающие данные
Найти строки которые есть в двух таблицах Excel и перенести их в новый файл.
Выполните следующие шаги:

1. Выберите Ваши файлы Excel в онлайн сервисе сравнения файлов
2. Укажите ключевые столбцы и строки заголовка если они есть в Вашей таблице
3. Нажмите кнопку Сравнить Таблицы и получите отчет о сравнении
4. Примените фильтр Общие Строки в выпадающем списке фильтра
5. Ваш броузер содержит строки, общие для двух таблиц.
6. Кликните на кнопке Копировать чтобы поместить таблицу с результатами в Буфер Обмена и вставьте ее в новую таблицу Excel, который можно сохранить на диске как XLSX\CSV файл.

Сравнить два списка и извлечь не совпадающие данные

Предположим, у Вас 2 таблицы в Excel. Одна из них была изменена. Как найти отличия? Как сохранить их в формате XLSX?

Ниже приведена инструкция, которая укажет Вам как извлечь отличия между списками.

1. Выберите Ваши файлы Excel в онлайн сервисе сравнения файлов
2. Укажите ключевые столбцы и строки заголовка если они есть в Вашей таблице
3. Нажмите кнопку Сравнить Таблицы и получите отчет о сравнении
4. Примените фильтр Измененные Строки в выпадающем списке фильтра
5. В панели результатов появятся только строки которые были изменены
6. Кликните на кнопке Копировать чтобы поместить таблицу с результатами в Буфер Обмена и вставьте ее в новую таблицу Excel, который можно сохранить на диске как XLSX\CSV файл.

xlCompare — средство сравнения файлов Excel

Приложение xlCompare это лидер среди средств сравнения файлов Excel на рынке. xlCompare детально сравнит Ваши таблицы Excel и предоставит подробный отчет. xlCompare существенно улучшит Вашу продуктивность. С xlCompare сравнение файлов Excel становится очень простым!

Сравнить две таблицы excel

Решим достаточно тривиальную задачу с помощью языка python — сравним две таблицы excel и выведем результат в третью. Что может быть проще, и почему просто не использовать средства самой программы, входящей в пакет office? Попробуем разобраться.

Дано

У нас есть две таблицы с условными названиями «Прайс1» и «Прайс2».

Обе имеют расширение .xlsx и открываются программой excel без каких-либо дополнительных действий. Но есть проблема — таблицы доступны в формате read-only дабы никто кроме владельца не мог изменить данные. Поэтому, для того, чтобы начать применять какие-либо формулы в самих таблицах необходимо таблицы продублировать, сохранив их дубликаты.

Вторая проблема — позиции товаров перемешаны, идут не в алфавитном порядке и вообще могут иметь разное количество позиций наименований.

И проблема третья — столбец с количеством товара не обязательно следует за столбцом с наименованиями товаров.

Как сравнить данные таблицы с наименьшими трудозатратами и сделать, так чтобы это сравнение легко адаптировалось под иные вводные?

Какие предложения от excel ?

Как правило, в задачах подобного рода применяется функция ВПР.

Например формула может выглядеть следующим образом:

=ЕСЛИОШИБКА(ВПР(F4;$B$3:$C$5;2;0);0)

Логика следующая: берем позицию в Прайсе2 и ищем ее по Прайсу1, выводя значение.

Однако, этот вариант работает не для всех случаев: если в Прайсе2 нет позиции, которая была в Прайсе1, формула не работает —

Формула посложней

Она повторяет предыдущую, но уже учитывает значение (количество товара) при поиске.

=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$5;2;0);0)=G3;"";ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$5;2;0);0))

Но она также бесполезна, если позиция выбыла в Прайсе2:

И третий вариант формулы

Для небольшого удобства прайсы разнесены по разным листам одной таблицы, а сама итоговая таблица на третьем листе.

Для ячеек в столбце с Прайсом1 формула примет вид:

=ЕСЛИ(ЕНД(ВПР(A2;'D:\Users\al\Desktop\[Прайс1.xlsx]Лист1'!$B$3:$B$5;1;0));"Нет";ВПР(A2;'D:\Users\al\Desktop\[Прайс1.xlsx]Лист1'!$B$3:$C$5;2;0))

Для ячеек в столбце с Прайсом2:

=ЕСЛИ(ЕНД(ВПР(A2;'D:\Users\al\Desktop\[Прайс2.xlsx]Лист1'!$B$3:$B$5;1;0));"Нет";ВПР(A2;'D:\Users\al\Desktop\[Прайс2.xlsx]Лист1'!$B$3:$C$5;2;0))

Выглядит это следующим образом:

Здесь видно, что формула учитывает моменты, если в Прайсах пропадают или появляются позиции. В таблице они обозначены словом «Нет».

Формула работает. Но, помимо ужасающих размеров, она имеет одно «но», точнее два «но».
Чтобы все работало корректно, необходимо:

• правильно указать диапазоны из Прайсов (выделить их в Прайсах Ctrl+Shift+Enter и перенести в формулу);
• позиции товаров в финальной таблице должны идти с учетом всех выбывших и/или прибывших позиций в обоих Прайсах. Сама формула не будет искать эти позиции в Прайсах и в вставлять в итоговую. Она просто берет наименование в итоговой таблице и ищет его в Прайсах, записывая количество товара и/или его отсутствие.

Посмотрим, что предлагает python.

Python в деле

Можно пойти через использование библиотеки openpyxl и тогда решение будет выглядеть примерно так.

*Код написан не для прайсов, но для вычисления прямого и косвенного владения в компаниях, но логика та же.

текст программы

import openpyxl,pprint from openpyxl.utils import get_column_letter,column_index_from_string wb = openpyxl.load_workbook('Прайс1.xlsx') sheet=wb.get_active_sheet() wb2 = openpyxl.load_workbook('Прайс2.xlsx') sheet2=wb2.get_active_sheet() h = open('struct.txt','a') test=<> test2=<> test3=[] poisk=str(input('компания: ')) #test - словарь из "кто владеет:номер строки) for row in sheet['A2':'A290']: for cellObj in row: i = cellObj.value b = cellObj.row test.setdefault(i,b) #test2 - словарь из "кем владеют:номер столбца) for row in sheet['B1':'HH']: for cellObj in row: i = cellObj.value b = cellObj.column c = column_index_from_string(b) #переводим названия столбцов excel в цифры test2.setdefault(i,c) print('\n'+'прямое владение') # прямое владение for row1 in sheet['B2':'HH290']: for cellObj in row1: if cellObj.value ==None: #пропускаем пустые значения в клетках continue i = float (cellObj.value)/100 #A в B s =sheet.cell(row=cellObj.row,column=1).value if s!=poisk: continue d=sheet.cell(row=1,column=column_index_from_string(cellObj.column)).value #B (кем владеют) for k,v in test.items(): for u in range (2,217): # все значения- B2:F6 if sheet.cell(row=v, column=u).value ==None: continue b = sheet.cell(row=v, column=u).value # % владения q=float(''.format(i*100)) y=sheet.cell(row=1,column=u).value #кем владеют p=s+' владеет '+ d +' - '+str(q)+'%' if p not in test3: test3.append(p) print(p) h.write(p+'\n') print('\n'+'1-е косвенное участие') # 1-е косвенное участие for row1 in sheet['B2':'HH290']: for cellObj in row1: if cellObj.value ==None: #пропускаем пустые значения в клетках continue i = float (cellObj.value)/100 #A в B s =sheet.cell(row=cellObj.row,column=1).value if s!=poisk: continue d=sheet.cell(row=1,column=column_index_from_string(cellObj.column)).value #B (кем владеют) for k,v in test.items(): if d in k: # если кем владеют, есть в кто владеет - то ищем по строке значение for u in range (2,217): # все значения if sheet.cell(row=v, column=u).value ==None: continue b = sheet.cell(row=v, column=u).value # % владения q=float(i)*float(b) #процент косвенного владения A через B в С q1=float(''.format(q)) #5 знаков после запятой и * 100 y=sheet.cell(row=1,column=u).value #кем владеют print (' через '+ d + ' в ' + y +' - '+str(q1)+'%') h.write(s+' через '+ d + ' владеет ' + y +' - '+str(q1)+'%'+'\n') h.write('\n') 

Программа собирает все наименования и количество товара по ячейкам в обоих Прайсах, далее заполняет итоговую таблицу excel наименованиями и, найдя по координатам, количество товара — также и значениями количества товара.

Работает. Но громоздко и легко запутаться.

Воспользуемся возможностями библиотеки pandas, если она не установлена, то pip install pandas.
Импортируем библиотеку и считаем Прайсы в датафреймы(наборы данных):

import pandas as pd df1 = pd.read_excel('Прайс1-.xlsx', sheet_name = 'Лист1') df2 = pd.read_excel('Прайс2-.xlsx', sheet_name = 'Лист1') 

Произведем слияние датафреймов, чтобы охватить случаи, когда товары исчезают/появляются как в первом Прайсе, так и во втором:

m = (df1.merge(df2, how='outer', on=['товар','Количество'], suffixes=['', '_new'], indicator=True)) m2 = (df2.merge(df1, how='outer', on=['товар','Количество'], suffixes=['', '_new'], indicator=True)) 

Создадим третий датафрейм из запросов к двум предыдущим и уберем оттуда дубликаты:

m3=pd.merge(m.query("_merge=='right_only'"), m2.query("_merge=='right_only'"), how ='outer').drop_duplicates(subset=['товар','Количество']) 

Осталось сохранить новую таблицу:

m3.query("_merge=='right_only'").to_excel('out.xlsx') 

На выходе мы получаем итоговую таблицу:

Как видно, в нее не попала позиция «сок», так как в этой позиции не произошло изменений.

Обозначены позиции «пиво» со старым и новым значениями, а также учтены позиции, которые «добавились» и «пропали» в Прайсах.

Какое из решений использовать — дело вкуса.

Однако данный вариант имеет преимущества:

• не требует перевода таблиц из «read-only»;
• нет необходимости вручную выправлять формулы по столбцам и сами таблицы.

Надеюсь, решения, приведенные в статье, окажутся полезными.

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

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения. Для любой задачи в 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
• Слияние двух списков без дубликатов

Сравнение двух таблиц с целью выявления записей без соответствия

Иногда может потребоваться сравнить две таблицы в Access и определить записи в одной из таблиц, не имеющие соответствующих записей в другой таблице. Эти записи проще всего найти с помощью мастера запросов на поиск записей, не имеющих подчиненных. Когда мастер сформирует ваш запрос, его структуру можно будет изменить, добавив или удалив поля либо добавив объединения между двумя таблицами (чтобы указать поля, значения которых должны совпадать). Вы также можете создать запрос на поиск записей, не имеющих подчиненных, самостоятельно, не прибегая к помощи мастера.

В этой статье описывается, как запустить мастер запросов на поиск записей, не имеющих подчиненных, как изменить результаты работы этого мастера и как создать такой запрос самостоятельно.

В этой статье

• Когда следует выполнять поиск записей, не имеющих подчиненных
• Использование мастера для сравнения двух таблиц
• Создание и изменение запроса для сравнения по нескольким полям
• Создание собственного запроса на поиск записей, не имеющих подчиненных

Когда следует выполнять поиск записей, не имеющих подчиненных

Ниже описаны две распространенные ситуации, в которых может потребоваться сравнить две таблицы и найти записи, не имеющие подчиненных. В зависимости от ситуации поиск записей, не имеющих подчиненных, может стать первым из нескольких требуемых шагов. В этой статье рассматривается только поиск таких записей.

• Одна таблица используется для хранения данных об объектах (например, товарах), а другая таблица — для хранения данных о действиях (например, заказах) в отношении эти объектов. Например, в шаблоне базы данных «Борей» данные о товарах хранятся в таблице «Товары», а данные о том, какие товары включены в тот или иной заказ — в таблице «Сведения о заказе». Поскольку (в соответствии со структурой) данные о заказах отсутствуют в таблице «Товары», невозможно только на основе данных таблицы «Товары» определить, какие товары никогда не продавались. Эти сведения также нельзя получить только на основе данных таблицы «Сведения о заказе», поскольку эта таблица содержит только данные о товарах, по которым были продажи. Необходимо сравнить эти две таблицы, чтобы определить, какие товары никогда не продавались. Если нужно получить список объектов из первой таблицы, по которым не содержится соответствующих действий во второй таблице, можно воспользоваться мастером запросов на поиск записей, не имеющих подчиненных.
• Есть две таблицы, которые содержат пересекающиеся, избыточные или противоречивые данные, и требуется консолидировать эти таблицы в одну. Предположим, например, что есть две таблицы, которые называются «Заказчики» и «Клиенты». Таблицы практически совпадают, но в одной или обеих из них есть записи, отсутствующие в другой таблице. Перед тем как объединять таблицы, нужно определить, какие записи в них являются уникальными. Если вы оказались в подобной ситуации, рассмотренные в статье способы помогут решить эту задачу, однако потребуется предпринять ряд дополнительных шагов. Можно запустить мастер запросов на поиск записей, не имеющих подчиненных, чтобы выявить записи без соответствия, однако для извлечения объединенного набора записей следует использовать эти результаты для создания запроса на объединение. Если вы хорошо знакомы с инструкциями SQL, можете пропустить поиск записей, не имеющих подчиненных, и создать запрос на объединение вручную. Часто с помощью поиска повторяющихся данных в двух или нескольких таблицах можно решить проблему пересечения, избыточности или противоречивости данных.

Дополнительные сведения о создании запросов на объединение, а также о поиске, скрытии и удалении повторяющихся данных см. в статьях, ссылки на которые приведены в разделе См. также.

Примечание: В примерах, которые описываются в этой статье, используется база данных, созданная с использованием шаблона базы данных «Борей».

Инструкции по настройке базы данных «Борей»

1. На вкладке Файл нажмите кнопку Создать.
2. В зависимости от используемой версии Access поиск базы данных «Борей» можно выполнить в поле «Поиск» либо в области слева (в разделе Категории шаблонов выберите пункт Локальные шаблоны).
3. В разделе Локальные шаблоны выберите шаблон Борей 2007 и нажмите кнопку Создать.
4. Следуйте инструкциям на странице Борей (на вкладке объектов Заставка), чтобы открыть базу данных, а затем закройте окно входа.

Использование мастера для сравнения двух таблиц

1. На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.

В диалоговом окне Новый запрос дважды щелкните пункт Поиск записей, не имеющих подчиненных.

На первой странице мастера выберите таблицу, которая содержит записи, не имеющие подчиненных, а затем нажмите кнопку Далее. Например, если требуется просмотреть список товаров компании «Борей», которые никогда не продавались, выберите таблицу «Товары».

На второй странице выберите связанную таблицу и нажмите кнопку Далее. В нашем примере нужно выбрать таблицу «Сведения о заказе».

На третьей странице выберите поля, связывающие таблицу, нажмите , а затем нажмите кнопку Далее. Можно выбрать только одно поле из каждой таблицы. В нашем примере нужно выбрать поле «ИД» из таблицы «Товары» и поле «ИД товара» из таблицы «Сведения о заказе». Убедитесь в том, что сопоставлены нужные поля, просмотрев поле Соответствующие поля.

Обратите внимание на то, что поля «ИД» и «ИД товара» могут быть уже выбраны из-за существующих отношений, встроенных в шаблон.
На четвертой странице дважды щелкните нужные поля из первой таблицы, а затем нажмите кнопку Далее. Например, выберите поля «ИД» и «Наименование».

На пятой странице можно просмотреть результаты или изменить структуру запроса. Нажмите кнопку Просмотреть результаты запроса. Примите предложенное имя запроса и нажмите кнопку Готово.

Создание и изменение запроса для сравнения по нескольким полям

1. На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
2. В диалоговом окне Новый запрос дважды щелкните пункт Поиск записей, не имеющих подчиненных.
3. На первой странице мастера выберите таблицу, которая содержит записи, не имеющие подчиненных, а затем нажмите кнопку Далее. Например, если требуется просмотреть список товаров компании «Борей», которые никогда не продавались, выберите таблицу «Товары».
4. На второй странице выберите связанную таблицу и нажмите кнопку Далее. В нашем примере нужно выбрать таблицу «Сведения о заказе».
5. На третьей странице выберите поля, связывающие таблицы, щелкните значок , а затем нажмите кнопку Далее. Можно выбрать только одно поле из каждой таблицы. В нашем примере нужно выбрать поле «ИД» из таблицы «Товары» и поле «ИД товара» из таблицы «Сведения о заказе». Убедитесь, что сопоставляются нужные поля, просмотрев текст в поле Соответствующие поля. Остальные поля можно объединить после завершения работы мастера. Обратите внимание на то, что поля «ИД» и «ИД товара» могут быть уже выбраны из-за существующих отношений, встроенных в шаблон.
6. На четвертой странице дважды щелкните нужные поля из первой таблицы, а затем нажмите кнопку Далее. Например, выберите поля «ИД» и «Наименование».
7. На пятой странице выберите параметр Изменить структуру запроса и нажмите кнопку Готово. Запрос откроется в режиме конструктора.
8. Обратите внимание, что в бланке запроса две таблицы объединены по полям, указанным на третьей странице мастера (в нашем примере это поля «ИД» и «ИД товара»). Создайте объединение для каждой оставшейся пары связанных полей, перетащив их из первой таблицы (то есть таблицы, которая содержит записи, не имеющие подчиненных) во вторую таблицу. В нашем примере необходимо перетащить поле «Цена по прейскуранту» из таблицы «Товары» на поле «Цена за единицу» таблицы «Сведения о заказе».
9. Дважды щелкните соединение (строку, соединяющую поля), чтобы открыть диалоговое окно Свойства соединения . Для каждого соединения выберите параметр, включающий все записи из таблицы Products, и нажмите кнопку ОК. В бланке запроса на конце каждой линии объединения появится стрелка.

1. При создании объединения между полями «Цена по прейскуранту» и «Цена за единицу» ограничивается вывод данных из обоих таблиц. В результаты запроса включаются только записи с совпадающими данными в полях обеих таблиц. 2. После изменения свойств объединения будет ограничена только таблица, на которую указывает стрелка. Все записи в другой таблице включаются в результаты поиска.

Примечание: Убедитесь, что все стрелки объединений имеют одинаковое направление.

Создание собственного запроса для поиска записей, не имеющих подчиненных

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу с несоответствующими записями, а затем дважды щелкните таблицу с связанными записями.
3. В бланке запроса между связанными полями должны быть линии объединения. Если они отсутствуют, создайте их, перетащив каждое связанное поле из первой таблицы (таблицы с записями, не имеющими подчиненных) во вторую (таблицу со связанными записями).
4. Дважды щелкните соединение, чтобы открыть диалоговое окно Свойства соединения . Для каждого соединения выберите вариант 2 и нажмите кнопку ОК. В бланке запроса на конце каждой линии объединения появится стрелка.

Примечание: Убедитесь, что все соединения указывают в одном направлении. Запрос не будет выполняться, если соединение указывает в разных направлениях, и может не выполняться, если любое соединение не является стрелкой. Соединения должны указывать в сторону от таблицы с несовпадными записями.

Похожие публикации:

1. Xray engine ошибка в сталкере как исправить
2. Как перейти из ветки master в ветку dev
3. Как сделать белый цвет прозрачным в фотошопе
4. Какие беспроводные наушники лучше купить форум