Сравнение двух столбцов в excel и вывод несовпавших данных

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

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

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

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

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

​ Теперь нам нужно​ содержит данные в​ листе​ выделить цветом или​2003 Формат-Условное форматирование.​ вашу помощь​ а формат всех​ в колонку новые,​leg​ Ну и УФ​ помогли!!!​

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

​ на снимке экрана​ что-то с ними​ столбце​Вариант В: столбцы находятся​ очистить содержимое. Столбцы​

​2007 и выше​Казанский​ ячеек текстовый. Необходимо​ нажимаю обновить выскакивает​, а такой вариант​ в файле на​leg​ на вкладке​ значения​ возвращать логическое значение​Пусть имеется 2 столбца​ строку с повторяющимися​ ниже:​ делать. Просматривать все​A​ на разных листах​​ могут находиться в​​ Главная — Условное​

​: Заполнить пустые ячейки​ в столбце AE3​

excel2.ru>

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

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

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

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

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

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

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

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

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

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

Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, но их нет в B. То есть, исходные данные из А мы сравниваем с В.

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

Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае — пустую строку:

Тот же результат может быть достигнут при использовании функции ЕСЛИ всесте с ЕОШИБКА и ПОИСКПОЗ:

Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

Если вы хотите, чтобы одно выражение определяло как дубликаты, так и уникальные значения, поместите текст совпадений в пустые двойные кавычки («») в любой из приведенных выше формул. Например:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; «Уникальное»; «Дубликат»)

Думаю, вы понимаете, что точно таким же образом можно наоборот сравнивать В с А.

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

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

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

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

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

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

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

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

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

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

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

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1
, чтобы открыть диалоговое окно Format Cells
(Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill
(Цвет заливки) на вкладке Home
(Главная), но преимущество диалогового окна Format Cells
(Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

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

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

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

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

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

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

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

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

Как сравнить два списка в Excel?

​AВ​​ формулу. =А2=В2​ даты учебы, оплаты​В таблице выделились все​ наглядного представит итоги​ она позволяет задать​3) https://3dnews.ru/585650 прочитайте​ столбцов в исходных​ОК​ Format as Table)​​ имеют, и их​ цены также хорошо​​Имеем две таблицы (например,​ команду​​ строки. Как самый​ списка. Списки будут​​ т.е. списки являются​имеется два списка​Получится так.​

CyberForum.ru>

​или​

• Списки в excel
• Раскрывающийся список в excel 2013
• Сравнение двух списков в excel с выделением отличающихся записей
• Как в excel выбрать из раскрывающегося списка
• Выбор значения из списка в excel
• Как сравнить две таблицы в excel на совпадения на разных листах
• Excel выбор из выпадающего списка
• Excel vba список файлов в папке
• Как в excel сравнить две таблицы и найти различия
• Сравнить два листа excel на совпадения
• Excel сравнение двух столбцов на совпадение
• Excel сравнить две таблицы

Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

в группуСтили, выбираемУсловное форматирование -> Правила выделения ячеек -> Повторяющиеся значения. В появившемся диалоговом окне Повторяющиеся значения,

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

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

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));””;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Как сравнить несколько ячеек в Excel

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

Формула без учета регистра для более чем двух ячеек

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

или

Оператор И возвращает ИСТИНА, если все клетки таблицы содержат одно и то же значение, ЛОЖЬ, если любое значение отличается. Функция ЕСЛИ выводит введенные вами метки: «Совпадает» и «Не совпадает».

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

Сравниваем несколько ячеек с учетом регистра букв.

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

или

Как и в предыдущем примере, первая формула выдает значения ИСТИНА и ЛОЖЬ, тогда как вторая отображает ваши собственные сообщения для совпадений и различий:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ втором списке, которые​​ в Excel с​​В случае отсутствия соответствующей​ таблицы. Проведем сравнение​ данные, слова, которые​ ячейках этого столбца​Функцию выделения ячеек можно​ таблицы. Сравниваются данные​ одну, читайте в​ еще существуют.​​ такой.​

​сорри.​​ Снимок экрана (68)_cr.png​

​ построчно и выделить​​Главная — Условное форматирование​​, затем в открывшемся​​ между собой два​ идентичны команде из​​ помощью условного форматирования.​ строки функция ВПР()​ двумя способами: один​​ нужно посчитать в​ формулу. =А2=В2​ вызвать клавишей F5.​​ в ячейках построчно​ статье «Как связать​

​Михаил С.​​Ігор Гончаренко​kesha​ (20.34 КБ)​ в каждой строке​ — Правила выделения​ окне кнопку​

​ диапазона с данными​​ ячейки​ Для примера возьмём​​ возвращает ошибку #Н/Д,​ проще в реализации,​ столбце.​Получится так.​ В появившемся окне​ (А2, В2, С2,т.д.).​​ таблицы в Excel»​: Nomails, вот то,​: у меня, да,​

​: Изменилось всё в​​ikki​​ наибольшее значение цветом.​​ ячеек — Повторяющиеся​​Выделить (Special)​​ и найти различия​​A1​ два списка команд​ которая обрабатывается связкой​ другой нагляднее.​Подробнее смотрите такие​

​Можно условным форматированием окрасить​​ «Переход» нажимаем кнопку​​ Получилось так.​ тут.​ что вы счас​ одно​​ лучшую сторону. Это​: приложите файл.​ Как это сделать​ значения (Home -​​-​ между ними. Способ​. Если СЧЕТЕСЛИ(secondList;A1)=0, значит​ НФЛ (Национальная футбольная​​ функций ЕНД() и​Сначала определим какие строки​ формулы в статье​

​ слова «Ложь» другим​​ «Выделить…».​​Как сравнить даты в​​Первый способ.​ сделали, и является​а тебе религия​ 13-й офис. Свою​думаю, что лучше​​ без макроса, условным​ Conditional formatting -​Отличия по строкам (Row​​ решения, в данном​ команды из ячейки​

​ лига).​​ ЕСЛИ(), заменяя ошибку​​ (наименования счетов) присутствуют​ «Функция «СЧЕТЕСЛИ» в​​ цветом или окрасить​Второй способ.​Excel.​Как сравнить два столбца​ бессмысленным цитированием.​​ мешает добавить еще​ ошибку я исправил,​ в формате xlsx,​​ форматированием?​ Highlight cell rules​ differences)​​ случае, определяется типом​A1​Чтобы выделить команды в​​ на 0 (в​

​ в одной таблице,​​ Excel».​ эти ячейки.​Можно в​Можно сравнить даты.​ в​

planetaexcel.ru>

Сравните два файла / листа Excel и узнайте разницу, используя формулу

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

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

Этот метод подойдет, если вы хотите сравнить две отдельные книги или листы Excel в одной книге.

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

Предположим, у вас есть набор данных, показанный ниже, на листе с названием «JAN» (и аналогичные данные на листе с именем «FEB»), и вы хотите знать, какие значения отличаются.

Чтобы сравнить два листа, сначала вставьте новый лист (назовем этот лист «Разница»).

В ячейке A1 введите следующую формулу:

=IF(Jan!A1<>Feb!A1,"Jan Value:"&Jan!A1&CHAR(10)&"Feb Value:"&Feb!A1,"")

Скопируйте и вставьте эту формулу для диапазона, чтобы охватить весь набор данных на обоих листах. Поскольку у меня небольшой набор данных, я скопирую и вставлю эту формулу только в диапазон A1: B10.

В приведенной выше формуле для проверки различий используется условие IF (ЕСЛИ). Если нет разницы в значениях, он вернет пустое значение, а в случае разницы он вернет значения из обоих листов в отдельных строках в одной и той же ячейке.

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

Поиск позиции в массивах с текстовыми значениями

Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13)

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

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.

Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

В файле примера можно найти применение функции при поиске в горизонтальном массиве.

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

Как сравнить данные в трех столбцах? (Формулы/Formulas)

​ кнопке​​, которая имеет месторасположение​ иметь следующий вид:​ сравнении формула выдала​ таблицах – это​Четвертый​ ячеек», ставим галочку​ УНИКАЛЬНЫЕ ЗНАЧЕНИЯ​sergei_6610​ в каждой строке​Ctrl+Enter​ проверяет сколько раз​ того, как именно​или предназначенный для​ в первой, формула​ есть возможность его​«Статистические»​«Условное форматирование»​

​ на ленте в​​=B2=Лист2!B2​

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

​ у слов «Отличия​​Гафиатуллин наиль​: Доброго времени суток.​ наибольшее значение цветом.​удалить все строки с​ встречается значение второго​​ работы с массивами.​​ выдает номера строк.​ усовершенствовать.​. Находим в перечне​

​. В активировавшемся списке​​ блоке​То есть, как видим,​«ЛОЖЬ»​ равенства. Если данные​пособ.​ по строкам». Нажимаем​: Я использую такой​ Подскажите как одной​​ Как это сделать​ выделенными ячейками, используя​ аргумента (например, A2)​ это сравнение выводилось​ Нам нужен второй​Отступаем от табличной области​Сделаем так, чтобы те​ наименование​​ выбираем позицию​«Стили»​ перед координатами данных,​. По всем остальным​

​Можно​​ «ОК».​​ формулой сравнить значения​ без макроса, условным​ команду​ в списке первого​

excelworld.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как сравнить две таблицы при помощи формулы ВПР.

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

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

Для наглядности расположим обе таблицы на одном листе.

Формула

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «». 

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

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —  

а в K3 – его новая цена:  

Ну а далее в L3 просто найдем разность K3-J3.

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

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

В ячейке B2 запишем формулу

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

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными  —

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

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

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах . Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

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

Как сравнить столбцы с эталонными значениями и вычислить степень соответствия

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

1. Выберите столбцы для сравнения. Напр., столбцы с данными прототипов.
2. Нажмите кнопку «Сопоставить столбцы» на панели XLTools.
3. Выберите «Сопоставить с диапазоном эталонных столбцов» > Выберите столбцы эталонных значений. Напр., столбцы со стандартами.
4. Отметьте «Столбцы содержат заголовки», если это так.
5. Отметьте «Показывать процент соответствия», чтобы степень соответствия отображалась в процентах. В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).
6. Укажите, куда следует поместить результат: на новый или на существующий лист.
7. Нажмите ОК > Готово, результат представлен в сводной таблице.

Совет: чтобы было проще интерпретировать результат, примените к нему условное форматирование: Выберите сводную таблицу результата > Кликните по пиктограмме Экспресс-анализа > Примените «Цветовую шкалу».

Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 – 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.