Как сравнить два столбца в excel на больше меньше и выделить цветом

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

Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 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. Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.

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

Сравнение числовых столбцов

Сравним 2 столбца значений. Сравнение будем производить построчно: если значение во втором столбце больше, чем в первом, то оно будет выделено красным, если меньше — то зеленым. Выделять ячейки будем Условным форматированием.

Пусть имеется 2 столбца с числами. Сравним значения второго столбца с первым (см. файл примера ).

Выделите все значения из второго столбца и создайте правило Условного форматирования для выделения меньших значений.

Обратите внимание, что при создании ссылки на ячейку из первого столбца использована относительная адресация на строку, т.е. =$A6 , а не =$A$6 , которая предлагается MS EXCEL по умолчанию. Такая адресация позволяет построчно сравнивать столбцы, в противном случае все значения второго столбца будут сравниваться только со значением из ячейки А6 .

Аналогично создайте правило для значений, которые больше.

Как сравнить и выделить цветом ячейки Excel?

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

Как сравнить столбцы в Excel и выделить цветом их ячейки?

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

Чтобы создать новое пользовательское правило делаем следующее:

1. Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:
4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.

Обратите внимание! В данной формуле мы используем только относительные ссылки на ячейки – это важно. Ведь нам нужно чтобы формула анализировала все ячейки выделенного диапазона.

Как выделить цветом ячейку в Excel по условию?

Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:

1. Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:
4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

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

0;D2>C2)’ >

Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

покупка

Сравнение столбцов Excel: найдите совпадения и различия!

В этом руководстве рассматриваются различные методы сравнения двух столбцов в Excel, что является рутинной задачей для многих пользователей. Будь ты сравнение строк за строками, ячейка за ячейкой, выделение совпадений или выявление различий, в этом руководстве рассматриваются различные сценарии, с которыми вы можете столкнуться. Мы подготовили решения для большинства ситуаций, стремясь улучшить вашу работу с Excel. Примечание: Быстро перейти к нужному контенту можно с помощью правильной таблицы��.

Сравнить два столбца построчно

Ниже приведен набор данных (диапазон B2:C8), в котором мне нужно проверить, совпадают ли имена в столбце B с именами в столбце C в той же строке.

В этой части представлены два примера, объясняющие, как сравнивать два столбца построчно.

• Пример 1. Сравнение ячеек в одной строке
• Пример 2. Сравнение ячеек в одной строке с учетом регистра

Пример 1. Сравнение ячеек в одной строке

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

=B2=C2

Нажмите Enter и перетащите маркер заполнения вниз в ячейку D8. Если формула возвращает TRUE, значения в одной строке двух столбцов полностью одинаковы; если он возвращает FALSE, значения разные.

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

=IF(B2=C2,"Match","No match")

Результат может выглядеть следующим образом:

Пример 2. Сравнение ячеек в одной строке с учетом регистра

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

IF(EXACT(B2,C2), "Match", "Mismatch")

Нажмите Enter , чтобы получить первый результат, затем перетащите дескриптор автозаполнения в ячейку D8.

В приведенной выше формуле вы можете изменить тексты «Совпадение» и «Несоответствие» на свое собственное описание.

Сравнить несколько столбцов в одной строке

Иногда вам может потребоваться сравнить более двух столбцов в одной строке, например, набор данных (диапазон B2: D7), как показано на снимке экрана ниже. В этом разделе перечислены различные методы сравнения нескольких столбцов.

Здесь он разделен на две части, чтобы предоставить подробные инструкции по сравнению нескольких столбцов в одной строке.

• Пример 1. Сравните несколько столбцов и найдите совпадения во всех ячейках в одной строке.
• Пример 2. Сравните несколько столбцов и найдите совпадения в любых двух ячейках в одной строке.

Пример 1. Сравните несколько столбцов и найдите совпадения во всех ячейках в одной строке.

Чтобы найти полные совпадения между столбцами в одной строке, вам может помочь приведенная ниже формула.

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

Нажмите Enter , чтобы получить первый результат сравнения, затем перетащите дескриптор автозаполнения в ячейку E7.

Если ячейки в одной строке совпадают, отображается «Полное совпадение», в противном случае — «Нет».

• Формула сравнивает столбцы без учета регистра.
• В формуле 3 — это количество столбцов, вы можете изменить его по своему усмотрению.

Пример 2. Сравните несколько столбцов и найдите совпадения в любых двух ячейках в одной строке.

Иногда вы хотите узнать, совпадают ли какие-либо два столбца в одной строке, вы можете использовать приведенную ниже формулу IF.

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

Нажмите Enter и перетащите маркер заполнения в ячейку E7.

В этой формуле вам нужно сравнить любую пару ячеек в одной строке. «Совпадение» указывает на совпадение любых двух ячеек, если совпадающих ячеек нет, формула отображает «Нет совпадений», вы можете изменить тексты по своему усмотрению.

• Эта формула не поддерживает нечувствительность к регистру.
• В формуле цифра 2 означает поиск совпадений в любых двух столбцах одной строки. Если вы хотите найти совпадения в трех столбцах в одной строке, измените 2 на 3.

Сравните два или несколько столбцов построчно и выделите совпадения или различия.

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

Есть два примера для сравнения и выделения совпадений и различий.

• Пример 1. Сравните два столбца и выделите полные совпадения во всех ячейках одной строки или в любых двух ячейках одной строки.
• Пример 2. Сравните два столбца и выделите различия в одной строке.

Пример 1. Сравните два столбца и выделите полные совпадения во всех ячейках одной строки или в любых двух ячейках одной строки.

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

1. Выберите диапазон, который вы используете, затем щелкните Главная > Условное форматирование > Новое правило.

2. в Новое правило форматирования Диалог

1. Выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила разделе.
2. Используйте приведенную ниже формулу в Формат значений, где эта формула истинна текстовое окно.

=COUNTIF($B2:$D2, $B2)=3

Если вы хотите сравнить два столбца, измените 3 в формуле на 2; если вы хотите сравнить пять столбцов, измените 3 в формуле на 5.

3. в Формат ячеек диалоговое окно, затем выберите один цвет заливки или другое форматирование ячейки, чтобы выделить строки. Нажмите OK > OK закрыть диалоги.

Теперь будут выделены только те строки, в которых все ячейки совпадают.

Пример 2. Сравните два столбца и выделите различия в одной строке.

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

1. Выберите диапазон, в котором нужно выделить различия строк, и нажмите Главная > Найти и выбрать > Перейти к специальному.

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

Теперь различия строк выбраны.

3. Теперь, не снимая выделения с ячеек, нажмите Главная > Цвет заливки чтобы выбрать один цвет из раскрывающегося меню.

Внимание: этот метод сравнивает ячейки без учета регистра.

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

В этой части набор данных (диапазон B2:C8) показан ниже, и вы хотите найти все значения, которые находятся как в столбце B, так и в столбце C одновременно, или найти значения только в столбце B.

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

• Пример 1. Сравните два столбца в ячейках и отобразите результат сравнения в другом столбце.
• Пример 2. Сравните два столбца в ячейках и выберите или выделите повторяющиеся или уникальные данные с помощью удобного инструмента.
• Пример 3. Сравните два столбца в ячейках и выделите повторяющиеся или уникальные данные.
• Пример 4. Сравните два столбца в ячейках и перечислите точные дубликаты в другом столбце.

Пример 1. Сравните два столбца в ячейках и отобразите результат сравнения в другом столбце.

Здесь вы можете использовать формулу в сочетании с функциями ЕСЛИ и СЧЕТЕСЛИ, чтобы сравнить два столбца и найти значения, которые находятся в столбце B, но отсутствуют в столбце C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

Нажмите Enter и перетащите маркер автозаполнения в ячейку D8.

• Эта формула сравнивает два столбца без учета регистра.
• Вы можете изменить описание «Нет на C» и «Да на C» на другие.

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

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

После бесплатная установка Kutools for Excel, нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки. Затем в диалоговом окне «Выбрать одинаковые и разные ячейки» выполните следующие действия:

1. В Найдите значения в и По словам секции, выберите два столбца отдельно то, с чем раньше сравнивал.
2. Выберите Каждый ряд опцию.
3. Выберите Те же значения or Разные ценности как вам нужно.
4. Укажите, следует ли раскрашивать выбранные значения, и щелкните OK.

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

Если вы установите флажки «Заливка фонового цвета» и «Заливка цвета шрифта», результат будет выглядеть следующим образом:

• Если вы хотите сравнивать с учетом регистра, отметьте флажок Деликатный случай опцию.
• Этот инструмент поддерживает сравнение двух столбцов на разных листах. Нажмите здесь, чтобы получить более подробную информацию о выборе одинаковых и разных ячеек..
• Если вас заинтересовал этот инструмент, нажмите здесь, чтобы загрузить бесплатно с 30-дневной пробной версией.

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

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

1. Выберите два столбца, с которыми вы будете сравнивать, затем щелкните Главная > Условное форматирование > Выделите правила ячеек > Дублирующие значения.

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

3. Нажмите OK. Затем были выделены дубликаты в двух столбцах.

Внимание: Если вы хотите выделить уникальные значения (разницы) в двух столбцах, выберите Уникальное Произведение в левом раскрывающемся списке на шаге 2.

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

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

1. Включите лист, на котором вы хотите сравнить два столбца, затем нажмите Alt + F11 клавиши для отображения Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули в Microsoft Visual Basic для приложений окно.

3. Затем скопируйте и вставьте приведенный ниже код в новый пустой скрипт модуля.

VBA: список дубликатов в соседнем столбце после сравнения двух столбцов

Sub ExtendOffice_FindMatches() 'UpdatebyKutools Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range Dim xIntSR, xIntER, xIntSC, xIntEC As Integer On Error Resume Next SRg: Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8) If xRgC1 Is Nothing Then Exit Sub If xRgC1.Columns.Count <> 1 Then MsgBox "Please select single column" GoTo SRg End If SsRg: Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8) If xRgC2 Is Nothing Then Exit Sub If xRgC2.Columns.Count <> 1 Then MsgBox "Please select single column" GoTo SsRg End If Set xWs = xRg.Worksheet For Each xRgF1 In xRgC1 For Each xRgF2 In xRgC2 If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value Next xRgF2 Next xRgF1 End Sub 

4. Нажмите F5 ключ для запуска кода, одно за другим появляются два диалоговых окна, в которых вы можете выбрать два столбца по отдельности. Затем нажмите OK > OK.

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

Совпадения были автоматически перечислены в правом столбце из двух столбцов.

Сравните два списка и извлеките соответствующие данные.

Здесь мы представляем два разных сценария сравнения двух списков и извлечения данных.

• Пример 1. Сравните два столбца и извлеките точно совпадающие данные.
• Пример 2. Сравните два столбца и извлеките данные частичного соответствия.

Пример 1. Сравните два столбца и извлеките точно совпадающие данные.

Например, есть две таблицы, теперь вы хотите сравнить столбец B и столбец E, затем найти относительные цены из столбца C и вернуть их в столбец F.

Здесь представлены некоторые полезные формулы для решения этой задачи.

В ячейке F2 (ячейка, в которую вы хотите поместить возвращаемое значение) используйте одну из следующих формул:

=VLOOKUP(E2,$B$2:$C$8,2,0)

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

Нажмите Enter ключ, и первое значение было найдено. Затем перетащите дескриптор автозаполнения в ячейку F6, все значения были извлечены.

• В формулах не поддерживается чувствительность к регистру.
• Цифра 2 в формулах означает, что вы нашли совпадающие значения во втором столбце массива таблицы.
• Если формулы не могут найти относительное значение, возвращается значение ошибки #Н/Д.

Если вы запутались с формулами, вы можете попробовать удобный инструмент — Формула Помощник of Kutools for Excel, который содержит несколько формул для решения большинства задач в Excel. С его помощью вам нужно только выбрать диапазон, но не нужно помнить, как используются формулы. Скачайте и попробуйте прямо сейчас!

Пример 2. Сравните два столбца и извлеките данные частичного соответствия.

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

В ячейке F2 (ячейка, в которую вы хотите поместить возвращаемое значение) используйте одну из следующих формул:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

Нажмите Enter ключ, и первое значение было найдено. Затем перетащите дескриптор автозаполнения в ячейку F6, все значения были извлечены.

• В формулах не поддерживается чувствительность к регистру.
• Цифра 2 в формулах означает, что вы нашли совпадающие значения во втором столбце массива таблицы.
• Если формулы не могут найти относительное значение, возвращается значение ошибки #Н/Д.
• * в формуле является подстановочным знаком, который используется для обозначения любого символа или строки.

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

Предположим, что есть два столбца, столбец B длиннее, а столбец C короче, как показано ниже. По сравнению со столбцом B, как найти недостающие данные в столбце C?

• Пример 1. Сравните два столбца и найдите недостающие точки данных.
• Пример 2. Найдите недостающие точки данных и перечислите их в другом столбце (с помощью удобного инструмента).
• Пример 3. Сравните два столбца и перечислите недостающие данные ниже.

Пример 1. Сравните два столбца и найдите недостающие точки данных.

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

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

Нажмите Enter , затем перетащите маркер автозаполнения на ячейку D10. Теперь, если данные находятся как в столбце B, так и в столбце C, формула возвращает FALSE, если данные находятся только в столбце B, но отсутствуют в столбце C, формула возвращает TRUE.

Внимание: две приведенные выше формулы сравнивают данные без учета регистра.

Пример 2. Найдите недостающие точки данных и перечислите их в другом столбце (с помощью удобного инструмента).

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

После установки Kutools for Excel, нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки. Затем в диалоговом окне «Выбрать одинаковые и разные ячейки» выполните следующие действия:

1. В Найдите значения раздел, выберите более длинный столбец, содержащий полный список.
2. В По словам раздел, выберите более короткий столбец, в котором отсутствуют некоторые данные.
3. Выберите Каждый ряд опцию.
4. Выберите Разные ценности вариант. Нажмите OK.

Появится диалоговое окно, чтобы напомнить вам количество недостающих данных, нажмите OK чтобы закрыть это. Затем были выбраны недостающие данные.

Теперь вы можете нажать Ctrl + C клавиши, чтобы скопировать выбранные недостающие данные, и вставьте их, нажав Ctrl + V клавиши под более коротким столбцом или другой новый столбец по мере необходимости.

• Отметив Без учета регистра опция в диалоговом окне «Выбрать одинаковые и разные ячейки» сравнивает два столбца с учетом регистра.
• Этот инструмент поддерживает сравнение двух столбцов на разных листах. Нажмите здесь, чтобы получить более подробную информацию о выборе одинаковых и разных ячеек..
• Если вас заинтересовал этот инструмент, нажмите здесь, чтобы загрузить бесплатно с 30-дневной пробной версией.

Пример 3. Сравните два столбца и перечислите недостающие данные ниже.

Если вы хотите перечислить недостающие данные под более коротким столбцом после сравнения двух столбцов, вам может помочь формула массива INDEX.

В ячейке ниже более короткого столбца, предположим ячейку C7, введите следующую формулу:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

Нажмите Shift + Ctrl + Enter чтобы получить первые отсутствующие данные, затем перетащите дескриптор автозаполнения вниз, пока он не вернет значение ошибки # Н / Д.

Затем вы можете удалить значение ошибки, и все недостающие данные будут перечислены под более коротким столбцом.

Внимание: Эта формула сравнивает ячейки без учета регистра.

Сравнить два столбца с подстановочным знаком

Предположим, что это список данных в столбце B, и вы хотите подсчитать ячейки, содержащие «Apple» или «Candy» в столбце D, как показано ниже:

Чтобы подсчитать, содержит ли ячейка одно или несколько значений, вы можете использовать формулу с подстановочными знаками для решения этой проблемы.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

Нажмите Shift + Ctrl + Enter , чтобы получить первую проверку, затем перетащите дескриптор автозаполнения в ячейку F8.

Теперь, если связанная ячейка содержит одно или несколько значений в столбце D, в результате отображается число 1; если он не содержит никакого значения в столбце D, он возвращает 0.

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

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

=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))

Сравните два столбца (даты), если они больше или меньше

Если есть два столбца дат, как показано на скриншоте ниже, вы можете сравнить, какая дата находится позже в той же строке.

• Пример 1. Сравнение двух столбцов, если они больше или меньше
• Пример 2. Сравните два столбца, если они больше или меньше, а затем отформатируйте их.

Пример 1. Сравнение двух столбцов, если они больше или меньше

Вы можете использовать простую формулу, чтобы быстро определить, является ли дата 1 более поздней, чем дата 2 в каждой строке.

=IF(B2>C2,"Yes","No")

Нажмите Enter чтобы получить первый сравниваемый результат, затем перетащите дескриптор автозаполнения в ячейку C6, чтобы получить все результаты.

• В Excel даты хранятся в виде числовых серий, фактически это числа. Таким образом, вы применяете формулу для прямого сравнения дат.
• Если вы хотите сравнить, находится ли дата 1 раньше даты 2 в каждой строке, измените символ > на < в формуле.

Пример 2. Сравните два столбца, если они больше или меньше, а затем отформатируйте их.

Если вы хотите выделить ячейки в столбце «Дата 1», если они больше даты 2, вы можете использовать функцию условного форматирования в Excel.

1. Выберите даты в столбце B (Date1), затем щелкните Главная > Условное форматирование > Новое правило.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем введите формулу

 =$B2>$C2

в текстовое поле Отформатируйте значения, в которых эта формула верна.

Если вы хотите выделить ячейки в столбце B, размер которых меньше, чем в столбце C, используйте формулу

3. Нажмите Формат Нажмите кнопку, чтобы открыть диалоговое окно «Формат ячеек», затем выберите нужный тип формата. Нажмите OK > ОК.

4. Затем были выделены ячейки в столбце Date1, которые больше, чем ячейки в столбце Date2.

Сравните два столбца и подсчитайте совпадения или разницу.

Ниже приведен набор данных для сравнения и подсчета совпадений или различий.

Формула СУММПРОИЗВ может быстро подсчитать совпадения в двух столбцах.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

Нажмите Enter ключ для получения результата.

Внимание: формула подсчитывает ячейки без учета регистра.

Дополнительные методы подсчета совпадений и различий можно найти на этой странице: Подсчитайте все совпадения / дубликаты между двумя столбцами в Excel

Сравнить два диапазона

Теперь вы знаете, как сравнить два столбца после прочтения вышеуказанных методов. Однако в некоторых случаях вам может потребоваться сравнить два диапазона (две серии с несколькими столбцами). Вы можете использовать вышеуказанные методы (формулы или условное форматирование), чтобы сравнить их столбец за столбцом, но здесь представлен удобный инструмент — Kutools for Excel может быстро решить эту задачу в разных случаях без формулы.

• Пример 1. Сравнение двух диапазонов по ячейкам
• Пример 2. Сравнение двух диапазонов, если данные расположены в одном и том же порядке.

Пример 1. Сравнение двух диапазонов по ячейкам

Вот два диапазона, которые необходимо сравнить по ячейкам, вы можете использовать Выберите одинаковые и разные ячейки полезности Kutools for Excel справиться с этим.

После бесплатной установки Kutools for Excel, нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки. Затем в появившемся диалоговом окне «Выбрать одинаковые и разные ячейки» выполните следующие действия:

1. В Найдите значения в В разделе выберите диапазон, в котором вы хотите найти совпадения или различия после сравнения двух диапазонов.
2. В По словам раздел, выберите другой диапазон, используемый для сравнения диапазона.
3. In на основании раздел, выбрать Однокамерная.
4. В Найдите раздел, выберите тип ячеек, которые вы хотите выделить или выделить.
5. В Обработка результатов разделе вы можете выделить ячейки цветом заливки фона или цветом шрифта, если подсвечивать не нужно, не ставьте галочки. Нажмите OK.

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

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

Заметки

• Если вы хотите сравнить два диапазона по строкам, вы также можете применить функцию «Выбрать одинаковые и разные ячейки», но в этом случае выберите Каждый ряд опцию.
  
  
• Отметив Без учета регистра опция в диалоговом окне «Выбрать одинаковые и разные ячейки» сравнивает два столбца с учетом регистра.
• Этот инструмент поддерживает сравнение двух столбцов на разных листах. Нажмите здесь, чтобы получить более подробную информацию о выборе одинаковых и разных ячеек..
• Если вас заинтересовал этот инструмент, нажмите здесь, чтобы загрузить бесплатно с 30-дневной пробной версией.

Пример 2. Сравнение двух диапазонов, если данные расположены в одном и том же порядке.

Предположим, диапазон F2: H7 является моделью, теперь вы хотите определить, находятся ли данные в диапазоне B2: D7 в правильном порядке в соответствии с диапазоном F2: H7.

В этом случае Сравнить ячейки of Kutools for Excel могу помочь тебе.

После бесплатной установки Kutools for Excel, нажмите Кутулс > Сравнить ячейки. Затем в диалоговом окне «Сравнить ячейки» установите, как показано ниже:

1. Выберите два диапазона в Найдите значения в и По словам коробки отдельно.
2. Выберите тип ячейки, который вы хотите выделить в Найдите .
3. Выберите тип выделения в Обработка результатов раздел. Нажмите ОК.

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

• Отметив Без учета регистра опция будет сравнивать две ячейки с учетом регистра.
• Нажмите здесь, чтобы получить более подробную информацию о сравнении ячеек. Если вас заинтересовал этот инструмент, нажмите здесь, чтобы загрузить бесплатно с 30-дневной пробной версией.

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

Лучшие инструменты для офисной работы

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

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!