Сравнение столбцов в excel и выделение отличающихся ячеек
Сравнение ячеек в Excel довольно частая задача, которую лучше выполнять автоматически, т.к. в ручную это займет много времени.
Сравнение двух столбцов с помощью инструмента «Выделить группы ячеек»
Если у нас есть две колонки, которые нужно сравнить, выделим их и перейдём в раздел «Редактирование» на ленте инструментов в группку «Найти и выделить» и вызовем команду «Выделить группу ячеек», как показано ниже.
- Далее установим переключатель, как показано:
Нажимаем, «ок»
Как видно, все отличия в колонках отмечены и выделены. Далее можно их удалить или отметить цветом.
Условное форматирование для сравнения ячеек или как сравнить таблицы в Excel с помощью цветовой заливки
Сравнить и выделить различные ячейки цветом можно с помощью условного форматирования:
- 1. Переходим в раздел «Стили» на вкладке Главная Ленты инструментов Excel, выбираем «Условное форматирование», как показано ниже и нам требуется Создать правило.
- 2. В следующем диалоговом окне есть шаблоны правил. Нужно выбрать «Использовать формулу для определения форматируемых ячеек». Правило позволяет сравнивать построчно два столбца.
Знак «<>» в Excel обозначает неравенство.
- 3. Следует задать формат ячейки, если формула принимает значение «ИСТИНА». Нажмите кнопку Формат. В появившемся окне можно настроить параметры шрифта и заливки ячейки.
- 4. Нажимаем «ОК» для каждого окна. После этого разные ячейки будут сравнены и подсвечены в соответствии с заданным условием.
На заметку! Для изменения правила, используйте команду «Управление правилами».
Сравнение столбцов Эксель с помощью функции ВПР
Функция ВПР (вертикальный просмотр) позволяет сравнить данные двух колонок и подставить данные одного столбца к другому соответственно. Такой вариант отлично подойдет для сопоставления данных двух колонок.
- 1. Например, у нас есть два блока со списком продуктов и ценами на них, в одном из них исходные значения, а в другом – после изменения.
- 2. В отдельной ячейке нужно ввести формулу ВПР в строке формул и указать аргументы.
- 3. Чтобы протянуть функцию к другим строкам, нужно использовать маркер автозаполнения.
- 4. Если в ячейке появилось #Н/Д, то это значит, что в исходном массиве нет данной позиции.
Как сравнить два столбца на совпадения и различия с помощью функции «Если»
Как сравнить 2 столбца в Excel по строкам.
Когда вы выполняете анализ данных в Excel, одной из наиболее частых задач является сравнение данных нескольких колонок в каждой отдельной их строке. Эту задачу можно выполнить с помощью функции ЕСЛИ , как показано в следующих примерах.
1. Проверяем совпадения или различия в строке.
Чтобы выполнить построчное сравнение, применяется функция «ЕСЛИ». Она сравнивает первые две ячейки каждого из столбцов.
Введём эту функцию в отдельный рядом стоящий столбик той же строки, а затем скопируйте ее вниз, с помощью маркера автозаполнения (маленький крестик в правом нижнем углу).
Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.
2. Сравниваем столбцы построчно с учетом регистра.
Формулы из предыдущего примера не учитывают регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если требуется найти совпадения с учетом регистра, нужно применять функцию «СОВПАД»:
Сравнение в Excel несколько столбцов построчно
Если нам необходимо:
- 1. Найти строки с одинаковыми значениями во всех из них.
- 2. Найти строки с одинаковыми значениями в любых двух.
- то следует воспользоваться следующим способом —
Пример 1. Поиск полного совпадения в одной строке.
Если в таблице более двух колонок, и вы хотите найти строки с одинаковым содержание во всех из них, функция «ЕСЛИ» с оператором «И» подойдет. Запишите её, как показано ниже:
Пример 2. Поиск хотя бы 2-х совпадений.
Если требуется сравнить данные двух или более ячеек в одной строке, применяется функция «ЕСЛИ» с оператором «ИЛИ», как показано ниже:
Если в таблице много колонок для сравнения, то конструкция с оператором «ИЛИ» может быть довольно неудобной. В этом случае лучшим решением будет использование несколько функций «СЧЁТЕСЛИ».
Первый раз «СЧЁТЕСЛИ» подсчитывает, сколько раз значение из первой колонки встречается в других, записанных правее него, второй раз «СЧЁТЕСЛИ» определяет то же самое для значения из второй колонки, и так далее. Если счётчик покажет 0, то в ячейку запишется «Все уникальные», в противном случае — «Найдены одинаковые». Например:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;»Все уникальные»;»Найдены одинаковые»)
Как сравнить два столбца в Excel на совпадения и различия?
Задача: есть 2 списка данных в Excel, и нужно найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, при условии, что их нет в B. То есть, исходные данные из А мы сравниваем с В.
Для этого можно использовать функцию «СЧЁТЕСЛИ($B:$B;$A2)=0» и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).
Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае — пустую строку:
Примечание. Если таблица имеет фиксированное количество строк, можно указать определенный диапазон (например, $B2:$B20), а не целиком $B:$B, чтобы анализ данных шёл быстрее.
Тот же результат может быть достигнут при использовании функции «ЕСЛИ» вместе с «ЕОШИБКА» и «ПОИСКПОЗ»:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));»Уникальное»; » Найдено в B»)
Как сравнить две колонки в Excel и извлечь совпадающие данные?
Если требоваться не только сравнить две колонки, но и извлечь соответствующие записи, можно воспользоваться функцией ВПР.
Также можно использовать более универсальную комбинацию «ИНДЕКС» и «ПОИСКПОЗ».
Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если есть совпадение, то соответствующая цифра продаж извлекается из колонки B и записывается в колонку Е. Если совпадения не найдено, записывается ошибка «#Н/Д» (нет данных).
Как выделить совпадения и/или отличия в двух колонках в Excel.
При сравнении данных в Excel, может потребоваться «подкрасить» сравниваемые элементы, которые присутствуют в одном, но отсутствуют в другом столбце.Можно закрасить такие позиции любым цветом с помощью формул. Ниже примеры с инструкциями.
1. Выделите совпадения и различия построчно с помощью Условного форматирования.
Чтобы выделить одинаковые записи во втором столбце по строкам, нужно
- 1. Выделить диапазон данных сравнения.
- 2. Вызвать команду «Условное форматирование> Новое правило…> Использовать формулу«.
- 3. Записать формулу, например =$B2=$A2.
4. Чтобы выделить отличные записи в колонках A и B, нужно использовать правило с формулой =$B2<>$A2
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«
Получаем результат:
3. Выделение дубликатов в двух столбцах.
Чтобы функция находила совпадения, а не различия нужно установить счетчик больше нуля:
Выделение цветом отличные и совпадающие ячейки в нескольких столбцах
При сравнении значений в нескольких колонках построчно, самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — воспользоваться инструментом «Выделить группу ячеек», как показано в следующих примерах.
1. Как выделить совпадения в ячейках Excel.
Чтобы выделить строки, которые имеют одинаковые значения, нужно задать правило условного форматирования на основе одного из следующих выражений:
Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сравнения.
Можно не ограничиваться сравнением только 3 колонок и использовать формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более колонках.
Сравнение колонок или целых таблиц с помощью надстройки Kutools для Excel
Предположим, нам нужно ставить две небольшие таблицы, как показано ниже. Мы точно знаем, что в них есть отличия, но где они (то есть в каких ячейках) нам нужно узнать. Ячейки нужно выделить цветом.
Вручную искать различия в таблицах, долго. Мы воспользуемся надстройкой Kutools для Excel.
Делается довольно просто:
1. Выделяем обе таблицы, через Ctr — сначала выделить одну табличку, затем нажать Ctr и выделить другую.
2. Вызываем команду Kutools -Сравнить ячейки, как показано ниже
В диалоговом окне выбираем опции: программа автоматически выбрала диапазоны для сравнения и предлагает варианты Поиска — либо ей искать разные ячейки, либо отличные. Также следует указать формат результатов, то есть залить ячейки пос результатам сравнения или нет и/или изменить формат шрифта. Также предлагается учитывать регистр записи при сравнении таблиц.
Нажимаем «Ок» и видим результат.
Программа подсчитала и показала результат сравнения и подкрасила и выбрала отличающиеся ячейки.
Если нужно выделить и подкрасить отличия во второй таблице, то следует в диалоговом окне первым указать «диапазон В».
Дополнительные источники: неплохо описано сравнение ячеек в этой статье.