Как сравнить два файла в excel на различия
8 способов как сравнить две таблицы в 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 и ввести код:
Источник: topexcel.ru
Сравнение двух версий книги с помощью средства сравнения электронных таблиц
Если другие пользователи имеют право на редактирование вашей книги, то после ее открытия у вас могут возникнуть вопросы “Кто ее изменил? И что именно изменилось?” Средство сравнения электронных таблиц от Майкрософт поможет вам ответить на эти вопросы — найдет изменения и выделит их.
Важно: Сравнение электронных таблиц поддерживается только в Office профессиональный плюс 2013 или Office 365 профессиональный плюс.
Откройте средство сравнения электронных таблиц.
В левой нижней области выберите элементы, которые хотите включить в сравнение книг, например формулы, форматирование ячеек или макросы. Или просто выберите вариант Select All (Выделить все).
На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).
В диалоговом окне Compare Files (Сравнение файлов) в строке Compare (Сравнить) с помощью кнопки обзора 
выберите более раннюю версию книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.
В диалоговом окне Compare Files (Сравнение файлов) в строке To (С чем) с помощью кнопки обзора 
выберите версию книги, которую хотите сравнить с более ранней.
Примечание: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.
Нажмите кнопку ОК, чтобы выполнить сравнение.
Примечание: Появление сообщения “Не удается открыть книгу” может означать, что книга защищена паролем. Нажмите кнопку ОК и введите пароль. Узнайте подробнее о том, как действуют пароли при использовании средства сравнения электронных таблиц.
Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле “Compare” (Сравнить), а книга в правой части — файлу, указанному в поле “To” (С чем). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.
Интерпретация результатов
В двух расположенных рядом частях таблицы сравнивается каждый лист из обоих файлов, начиная с самого крайнего слева. Если лист в книге скрыт, он все равно отображается и сравнивается в средстве сравнения электронных таблиц.
Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).
Различия разного типа выделяются с помощью цвета заливки ячейки или цвета шрифта текста. Например, ячейки с введенными значениями (не с формулами) выделяются заливкой зеленого цвета в расположенных рядом частях таблицы и шрифтом зеленого цвета в области результатов. В левой нижней части указаны условные обозначения, поясняющие значения цветов.
Другие способы работы с результатами сравнения
Если вы хотите сохранить результаты или проанализировать их в другом приложении, экспортируйте их в файл Excel или скопируйте и вставьте в другую программу, например Microsoft Word. Можно также получить более точное представление каждого листа с отображением форматирования ячеек, близкое к тому, что вы видите в Excel.
Вы можете экспортировать результаты в файл Excel, более удобный для чтения. Выберите Home > Export Results (Главная > Экспорт результатов).
Чтобы скопировать результаты и вставить их в другую программу, выберите Home > Copy Results to Clipboard (Главная > Копировать результаты в буфер обмена).
Чтобы отобразить форматирование ячеек из книги, выберите Home > Show Workbook Colors (Главная > Показать цвета книги).
Другие причины для сравнения книг
Допустим, в вашей организации ожидается аудиторская проверка. Вам нужно проследить данные в важных книгах, в которых показаны изменения по месяцам и по годам. Это поможет вам найти и исправить ошибки раньше, чем до них доберутся проверяющие.
Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.
Источник: support.office.com
Поиск отличий в двух списках
Типовая задача, возникающая периодически перед каждым пользователем Excel – сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант – используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :
Число несовпадений можно посчитать формулой:
или в английском варианте =SUMPRODUCT(–(A2:A20<>B2:B20))
Если в результате получаем ноль – списки идентичны. В противном случае – в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) – Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) – Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная – Удалить – Удалить строки с листа (Home – Delete – Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения (Home – Conditional formatting – Highlight cell rules – Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные – различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, “высший пилотаж” – можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Выглядит страшновато, но свою работу выполняет отлично 😉
Источник: www.planetaexcel.ru
Сравнение двух файлов Excel
Мне нужно сравнить две электронные таблицы Excel, чтобы узнать, изменились ли значения каждого пользователя в определенном столбце. Новая таблица может иметь дополнительных пользователей, которые были добавлены в результате обновления старых пользователей строки.
1 ответов
важно это руководство применимо для Microsoft Office профессиональный плюс 2013 или Office 365 профессиональный плюс. Функция сравнения электронных таблиц не включена ни в один другой дистрибутив Microsoft Office.
Таблица Сравнения
на Windows 7 в меню Пуск Windows В разделе Средства Office 2013 выберите сравнение электронных таблиц.
на Windows 8 на начальном экране нажмите сравнение электронных таблиц. Если вы не видите Плитка сравнение электронных таблиц, начните вводить слова сравнение электронных таблиц, а затем выберите ее плитку.
в дополнение к электронной таблице сравнить, вы также найдете сопутствующую программу для Access – Microsoft Database Compare. Он также требует профессионала офиса плюс 2013. (В Windows 8 введите Database Compare, чтобы найти его.)
сравнить две книги Excel
Нажмите Главная > Сравнить Файлы.
Откроется диалоговое окно сравнение файлов.
щелкните синий значок папки рядом с полем сравнить, чтобы перейти к более ранней версии книги. Помимо файлов, сохраненных на компьютере или в сети, можно ввести веб-адрес сайта, на котором хранятся книги.
щелкните значок зеленой папки рядом с полем кому, чтобы перейти к папке книги, которую требуется сравнить с более ранней версией, а затем нажмите кнопку ОК.
совет можно сравнить два файла с одинаковым именем, если они сохранены в разных папках.
на левой панели выберите параметры, которые должны отображаться в результатах сравнения книг, установив или сняв флажки для таких параметров, как Формулы, макросы или Формат ячеек. Или просто выберите Все.
клик OK для выполнения сравнения.
Если появляется сообщение “не удается открыть книгу”, это может означать, что одна из книг защищена паролем. Нажмите кнопку ОК, а затем введите пароль книги. Узнайте больше о том, как пароли и таблицы сравнить работать вместе.
результаты сравнения отображаются в панели сетки. Книга слева соответствует выбранному файлу сравнения (обычно более старому), а книга справа соответствует файлу” кому ” (обычно более новому). Подробные сведения отображаются на панели под двумя сетками. Изменения подсвечиваются цветом в зависимости от вида изменений.
понимание результатов
в параллельной сетке лист для каждого файла сравнивается с листом в другом файле. Если листов несколько, они доступны, нажав кнопки вперед и назад на горизонтальной прокрутке бар.
Примечание даже если лист скрыт, он все равно сравнивают и показывают результаты.
различия выделяются цветом заливки ячейки или цветом шрифта текста в зависимости от типа различия. Например, ячейки с “введенными значениями”(ячейки без формул) форматируются зеленым цветом заливки в параллельной сетке и зеленым шрифтом в списке результатов панели. Нижняя левая панель-это легенда, которая показывает, какие цвета означать.
в примере, показанном здесь, результаты для Q4 в более ранней версии не были окончательными. Последняя версия книга содержит окончательные цифры в столбце E для В4.
в результатах сравнения ячейки E2: E5 в обеих версиях имеют зеленую заливку, что означает изменение введенного значения. Поскольку эти значения изменились, вычисленные результаты в столбце YTD также изменились-ячейки F2: F4 и E6: F6 имеют a сине-зеленая заливка, означающая изменение расчетного значения.
расчетный результат в ячейке F5 также изменился, но более важной причиной является то, что в более ранней версии его формула была неправильной (она суммировала только B5:D5, опуская значение для Q4). При обновлении книги Формула в F5 была исправлена так, что теперь =SUM(B5:E5).
Если ячейки слишком узкие для отображения содержимого ячейки, щелкните изменить размер ячеек до Пригонка.
Источник: kompsekret.ru
Как сравнить два файла в excel на различия
Здравствуйте, уважаемые гуру!
Есть такая интересная задача – сравнить два файла Excel.
Нужно, чтобы макрос спрашивал с какой строки сравнивать, так как до шапки может идти ненужный текст. И несовпадающие (соответствующие. ) ячейки закрасить в какой-нить цвет!
Всем буду очень признателен за ответ!
——— примечание модератора – вдруг кому пригодится ————–
| Надстройка LOOKUP предназначена для сравнения и подстановки значений в таблицах Excel. |
Если вам надо сравнить 2 таблицы (по одному столбцу, или по нескольким),
и для совпадающих строк скопировать значения выбранных столбцов из одной таблицы в другую,
надстройка «Lookup» поможет сделать это нажатием одной кнопки.
В настройках программы можно задать:
- где искать сравниваемые файлы (использовать уже открытый файл, загружать файл по заданному пути, или же выводить диалоговое окно выбора файла)
- с каких листов брать данные (варианты: активный лист, лист с заданным номером или названием)
- какие столбцы сравнивать (можно задать несколько столбцов)
- значения каких столбцов надо копировать в найденные строки (также можно указать несколько столбцов)
Скачать надстройку для сравнения таблиц Excel и копирования данных из одинаковых строк
Вложения
 |
сми.rar (7.4 Кб, 299 просмотров) |
| 04.03.2010, 15:52 | #2 |
 |
сми.zip (22.8 Кб, 262 просмотров) |
