В этом руководстве вы узнаете о различных методах сравнения электронных таблиц Excel и выявления различий между ними. Узнайте, как отображать две таблицы рядом друг с другом, использовать формулы для сообщения о различиях, выделять несоответствия с помощью условного форматирования и т д.
Когда у вас есть две одинаковые книги Excel или, еще лучше, две версии одной и той же книги, что вы обычно хотите сделать с ними в первую очередь? Сравните их на наличие различий и, возможно, объедините их в один файл. Кроме того, такая операция может помочь вам обнаружить потенциальные проблемы, такие как неработающие ссылки, повторяющиеся записи, несогласованные формулы.
- Визуальное сравнение таблиц.
- Быстрое выделение различий.
- Использование формулы сравнения.
- Как показать отличия на отдельном листе.
- Как я могу использовать функцию ВПР.
- Выделите различия с условным форматированием.
- Отображение с помощью сводной таблицы.
- Сравнение таблиц с помощью Pover Query.
- Инструмент сравнения таблиц Ultimate Suite.
Итак, давайте подробнее рассмотрим различные методы сравнения электронных таблиц Excel и увидим различия между ними.
Просмотр рядом, чтобы сравнить таблицы.
Если у вас относительно небольшие файлы и вы заботитесь о деталях, это быстрое и простое сравнение может вам подойти. Я говорю о представлении «Бок о бок», которое позволяет разместить два окна Excel рядом. Вы можете использовать этот метод для визуального сравнения двух таблиц или двух листов из одной книги.
Сравните 2 книги.
Предположим, у вас есть отчеты о продажах за два месяца, и вы хотите просмотреть их одновременно, чтобы увидеть, какие продукты показали лучшие результаты в этом месяце, а какие продержались дольше.
Чтобы просмотреть два файла Excel рядом, выполните следующие действия:
- откройте оба файла.
- Перейдите на вкладку «Вид» и нажмите кнопку «Далее». (1) Вот оно!
По умолчанию два отдельных окна Excel отображаются горизонтально.
Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):
В результате будут размещены два отдельных окна, как на скриншоте.
Если вы хотите одновременно прокручивать оба листа для сравнения данных построчно, убедитесь, что параметр синхронной прокрутки (2) включен. Обычно он включается автоматически, как только вы активируете режим одновременного просмотра двух книг.
Расположите рядом несколько таблиц Excel.
Чтобы увидеть более двух файлов одновременно, откройте все книги, которые хотите сравнить, и нажмите кнопку «Рядом».
Появится диалоговое окно «Сравнить рядом», в котором можно выбрать файлы для отображения с активной книгой.
Чтобы просмотреть все открытые файлы сразу, нажмите кнопку «Упорядочить все» и выберите нужный макет: мозаичный, альбомный, вертикальный или размашистый.
Для небольших таблиц можно легко визуально сравнить их данные. Хотя, конечно, риск ошибки из-за человеческого фактора здесь присутствует.
Сравните два листа в одной книге.
Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы увидеть их рядом, сделайте следующее.
- откройте файл, перейдите на вкладку «Вид» и нажмите кнопку «Новое окно».
- Это действие откроет тот же файл в дополнительном окне.
- Включите режим отображения поблизости, нажав соответствующую кнопку на ленте.
- Выберите лист 1 в первом окне и лист 2 во втором окне.
Быстрое выделение значений, которые различаются.
Тоже не очень трудоемкий способ. Если вам нужно просто найти и подтвердить наличие или отсутствие различий между записями, на вкладке «Главная» нажмите кнопку «Найти и выбрать», предварительно выбрав область, где вы хотите сравнить данные в Excel.
В открывшемся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «различия по строкам».
К сожалению, это нормально работает только для сравнения двух столбцов (или строк), а не всей таблицы. Кроме того, строки должны быть отсортированы таким же образом, так как ячейки сравниваются построчно. Если ваши товары сортируются по-разному, или вообще разный ассортимент, пользы от этого метода не будет.
Формула сравнения.
Это самый простой способ корреляции таблиц в Excel, который позволяет идентифицировать в них ячейки с разными значениями.
Самый простой вариант — сравнить две таблицы, находящиеся на одном листе. Вы можете соотнести как числовые, так и текстовые значения, просто написав в одной из соседних ячеек формулу их подобия. В результате, если ячейки идентичны, мы получим сообщение ИСТИНА, иначе - ЛОЖЬ.
Предположим, у нас есть два прайс-листа (старый и новый), где цены различаются на определенные позиции. В этом случае порядок элементов тот же. Поэтому с помощью простейшей формулы мы можем сравнивать одинаковые ячейки с данными прямо на одном листе.
=G3=С3
Результат будет либо TRUE (для совпадения), либо FALSE (для отрицательного результата).
Таким же образом можно сравнивать данные в таблицах, расположенных на разных листах. Процедура сравнения почти точно такая же, как описано выше, за исключением того, что при создании формулы нужно переключаться между листами. В нашем случае выражение будет выглядеть так:
=G3=Лист2!C3
Если ваши таблицы достаточно велики, поиск в столбце I слова FALSE может быть довольно утомительным. Поэтому может быть полезно сразу определить - есть ли отклонения вообще?
Можно посчитать общее количество отклонений и тут же вывести это число где-то отдельно.
=СУММПРОИЗВ(--(C3:C25G3:G25))
или вы можете сделать это с помощью формулы массива
{=СУММ(--(C3:C25G3:G25))}
Если формула возвращает значение null, данные точно такие же. Ну а если результат положительный, нужны более детальные исследования. О них мы поговорим далее.
Как произвести сравнение на отдельном листе.
Чтобы сравнить два листа Excel на наличие различий, откройте новый пустой лист, введите следующую формулу в ячейку A1 и скопируйте ее вниз и вправо, перетащив маркер заполнения:
=ЕСЛИ(Лист1!A1 Лист2!A1; "Лист1:"&Лист1!A1&" - Лист2:"&Лист2!A1; "")
Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от положения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 на Листе1 и Листе2, формула в B1 будет сравнивать ячейку B1 на обоих листах и так далее. Результат будет выглядеть примерно так:
В результате вы получите отчет о различиях на новом листе. Думаю достаточно информативно.
Как вы можете видеть на изображении выше, формула сравнивает 2 листа, находит ячейки с разными значениями и отображает различия в соответствующих местах.
Обратите внимание, что в отчете о разнице (ячейка D4) даты представлены в виде чисел, потому что в таком виде они хранятся внутри Excel, что не очень удобно для анализа различий между ними.
Как сравнить две таблицы при помощи формулы ВПР.
Допустим, у нас осталось 2 прайс-листа. Однако, в отличие от предыдущего примера, они содержат разное количество элементов, а сами элементы расположены в случайном порядке. Поэтому описанный выше способ, когда мы сравнивали две таблицы построчно, здесь не сработает.
Нам нужно последовательно брать каждый товар из одной таблицы, находить его в другой, извлекать оттуда цену и сравнивать с исходной ценой. Здесь не обойтись без поисковых формул. В этом нам поможет функция ВПР.
Для наглядности разместим обе таблицы на одном листе.
Формула
=ОШИБКА(ПРОСМОТР(F3;$B$3:$C$18;2;0);0)
берет название товара из второго прайс-листа, ищет его в первом и в случае успеха извлекает соответствующую цену из первой таблицы. Она будет написана рядом с новой ценой в столбце H. Если поиск не удался, то есть такого товара раньше не было, то ставим 0. Таким образом, старая и новая цена стоят рядом, и их можно легко сравнить с простейшей операцией вычитания. Что сделано в столбце I.
Точно так же можно сравнивать данные на разных листах. Вам просто нужно соответствующим образом изменить ссылки в формуле, чтобы включить имя листа.
Вот еще один пример. Начнем с более свежей информации, т.е со второго прайс-листа. Мы покажем только информацию о том, какие цены и какие товары изменились. А то, что не было изменено, в итоговом отчете не появится.
Давайте пройдемся по шагам шаг за шагом. Формула в ячейке J3 ищет название продукта с первой позиции во второй таблице в первой. Если она найдена, старая цена, соответствующая этому продукту, извлекается и немедленно сравнивается с новой. Если они равны, в ячейку "" записывается пробел «».
=ЕСЛИ(ОШИБКА(F3,$B$3:$C$18,2,0),0)=G3,"",ОШИБКА(F3,$B$3:$C$18,2,0),0))
Таким образом, в ячейке J3 будет указана старая цена, если ее можно найти, а также если она не равна новой.
Кроме того, если ячейка J3 не пуста, название продукта будет введено в I3 —
=ЕСЛИ(J3"";F3;"")
а в К3 новая цена:
=ЕСЛИ(J3"";G3;"")
Ну так в L3 мы просто находим разницу K3-J3.
Поэтому в отчете о сравнении мы видим только отклонения между значениями во второй таблице по сравнению с первой.
И еще один пример, который может быть полезен. Попробуем сравнить оба прайс-листа с общим справочником позиций в итоговой таблице.
Введите формулу в ячейку B2
=ЕСЛИ(ПРОСМОТР(A2,Цена1!$B$3:$B$19,1,0)),"Нет",ВПР(A2,Цена1!$B$3:$C$19,2,0))
Так мы узнаем, какие цены из второй таблицы находятся в первой.
По каждой цене из первого прайс-листа проверяем, соответствует ли она новым данным —
=ЕСЛИ(КОНЕЦ(ПОИСК(A2,Цена2!$B$3:$B$22,1,0)),"Нет",ПОИСК(A2,Цена2!$B$3:$C$22,2,0))
В колонке А у нас есть список ссылок. В итоге у нас есть этакая сводная таблица цен — старых и новых.
Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.
Выделение различий между таблицами цветом.
Чтобы покрасить ячейки с разными значениями на двух листах в нужный цвет, используйте функцию условного форматирования Excel:
- На листе, где вы хотите отметить различия, выделите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
- На вкладке «Главная» нажмите «Условное форматирование» > «Новое правило» и создайте его со следующей формулой:
=A1Лист2!A1
Где Лист2 — это имя второго листа, который вы сравниваете с текущим.
В результате ячейки с разными значениями будут выделены выбранным вами цветом:
Если вы не очень хорошо знакомы с условным форматированием, вы можете найти подробные инструкции по созданию правила в следующем руководстве: Условное форматирование Excel на основе значения ячейки.
Сравнение при помощи сводной таблицы.
Хороший вариант сравнения — объединить таблицы в единую сводку, а потом уже сравнивать данные друг с другом.
Вернемся к нашему примеру с двумя прайс-листами. Объединим наши данные на одном листе. Чтобы отделить данные одной таблицы от другой, добавьте вспомогательный столбец D и укажите в нем, откуда именно берутся данные:
Теперь приступим к созданию сводной таблицы. Я не буду вдаваться в подробности того, как мы собираемся это сделать. Все шаги описаны в статье Как создать сводную таблицу в Excel.
Давайте поместим поле продукта в область строки, поле цены в область столбца и поле цены в область значения.
Как вы можете видеть на скриншоте ниже, цена указана для каждого продукта, найденного хотя бы в одном из прайс-листов.
Сводная таблица автоматически сгенерирует общий список всех товаров из старого и нового прайс-листа и отсортирует их по алфавиту. И никаких повторов. Новые товары не имеют старой цены, удаленные товары не имеют новой цены. Легко увидеть изменения цен, если таковые имеются.
Общие итоги здесь не имеют значения и могут быть отключены на вкладке «Дизайн» — «Общие итоги» — «Отключить для строк и столбцов.
Если цены изменятся, просто обновите созданную сводку, щелкнув по ней правой кнопкой мыши - Обновить. Однако при изменении списка элементов или добавлении нового файла для сравнения необходимо переформатировать исходную матрицу или добавить в нее новые данные.
Преимущества: этот подход работает намного быстрее с большими объемами данных, чем ВПР. Вы можете сравнивать данные из нескольких таблиц.
Минусы: приходится вручную копировать данные в большую таблицу и добавлять столбец с названием исходного файла.
Сравнение таблиц с помощью Power Query
Power Query — это бесплатная надстройка для Microsoft Excel, которая позволяет загружать данные практически из любого источника, а затем преобразовывать их нужным образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке «Данные», а для более ранних версий ее нужно скачать и установить отдельно с сайта Microsoft.
Прежде чем загружать наши прайс-листы в Power Query, их нужно предварительно преобразовать в смарт-таблицы. Для этого выделите область данных и нажмите Ctrl+T на клавиатуре или выберите вкладку Главная — Форматировать как таблицу на ленте. Названия создаваемых таблиц можно изменить на вкладке Дизайн (я оставляю по умолчанию Table1 и Table2, которые генерируются по умолчанию).
Загрузите первую цену в Power Query с помощью кнопки «Из таблицы/диапазона» на вкладке «Данные.
После загрузки возвращаемся в Excel из Power Query командой Close and Load - Close and Load...
В появившемся окне выберите «Только создать подключение».
Повторите те же действия с новым прайс-листом.
Теперь давайте создадим третий запрос, который будет объединять и сравнивать данные двух предыдущих. Для этого на вкладке Данные — Получить данные — Объединить запросы — Объединить. Вы можете увидеть все шаги на скриншоте ниже.
В окне слияния выбираем в выпадающих списках наши таблицы, выбираем столбцы с названиями элементов в них, а в нижней части определяем метод слияния - Полное внешнее.
После нажатия ОК должна появиться таблица с четырьмя столбцами, где в четвертом столбце нужно развернуть вложенное содержимое с помощью двойной стрелки в заголовке.
После нажатия вы увидите список колонок из второго прайс-листа. Выберите товар и цену. Получаем следующую картину:
Примечание. Полученные таким образом сводные данные можно сделать исходными для сводной таблицы, о которой мы говорили выше. Тогда не нужно будет вручную объединять несколько таблиц в одну.
Теперь сравним цены. Перейдите на вкладку «Добавить столбец» и нажмите кнопку «Условный столбец». А затем в открывшемся окне введите несколько тестовых условий с соответствующими им значениями, которые вы хотите отобразить:
Теперь осталось только вернуться на вкладку «Главная» и нажать «Закрыть и загрузить.
Получаем новый лист в нашей рабочей тетради:
Примечание. Если в будущем произойдут изменения в наших прайс-листах (добавятся или уберутся строки, изменятся цены и т д.), достаточно будет просто обновить наши поиски с помощью комбинации клавиш Ctrl+Alt+F5 или кнопки «Обновить все» на странице Данные - вкладка.
Ведь все данные берутся из «умных» таблиц Excel, которые автоматически изменяют размер при добавлении или удалении из них информации. Однако помните, что названия столбцов в исходных таблицах менять нельзя, иначе мы получим ошибку "Такой-то столбец не найден!" при попытке обновить запрос.
Это, пожалуй, самый красивый и практичный способ из всех стандартов. Умело работает с большими таблицами. Не требует ручных изменений при изменении размера.
Как видите, есть несколько способов сравнить два листа Excel с помощью формул или условного форматирования. Однако эти методы не подходят для комплексного сравнения из-за следующих ограничений:
- Они находят только различия в значениях, но не могут сравнивать формулы или форматирование ячеек.
- Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку/столбец на одном листе, все последующие строки/столбцы будут отмечены как разные.
- Они хорошо работают на уровне листа, но не могут обнаруживать структурные различия на уровне книги Excel, такие как добавление и удаление листов.
Эти проблемы решаются с помощью надстроек Excel, о которых мы поговорим далее.
Как сравнить таблицы при помощи Ultimat Suite для Excel
Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, наиболее интересным из которых является Сравнение таблиц, инструмент для сравнения листов или диапазонов данных в Excel.
Чтобы сделать сравнение более интуитивным и практичным, приложение разработано следующим образом:
- Мастер шаг за шагом проведет вас через весь процесс и поможет настроить различные параметры.
- Вы можете выбрать алгоритм сравнения, который лучше всего подходит для ваших наборов данных.
- Вместо отчета о различиях сравниваемые листы отображаются в режиме просмотра различий, чтобы вы могли сразу увидеть все различия и управлять ими по одному.
Теперь давайте попробуем использовать этот инструмент на примерах электронных таблиц из предыдущего примера и посмотрим, отличаются ли результаты.
- Нажмите кнопку «Сравнить два листа» в категории «Данные Ablebits» в группе «Объединить »:
- Появится окно мастера с просьбой выбрать два листа, которые вы хотите сравнить на наличие различий.
По умолчанию выбраны все листы, но вы также можете выбрать текущую таблицу или конкретный диапазон, нажав соответствующую кнопку:
- На следующем шаге выберите алгоритм сравнения:
- Без ключевых столбцов (по умолчанию) — лучше всего подходит для сложных документов, таких как счета или контракты.
- По ключевым столбцам — подходит для таблиц, организованных по столбцам, которые имеют один или несколько уникальных идентификаторов, таких как номера заказов или артикулы продуктов.
- По ячейкам — лучше всего использовать для сравнения таблиц одинакового формата и размера, таких как балансовые отчеты или статистические отчеты.
Совет. Если вы не уверены, какой алгоритм вам подходит, выберите вариант по умолчанию (без ключевых столбцов). Независимо от того, какой алгоритм вы выберете, модуль найдет все отличия, просто выделив их по-разному (целые строки или отдельные ячейки).
На этом же шаге вы можете выбрать предпочитаемый тип соответствия:
- Первое совпадение (по умолчанию) — сравнивает строку на листе 1 с первой строкой на листе 2, в которой есть хотя бы одна совпадающая ячейка.
- Наилучшее совпадение — сравнивает строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
- Точное совпадение — находит на обоих листах строки, имеющие одинаковое значение во всех ячейках, и помечает все остальные строки как уникальные.
В этом примере мы сначала будем искать наилучшее совпадение, используя режим сравнения без ключевых столбцов, который используется по умолчанию.
- На следующем шаге вы указываете, какие различия следует выделять, а какие следует игнорировать, и как следует помечать различия.
Скрытые строки и столбцы не имеют значения, и мы просим плагин их игнорировать:
- Нажмите кнопку «Сравнить» и подождите некоторое время, пока программа обработает ваши данные и создаст резервные копии. Резервные копии всегда создаются автоматически, поэтому вам не нужно беспокоиться о безопасности ваших данных.
После обработки листы открываются рядом в специальном отображении различий с выбранным методом выделения различий:
На скриншоте выше отличия выделены стандартными цветами:
- Красные линии — это линии, которые можно найти только на листе 2 (справа).
- Зеленые ячейки — это разные ячейки в перекрывающихся рядах.
Но если мы выберем второй алгоритм сравнения — по ключевому столбцу, нас попросят его указать. В нашем случае в качестве ключевого столбца вполне можно указать «Продукт».
После этого мы видим немного другой результат сравнения:
Как видите, самое главное здесь на самом деле то, что значения в столбце B совпадают. Строки, где такого совпадения нет, сразу выделяются красным или фиолетовым цветом. Но если есть совпадение, то идем в столбец С и сравниваем записанную там цену. Зеленые клетки показывают нам только те позиции, которые есть в обоих прайс-листах, но цена на них изменилась.
Не знаю, как вам, а мне второй вариант кажется более информативным.
Что мне делать дальше с этим сравнением?
Чтобы помочь вам увидеть различия и управлять ими, у каждого листа есть собственная вертикальная панель инструментов. Для неактивного листа (справа на скриншоте) эта панель отключена. Чтобы активировать панель инструментов, выберите любую ячейку на соответствующем листе.
Используя его, вы проходите по найденным различиям и решаете, объединять их или игнорировать:
Когда последняя разница будет разрешена, вам будет предложено сохранить рабочие книги и выйти из представления различий.
Если вы еще не закончили обработку различий, но хотите сделать перерыв, нажмите кнопку «Завершить различия» в нижней части панели инструментов и выберите один из следующих вариантов:
- Сохраните изменения и сохраните оставшиеся отличия (сохраните рабочие книги и сохраните отметки различий),
- Сохраните изменения и удалите оставшиеся различия (Сохранить рабочие книги и удалить отметки различий),
- Восстановление книг из резервных копий).
Как сравнить два листа в Excel с помощью инструмента «Сравнить два листа» (надеюсь, он вам понравился :)
Если вы хотите попробовать, полнофункциональную ознакомительную версию можно загрузить здесь .
Функция ЕСЛИОШИБКА — примеры формул. В этой статье описывается, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или конкретным сообщением. Давайте покажем примеры того, как использовать функцию ЕСЛИОШИБКА с визуальным…
Функция MAX в Excel: как найти максимальное значение. В этой статье объясняется функция MAX со множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число в электронной таблице. MAX — один из самых простых…
Как сравнить два столбца на наличие совпадений и различий — прочтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Сравнение ячеек в Excel. Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или нечувствительности к регистру. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…
Как выделить повторяющиеся значения в Excel? - В этом уроке вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения повторяющихся ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы изучали различные…
Группировка в сводной таблице Excel — полезные советы. - Возможность группировать данные — одна из самых мощных и полезных функций сводной таблицы. Это позволит выявить ранее скрытые состояния, сделать интересные выводы. При работе со сводными таблицами в Microsoft Excel вы можете…
Как создать сводную таблицу в Excel — пошаговое руководство для чайников. В этом руководстве вы узнаете, что такое сводная таблица, и найдете подробные инструкции о том, как шаг за шагом ее создавать и использовать в Excel. Если вы работаете с большими наборами данных в…
Как изменить цвет ячейки на основе значения в Excel? - В этой статье вы найдете 13 примеров того, как изменить цвет фона ячейки в зависимости от значения в Excel 2016, 2013 и 2010. Вы также узнаете, как использовать формулы Excel…
Как сделать условное форматирование в Excel? Инструкция с примерами. - В этой статье вы найдете множество быстрых способов условного форматирования строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы рассмотрим, как вы можете использовать разные…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вы должны посмотреть, какие цены изменились и
