Сравнение двух файлов в Excel на наличие различий

Каждый пользователь может столкнуться с ситуацией, когда ему необходимо сравнить две таблицы. Ну и в крайнем случае, всем приходится сравнивать две колонки. Да, конечно, работать с файлами Excel очень удобно и комфортно. Извините, это не сравнение. Конечно, визуальная сортировка небольшой таблицы возможна, но когда количество ячеек исчисляется тысячами, приходится использовать дополнительные аналитические инструменты.

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

Таких действий много. Давайте посмотрим на некоторые из них.

Какова цель сравнения файлов Excel?

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

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

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

Все способы сравнить 2 таблицы в Excel

Хоть тема и сложная, но простая. Да, не удивляйтесь. Он сложен, потому что состоит из множества частей. Но сами эти части легко понять и выполнить. Давайте посмотрим, как можно сравнить две таблицы Excel непосредственно на практике.

Формула равенства и тест на ложное истинность

Начнем, конечно, с самого простого метода. Такой способ сравнения документов возможен, причем в достаточно широком диапазоне. Сравнивать можно не только текстовые значения, но и числовые. И давайте возьмем небольшой пример. Допустим, у нас есть два диапазона с ячейками числового формата. Для этого просто напишите формулу равенства =C2=E2. Если окажется, что они равны, в ячейке будет записано «ИСТИНА». Если они различаются, то ЛОЖЬ. После этого вам нужно перенести эту формулу на весь диапазон с помощью маркера автозаполнения.

Теперь разница видна невооруженным глазом.

Выделение различных ценностей

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

В появившемся меню нажмите на пункт «Выбрать группу ячеек…». Далее откроется окно, в котором нам нужно выбрать в качестве критерия различия по линиям.

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

Условное форматирование — очень удобный и, что немаловажно, функциональный метод, позволяющий выбрать цвет, который будет выделять другое или одинаковое значение. Вы можете найти эту опцию на вкладке «Главная». Там вы сможете найти кнопку с соответствующим названием и в появившемся списке выбрать «Управление правилами». Появится менеджер правил, в котором нам нужно выбрать меню «Создать правило».

Далее из списка критериев нам нужно выбрать тот, где написано, что нам нужно использовать формулу для определения ячеек, которые будут отформатированы особым образом. В описании правила необходимо указать формулу. В нашем случае это =$C2<>$E2, после чего подтверждаем свои действия нажатием кнопки «Форматировать». После этого задаем внешний вид ячейки и смотрим, нравится ли он нам через специальное мини-окно с образцом. 

Если все устраивает, нажмите кнопку «ОК» и подтвердите действия.

В Диспетчере правил условного форматирования пользователь может найти все правила форматирования, действующие в данном документе. 

Функция СЧЕТЕСЛИ + правила сравнения таблиц

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

Давайте представим, что у нас есть два диапазона с немного разной информацией. Перед нами стоит задача сравнить их и понять, какое значение отличается. Сначала вам нужно выбрать его в первом диапазоне и перейти на вкладку «Главная». Там находим ранее знакомый пункт «Условное форматирование». Мы создаем правило и устанавливаем правило для использования формулы. 

В этом примере формула такая, как показано на этом снимке экрана.

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

Функция ВПР для сравнения двух таблиц

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

Эта функция перебирает каждое значение и проверяет, есть ли дубликаты из первого столбца во второй. Ну и после выполнения всех операций это значение записывается в ячейку. Если его нет, то мы получаем ошибку #N/A, которой вполне достаточно, чтобы автоматически понять, какое значение не будет совпадать.

Функция IF

Логическая функция ЕСЛИ - это еще один хороший способ сравнить два диапазона. Основная особенность этого метода в том, что вы можете использовать только часть сравниваемого массива, а не всю таблицу. Это экономит ресурсы как компьютера, так и пользователя.

Давайте возьмем небольшой пример. У нас есть два столбца — A и B. Нам нужно сравнить некоторую информацию в них друг с другом. Для этого нам нужно подготовить еще один служебный столбец C, в котором записана следующая формула.

Использование формулы, в которой используются функции IF, IFERROR и БОЛЬШЕ ОТКРЫТОГО можно перебрать все нужные элементы столбца А, а затем и в столбце Б. Если он был найден в столбцах Б и А, то он возвращается в соответствующую ячейку.

Макрос VBA

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

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

Как сравнить файлы в Excel

Если пользователь поставил перед собой задачу (ну или ему поставили) сравнить два файла, то это можно сделать сразу двумя способами. Первый — использование специализированной функции. Для реализации этого метода следуйте инструкции:

1. Откройте файлы, которые хотите сравнить.
2. Откройте вкладку «Вид» – «Окно» – «Просмотр рядом».

После этого два файла будут открыты в одном документе Excel.

То же самое можно сделать и обычными средствами Windows. Сначала вам нужно открыть два файла в разных окнах. После этого возьмите одно окно и перетащите его в самую левую часть экрана. После этого откройте второе окно и перетащите его в самую правую сторону. После этого два окна окажутся рядом. 

Условное форматирование для сравнения двух файлов Excel

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

Для начала нам необходимо перенести сравниваемые листы в один документ. 

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

Далее вам необходимо выделить все нужные ячейки, чтобы отобразить все различия. Самый простой способ сделать это — щелкнуть верхнюю левую ячейку, а затем нажать комбинацию клавиш Ctrl + Shift + End.

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

Внимание: адреса ячеек необходимо указывать те, которые находятся на другом листе. Это можно сделать через меню ввода формулы.

Сравнение данных в Excel на разных листах

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

Предположим, нам нужно сравнить зарплаты. В этом случае в качестве данных можно использовать таблицы с разных листов. Мы будем использовать условное форматирование, чтобы выделить различия. Все просто.

Благодаря условному форматированию вы можете эффективно сравнивать данные, даже если имена сотрудников расположены в другом порядке.

Как сравнить 2 листа в таблице Excel

Сравнение информации, расположенной на двух листах, осуществляется с помощью функции БОЛЬШЕ ОТКРЫТОГО. В качестве первого его параметра стоит пара значений, которые нужно искать на листе, отвечающем за следующий месяц. Проще говоря, март. Мы можем обозначить просматриваемый диапазон как совокупность ячеек, входящих в именованные диапазоны, объединенных попарно.

Таким образом, вы можете сравнивать строки по двум критериям — фамилии и зарплате. Ну или любой другой, определяемый пользователем. Для всех совпадений, которые удалось найти, в ячейку, в которую введена формула, записывается число. Для Excel это значение всегда будет истинным. Поэтому, чтобы форматирование было применено к тем ячейкам, которые были разными, нужно заменить это значение на ЛЕЖА, используя функцию =НЕ().

Инструмент сравнения электронных таблиц

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

Важно отметить, что этот инструмент доступен только тем пользователям, которые приобрели офисные пакеты Professional Plus.

Открыть его можно прямо из вкладки «Главная», выбрав пункт «Сравнить файлы».

После этого появится диалоговое окно, в котором вам нужно выбрать вторую версию книги. Вы также можете ввести интернет-адрес, по которому находится эта книга.

После того, как мы выбрали две версии документа, нам необходимо подтвердить свои действия клавишей ОК.

В некоторых случаях может возникнуть ошибка. Если он появляется, это может указывать на то, что файл защищен паролем. После того, как вы нажмете ОК, вам будет предложено ввести его. 

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

Как интерпретировать результаты сравнения

Все очень просто: разные виды различий обозначаются разными цветами. Форматирование может распространяться как на заливку ячейки, так и на сам текст. Итак, если в ячейку были введены данные, то заливка имеет зеленый цвет. Если что-то становится непонятно, то в самом сервисе есть символы, которые показывают, какой тип изменения каким цветом выделен.