Как сравнить таблицы в Excel или столбцы в Excel - Kutools

Как сравнить таблицы в Excel или столбцы в Excel

Сравнение столбцов в excel и выделение отличающихся ячеек

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

Сравнение двух столбцов с помощью инструмента «Выделить группы ячеек»

как сравнить таблицы в excel

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

Сравнить два столбца в excel на совпадения
  1. Далее установим переключатель, как показано:
сравниваем таблицы в Excel

Нажимаем, «ок»

Как сравнить таблицы в Excel или столбцы в Excel

Как видно, все отличия в колонках отмечены и выделены. Далее можно их удалить или отметить цветом.

Как сравнить таблицы в Excel или столбцы в Excel

Условное форматирование для сравнения ячеек или как сравнить таблицы в Excel с помощью цветовой заливки

Сравнить и выделить различные ячейки цветом можно с помощью условного форматирования:

  1. 1. Переходим в раздел «Стили» на вкладке Главная Ленты инструментов Excel, выбираем «Условное форматирование», как показано ниже и нам требуется Создать правило.
Сравнить два столбца в excel на совпадения
  1. 2. В следующем диалоговом окне есть шаблоны правил. Нужно выбрать «Использовать формулу для определения форматируемых ячеек». Правило позволяет сравнивать построчно два столбца.

Знак «<>» в Excel обозначает неравенство.

Как сравнить таблицы в Excel или столбцы в Excel
  1. 3. Следует задать формат ячейки, если формула принимает значение «ИСТИНА». Нажмите кнопку Формат. В появившемся окне можно настроить параметры шрифта и заливки ячейки.
как сравнить ячейки в Excel
  1. 4. Нажимаем «ОК» для каждого окна. После этого разные ячейки будут сравнены и подсвечены в соответствии с заданным условием.
Как сравнить таблицы в Excel или столбцы в Excel

На заметку! Для изменения правила, используйте команду «Управление правилами».

как сравнить таблицы в Эксель

Сравнение столбцов Эксель с помощью функции ВПР

Функция ВПР (вертикальный просмотр) позволяет сравнить данные двух колонок и подставить данные одного столбца к другому соответственно. Такой вариант отлично подойдет для сопоставления данных двух колонок.

  • 1. Например, у нас есть два блока со списком продуктов и ценами на них, в одном из них исходные значения, а в другом – после изменения.
Как сравнить таблицы в Excel или столбцы в Excel
  • 2. В отдельной ячейке нужно ввести формулу ВПР в строке формул и указать аргументы.
Как сравнить таблицы в Excel или столбцы в Excel
  • 3. Чтобы протянуть функцию к другим строкам, нужно использовать маркер автозаполнения.
Как сравнить таблицы в Excel или столбцы в Excel
  • 4. Если в ячейке появилось #Н/Д, то это значит, что в исходном массиве нет данной позиции.

Как сравнить два столбца на совпадения и различия с помощью функции «Если»

Как сравнить 2 столбца в Excel по строкам.

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

1. Проверяем совпадения или различия в строке.

Чтобы выполнить построчное сравнение, применяется функция «ЕСЛИ». Она сравнивает первые две ячейки каждого из столбцов.

Введём эту функцию в отдельный рядом стоящий столбик той же строки, а затем скопируйте ее вниз, с помощью маркера автозаполнения (маленький крестик в правом нижнем углу).

Как сравнить таблицы в Excel или столбцы в Excel

Как сравнить таблицы в Excel или столбцы в Excel

Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.

2. Сравниваем столбцы построчно с учетом регистра.

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

Как сравнить таблицы в Excel или столбцы в Excel

Сравнение в Excel несколько столбцов построчно

Если нам необходимо:

  1. 1. Найти строки с одинаковыми значениями во всех из них.
  2. 2. Найти строки с одинаковыми значениями в любых двух.
  3. то следует воспользоваться следующим способом —

Пример 1. Поиск полного совпадения в одной строке.

Если в таблице более двух колонок, и вы хотите найти строки с одинаковым содержание во всех из них, функция «ЕСЛИ» с оператором «И» подойдет. Запишите её, как показано ниже:

Как сравнить таблицы в Excel или столбцы в Excel

Пример 2. Поиск хотя бы 2-х совпадений.

Если требуется сравнить данные двух или более ячеек в одной строке, применяется функция «ЕСЛИ» с оператором «ИЛИ», как показано ниже:

Как сравнить таблицы в Excel или столбцы в Excel

Если в таблице много колонок для сравнения, то конструкция с оператором «ИЛИ» может быть довольно неудобной. В этом случае лучшим решением будет использование несколько функций «СЧЁТЕСЛИ».

Первый раз «СЧЁТЕСЛИ» подсчитывает, сколько раз значение из первой колонки встречается в других, записанных правее него, второй раз «СЧЁТЕСЛИ» определяет то же самое для значения из второй колонки, и так далее. Если счётчик покажет 0, то в ячейку запишется «Все уникальные», в противном случае — «Найдены одинаковые». Например:

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;»Все уникальные»;»Найдены одинаковые»)

Как сравнить таблицы в Excel или столбцы в Excel

Как сравнить два столбца в Excel на совпадения и различия?

Задача: есть 2 списка данных в Excel, и нужно найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, при условии, что их нет в B. То есть, исходные данные из А мы сравниваем с В.

Для этого можно использовать функцию «СЧЁТЕСЛИ($B:$B;$A2)=0» и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).

Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае — пустую строку:

Как сравнить таблицы в Excel или столбцы в Excel

Примечание. Если таблица имеет фиксированное количество строк, можно указать определенный диапазон (например, $B2:$B20), а не целиком $B:$B, чтобы анализ данных шёл быстрее.

Тот же результат может быть достигнут при использовании функции «ЕСЛИ» вместе с «ЕОШИБКА» и «ПОИСКПОЗ»:

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));»Уникальное»; » Найдено в B»)

Как сравнить две колонки в Excel и извлечь совпадающие данные?

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

Также можно использовать более универсальную комбинацию «ИНДЕКС» и «ПОИСКПОЗ».

Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если есть совпадение, то соответствующая цифра продаж извлекается из колонки B и записывается в колонку Е. Если совпадения не найдено, записывается ошибка «#Н/Д» (нет данных).

Как сравнить таблицы в Excel или столбцы в Excel

Как выделить совпадения и/или отличия в двух колонках в Excel.

При сравнении данных в Excel, может потребоваться «подкрасить» сравниваемые элементы, которые присутствуют в одном, но отсутствуют в другом столбце.Можно закрасить такие позиции любым цветом с помощью формул. Ниже примеры с инструкциями.

1. Выделите совпадения и различия построчно с помощью Условного форматирования.

Чтобы выделить  одинаковые записи во втором столбце по строкам, нужно

  1. 1. Выделить диапазон данных сравнения.
  2. 2. Вызвать команду «Условное форматирование> Новое правило…> Использовать формулу«.
  3. 3. Записать формулу, например =$B2=$A2.

4. Чтобы выделить отличные записи в колонках A и B, нужно использовать правило с формулой =$B2<>$A2

Как сравнить таблицы в Excel или столбцы в Excel

2. Выделить цветом уникальные записи в каждом столбце.

Если сравниваются два столбца в Excel, можно выделить 3 типа элементов:

  • 1. Уникальные, записанные только в первом столбце (уникальные)
  • 2. Уникальные, записанные только во втором столбце (уникальные)
  • 3. Записи, которые есть в обоих списках (дубликаты).

Рассмотрим, как выделить неповторяющиеся элементы в каждом из столбцов.

Если список 1 находится в колонке A (A2:A8), а список 2 — в колонке C (C2:C8), нужно создать правила условного форматирования с помощью следующих формул:

1. Выделить уникальные значения в колонке 1 (столбец A): «=СЧЁТЕСЛИ($A$2:$A$8;C$2)=0«

2. Выделить уникальные значения в столбце 2 (столбец C): «=СЧЁТЕСЛИ($C$2:$C$8;$A2)=0«

Получаем результат:

Как сравнить таблицы в Excel или столбцы в Excel

3. Выделение дубликатов в двух столбцах.

Чтобы функция находила совпадения, а не различия нужно установить счетчик больше нуля:

Как сравнить таблицы в Excel или столбцы в Excel

Выделение цветом отличные и совпадающие ячейки в нескольких столбцах

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

1. Как выделить совпадения в ячейках Excel.

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

Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сравнения.

Как сравнить таблицы в Excel или столбцы в Excel

Можно не ограничиваться сравнением только 3 колонок и использовать формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более колонках.

Сравнение колонок или целых таблиц с помощью надстройки Kutools для Excel

Предположим, нам нужно ставить две небольшие таблицы, как показано ниже. Мы точно знаем, что в них есть отличия, но где они (то есть в каких ячейках) нам нужно узнать. Ячейки нужно выделить цветом.

Как сравнить таблицы в Excel или столбцы в Excel

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

Делается довольно просто:

1. Выделяем обе таблицы, через Ctr — сначала выделить одну табличку, затем нажать Ctr и выделить другую.

2. Вызываем команду Kutools -Сравнить ячейки, как показано ниже

Как сравнить таблицы в Excel или столбцы в Excel

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

Как сравнить таблицы в Excel или столбцы в Excel

Нажимаем «Ок» и видим результат.

Как сравнить таблицы в Excel или столбцы в Excel

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

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

Дополнительные источники: неплохо описано сравнение ячеек в этой статье.

Footer v.1

Владельцем данного ресурса и товарного знака Kutools является ООО «ИННОВА».

По всем вопросам свяжитесь с нами по указанным ниже контактным данным.

Малыгина 84/1
(10am - 04 pm)

Получение файлов и Активация Kutools для физических лиц

Для Excel Для Word Для Outlook OfficeTab
  • Оплачивается получение файлов программы и активация бессрочной лицензии на указанную версию
  • Файлы направляются автоматически после оплаты на указанную почту
  • Версия программы 30.0

Количество Активаций
Скидка, %
Стоимость, руб
Ссылка на оплату
Для 1 ПК 
(к указанному при покупке email начисляется 20% от платежа к любым будущим покупкам)            
05 100
Для 2 ПК                     109 180
Для 3 ПК                     20не доступно
Стоимость для юридических лиц составляет 7250 руб за 1  передачу файлов и Активацию бессрочной лицензии. От количества предусмотрены скидки до 70%. 
  • Оплачивается получение файлов программы и активация бессрочной лицензии на указанную версию
  • Файлы направляются автоматически после оплаты на указанную почту
  • Версия программы 9.0
Количество Kutools для Word
Скидка, %
Стоимость, руб
Ссылка на оплату
Для 1 ПК                      
(к указанному при покупке email начисляется 20% от платежа к любым будущим покупкам)
04 150
Для 2 ПК                     107 470
Для 3 ПК                     20не доступно
Стоимость для юридических лиц составляет 7250 руб за 1  передачу файлов и Активацию бессрочной лицензии. От количества предусмотрены скидки до 70%. 
  • Оплачивается получение файлов программы и активация бессрочной лицензии на указанную версию
  • Файлы направляются автоматически после оплаты на указанную почту
  • Версия программы 14.0
Количество Kutools для Outlook
Скидка, %
Стоимость, руб
Ссылка на оплату
Для 1 ПК                      
(к указанному при покупке email начисляется 20% от платежа к любым будущим покупкам)
04 150
Для 2 ПК                     107 470
Для 3 ПК                     208 520
Стоимость для юридических лиц составляет 7250 руб за 1  передачу файлов и Активацию бессрочной лицензии. От количества предусмотрены скидки до 70%. 
  • Оплачивается получение файлов программы и активация бессрочной лицензии на указанную версию
  • Файлы направляются автоматически после оплаты на указанную почту
  • Версия программы 14.5
Количество OfficeTab
Скидка, %
Стоимость, руб
Ссылка на оплату
Для 1 ПК                      
(к указанному при покупке email начисляется 20% от платежа к любым будущим покупкам)  
02 550
Для 2 ПК                     10не доступно
Для 3 ПК                     20не доступно
Стоимость для юридических лиц составляет 4150 руб за 1  передачу файлов и Активацию бессрочной лицензии. От количества предусмотрены скидки до 70%. 
После оплаты автоматически отправляются файлы:
Архив с файлами программы
Микропрограмма для русификации (.exe) или файлы русификации
Микропрограмма для Активации Kutools или OfficeTab (в зависимости от оплаченного)

Примеры задач и возможности Kutools

Презентации Kutools

Kutools для Excel презентация

Презентация (2 мин, 30 сек.)

Ролик Kutools

Реклама Kutools (35 сек)

Запись презентации Kutools

Запись презентации online

Интересные возможности Kutools

Выбор ячеек одинаковых с другой таблицей

Выбор ячеек одинаковых с другой таблицей

Выбор ячеек с определенным форматом в Excel

Выбор ячеек с определенным форматом в Excel

Слияние таблиц

Слияние таблиц

Описание для картинки

Создание списка файлов в Excel

Описание для картинки

Выделение слов и знаков в ячейках Excel

Как удалить все пустые строки в таблице Excel

Как удалить все пустые строки в таблице Excel

Поиск и вставка пропущенных номеров в таблицу Excel

Поиск и вставка пропущенных номеров в таблицу Excel

Как быстро объединить несколько таблиц

Как быстро объединить несколько таблиц

Использование ВПР при поиске данных в Excel

Использование ВПР при поиске данных в Excel

Слияние таблиц в Excel

Слияние таблиц в Excel по ключевому столбцу

Сравнение двух колонок в Excel

Сравнение двух колонок в Excel

Как в Excel не запоминать формулы

Как в Excel не запоминать формулы

Объединение таблиц из разных файлов Excel

Объединение таблиц из разных файлов Excel

Выпадающий список с картинками в Excel

Выпадающий список с картинками в Excel

Расчет разницы между датами и расчёт возраста на заданную дату в Excel

Расчет разницы между датами и расчёт возраста на заданную дату в Excel