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

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

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

Cравнить две таблицы в Excel на совпадения

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

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

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

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

  1. Отлично! Из примера ниже мы видим, что первая строчка, первого столбца обеих таблиц совпадает, и мы видим значение:

ИСТИНА

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

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

  1. Ради интереса посмотрите какие формулы применили в других ячейках. Маркер автоматически сдвинул адреса ячеек для правильного подсчета.

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

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

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

=А2=Лист1!А2

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

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

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

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

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

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

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

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

  1. Находим функцию СУММПРОИЗВ, она находится в «Математической» категории.

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

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

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

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

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

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

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

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

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

  1. В правом верхнем углу в разделе «Редактирование» кликните по кнопке «Найти и выделить» – «Выделить группу ячеек».

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

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

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

  1. Теперь вы увидите, как выделятся только те ячейки, которые отличаются друг от друга.

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

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

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

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

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

  1. На «Главной» в разделе «Стили» кликаем ЛКМ по кнопке «Условное форматирование» – «Управление правилами…».

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

  1. Создаем правило!

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

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

=$A2<>$D2

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

  1. Если у вас таблица находится на другом листе, то формула будет примерно аналогичная, только не забудьте указать адрес листа:

=$A2<>Лист1!$A2

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

  1. Теперь наша задача как-то выделить и подкрасить эти ячейки – кликните по кнопке «Формат…». На вкладке «Заливка» выделите яркий цвет, отличающийся от фонового. Можно выбрать красный, голубой или желтый. Жмем «ОК».

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

  1. Применяем настройки, еще раз два раза нажав «ОК».

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

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

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

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

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

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

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

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

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

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

  1. Слева в блоке поставьте настройку «Повторяющиеся» и нажмите «ОК».

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

  1. Далее вы увидите, что все одинаковые ячейки будут подкрашены.

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

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

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

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

СЧЁТЕСЛИ

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

СЧЁТЕСЛИ(массив;условие)

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

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

ПРИМЕЧАНИЕ! Я буду показывать на примере двух таблиц, расположенных на одном листе, вы же можете использовать несколько листов. Вы уже знаете, как использовать адрес в таком случае.

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

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

  1. Находим СЧЁТЕСЛИ – формула находится в «Статической» категории.

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

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

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

  1. Ставим «ОК».

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

  1. Копируем формулу с помощью маркера автозаполнения.

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

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

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

  1. Установите курсор в первую ячейку и допишите после знака равно (=) формулу:

ЕСЛИ()

  1. Не забудьте закрыть кавычки, а потом сразу же нажмите по знаку «Функции», чтобы открыть настройки.

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

  1. Должны открыться «Аргументы функции» ЕСЛИ. Если открылось не то окошко – два раза кликните левой кнопкой мыши по надписи «ЕСЛИ» в строке адреса. Далее в первой строке (Лог_выражение) дописываем в самом конце:

=0

  1. Во второй строке используем формулу СТРОКА и выделяем первую ячейку второй таблички.

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

  1. Вы увидите значение «ЛОЖЬ», если обе строчки одинаковые.

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

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

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

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

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

  1. Ставим курсор на первую строчку соседнего пустого столбца.

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

  1. Вставляем формулу:

НАИМЕНЬШИЙ()

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

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

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

  1. Копируем формулу.

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

  1. Теперь в этой строке добавляем:

ИНДЕКС()

  1. Заходим в настройки формулы.

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

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

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

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

-1

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

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

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

На этом все, дорогие читатели. Урок получился достаточно длинным и моментами сложным, поэтому советую сохранить его в закладках, чтобы всегда обращаться к нему как к шпаргалке. До новых встреч на портале WiFiGiD.RU.

Автор статьи
Бородач 2209 статей
Сенсей по решению проблем с WiFiем. Обладатель оленьего свитера, колчана витой пары и харизматичной бороды. Любитель душевных посиделок за танками.
WiFiGid
Комментарии: 3
  1. Аноним

    Спасибо вам большое, за простые примеры. Главное их запомнить еще)

  2. Леонид

    Надо закрепить статью, так как сразу это не запомнить

  3. Гоша

    Спасибо. Примеров много, но на деле буду применять от силы 2-3. все равно
    достаточно интересно.

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

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