Иногда возникает необходимость сравнить два файла MS Excel. Это может быть нахождение расхождения цен на определенные позиции или изменение каких-либо показаний, не суть важно, главное, что необходимо найти определенные расхождения.
Не лишним будет упомянуть, что, если в файле MS Excel находится пара-тройка записей, тогда прибегать к автоматизации нет смысла. Если же файл насчитывает несколько сотен, а то и тысяч записей, то без помощи вычислительной мощности компьютера не обойтись.
Смоделируем ситуацию, когда два файла имеют одинаковое количество строк, а расхождение необходимо искать в определенном столбце либо в нескольких столбцах. Такая ситуация возможна, например, если необходимо сравнить цену на товары по двум прейскурантам, либо сравнить замеры спортсменов до и после тренировочного сезона, правда для такой автоматизации их должно быть очень много.
В качестве рабочего примера возьмем файл с показателями вымышленных участников: бег на 100 метров, бег на 3000 метров и подтягивания. Первый файл – это замер в начале сезона, а второй – конец сезона.
Первый способ решения поставленной задачи. Решение только силами формул MS Excel.
Поскольку записи выстроены вертикально (наиболее логичное построение) то необходимо воспользоваться функцией . В случае использования горизонтального размещения записей придется воспользоваться функцией .
Для сравнения показателей бега на 100 метров формула выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА);"Разницы нет")
В случае, если разницы нет, выводится сообщение, что разницы нет, если она присутствует, тогда от значения в конце сезона отнимается показатель начала сезона.
Формула для бега на 3000 метров выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;4;ИСТИНА)<>E2;"Разница есть";"Разницы нет")
Если конечное и начальное значения не равны выводится соответствующее сообщение. Формула для подтягиваний может быть аналогична любой из предыдущих, дополнительно приводить ее смысла нет. Конечный файл с найденными расхождениями приведен ниже.
Небольшое пояснение. Для удобства читаемости формул данные из двух файлов были перемещены в один (на разные листы), но этого можно было не делать.
Видео сравнения двух файлов MS Excel с помощью функций и .
Второй способ решения задачи. Решение с помощью MS Access.
Поставленную задачу можно решить, если предварительно импортировать файлы MS Excel в Access. Что касается самого способа импорта внешних данных, то для нахождения различающихся полей разницы нет (любой из представленных вариантов подойдет).
Последний представляет собой связь файлов Excel и Access, поэтому при изменении данных в файлах Excel, расхождения будут находится автоматически при выполнении запроса в MS Access.
Следующим шагом после произведения импорта будет создание связей между таблицами. В качестве связующего поля выбираем уникальное поле «№ п/п».
Третьим шагом будет создание простого запроса на выборку с помощью конструктора запросов.
В первом столбце указываем, какие записи необходимо вывести, а во втором – при каких условиях записи будут выведены. Естественно, для второго и третьего поля действия будут аналогичными.
Видео сравнения файлов MS в Excel, с помощью MS Access.
В результате проделанных манипуляций выведены все записи, с разными данными в поле: «Бег на 100 метров». Файл MS Access представлен ниже (к сожалению, внедрить, как файл Excel, SkyDrive не позволяет)
Вот такие два способа существуют для нахождение расхождений в таблицах MS Excel. У каждого есть как преимущества, так и недостатки. Понятно, что это не исчерпывающий список сравнения двух файлов Excel. Ваши предложения ждем в комментариях.
Добрый день!
Эта статья посвящена решению такого вопроса, как сравнить две таблицы в Excel, ну или, по крайней мере, два столбца. Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно. Быть может таблицу до десятка или двух, вы и сможете визуально отсортировать, но вот когда они будут превышать тысячи, тут уже вам будет необходимо дополнительные инструменты анализа.
Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы.
Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:
Простой способ,
Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2 , как результат при равенстве ячеек мы получим ответ «ИСТИНА », а если совпадений нет, будет «ЛОЖЬ» . Теперь простым авто копированием копируем на нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.
Быстрое выделение значений, которые отличаются
Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…»
и в появившемся диалоговом окне выберите «отличия по строкам»
.
Сравнить две таблицы в Excel
с помощью условного форматирования
Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить вы можете на вкладке «Главная»
, нажав кнопку «Условное форматирование»
и в предоставленном списке выбираем «Управление правилами»
.
В диалоговом окне «Диспетчер правил условного форматирования»
, жмем кнопочку «Создать правило»
и в новом диалоговом окне «Создание правила форматирования»
, выбираем правило . В поле «Изменить описание правила»
вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат»
.
Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. 
Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок»
.
И все правило применилось к нашему диапазону, где мы пытаемся проверить на похожесть две таблицы, и стало видны отличия, к которым применилось условное форматирование.
Как сравнить две таблицы в Excel
с помощью функции СЧЁТЕСЛИ и правил
Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная»
, пункт меню «Условное форматирование»
и в списке жмем пункт «Создать правило…»
, выбираем правило «Использовать формулу для определения форматируемых ячеек»
, вписываем формулу = ($C$1:$C$7;C1)=0
и выбираем формат условного форматирования.
Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.
Как сравнить две таблицы в Excel
с помощью функции ВПР
В этом варианте мы будем использовать , которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем .
Как сравнить две таблицы в Excel
функции ЕСЛИ
Этот вариант предусматривает использования логической и отличие этого способа в том что для сравнения двух столбцов будет использован не весь целиком, а только та ее часть, которая нужна для сравнения.
Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ( (ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь . Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.
Сравнить две таблицы
с помощью макроса VBA
Есть много способов проверить две таблицы на схожесть, но некоторые варианты возможно только с помощью макросов VBA. Макросы для того что бы сравнить две таблицы, унифицирует этот процесс и существенно сокращает затраченное время на подготовку данных. Исходя из решаемой вами задачи и знаний макросов VBA, вы можете создавать любые варианты макросов. Ниже я привел методику, указанную на официальной страничке Microsoft. Вам нужно создать модуль для кода VBA и ввести код:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Если сравниваемый диапазон находится на другом листе или книге, " используйте следующий синтаксис " Set CompareRange = Workbooks("Книга2"). _ " Worksheets("Лист2").Range("B1:B11") " " Сравнение каждого элемента в выделенном диапазоне с каждым элементом " переменной CompareRange For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 2) = x Next y Next x End Sub
Sub Find_Matches () Dim CompareRange As Variant , x As Variant , y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Еслисравниваемыйдиапазоннаходитсянадругомлистеиликниге, " используйте следующий синтаксис " Set CompareRange = Workbooks ("Книга2" ) . _ Горячее сочетание клавиш Alt+F8 . В новом диалоговом окне выбираете ваш макрос Find_similar и выполняете его. Сравнение
с помощью надстройки Inquire
Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire , которая позволит проанализировать и сравнить два файла Excel. Этот способ хорош, когда у вас есть необходимость сравнить два файла, в случае, когда ваш коллега работал над книгой и ввел некоторые изменения. Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire. Ну вот мы и рассмотрели 8 способов как сравнить две таблицы в Excel, эти варианты помогут вам решить свои аналитические задачи и упростят вашу работу. Был рад вам помочь!
|
Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.
You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.
If you don"t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in .
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.
Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There"s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:
The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013 .
To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook .
Analyze a workbook
The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.
Show workbook links
Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.
This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.
This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.
Show cell relationships
To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell Relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.
This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.
To learn more about viewing cell relationships, read See links between cells .
Clean excess cell formatting
Ever open a workbook and find it loads slowly, or has become huge? It might have formatting applied to rows or columns you aren"t aware of. Use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce file size. This helps you avoid "spreadsheet bloat," which improves Excel"s speed.
Manage passwords
If you"re using the Inquire features to analyze or compare workbooks that are password protected, you"ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.
Нередко при работе приходится сравнивать между собой различные модификации документов, например, исходную и измененную редакции материалов, подготовленных в Word либо в виде PDF-документов или презентаций, рабочую и обновленные версии прайс-листов с изменившимися ценами в Excel, разные версии текстовых документов и т.п. При этом вопрос не в том, какая из версий файлов является более свежей (это и так понятно из свойств файлов), а важно, что именно изменилось в документах с точки зрения содержимого. Сравнивать документы вручную - занятие неблагодарное из-за слишком больших затрат времени и возможности ошибок, ведь не заметить какую-то важную деталь при просмотре проще простого. Гораздо разумнее задачу сравнения файлов перепоручить компьютеру. В целом, в плане сравнения Word-документов все обстоит достаточно благополучно и без использования вспомогательных инструментов, хотя в версиях Word 2002 и Word 2003 данная возможность надежно скрыта от чужих глаз, и, вероятно, не так много пользователей о ее существовании вообще догадываются. Дело в том, что для сравнения документов здесь нужно вначале загрузить исходный файл. Затем из меню "Сервис" открыть команду "Сравнить и объединить исправления", указать файл, сравниваемый с исходным, и включить флажок "Черные строки". Только после этих манипуляций кнопка "Объединить" превратится в кнопку "Сравнить", и при щелчке по данной кнопке программа и проведет сравнение файлов.
Результаты сравнения будут показаны во вновь созданном документе в традиционном режиме рецензирования. С появлением Word 2007 все стало гораздо проще, поскольку теперь достаточно переключиться на вкладку "Рецензирование", щелкнуть по кнопке "Сравнить" и указать сравниваемые версии документа. Результат сравнения окажется представленным в новом документе, где слева будет отображен отрецензированный документ с учетом изменений, а справа (друг над другом) - исходный и измененный документы. Теоретически, в Excel тоже возможно сравнение документов встроенными средствами, правда, только при работе в режиме фиксирования изменений. Однако это неудобно, поскольку каждую из измененных ячеек придется просматривать, наводя на нее мышь, так как изменения, внесенные в документ, отображаются во всплывающих окошках (примерно таких, как обычные примечания).
Во-вторых, если названный режим не будет предварительно включен (команда "Сервис" > "Исправления" > "Выделить исправления", флажок "Отслеживать исправления"), то произвести сравнение XLS-файлов потом окажется невозможно.
Что касается быстрого сравнения PDF-документов, то такая возможность, конечно, имеется в Acrobat 9 Pro и Acrobat 9 Pro Extended, но эти решения установлены далеко не на каждом компьютере. Поэтому при необходимости быстрого сравнения Excel-таблиц, PDF-документов, презентаций, а также документов в других форматах, в частности, текстовых файлов и программных кодов, приходится прибегать к использованию дополнительного инструментария. Вариантов тут множество, и это могут быть как комплексные решения, позволяющие работать с несколькими файловыми форматами, так и узкоспециализированные утилиты. Немалая часть подобных решений предлагается за приличные деньги - скажем, цена одного из самых известных в этой сфере комплексных решений Diff Doc составляет $99,95, а весьма популярная среди программистов утилита Araxis Merge оценивается в €119. Вместе с тем, на рынке имеются и вполне доступные по цене либо вообще бесплатные программы подобного плана, именно такие решения мы и оценим в данной статье. При этом основное внимание уделим комплексным решениям, а из узкоспециализированных средств отметим лишь утилиты для быстрого сравнения таблиц Excel, поскольку это одна из наиболее актуальных задач, а проведение сравнения Excel-документов комплексными решениями хоть и возможно, но менее эффективно, чем с помощью узкоспециализированных утилит.
⇡ Комплексные решения для быстрого сравнения документов
Compare Suite 7.0
Разработчик: AKS-LabsРазмер дистрибутива: 3,79 Мб
Распространение: условно бесплатная Compare Suite - удобный инструмент для быстрого сравнения текстовых файлов, документов MS Office, RTF-документов, файлов PDF, web-страниц (HTM), презентаций PowerPoint, бинарных и некоторых других типов файлов, а также файлов в ZIP- и RAR-архивах и на FTP-серверах. В случае сравнения листингов программ на ряде языков программирования (Object Pascal, HTML, C/C++, JavaScript, PHP и др.) предусмотрена подсветка синтаксиса. Имеется функционал для синхронизации текстовых файлов и сравнения папок вместе с подпапками. Демо-версия программы (имеется русскоязычная локализация) работоспособна в течение 30 дней и полностью функциональна. Стоимость коммерческой версии составляет 60 долл. Технология сравнения файлов в Compare Suite проста. Вначале выбирается метод сравнения файлов, для чего в меню "Сравнить" нужно выбрать один из вариантов: "посимвольно", "пословно", "по ключевым словам". Классическое посимвольное сравнение полезно в ситуациях, когда нужно зафиксировать малейшие отличия в написании слов - скажем, при сравнении листингов программ. Пословное сравнение документов, как правило, используется для сравнения разных версий одного и того же файла. С помощью метода сравнения по ключевым словам можно сравнить непохожие документы даже тогда, когда пословное сравнение невозможно.
После выбора метода сравнения, в меню "Файл" надо выбрать команду "Новое сравнение файлов" и указать на левой и правой панелях файлы для сравнения. Программа проанализирует файлы и выделит все имеющиеся отличия (добавленные, измененные и удаленные фрагменты) различными цветами. Дополнительно можно создать сравнительный отчет с детальной информацией о сравниваемых файлах (команда "Файл" > "Отчет"). При сравнении файлов, отличных от текстовых, следует иметь в виду, что их сравнение во всех комплексных решениях (как в Compare Suite, так и в других утилитах) производится после преобразования в текстовый формат. На практике это означает, например, что если в сравниваемых версиях Word-документа одно и то же слово в конкретном предложении оказалось на разных строках, то оно будет причислено к списку изменений. При сравнении XLS-документов данные из таблиц перед проведением анализа извлекаются, а найденные отличия отображаются построчно в текстовом формате с указанием имени листа и названий столбцов, что позволяет ориентироваться в данных. По такой же схеме осуществляется сравнение PDF-документов и презентаций PowerPoint.
Программа Compare Spreadsheets for Excel – это мощный и удобный инструмент для сравнения файлов Microsoft Excel.
Для чего это нужно и как это работает:
Например, вы ежемесячно получаете от своих партнеров прайс-листы такого вида:
Сентябрь
Октябрь
При их беглом просмотре кажется, что прайс-листы не изменились. Конечно вы можете потратить более длительное время на детальное сравнение всех товарных позиций и цен, что бы понять есть какие-то изменения или нет. Однако давайте попробуем поручить эту задачу Compare Spreadsheets for Excel: :
- Запустите программу (при этом не обязательно открывать сравниваемые таблицы в Microsoft Excel).
- Укажите таблицы или диапазоны ячеек для сравнения;
- Выберете параметры выравнивания при сравнении (по строкам, столбцам или без выравнивания);
- Укажите, что вы хотите сравнивать: значения ячеек или формулы;
- Задайте варианты выделения различающихся ячеек (цвет фона и/или цвет и стиль границы ячейки).
Пять несложных действий, несколько секунд времени и перед вами следующий отчет:
Программа нашла все изменения в последнем прайс листе: новый продукт (выделено салатовым) и изменившиеся цены (ячейки с красной границей).
А теперь представтье себе, что в ваших файлах не 20 строк и не 8 столбцов. Корректно сравнить два больших документа вручную очень сложная задача. Но все сложности исчезают если вы сравниваете таблицы Microsoft Excel с помощью Compare Spreadsheets for Excel!
Возможности программы
- Работа с файлами, таблицами или выбранным диапазоном ячеек.
- Работа с файлами без их открытия в Microsoft Excel.
- Простой и удобный интерфейс программы в виде мастера.
- Вывод результатов сравнения в виде настраиваемого, удобного и наглядного отчета.
- Возможность сравнения произвольных ячеек в полученном отчете.
- Сравнение по значениям ячеек или по формулам.
Загрузка демонстрационной версии
Вы можете загрузить демоверсию программы Compare Spreadsheets for Excel (17906 KB) протестировать её перед покупкой:
Регистрация программы
Демонстрационная версия Compare Spreadsheets for Excel не имеет ограничений. Если Вы хотите после окончания 20-дневного демонстрационного периода, то вам необходимо зарегистрировать программу. Вы можете зарегистрировать Compare Spreadsheets for Excel через Интернет, выбрав удобный для вас способ оплаты.
