Как сравнить и найти разницу между двумя таблицами Excel
Содержание
При наличии двух версий таблицы пользователям нередко приходится сравнивать их при работе в программе. При этом у них возникает закономерный вопрос — как их сравнить и найти различия. В Экселе есть немало способов, позволяющих довольно легко решить данную задачу. К ним относятся как простые методы, так и довольно сложные, которые применяются только в отношении таблиц с большим количеством ячеек. В данном материале рассмотрим, как сравнить две таблицы в Excel на совпадения и найти в них различия.
Визуальное сравнение
Если нужно сравнить 2 таблицы с небольшим числом ячеек — это можно сделать без использования каких-либо специальных методов или инструментов. Достаточно сопоставить 2 версии документа и визуально определить разницу значений. Проще всего сделать это в том случае, когда каждая таблица располагается в разных файлах. Вы сможете открыть их одновременно и расположить на экране компьютера, что существенно упростит задачу, связанную с поиском различий:
- Откройте обе таблицы на ПК. После этого перейдите во вкладку «Вид». Это нужно сделать в одном из открытых окон, а не в обеих одновременно.
Открываем таблицы и переходим во вкладку вид - Далее нажмите кнопку «Упорядочить все». В выпадающем окне выберите значение «Рядом» и затем нажмите .
Ставим значение рядом - В результате выполнения описанных выше действий, на экране будет отображаться два окна MS Excel с двумя таблицами соответственно. Вы сможете сравнить их визуально и найти различия, если они имеются.
Просмотр двух таблиц
Очевидно, что такое сравнение данных в Экселе нельзя назвать самым удобным и быстрым. Однако если таблицы содержат небольшое число ячеек, поиск различий не займет много времени. Для других случаев вы можете использовать один из способов, описанных ниже. Чтобы вернутся к обычному отображению окон, достаточно нажать на значок вверху справа или растянуть область программы, зажав границу мышью.
Применение формул для поиска различий
Известно, что программа MS Excel предназначена для обработки разных типов данных. Наиболее распространенными являются текст и числа, однако есть и другие, редкие категории. К таковым можно отнести логический тип данных, который также называется булевыми значениями. Этот тип данных принимает только 2 возможных значения — ложь и истина. Логический тип считается примитивным, но при этом он является основой современных информационных технологий. Неудивительно, что он же может помочь и в решении задач, связанных с проверкой таблиц на совпадение.
Прежде чем определить порядок сравнения двух таблиц, рассмотрим, как вообще работает данный тип данных. Для примера, заполним 2 ячейки в Excel одинаковыми значениями. В данном примере это будут числа, однако можно написать и текст.
Теперь в любой свободной клетке впишем простую логическую конструкцию — операцию сравнения. Для этого через знак равенства ставим ссылки на эти ячейки. Перед конструкцией также нужно указывать символ «=», как и перед любой формулой или вычислительной конструкцией.
При оформлении формулы таким способом, программа может вернуть только значение логического типа. По сути, пользователь спрашивает Excel, является ли значения из ячеек А1 и В2 одинаковыми. Прописав формулу, нажимаем . В результате этого появится значение «ИСТИНА», а это указывает на то, что значения в этих ячейках одинаковые.
Теперь, для примера, изменим значение внутри одной из ячеек, чтобы они не были одинаковыми. При вводе аналогичной логической конструкции вернется значение ЛОЖЬ. Это указывает на то, что программа нашла отличия между числами и они не равны друг другу.
Отметим, что аналогичный способ сравнения можно выполнить в обратном порядке. Если между ссылками на ячейки поставить вместо знака равенства знак неравенства (<>), программа также будет находить различия между значениями. Однако процесс будет осуществляться в обратном порядке. Если значения в ячейках разные — будет возвращена «ИСТИНА», тогда как при выявлении дубликатов вернется «ЛОЖЬ». Такой способ оформления проверки может применяться в том случае, когда различий в таблицах больше, чем сходств.
Сравнение двух таблиц на одном листе
Принцип работы логического типа данных, описанный выше, поможет выполнить сравнение двух таблиц в MS Excel. Также как сравнивались две ячейки с числами, можно выполнить проверку соответствия множества ячеек в разных таблицах. Проще всего сделать это тогда, когда они находятся на одном листе. Такой вариант подойдет в том случае, если обе таблицы имеют небольшое число столбцов, так как в ином случае их будет неудобно размещать на одном листе и выполнять выделение.
Рассмотрим, как выполняется сравнение таблиц в Excel на совпадения подробно:
- Размещаем обе таблицы на одном листе. Еще раз подчеркиваем, что это следует делать только тогда, когда они небольшие и удобно вписываются в рабочее пространство табличного редактора MS Excel.
Размещаем таблицы на одном листе - Выбираем ячейку для записи результата. В нашем случае это клетка с адресом I В нее записываем формулу для проверки первых ячеек из обеих таблиц — А1 и Е1. После этого нажимаем .
Сравниваем первые ячейки - После ввода формулы в ячейке отобразится логическое значение — результат сравнения. Первые ячейки таблицы одинаковы, поэтому возвращается слово «ИСТИНА». Теперь необходимо применять эту формулу к остальным ячейкам. Наводим курсор на правый нижний угол ячейки с формулой и ждем, когда появится значок «+». После этого протягиваем границу вниз на то количество строк, которое есть в таблице. В результате этого формула будет автоматически применена к остальным ячейкам в первом столбце.
Применяем формулу к столбцу - Программа сравнивает 2 таблицы, каждая из которых состоит из 3 столбов. Поэтому применяем маркер автозаполнения повторно, но теперь протягиваем ячейки вправо на 2 столбца.
Применяем к остальным столбцам
В результате чего мы получим результат сравнения всех ячеек двух таблиц. Как видно, в третьем столбце присутствуют значения «ЛОЖЬ». Это указывает на то, что в них есть различия. Описанный способ сопоставления удобен тем, что при размещении таблиц на одной линии вы без проблем узнаете, где находятся несовпадающие значения. Тем не менее следует обязательно учитывать несколько важных нюансов. Все они перечислены ниже.
Если одна таблица содержит больше ячеек, чем другая, то в местах несоответствий будет стоять значение «ЛОЖЬ». Это логично, так как таблицы неодинаковые и сопоставление показывает, что их структура различается. Это будет происходить даже в том случае, если все остальные значения в обеих таблицах абсолютно одинаковые.
Также следует учитывать, что программа может реагировать на способ оформления значений, но не учитывать регистр букв в ячейках. Даже если в смысловом плане значения не отличаются, способ написания не будет автоматически определятся в виде различия. Поэтому программа, проверив найденные отличия, вернет значение «ИСТИНА».
Сравнение таблиц на разных листах
Описанный способ сравнения можно использовать тогда, когда таблицы находятся на разных листах. Общий алгоритм действий идентичный, различается только способ ввода координат ячеек. Необходимо правильно задать адреса ячеек для сравнения, иначе формула отработает некорректно и может вернуть недостоверный результат. Рассмотрим, как сравнить две таблицы в Эксель и найти между ними разницу, используя данный способ.
- В нашем примере есть 2 таблицы для сравнения, в которых есть ячейки с отличающимися значениями. Так как они находятся на разных листах, целесообразно применить формулу массива. В первую очередь выделите диапазон ячеек, который соответствует размеру таблицы. В нашем случае это 3 столбца и 22 строки.
Выделяем диапазон ячеек - Далее нужно оформить формулу, которая будет сопоставлять ячейки в таблицах на разных листах. Сначала ставим знак равенства и прописываем диапазон первой таблицы. Проще всего сделать это с помощью мышки. Зажмите и поочередно нажмите на первую и последнюю ячейку диапазона. Ссылка подставится в формулу автоматически.
Вводим ссылку на диапазон первой таблицы - Теперь ставим знак «равно» и указываем ссылку на диапазон второй таблицы. В нашем примере он такой же, как адрес первой, но перед названиями ячеек нужно написать в кавычках название листа и поставить восклицательный знак. В нашем примере он называется «Таблица 2».
Вводим ссылку на второй диапазон - Теперь остается применить данную конструкцию к выделенным ячейкам. Так как это формула массива, необходимо нажать комбинацию ++ вместо обычного . Если этого не сделать, формула отработает некорректно и в ячейках отобразится ошибка «#ЗНАЧ!». Жмем нужную комбинацию, после чего формула массива вернет результат логического сравнения таблиц, если они занимают два листа.Результат выполнения
Отметим, что формулу массива необязательно использовать только тогда, когда таблицы находятся в разных листах. Этот способ позволит проверить диапазоны на наличие совпадений даже в том случае, если они находятся в одном рабочей области.
Отметим, что таким способом можно выполнить проверку только двух таблиц на разных листах. Сравнить 3 или более документов таким образом нельзя, ведь логическая конструкция может взаимодействовать только с двумя сравниваемыми параметрами. Альтернативой являются только формулы с применением операторов «И» или «ИЛИ», однако при сравнении таблиц на наличие сходств они не используются.
Проверка с помощью функции ЕСЛИ
Формула ЕСЛИ работает по схожему принципу с тем, что был описан в примерах выше. Однако сравнение данных в ячейках с помощью этой функции позволяет пользователю задать собственные значения, которые будут возвращены при значении «ИСТИНА» или «ЛОЖЬ». Иными словами, вместо логического типа данных в ячейках с результатами будет отображаться текст, который указал пользователь. Рассмотрим, как в Excel сравнить таблицы на наличие сходств и различий, используя эту формулу:
- Выделяем ячейку для записи результата и вводим в нее формулу «ЕСЛИ». В качестве первого аргумента нужно указать уже знакомое нам логическое выражение — равенство первых ячеек из обеих таблиц. Указав первый аргумент, ставим разделительный знак — точку с запятой.
Вводим функцию если и логическое выражение - Далее задаем следующий аргумент. Это значение, которое будет отображаться в том случае, если значения ячеек в двух таблицах совпадают. Для примера, укажем текст — слово «Совпадает».
Вводим значение если истина - Аналогичным образом добавляем еще одно значение — это делается на тот случай, если ячейки из двух таблиц различаются. Для примера, введем строку «Не совпадает». Текстовые аргументы нужно указывать внутри формул в кавычках. Иначе программа не сможет интерпретировать конструкцию правильно и вернет ошибку вместо того, чтобы сравнить данные.
Вводим значение если ложь - Нажимаем и получаем результат сравнения первых ячеек из двух таблиц Excel. Используем маркер автоматического заполнения и применяем формулу ко всему диапазону, чтобы получить результаты сравнения всех клеток таблиц.
Результат выполнения
Как видно из примера, функция очень простая и удобная в использовании. Такое сравнение данных можно использовать для таблиц любого размера, а также задействовать в том случае, если они находятся на разных листах. В местах, где значения ячеек одинаковы возвращается слово «Совпадает», тогда как в обратном случае отображается строка «Не совпадает». Текст, подставляемый для значений «ИСТИНА» и «ЛОЖЬ» при использовании функции может быть любой. Сам пользователь решает, что будет отображаться после отработки формулы.
Отметим, что формулу ЕСЛИ также можно применять к целому массиву с данными. Алгоритм действий точно такой же, как и при использовании обычной функции с логическим выражением. Сначала необходимо выделить диапазон для записи результата, соответствующий размеру таблицы.
После этого нажимаем «=» и вводим формулу с указанием сравниваемых диапазонов и прописываем значения для вариантов «ИСТИНА» и «ЛОЖЬ».
После этого нажимаем комбинацию ++. В результате этого таблица будет заполнена необходимыми значениями, которые соответствуют результатам поиска совпавших ячеек.
Выделение групп ячеек
Это удобный инструмент, с помощью которого можно сравнить данные в двух диапазонах либо в разных таблицах в Экселе. Этот метод будет удобен тогда, когда сравниваемые диапазоны находятся на одном листе. Более того, он будет удобен в том случае, если нужно найти отличия в определенной части таблицы:
- Выделяем диапазон с двумя таблицами. Они должны находится в синхронном положении, то есть строки для сравнения должны располагаться друг напротив друга. Это важно при поиске различий, так как инструмент будет искать разницу в значения построчно.
Выделяем две таблицы - Далее переходим во вкладку «Главная». В правой части Панели инструментов нажимаем «Найти и выделить». В выпадающем окне потом выбираем «Выделить группу ячеек».
Нажимаем выделить группу ячеек - В открывшемся окне параметров нужно поставить галочку около надписи «Отличия по строкам». После этого нажмите .
Выбираем параметр по строкам
В результате чего ячейки второй таблицы, значения которых отличаются от первой, будут выделены. Отметим, что для получения результата нужно повторить такую проверку то количество раз, которое соответствует числу столбцов в проверяемых диапазонах. Таким образом можно сравнить таблицы с различными данными и в несколько нажатий определить несовпадающие участки.
Описанный способ нельзя назвать универсальным. Однако если речь идет о небольших таблицах, размещенных на одном листе Excel, этот вариант будет не менее удобным, чем применение формул или визуальное сравнение данных.
Условное форматирование
В наборе инструментов приложения для форматирования есть функция, которая позволяет отыскать и выделить цветом отличающиеся ячейки из двух таблиц Excel. Это удобный вариант для тех случаев, когда нужно сопоставить 2 диапазона или же сравнивать пару столбцов при условии, что они находятся на одном листе. Применять данный метод для документов на разных листах и тем более в разных книгах нецелесообразно. Рассмотрим, как найти совпадения и различия, используя условное форматирование.
При использовании условного форматирования нужно понимать, что две таблицы не являются равнозначными. Одна из них считается основной — эталонной, а вторая — проверяемая, в которой мы находим отличия от основной. Рассмотрим самый простой способ проверки на наличие сходств и различий.
В данном случае все очень просто. Сначала выделяем обе таблицы и нажимаем «Условное форматирование» во вкладке «Главная».
В открывшемся окне выбираем «Правила выделения ячеек» и далее нажимаем пункт «Повторяющиеся значения».
В открывшемся окне параметров можно настроить способ выделения клеток. Можно окрасить в цвет те, которые содержат уникальные значения. Это будут именно те ячейки в двух таблицах, которые различаются. Можно же оставить параметр по умолчанию. Тогда будут выделены все ячейки с одинаковыми значениями, тогда как различия не будут окрашены. Оба варианта позволят вам найти разницу двух таблиц без каких-либо особых усилий.
Описанный способ проверки имеет одно важное преимущество перед другими методами. С его помощью можно осуществить сравнение 3 таблиц. Только необходимо учитывать, что одна из них все равно будет занимать позицию эталонной, тогда как различия будут находиться в двух остальных. Поэтому все же лучше ограничивать проверочные действия двумя таблицами, чтобы в дальнейшем не допускать ошибки при редактировании данных.
Если нужно сравнить не две таблице целиком, а только выделенный фрагмент, например, столбец, можно создать собственное правило форматирования. Данный способ немного сложнее вышеописанного, однако он может быть полезен в том случае, если необходимо осуществить частичную проверку таблицы в Excel и найти разницу значений в двух колонках. Рассмотрим, как это сделать на примере:
- Выделяем столбцы, которые нужно подвергнуть сравнению. Для этого зажимаем , а затем кликаем мышью по верхней и нижней ячейке нужного столбца. Затем зажимаем и повторяем эту процедуру с проверяемым столбцом второй таблицы. Таким образом будут выделены оба проверяемых фрагмента, тогда как остальные части документа при проверке игнорируются.
Выделяем колонки для проверки - Нажимаем «Условное форматирование» во вкладке «Главная». В выпадающем окне далее жмем «Создать правило».
Жмем создать правило - В окне параметров выбираем тип правила «Использовать формулу для определения форматируемых ячеек». Далее нужно вставить логическое выражение — сравнение первой ячейки в двух выделенных столбцах. Между ними стоит знак <>, который означает неравенство. Перед ссылками на ячейки нужно также поставить специальный символ $, чтобы они были абсолютными. После ввода формулы нужно нажать .
Вводим формулу для проверки - Здесь необходимо настроить отображение ячеек, которые будут отформатированы по заданному условию. Самый удобный и простой вариант — изменение цвета заливки. Переходим во вкладку «Заливка» и выбираем любой подходящий цвет. Затем жмем .
Выбираем цвет заливки - Программа вернет вас к окну создания правила. Здесь также жмем кнопку «OK», после чего выделенные ячейки будут отформатированы по заданному условию. Клетки, в которых содержатся неодинаковые значения, будут выделены в нашем случае зеленым цветом.
Результат выполнения
В целом, условное форматирование можно уверенно назвать одним из самых удобных способов, если требуется выполнить сравнения ячеек таблицы Excel. Благодаря гибкому набору параметров у пользователей есть возможность настроить способ отображения ячеек наиболее оптимальным образом.
Отметим, что заливка является не единственным способом оформления при создании правил форматирования. Пользователь также может задавать для значения любой из доступных форматов данных Excel, включая числа, текст, даты. Также можно менять значения, выбирать шрифт, размер, способ начертания и цвет. Кроме того, присутствует возможность изменения типа границ выделяемых ячеек. Все эти функции доступны во вкладках окна параметров форматирования во время создания правила.
Функция СОВПАД
Ранее мы уже познакомились с логическим типом данных в MS Excel и выяснили, как его можно использовать для сравнения двух таблиц. Однако рассматривая вопрос, как найти одинаковые и разные ячейки, стоит обратить внимание на еще один способ определения — формулу СОВПАД. Этот способ удобен в том случае, если необходимо найти различия в ячейках с текстовыми данными.
В отличие от обычного логического выражения, формула СОВПАД реагирует на наличие любых различий в строках. Она вернет значение «ЛОЖЬ» даже в том случае, если отличается регистр хотя бы одной буквы. Это удобно при сравнении текстовых данных, когда нужно обнаружить любые, даже самые незначительные на первый взгляд несоответствия. Способ применения данной функции очень прост. Далее рассмотрим, как пользоваться ею правильно на примере:
- Выделяем ячейку для записи результата. Вводим в нее далее формулу «СОВПАД». В качестве первого аргумента указываем ссылку на первую ячейку проверяемых таблиц.Применяем формулу совпад
- При проверке первых ячеек мы получим результат «ИСТИНА», так как они одинаковые. Далее используем маркер автозаполнения, чтобы применить формулу ко всему строкам и столбцам. Как видно, с помощью формулы мы быстро найдем несоответствия значений внутри таблицы.
Результат выполнения
Как уже было отмечено выше, формула СОВПАД лучше всего подходит для сравнения текстовых данных, так как она реагирует на разницу в регистре букв. Однако это не означает, что ее нельзя задействовать для сравнения других типов данных. Формула также хорошо подходит для сопоставления числовых значений в ячейках, что видно по результатам сравнения третьего столбца.
Как и в случае с другими формулами для проверки таблиц на идентичность, функцию СОВПАД можно использовать для обработки массивов. Этот способ будет работать корректно, если сравниваемые диапазоны имеют равное количество столбцов и строк. Выделяем ячейки для записи результата и вводим формулу, где в качестве аргументов указываем адреса двух диапазонов.
Оформив формулу массива, нажимаем комбинацию +t+. В результате чего получим результат проверки всех ячеек из двух таблиц в виде логических значений.
Формула массива в данном примере, как и в предыдущих случаях, возвращает логическое значение. Однако на основании совокупности параметров «ИСТИНА» и «ЛОЖЬ» можно получить подробное представление о том, есть ли различия между двум таблицами и узнать, в каких именно ячейках они находятся.
Подсчет количества повторяющихся значений
Если необходимо выполнить сравнение фрагментов двух таблиц, например, значений из выделенных столбцов, можно воспользоваться формулой для подсчета числа вхождений. Для этих целей используется функция СЧЕТЕСЛИ, которая выполняет поиск вхождений значения в определенный диапазон. Этот способ проверки табличных данных нельзя назвать универсальным. Однако в определенных случаях пользователю может потребоваться не только узнать, одинаковы ли таблицы, но и выяснить, сколько раз их значения повторяются. Именно в таких ситуациях эта формула будет очень полезна.
Для примера, сравним стоимость товаров в 1 и 2 таблицах. Нам необходимо узнать, одинакова ли цена на каждый товар в первом и втором перечне. Эту задачу можно решить следующим образом:
- Для удобства восприятия копируем наименования товаров. Это делать необязательно, в нашем случае такое действие выполнено для того, чтобы лучше понять суть происходящего. Напротив первого товара вводим формулу СЧЁТЕСЛИ.
Вводим формулу СЧЁТЕСЛИ - В качестве первого аргумента указываем ячейку, в которой будет проверяться вхождение. В нашем случае — это первая клетка из столбца «Стоимость», так как по условиям задачи нужно сопоставить именно цены. Второй аргумент — это значение стоимости из второй таблицы.
Вводим аргументы - Нажимаем кнопку «Enter» и после этого в ячейке будет отображен результат проверки. Если цена в указанных клетках одинаковая — на месте формулы будет отображен результат 1, то есть одно вхождение.
Проверяем идентичность цены - Используя маркер автозаполнения, применяем формулу к остальным наименованиям. При соответствии цен будет стоять значение 1, тогда как при различиях будет отображаться 0.
Применяем к остальным наименованиям
Таким образом можно выполнить проверку соответствия одной или нескольких ячеек, а также целого диапазона. По результатам проверки можно с легкостью определить, есть ли разница между таблицами и выяснить, где именно находятся отличающиеся ячейки.
Подводим итоги
Сравнение двух таблиц в MS Excel — задача, с которой может столкнуться каждый пользователь. Вариантов решения довольно много, потому при выборе оптимального следует опираться не только на индивидуальные навыки и опыт работы в программе, но и на специфику документов, которые подлежат сравнению.
Так как в Excel можно выполнить проверку на наличие сходств и различий несколькими способами, стоит в первую очередь обратить внимание на самые простые. К таковым можно отнести применение формул и логических конструкций для сопоставления ячеек. Более сложным методом являются формулы массивов, позволяющие одновременно обработать множество строк и столбцов. Удобным средством для поиска сходств и различий также является условное форматирование и инструмент выделения группы ячеек.
Загрузка...
