3 рабочих способа сравнить два столбца на совпадения в excel

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

Мы можем ставить перед собой следующие цели:

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

Пример 1. Найдите полное совпадение по одной строке.

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

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

где 3 — количество сравниваемых колонок.

Или можно использовать —

Пример 2. Найдите хотя бы 2 совпадения в данных.

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

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

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

или

Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести всё правильно.

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Сравнение двух списков в Excel

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

Постановка задачи

Итак, нам нужно сравнить эти столбцы. Методика сравнения двух документов следующая:

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

Все эти три условия одновременно и являются условиями нашей задачи.

Решение задачи

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

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

1. В отдельной колонке создаем список уникальных значений, характерных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); “”). Сама формула должна записываться, как формула массива.
2. Определим, сколько раз каждое уникальное значение, встречается в массиве данных. Вот, какими формулами можно это сделать: =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5).
3. Если и число повторений, и количество уникальных значений одинаковое во всех перечнях, которые входят в эти диапазоны, то функция возвращает значение 0. Это говорит о том, что совпадение стопроцентное. В этом случае заголовки этих списков обретут зеленый фон.
4. Если все уникальное содержимое есть в обоих списках, то возвращенное формулами =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;F5:F34;0) значение составит ноль. Если же E1 содержит не ноль, а такое значение содержится в ячейках E2 и F2, то в этом случае диапазоны будут признаны совпадающими, но только частично. В таком случае заголовки соответствующих списков станут оранжевыми.
5. И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими.

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

Тестирование на примере

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

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

Форматирование

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

1. Шаг 1. Переходите к блоку Стили на главной вкладке, нажимаете кнопку Условное форматирование, опускаетесь вниз и щелкаете по строке Создать правило.

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

1. Шаг 3. Если формула принимает значение ИСТИНА, то ячейка форматируется заданным образом. Делается это через кнопку Формат. В появившемся окне настраиваете параметры шрифта и ячейки.

1. Шаг 4. Чтобы подтвердить формат и закрепить правило, дважды нажимаете ОК. После этого разные ячейки подсветятся по заданным условиям.

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

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

Совпадает ли ячейка с образцом?

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

ЧСТРОК( диапазон ) * ЧИСЛСТОЛБ( диапазон ) = СЧЁТЕСЛИ( диапазон ; ячейка образца )

В логической проверке функции ЕСЛИ вы проверяете равенство двух чисел:

1. Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов), и
2. Количество ячеек, содержащих то же значение, что и в образце (возвращенное функцией СЧЁТЕСЛИ ).

Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2: B6, формула выглядит следующим образом:

(ЧСТРОК(A2:B6)*ЧИСЛСТОЛБ(A2:B6))=СЧЁТЕСЛИ(A2:B6;C2)

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

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

Проверяем совпадение с образцом с учётом регистра букв.

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

Поскольку исходный диапазон находится в области A2: B6, а образец текста — в C2, получаем следующее:

В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано ниже:

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

​ ожидается аудиторская проверка.​ОК​(Запрос), чтобы добавить​ узел схемы, например​.​ внизу страницы. Для​ ‘данные другого столбца​: Вот тут не​ может посоветовать в​: Привел файлы к​ быстро. Если попробуете​ которые и требуется​ ‘ Эта строчка​ проблема в объеме​

​ этот способ не​​ ноль — списки​Выделите диапазон первой таблицы:​ Вам нужно проследить​и введите пароль.​ пароли, которые будут​

1. ​ на страницу с​Подробнее о средстве сравнения​

2. ​ удобства также приводим​If .exists(arrB(i, 1))​ понял:​ плане решения данной​ одному виду, т.е.​ — расскажите :)​ обнаружить. Т.е. нужно​​ красит всю строку​​ файлов. Подскажите пожалуйста​

3. ​ подойдет.​​ идентичны. В противном​​ A2:A15 и выберите​​ данные в важных​​ Узнайте подробнее о​

4. ​ сохранены на компьютере.​​ именем «Запад», появляется​​ электронных таблиц и​ ссылку на оригинал​​ Then​​For i =​ задачи, так же​

   ​ колонка с требуемыми​Steel Rain​ найти все уникальные​ в зеленый цвет​ какой нибудь алгоритм,​В качестве альтернативы можно​ случае — в​ инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать​

5. ​ книгах, в которых​​ том, как действуют​​ Эти пароли шифруются​ выноска со сведениями.​​ сравнении файлов можно​​ (на английском языке).​.Item(arrB(i, 1)) =​

   ​ 1 To UBound(arrB)​ буду признателен.​ данными для отбора​

   ​: Объясните пожалуйста логику​​ значения в файле​.Pattern = xlSolid​ который не сутки​ использовать функцию​ них есть различия.​

6. ​ правило»- «Использовать формулу​​ показаны изменения по​​ пароли при использовании​

​ и доступны только​​Подробнее об этом можно​ узнать в статье​Предположим, что вы хотите​ .Item(arrB(i, 1)) +​If .exists(arrA(i, 1))​​Hugo​​ — столбец А,​ процесса, для чего​ 1, которых нет​End With​ будет работать.​СЧЁТЕСЛИ​

​ Формулу надо вводить​ для определения форматированных​ месяцам и по​ средства сравнения электронных​ вам.​ узнать в статье​ Сравнение двух версий​ Сравнение версий книги,​ 1​ Then​: Для строк полностью​ затем столбец Б​ выделять по две​ в файле 2​End If​P.S. поиском по​(COUNTIF)​

Интерпретация результатов

• ​ как формулу массива,​ ячеек:».​ годам. Это поможет​ таблиц.​Подробнее об использовании паролей​ Просмотр связей между​ книги.​ анализ книги для​p = p + 1​Что-то не то​ нужно писать другой​

• ​ пустой. Выделяю в​ ячейки? Мне нужно​ и, соответственно, наоборот.​​i = i + 1​​ форуму воспользовался как​из категории​

• ​ т.е. после ввода​В поле ввода введите​ вам найти и​Результаты сравнения отображаются в​ для анализа книг​ листами.​Команда​ проблемы или несоответствия​Else: .Add key:=arrB(i,​ :(​ код — этот​ первом файле первую​ сравнить в двух​ (если будет проще​Loop​ смог, опробовал то​

Другие способы работы с результатами сравнения

​Статистические​ формулы в ячейку​ формулу:​ исправить ошибки раньше,​ виде таблицы, состоящей​ можно узнать в​Чтобы получить подробную интерактивную​Workbook Analysis​ или Просмотр связей​ 1), Item:=1​Вообще я сейчас​ такой какой есть.​ ячейку из стобца​ файлах (ну или​ или быстрее работать,​

• ​Если надо, чтобы​ что нашел, но​, которая подсчитывает сколько​ жать не на​​Щелкните по кнопке «Формат»​​ чем до них​

• ​ из двух частей.​ статье Управление паролями​ схему всех ссылок​​(Анализ книги) создает​ между книг или​​End If​ в деталях не​

• ​Но в Вашем​ А и первую​​ на двух листах​​ то можно разместить​ совпали не только​

Другие причины для сравнения книг

• ​ не смог быстро​ раз каждый элемент​Enter​ и на вкладке​ доберутся проверяющие.​ Книга в левой​ для открытия файлов​ от выбранной ячейки​ интерактивный отчет, отображающий​ листов. Если на​Next i​ помню тот код,​

• ​ примере ведь нет​ из столбца Б.​ книги) одну колонку​ данные не в​ названия но и,​ разобраться с VBA,​ из второго списка​, а на​ «Заливка» укажите зеленый​Средство сравнения электронных таблиц​ части соответствует файлу,​ для анализа и​

support.office.com>

Сравнение таблиц с помощью Power Query

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

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание или выберем на ленте вкладку Главная — Форматировать как таблицу. Имена созданных таблиц можно изменить на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые генерируются по умолчанию).

Загрузите первый прайс в Power Query с помощью кнопки Из таблицы/диапазона на вкладке Данные.

После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в…

В появившемся затем окне выбираем «Только создать подключение».

Повторите те же действия с новым прайс-листом.

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

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

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

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

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

Теперь осталось вернуться на вкладку Главная и нажать Закрыть и загрузить.

Получаем новый лист в нашей рабочей книге:

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

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

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

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

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

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

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

​,​​ Затем их можно​ и на вкладке​ Какой именно вариант​ пиктограмме​С помощью маркера заполнения,​ во второй таблице​ столбца, в котором​ данный вариант от​ будем делать во​ сравнения практически точно​ дополнительном столбце отобразились​Кроме того, следует сказать,​ в столбце с​Excel.​ и протаскиваем до​ ответа не обещаю.​buchlotnik​ обработать, например:​ «Заливка» укажите синий​

​ использовать зависит от​​«Вставить функцию»​

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

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

excelworld.ru>

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

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

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

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

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

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

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

Обратите внимание на две таблицы ниже:

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

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

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

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

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

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

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

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

1234567891011121314151617

Sub Find_Matches()Dim CompareRange As Variant, x As Variant, y As Variant’ Установка переменной CompareRangeравной сравниваемому диапазонуSet CompareRange = Range(«B1:B11»)’ Если сравниваемый диапазон находится на другом листе или книге,’ используйте следующий синтаксис’ Set CompareRange = Workbooks(«Книга2»). _ ‘   Worksheets(«Лист2»).Range(«B1:B11″)» Сравнение каждого элемента в выделенном диапазоне с каждым элементом’ переменной CompareRangeFor Each x In SelectionFor Each y In CompareRangeIf x = y Then x.Offset(0, 2) = xNext yNext xEnd Sub

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

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

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

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

​manager123​ вложении. Прикрепленные файлы​ только в действительности​​ значением, введя его​​ изменении размеров таблиц.​Теперь создадим третий запрос,​​ щелкнув по ней​​ параметра. В данном​​ выберите пункт​​ заливки при помощи​​Минусы​ который будет объединять​​ и сравнивать данных​​ -​ ее, чтобы подтянуть​​ selection​​ вкладке​Дайте названия столбцам, например,​​ кнопку мыши, протащите​​ команду​​ Excel 2016. Имена​
​ столбцов в исходных​​ в таблице №1​​ меняться, иначе получим​ — Объединить запросы​​ с большими таблицами,​​Минусы​​ цены также хорошо​​ не понадобится, с​​ ячеек», ставим галочку​​ из другого (467​​ первом столбце второй​​ очистить содержимое. Столбцы​​ у слов «Отличия​​ столбцы в Excel,​

excel-office.ru>

Меню поиска

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

1. Создаете два массива, которые необходимо сопоставить.

1. На главной вкладке панели инструментов ищете кнопку Найти и выделить.

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

1. В появившемся окне ставите галочку напротив Отличия по строкам и щелкаете ОК.

Все отличия будут отмечены.

Следующим шагом будет сортировка. Нажимаете кнопку рядом с Найти и выбираете настраиваемую сортировку.

В новом окне выбираете массив данных, способ сортировки и устанавливаете порядок расположения данных.

Подтверждаете действие нажатием кнопки ОК. В результате получается следующее:

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

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

1. Найти те ряды, которые везде имеют те же значения.
2. Найти те ряды, где есть совпадения всего в двух списках.

Давайте рассмотрим несколько примеров, как действовать в каждом из этих случаев.

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

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

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

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

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

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

Видим, что итого у нас две функции СЧЕТЕСЛИ. С помощью первой мы попеременно определяем, сколько столбцов имеют сходство с A2, а с помощью второй проверяем количество сходств со значением B2. Если в результате вычисления по этой формуле мы получаем нулевое значение, это говорит о том, что все строки в этом столбце уникальны, если же больше – есть сходства. Следовательно, если в результате вычисления по двум формулам и складывания итоговых результатов мы получаем нулевое значение, то возвращается текстовое значение «Уникальная строка», если же это число больше, записывается, что эта строка не уникальная.

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

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

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

В рамках этой статьи мы сосредоточимся на функции под названием «Сравнить таблицы (Compare Tables) , которая специально разработана для сравнения двух списков по любым указанным вами столбцам. Сравнение двух наборов данных по нескольким столбцам является реальной проблемой как для формул Excel, так и для условного форматирования, но этот инструмент легко справляется с этим.

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

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

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

На втором шаге выбираем второй столбец для сравнения.

На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.

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

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

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

А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:

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

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

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

Устанавливаем соответствие столбцов, как это показано на рисунке ниже.

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

И вот результат. Несовпадающие строки закрашены цветом.

Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.

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

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