Сравнение двух списков в excel с выделением отличающихся записей

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: www.planetaexcel.ru

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

Этот урок описывает, как сравнить два списка в Excel с помощью условного форматирования. Для примера возьмём два списка команд НФЛ (Национальная футбольная лига).

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

  1. Сперва выделите диапазон A1:A18 и дайте ему имя “firstList”.
  2. Затем выделите диапазон B1:B20 и назовите его “secondList”.
  3. Теперь выделите диапазон A1:A18.
  4. На вкладке Главная (Home) выберите команду Условное форматирование >Создать правило (Conditional Formatting > New rule).
  5. Выберите Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format).
  6. Введите следующую формулу:

=СЧЕТЕСЛИ(secondList;А1)=0
=COUNTIF(secondList,А1)=0
Задайте стиль форматирования и нажмите ОК.Результат: Команд “Miami Dolphins” и “Tennessee Titans” нет во втором списке.

  • Формула =СЧЕТЕСЛИ (secondList;А1) подсчитывает количество команд во втором списке, которые идентичны команде из ячейки A1. Если СЧЕТЕСЛИ(secondList;A1)=0, значит команды из ячейки A1 нет во втором списке. В результате Excel окрашивает ячейку голубым цветом.
  • Так как мы выбрали диапазон A1:A18 прежде, чем применили условное форматирование, Excel автоматически скопирует формулы в остальные ячейки.
  • Таким образом, ячейка A2 содержит следующую формулу:=СЧЕТЕСЛИ(secondList;А2)=0,ячейка А3:

=СЧЕТЕСЛИ(secondList;А3)=0 и т.д.

Чтобы выделить команды во втором списке, которые не входят в первый, выделите диапазон B1:B20 и создайте новое правило, используя формулу ниже:

Затем задайте стиль форматирования (оранжевую заливку) и нажмите ОК.Результат: Команд “Denver Broncos”, “Arizona Cardinals”, “Minnesota Vikings” и “Pittsburgh Steelers” нет в первом списке.

Источник: office-guru.ru

Сравнение данных в Excel на разных листах

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

Сравнение двух листов в Excel

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

Решить эту непростую задачу нам поможет условное форматирование. Для примера, возьмем данные за февраль и март, как показано на рисунке:

Чтобы найти изменения на зарплатных листах:

  1. Перейдите на лист с именем «Март» и выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
  2. В окне «Создание имени» для поля «Имя:» введите значение – Фамилия.
  3. Ниже в поле ввода «Диапазон:» введите следующую ссылку:
  4. Выберите инструмент «ФОРМУЛЫ»-«Присвоить имя» и в поле «Имя:» введите значение – Зарплата.
  5. В поле «Диапазон:» введите ссылку:
  6. Теперь перейдите на лист с именем «Февраль» и выделите диапазон ячеек B2:C12.
  7. А на панели «ГЛАВНАЯ» выберите «Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
  8. В поле ввода формул вводим следующее:
  9. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет.
  10. На всех окнах жмем ОК.

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

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

В определенном условии существенное значение имеет функция ПОИСКПОЗ. В ее первом аргументе находится пара значений, которая должна быть найдена на исходном листе следующего месяца, то есть «Март». Просматриваемый диапазон определяется как соединение значений диапазонов, определенных именами, в пары. Таким образом выполняется сравнение строк по двум признакам – фамилия и зарплата. Для найденных совпадений возвращается число, что по сути для Excel является истиной. Поэтому следует использовать функцию =НЕ(), которая позволяет заменить значение ИСТИНА на ЛОЖЬ. Иначе будет применено форматирование для ячеек значение которых совпали. Для каждой не найденной пары значений (то есть – несоответствие) &B2&$C2 в диапазоне Фамилия&Зарплата, функция ПОИСКПОЗ возвращает ошибку. Ошибочное значение не является логическим значением. Поэтому исползаем функцию ЕСЛИОШИБКА, которая присвоит логическое значение для каждой ошибки – ИСТИНА. Это способствует присвоению нового формата только для ячеек без совпадений значений по зарплате в отношении к следующему месяцу – марту.

Источник: exceltable.com

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

Если другие пользователи имеют право на редактирование вашей книги, то после ее открытия у вас могут возникнуть вопросы “Кто ее изменил? И что именно изменилось?” Средство сравнения электронных таблиц от Майкрософт поможет вам ответить на эти вопросы — найдет изменения и выделит их.

Важно: Сравнение электронных таблиц поддерживается только в Office профессиональный плюс 2013 или Office 365 профессиональный плюс.

Откройте средство сравнения электронных таблиц.

В левой нижней области выберите элементы, которые хотите включить в сравнение книг, например формулы, форматирование ячеек или макросы. Или просто выберите вариант Select All (Выделить все).

На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).

В диалоговом окне Compare Files (Сравнение файлов) в строке Compare (Сравнить) с помощью кнопки обзора выберите более раннюю версию книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

В диалоговом окне Compare Files (Сравнение файлов) в строке To (С чем) с помощью кнопки обзора выберите версию книги, которую хотите сравнить с более ранней.

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

Нажмите кнопку ОК, чтобы выполнить сравнение.

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

Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле “Compare” (Сравнить), а книга в правой части — файлу, указанному в поле “To” (С чем). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.

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

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

Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).

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

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

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

Вы можете экспортировать результаты в файл Excel, более удобный для чтения. Выберите Home > Export Results (Главная > Экспорт результатов).

Чтобы скопировать результаты и вставить их в другую программу, выберите Home > Copy Results to Clipboard (Главная > Копировать результаты в буфер обмена).

Чтобы отобразить форматирование ячеек из книги, выберите Home > Show Workbook Colors (Главная > Показать цвета книги).

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

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

Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

Источник: support.office.com

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

Microsoft Office Excel часто используется для ведения бухгалтерии, анализа финансовой деятельности предприятия и формирования прайс-листов. Поскольку все эти показатели меняются с течением времени, то необходимо постоянно отслеживать динамику изменений. Для более качественного анализа лучше всего сопоставлять значения двух массивов, и сегодня в статье разберемся, как сравнить два столбца в excel на совпадения.

Меню поиска

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

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

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

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

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

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

Важно! Чтобы зафиксировать отличия, необходимо сразу перейти в раздел Шрифт и изменить цвет текста или залить ячейку любым цветом. Ни в коем случае не нажимайте Enter и не щелкаете по рабочему листу, иначе выделение пропадет.

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

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

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

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

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

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

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

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

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

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

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

Функция

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

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

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

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

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

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

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

Источник: mir-tehnologiy.ru

Сравнение двух списков в excel с выделением отличающихся записей

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

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

Вот перед вами упрощённый учебный пример такой задачи.

Путём нехитрых развлечений с функцией ВПР , вы можете получить такой результат:

То есть мы нашли пересечение наших двух диапазонов там, где ВПР вернул значение. Отфильтровав по #Н/Д в каждой таблице, мы получаем список значений того, что есть слева, но отсутствует справа, и того, что есть справа, но отсутствует слева. Это максимум того, что можно выжать из стандартного подхода с ВПР.

Недостатки стандартного подхода:

  • Мы потратили много сил. Если таких таблиц у нас много, то такой метод не годится. Это и трудоёмко и слишком легко запутаться.
  • Как мы знаем, ВПР ищет первое совпадение и значит о том, что, например, в левой таблице два значения 040310475653, а в правой только одно, мы можем и не узнать, особенно, если таблицы большие.
  • В случае с #Н/Д мы также не будем понимать структуру аномалий наших данных. Например, то, что значение 40310307297 справа встречается дважды.
  • У нас не будет информации, сколько вообще уникальных значений встречается в каждой таблице.

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

Основные возможности предлагаемого инструмента:

  • Исчерпывающее сравнение двух списков с выявлением всех возможных аномалий в данных
  • Сравнение осуществляется как на месте, где расположены указанные пользователем диапазоны (вставляется колонка справа), так и на отдельном новом листе рабочей книги
  • Цветовое акцентирование результатов сравнения
  • Предоставление детальной статистики по результатам сравнения (вставляется в комментарий к ячейке заголовка столбца, где происходит сравнение)
  • Автоопределение столбца с данными при указании диапазонов (достаточно указать одну ячейку)
  • Две модели сравнения: простая и обычная.
  • Учёт регистра текста, если в этом есть необходимость
  • Учёт наличия / отсутствия заголовка у диапазонов
  • 2 типа сортировки
  • Возможность заменить стандартные статусы сравнения на пользовательские

Используемая концепция сравнения списков

Концепция очень проста и легка для понимания при минимуме усилий.

  1. 2 сравниваемых диапазона будем называть ЛЕВЫЙ и ПРАВЫЙ . Это очевидно и естественно, если таблицы располагаются на одном листе. В случае разных листов, левым диапазоном можно называть ту таблицу, чей рабочий лист располагается левее листа второй таблицы.
  2. Каждое значение в сравниваемых списках получит СТАТУС , означающий то, как данное значение соотносится с аналогичным значением во второй таблице и (!) с такими же значениями в своей таблице, если данное значение повторяется.
  3. Есть 2 набора статусов: упрощённый и обычный.
  4. Упрощенные статусы: BOTH , LEFT , RIGHT .
    • BOTH – значение есть в обоих столбцах. Например, если значение “5” встречается в левой таблице 2 раза, а в правой 3 раза, то все эти строки получат статус BOTH
    • LEFT – значение встречается только в левой таблице
    • RIGHT – значение встречается только в правой таблице
  5. Обычные статусы (вы можете выбрать, какой набор статусов будете использовать в форме управления данным инструментом):
    • Группа BOTH :
      • L1R1 – в левои и правом диапазонах есть по одному значению
      • LnRn – в левои и правом диапазонах есть по нескольку (более 1) значений с каждой стороны.
      • L1Rn – слева – одно значение, справа – несколько
      • LnR1 – слева – несколько значений, справа – одно
    • Группа LEFT :
      • L1R0 – в левом диапазоне одно значение, в правом такого нет
      • LnR0 – в левом диапазоне несколько одинаковых значений, в правом таких нет
    • Группа RIGHT :
      • L0R1 – в левом диапазоне нет таких значений, в правом – одно
      • L0Rn – в левом диапазоне нет таких значений, в правом – несколько (более одного)

Результаты работы инструмента

Вот как выглядят результаты сравнения примера из начала статьи

Мы видим, что получается следующая картина:

  1. В левом списке есть 12 значений, которые отсутствуют в правом (статус L1R0 )
  2. В левом списке задвоено значение 040310475653 (статус LnR1 )
  3. Только 3 значения совпали в списках по принципу “один к одному” (статус L1R1 )
  4. В правом списке есть 7 значений, которые отсутствуют в левом списке (статус L0R1 )
  5. В правом списке есть 4 строки (две по 2 значения), которых нет слева и которые задвоены справа (статус L0Rn )
  6. И слева, и справа есть 2 значения, которые встречаются единожды слева, но которые задвоены справа (статус L1Rn )

и режим объединенных списков:

Как видите, моя утилита всё разложила по полочкам!

Пользовательский интерфейс

Описание элементов управления:

  1. Списки для сравнения – Левый список и Правый список
    • Сравниваемые списки должны быть в составе одной книги Excel
    • Списки могут располагаться на разных листах книги
    • Если они располагаются на одном листе, то колонка левого списка должен быть действительно левее правой колонки
    • Рекомендуется заполнять данные поля ввода при помощи кнопок Выбор
    • Через кнопку Выбор достаточно указать одну ячейку нужной колонки, программа автоматически расширит ваш выбор на всю область в составе этого столбца, к которой относится указанная ячейка. В этой связи пустые ячейки в сравниваемых колонках рекомендуется заменять на какие-то текстовые константы, типа “Пусто”.
  2. Разное – Выделить цветом
    • Если указана данная опция, то колонки со сравниваемыми значениями и колонка со статусами получают стандартное цветовое акцентирование, образцы которого вы можете видеть ниже
    • Простые статусы:
    • Обычные статусы:
  3. Разное – Объединить списки
    • По умолчанию эта опция не выбрана и списки сравниваются в том месте, где они расположены. Справа от колонки с данными вставляется столбец со статусами сравнения.
    • Если эта опция выбрана, то создаётся новый лист, куда помещается таблица с объединенными уникальными значениями из сравниваемых списков. Данная таблица имеет 4 столбца: Значения , Кол-во слева , Кол-во справа , Статус .
    • Данный режим удобен для детального анализа всех аномалий в данных. В частности только так видны конкретные количества строк у статусов с буквой “n” . Например, LnRn или L0Rn .
    • Только в режиме объединения списка таблица может сортироваться, так как в проивном случае это могло бы повредить ваши данные – ведь указанный столбец может быть в составе большой таблицы, но определение координат этой таблицы (для сортировки) уже слишком выходит за рамки данного инструмента.
  4. Разное – Учёт регистра
    • Полезно при анализе текстовых списков, где может быть важен регистр текстовых значений.
  5. Разное – Статистика в комм .
    • При этом создаётся комментарий к ячейке, содержащей заголовок столбца со статусом сравнения данных.
    • Таких комментариев 2, если списки сравниваются на своих оригинальных местах, и один, если они объединены.
    • Комментарий выглядит примерно так
  6. Разное – Простые статусы
    • Осуществляется выбор между простыми и обычными статусами. Данные статусы были описаны выше.
  7. Разное – Есть заголовки
    • Указываем имеют ли ваши списки заголовки столбцов. По умолчания включено.
  8. Где сравниваем? – Оригинальное место или Отдельный лист
    • Настройка блокируется, если выбрана опция Объединить списки.
    • В противном случае вы в праве выбрать место самостоятельно.
  9. Сортировка – По значению или По статусу
    • Настройка блокируется (по умолчанию), если не выбрана опция Объединить списки, так как сотрировка осуществляется только на отдельном листе.
  10. Дополнительно – Альтернативные статусы
    • По умолчанию отключено. Включаем, если вы хотите по каким-то своим причинам использовать свои статусы.
  11. Дополнительно – Обычные статусы или Упрощённые статусы
    • Статусы сохраняются на скрытом листе той книги, где вы воспользовались этим инструментом, и в следующий раз подгружаются оттуда на форму. То есть вам не придётся их вводить постоянно.
  12. Сравнить списки
    • Кнопка, которую необходимо нажать для запуска процедуры сравнения после того, как вы выбрали все необходимые вам опции.

Скачать утилиту

Можно здесь. Текущая версия 0.05 от 09.12.2014.

Источник: perfect-excel.ru