Как сравнить три столбца в 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 таблицах, данные часто организуют по столбцам, чтобы было удобно сравнивать характеристики этих объектов построчно. Например, модели автомобилей, телефоны, экспериментальные и контрольные группы, ряд магазинов торговой сети и др. При большом числе строк визуальный анализ не может быть достоверным. Функции ВПР, ИНДЕКС, ПОИСКПОЗ (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 «Сопоставить столбцы» не подходит для обычного сравнения значений в ячейках — она не предназначена для поиска дубликатов или уникальных значений.

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

Сравнение данных 3-х ячеек и вывод данных в 4-ую

Всем доброго времени суток! люди добрый помогите плиз составить расчет.
Пр: в 3 ячейках существуют числа.
A1=7; B1=4; C1=11
в D1 необходимо поставить условие таким образом,
чтоб при «A1 при «A1C1; A1>B1 A1 при «A1>B1 и A1>C1» в D1 подставлялась ‘3’.

как составить 1 формулу с соблюдением всех параметров?

P.S. Я себе уже все волосы вырвал

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

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

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

Сравнение данных из ячеек DataGridView на двух разных формах
Есть 2 формы, на одной есть DataGridView и на второй — DataGridView. Нужно сравнить данные из.

Сравнение (поиск) ячеек из столбца и копирование к ним дополнительных данных
Добрый день! У меня к сожалению пока посредственные знания VBA, но я уже примерно попытался.

15141 / 6414 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999

Сообщение было отмечено alyaska213 как решение

Решение

alyaska213, а в чем проблема? И при чем тут VBA?

=ЕСЛИ(И(A1C1);И(A1>B1;A1B1;A1>C1);3)))

=(A1>B1)+(A1>C1)+1

Регистрация: 19.10.2015
Сообщений: 13
Спасибо огромное! VBA мимо глаз пролетело.
И я понял свой косяк.
Регистрация: 22.03.2019
Сообщений: 3

Добрый день!
Помогите пожалуйста с формулой.
У меня похожие условия, сравниванием 3 ячейки, нужно выбрать одинаковые и вынести информацию в четвертую, если найдется 2 совпадения, в пятую — если 3 совпадения. Данные в ячейках от 1 до 9.
Благодарю.

15141 / 6414 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999
tam_tam, жаль, что файла-примера нет. Как понял, сравниваем А1, В1, С1.

в D1: =ВЫБОР((A1=B1)+(A1=C1)+(B1=C1)+1;"";ЕСЛИ((A1=B1)+(A1=C1);A1;B1);;"") в Е1: =ЕСЛИ((A1=C1)+(A1=C1)+(B1=C1)=3;A1;"")

Регистрация: 22.03.2019
Сообщений: 3

Отлично. все работает.
Можно еще подключить данные из четвертой ячейки?
файл прицепом.
Вложения

для форума.xlsx (9.0 Кб, 16 просмотров)

15141 / 6414 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999

tam_tam, среди 4 значений есть еще вариант 2+2, его как считать — как 2 или как отдельную категорию?
Вообще, одной формулой видимо не получится, надо использовать промежуточные столбцы или функцию VBA — Вас что больше устроит?

Регистрация: 22.03.2019
Сообщений: 3

Про вариант 2+2 я тоже думала. а, что если вместо значения будет подставляться «Есть»? Возможно такое?
В принципе, устроит любое решение.

15141 / 6414 / 1731
Регистрация: 24.09.2011
Сообщений: 9,999

Сообщение от tam_tam

устроит любое решение
Тогда просто вид комбинации, по покерной терминологии

=ВЫБОР(ОКРУГЛ(СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:D2;A2:D2)););"каре";ЕСЛИ(СЧЁТЕСЛИ(A2:D2;МИН(A2:D2))=2;"две пары";"тройка");"пара";"-")

Формула основана на известной формуле для определения числа уникальных в диапазоне: <СУММ(1/СЧЁТЕСЛИ(диап;диап))>
Вложения

для форума (9).xlsx (9.2 Кб, 11 просмотров)

Регистрация: 22.09.2020
Сообщений: 2

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

Необходимо:
1. Если текст в ячейке столбца A совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С есть текст, то выводить в соседнюю ячейку столбца D «текст 1»;
2. Если текст в ячейке столбца A совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С НЕТ текста, то выводить в соседнюю ячейку столбца D «текст 2»;
3. Если текст в ячейке столбца A НЕ совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С есть текст, то выводить в соседнюю ячейку столбца D «текст 3»;
4. Если текст в ячейке столбца A НЕ совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С НЕТ текста, то выводить в соседнюю ячейку столбца D «текст 4».

Буду очень признателен за помощь.
Спасибо

Регистрация: 22.09.2020
Сообщений: 2

Написал примитивный цикл под вышеуказанную задачу, но он не работает. ((

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

Private Sub Find_me_Click() Dim i, j As Integer Dim A, B, C As Variant For i = 1 To 1000 A = Cells(2 + i, 2) B = Cells(2 + i, 3) For j = 1 To 1000 C = Cells(2 + j, 5) If A = C And B <> "" Then Cells(2 + j, 6) = "Текст 1" Cells(2 + j, 6).Font.ColorIndex = 10 ElseIf A = C And B = "" Then Cells(2 + j, 6) = "Текст 2" Cells(2 + j, 6).Font.ColorIndex = 11 ElseIf A <> C And B <> "" Then Cells(2 + j, 6) = "Текст 3" Cells(2 + j, 6).Font.ColorIndex = 3 ElseIf A <> C And B = "" Then Cells(2 + j, 6) = "Текст 4" Cells(2 + j, 6).Font.ColorIndex = 4 End If Next j Next i End Sub

Как в Эксель сравнить два столбца на совпадения и найти расхождения

Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.

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

Одна из особенностей приложения — возможность в Эксель сравнить столбцы (два и более) на факт отличий и различий, а после вывести результаты в виде подсвечивания цветом. Ниже рассмотрим, как правильно сделать эту работу для разного количества столбцов.

Два

При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.

Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:

• =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
• =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).

При этом в таблице выводится информация о наличии совпадений или отличий.

Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Альтернативный вариант

Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.

Больше двух

По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.

Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).

Поиск совпадений в двух и более столбцах

Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).

В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).

В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».

Как вывести результат в Эксель

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

Для этого в Эксель сделайте следующее:

• Выделите вертикальные секции с данными, которые нужно сравнить.
• Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
• Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».

• В появившемся диалоговом окне выберите слева пункт «Повторяющиеся», а в правом списке укажите, каким цветом будут выделяться данные. Жмите на кнопку «ОК».
• После этого в выделенной колонке подсвечиваются цветом совпадения.

При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:

1. С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
2. Выделите область с информацией в дополнительной колонке.
3. В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
4. Кликните на «Повторяющиеся значения».
5. Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.

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