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

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

1 Сравнение с помощью простого поиска 

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

1. Перейти на главную вкладку табличного процессора. 
2. В группе «Редактирование» выбрать пункт поиска. 
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй. 
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения. 

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

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

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

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

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

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

=ЕСЛИ(A2=B2; «Совпадают»; «»)

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

=ЕСЛИ(A2<>B2; «Не совпадают»; «»)

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

=ЕСЛИ(A2=B2; «Совпадают»; «Не совпадают»)

или

=ЕСЛИ(A2<>B2; «Не совпадают»; «Совпадают»)

Пример результата вычислений может выглядеть так:

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

=ЕСЛИ(СОВПАД(A2,B2); «Совпадает»; «Уникальное»)

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

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

• Найти строки с одинаковыми значениями во всех столбцах таблицы;
• Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

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

Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel ЕСЛИ и И. Формула для определения совпадений будет следующей:

=ЕСЛИ(И(A2=B2;A2=C2); «Совпадают»; » «)

Если в нашей таблице очень много столбцов, то более просто будет использовать функцию СЧЁТЕСЛИ в сочетании с ЕСЛИ:

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;»Совпадают»;» «)

В формуле в качестве «5» указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

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

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);»Совпадают»;» «)

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; «Уникальная строка»; «Не уникальная строка»)

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; «Нет совпадений в столбце B»; «Есть совпадения в столбце В»)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10). Это позволит ускорить работу формулы.

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

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

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
• На вкладке «Главная» на Панели инструментов нажимаем на пункт меню «Условное форматирование» -> «Правила выделения ячеек» -> «Повторяющиеся значения»;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке «Повторяющиеся», в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку «ОК»:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

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

=A2&B2&C2&D2

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

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
• Затем, на вкладке «Главная» на Панели инструментов нажмем на пункт «Условное форматирование» -> «Создать правило»:

• В диалоговом окне «Создание правила форматирования» кликните на пункт «Использовать формулу для определения форматируемых ячеек» и в поле «Форматировать значения, для которых следующая формула является истинной» вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

• Не забудьте задать формат найденных дублированных строк.

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

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

​Смотрите также​​ ODBC Excel] Несоответствие​ эксель просто с​Задача 1​​Ольчик30​​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​ те же действия​​ только шапка. Это​​ и щелкаем по​​ во второй таблице,​​ столбца, в котором​​ применения условного форматирования​.​<>​ кликаем по первой​Подробнее смотрите такие​ чтобы в отпуске​Есть несколько способов,​ типа выражений​ ума сходит. А​Есть таблица с​
​: Добрый день!​​или в английском варианте​ только имя присвойте​​ значит, что разница​ значку​
​ но отсутствуют в​ будет производиться подсчет​ для выполнения поставленной​Как видим, после этого​) и выделяем сравниваемый​
​ ячейке колонки, которую​ формулы в статье​ не было два​как сравнить два столбца​Макрописец​ мне как раз​ номерами: в столбце​Есть 2 столбца​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ ​ – Таблица_2. А​
​ составляет одну строку.​«Вставить функцию»​ ​ первой, выводились отдельным​
​ количества совпадений. Далее​ задачи. Как и​ несовпадающие значения строк​ диапазон второй области.​ мы сравниваем, во​ «Функция «СЧЕТЕСЛИ» в​ сотрудника сразу или​ в Excel на​
​:​ необходимо обрабатывать большие​ А — старые​ с данными, в​​Если в результате получаем​ диапазон укажите C2:C15​ Поэтому дописываем в​.​ списком.​ щелкаем по пиктограмме​ предыдущие варианты, он​ будут подсвечены отличающимся​
​ Далее обворачиваем полученное​ второй таблице. Получилось​ Excel».​ даты учебы, оплаты​ совпадения​leg​ объемы информации. Или​ номера, в столбце​ первом столбце данных​ ноль — списки​ – соответственно.​ поле​Открывается​
​Прежде всего, немного переработаем​
​«Вставить функцию»​ требует расположения обоих​ ​ оттенком. Кроме того,​
​ выражение скобками, перед​ выражение следующего типа:​Этот способ сравнения​ счетов, т.д. не​,​,​ я слишком много​ B — новые​ ​ больше , чем​ ​ идентичны. В противном​
​Полезный совет! Имена диапазонов​«Номер строки»​Мастер функций​ нашу формулу​.​ сравниваемых областей на​ как можно судить​ которыми ставим два​=A2=D2​ можно применить при​ пересекались. Об этом​
​как сравнить две таблицы​Файл такой же,​ требую от эксель​
​ номера, номера в​ во втором.Необходимо узнать,​​ случае — в​​ можно присваивать быстрее​значение​. Переходим в категорию​СЧЁТЕСЛИ​Происходит запуск​ одном листе, но​ из содержимого строки​ знака​Хотя, конечно, в каждом​ сравнении двух прайсов.​ способе читайте в​ Excel​ как и в​ и он под​ обоих столбцах могут​ какие данные во​ них есть различия.​ с помощью поля​
​«-1»​«Статистические»​, а именно сделаем​Мастера функций​ в отличие от​ формул, программа сделает​«-»​
​ конкретном случае координаты​
​ Смотрите статью «Как​ статье «Как сравнить​,​ стартовом посте этой​ такие нужды не​
​ повторятся. Необходимо сравнить​
​ втором столбце отсутствуют.​​ Формулу надо вводить​ имен. Оно находится​ ​без кавычек.​​и производим выбор​ её одним из​
​. Переходим в категорию​ ранее описанных способов,​ активной одну из​. В нашем случае​ будут отличаться, но​ сделать прайс-лист в​ даты в Excel».​списки​ темы? Если нет​​ подходит?​ на повторы столбец​ Думаю функция «ВПР».​ как формулу массива,​ левее от строки​В поле​ наименования​ аргументов оператора​«Статистические»​ условие синхронизации или​ ячеек, находящуюся в​
​ получилось такое выражение:​ суть останется одинаковой.​ Excel».​Как посчитать разницу​,​ — сделайте пример​Manyasha​ В и А​ Но куда что​ т.е. после ввода​ формул. Просто выделяйте​ ​«Массив»​
​«НАИМЕНЬШИЙ»​ ​ЕСЛИ​
​. Находим в перечне​ сортировки данных не​ указанных не совпавших​—(A2:A7<>D2:D7)​Щелкаем по клавише​
​Довольно часто перед пользователями​
​ дат, стаж, возраст,​даты в Excel​ со СТРУКТУРОЙ данных​:​
​ и в столбец​ подставлять- не пойму.​ формулы в ячейку​ диапазоны ячеек, а​указываем адрес диапазона​. Щелкаем по кнопке​. Для этого выделяем​ наименование​ будет являться обязательным,​ строках.​Щелкаем по кнопке​Enter​ Excel стоит задача​
​ как прибавить к​​. Не только сравнить​​ ИДЕНТИЧНОЙ структуре данных​
​leg​​ С вывести только​​ Нужно создать третий​ жать не на​ в поле имен​ значений второй таблицы.​«OK»​ первую ячейку, в​«СЧЁТЕСЛИ»​ что выгодно отличает​Произвести сравнение можно, применив​«OK»​
​, чтобы получить результаты​
​ сравнения двух таблиц​​ дате число, т.д.,​ ​ столбцы в Excel,​​ в Вашем файле​, массивные формулы на​ те номера из​ столбец и туда​Enter​
​ вводите соответствующее имя​ При этом все​.​ которой расположен оператор​. После его выделения​ данный вариант от​ метод условного форматирования.​.​
​ сравнения. Как видим,​ или списков для​ смотрите в статье​ но и выделить​
​ и данными вида​ таких объемах всегда​ столбца В, которых​ прописать какую-то функцию?Подскажите​, а на​ для диапазона и​

excel-office.ru

Методы сравнения таблиц в Microsoft Excel

​ координаты делаем абсолютными,​Функция​СЧЁТЕСЛИ​ щелкаем по кнопке​ ранее описанных.​ Как и в​Оператор производит расчет и​ при сравнении первых​ выявления в них​ «Дата в Excel.​ разницу цветом шрифта,​ идентичного данным в​ тормозят.​ нет в столбце​ пожалуйста.​Ctrl+Shift+Enter​ нажмите Enter.​ то есть, ставим​НАИМЕНЬШИЙ​. В строке формул​«OK»​Производим выделение областей, которые​ предыдущем способе, сравниваемые​ выводит результат. Как​ ячеек обоих списков​ отличий или недостающих​ Формула» здесь.​

​ ячеек, т.д. Один​ Вашем файле и​

​Можно написать макрос,​ А. Т.е. если​Vlad999​

Способы сравнения

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

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

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

​Выполняем переход во вкладку​ на одном рабочем​ случае результат равен​«ИСТИНА»​ справляется с этой​Функцию выделения ячеек можно​

Способ 1: простая формула

​ две таблицы на​ сообщении.​ можно формулой​ нет номера, который​ на листе где​ надо что сделать,​ двух списков в​ описанным нами способом.​ для вывода указанного​«ЕСЛИ»​ оператора​ под названием​ листе Excel и​ числу​, что означает совпадение​ задачей по своему,​ вызвать клавишей F5.​ одном мониторе одновременно,​SLAVICK​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$22;A2)>0;1;0)​ есть в столбце​ данных больше формулу​ то подойдет другой​ Excel. Нам нужно​Жмем на кнопку​ по счету наименьшего​без кавычек и​

​СЧЁТЕСЛИ​«Главная»​ быть синхронизированными между​«1»​ данных.​ но чаще всего​ В появившемся окне​ описан в статье​

1. ​: Спасибо конечно, но​отметить ячейки, удовлетворяющие​ А, то его​ ВПР(что ищем;таблица;1;0), где​​ быстрый способ: выделите​​ получить следующий результат:​«OK»​ значения.​ открываем скобку. Далее,​. Как видим, наименования​​. Делаем щелчок по​​ собой.​, то есть, это​Теперь нам нужно провести​ на решение указанного​ «Переход» нажимаем кнопку​ «Сравнение таблиц Excel».​

   ​ это файл​

   

   ​ условиям (есть в​ выводим в столбец​ будет ответ Н/Д​ оба столбца и​

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

   

3. ​ «Выделить…».​Здесь рассмотрим,​krosav4ig​ А, нет в​ С, если же​ значит этого нет.​ нажмите клавишу​Позиции, которые есть в​После вывода результат на​«Массив»​ было работать, выделяем​ окне соответствуют названиям​«Условное форматирование»​

   ​ табличную область будем​ сравниваемых списках было​ с остальными ячейками​ большое количество времени,​Второй способ.​как сравнить столбцы в​(10-й пост), я​​ В), а потом​​ в обоих столбцах​ если не понятно​F5​ Таблице_1, но нет​ экран протягиваем функцию​следует указать координаты​ в строке формул​ аргументов.​. В активировавшемся списке​ считать основной, а​

   

4. ​ найдено одно несовпадение.​ обеих таблиц в​ так как далеко​Можно в​ Excel​ только добавил одну​ отсортировать по убыванию.​ есть одинаковые номера,​ приложите файл покажем​, затем в открывшемся​ в Таблцие_2 будут​​ с помощью маркера​​ диапазона дополнительного столбца​ значение​Устанавливаем курсор в поле​ выбираем позицию​​ в какой искать​​ Если бы списки​

   

5. ​ тех колонках, которые​ не все подходы​Excel сравнить и выделить​, выделить разницу цветом,​ строку​ Отсортировать, кстати, еще​ то в столбец​ на нем. Можно​​ окне кнопку​​ отображаться зеленым цветом.​

   

6. ​ заполнения до конца​​«Количество совпадений»​​«ЕСЛИ»​​«Диапазон»​​«Правила выделения ячеек»​​ отличия. Последнее давайте​​ были полностью идентичными,​​ мы сравниваем. Но​​ к данной проблеме​

   

7. ​ цветом​​ символами, т.д.​​:​ можно и по​ С их выводить​ с помощью УФ​Выделить (Special)​ В тоже время​ столбца вниз. Как​, который мы ранее​

   ​и жмем по​

   ​. После этого, зажав​. В следующем меню​ будем делать во​ то результат бы​ можно просто провести​ являются рациональными. В​ячейки с разными данными​Например, несколько магазинов​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Me.Connections(«запрос»).Refresh​

   ​ цвету (УФ в​​ не надо, так​​ выделить эти данные.​-​ позиции, находящиеся в​ видим, обе фамилии,​ преобразовали с помощью​ иконке​​ левую кнопку мыши,​​ делаем выбор позиции​​ второй таблице. Поэтому​​ был равен числу​ копирование формулы, что​ то же время,​с помощью условного​ сдали отчет по​Попробуйте приложить больше​​ предыдущем файле есть)​​ же если в​Uskorenie​

   ​Отличия по строкам (Row​

   ​ Таблице_2, но отсутствующие​​ которые присутствуют во​​ функции​

   

8. ​«Вставить функцию»​ выделяем все значения​«Повторяющиеся значения»​ выделяем список работников,​«0»​​ позволит существенно сэкономить​​ существует несколько проверенных​ форматирования.​ продажам. Нам нужно​ своих данных для​leg​ столбце А есть​: А во втором​ differences)​​ в Таблице_1, будут​​ второй таблице, но​

​ЕСЛИ​.​ столбца с фамилиями​.​ находящийся в ней.​.​ время. Особенно данный​ алгоритмов действий, которые​Итак, мы выделяем​ сравнить эти отчеты​ проверки, возможно действительно​: Manyasha, все теперь​ номера, которых нет​ столбце только данные,​. В последних версиях​ подсвечены синим цветом.​ отсутствуют в первой,​. Делаем все ссылки​

​Открывается окно аргументов функции​

​ второй таблицы. Как​Запускается окно настройки выделения​ Переместившись на вкладку​Таким же образом можно​ фактор важен при​ позволят сравнить списки​ столбцы с данными​ и выявить разницу.​

Способ 2: выделение групп ячеек

​ другая структура, или​ все отлично и​ в столбце В,​ которые совпадают с​ Excel 2007/2010 можно​Выделите диапазон первой таблицы:​ выведены в отдельный​ абсолютными.​ЕСЛИ​ видим, координаты тут​ повторяющихся значений. Если​«Главная»​

1. ​ производить сравнение данных​ сравнивании списков с​​ или табличные массивы​​ (без названия столбцов).​У нас такая​​ еще что.​​ быстро работает. Я​ их тоже не​ первым. Точнее в​​ также воспользоваться кнопкой​​ A2:A15 и выберите​ диапазон.​В поле​​. Как видим, первое​​ же попадают в​

   

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

2. ​ расположены на разных​Процедуру копирования легче всего​​ сроки с минимальной​​ в разделе «Стили»​ из магазинов. Как​

   

3. ​: Друзья, выручайте! Есть​krosav4ig​ столбец С.​ входят только те​ & Select) -​ правило»- «Использовать формулу​ разных книгах можно​​указывается, какое по​​ заполнено значением оператора​​ для наших целей​​ данном окне остается​

   

4. ​, которая имеет месторасположение​ листах. Но в​ выполнить при помощи​ затратой усилий. Давайте​ нажимаем на кнопку​ перенести данные из​ 3 столбца артикулов:​:​Задача 2​ данные, которые есть​ Выделение группы ячеек​

Способ 3: условное форматирование

​ для определения форматированных​ использовать перечисленные выше​ счету наименьшее значение​СЧЁТЕСЛИ​ следует сделать данный​ только нажать на​ на ленте в​ этом случае желательно,​ маркера заполнения. Наводим​

1. ​ подробно рассмотрим данные​ «Условное форматирование». Из​ разных таблиц в​1 (битрикс) -​leg​В этой же​ в первом или​ (Go to Special)​ ячеек:».​ способы, исключая те​​ нужно вывести. Тут​​. Но нам нужно​​ адрес абсолютным. Для​​ кнопку​ блоке​ чтобы строки в​​ курсор на правый​​ варианты.​ появившегося списка выбираем​​ одну, читайте в​​ то, что есть​

   

2. ​, а такой вариант​ таблице в столбец​ во втором столбце​​на вкладке​​В поле ввода введите​

   

3. ​ варианты, где требуется​ указываем координаты первой​​ дописать кое-что ещё​​ этого выделяем данные​​«OK»​​«Стили»​ них были пронумерованы.​ нижний угол ячейки,​Скачать последнюю версию​ функцию «Создать правило».​​ статье «Как связать​​ на сайте,​ не подойдет?​ D вывести номера​ могут присутствовать позиции,​​Главная (Home)​​ формулу:​ размещение обоих табличных​ ячейки столбца с​ в это поле.​ координаты в поле​. Хотя при желании​. Из выпадающего списка​ В остальном процедура​​ где мы получили​​ Excel​В строке «Формат…» пишем​ таблицы в Excel»​2 (навижн) -​leg​ из столбца B,​ которых нет в​Excel выделит ячейки, отличающиеся​

   ​Щелкните по кнопке «Формат»​

   ​ областей на одном​ нумерацией, который мы​ Устанавливаем туда курсор​ и жмем на​​ в соответствующем поле​​ переходим по пункту​

   

4. ​ сравнения практически точно​​ показатель​​Читайте также: Сравнение двух​​ такую формулу. =$А2<>$В2.​​ тут.​ то, что есть​: krosav4ig, к сожалению​ которые есть в​ первом столбце?​ содержанием (по строкам).​ и на вкладке​​ листе. Главное условие​​ недавно добавили. Адрес​

   

5. ​ и к уже​ клавишу​ данного окошка можно​​«Управление правилами»​​ такая, как была​

   

6. ​«ИСТИНА»​ документов в MS​​ Этой формулой мы​​Первый способ.​​ в учетной программе,​​ я не разобрался,​

   

7. ​ столбце А. Т.е.​Ольчик30​ Затем их можно​ «Заливка» укажите зеленый​ для проведения процедуры​ оставляем относительным. Щелкаем​

​ существующему выражению дописываем​F4​ выбрать другой цвет​.​ описана выше, кроме​. При этом он​ Word​ говорим Excel, что​Как сравнить два столбца​3 (совпадения) -​ как вашим вариантом​ если в столбце​: Просто в первом​ обработать, например:​ цвет. На всех​ сравнения в этом​

1. ​ по кнопке​«=0»​

   

2. ​.​ выделения.​​Активируется окошко диспетчера правил.​​ того факта, что​ должен преобразоваться в​​Существует довольно много способов​​ если данные в​ в​​ то, что есть​​ пользоваться. И у​ B есть номер​​ столбце их больше,​​залить цветом или как-то​

   

3. ​ окнах жмем ОК.​ случае – это​«OK»​без кавычек.​Как видим, ссылка приняла​После того, как мы​ Жмем в нем​​ при внесении формулы​​ черный крестик. Это​ сравнения табличных областей​ ячейках столбца А​Excel на совпадения.​ в обоих столбцах​

   

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

​ в Excel, но​ не равны данным​Выделяем столбцы (у​ — не уверен​ на иконке вашего​ то его выводим​ а так все​очистить клавишей​ C2:C15 и снова​ файлов одновременно. Для​Оператор выводит результат –​ полю​​ характеризуется наличием знаков​​ все повторяющиеся элементы​​«Создать правило»​​ листами. В нашем​ заполнения. Жмем левую​​ все их можно​​ в ячейках столбца​

​ нас столбцы А​ в правильности, делал​ файла восклицательный знак​

​ столбец D.​ тоже самое и​

Способ 4: комплексная формула

​Delete​ выберите инструмент: «ГЛАВНАЯ»-«Условное​ версий Excel 2013​ число​​«Значение если истина»​​ доллара.​ будут выделены выбранным​.​ случае выражение будет​ кнопку мыши и​ разделить на три​ В, то окрасить​

​ и В). На​​ сам​​ стоит.​Файл с номерами​ вразброс. Пример прикладываю.​заполнить сразу все одинаковым​ форматирование»-«Создать правило»- «Использовать​ и позже, а​3​. Тут мы воспользуемся​

​Затем переходим к полю​

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

​ закладке «Главная» нажимаем​​Нужно сделать 4​​vikttur​ в приложении. Буду​Ольчик30​ значением, введя его​ формулу для определения​ также для версий​

1. ​. Именно оно наименьшее​ ещё одной вложенной​«Критерий»​ которые не совпадают,​ выбор позиции​​=B2=Лист2!B2​​ на количество строчек​

   

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

   

3. ​ функцией –​, установив туда курсор.​​ останутся окрашенными в​​«Использовать формулу»​То есть, как видим,​ в сравниваемых табличных​ одном листе;​

   ​Как работать с​​ «Найти и выделить»,​​ котором будут данные​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—(СЧЁТЕСЛИ($B$2:$B$22;A2)>0)​ помощь​Vlad999​Ctrl+Enter​В поле ввода введите​ с выполнением этого​ строк табличных массивов.​СТРОКА​ Щелкаем по первому​ свой изначальный цвет​. В поле​ перед координатами данных,​ массивах.​сравнение таблиц, расположенных на​​ условным форматированием, как​​ выбираем функцию «Выделение​

   ​ из второго столбца,​Manyasha​Manyasha​: смотрите УФ​

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

   

4. ​:​:​Ольчик30​​ выделенными ячейками, используя​​Щелкните по кнопке «Формат»​ проблем. Но в​ заполнения копируем формулу​​«СТРОКА»​​ в первом табличном​ Таким образом, можно​записываем формулу, содержащую​ других листах, отличных​

   

5. ​ дополнительном столбце отобразились​сравнение табличных диапазонов в​ шрифта в условном​В появившемся окне ставим​ будет в первом​vikttur​leg​: А какую функцию-то​ команду​ и на вкладке​ Excel 2007 и​ до самого низа.​​без кавычек, далее​​ диапазоне. В данном​ сразу визуально увидеть,​ адреса первых ячеек​ от того, где​ все результаты сравнения​

   

6. ​ разных файлах.​ форматировании, как написать​ галочку у слов​Помогите пожалуйста решить​, верно, частенько забываю​, я запуталась, так​ писать и где?​Главная — Удалить -​​ «Заливка» укажите синий​​ Excel 2010 для​Теперь, зная номера строк​​ открываем скобки и​​ случае оставляем ссылку​ в чем отличие​ диапазонов сравниваемых столбцов,​ выводится результат сравнения,​ данных в двух​Именно исходя из этой​

​ другие условия для​ «Отличия по строкам».​ данную задачку!​ про него (в​ как нужно: или​Vlad999​ Удалить строки с​

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

1. ​ разделенные знаком «не​ указывается номер листа​​ колонках табличных массивов.​​ классификации, прежде всего,​ выделения ячеек, строк,​ Нажимаем «ОК».​​Таблица с данными​​ смысле про двойное​Цитата​:​​ листа (Home -​​ окнах жмем ОК.​ оба окна одновременно,​ можем вставить в​​ ячейки с фамилией​​ как она отобразилась​При желании можно, наоборот,​ равно» (​ и восклицательный знак.​ В нашем случае​ подбираются методы сравнения,​​ т.д., читайте в​​В таблице выделились все​ https://drive.google.com/open?id=0B8D43Ww0tpVyQ1RkcXNNQUxveTg​​ отрицание)​​leg, 17.06.2015 в​

   

2. ​Ольчик30​​ Delete — Delete​​При определении условий для​ требуется провести дополнительные​ ячейку и их​​ во второй таблице,​​ в поле, можно​ окрасить несовпадающие элементы,​<>​Сравнение можно произвести при​ не совпали данные​ а также определяются​​ статье «Условное форматирование​​ ячейки с разными​

   ​заранее спасибо!​leg​​ 23:11, в сообщении​​там в УФ​ Rows)​ форматирования ячеек столбцов​​ манипуляции. Как это​​ значения с помощью​​ после чего закрываем​​ щелкать по кнопке​ а те показатели,​). Только перед данным​ помощи инструмента выделения​ только в одной​ конкретные действия и​ в Excel». Получилось​ данными так. Excel​Pelena​​, восклицательный знак -​​ № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»> в​

   ​ (условное форматирование) формула​

   ​и т.д.​​ мы использовали функцию​​ сделать рассказывается в​​ функции​​ скобки. Конкретно в​«OK»​ которые совпадают, оставить​ выражением на этот​ групп ячеек. С​ строке. При их​ алгоритмы для выполнения​​ так.​​ сравнила данные в​:​ это признак того,​​ столбце В нет​​ =ЕНД(ВПР($A2;$B$2:$B$5;1;0))​

   

3. ​Если списки разного размера​ СЧЕТЕСЛИ. В данном​​ отдельном уроке.​​ИНДЕКС​ нашем случае в​.​​ с заливкой прежним​​ раз будет стоять​ его помощью также​ сравнении формула выдала​

   

4. ​ задачи. Например, при​Третий способ.​ ячейках двух столбцов​​onzek​​ что файл содержит​ номера, который есть​какая версия эксель?​ и не отсортированы​ примере эта функция​Урок: Как открыть Эксель​. Выделяем первый элемент​

   

5. ​ поле​В элемент листа выводится​ цветом. При этом​ знак​​ можно сравнивать только​​ результат​ проведении сравнения в​Сравнить значения столбцов в​ — данные ячейки​, Прочитайте Правила форума​ макросы.​ в столбце А​

   

6. ​ — чтобы проще​ (элементы идут в​ проверяет сколько раз​ в разных окнах​ листа, содержащий формулу​​«Значение если истина»​​ результат. Он равен​

   

7. ​ алгоритм действий практически​​«=»​​ синхронизированные и упорядоченные​​«ЛОЖЬ»​​ разных книгах требуется​Excel формулой.​​ А2 и данными​​ и создайте свою​​Откройте файл, нажмите​​Сделала для обоих​

   

8. ​ было подсказывать если​​ разном порядке), то​​ встречается значение второго​Как видим, существует целый​НАИМЕНЬШИЙ​получилось следующее выражение:​ числу​

   ​ тот же, но​​. Кроме того, ко​​ списки. Кроме того,​. По всем остальным​​ одновременно открыть два​​Можно сделать в​ ячейки В2, т.д.​ тему. Для Google​​ alt+f11 и в​​ случаев​ что не понятно.​

   ​ придется идти другим​​ аргумента (например, A2)​​ ряд возможностей сравнить​. После этого переходим​СТРОКА(D2)​«1»​ в окне настройки​ всем к координатам​ в этом случае​ строчкам, как видим,​ файла Excel.​​ таблице дополнительный столбец​​Если мы сейчас нажмём​

   

9. ​ Docs есть свой​ модуле «Эта книга»​​есть в В,​​2003 Формат-Условное форматирование.​ путем.​ в списке первого​ таблицы между собой.​ в строку формул​Теперь оператор​

   

10. ​. Это означает, что​ выделения повторяющихся значений​ столбцов в данной​ списки должны располагаться​ формула сравнения выдала​Кроме того, следует сказать,​​ и установить в​​ на мышь, то​ раздел.​​ увидите код (лично​​ нет в А​2007 и выше​Самое простое и быстрое​​ аргумента (например, Таблица_2).​​ Какой именно вариант​​ и перед наименованием​​СТРОКА​ в перечне имен​ в первом поле​ формуле нужно применить​​ рядом друг с​​ показатель​ что сравнивать табличные​​ ячейках этого столбца​​ выделения ячеек исчезнут.​Эта тема закрыта​​ для меня пока​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$22;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$2:A$22;B$2:B$22)=0;ЕСЛИ(B$2:B$22<>0;СТРОКА(B$2:B$22);»»));СТРОКА(B1:B21))-1);»»)​

    

11. ​ Главная — Условное​ решение: включить цветовое​ Если количество раз​ использовать зависит от​«НАИМЕНЬШИЙ»​​будет сообщать функции​​ второй таблицы фамилия​ вместо параметра​ абсолютную адресацию. Для​ другом на одном​«ИСТИНА»​ области имеет смысл​ формулу. =А2=В2​ Чтобы эти выделенные​​Есть два столбца, они​​ загадочный:))​

    

12. ​для второго случая​​ форматирование — Управление​​ выделение отличий, используя​ = 0 в​ того, где именно​дописываем название​ЕСЛИ​

    ​«Гринев В. П.»​​«Повторяющиеся»​​ этого выделяем формулу​ листе.​​.​​ только тогда, когда​Получится так.​ ячеек оставить, мы​ же списки, с​SLAVICK​ аналогично, только на​ правилами.​ условное форматирование. Выделите​ таком случае формула​ расположены табличные данные​«ИНДЕКС»​номер строки, в​, которая является первой​​следует выбрать параметр​​ курсором и трижды​​Выделяем сравниваемые массивы. Переходим​​Кроме того, существует возможность​

    ​ они имеют похожую​​Можно условным форматированием окрасить​​ можем закрасить эти​ ФИО студентов. В​:​ пустые ячейки проверять​Ольчик30​ оба диапазона с​ возвращает значение ИСТИНА.​ относительно друг друга​

    ​без кавычек, тут​​ которой расположена конкретная​​ в списке первого​

    

13. ​«Уникальные»​ жмем на клавишу​ во вкладку​ с помощью специальной​ структуру.​ слова «Ложь» другим​ ячейки или изменить​ правом ФИО больше,​Manyasha​ не обязательно.​: 2007 версия.Я нажала​

Способ 5: сравнение массивов в разных книгах

​ данными и выберите​ В таком случае​ (на одном листе,​ же открываем скобку​ фамилия, и в​ табличного массива, встречается​. После этого нажать​F4​«Главная»​ формулы подсчитать количество​Самый простой способ сравнения​ цветом или окрасить​ цвет шрифта в​ чем в левом.​, посмотрите в подключениях​Обе формулы​ вставка функций, но​ на вкладке​ ячейке присваивается пользовательский​ в разных книгах,​ и ставим точку​ случае, когда условие,​ один раз.​ на кнопку​. Как видим, около​. Далее щелкаем по​ несовпадений. Для этого​ данных в двух​

​ эти ячейки.​ этих ячейках функциями​

​ Нужно найти совпадающие​ и в именах​массивные​ там не вижу​Главная — Условное форматирование​ формат, указанный в​ на разных листах),​ с запятой (​ заданное в первом​Теперь нам нужно создать​«OK»​ всех адресов столбцов​ значку​ выделяем тот элемент​ таблицах – это​Можно написать такую​

​ раздела «Шрифт» на​

lumpics.ru

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

​ строчки. Применяю правило​ — там самое​, вводятся сочетанием ctrl+shift+enter.​ функции с называнием​ — Правила выделения​ параметрах условного форматирования.​ а также от​;​ поле, будет выполняться,​ подобное выражение и​.​ появился знак доллара,​

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

​«Найти и выделить»​ листа, куда оно​ использование простой формулы​ формулу в ячейке​ закладке «Главная», пока​ =ЕНД (ВПР ($A$1;$D$1:$D$115;1;0))​ интересное, а код​ Ну и УФ​ УФ?​ ячеек — Повторяющиеся​

​Скачать пример сравнения 2​ того, как именно​). Затем выделяем в​ функция​ для всех других​

1. ​Таким образом, будут выделены​ что и означает​
2. ​, который располагается на​ будет выводиться. Затем​ равенства. Если данные​
3. ​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​ эти ячейки выделены.​ ничего не получается.​ — это часть​ в файле на​

​Vlad999​ значения (Home -​ таблицы в Excel​ пользователь желает, чтобы​ строке формул наименование​ЕСЛИ​

​ элементов первой таблицы.​ именно те показатели,​ превращение ссылок в​ ленте в блоке​ щелкаем по значку​ совпадают, то она​ «Enter». Копируем формулу​ Например, так.​Эксель 2007.​ решения.​ всякий случай.​

​:​ Conditional formatting -​Ссылка во втором аргументе​ это сравнение выводилось​«ИНДЕКС»​

​будет выводить этот​

​ Для этого выполним​ которые не совпадают.​ абсолютные. Для нашего​ инструментов​«Вставить функцию»​ выдает показатель ИСТИНА,​ по столбцу. Тогда​Или так.​Вадим догдогдог​

1. ​правда этот код​leg​Ольчик30​ Highlight cell rules​ относительная, значит по​ на экран.​
2. ​и кликаем по​ номер в ячейку.​
3. ​ копирование, воспользовавшись маркером​Урок: Условное форматирование в​ конкретного случая формула​«Редактирование»​.​
4. ​ а если нет,​ в столбце с​Сравнить данные в нескольких​: 1. Уберите $​ по идее призван​: Manyasha, огромное вам​
5. ​становитесь в ячейку​ — Duplicate Values)​
6. ​ очереди будут проверятся​Автор: Максим Тютюшев​ пиктограмме​ Жмем на кнопку​ заполнения, как это​

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

​ Экселе​ примет следующий вид:​. Открывается список, в​В окне​ то – ЛОЖЬ.​ разницей будут стоять​ столбцах​ в А1 =ЕНД​ обновлять подключение.​ спасибо! Все работает​ А2 и жмете​:​ все ячейки выделенного​У нас имеется две​«Вставить функцию»​«OK»​ мы уже делали​Также сравнить данные можно​

​=$A2<>$D2​ котором следует выбрать​

​Мастера функций​ Сравнивать можно, как​ цифры. Единица будет​Excel.​ (ВПР (A1;D$1:D$115;1;0))​по моему нужно​leg​ вкладка «Главная» -​Если выбрать опцию​ диапазона (например, A2:A15).​ таблицы заказов, скопированных​.​.​

exceltable.com

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

​ прежде. Ставим курсор​ при помощи сложной​Данное выражение мы и​ позицию​в группе операторов​ числовые данные, так​ стоять, если есть​Здесь мы сравнили​2. Так Вы​ тогда еще добавить​

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

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

​ обновление.​ вот не понял​

​ Управление правилами. Там​

​, то Excel выделит​ двух прайсов в​

​ лист. Необходимо выполнить​ окошко, в котором​ отображается, как​ часть элемента листа,​ является функция​ поле. После этого​.​выделяем наименование​ данного способа состоит​ — данные в​​ таблице, предварительно выделив​​ которых нет во​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Workbook_Open()​​ как переделать формулу​

​ все увидете.​ цветом совпадения в​ Excel даже на​ сравнение данных двух​ нужно определить, ссылочный​«ЛОЖЬ»​​ который содержит функцию​​СЧЁТЕСЛИ​ щёлкаем по кнопке​​Кроме того, в нужное​​СУММПРОИЗВ​​ в том, что​ ячейках одинаковые. Получится​​ все три столбца​ втором столбце, для​Me.Connections(«запрос»).ODBCConnection.Connection = «ODBC;DBQ=»​​ под задачу 2,​Ольчик30​ наших списках, если​ разных листах. Вторая​​ таблиц в Excel​​ вид должна иметь​

​. Это означает, что​СЧЁТЕСЛИ​. С помощью данного​«Формат…»​

• ​ нам окно выделения​. Щелкаем по кнопке​
• ​ ним можно пользоваться​​ так.​
• ​ таблицы. Сравниваются данные​ совпадающих​ & Me.FullName &​​ т.е. как в​
• ​: Это я нашла,​ опцию​ формула действует аналогично.​​ и проверить, которые​ функция​ значение не удовлетворяет​, и после преобразования​ инструмента можно произвести​
• ​.​

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

​ группы ячеек можно​«OK»​ только в том​Четвертый​ в ячейках построчно​=НЕ (ЕНД (ВПР​

​ «;Driver={Microsoft Excel Driver​ столбец D вывести​ а дальше появилось​Уникальные​ Этот же принцип​ позиции есть первой​ИНДЕКС​​ условиям оператора​ его в маркер​ подсчет того, сколько​Активируется окно​ попасть и другим​.​ случае, если данные​​с​

​ (А2, В2, С2,т.д.).​​ (A1;D$1:D$115;1;0)))​​ (*.xls, *.xlsx, *.xlsm,​ ячейки с номерами​ окно и куда​- различия.​​ можно применять для​​ таблице, но нет​

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

​пособ.​ Получилось так.​​а лучше​ ​ *.xlsb)}»​​ из столбца B,​​ дальше идти в​​Цветовое выделение, однако, не​ разных подобных задач.​ во второй. Нет​ работы с массивами.​

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

​. Идем во вкладку​ особенно будет полезен​СУММПРОИЗВ​ или отсортированы одинаково,​Можно​

​Как сравнить даты в​=СЧЁТЕСЛИ (D$1:D$115;A1)​Me.Connections(«запрос»).Refresh​

planetaexcel.ru

Как сравнить два столбца в Excel

​ которые есть в​​ нем?Нажимаю создать правило,​
​ всегда удобно, особенно​Типовая задача, возникающая периодически​ смысла вручную сравнивать​ Нам нужен второй​ фамилия присутствует в​ тянем курсор вниз.​ таблицы повторяется в​«Заливка»​ тем пользователям, у​, главной задачей которой​ синхронизированы и имеют​объединить таблицы Excel​Excel.​Денис​

​End Sub​​ столбце А. С​ а реакции нет.​ для больших таблиц.​ перед каждым пользователем​ значение каждой ячейки.​ вариант. Он установлен​ обоих списках.​Как видим, программа произвела​ первой.​. Тут в перечне​ которых установлена версия​

​ является вычисление суммы​​ равное количество строчек.​с отчетами. Тогда​Можно сравнить даты.​: суть задачи не​Цитата​ массивами я вообще​Vlad999​ Также, если внутри​ Excel — сравнить​Как сделать сравнение значений​ по умолчанию, так​С помощью маркера заполнения,​

​ вычисление совпадений, сравнив​​Оператор​ цветов останавливаем выбор​ программы ранее Excel​ произведений выделенного диапазона.​ Давайте посмотрим, как​ все одинаковые данные​

​ Принцип сравнения дат​​ очень ясна особенно​

​leg, 26.06.2015 в​​ не дружу просто​

​: если вы смотрите​​ самих списков элементы​ между собой два​

​ в Excel двух​​ что в данном​​ уже привычным способом​​ каждую ячейку первой​СЧЁТЕСЛИ​ на цвете, которым​
​ 2007, так как​ Но данную функцию​ использовать данный способ​ соберутся в одну​
​ тот же –​
​ без исходника перед​ 20:17, в сообщении​Manyasha​ мой фай то​

​ могут повторяться, то​​ диапазона с данными​ столбцов? Для решения​ окошке просто щелкаем​ копируем выражение оператора​ таблицы с данными,​

​относится к статистической​​ хотим окрашивать те​​ метод через кнопку​​ можно использовать и​ на практике на​ строку, но можно​ выделяем столбцы, нажимаем​ глазами, если вышлешь​ № 11200?’200px’:»+(this.scrollHeight+5)+’px’);»>krosav4ig, к​

​:​​ правила там уже​ этот способ не​ и найти различия​ данной задачи рекомендуем​ по кнопке​ЕСЛИ​

​ которые расположены во​​ группе функций. Его​ элементы, где данные​«Найти и выделить»​ для наших целей.​ примере двух таблиц,​
​ будет посмотреть и​ на кнопку «Найти​ мне могу помочь​
​ сожалению я не​leg​ написаны просто разберитесь​
​ подойдет.​ между ними. Способ​ использовать условное форматирование,​«OK»​на весь столбец.​
​ втором табличном диапазоне.​
​ задачей является подсчет​ не будут совпадать.​эти приложения не​ Синтаксис у неё​ размещенных на одном​

CyberForum.ru

Сравнить два столбца и вывести уникальные значения (Формулы/Formulas)

​ отдельно данные по​​ и выделить». Выбираем​Сергей владимирович​
​ разобрался, как вашим​
​, просто замените знак​ в нем.​В качестве альтернативы можно​ решения, в данном​ которое быстро выделить​.​ Как видим, по​ В четырех случаях​ количества ячеек, значения​ Жмем на кнопку​ поддерживают. Выделяем массивы,​ довольно простой:​ листе.​ магазинам. Как это​ функцию «Выделение группы​: Если в правом,​ вариантом пользоваться.​ «=» на знак​если хотите создавать​ использовать функцию​ случае, определяется типом​ цветом позиции, находящиеся​Запускается окно аргументов функции​ двум позициям, которые​ результат вышел​ в которых удовлетворяют​«OK»​ которые желаем сравнить,​=СУММПРОИЗВ(массив1;массив2;…)​Итак, имеем две простые​ сделать, смотрите в​ ячеек», ставим галочку​ т. е. D​достаточно на синей​
​ «>» в условии​
​ своё то действовать​СЧЁТЕСЛИ​ исходных данных.​ только в одном​ИНДЕКС​ присутствуют во второй​«1»​ заданному условию. Синтаксис​.​ и жмем на​Всего в качестве аргументов​
​ таблицы со списками​ статье «Как объединить​ у слов «Отличия​ больше фамилий, чем​

​ таблице просто клацнуть​​ для СЧЁТЕСЛИ, и​​ нужно так:​​(COUNTIF)​Если списки синхронизированы (отсортированы),​​ столбце. Рабочий лист​​. Данный оператор предназначен​ таблице, но отсутствуют​, а в двух​ данного оператора имеет​Вернувшись в окно создания​ клавишу​
​ можно использовать адреса​ работников предприятия и​
​ таблицы в Excel».​ по строкам». Нажимаем​​ в A, то​​ правой кнопкой мыши​ можно проверку на​1. выделяете весь​из категории​
​ то все делается​​ с таблицами:​​ для вывода значения,​ в первой, формула​ случаях –​ такой вид:​

​ правила форматирования, жмем​​F5​ до 255 массивов.​

​ их окладами. Нужно​​Пятый способ.​ «ОК».​ =ЕНД (ВПР (D1;$A$1:$A$115;1;0))​ и выбрать обновить.​ пустые ячейки убрать:​ диапазон на который​Статистические​ весьма несложно, т.к.​В первую очередь необходимо​ которое расположено в​ выдает номера строк.​«0»​

​=СЧЁТЕСЛИ(диапазон;критерий)​​ на кнопку​​.​​ Но в нашем​ сравнить списки сотрудников​Используем​Здесь расхождение дат в​ и протаскиваем до​Это запустит SQL​
​200?'200px':''+(this.scrollHeight+5)+'px');">=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$22;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$2:A$22;B$2:B$22)>0;СТРОКА(B$2:B$22));СТРОКА(B1:B21))-1);"")​

​ будет применено правило;​​, которая подсчитывает сколько​ надо, по сути,​

​ присвоить имена обоим​​ определенном массиве в​Отступаем от табличной области​. То есть, программа​Аргумент​«OK»​Активируется небольшое окошко перехода.​ случае мы будем​ и выявить несоответствия​функцию «СЧЕТЕСЛИ» в​ ячейках строк второй​ конца столбца D.​ запрос написанный​leg​2. Заходим в​ раз каждый элемент​ сравнить значения в​ таблицам. Благодаря этому​ указанной строке.​ вправо и заполняем​ не смогла отыскать​

​«Диапазон»​​.​​ Щелкаем по кнопке​​ использовать всего два​ между столбцами, в​Excel​
​ и третьей.​ ЛОЖЬ — фамилия​krosav4ig​
​: Manyasha, еще раз​​ УФ — Создать​ из второго списка​ соседних ячейках каждой​ легче понять, какие​Как видим, поле​ колонку номерами по​ во второй таблице​представляет собой адрес​После автоматического перемещения в​

​«Выделить…»​​ массива, к тому​ которых размещены фамилии.​. Эта функция посчитает​Есть еще один​

​ есть в другом​​.​​ огромно вам спасибо​​ правило — выбираем​ встречался в первом:​

​ строки. Как самый​​ сравниваются диапазоны ячеек:​«Номер строки»​ порядку, начиная от​ два значения, которые​ массива, в котором​ окно​в его нижнем​ же, как один​

​Для этого нам понадобится​​ количество повторов данных​
​ способ​

​ столбце, ИСТИНА -​​Или просто открыть​​leg​​ нужное правило и​Полученный в результате ноль​ простой вариант -​Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить​
​уже заполнено значениями​​1​ имеются в первом​ производится подсчет совпадающих​«Диспетчера правил»​
​ левом углу.​ аргумент.​ дополнительный столбец на​ их первого столбца,​сравнить даты в Excel​ нет.​

​ файл.​​: Manyasha, а есть​​ форматирование. Ок.​​ и говорит об​ используем формулу для​ имя».​ функции​. Количество номеров должно​ табличном массиве.​
​ значений.​щелкаем по кнопке​После этого, какой бы​
​Ставим курсор в поле​ листе. Вписываем туда​ сравнив их с​
​- сравнить периоды​
​Abram pupkin​leg​ ли вариант формулы​Это вкратце.​ отличиях.​
​ сравнения значений, выдающую​
​В появившемся окне в​
​НАИМЕНЬШИЙ​​ совпадать с количеством​Конечно, данное выражение для​Аргумент​«OK»​ из двух вышеперечисленных​«Массив1»​
​ знак​ данными второго столбца.​ дат,есть ли в​: ПОВТОРЫ, СОВПАДЕНИЯ И​
​: SLAVICK, в ваш​ попроще? С не​​П.С. если нужно​​И, наконец, «высший пилотаж»​
​ на выходе логические​ поле «Имя:» введите​

​. От уже существующего​​ строк во второй​ того, чтобы сравнить​«Критерий»​и в нем.​ вариантов вы не​и выделяем на​«=»​В дополнительном столбце​ указанных периодах одинаковые​ УНИКАЛЬНЫЕ ЗНАЧЕНИЯ​

​ файл вставил номера,​​ большими объемами это​​ увидеть все правила​​ — можно вывести​
​ значения​ значение – Таблица_1.​ там значения следует​ сравниваемой таблице. Чтобы​ табличные показатели, можно​задает условие совпадения.​Теперь во второй таблице​ избрали, запускается окно​ листе сравниваемый диапазон​. Затем кликаем по​ устанавливаем формулы, они​ даты и сколько​Гафиатуллин наиль​

​ более 60 тысяч​​ все работает отлично,​ на листе то​​ отличия отдельным списком.​​ИСТИНА (TRUE)​Левой клавишей мышки сделайте​ отнять разность между​​ ускорить процедуру нумерации,​
​ применять и в​
​ В нашем случае​ элементы, которые имеют​ выделения групп ячеек.​ данных в первой​ первому наименованию, которое​

​ разные с разными​​ дней в периодах​: Я использую такой​
​ в колонку старые​ а вот если​ вместо «Текущее правило»​
​ Для этого придется​или​ щелчок по полю​
​ нумерацией листа Excel​ можно также воспользоваться​ существующем виде, но​ он будет представлять​ данные, несовпадающие с​ Устанавливаем переключатель в​
​ области. После этого​ нужно сравнить в​ условиями. Или в​ совпадают. Этот способ​ вариант сравнения​ и 10 тыс.​
​ в одном столбце​ выбираем «Этот лист»​
​ использовать формулу массива:​ЛОЖЬ (FALSE)​
​ ввода «Диапазон:» и​

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

excelworld.ru

Сравнение 2 столбцов в Excel

​ может пригодиться, чтобы​http://hammga.ru/blogs?id=2​ в колонку новые,​ около 100 тысяч​leg​Выглядит страшновато, но свою​:​ выделите диапазон: A2:A15.​ табличной области. Как​
​После этого выделяем первую​

​ усовершенствовать.​​ ячеек первой табличной​ табличной области, будут​«Выделить по строкам»​
​ знак​ ставим символ​ данные, слова, которые​ выявить пересечение дат​На VBA безграничные​
​ нажимаю обновить выскакивает​ и в другом​
​: Здравствуйте! Помогите пожалуйста​
​ работу выполняет отлично​

​Число несовпадений можно посчитать​​ И нажмите ОК.​ видим, над табличными​ ячейку справа от​Сделаем так, чтобы те​ области.​

​ выделены выбранным цветом.​​. Жмем по кнопке​«не равно»​«=»​ нужно посчитать в​ в периодах. Например,​ возможности.​ сообщение — [Драйвер​ примерно столько же,​ найти решение.​ ;)​ формулой:​

​Для второго списка выполните​​ значениями у нас​ колонки с номерами​

​ значения, которые имеются​​Выделяем первый элемент дополнительного​Существует ещё один способ​
​«OK»​
​(​с клавиатуры. Далее​

​ столбце.​

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

Если необходимо выделить цветом сами строки в таблице Эксель, также создайте дополнительный столбик, а в каждой строчке используйте формулу =A2&B2&C2&D2. После этого выделите все данные таблицы, войдите в «Главная» и пункт «Условное форматирование». Здесь жмите на «Создать правило» и «Создание правила форматирования». Теперь жмите на «Использовать формулу для определения…» и в категории «Форматировать значения, для которых …» введите =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1. На завершающе этапе задайте формат найденных дублей.

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

Отличного Вам дня!

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

Читайте также: Сравнение двух документов в MS Word

Способы сравнения

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

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

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

Способ 1: простая формула

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

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

1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

   =A2=D2

   

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

2. Щелкаем по клавише Enter, чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА», что означает совпадение данных.



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

   Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА». При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.



4. Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ». По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА».



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





6. В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ. Щелкаем по кнопке «OK».



7. Активируется окно аргументов функции СУММПРОИЗВ, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:

   =СУММПРОИЗВ(массив1;массив2;…)

   Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

   Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение:

   --(A2:A7<>D2:D7)

   Щелкаем по кнопке «OK».



8. Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1», то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0».

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

=B2=Лист2!B2

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

Способ 2: выделение групп ячеек

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

1. Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».
   

   Кроме того, в нужное нам окно выделения группы ячеек можно попасть и другим способом. Данный вариант особенно будет полезен тем пользователям, у которых установлена версия программы ранее Excel 2007, так как метод через кнопку «Найти и выделить» эти приложения не поддерживают. Выделяем массивы, которые желаем сравнить, и жмем на клавишу F5.

2. Активируется небольшое окошко перехода. Щелкаем по кнопке «Выделить…» в его нижнем левом углу.



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



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

Способ 3: условное форматирование

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

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



2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило».



3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

   =$A2<>$D2

   Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…».



4. Активируется окно «Формат ячеек». Идем во вкладку «Заливка». Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку «OK».



5. Вернувшись в окно создания правила форматирования, жмем на кнопку «OK».



6. После автоматического перемещения в окно «Диспетчера правил» щелкаем по кнопке «OK» и в нем.



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

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

1. Производим выделение областей, которые нужно сравнить.



2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения».



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



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

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

Таким образом, будут выделены именно те показатели, которые не совпадают.

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

Способ 4: комплексная формула

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

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

=СЧЁТЕСЛИ(диапазон;критерий)

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

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



2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK».



3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

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

   Как видим, ссылка приняла абсолютную форму, что характеризуется наличием знаков доллара.

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



4. В элемент листа выводится результат. Он равен числу «1». Это означает, что в перечне имен второй таблицы фамилия «Гринев В. П.», которая является первой в списке первого табличного массива, встречается один раз.



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



6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел «1», а в двух случаях – «0». То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.

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

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

1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию».



2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

   После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

   СТРОКА(D2)

   Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK».



3. Как видим, первый результат отображается, как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ. То есть, первая фамилия присутствует в обоих списках.



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



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



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



7. Открывается Мастер функций. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ». Щелкаем по кнопке «OK».



8. Функция НАИМЕНЬШИЙ, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK».



9. Оператор выводит результат – число 3. Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.



10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС. Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ. После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (;). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию».



11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK».



12. Запускается окно аргументов функции ИНДЕКС. Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

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

    Жмем на кнопку «OK».



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

Способ 5: сравнение массивов в разных книгах

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

Урок: Как открыть Эксель в разных окнах

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

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

Внимание

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

Ниже приведен набор данных, в котором мне нужно проверить в той же строке, совпадают ли имена в столбце A с именами в столбце B или нет.

Нажмите, чтобы загрузить образец файла

1.1 Сравните ячейки в одной строке на предмет точного совпадения

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

= B2 = C2

Нажмите Enter и перетащите маркер заполнения в ячейку D8. Если формула возвращает ИСТИНА, значения двух столбцов полностью совпадают, если возвращается ЛОЖЬ, они разные.

1.2 Сравнить ячейки в одной строке на предмет точного совпадения или совпадения без учета регистра (с использованием формулы ЕСЛИ)

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

Сравнение ячеек в одной строке на предмет точного совпадения

Если вы хотите использовать тексты «Совпадение» и «Несоответствие» для описания результатов сравнения, воспользуйтесь приведенной ниже формулой:

=IF(EXACT(B2,C2),»Match»,»Mismatch»)

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

Сравнение ячеек в одной строке на предмет соответствия без учета регистра

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

=IF(B2=C2,»Match»,»Mismatch»)

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

замечание

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

1.3 Сравните ячейки в одной строке и выделите совпадающие или несоответствующие данные (используя условное форматирование)

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

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

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

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

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

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

Если вы хотите выделить значения несоответствия, вы можете использовать это в = $ B2 <> $ C2 в Формат значений, где эта формула истинна текстовое поле в Изменить правило форматирования Диалог.

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

1.4 Сравните два столбца строка за строкой и выделите значения несоответствия (используя VBA)

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

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

2. В появившемся диалоговом окне щелкните Вставить > Модули.

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

VBA: сравнение двух столбцов строка за строкой и выделение различий

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

4. нажмите F5 нажмите клавишу для запуска кода, затем появится диалоговое окно для выбора двух столбцов.

5. Нажмите OK. Затем различия двух столбцов были выделены цветом фона.

замечание

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

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

Нажмите, чтобы загрузить образец файла

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

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

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

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

замечание

1. Эта формула сравнивает два столбца без учета регистра.

2. Вы можете изменить описание «Нет в C» и «Да в C» на другие.

3. Если вы хотите сравнить два целых столбца, измените фиксированный диапазон $ C $ 2: $ C $ 8 на $ C: $ C.

2.2 Сравнение и выделение повторяющихся или уникальных данных (с использованием условного форматирования)

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

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

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

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

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

замечание

Если вы хотите выделить уникальные значения (различия) в двух столбцах, также нажмите Главная > Условное форматирование > Выделите правила ячеек > Дублирующие значения для отображения Дублирующие значения диалог, измените Дублировать в Уникальное Произведение в левом раскрывающемся списке, затем выберите другой формат из значений в раскрывающемся списке, нажмите OK.

Уникальные значения будут выделены.

Найдите и выделите значения в столбце B, если они также находятся в столбце C.

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

1. Выберите столбец B2: B8, щелкните Главная > Условное форматирование > Новое правило.

2. в Новое правило форматирования диалога, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила раздел, затем введите = СЧЁТЕСЛИ ($ C $ 2: $ C $ 8, $ B2)> 0 в текстовое поле Формат значений, где эта формула истинна.

3. щелчок Формат , чтобы перейти к Формат ячеек диалога под Заполнять на вкладке выберите один цвет, чтобы выделить совпадения.

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

4. Нажмите OK > OK. Затем значения в столбце B, которые также существуют в столбце C, были выделены указанным цветом.

Если вы хотите выделить значения, которые только в столбце B, но не в столбце C, повторите указанные выше шаги, но измените формулу на шаге 2 на = СЧЁТЕСЛИ ($ C $ 2: $ C $ 8, $ B2) = 0 в Новое правило форматирования Диалог.

Затем выберите другой цвет для форматирования значений.

замечание

Здесь условное форматирование сравнивает два столбца без учета регистра.

2.3 Сравните и выберите или выделите повторяющиеся или уникальные данные (с помощью удобного инструмента)

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки.

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

1). Найдите значения в и Согласно информации разделы, выберите два столбца, с которыми хотите сравнить;

2) Выберите Каждый ряд вариант;

3) Выберите Те же значения or Разные ценности как вам нужно;

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

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

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

замечание

Если вы хотите сравнить с чувствительностью к регистру, отметьте Деликатный случай опцию.

Этот инструмент поддерживает сравнение двух столбцов на разных листах.

2.4 Сравните два столбца и укажите точные дубликаты в другом столбце (используя код VBA)

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

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

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

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

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

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
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 Чтобы запустить код, поочередно появляются два диалоговых окна, в которых вы можете выбрать два столбца отдельно.

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

5. Нажмите OK > OK, то совпадения будут автоматически перечислены в правом столбце двух столбцов.

замечание

Код VBA сравнивает два столбца с учетом регистра.

2.5 Сравните два столбца и выделите дубликаты (используя код VBA)

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

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

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

3. Скопируйте приведенный ниже код макроса и вставьте его в новый бланк. Модули скрипты.

VBA: сравнить два столбца и выделить дубликаты

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

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

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

6. Нажмите OK. Теперь дубликаты во втором столбце были выделены цветом фона после сравнения с первым столбцом.

замечание

1. Код сравнивает столбцы с учетом регистра.

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

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

Нажмите, чтобы загрузить образец файла

3.1. Найдите полные совпадения во всех ячейках одной строки (используя формулу ЕСЛИ)

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

=IF(AND(B2=C2, B2=D2), «Full match», «Not»)

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

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

замечание

1. Формула сравнивает столбцы без учета регистра.

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

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

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

3.2 Найдите совпадения в любых двух ячейках в одной строке (используя формулу ЕСЛИ)

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

=IF(OR(B2=C2, C2=D2, B2=D2), «Match», «Not»)

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

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

замечание

1. Эта формула не поддерживает регистр без учета.

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

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,»Not»,»Match»)

3.3 Найдите полные совпадения во всех ячейках одной строки и выделите их (используя условное форматирование)

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

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

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

=AND($B2=$C2, $B2=$D2)

Or

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

Внимание: Если количество столбцов больше трех, предположим, 5, формулы следует изменить на:

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

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

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

замечание

Приведенные выше формулы не поддерживают регистр.

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

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

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

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

3. Нажмите OK. Теперь различия строк выделены.

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

замечание

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

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

Нажмите, чтобы загрузить образец файла

4.1 Сравните и найдите недостающие точки данных (с помощью формулы ВПР или ПОИСКПОЗ)

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

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

Or

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

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

замечание

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

4.2 Сравните два столбца и перечислите недостающие данные ниже (используя формулу ИНДЕКС)

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

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

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

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

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

замечание

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

4.3 Сравните и найдите недостающие точки данных и перечислите их в другом столбце (используя удобный инструмент)

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

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки.

2. в Выберите одинаковые и разные ячейки диалог, сделайте как показано ниже:

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

2) Выберите Каждый ряд опцию.

3) Выберите Разные ценности опцию.

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

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

замечание

Отметив Без учета регистра опция в диалоговом окне «Выбрать одинаковые и разные ячейки» сравнивает два столбца с учетом регистра.

4.4 Сравните два столбца и перечислите недостающие данные ниже (используя VBA)

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

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

2. Нажмите Вставить > Модули чтобы создать новый модуль для размещения под кодом VBA.

VBA: сравнить два столбца и заполнить недостающие данные

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

3. Затем нажмите F5 нажмите клавишу для запуска кода, появится диалоговое окно для выбора двух сравниваемых столбцов.

4. Нажмите OK. Теперь недостающие данные перечислены под двумя столбцами.

замечание

Код сравнивает ячейки без учета регистра.

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

Нажмите, чтобы загрузить образец файла

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

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

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

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

замечание

1. В Excel даты хранятся в виде числовых рядов, по сути, это числа. Таким образом, вы применяете формулу для прямого сравнения дат.

2. Если вы хотите сравнить, если дата 1 раньше даты 2 в каждой строке, измените символ. > в < в формуле.

5.2 Сравните два столбца, если они больше или меньше, чем формат (с использованием условного форматирования)

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

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

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

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

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

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

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

Нажмите, чтобы загрузить образец файла

6.1 Получите точные совпадающие данные (используя формулу или удобный инструмент)

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

Формульный метод

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

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

Or

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

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

замечание

1. Формулы не поддерживают регистр.

2. Число 2 в формуле означает, что вы нашли совпадающие значения во втором столбце массива таблицы.

3. Если формулы не могут найти относительное значение, возвращается значение ошибки # N / A.

Удобный инструментальный метод

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите ячейку F2 (ячейку, в которую нужно поместить возвращаемое значение) и щелкните Кутулс > Формула Помощник > Поиск и справка > Найдите значение в списке.

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

Функции: Не забудьте изменить абсолютную ссылку в Искомое_значение поле к относительному, или вы можете найти только первое значение.

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

6.2 Получение данных частичного совпадения (используя формулу)

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

Для решения этой задачи выберите одну из формул ниже:

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

Or

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

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

замечание

1. Формулы не поддерживают регистр.

2. Число 2 в формуле означает, что вы найдете возвращаемые значения во втором столбце массива таблицы.

3. Если формулы не могут найти относительное значение, возвращается значение ошибки # N / A.

4. * в формуле — это подстановочный знак, который используется для обозначения любого символа или строки.

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

Нажмите, чтобы загрузить образец файла

7.1 Сравните два столбца и подсчитайте совпадения (используя формулу СУММПРОИЗВ)

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

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

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

замечание

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

7.2 Сравните два столбца и подсчитайте совпадения или различия (с помощью удобного инструмента)

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки.

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

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

Соответствующие ячейки

Разные клетки

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

Нажмите, чтобы загрузить образец файла

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

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

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

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

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

замечание

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

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

Эту формулу нужно только нажать Enter и перетащите маркер автозаполнения.

2. В формулах * — это подстановочный знак, обозначающий любой символ или строку.

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

Нажмите, чтобы загрузить образец файла

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

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),»Difference»,»Match»)

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

Затем примените Фильтр функцию в Excel чтобы отфильтровать различия или совпадения по значению.

Выберите столбец формулы, затем щелкните Данные > Фильтр.

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

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

Теперь нажмите Данные > Фильтр еще раз, чтобы очистить фильтр.

Удалите столбец формулы, если он вам больше не нужен.

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

Нажмите, чтобы загрузить образец файла

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

=(C2-B2)/B2

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

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

Результаты формулы были отформатированы в процентах.

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

Нажмите, чтобы загрузить образец файла

11.1 Сравнить два диапазона по ячейке

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки.

2. Во всплывающем Выберите одинаковые и разные ячейки диалог, сделайте как показано ниже:

1). Найдите значения в В разделе выберите диапазон, в котором вы хотите найти совпадения или различия после сравнения двух диапазонов.

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

3) В на основании раздел, выбрать Однокамерная.

4) Затем в Найти раздел, выберите тип ячеек, которые вы хотите выделить или выделить.

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

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

Выделение одинаковых значений

замечание

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

11.2 Сравните два диапазона, если данные в одном порядке

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

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

1. Нажмите Кутулс > Сравнить ячейки.

2. в Сравнить ячейки диалог, установите как показано ниже:

1) Выберите два диапазона в Найдите значения в и Согласно информации коробки отдельно.

2) Выберите тип ячейки, которую хотите выделить в Найти .

3) Выберите тип выделения в Обработка результатов .

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

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

Использование условного оператора ЕСЛИ

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

Разместите оба столбца для сравнения в колонках A и B рабочего листа.

В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

Использование формулы подстановки ВПР

Принцип работы формулы аналогичен предыдущей методике, отличие заключается в использовании формулы ВПР, вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

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

Использование макроса VBA

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

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

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

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

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

Использование надстройки Inquire

С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.

Итог

Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.