Как сравнить два списка в Excel? (с использованием 6 лучших методов)

Сравните списки клиентов, чтобы узнать о них больше

Инструкция

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

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

  1. Только уникальные значения без совпадений в списках — алгоритм проверяет два списка и удаляет дубли, отображая только уникальные значения;
  2. Только совпадения в списках — алгоритм анализирует списки и выводит в результатах только найденные совпадения;
  3. Только уникальные значения первого списка — алгоритм сравнивает первый список со вторым и выводит строки из первого списка не содержащиеся во втором;
  4. Только уникальные значения второго списка — алгоритм сравнивает второй список с первым и выдает в результатах значения из второго списка не содержащихся в первом.

Способ № 1: сравнить в базе 1С

На любой платформе 1С есть типовая обработка «Сравнить файлы». Находится в верхнем левом углу: «Главное меню — Файл — Сравнить файлы». 

dd3036acaf9f75368af42a87a3e82a7b.png

Она позволяет сравнить содержимое файлов формата .txt и .mxl. Другие форматы не принимает и выдает сообщения: «Ошибка при выполнении файловой операции» и «Бинарные файлы различны».

8ae915af1ab873e41d7038ccd6fd6599.png 

Чтобы начать работать, добавьте сопоставляемые файлы в окошки «Сравнить» и «С». Укажите «Как» сравнить: табличный документ, текстовый документ или двоичное сравнение. Нажмите клавишу «ОК». 

ea45679f2448e56e683e7874d2c255a0.png

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

c38a140c0c4a45802e2de40540dc5bca.png

На верхней панели инструментов доступны следующие команды 0b1c7fcf9acc7c678d62abd1ca389cd5.png:

290caf7186e59267f37345d15ef98abc.png позволяют переходить по различиям от текущего значения к следующему и обратно, в это время в основном окне будет выделена ячейка с отличием; 

901b2636aa7d413b5607f1d3e8e1b8ef.png меняет порядок сравнения: если ее нажать, то правый лист окажется слева, а левый — справа; 

5040501e27e2993e30907fb061b2b73f.png — настройка ячеек по цветам.

1f2d4c06a01017db4ddbd4abce9cc2c5.png

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

b6c4a20981ad0c35ee8be273934a2b9f.png

Плюсы: 

  • не требует дополнительного программного обеспечения;

  • моментально показывает все изменения без предварительных настроек;

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

Минусы: 

  • ограниченное количество форматов — работает только с .txt и .mxl. Нельзя вставить Excel-файлы; 

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

  • нет дополнительных формул, например, чтобы вывести сумму по колонке. 

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

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

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

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

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

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

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

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

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

  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. И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими. Как в Excel сравнить два списка

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

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

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

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

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

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

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

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

Это простой вариант. Предположим, у нас такие списки.

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

Чтобы определить, какое количество раз значения не сошлись, можно с использованием формулы: =СУММПРОИЗВ(—(A2:A20<>B2:B20)). Если по итогу мы получили 0, это говорит о том, что два перечня одинаковые.

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

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

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

Дополнительно

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

  1. Автоматическое удаление лишних пробелов — двойной пробел, пробел в начале и в конце строки.
  2. Чувствительность к регистру — приравнивает «Петров» и «пЕтРов» к дублям.

Формула сравнения.

Это самый простой способ соотнесения таблиц в Excel, который позволяет идентифицировать в них ячейки с разными значениями. 

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

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

=G3=C3

Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).

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

=G3=Лист2!C3

Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?

Можно подсчитать общее количество расхождений и сразу вывести это число где-нибудь отдельно.

=СУММПРОИЗВ(–(C3:C25<>G3:G25))

или можно сделать это формулой массива

{=СУММ(–(C3:C25<>G3:G25))}

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

Понимает основные типы контактов

Sergey Ivanov

Фитофарм (аптечная сеть)

сухонская, нет, мск мск, 11 дом, 89 квартира

моб 9168-233-454

serega.ivnv@mailюг

Сергей Иванов

✓ Фитофарм

✓ г. Москва, ул. Сухонская, дом 11, кв. 89

✓ +7 916 823-34-54

✓ serega.ivnv@mail.ru

Сравнивает с учетом ошибок, опечаток и вариантов написания.

В процессе находит и объединяет одинаковые записи (дубли).

Обратная связь

Друзья, буду благодарен за любую обратную связь. Мне важно знать как работает инструмент и как его можно улучшить. Пишите комментарии на info@konstantinbulgakov.com. Добавляйте ссылку на этот и другие инструменты сайта в закладки и делитесь с коллегами.

1 копейка за запись

Например, 2 списка по 5 000 записей = 100 ₽

Сервис сначала покажет статистику: сколько пересечений в файлах. А деньги возьмет, только если решите скачать результат.

Сравнение с помощью надстройки Inquire

     Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire, которая позволит проанализировать и сравнить два файла Excel. Этот способ хорош, когда у вас есть необходимость сравнить два файла, в случае, когда ваш коллега работал над книгой и ввел некоторые изменения. Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire.

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

     Был рад вам помочь!

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

 

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...