Введение в задачу

При работе в 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.

Как установить данную надстройку

Инструкция для MS Excel 2013 (или 2010)

Инструкция для MS Excel 2007

Читайте также:

• Извлекаем список уникальных значений из необработанных данных

• Нечёткий поиск

• Формирование случайных списков на основе справочника