Сравнить два столбца в Excel на совпадения: полный урок

Здравствуйте, дорогие друзья! Сегодняшний урок будет посвящен методам сравнения таблиц в программе Excel. Мы изучим различные способы сопоставить данные в таблицах, а также проведем анализ совпадений между столбцами. В ходе урока мы познакомимся с использованием сложных формул и простых приемов для сравнения небольших объемов информации. Обещаю, что урок будет интенсивным, но увлекательным. Я постарался максимально детально описать все шаги, но если у вас возникнут вопросы или у вас будут свои методы сравнения таблиц – не стесняйтесь, пишите в комментариях!

Способ 1: Логическое сравнение

Если в Excel нужно сравнить два столбца на наличие совпадений, то можно воспользоваться формулой равенства. Она поможет вывести нужные данные, но перед этим лучше отсортировать данные по столбцам. При этом важно определить, каким образом и по какому столбцу проводить сортировку. Идеально выбирать объект, который не повторяется, например, в случае с таблицами заработных плат — по фамилиям сотрудников.

Как только основные столбцы таблиц сравнимы, можно приступать к сравнению:

• Допустим, у нас есть несколько таблиц, и мы хотим сравнить значения двух столбцов. Для начала выбираем пустую ячейку, которая расположена напротив этих столбцов. Затем начинаем вводить формулу вычисления, начиная с знака равно (=). После этого мышью выбираем одну ячейку из первой таблицы, ставим знак равно (=) для сравнения, и затем выбираем аналогичную ячейку из второй таблицы.
• Чтобы применить вычисления, просто щелкните мышью в любом месте или нажмите клавишу:

1. Отлично! Из примера ниже мы можем увидеть, что первая строка, первого столбца обеих таблиц совпадает, и содержит значение:
2. Сейчас необходимо применить этот же метод сравнения и для остальных ячеек. Для этого воспользуемся инструментом автозаполнения – наведите указатель на нужную ячейку, зажмите левую кнопку мыши (ЛКМ) на квадратике и перетащите его до конца таблицы или до места, где вы хотите завершить сравнение.

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

СОВЕТ! Я настоятельно рекомендую изучить урок о маркере автозаполнения. Это один из самых важных уроков, и его должен знать каждый, кто работает с Excel.

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

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

Способ 2: Функция

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

1. Установите курсор в любую ячейку, в которой будет отображаться результат.

ПРИМЕЧАНИЕ! Что касается результатов — мы будем выводить количество несовпадений.

1. Затем нажмите на кнопку вставки функции, которая находится рядом с адресной строкой.

1. Ищем функцию ПРОИЗВЕД, которая расположена в разделе «Математические операции».

1. Давайте теперь пошагово разберем, что нужно делать. Выделите строку «Массив1».
2. Поставьте два знака минус (—), откройте скобку и выделите столбец первой части таблицы.
3. Поставьте знак неравенства (<>)
4. Теперь точно также выделите вторую часть таблицы и закройте скобку. Ориентируйтесь на скриншот ниже.

1. Сейчас мы узнаем количество несовпадений, отображаемое числом. Если оба столбца точно совпадут, то результат будет нулевым (0).

Способ 3: Работа с несколькими столбцами

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

1. Выбираем две таблицы – они должны быть одинакового размера.
2. Переходим на вкладку «Главная».

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

1. Выбираем опцию «Отличия по строкам» и нажимаем кнопку «ОК».

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

Способ 4: Форматирование по условию

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

1. Отметьте данные второй таблицы.

1. На вкладке «Главная» выбираем раздел «Стили» и щелкаем левой кнопкой мыши на кнопке «Условное форматирование» — «Управление правилами…».

1. Подбираем правило «Применять уравнение для определения форматируемых ячеек».
2. Сейчас придется указать уравнение. Начинаем с символа равно (=). Потом выделяем сначала первую ячейку первой таблицы. Обратите внимание, чтобы адрес был абсолютным по столбцам, но изменялся по строкам. Затем ставим знак неравенства (<>) и указываем ячейку второй таблицы. Пример:

1. В случае, если ваша таблица расположена на другом листе, то формула будет примерно такой же, но не забудьте указать адрес листа:

1. Теперь нужно выделить и подкрасить ячейки. Для этого нажмите на кнопку «Формат». Затем на вкладке «Заливка» выберите яркий цвет, который отличается от фона. Можно выбрать красный, голубой или желтый. После этого нажмите «ОК».

1. Необходимо выполнить настройки и нажать кнопку «ОК» дважды.

1. Заметьте, что теперь ячейки окрашены в другой цвет.

Способ 5: Совпадения и отличия

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

1. Выберите область из двух таблиц.

1. Категория «Условное форматирование» включает в себя «Правила выделения ячеек» и «Повторяющиеся значения»

1. Разместите настройку «Повторяющиеся» слева в блоке и нажмите «ОК».
2. Затем вы заметите, что все одинаковые ячейки будут выделены цветом.

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

Способ 6: Сложные формулы

В данной главе мы изучим применение комплексных формул в Excel для сравнения ячеек на их соответствие. Я мог бы сейчас все объяснить, но лучше рассмотреть все на практике, чтобы в конце вы поняли, о чем идет речь. Для примера мы возьмем функцию:

Она позволяет подсчитать количество ячеек, которые соответствуют выбранному условию. Давайте рассмотрим синтаксис формулы:

СЧЁТЕСЛИ(диапазон;условие)

• Диапазон – это набор ячеек, в котором мы производим подсчет.
• Условие – условие, по которому мы производим подсчет выбранных ячеек.

Если вам пока что-то не ясно, не беспокойтесь, сейчас мы все рассмотрим на практическом примере.

Обратите внимание! Я покажу на примере двух таблиц, находящихся на одном листе, но вы можете использовать несколько листов. Вы уже знаете, как использовать адрес в таком случае.

1. Вы можете создать новый столбец, в который будут выводиться результаты сравнения.
2. Помещаем курсор в ячейку и вставляем формулу.

1. Отыскиваем ФУНКЦИЮ СЧЁТЕСЛИ, которая находится в разделе «Статическая».

1. Теперь следует выполнить инструкцию поэтапно. Начнем с того, чтобы поставить курсор в строку «Диапазон».
2. Выбираем диапазон второй таблицы. ОЧЕНЬ ВАЖНО, чтобы все адреса были абсолютными – то есть перед адресом строки и столбца стоял знак доллар ($). Чтобы сделать это быстрее и не вводить вручную, выделите строчку и нажмите на клавишу:
3. Выбираем «Критерий».
4. Выбираем первую ячейку первой таблицы.

1. Используем автоматическое заполнение для копирования формулы.

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

1. Поместите указатель в начало первой ячейки и добавьте формулу после знака равенства (=):
2. Не забудьте закрыть кавычки, затем сразу же нажмите на кнопку «Функции», чтобы открыть настройки.

1. Если не отображается «Аргументы функции» для условия «ЕСЛИ», дважды щелкните левой кнопкой мыши по слову «ЕСЛИ» в строке адреса. Затем в первой строке (Лог_выражение) добавьте в конце:
2. Во второй строке используйте функцию СТРОКА и выберите первую ячейку второй таблицы.

1. Если обе строки идентичны, то на экране появится слово «ЛОЖЬ».

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

1. Укажите порядковый номер для столбца, расположенного напротив таблицы — также можно использовать маркер.

1. Размещаем указатель на первую строку смежного пустого столбца.

1. Напишите формулу:

1. Начнем с выбора столбца, в котором мы показывали номера строк. Затем укажем только первый элемент нашего пронумерованного столбца.

1. Делаем копию формулы.

1. Сейчас необходимо внести следующие изменения в эту строку:
2. Заходим в параметры формулы.

1. Не требуется вносить изменения в окне, оставьте настройки по умолчанию.
2. Укажите в качестве первого массива столбец второй таблицы и сделайте адреса абсолютными. Можно воспользоваться клавишей F4, чтобы не вводить знак доллара ($) вручную.
3. В поле «Номер строки» введите:

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

Это все, уважаемые читатели. Урок оказался довольно длинным и иногда сложным, поэтому я рекомендую сохранить его в закладках, чтобы всегда иметь возможность обратиться к нему как к подсказке. До новых встреч на портале MoiSovety.ru.