Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

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

Чтение этой статьи займёт у Вас около 10 минут. В следующие 5 минут Вы сможете легко сравнить два столбца в Excel и узнать о наличии в них дубликатов, удалить их или выделить цветом. Итак, время пошло!

Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B . Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.

Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B .

Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3 .

В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

Вариант В: два столбца находятся на разных листах (в разных книгах)

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name ” и “Duplicate? ” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate? “, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate , и нажмите ОК .

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

Чтобы снова отобразить все строки столбца А , кликните символ фильтра в столбце В , который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки “Duplicate ” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

Удаление повторяющихся значений из первого столбца

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

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

Нажмите ОК , когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:

Если 2 столбца расположены на одном листе , вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А , сделайте следующее:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

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

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

Для следующих примеров мы заранее скопировали новые данные на тот же лист, где находятся исходные. На рис. 6.1 показано, как данные выглядят в этих двух диапазонах. Обратите внимание, что для упрощения восприятия мы выделили ячейки таблицы 2, отличающиеся от соответствующих ячеек в таблице 1, жирным шрифтом.

Способ 1. Использование значений Истина и Ложь

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

Диапазон E1:G7 будет заполнен значениями Истина (True) — такое же значение, и Ложь (False) — значение отличается.

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

Способ 2. Условное форматирование

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

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Щелкните на кнопке ОК, и все различия будут отформатированы согласно выбранному вами формату. Если вы внесете какие-либо изменения в данные, то при одинаковом содержимом ячеек в обеих таблицах будет восстановлено обычное форматирование.

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

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

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

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

В диспетчере правил выбираем пункт «Создать правило» , а в создании правил выбираем . Теперь мы можем задать формулу «=$B3$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат» .

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

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

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

Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1» , и в меню «Условное форматирование» выбираем пункт «Создать правило…» . В появившемся окошке выбираем , вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.

В нашей формуле используется функция «СЧЁТЕСЛИ» , которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12» , которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.

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

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

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

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

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

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

В этой статье

Сравнение двух таблиц с помощью объединений

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

Подготовка примера данных

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

Access предоставляет несколько способов добавления этих таблиц образец базы данных. Можно ввести данные вручную, скопируйте каждую таблицу в электронную таблицу программы (например, Microsoft Office Excel 2007) и затем импортируйте листы в Access или можно вставлять данные в текстовом редакторе, например Блокнот и затем импортировать данные из результирующего текстовые файлы.

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Если же вы хотите что-то уточнить, я с радостью помогу!
Если требуется выполнить построчный анализ двух столбиков, в третий столбик помещаем любую функцию, о которых шла речь выше («если», либо «совпад»). Действие ее распространяем на всю высоту заполненных колонок. Далее, выделяем третий столбец, щелкаем вкладку «главная», ищем в появившейся группе слово «стили». Откроются «правила выделения столбцов/ячеек».
Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Excel поиск одинаковых значений в двух столбцах

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

Сравнить 2 столбца в excel на совпадения. Как сравнить два столбца в Excel — методы сравнения данных Excel

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

Сравнение двух столбцов на совпадения в Excel

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

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

  1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
  2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
  3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

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

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

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookupКак Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Принцип сравнения данных двух столбцов в Excel

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

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

Первый список — А2:В31 , этот диапазон называется OldList . Второй список — D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.

Начнем с добавления условного форматирования к старому списку.

  1. Выделите ячейки диапазона OldList .
  2. Выберите .
  3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу
  4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
  5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
  6. Нажмите кнопку ОК.

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

  1. Выделите ячейки диапазона NewList .
  2. Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
  3. В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
  4. Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
  5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
  6. Нажмите кнопку ОК.

В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.

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

Как Сравнить Несколько Столбцов в Excel на Совпадения и Выделить Цветом • Hlookup и vlookup

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

Надстройка для сравнения значений в двух диапазонах Excel

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. находить элементы диапазона №1, которых нет в диапазоне №2;

3. находить элементы диапазона №2, которых нет в диапазоне №1;

4. находить элементы диапазона №1, которые есть в диапазоне №2;

5. находить элементы диапазона №2, которые есть в диапазоне №1;

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

7. быстро выделять диапазоны, используя опцию «Ограничить диапазоны», при этом можно выделять целиком строки и столбцы, сокращение выделенного диапазона до используемого производится автоматически;

8. вместо сравнения числовых значений использовать сравнение текстовых значений при помощи опции «Сравнить числа как текст»;

9. сравнивать значения в ячейках диапазона, не учитывая лишние пробелы;

10. сравнивать значения в ячейках диапазона, не учитывая регистр.

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

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

Данные в Экселе обыкновенно сравниваются между строками, между столбцами, со значением, заданным, как эталон. Если имеется надобность сравнить столбцы, можно использовать встроенный функционал, а именно, действия «Совпад» и «Если». И все, что вам потребуется – это Эксель не ранее седьмого года «выпуска».

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

Через мгновение на дисплее вы увидите новую форму, где будут всего два поля: «текст один», «текст два». В них нужно забить, как раз, адреса сравниваемых столбцов (С3, В3), после щелкнуть на привычную клавишу «ОК». В итоге, вы увидите результат со словами «Истина»/«Ложь». В принципе, ничего особо сложного даже для начинающего юзера! Но это далеко не единственный метод. Давайте разберем функцию «Если».

Если требуется выполнить построчный анализ двух столбиков, в третий столбик помещаем любую функцию, о которых шла речь выше («если», либо «совпад»). Действие ее распространяем на всю высоту заполненных колонок. Далее, выделяем третий столбец, щелкаем вкладку «главная», ищем в появившейся группе слово «стили». Откроются «правила выделения столбцов/ячеек».

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

Эксель: условное форматирование

Форматирование условного типа позволит вам не просто сравнить два разных столбика/ячейки/строчки, а и выделить заданным цветом (красным) разные в них данные. То есть мы ищем не совпадения, а разности. Чтобы это получить, действуем так. Выделяем нужные столбики, не трогая их названий, заходим в директорию меню «главная», в ней отыскиваем подраздел «стили».

HLOOKUP и VLOOKUP

Значение, которое нужно отыскать, обозначаем, как «lookup_value». Колонки для поиска вбиваются, как «table array». Номер столбика следует указать, как «сol_index_num». Причем это тот столбец, значение которого совпало, и которое нужно вернуть/исправить. Команда «range lookup» здесь выступает, как добавочная. Она может указать, нужно значение сделать точным, либо приближенным.

Способ обработки значений-дубликатов

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

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

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

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

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Если же вы хотите что-то уточнить, я с радостью помогу!
Пример 2. В таблице содержатся данные о продажах мобильных телефонов (наименование и стоимость). Определить самый продаваемый вид товара за день, рассчитать количество проданных единиц и общую выручку от их продажи.

Сравнить два неодинаковых столбца excel. Как сравнить два столбца в Excel — методы сравнения данных Excel

  1. Выделите ячейки диапазона OldList .
  2. Выберите .
  3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу
  4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
  5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
  6. Нажмите кнопку ОК.

В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.

Оставить отзыв

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