В этом руководстве вы научитесь различным методам сравнения электронных таблиц Excel и определения различий между ними. Узнайте, как отображать две таблицы рядом, как использовать формулы для сообщения о различиях, выделять расхождения с помощью условного форматирования и т.д.
Когда у вас есть две похожие книги Excel или, скорее, две версии одной и той же книги, что вы обычно хотите с ними сделать в первую очередь? Сравните их на предмет различий, а затем объедините их в один файл. Это также может помочь вам обнаружить потенциальные проблемы, такие как неработающие ссылки, повторяющиеся записи и несогласованные формулы.
Итак, давайте более подробно рассмотрим различные методы сравнения электронных таблиц Excel и выявим различия между ними.
Просмотр рядом, чтобы сравнить таблицы.
Если у вас относительно небольшие файлы и вы внимательно относитесь к деталям, это быстрое и простое сравнение может быть для вас. Я говорю о режиме просмотра сбоку, который позволяет разместить два окна Excel рядом. Вы можете использовать этот метод для визуального сравнения двух таблиц или двух листов в одной книге.
Сравните 2 книги.
Предположим, у вас есть отчеты о продажах за два месяца, и вы хотите просматривать их одновременно, чтобы понять, какие продукты показали лучшие результаты в этом месяце, а какие прослужили.
Чтобы просмотреть два файла Excel рядом, сделайте следующее:
- Откройте оба файла.
- Перейдите на вкладку «Просмотр» и нажмите кнопку «Далее». (1) Вот и все!
По умолчанию два отдельных окна Excel отображаются по горизонтали.
Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):
В результате будут размещены два отдельных окна, как на скриншоте.
Если вы хотите прокручивать оба листа одновременно, чтобы сравнивать данные строка за строкой, убедитесь, что параметр синхронной прокрутки (2) включен. Обычно он включается автоматически, как только вы входите в режим просмотра двух книг одновременно.
Расположите рядом несколько таблиц Excel.
Чтобы просмотреть более двух файлов одновременно, откройте все книги, которые вы хотите сравнить, и нажмите кнопку «Далее».
Рядом появляется диалоговое окно «Сравнить», в котором вы можете выбрать файлы для просмотра с активной книгой.
Чтобы просмотреть все открытые файлы сразу, нажмите кнопку «Упорядочить все» и выберите предпочтительный макет: бок о бок, горизонтальный, вертикальный или водопад.
Для небольших таблиц вы можете легко сравнить их данные визуально. Хотя, конечно, здесь есть риск ошибки из-за человеческого фактора.
Сравните два листа в одной книге.
Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы просмотреть их рядом, выполните следующие действия.
- Откройте файл, перейдите на вкладку «Просмотр» и нажмите кнопку «Новое окно».
- Это действие откроет тот же файл в дополнительном окне.
- Активируйте параллельный просмотр, нажав соответствующую кнопку на ленте.
- Выберите лист 1 в первом окне и лист 2 во втором окне.
Быстрое выделение значений, которые различаются.
Это тоже не очень громоздкий способ. Если вам просто нужно найти и проверить наличие или отсутствие различий между записями, вам нужно нажать кнопку «Найти и выбрать» на вкладке «Главная» после выбора диапазона, в котором вы хотите сравнить данные в Excel.
В открывшемся меню выберите пункт «Выбрать группу ячеек…» и в появившемся диалоговом окне выберите «различия линий».
К сожалению, это хорошо работает только для сравнения двух столбцов (или строк), а не всей таблицы. Кроме того, строки должны быть отсортированы так же, как ячейки сравниваются строка за строкой. Если ваши товары заказываются по-разному или в общем с другим ассортиментом, этот способ не пригодится.
Формула сравнения.
Это самый простой способ связать таблицы в Excel, который позволяет идентифицировать ячейки с разными значениями внутри них.
Самый простой вариант — сравнить две таблицы на одном листе. Вы можете связать числовые и текстовые значения, просто написав формулу их равенства в одной из соседних ячеек. Следовательно, если ячейки идентичны, мы получим сообщение ИСТИНА, иначе — ЛОЖЬ.
Допустим, у нас есть два прайс-листа (старый и новый), где цены на некоторые товары различаются. В этом случае порядок товаров такой же. Таким образом, используя простейшую формулу, прямо на одном листе, мы можем сравнивать идентичные ячейки с данными.
= G3 = C3
Результатом будет ИСТИНА (в случае совпадения) или ЛОЖЬ (в случае отрицательного результата).
Точно так же вы можете сравнивать данные в таблицах, которые находятся на разных листах. Процедура сравнения почти такая же, как описано выше, за исключением того, что при создании формулы вам нужно будет переключаться между листами. В нашем случае выражение будет выглядеть так:
= G3 = Лист2! C3
Если ваши таблицы достаточно велики, будет довольно утомительно сканировать столбец I на наличие слова FALSE. Поэтому может быть полезно сразу определить: есть ли неточности?
Вы можете посчитать общее количество расхождений и сразу посмотреть это число где-нибудь отдельно.
= СУММПРОИЗВ (- (C3: C25 G3: G25))
или вы можете сделать это с помощью формулы массива
{= СУММ (- (C3: C25 SOL3: SOL25))}
Если формула возвращает ноль, данные точно такие же. Что ж, если результат положительный, нужно более детальное исследование. Об этом и поговорим дальше.
Как произвести сравнение на отдельном листе.
Чтобы сравнить два листа Excel на предмет различий, просто откройте новый чистый лист, введите следующую формулу в ячейку A1, затем скопируйте ее вниз и вправо, перетащив маркер заполнения:
= IF (Sheet1! A1 Sheet2! A1; «Sheet1:» & Sheet1! A1 & «- Sheet2:» & Sheet2! A1; «»)
Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от положения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 на листе Sheet1 и Sheet2, формула в 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 запишите формулу
= ЕСЛИ (UND (ВПР (A2; Price1! $ B $ 3: $ B $ 19; 1,0)), «Нет», VLOOKUP (A2, Price1! $ B $ 3: $ C $ 19; 2; 0))
Затем мы узнаем, какие цены из второй таблицы находятся в первой.
Для каждой цены первого прайс-листа мы проверяем, соответствует ли она новым данным —
= ЕСЛИ (UND (ВПР (A2; Цена2! $ B $ 3: $ B $ 22; 1; 0)); «Нет»; ВПР (A2; Цена2! $ B $ 3: $ C $ 22; 2; 0))
Список тестов находится в столбце A. В результате мы получили своего рода сводную таблицу цен — старых и новых.
Вы можете найти больше примеров использования функции ВПР для сравнения таблиц в этой статье.
Выделение различий между таблицами цветом.
Чтобы заполнить ячейки с разными значениями на двух листах выбранным вами цветом, используйте функцию условного форматирования Excel:
- На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
- На вкладке Главная щелкните Условное форматирование> Новое правило и создайте его с помощью следующей формулы:
= A1 Sheet2! A1
Где Sheet2 — это имя другого листа, который вы сравниваете с текущим.
В результате ячейки с разными значениями будут выделены выбранным цветом:
Если вы не очень хорошо знакомы с условным форматированием, вы можете найти подробные инструкции по созданию правила в следующем руководстве: Условное форматирование 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 In…
В появившемся окне выберите «Только создание подключения».
Повторите те же шаги с новым прайс-листом.
Теперь давайте создадим третий запрос, который объединит и сравнит данные из двух предыдущих. Для этого выберите на вкладке Данные — Получить данные — Объединить запрос — Объединить. Вы можете увидеть все шаги на скриншоте ниже.
В окне слияния выбираем наши таблицы в выпадающих списках, выбираем столбцы с названиями товаров в них и внизу определяем режим слияния — Full External.
После нажатия кнопки ОК должна появиться таблица из четырех столбцов, где в четвертом столбце нужно развернуть вложенное содержимое с помощью двойной стрелки в заголовке.
После нажатия вы увидите список столбцов из второго прайс-листа. Выбираем Товар и Цену. Получаем такую картинку:
Примечание. Полученные таким образом сводные данные можно сделать источником сводной таблицы, о которой мы говорили выше. Таким образом, вам не нужно вручную объединять несколько таблиц в одну.
А теперь сравним цены. Перейдите на вкладку «Добавить столбец» и нажмите кнопку «Условный столбец». А затем в открывшемся окне введите разные условия тестирования с соответствующими значениями, которые должны отображаться:
Теперь осталось вернуться на вкладку «Главная» и нажать «Закрыть и загрузить.
Получаем новый лист в нашей рабочей тетради:
Примечание. Если в будущем в наших прайс-листах произойдут изменения (добавляются или удаляются строки, меняются цены и т.д.), То достаточно будет обновить наши запросы с помощью сочетания клавиш Ctrl + Alt + F5 или кнопки Обновить все на вкладке Данные.
В конце концов, все данные берутся из «умных» таблиц Excel, которые автоматически изменяют свой размер по мере добавления или удаления информации из них. Однако помните, что имена столбцов в исходных таблицах не должны изменяться, иначе вы получите ошибку «Такой-то столбец не найден!» при попытке обновить запрос.
Это, пожалуй, самый красивый и доступный из всех стандартных способов. Хорошо работает с большими таблицами. При изменении размера не требует ручного изменения.
Как видите, есть несколько способов сравнить две таблицы Excel с помощью формул или условного форматирования. Однако эти методы не подходят для сложных сравнений из-за следующих ограничений:
- Они только находят различия в значениях, но не могут сравнивать формулы или форматирование ячеек.
- Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку / столбец на листе, все последующие строки / столбцы будут отмечены как отличия.
- Они хорошо работают на уровне листа, но не могут обнаружить структурные различия на уровне книги Excel, такие как добавление и удаление листов.
Эти проблемы решаются с помощью надстроек для Excel, о которых мы поговорим позже.
Как сравнить таблицы при помощи Ultimat Suite для Excel
Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, наиболее интересным из которых является «Сравнение таблиц», инструмент для сравнения листов или диапазонов данных в Excel.
Чтобы сравнение было нагляднее и удобнее, надстройка оформлена следующим образом:
- Мастер пошагово проведет вас через процесс и проведет вас через различные настройки.
- Вы можете выбрать алгоритм сравнения, который лучше всего подходит для ваших наборов данных.
- Вместо отчета о различиях сравниваемые листы отображаются в режиме просмотра различий, поэтому вы можете сразу увидеть все различия и управлять ими по одному.
Теперь давайте попробуем этот инструмент на наших примерах электронных таблиц из предыдущего примера и посмотрим, отличаются ли результаты.
- Нажмите кнопку «Сравнить два листа» на вкладке «Биты данных» в группе «Объединить »:
- Появится окно мастера с просьбой выбрать два листа, которые вы хотите сравнить на предмет различий.
По умолчанию выбраны все листы, но вы также можете выбрать текущую таблицу или определенный диапазон, нажав соответствующую кнопку:
- На следующем шаге выберите алгоритм сравнения:
- Без ключевых столбцов (по умолчанию) — идеально подходит для сложных документов, таких как счета-фактуры или контракты.
- По ключевым столбцам — подходит для таблиц, организованных по столбцам, имеющим один или несколько уникальных идентификаторов, таких как номера заказов или артикулы.
- По ячейкам — идеально подходит для сравнения таблиц с одинаковым макетом и измерениями, таких как балансы или статистические отчеты.
Совет. Если вы не уверены, какой алгоритм подходит вам, выберите значение по умолчанию (без ключевых столбцов). Какой бы алгоритм вы ни выбрали, надстройка найдет все отличия, только выделит их по-разному (целые строки или отдельные ячейки).
На этом же этапе вы можете выбрать предпочтительный тип соответствия:
- Первое совпадение (по умолчанию): сравните строку на листе 1 с первой строкой на листе 2, в которой есть хотя бы одна совпадающая ячейка.
- Наилучшее совпадение: сравните строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
- Полное совпадение: найдите на обоих листах строки, которые имеют одинаковое значение во всех ячейках, и отметьте все остальные строки как уникальные.
В этом примере мы сначала будем искать наилучшее совпадение, используя режим сравнения без ключа, который установлен по умолчанию.
На следующем шаге укажите, какие различия следует выделить, а какие игнорировать, а также как отмечать различия.
Скрытые строки и столбцы не имеют значения, и мы говорим надстройке игнорировать их:
Нажмите кнопку «Сравнить» и подождите, пока программа обработает данные и создаст резервные копии. Резервные копии всегда создаются автоматически, поэтому вам не нужно беспокоиться о сохранности ваших данных.
После обработки листы открываются рядом друг с другом в специальном виде различий с выбранным методом выделения различий:
На скриншоте выше различия выделены цветами по умолчанию:
- Красные линии — это линии, которые существуют только на листе 2 (справа).
- Зеленые ячейки — это разные ячейки в перекрывающихся рядах.
Но если мы выберем второй алгоритм сравнения — из ключевого столбца, нас попросят его указать. В нашем случае ключевой колонкой вполне можно обозначить «Товар».
Далее мы видим немного другой результат сравнения:
Как видите, главным здесь является совпадение значений в столбцах B. Линии, у которых нет такого совпадения, сразу выделяются красным или фиолетовым цветом. Но если есть совпадение, перейдите в столбец C и сравните записанную там цену. Зеленые ячейки показывают нам только те товары, которые есть в обоих прайс-листах, но цена на них изменилась.
Не знаю, как вы, но мне второй вариант кажется более информативным.
Что тут делать с этим сравнением?
Чтобы помочь вам просматривать различия и управлять ими, на каждом листе есть собственная вертикальная панель инструментов. Для неактивного листа (прямо на нашем скриншоте) эта панель отключена. Чтобы активировать панель инструментов, просто выберите любую ячейку на соответствующем листе.
Используя его, последовательно просматривайте найденные различия и решайте, комбинировать или игнорировать их:
После устранения последнего различия вам будет предложено сохранить книги и выйти из режима просмотра различий.
Если вы еще не закончили работу над различиями, но хотите приостановить работу, нажмите кнопку «Выйти из просмотра различий» в нижней части панели инструментов и выберите один из следующих вариантов:
- Сохраняйте книги и сохраняйте отметки различий),
- Сохраните книги и удалите разницы),
- Восстановить книги из резервных копий).
Вот как вы можете сравнить два листа в Excel с помощью инструмента Сравнить два листа (надеюсь, вам понравилось