Как Сравнить 2 Списка в Excel и Выявить Различия • Пользовательский интерфейс
Как сравнить два списка на совпадения. Как сравнить два столбца в Excel — методы сравнения данных Excel
При работе в Excel достаточно часто встречается задача, заключающаяся в сравнении разного рода списков значений. Для сравнения диапазонов значений вообще и столбцов значений в частности могут использоваться такие стандартные средства Excel, как условное форматирование и функции. Кроме того, решать такие задачи можно при помощи макросов VBA и надстроек для Excel на их основе.
Надстройка для сравнения значений в двух диапазонах Excel
1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;
2. находить элементы диапазона №1, которых нет в диапазоне №2;
3. находить элементы диапазона №2, которых нет в диапазоне №1;
4. находить элементы диапазона №1, которые есть в диапазоне №2;
5. находить элементы диапазона №2, которые есть в диапазоне №1;
6. выбирать один из девяти цветов заливки для ячеек с искомыми значениями;
7. быстро выделять диапазоны, используя опцию «Ограничить диапазоны», при этом можно выделять целиком строки и столбцы, сокращение выделенного диапазона до используемого производится автоматически;
8. вместо сравнения числовых значений использовать сравнение текстовых значений при помощи опции «Сравнить числа как текст»;
9. сравнивать значения в ячейках диапазона, не учитывая лишние пробелы;
10. сравнивать значения в ячейках диапазона, не учитывая регистр.
Как сравнить два столбца с использованием макроса (надстройки) для Excel?
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в , корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Использование условного оператора ЕСЛИ
Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:
Разместите оба столбца для сравнения в колонках A и B рабочего листа.
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в , вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.
Использование макроса VBA
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.
Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_ Matches и щелкните кнопку выполнить.
После выполнения макроса, результат должен быть следующим:
Использование надстройки Inquire
Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.
Часто стоит задача сравнить два списка элементов. Делать это вручную слишком утомительно, и к тому же нельзя исключать вероятность ошибок. Excel упрощает эту операцию. В данном совете описывается метод с применением условного форматирования.
На рис. 164.1 приведен пример двух многостолбцовых списков имен. Применение условного форматирования может сделать различия в списках очевидными. Эти примеры списков содержат текст, но рассматриваемый метод работает и с числовыми данными.
Первый список — А2:В31 , этот диапазон называется OldList . Второй список — D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.
Начнем с добавления условного форматирования к старому списку.
- Выделите ячейки диапазона OldList .
- Выберите .
- В окне Создание правила форматирования выберите пункт под названием Использовать формулу
- Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
- Нажмите кнопку ОК.
Ячейки в диапазоне NewList используют подобную формулу условного форматирования.
- Выделите ячейки диапазона NewList .
- Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
- В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
- Нажмите кнопку ОК.
В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.
Обе формулы условного форматирования используют функцию СЧЁТЕСЛИ . Она рассчитывает, какое количество раз определенное значение появляется в диапазоне. Если формула возвращает 0, это означает, что элемент не входит в диапазон. Таким образом, условное форматирование берется за дело, и цвет фона ячейки меняется.
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города
Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду.
Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН.
Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ
Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9
Расширенное сравнение двух колонок (списков) — Макросы и программы VBA — Excel — Каталог статей — Perfect Excel
- Выделите ячейки диапазона OldList .
- Выберите .
- В окне Создание правила форматирования выберите пункт под названием Использовать формулу
- Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
- Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
- Нажмите кнопку ОК.
Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра.
Как сравнить два файла Excel
Умение сравнивать два файла Excel важно в любой среде, где многие люди вносят изменения в один и тот же файл. Это распространено в бизнес-среде или в случае, когда файлы Excel совместно используются в облаке, и многие люди имеют доступ к внесению изменений.
К счастью, есть несколько способов сравнить два файла Excel. В некоторых случаях вы даже можете объединить изменения, сделанные в двух разных файлах Excel, в один файл.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365 и Excel для Mac
Как сравнить два файла Excel
Если у вас есть два файла Excel с несколькими листами, самый простой подход — выполнить полное сравнение листов с помощью сторонних инструментов.
В Интернете вы можете найти несколько инструментов, которые помогут вам сравнить файлы Excel, но есть и несколько, которые вы можете использовать бесплатно. Одним из них является Spreadsheet Compare , доступное от SourceForge.
Загрузите и запустите приложение, которое установит надстройку в приложение Excel.
Spreadsheet Compare работает на всех версиях Excel после Excel 2000. Он совместим только с операционной системой Windows.
Чтобы использовать Spreadsheet Compare для сравнения двух файлов Excel:
Откройте оба файла Excel, которые вы хотите сравнить, и выберите меню « Надстройки» . В этом меню вы увидите три параметра: Полное сравнение, Быстрое сравнение и Сравнение диапазона.
Выберите Полное сравнение . Вы увидите всплывающее окно с обоими файлами в двух полях. Если файл до внесения изменений находится не в верхнем поле, просто нажмите кнопку « Поменять» . Затем выберите Далее .
В следующем окне вы можете настроить, как выполняется сравнение. Вы можете изменить, где в листе начинается сравнение, чувствительно ли оно к регистру и как выявляются несоответствия. Выберите Далее, чтобы перейти к следующему окну.
Выберите листы, которые вы хотите сравнить, и нажмите Добавить, чтобы переместить эти листы на панель справа. Выберите Далее и сделайте то же самое для следующего листа.
Нажмите Далее, чтобы открыть окно с настройками конфигурации отчета. Измените их, если хотите, затем дважды нажмите Next и сравните, чтобы закончить.
Наконец, вы увидите, что каждый исходный лист обновляется с измененными ячейками, выделенными красным. Инструмент также создает третий лист с отчетом, в котором вычеркнуто старое значение и вместо него новое значение.
Этот инструмент представляет собой мощный способ сравнить целые рабочие листы Excel и увидеть быстрые результаты со всеми изменениями. Вы можете прокручивать и сохранять или удалять изменения, которые вы хотите сохранить.
Используйте Excel для сравнения двух листов
Если у вас есть отдельные листы в файле Excel для сравнения, вы можете сделать это несколькими различными способами. Одним из них является использование формул для создания третьей электронной таблицы, показывающей все различия. Другой способ заключается в условном форматировании, чтобы выделить измененные ячейки.
Сравнить таблицы Excel с помощью формул
Если у вас есть два листа, которые вы хотите сравнить, вы можете создать третий лист, который выделит, какие ячейки отличаются, и покажет разницу. Это достигается с помощью формулы IF.
Формула IF полезна для сравнения одной ячейки за раз, а также с помощью функции автозаполнения Excel, чтобы заполнить весь лист результатов результатами сравнения. Бесполезно сравнивать целые рабочие книги без большого количества дополнительной работы.
Чтобы начать сравнение двух листов, создайте третий лист. Пометьте его как « Результаты», чтобы позже было легче определить лист результатов.
В ячейку A2 таблицы результатов вставьте следующую формулу и нажмите Enter :
Вы можете изменить эту формулу, чтобы начать с любой ячейки, которая вам нравится. Если данные на вашем листе начинаются со строки B и столбца 3, вы должны изменить формулу, чтобы использовать B3 вместо A2.
Эта формула сравнивает ячейку из Sheet1 с той же ячейкой в Sheet2. Если ячейки совпадают, на листе результатов отобразится Без разницы . Если они разные, ячейка будет предоставлять разные значения для каждого листа.
Щелкните по углу ячейки и перетащите указатель мыши по листу в последний столбец, в котором есть данные из других сравниваемых листов. Это заполнит формулу сравнения до последнего столбца и автоматически настроит ссылки на ячейки.
Выделив ту же строку, щелкните угол последней ячейки и перетащите мышь вниз по листу до последней строки, в которой есть данные из других сравниваемых листов. Это заполнит формулу сравнения до последней строки и автоматически настроит ссылки на ячейки.
Прокручивая лист, вы увидите, что все ячейки, которые различаются между Sheet1 и Sheet2, отображаются со значениями для каждого листа. Просто прокрутите, чтобы определить все различия.
Использование формул — это один из самых простых способов сравнения отдельных листов, поскольку вам не нужно каким-либо образом изменять исходные листы.
Сравните листы Excel с условным форматированием
Другой подход к сравнению двух листов — использование условного форматирования. Как видно из приведенного выше листа результатов, при одинаковом форматировании всех ячеек может быть трудно определить различия между листами.
Использование условного форматирования особенно полезно для больших листов с большим количеством данных. Изменение цвета или форматирования ячеек, имеющих различия, значительно облегчает выявление этих различий даже в листах с большим количеством строк и столбцов данных.
Вы можете использовать условное форматирование, чтобы определить различия.
Сначала выделите все ячейки в таблице результатов. Выберите меню « Домой» , затем выберите « Условное форматирование» в группе « Стили » и выберите « Новое правило» .
В открывшемся окне « Новое правило форматирования» выберите « Использовать формулу», чтобы определить ячейки для форматирования . В поле « Значения формата», где эта формула является истинным , вставьте следующую формулу.
Как и в случае с формулой, вы можете запустить функцию форматирования в любой ячейке. Если ваши данные начинаются с B3 вместо A2, отредактируйте эту формулу, чтобы использовать вместо нее B3. Форматирование начнется с B3 и заполнит все строки и столбцы ниже и справа от него.
Выберите « Формат» , а затем настройте форматирование, которое должны иметь ячейки при различиях между двумя листами. Затем выберите ОК .
Теперь вы можете видеть, что в тех случаях, когда ячейки между двумя листами имеют различия, форматирование отличается. Это значительно облегчает выявление этих различий.
Сравнить листы Excel вручную
Один простой и быстрый способ сравнить две таблицы — сделать это визуально. Excel предоставляет простой способ визуального сравнения двух листов бок о бок.
На листе, где у вас есть два листа, которые вы хотите сравнить, выберите меню Вид . Выберите « Новое окно», чтобы открыть ту же книгу в новом окне Excel на заднем плане.
Снова выберите меню « Вид» и нажмите « Просмотр рядом» . Это помещает два окна книги рядом друг с другом, каждое из которых занимает половину экрана.
В одном окне выберите другой лист, который вы хотите сравнить. Прокручивая лист в окне с одной стороны, вы одновременно увидите лист в другом окне.
Боковой просмотр особенно полезен в тех ситуациях, когда две таблицы в основном одинаковы, но вы знаете, что есть несколько отличий. Синхронизированная прокрутка позволяет визуально сканировать электронную таблицу, чтобы найти эти различия.
Просмотр бок о бок в Excel 2010 и более ранних версиях
Если вы используете более раннюю версию Excel, отдельные файлы рабочей таблицы открываются в том же окне. Вы все еще можете использовать функцию «Просмотр бок о бок», но найти ее немного по-другому.
В меню « Вид» выберите « Упорядочить все», чтобы разбить открытые файлы на несколько вложенных окон.
Выберите значок « Просмотр рядом», чтобы просмотреть подокна рядом и прокрутите их одновременно.
Параметры облегчают сравнение двух файлов Excel
Опция, которую вы используете для сравнения данных в Excel, зависит от объема данных и места их хранения. Если у вас есть несколько листов в двух разных файлах Excel, лучше всего использовать стороннее дополнение, например Spreadsheet Compare.
Сравнение двух листов в excel. Как сравнить два столбца в Excel на совпадения
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
Как Сравнить 2 Списка в Excel и Выявить Различия
Лично я имел счастье сталкивался с этим при сведении инвентаризации основных средств, когда у вас есть результаты подсчёта и данные из учётной системы. Надо быстро найти чего не хватает, что лишнее, какие аномалии наличествуют.
Вот перед вами упрощённый учебный пример такой задачи.
Путём нехитрых развлечений с функцией ВПР , вы можете получить такой результат:
То есть мы нашли пересечение наших двух диапазонов там, где ВПР вернул значение. Отфильтровав по #Н/Д в каждой таблице, мы получаем список значений того, что есть слева, но отсутствует справа, и того, что есть справа, но отсутствует слева. Это максимум того, что можно выжать из стандартного подхода с ВПР.
Недостатки стандартного подхода:
- Мы потратили много сил. Если таких таблиц у нас много, то такой метод не годится. Это и трудоёмко и слишком легко запутаться.
- Как мы знаем, ВПР ищет первое совпадение и значит о том, что, например, в левой таблице два значения 040310475653, а в правой только одно, мы можем и не узнать, особенно, если таблицы большие.
- В случае с #Н/Д мы также не будем понимать структуру аномалий наших данных. Например, то, что значение 40310307297 справа встречается дважды.
- У нас не будет информации, сколько вообще уникальных значений встречается в каждой таблице.
Я думаю, вы уже согласны, что есть необходимость в специальном инструменте, который я и собираюсь сейчас представить вашему вниманию.
Основные возможности предлагаемого инструмента:
- Исчерпывающее сравнение двух списков с выявлением всех возможных аномалий в данных
- Сравнение осуществляется как на месте, где расположены указанные пользователем диапазоны (вставляется колонка справа), так и на отдельном новом листе рабочей книги
- Цветовое акцентирование результатов сравнения
- Предоставление детальной статистики по результатам сравнения (вставляется в комментарий к ячейке заголовка столбца, где происходит сравнение)
- Автоопределение столбца с данными при указании диапазонов (достаточно указать одну ячейку)
- Две модели сравнения: простая и обычная.
- Учёт регистра текста, если в этом есть необходимость
- Учёт наличия / отсутствия заголовка у диапазонов
- 2 типа сортировки
- Возможность заменить стандартные статусы сравнения на пользовательские
Используемая концепция сравнения списков
Концепция очень проста и легка для понимания при минимуме усилий.
Результаты работы инструмента
Вот как выглядят результаты сравнения примера из начала статьи
- В левом списке есть 12 значений, которые отсутствуют в правом (статус L1R0 )
- В левом списке задвоено значение 040310475653 (статус LnR1 )
- Только 3 значения совпали в списках по принципу «один к одному» (статус L1R1 )
- В правом списке есть 7 значений, которые отсутствуют в левом списке (статус L0R1 )
- В правом списке есть 4 строки (две по 2 значения), которых нет слева и которые задвоены справа (статус L0Rn )
- И слева, и справа есть 2 значения, которые встречаются единожды слева, но которые задвоены справа (статус L1Rn )
Пользовательский интерфейс
Описание элементов управления:
Как сравнить два столбца в Excel — методы сравнения данных Excel
- Мы потратили много сил. Если таких таблиц у нас много, то такой метод не годится. Это и трудоёмко и слишком легко запутаться.
- Как мы знаем, ВПР ищет первое совпадение и значит о том, что, например, в левой таблице два значения 040310475653, а в правой только одно, мы можем и не узнать, особенно, если таблицы большие.
- В случае с #Н/Д мы также не будем понимать структуру аномалий наших данных. Например, то, что значение 40310307297 справа встречается дважды.
- У нас не будет информации, сколько вообще уникальных значений встречается в каждой таблице.
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.