Сравнение двух версий книги с помощью средства сравнения электронных таблиц
Office для бизнеса Средство сравнения электронных таблиц 2013 Средство сравнения электронных таблиц 2016 Средство сравнения электронных таблиц 2019 Средство сравнения электронных таблиц 2021 Еще. Меньше
Если другие пользователи имеют право на редактирование вашей книги, то после ее открытия у вас могут возникнуть вопросы «Кто ее изменил? И что именно изменилось?» Средство сравнения электронных таблиц от Майкрософт поможет вам ответить на эти вопросы — найдет изменения и выделит их.
Важно: Spreadsheet Compare is only available with Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019, or Приложения Microsoft 365 для предприятий.
- Откройте средство сравнения электронных таблиц.
- В левой нижней области выберите элементы, которые хотите включить в сравнение книг, например формулы, форматирование ячеек или макросы. Или просто выберите вариант Select All (Выделить все).
- На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).
В диалоговом окне «Сравнение файлов» в строке »
» выберите более ранжную версию книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.
В диалоговом окне «Сравнение файлов» в строке «С»
до нужной версии.
Примечание: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.
Примечание: Появление сообщения «Не удается открыть книгу» может означать, что книга защищена паролем. Нажмите кнопку ОК и введите пароль. Узнайте подробнее о том, как действуют пароли при использовании средства сравнения электронных таблиц.
Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле «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). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.
Как сравнить два акта сверки в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Одновременное сравнение двух или более таблиц
Можно быстро сравнить два листа в одной или разных книгах, просматривая их одновременно. Кроме того, возможен и одновременный просмотр нескольких листов.
Одновременный просмотр двух листов в одной книге
- На вкладке Вид в группе Окно щелкните элемент Новое окно.
, на вкладке Вид нажмите кнопку Синхронная прокрутка.
Примечание: Этот параметр доступен, только если включен режим Рядом.
-
Если вы хотите оптимально просмотреть окна книги, нажмите кнопку Восстановить положение
Одновременный просмотр двух листов разных книг
- Откройте обе книги, которые содержат листы для сравнения.
- На вкладке Вид в группе Окно нажмите кнопку Рядом
.
, на вкладке Вид нажмите кнопку Синхронная прокрутка.
Примечание: Этот параметр доступен, только если включен режим Рядом.
- Начиная с Excel 2013 г., книги, открытые в Excel, больше не отображаются в одном окне как несколько окон Excel книг. Вместо этого они выводятся в отдельных окнах Excel. Благодаря этому открытые книги отображаются с собственной лентой и их можно просматривать на разных мониторах.
- Если вы хотите оптимально просмотреть окна книги, нажмите кнопку Восстановить положение
Одновременный просмотр нескольких листов
- Откройте одну или несколько книг, листы из которых требуется просмотреть одновременно.
- Выполните одно из указанных ниже действий.
- Если листы, которые нужно просмотреть, находятся в одной книге, выполните указанные ниже действия.
- Щелкните лист, который нужно просмотреть.
- На вкладке Вид в группе Окно щелкните Новое окно.
Совет: Чтобы восстановить окно книги до полного размера, нажмите кнопку Развернуть
в правом верхнем углу окна книги.
Одновременный просмотр двух листов в одной книге
- На вкладке Вид в группе Окно щелкните элемент Новое окно.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Сравнение двух таблиц
Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения. Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:- функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
- объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
- использовать надстройку Power Query для Excel
Давайте разберем их все последовательно.
Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)
Если вы совсем не знакомы с этой замечательной функцией, то загляните сначала сюда и почитайте или посмотрите видеоурок по ней — сэкономите себе пару лет жизни.
Обычно эту функцию используют для подтягивания данных из одной таблицы в другую по совпадению какого-либо общего параметра. В данном случае, мы применим ее, чтобы подтянуть старые цены в новый прайс:
Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.
Плюсы этого способа: просто и понятно, «классика жанра», что называется. Работает в любой версии Excel.
Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (>100 тыс. строк) все это счастье будет прилично тормозить.
Способ 2. Сравнение таблиц с помощью сводной
Скопируем наши таблицы одна под другую, добавив столбец с названием прайс-листа, чтобы потом можно было понять из какого списка какая строка:
Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:
Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.
Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .
Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить (Referesh).
Плюсы : такой подход на порядок быстрее работает с большими таблицами, чем ВПР.
Минусы : надо вручную копировать данные друг под друга и добавлять столбец с названием прайс-листа. Если размеры таблиц изменяются, то придется делать все заново.
Способ 3. Сравнение таблиц с помощью Power Query
Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.
Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).
Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :
. и в появившемся затем окне выбрем Только создать подключение (Connection Only) .
Повторите то же самое с новым прайс-листом.
Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.
В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :
После нажатия на ОК должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в шапке:
В итоге получим слияние данных из обеих таблиц:
Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:
А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:
Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :
Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl + Alt + F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data) .
Плюсы : Пожалуй, самый красивый и удобный способ из всех. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров таблиц.
Минусы : Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.
Ссылки по теме
- Как собрать данные из всех файлов Excel в заданной папке с помощью Power Query
- Как найти совпадения между двумя списками в Excel
- Слияние двух списков без дубликатов