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

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

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

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):

difference1.png

Число несовпадений можно посчитать формулой:

=СУММПРОИЗВ(--(A2:A20<>B2:B20))

или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))

Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences). В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

difference2.png

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
  • и т.д.

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

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):

difference5.png

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

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

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

difference4.png

Полученный в результате ноль и говорит об отличиях.

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

difference6.png

Выглядит страшновато, но свою работу выполняет отлично ;)

Ссылки по теме

 


Альберт
06.11.2012 23:17:08
Способ 5. Проверка вхождения элементов одного списка в другой
Большое спасибо за способ!!!!
Ато целый день сидел никак не мог сделать.
Что только не устанавливал, оказывается всё так просто))).
Светлана
06.11.2012 23:17:55
Помогите, пожалуйста. Использую способ 5. В одном столбце 500 текстовых значений. В другой столбец попадает текст, обработанный макросом "выпадаюший список". Сравниваю два этих солбца (СЧЁТЕСЛИ) . В них есть заведомо одинаковые ячейки с текстом , а формула выдает мне 0 ( не находит) .При этом, если вручную перебить заново значение какой-либо ячейки в первом столбце, тут же проставляет 1 (находит). Форматы ячеек в столбцах вроде бы одинаковые, а в чём дело не пойму?.
06.11.2012 23:20:37
Дело может быть в лишних невидимых пробелах или в латинице - английскую "С" от русской "С" внешне не отличить, а для Excel это уже разные значения.
Игорь
06.11.2012 23:19:30
А я взял да и написал свою утилиту для сравнения. Ибо обычно алгоритмы сравнения или не описаны или убоги. Только приходится из Excel копировать в текстовые файлы данные, ну да ладно. Зато можно обрабатывать просто огромные списки по меркам Excel. Недели 2-е убил на это дело... просьба администрации не пинать за ссылку.
http://fobosworld.ru/programs/raznoe/68-compare
06.11.2012 23:21:01
Пинать никто не будет, совсем даже наоборот. Спасибо, что поделились.:)
Ольга
06.11.2012 23:20:02
Спасибо большое автору! Все очень понятно.
23.04.2013 03:50:17
Скажите пожалуйста, а можно добавить третий список, в котором будет происходить сравнение двух первых списков, при этом первый список берется за основу, а из второго выбираться те значения, которых нет в первом и добавляются в третий список? Ну очень нужно…
10.05.2013 22:49:13
Можно просто объединить два диапазона данных в один и удалить потом дубликаты (Данные - Удаление дубликатов).
30.05.2013 13:16:06
Добрый день.
У меня возник вопрос. Есть данные в трех столбцах. Необходимо их сравнить построчно и выделить ячейку с наименьшим значением цветом. При использовании формулы(=$A1=МИН($A1:$C1)) в условном форматировании для всего диапазона, выделяется вся строка. Данная формула работает, но только если выделять колонки отдельно и каждой прописывать это правило. Собственно вопрос в том, что можно ли описать данную операцию одним условием при выделении всего диапазона(A1:C10 - условно) сразу?
Заранее спасибо.
30.05.2013 14:00:04
Юрий, просто уберите доллар перед первым А1, т.е. нужно =A1=МИН($A1:$C1)
30.05.2013 14:05:38
Спасибо, слона то я и не заметил.
30.05.2013 17:13:47
А верна ли в моем случае формула подсчета ячеек, которые удовлетворяют этому условию(т.е. подсчет в столбце количества ячеек имеющих минимальные значения в строке)? Что-то не работает.
=СЧЁТЕСЛИ(B7:B97;B7=МИН($B7:$J7))
Считаю количество минимальных цен у нескольких поставщиков.
Спасибо.
31.05.2013 16:28:22
Условие у функции СЧЕТЕСЛИ должно быть текстовой строкой. Туда нельзя вставлять логические выражения, к сожалению. Посчитайте минимум в отдельной ячейке и вставьте адрес этой ячейки в качестве второго аргумента функции СЧЁТЕСЛИ.
31.05.2013 16:34:19
Благодарю за совет, попробую.
16.07.2013 20:20:55
Когда делаю способом1 почему-то по всей колонке выдает "ИСТИНА", даже там где должно быть "ЛОЖЬ".
Кликаю по неправильной ячейке "ИСТИНА" - значение меняется на верное - "ЛОЖЬ".
Как сделать так, чтобы сразу было видно где значение "ИСТИНА", а где "ЛОЖЬ"?
21.07.2013 12:02:18
Наверняка у вас включен ручной пересчет формул. Посмотрите на вкладке Формулы - Вычисления. Установите Автоматически.
22.07.2013 09:35:18
Подскажите пожалуйста. Необходимо сравнить два диапазона А1:С20 и Е1:G20. В диапазоне E1:G20 выделить те ячейки, которые не совпадают с диапазоном А1:C20 с помощью условного форматирования. При написании формулы =Е1=А1 все Ок, но я не могу понять какую задать формулу для всего диапазона???
22.07.2013 20:27:01
Выделить первый диапазон, создать правило условного форматирования с формулой и ввести
=СЧЁТЕСЛИ($E$1:$G$20;A1)=0
24.10.2013 17:32:47
Подскажите пож., нужно сравнить два текстовых диапазона А1:А20 и В1:В20. Текстовые значения отсортированны в разном порядке, нужно чтобы в столбце С1:С20 указывались те значения которых нет в столбце А1:А20, но есть столбце В1:В20. Формула если(еошибка(поискпоз(В1;А$1:А&20;0));"";В1) выводит только совпадающие значение, а нужно наоборот. Помогите пож. что не так.
02.12.2013 20:39:12
Чем Способ 5 не подходит? :)
27.11.2013 11:13:40
Добрый день, сравниваю диапазоны 5-м способом. Результаты неутешительны - 1 - там где надо и не надо, и тоже самое с 0(((( Сравниваю В2:В1683 с А2:А1530. Формула выглядит следующим образом: =СЧЁТЕСЛИ(B2:B3211;A2:A4370). Одновременно выдает ошибку "формула не охватывает смежную ячейку". А так же протаскивание формулы по всему диапазону осуществляется с автоматическим смещением ячеек в формуле на 1 шаг, а именно =СЧЁТЕСЛИ(B3:B3212;A3:A4371) и каждая последующая так же смещается, что делает затруднительным использование данного способа при больших диапазонах. Подскажите, пожалуйста, где моя ошибка. Спасибо.
02.12.2013 20:37:59
Второй аргумент функции СЧЕТЕСЛИ - одна ячейка с критерием, а не диапазон. Посмотрите файл примера повнимательнее, пожалуйста.
09.01.2014 12:57:32
Подскажите: есть два списка, надо выяснить какие элементы есть в первом списке, но нет во втором (исчезли), а так же какие есть во втором, но отсутствуют в первом (добавились за период). идеале- в отдельный столбец чтоб отбирались убывшие элементы, а в другой добавившиеся...
09.01.2014 13:27:26
Елена, а чем вам Способ 5 для этого не подходит? И фильтруете потом 1 или 0, чтобы увидеть только новые или старые элементы.
А если хочется, чтобы все само происходило и в отдельный список разница выгружалась, то посмотрите функцию сравнения диапазоновиз моей надстройки PLEX.
15.05.2014 03:06:19
Подскажите пожалуйста, как решить такую задачу.

Три столбца A,В и С. В столбце "А" список слов и выражений. В столбце "В" список слов и выражений больший, чем в столбце "А". Нужно в столбец "С" вписать формулу, которая будет сравнивать значени ячейки столбца "В" со всеми ячейками столбца "А". При совпадении значений в ячейках столбцов "А" и "В", в ячейке справа (столбец "С" появляется знак "*", при несовпадении ячейка справа остается пустой.

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

С этой задачей отлично справляется формула вида:
=ЕСЛИ(ЕОШИБКА(ПОИСК(A$1;B17));"";"*") Но она сравнивает ячейки столбца "В" только с одной ячейкой столбца "А", а вот как сделать, чтобы она проверяла все ячейки столбца "А" я не знаю, но решение этого вопроса решило бы всю задачу.

Подскажите пожалуйста как можно это осуществить. Спасибо!
18.06.2014 11:36:35
Как проверить текстовые значения двух разных столбцов с приблизительными значениями?
13.04.2015 10:06:50
Добрый день! Помогите, пожалуйста! Есть таблица, с кодами номенклатур и неделями, нужно понять сколько было изменений в периоде.
Можно это сделать с помощью формул? Спасибо!
Rua
19.05.2015 16:05:51
Здравствуйте! Помогите, пожалуйста, решить такую задачу: нужно сравнить значения (ФИО) и присвоить в отдельном столбце уникальные идентификаторы (1, 2, 3 и т.д.) уникальным ФИО (то есть, одинаковые ФИО должны иметь одинаковые идентификаторы).
23.06.2015 11:26:38
Здравствуйте! есть три столбца, это "артикул" "полугодие" и "количество" сравнить надо с другой таблицей, где так же "артикул" "полугодие" и "количество". Артикула могут повторяться, получается нужно взять сумму "артикула" определенного "полугодия" и сравнить с суммой "артикула" такого же "полугодия" но из другой таблицы. важно итоговое расхождение (т.е + или - по количеству) Пожалуйста подскажите как это можно реализовать, спасибо.
07.02.2016 15:10:24
Дмитрий, вам нужно сначала просуммировать данные по артикулам (сводной таблицей или функцией СУММЕСЛИ), а потом уже сравнивать любым подходящим способом из этой статьи.
05.02.2016 14:48:22
Добрый день, Николай! Огромное спасибо за формулу массива, действительно красивое решение). Пересмотрел Ваше видео несколько раз, попытался реализовать этот способ, до последнего момента подстановки формулы из лишнего столбца все работает и результат корректен. Как только делаю подстановку и ввожу формулу массива результат искажается, в моем случае из 7 различий остается только 5. Никак не могу понять в чем дело... Офис стоит 2016, насколько я понял такой же как и у Вас. Может еще в чем-то быть проблема? Заранее спасибо
07.02.2016 15:09:20
Ильшат, может про закрепление долларами абсолютных ссылок не забыли? Скачайте приложенный файл с примером - посмотрите как там сделано.
08.02.2016 09:19:08
Спасибо, Николай, не забыл. Изначально скачивал Ваш пример и копировал формулу массива, подставив свои диапазоны, корректно не получилось. Потом уже решил по шагам из видео все делать. Пока ошибку не нашел)
15.03.2016 22:50:10
Николай добрый вечер! подскажите пожалуйста!
Сравниваю два списка,прайс,ассортимент с помощью варианта 2, выделил уникальные,редактировал нужный список,необходимо скопировать.но он вставляется в условном форматировании в цвете а как это все отменить чтобы нормально копировалось? спасибо!
19.03.2016 21:01:44
Добрый день. У меня такая ситуация.... Есть папка с товаром за год . В этой папке 12 папок товара по месяцам. В каждой папке 20-22 книги ексель с названием по датам. В этих книгах от 1 до 15 листов а в каждом есть коды товара. При поступлении нового товара мне нужно проверить, не был ли этот товар раньше у нас. А на данный момент мне нужно сверить товар за весь год, не был ли тот же товар у нас дважды (напр. коды товара с апреля, не совпадают ли с кодами тов. за июль ) . Каждая книга называется датой , каждый лист - поставщик.
Нельзя ли создать сводную табл. из такого большого кол. книг и потом отфильтровать дубликаты ?
Или есть другие способы сверивания данных ?
За каждый совет большое спасибо ! ЮРА.
06.04.2016 18:53:36
при идентичности все понятно. Но если нужно сравнить когда в одном столбце есть данные как идентичные так и другие. тогда как их индифицировать ?
18.07.2016 14:05:57
Добрый день, Николай!
Считаю ваш сайт очень полезным и информативным, он не раз помогал мне в решении трудных задач.
Но сейчас столкнулся с проблемой, решение которой не смог найти. Суть проблемы в том, что когда-то мной была создана таблица и к ней был применен расширенный фильтр как было описано вами в теме "Расширенный фильтр и немного магии", в результате поиска по этой таблице получается список "A". Вопрос, можно ли этот отфильтрованный список "A" сравнить с еще тремя списками "B", "C" и "D", чтобы в отдельной ячейке появилось название списка в котором максимальное количество совпадений со списком "A"?
Заранее спасибо!