Условное форматирование Excel для сравнения столбцов
Обновлено: 04.07.2024
В Excel можно сравнить два столбца и выделить их различия с помощью условного форматирования. Допустим, у вас есть следующие данные с двумя списками имен в столбцах B и C.
Чтобы выделить все различия (строки 3, 6, 7 и 9) красным цветом, выполните следующие действия:
<р>1. Выберите данные в столбцах, которые вы хотите сравнить, и на ленте выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
Вы также можете выбрать Дублировать; в этом случае будут выделены одни и те же значения.
В результате ячейки, содержащие разные значения в столбцах B и C, выделяются красным цветом.
Сравните два столбца и выделите различия в Google Таблицах
Вы также можете сравнить два столбца, используя условное форматирование в Google Таблицах.
<р>1. Выберите диапазон данных, который вы хотите сравнить (B2:C9), и в меню выберите Формат > Условное форматирование.
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
| Метод A Условное форматирование строк или ячеек, если два столбца равны | Метод B Выделите и выберите ячейки, если два столбца равны |
 |  |
Метод A Условное форматирование строк или ячеек, если два столбца равны
В Excel вы можете использовать функцию условного форматирования для автоматического затенения строк или ячеек, если два столбца равны.
<р>1. Выберите первый список данных, которые вы хотите сравнить со вторым, например, A2:A7, затем нажмите Главная > Условное форматирование > Новое правило.
Совет. A2 — это первая ячейка в первом списке, а B2 — это первая ячейка во втором списке, с которым вы хотите сравнить.
Примечание: если вы хотите заполнить строки, когда два столбца равны, выберите два списка в качестве диапазона, а затем примените диалоговое окно «Новое правило форматирования».
Метод Б. Выделите и выберите ячейки, если два столбца равны
Если вы хотите выделить ячейки, если два столбца равны, и в то же время выбрать ячейки, вы можете использовать функцию «Сравнить ячейки» в Kutools for Excel.
После бесплатной установки Kutools for Excel сделайте следующее:
Пример файла
Другие операции (статьи), связанные с условным форматированием
Подсчет/суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых способах быстрого подсчета или суммирования ячеек по цветам с условным форматированием в Excel.
создайте диаграмму с условным форматированием в Excel
Например, у вас есть таблица оценок класса, и вы хотите создать диаграмму, чтобы раскрасить оценки в разных диапазонах, здесь этот учебник познакомит вас с методом решения эта работа.
Применить условное форматирование для каждой строки
Иногда вам может потребоваться применить условное форматирование для каждой строки, за исключением многократной установки одних и тех же правил для каждой строки, есть некоторые хитрости для решения этой задачи.
Условное форматирование гистограммы с накоплением в Excel
В этом руководстве показано, как создать гистограмму с накоплением при условном форматировании, как показано на снимке экрана ниже, шаг за шагом, в Excel.
Поиск и выделение результатов поиска в Excel
В Excel можно использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух разных способах, которые помогут вам выполнять поиск и одновременно выделять результаты поиска в Excel.
Независимо от того, сравниваете ли вы один список или несколько, условное форматирование Excel может решить эту задачу.
Сравнение списков на наличие общих значений или дубликатов — это задача, в которой часто используется много переменных. Вы можете сравнить значения в одном списке или сравнить один список с другим. Затем следует определение дубликата. Вы найдете много решений, если будете искать в Интернете, но вы не найдете универсального решения для всех. Вы должны знать свои данные и применять соответствующее решение.
В этой статье мы будем использовать условное форматирование для сравнения списков и поиска дубликатов. Во-первых, мы применим встроенное правило дубликатов для сравнения элементов в одном списке; затем мы будем использовать его для сравнения двух списков. Далее мы будем использовать пользовательское правило условного форматирования для поиска дубликатов, когда встроенное правило не подходит.
Подробнее о программном обеспечении
Я использую Excel 2016 (настольный компьютер) в системе Windows 10, но эти правила доступны в более ранних версиях ленты. Вы можете работать со своими данными или загрузить демонстрационные файлы .xls и .xlsx. Браузерная версия поддерживает существующие правила условного форматирования, и вы даже можете применять встроенные правила. Однако вы не можете применять пользовательские правила в браузере.
Встроенное правило
Для сравнения данных можно использовать формулу с условным форматированием, но иногда с этой задачей справляются встроенные правила.Вам не понадобятся специальные знания, но вы должны понимать, как работает эта функция, чтобы избежать разочарований. Чтобы проиллюстрировать это, мы сначала рассмотрим, как встроенное правило сравнивает элементы в одном списке, используя простую таблицу, показанную на рисунке A, следующим образом:
- Выберите первый список, B1:B12. (Я намеренно повторил последний пункт.)
- На вкладке "Главная" нажмите "Условное форматирование" в группе "Стили".
- Выберите «Правила выделения ячеек», а затем выберите «Повторяющиеся значения» в следующем меню (рис. A).
- В появившемся диалоговом окне выберите подходящий формат и нажмите «ОК». Как показано на рис. Б, это встроенное правило выделяет дубликаты в столбце одного и того же, поскольку мы выбрали один столбец.
Рисунок А
Это встроенное правило дублирования сравнивает элементы в одном списке.
Рисунок Б
Встроенные правила выделяют дубликаты в одном столбце.
Теперь давайте воспользуемся тем же встроенным правилом для сравнения списка в столбце B со списком в столбце C. Для этого выберите B2:C12 и выполните те же действия, что и выше. На рисунке С показаны результаты. Это правило применяет выделение, если элемент появляется более одного раза в любом месте. Это могут быть дубликаты в одном и том же столбце или элементы, встречающиеся более одного раза в обоих столбцах.
Рисунок C
Правило дублирования выделяет любой элемент, который встречается в выбранном диапазоне более одного раза.
Пользовательские правила
Два списка, показанные на рис. A, похожи, но есть небольшие различия. Несколько элементов уникальны для обоих списков. Кроме того, иногда элемент в столбце B отличается от соответствующего элемента в столбце C. В этом разделе мы будем использовать пользовательское правило условного форматирования, чтобы определить элементы, которые отличаются от одного столбца к другому. Вы можете думать об этих предметах как о несоответствующих. Мы уже видели, что встроенное правило оценивает все значения в любой позиции, а это не то, что нам нужно.
Условное форматирование позволяет быстро определить различия между двумя списками — от столбца к столбцу — с помощью выражения в форме:
Теперь применим это правило к столбцу B следующим образом:
- Выберите список в столбцах B, B2:B12.
- Перейдите на вкладку "Главная", нажмите "Условное форматирование" в группе "Стили" и выберите "Новое правило" в раскрывающемся списке.
- Выберите вариант Использовать формулу для определения форматируемых ячеек.
- Введите =СЧЁТЕСЛИ(C2:C2,B2)=0 в поле "Формула".
- Нажмите «Формат», перейдите на вкладку «Заливка», выберите цвет и нажмите «ОК». На рисунке D показаны формат и правило.
- Нажмите "ОК", чтобы вернуться к листу, показанному на рис. E.
Рисунок D
Это правило выделяет элементы в столбце B, которые не соответствуют соответствующим элементам в столбце C.
Рисунок E
Выделенные элементы в столбце B не соответствуют соответствующему значению в столбце C.
Это правило не выделяет дубликаты; любое значение в столбце B, которое содержит значение не в соответствующей ячейке столбца C, легко определить благодаря контрастному цвету заливки ячейки. Оно не выделяет значения, встречающиеся в обоих столбцах, как это делало встроенное правило. Чтобы применить правило к столбцу B вместо этого, вы должны использовать правило =COUNTIF(B2:B2,C2)=0 после выбора C2:C11.
Это правило работает как со значениями, так и с текстовыми записями. Списки также не должны совпадать по размеру. Например, правило выделяет последнее значение, Миши Кобе Нику, потому что соответствующая ячейка в столбце C пуста. Кроме того, небольшие различия имеют значение. Например, если вы удалите символ апострофа в имени Боба, элементы больше не будут совпадать. С другой стороны, сравнение не чувствительно к регистру. Если формат не выделяет элемент, как вы ожидаете, сравните два элемента. Возможно, есть дополнительный пробел.
Далее мы будем использовать ту же технику с немного другой ссылкой для сравнения двух списков. если элемент в столбце B нигде не встречается в столбце C, выделите этот элемент в столбце B. Ранее мы выделяли несовпадающие элементы из столбца в столбец; теперь мы выделим несопоставленные элементы в обоих столбцах.
В этом примере мы выделим элементы в столбце B, которые не встречаются в столбце C, следующим образом:
- Выделите B2:B12.
- Перейдите на вкладку "Главная", нажмите "Условное форматирование" в группе "Стили" и выберите "Новое правило" в раскрывающемся списке.
- Выберите вариант Использовать формулу для определения форматируемых ячеек.
- Введите =СЧЁТЕСЛИ($C$2:$C$12,$B2)=0 в элементе управления формулой.
- Нажмите «Формат», перейдите на вкладку «Заливка», выберите цвет и нажмите «ОК». На рисунке F показаны формат и правило.
- Нажмите "ОК", чтобы вернуться к листу, показанному на рис. G.
Рисунок F
Это правило выделяет элементы в столбце B, которые не встречаются нигде в столбце C.
Рисунок G
Любое значение в столбце B, которое не встречается в столбце C, легко определить благодаря контрастному цвету заливки ячейки.
Как видите, в столбце B есть три элемента, которых нет в столбце C. А как насчет столбца C? Чтобы определить элементы в столбце C, которые не встречаются в столбце B, повторите описанные выше шаги. Однако на шаге 1 выберите C2:C12, а на шаге 4 введите формулу =СЧЁТЕСЛИ($B$2:$B$12,$C2)=0; выберите другой цвет на шаге 5, если хотите. Как видно на рисунке H, четыре элемента в столбце C, включая пробел в C12, не встречаются в столбце B.
Рисунок Н
Два похожих правила выделяют элементы, которые не встречаются нигде в другом списке (столбце).
Оставайтесь с нами
В следующей статье мы продолжим рассмотрение более сложных задач поиска дубликатов.
Сравнение столбцов в Excel — это то, чем мы все время от времени занимаемся. Microsoft Excel предлагает ряд параметров для сравнения и сопоставления данных, но большинство из них сосредоточено на поиске в одном столбце. В этом руководстве мы рассмотрим несколько методов сравнения двух столбцов в Excel и поиска совпадений и различий между ними.
Как сравнить 2 столбца в Excel построчно
При анализе данных в Excel одной из наиболее частых задач является сравнение данных в каждой отдельной строке. Эту задачу можно выполнить с помощью функции ЕСЛИ, как показано в следующих примерах.
Пример 1. Сравните два столбца на наличие совпадений или различий в одной строке
Чтобы сравнить два столбца в Excel построчно, напишите обычную формулу ЕСЛИ, которая сравнивает первые две ячейки. Введите формулу в другой столбец той же строки, а затем скопируйте ее в другие ячейки, перетащив маркер заполнения (небольшой квадрат в правом нижнем углу выбранной ячейки). При этом курсор изменится на знак плюса:
Формула совпадений
Чтобы найти ячейки в одной строке с одинаковым содержимым, A2 и B2 в этом примере, формула выглядит следующим образом:
Формула различий
Чтобы найти ячейки в одной строке с разными значениями, просто замените знак равенства на знак отсутствия равенства (<>):
Совпадения и различия
И, конечно же, ничто не мешает вам находить как совпадения, так и различия с помощью одной формулы:
Результат может выглядеть примерно так:
Как видите, формула одинаково хорошо обрабатывает числа, даты, время и текстовые строки.
Пример 2. Сравнение двух списков на совпадения с учетом регистра в одной строке
Как вы, наверное, заметили, формулы из предыдущего примера не учитывают регистр при сравнении текстовых значений, как в строке 10 на снимке экрана выше. Если вы хотите найти совпадения с учетом регистра между двумя столбцами в каждой строке, используйте функцию EXACT:
=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "")
Чтобы найти различия в одной и той же строке с учетом регистра, введите соответствующий текст ("Уникальный" в данном примере) в 3-й аргумент функции ЕСЛИ, например:
=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "Уникальный")
Сравнить несколько столбцов на наличие совпадений в одной строке
На листах Excel несколько столбцов можно сравнивать по следующим критериям:
- Найти строки с одинаковыми значениями во всех столбцах (пример 1)
- Найти строки с одинаковыми значениями в любых двух столбцах (пример 2)
Пример 1. Поиск совпадений во всех ячейках одной строки
Если в вашей таблице есть три или более столбца и вы хотите найти строки с одинаковыми значениями во всех ячейках, формула ЕСЛИ с оператором И подойдет:
=ЕСЛИ(И(A2=B2, A2=C2), "Полное совпадение", "")
Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ:
=ЕСЛИ(СЧЁТЕСЛИ($A2:$E2, $A2)=5, "Полное совпадение", "")
Где 5 — количество сравниваемых столбцов.
Пример 2. Поиск совпадений в любых двух ячейках в одной строке
Если вы ищете способ сравнить столбцы для любых двух или более ячеек с одинаковыми значениями в одной строке, используйте формулу ЕСЛИ с оператором ИЛИ:
=ЕСЛИ(ИЛИ(A2=B2, B2=C2, A2=C2), "Соответствие", "")
Если нужно сравнить много столбцов, оператор ИЛИ может стать слишком большим. В этом случае лучшим решением будет добавление нескольких функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько столбцов имеют то же значение, что и в 1-м столбце, второй СЧЁТЕСЛИ подсчитывает, сколько из оставшихся столбцов равны 2-му столбцу, и так далее. Если счетчик равен 0, формула возвращает «Уникальный», в противном случае — «Совпадение». Например:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2,A2)+СЧЁТЕСЛИ(C2:D2,B2)+(C2=D2)=0,"Уникальный","Совпадение")
Как сравнить два столбца в Excel на совпадения и различия
Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые строки), которые находятся в столбце A, но не в столбце B.
Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B, $A2)=0 в логическую проверку ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (как минимум 1 совпадение). найдено).
Например, следующая формула ЕСЛИ/СЧЕТЕСЛИ ищет по всему столбцу B значение в ячейке A2. Если совпадений не найдено, формула возвращает «Нет совпадений в B», в противном случае — пустую строку:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "")
Совет. Если в вашей таблице фиксированное количество строк, вы можете указать определенный диапазон (например, $B2:$B10), а не весь столбец ($B:$B), чтобы формула работала быстрее с большими наборами данных.
Того же результата можно добиться, используя формулу ЕСЛИ со встроенными функциями ЕОШИБКА и ПОИСКПОЗ:
=IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"Нет совпадений в B","")
Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):
=IF(SUM(--($B$2:$B$10=$A2))=0, "Нет совпадений в B", "")
Если вы хотите, чтобы одна формула определяла как совпадения (дубликаты), так и различия (уникальные значения), поместите некоторый текст для совпадений в пустые двойные кавычки ("") в любой из приведенных выше формул. Например:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "Соответствует в B")
Как сравнить два списка в Excel и найти совпадения
Иногда может потребоваться не только сопоставить два столбца в двух разных таблицах, но и извлечь совпадающие записи из таблицы поиска. В Microsoft Excel для этого предусмотрена специальная функция — функция ВПР. В качестве альтернативы вы можете использовать более мощную и универсальную формулу INDEX MATCH. Пользователи Excel 2021 и Excel 365 могут выполнить эту задачу с помощью функции XLOOKUP.
=ВПР(D2, $A$2:$B$6, 2, ЛОЖЬ)
=ИНДЕКС($B$2:$B$6, ПОИСКПОЗ($D2, $A$2:$A$6, 0))
Если вам не очень удобно работать с формулами, вы можете выполнить эту работу с помощью быстрого и интуитивно понятного решения — Мастера объединения таблиц.
Сравните два списка и выделите совпадения и различия
При сравнении столбцов в Excel может потребоваться «визуализировать» элементы, которые присутствуют в одном столбце, но отсутствуют в другом.Вы можете закрасить такие ячейки любым цветом по вашему выбору, используя функцию условного форматирования Excel, и следующие примеры демонстрируют подробные шаги.
Пример 1. Выделение совпадений и различий в каждой строке
Чтобы сравнить два столбца и Excel и выделить ячейки в столбце A, которые имеют идентичные записи в столбце B в той же строке, выполните следующие действия:
- Выберите ячейки, которые хотите выделить (вы можете выбрать ячейки в одном столбце или в нескольких столбцах, если хотите закрасить целые строки).
- Нажмите Условное форматирование > Новое правило… > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
- Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок столбца). Убедитесь, что вы используете относительную ссылку на строку (без знака $), как в приведенной выше формуле.
Чтобы выделить различия между столбцами A и B, создайте правило со следующей формулой:
=$B2<>$A2
Если вы не знакомы с условным форматированием в Excel, пошаговые инструкции см. в разделе Как создать правило условного форматирования на основе формулы.
Пример 2. Выделение уникальных записей в каждом списке
Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:
- Элементы, находящиеся только в первом списке (уникальные)
- Элементы, находящиеся только во втором списке (уникальные)
- Элементы, которые есть в обоих списках (дубликаты) — показано в следующем примере.
В этом примере показано, как раскрасить элементы только в одном списке.
Предположим, что ваш список 1 находится в столбце A (A2:A6), а список 2 — в столбце C (C2:C5). Вы создаете правила условного форматирования со следующими формулами:
Выделить уникальные значения в списке 1 (столбец A):
Выделить уникальные значения в списке 2 (столбец C):
И получите следующий результат:
Пример 3. Выделение совпадений (дубликатов) между двумя столбцами
Если вы точно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой формул СЧЁТЕСЛИ, чтобы они находили совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:
Выделить совпадения в списке 1 (столбец A):
Выделить совпадения в списке 2 (столбец C):
Выделить различия строк и совпадения в нескольких столбцах
При сравнении значений в нескольких столбцах построчно самый быстрый способ выделить совпадения — создать правило условного форматирования, а самый быстрый способ скрыть различия — воспользоваться функцией Перейти к специальному, как показано в следующих примерах.
Пример 1. Сравните несколько столбцов и выделите совпадения строк
Чтобы выделить строки с одинаковыми значениями во всех столбцах, создайте правило условного форматирования на основе одной из следующих формул:
Где A2, B2 и C2 — самые верхние ячейки, а 3 — количество столбцов для сравнения.
Конечно, ни формула И, ни СЧЁТЕСЛИ не ограничиваются сравнением только 3 столбцов, вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбцах.
Пример 2. Сравните несколько столбцов и выделите различия в строках
Чтобы быстро выделить ячейки с разными значениями в каждой отдельной строке, вы можете использовать функцию Excel "Перейти к специальному".
-
Выберите диапазон ячеек, которые вы хотите сравнить. В этом примере я выбрал ячейки с A2 по C8.
По умолчанию самая верхняя ячейка выбранного диапазона является активной ячейкой, и ячейки из других выбранных столбцов в той же строке будут сравниваться с этой ячейкой. Как вы можете видеть на снимке экрана выше, активная ячейка имеет белый цвет, а все остальные ячейки выбранного диапазона выделены. В этом примере активной ячейкой является A2, поэтому столбцом сравнения является столбец A.
Чтобы изменить столбец сравнения, используйте либо клавишу Tab для перемещения по выбранным ячейкам слева направо, либо клавишу Enter для перемещения сверху вниз.
Совет. Чтобы выбрать несмежные столбцы, выберите первый столбец, нажмите и удерживайте клавишу Ctrl , а затем выберите другие столбцы. Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу Tab или Enter, как описано выше.
Как сравнить две ячейки в Excel
Фактически, сравнение двух ячеек — это частный случай сравнения двух столбцов в Excel построчно, за исключением того, что вам не нужно копировать формулы в другие ячейки столбца.
Например, для сравнения ячеек A1 и C1 можно использовать следующие формулы.
Чтобы узнать о нескольких других способах сравнения ячеек в Excel, см. раздел Как сравнивать строки в Excel.
Способ сравнения двух столбцов/списков в Excel без формул
Теперь, когда вы знакомы с предложениями Excel для сравнения и сопоставления столбцов, позвольте мне показать вам наше собственное решение для этой задачи. Этот инструмент называется «Сравнить две таблицы» и входит в состав Ultimate Suite.
Надстройка может сравнивать две таблицы или списки по любому количеству столбцов, а также выявлять совпадения/различия (как мы делали с формулами) и выделять их (как мы делали с условным форматированием).
Для целей этой статьи мы сравним два следующих списка, чтобы найти общие значения, присутствующие в обоих.
Чтобы сравнить два списка, выполните следующие действия:
- Повторяющиеся значения (совпадения) — элементы, существующие в обоих списках.
- Уникальные значения (различия) — элементы, которые присутствуют в списке 1, но отсутствуют в списке 2.
Статус с метками "Дубликат" или "Уникальный" (как в формулах ЕСЛИ).
< бр />
Для этого примера я решил выделить дубликаты следующим цветом:
И через мгновение получил следующий результат:
Для столбца Статус результат будет выглядеть следующим образом:
Таким образом вы сравниваете столбцы в Excel на совпадения (дубликаты) и различия (уникальные значения). Если вам интересно попробовать этот инструмент, вы можете загрузить ознакомительную версию по ссылке ниже.
Я благодарю вас за чтение и рекомендую вам ознакомиться с другими полезными руководствами, которые у нас есть :)
Читайте также: