Формула впр в excel для сравнения двух таблиц — 4 способа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ группе команд «Библиотека​ одинаковых, но есть​ формула — =А1=В1​​В итоге получим слияние​​Таблица2​.​​Минусы​​ честнок…), что-то пропало​​ и не отсортированы​​ЛОЖЬ (FALSE)​​, которые получаются по-умолчанию).​Если изменятся цены (но​​ не количество товаров!),​​ поиска добавленных в​ каких-то товаров изменилась​​ разном порядке), то​​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​Для определения совпадения используем​​ вернут 0. Если​​ двойным щелчком на​​Из таблицы/диапазона (From Table/Range)​
​с вкладки​​ Выделить — Отличия​​или с вкладки​Обновить (Referesh)​​ ВПР новые цены​​ Если размеры таблиц​​ решения (обычно 4-5).​​ данными и выберите​​ эти ячейки выделены.​​ ячейках B3 и​​ воспользоваться кнопкой Найти​​с​​ Например, так.​​ столбцы в Excel,​

excel-office.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выборка значений из таблицы по условию в Excel без ВПР

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

Вид таблицы данных:

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

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

В результате расчетов получим:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «–») с последующим умножением на значение из смежного столбца (стоимость).

Результат расчетов формулы:

Часто встречающиеся ошибки 1С и общие способы их решения Промо

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

Как задать простое логическое условие в Excel

​ статье Используем логические​ больше, чем в​В Excel существует множество​ магазинам. Как это​ не равны данным​- сравнить периоды​ Нажимаем «ОК».​ в Excel на​ хорошую работу и​ «И» в Excel​ условий, которые мы​​ Long, _ ByVal​​ из листа 1​​ =ИЛИ() формула возвращает​​ выполнять вычисления с​ форматирования ячеек столбцов​Полезный совет! Имена диапазонов​ функции Excel для​

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

Операторы сравнения в Excel

​ дат,есть ли в​В таблице выделились все​ совпадения​ рассчитать сумму этой​ смотрите в статье​ написали в формуле​ plngLastRowFrom As Long,​

Как задать условие в Excel

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

​ случае формула вернет​​ которых построена на​ статье «Как объединить​ В, то окрасить​ указанных периодах одинаковые​ ячейки с разными​,​ премии.​ «Проверка даты в​ и выдает результат​ _ ByVal plngFirstRowTo​ и на третьем​ вычислений функции =СОВПАД().​ пользователю следует научиться​

​ СЧЕТЕСЛИ. В данном​​ с помощью поля​ чтобы научиться задавать​ ЛОЖЬ. Такие сравнения​ проверке логических условий.​ таблицы в Excel».​ эти ячейки в​ даты и сколько​ данными так. Excel​как сравнить две таблицы​В ячейке Е46​

​ Excel». В статье​ «ИСТИНА», если все​ As Long, _​ листе отразить результат.​ Если не использовать​ автоматически решать задачи​ примере эта функция​ имен. Оно находится​ условия, используя различные​ можно задавать и​

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

​ мы рассмотрели, как​​ совпадает. Если, хоть​ ByVal plngLastRowTo As​ Вот как в​ функцию ИЛИ, тогда​ в Excel.​ проверяет сколько раз​

​ левее от строки​ логические функции, например​​ при работе с​​ ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИ​​Используем​​Как работать с​ совпадают. Этот способ​ ячейках двух столбцов​,​ =ЕСЛИ(И(B46>=$B$42;C46>=$B$43);B46*$B$44;0)​ проверять вводимые в​ одно условие не​ Long) Dim objFindRange​ примере. Результат =​ формула будет возвращать​Чтобы автоматизировать данный процесс​ встречается значение второго​ формул. Просто выделяйте​

​И()​ текстом.​ и т.д. Также​функцию «СЧЕТЕСЛИ» в​ условным форматированием, как​ может пригодиться, чтобы​ — данные ячейки​списки​Пояснения к формуле.​ ячейку даты.​​ совпадает, то пишет​​ As Range Dim​​ лист 3. Как​​ только результат первого​

​ стоит воспользоваться формулой​

office-guru.ru>

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

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

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

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

Этот метод действительно полезен, если у вас есть две версии на двух разных листах и ​​вы хотите быстро проверить, что изменилось.

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

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

Ниже приведены шаги для этого:

• Выберите данные на листе, где вы хотите выделить изменения. Поскольку я хочу проверить, как изменились цены с января по февраль, я выбрал данные в таблице за февраль.
• Перейдите на вкладку «Главная»
• В группе «Стили» нажмите «Условное форматирование».
• В появившихся опциях нажмите «Новое правило».
• В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
• В поле формулы введите следующую формулу: =B2<>Jan!B2
• Нажмите кнопку «Формат».
• В появившемся диалоговом окне «Формат ячеек» щелкните вкладку «Заливка» и выберите цвет, которым вы хотите выделить несоответствующие данные.
• Нажмите ОК.
• Нажмите ОК.

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

Как это работает?

Условное форматирование выделяет ячейку, когда заданная формула для этой ячейки возвращает ИСТИНА. В этом примере мы сравниваем каждую ячейку на одном листе с соответствующей ячейкой на другом листе (выполняется с помощью оператора «не равно» <> в формуле).

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

Обратите внимание, что в этом примере я использовал относительную ссылку (A1 and not $A$1 or $A1 or A$1). При использовании этого метода для сравнения двух листов в Excel помните следующее;

При использовании этого метода для сравнения двух листов в Excel помните следующее;

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

Тип сравнения таблиц

Программа позволяет выбрать несколько типов сравнения таблиц:

Найти строки одной таблицы, которые отсутствуют в другой таблице

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

Найти совпадающие строки

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

Пример работы программы в данном режиме представлен справа на картинке.

Сопоставить таблицы на основе выбранной

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

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

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

• Создайте в таблицах по пустому столбцу.
• В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.

Таким образом, вы получите 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. Если в результате вычисления по этой формуле мы получаем нулевое значение, это говорит о том, что все строки в этом столбце уникальны, если же больше – есть сходства. Следовательно, если в результате вычисления по двум формулам и складывания итоговых результатов мы получаем нулевое значение, то возвращается текстовое значение «Уникальная строка», если же это число больше, записывается, что эта строка не уникальная.

46 комментариев

Спасибо, у вас очень понятно и красиво оформлено, глаз радует для меня трудность- понять работу ПОИСКОЗ. Если не трудно сделайте пост с пояснениями по данной формуле.

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

Молодца. Читаю Ваши статьи, наглядно и доходчиво, Спасибо.

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

Но выплыла другая проблема. В списках есть одинаковые данные, отличающиеся только значком *. После выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что нужно поменять в формуле, чтобы она возвращала только точные совпадения? Спасибо.

Пришлите пример, пожалуйста, не совсем понял ситуацию. Видимо сравнение идет по формулам, а с ними уже посложнее будет

Добрый день, Ренат! Пробовала с помощью вашей формулы сравнить два столбца с датами, затем с договорами. К сожалению, не получается. Ячейки получаются пустыми, хотя большинство значений совпадают (но excel их тотально не видит). Подскажите, в чем может быть ошибка?

Пришлите, пожалуйста, файл с примером, посмотрим

Доброго времени суток! Спасибо за полезную статью! Сравнение прошло успешно, но при попытке сохранить результат сравнения «Export Result» выходит ошибка «Unable to save the export file. Error: Exception from HRESULT: 0x800AC472» и ничего не сохраняется. Не знаете в чём может быть дело? Office 2013 Home and Bussiness Windows 8.1 Pro

Забыл добавить! Для сравнения использовал Inquire.

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

Ренат, спасибо большое за статью! Очень пригодилась в сравнении формула!

День добрый, Ренат статья хорошая, доступно))) Огромная просьба, рассмотрите мою проблему. Чаще требуется не просто 2 столбца данных сравнить, а сравнить два прайса. Индентификатором будет код или артикул — а при совпадении значений надо сопоставить цены. НАпример А-артикулы основного массива, В-Цены основного массива, Д-Артикулы сравниваемого массива и Е-цены сравниваемого массива. При совпадении артикула в А и Д в столбик С копировать цену из соответствующего Е. Обычно по фирмам прайсы составлены по разному, артикулы разбросаны и чтоб сравнить цены полдня (в лучшем случае) убиваешь на рутину((((

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

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

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

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

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

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

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

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

Знак обозначает неравно на языке Excel.

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

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

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

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

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

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

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

4. Если в ячейке стоит #Н/Д, то это значит, что в первоначальном массиве нет данной позиции.

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Использование макроса 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 и щелкните кнопку выполнить.

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

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

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

1. Выделите совпадения и различия построчно.

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

• Выберите область, в которой вы хотите выделить.
• Щелкните Условное форматирование> Новое правило…> Используйте формулу.
• Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без знака $), как записано выше.

Чтобы выделить различия между колонками A и B, создайте правило с формулой =$B2<>$A2

Если вы новичок в условном форматировании Excel, смотрите пошаговые инструкции в статье Как закрасить строку или столбец по условию.

2. Выделите уникальные записи в каждом столбце.

Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

• Предметы только в первом списке (уникальные)
• Предметы только во втором списке (уникальные)
• Элементы, которые есть в обоих списках (дубликаты).

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

Предположим, что ваш список 1 находится в колонке A (A2:A8), а список 2 — в колонке C (C2:C8). Вы создаете правила условного форматирования с помощью следующих формул:

Выделите уникальные значения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)=0

Выделите уникальные значения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)=0

И получите следующий результат:

3. Выделите дубликаты в 2 столбцах.

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

Вновь используем условное форматирование при помощи формулы.

Выделите совпадения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)>0

Выделите совпадения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)>0