Как сравнить три столбца в excel на совпадения

Как сравнить три столбца в Excel (с примером)

Вы можете использовать следующую базовую формулу для сравнения трех столбцов в Excel:

=IF(AND( B2 = C2 , C2 = D2 ),"Equal","Not Equal") 

Эта конкретная формула сравнивает значения в ячейках B2 , C2 и D2 .

Если все значения равны, формула возвращает Equal.В противном случае возвращается Not Equal .

В следующем примере показано, как использовать эту формулу на практике.

Пример: сравнение трех столбцов в Excel

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

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

=IF(AND( B2 = C2 , C2 = D2 ),"Equal","Not Equal") 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Формула возвращает Not Equal , поскольку не все три имени в первой строке совпадают.

Затем мы можем перетащить эту формулу из ячейки E2 в оставшиеся ячейки в столбце E, чтобы проверить совпадения в каждой строке:

Обратите внимание, что формула возвращает значение « Равно » только для тех строк, в которых значения ячеек равны в каждом из трех столбцов.

Вы также можете применить условное форматирование к строкам, в которых все три значения ячеек равны, выделив диапазон ячеек E2:E11 , затем нажав кнопку « Условное форматирование » на вкладке «Главная», затем щелкнув « Выделить правила ячеек» и нажав «Равно »:

В появившемся новом окне введите « Равно » в поле и выберите цвет заливки, затем нажмите « ОК »:

Строки с совпадающими значениями во всех трех столбцах теперь будут окрашены зеленым цветом:

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Поиск отличий в двух списках

Типовая задача, возникающая периодически перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) : Число несовпадений можно посчитать формулой:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20)) Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter. Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
• и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично 😉

Ссылки по теме

• Выделение дубликатов в списке цветом
• Сравнение двух диапазонов с помощью надстройки PLEX
• Запрет ввода повторяющихся значений

Как сравнить три и более столбцов с эталоном?

Как построчно выделить цветом значение, которое меньше эталона?

• Вопрос задан более трёх лет назад
• 217 просмотров

Комментировать
Решения вопроса 0
Ответы на вопрос 1

Бизнес-аналитика, фин. моделирование, дашборды
Погуглите «условное форматирование»
Ответ написан более трёх лет назад
Комментировать
Нравится 3 Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

• Google Sheets

Скрип для записи последних действий в документе?

• 1 подписчик
• вчера
• 45 просмотров

• Google Sheets
• +1 ещё

Как настроить триггер на изменение в гугл таблицах?

• 1 подписчик
• 28 апр.
• 34 просмотра

Сравнивайте столбцы друг с другом и вычисляйте процент соответствия

При сравнении нескольких сопоставимых объектов в Excel таблицах, данные часто организуют по столбцам, чтобы было удобно сравнивать характеристики этих объектов построчно. Например, модели автомобилей, телефоны, экспериментальные и контрольные группы, ряд магазинов торговой сети и др. При большом числе строк визуальный анализ не может быть достоверным. Функции ВПР, ИНДЕКС, ПОИСКПОЗ (VLOOKUP, INDEX, MATCH) удобны для сравнения данных по ячейкам и не дают общей картины. А как выяснить, насколько в целом столбцы схожи между собой? Идентичны ли они?

Инструмент «Сопоставить столбцы» позволяет сопоставить данные и увидеть общую картину:

Сравнить два и более столбца друг с другом
Сравнить данные с эталонными значениями
Вычислить точный процент соответствия
Наглядно представить результат в сводной таблице

Перед началом работы добавьте «Сопоставить столбцы» в Excel

«Сопоставить столбцы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как сравнить два столбца и более друг с другом и вычислить процент соответствия

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

Выберите столбцы для сравнения.
Нажмите кнопку Сопоставить столбцы на панели XLTools Выберите Сопоставить столбцы между собой .
Отметьте Столбцы содержат заголовки , если это так.

Отметьте Показывать процент соответствия , чтобы степень соответствия отображалась в процентах. В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).

Укажите, куда следует поместить результат: на новый или на существующий лист.
Нажмите ОК Готово, результат представлен в сводной таблице.

Совет: чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата Кликните по пиктограмме Экспресс-анализа Примените «Цветовую шкалу».

Прочтение результата: прототипы Тип 1 и Тип 3 практически идентичны, показатель соответствия на 99% говорит о том, что 99% их параметров в строках совпадают. Тип 2 и Тип 4 схожи менее всего — их параметры совпадают только на 30%.

Как сравнить столбцы с эталонными значениями и вычислить степень соответствия

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

Выберите столбцы для сравнения. Напр., столбцы с данными прототипов.
Нажмите кнопку Сопоставить столбцы на панели XLTools.

Выберите Сопоставить с диапазоном эталонных столбцов Выберите столбцы эталонных значений. Напр., столбцы со стандартами.

Отметьте Столбцы содержат заголовки , если это так.

Отметьте Показывать процент соответствия , чтобы степень соответствия отображалась в процентах. В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).

Укажите, куда следует поместить результат: на новый или на существующий лист.
Нажмите ОК Готово, результат представлен в сводной таблице.

Совет: чтобы было проще интерпретировать результат, примените к нему условное форматирование:
Выберите сводную таблицу результата Кликните по пиктограмме Экспресс-анализа Примените «Цветовую шкалу».

Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 – 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.

Когда надстройка «Сопоставить столбцы» наиболее полезна

Надстройка построчно сканирует ячейки и вычисляет процент одинаковых значений в столбцах. XLTools «Сопоставить столбцы» не подходит для обычного сравнения значений в ячейках — она не предназначена для поиска дубликатов или уникальных значений.

Надстройка «Сопоставить столбцы» имеет другое назначение. Её главная задача — выяснить, насколько, в целом, наборы данных (столбцы) схожи или отличны. Надстройка помогает с анализом большого объёма данных, когда вам нужно посмотреть шире, на макро-уровне, напр. ответить на такие вопросы: