Сравнение файлов в Excel используем надстройку Inquire
Сравнение файлов в Excel используем надстройку Inquire
Добрый день, уважаемые читатели. Сегодня мы поговорим о проблеме сравнения файлов в Excel.
Для сравнения мы не будем применять формулы и функции, а воспользуемся надстройкой Inquire. Чтобы её включить необходимо перейти в:
Обязательным условием для сравнения будет являться одновременное открытие двух сравниваемых файлов!
Затем необходимо перейти на появившуюся вкладку «Inquire».
На вкладке нас будет интересовать кнопка «Compare Files». Смело жмём на неё и во всплывающем окне нажимаем «Compare». В списках (если не появились автоматически) нужно выбрать сравниваемые файлы.
Перед нами появится окно со аналитикой по файлам. В окне будут показаны формулы, отличающиеся значения, связи листов или книг (если они есть), а также изменения в структуре файлов (удалённые/добавленные столбцы, ячейки, строки). Каждому типу изменений назначен свой цвет. Также справа будет представлена диаграмма с количеством изменений. Вполне удобная вещь, которая позволит без формул произвести быстрое сравнение файлов.
Также надстройка имеет возможности для экспорта полученных сравнительных данных. Нужно всего лишь нажать кнопку «Export results». Файл будет со хранён как отдельная книга и его можно использовать в дальнейшем.
Немного подробнее в нашем новом видео:
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Надстройка Inquire в excel 2013 — сравнение, анализ и связь файлов Excel
Запуск надстройки Inquire
Надстройка Inquire для Excel идет в комплекте со стандартным набором Excel 2013 и дополнительно скачивать пакеты установки не требуется. Достаточно включить ее в надстройках. Более ранние версии Excel не поддерживают данную надстройку. К тому же на момент написания статьи, надстройка была доступна только на английском языке.
Чтобы запустить Inquire, перейдите по вкладке Файл –> Параметры. В появившемся диалоговом окне выберите вкладку Надстройки, в выпадающем меню Управление выберите Надстройки COM и щелкните кнопку Перейти. Появится окно Надстройки для модели компонентных объектов (COM), где вам необходимо будет поставить галочку напротив Inquire и нажать кнопку ОК.
После запуска надстройки на ленте появится новая вкладка Inquire.
Давайте посмотрим, какие бенефиты дает нам это дополнение.
Анализ рабочей книги
Анализ рабочей книги используется для выявления структуры рабочей книге, формул, ошибок, скрытых листов и т.д. Чтобы воспользоваться данным инструментом, перейдите в группу Report и щелкните кнопку Workbook Analysis. Результат работы надстройки представлен ниже.
Наверняка, многие обратили внимание на пункт Very hidden sheets (Очень скрытые листы). Это не шутка, в Excel действительно можно «хорошо» скрыть лист с помощью редактора VisualBasic. Подробнее об этом мы поговорим в наших последующих статьях.
Связь с рабочими листами
В группе Diagram, присутствует три инструмента определения связей между рабочими книгами, листами и ячейками. Они позволяют указать на отношения между элементами Excel. Данный функционал может быть полезен, когда у вас имеется большое количество ячеек с ссылками на другие книги. Попытки распутать этот клубок могут занять значительное время, тогда как надстройка Inquire позволяет визуализировать зависимость данных.
Чтобы построить диаграмму зависимостей, в группе Diagram выберите один из пунктов WorkbookRelationship, WorksheetRelationship или CellRelationship. Выбор будет зависеть от того, какую зависимость вы хотите увидеть: между книгами, листами или ячейками.
На рисунке ниже вы увидите диаграмму связей между книгами, которую Excel построил, когда я щелкнул кнопку WorkbookRelationship.
Сравнение двух файлов
Следующий инструмент надстройки Inquire для Excel– Compare– позволяет ячейка за ячейкой сравнивать два файла и указать на все различия между ними. Данный инструмент может понадобится, когда у вас есть несколько редакций одного и того же файла и необходимо понять, какие изменения были внесены в последние версии.
Чтобы воспользоваться данным инструментом вам понадобится два файла. В группе Compare выбираем CompareFiles. В появившемся диалоговом окне необходимо выбрать файлы, которые мы хотим сравнить, и щелкнуть кнопку Compare.
В нашем случае, это два одинаковых файла, в один из которых я преднамеренно внес кое-какие изменения.
После недолгих обдумываний, Excel выдаст результат сравнения, где цветом будут указаны различия между двумя таблицами. При этом цвет ячейки будет различным в зависимости от типа отличия ячеек (различия могут генерироваться из-за значений, формул, расчетов и т.д.).
Очистка излишнего форматирования
Данный инструмент позволяет очистить излишнее форматирование ячеек в книге, к примеру, ячеек, которые отформатированы, но не содержат значений. Инструмент Clean Excess Cell Formatting поможет «любителям» заливать цветом всю строку рабочей книги, вместо заливки определенных строк таблицы.
Чтобы воспользоваться инструментом, перейдите во вкладку Inquire в группу Miscellaneous и выберите Clean Excess Cell Formatting. В появившемся окне необходимо выбрать область очистки излишнего форматирования – вся книга или активный лист – щелкнуть ОК.
Очистка ненужного форматирования позволит снизить размер файла и увеличит производительность работы.
Пароли рабочих книг
Если вы собираетесь анализировать рабочие книги, защищенные паролем, вам необходимо будет указать их в Workbook Passwords.
Надстройка Inquire для Excel содержит несколько интересных инструментов, которые позволят увеличить точность и целостность рабочих книг. Если вы используете Excel 2013, имеет смысл обратить свое внимание на данную надстройку.
Вам также могут быть интересны следующие статьи
4 комментария
У меня следующая проблема
Есть ПЕРВЫЙ файл с прайс листом и необходимыми колонками(Product name/Product code/Description/Category/List price/Quantity/Detailed image), который заливается на сайт. Каждую неделю приходит новый прайс лист(названий столбцов отличаются от тех которые есть в ПЕРВОМ файле) с новыми ценами на товар который есть в старом прайс листе плюс добавляются новые товары которых нет в ПЕРВОМ файле. Нужно сделать так чтобы обновлялись цены и добавлялся новый товар в ПЕРВЫЙ файл с нового. С екселем не очень дружу может кто то подскажет как осуществить эту задумку
Обзор надстроек и приложений для Excel 2013
Inquire
Мощный инструмент диагностики и отладки. После подключения этой надстройки в интерфейсе Excel 2013 появляется новая вкладка на ленте:
Надстройка умеет проводить подробный анализ ваших книг (Workbook Analysis) и выдавать подробнейший отчет по более чем трем десяткам параметров:
Надстройка умеет наглядно отображать связи между книгами в виде диаграммы (команда Workbook Relationship):
Также возможно создать подобную диаграмму для формульных связей между листами и между ячейками в пределах одного листа с помощью команд Worksheet Relationship и Cell Relationship:
Такой функционал позволяет оперативно отслеживать и исправлять нарушенные связи в формулах и наглядно представлять логику в сложных файлах.
Особого внимания заслуживает функция Compare Files. Наконец-то появился инструмент для сравнения двух файлов в Excel! Вы указываете два файла (например, оригинальная книга и ее копия после внесения правок) и наглядно видите что, где и как изменилось по сравнению с оригиналом:
Отдельно, с помощью разных цветов, подсвечиваются изменения содержимого ячеек, формул, форматирования и т.д. В Word подобная функция есть уже с 2007 версии, а в Excel ее многим очень не хватало.
Ну, а для борьбы с любителями заливать цветом целиком все строки или столбцы в таблице пригодится функция Clean Excess Cell Formatting. Она убирает форматирования с незадействованных ячеек листа за пределами ваших таблиц, сильно уменьшая размер книги и ускоряя обработку, пересчет и сохранение тяжелых медленных файлов.
Power Pivot
Эта надстройка появилась еще для прошлой версии Excel 2010. Раньше ее требовалось отдельно скачать с сайта www.powerpivot.com и специально установить. Сейчас (в слегка измененном виде) она входит в стандартный комплект поставки Excel 2013 и подключается одной галочкой в окне надстроек. Вкладка Power Pivot выглядит так:
Фактически, эта надстройка является Excel-подобным пользовательским интерфейсом к полноценной базе данных SQL, которая устанавливается на ваш компьютер и представляет собой мощнейший инструмент обработки огромных массивов данных, открывающийся в отдельном окне при нажатии на кнопку Управление (Manage) :
Power View
Вставить в книгу лист отчета Power View можно при помощи одноименной кнопки на вкладке Вставка (Insert) :
В основе отчетов Power View лежит «движок» Silverlight. Если он у вас его нет, то программа скачает и установит его сама (примерно 11 Мб).
Power View автоматически «цепляется» ко всем загруженным в оперативную память данным, включая кэш сводных таблиц и данные, импортированные ранее в надстройку Power Pivot. Вы можете добавить в отчет итоги в виде простой таблицы, сводной таблицы, разного вида диаграмм. Вот такой, например, интерактивный отчет я сделал меньше чем за 5 минут (не касаясь клавиатуры):
Впечатляет, не правда ли?
Весьма примечательно, что Power View позволяет привязывать данные из таблиц даже к географическим картам Bing:
Apps for Office
Российского варианта магазина, правда, еще нет, так что вас перекидывает на родной штатовский магазин. Выбор достаточно велик:
Так, например, на данный момент оттуда можно установить приложение для создания интерактивного календаря на листе Excel, отображения географических карт Bing, модуль онлайнового перевода, построители различных нестандартных диаграмм (водопад, гантт) и т.д. Выбранные приложения вставляются на лист Excel как отдельные объекты и легко привязываются к данным из ячеек листа. Думаю, сообщество разработчиков не заставит себя ждать и очень скоро мы увидим большое количество полезных расширений и приложений для Excel на этой платформе.
Использование надстройки Inquire (запрос) в Excel 2013
В Office 2013 Professional Plus впервые используется надстройка для анализа содержимого книг Excel – Inquire (в переводе с английского: осведомляться, спрашивать, искать). [1] Чтобы проверить версию программы, установленной на вашем ПК, пройдите по меню Файл → Учетная запись (рис. 1). Например, на домашнем ПК у меня версия «для дома и учебы» (рис. 1а), а вот на работе – «профессиональный плюс» (рис. 1б).
Рис. 1. Проверка версии MS Office 2013
По умолчанию надстройка Inquire не установлена. Чтобы ее установить пройдите по меню Файл → Параметры. В открывшемся окне Параметры Excel перейдите в раздел Надстройки. В раскрывающемся списке Управление выберите Надстройки СОМ (рис. 2). Поставьте галочку напротив Inquire и нажмите Ok (рис. 3). На ленте появится новая вкладка – Inquire (рис. 4).
Рис. 2. Параметры Excel
Рис. 3. Надстройка Inquire
Рис. 4. Новая вкладка на ленте – Inquire
Инструмент Inquire позволяет выполнить следующее:
Анализ активной книги выполняется с помощью команды Workbook Analysis. Команда выводит на экран диалоговое окно Workbook Analysis Report (рис. 5). Поставьте галочки в левом окне Items, выбирая элементы для анализа. Результаты появятся в правом окне Results (чтобы увидеть их целиком обратите внимание на бегунок внизу окна). На самом деле, в окне Result отражаются лишь агрегированные результаты. Чтобы получить полный анализ файла создайте отчет (в новой книге Excel), нажав на кнопку Excel Export. Файл с полным отчетом будет содержать около 50 листов. Если вы понимаете, что ищите, выделите в окне Items только те опции, которые должны попасть в отчет и нажмите Excel Export. Фрагмент отчета (а именно, часть листа Summary) приведен на рис. 6.
Рис. 5. Окно Workbook Analysis Report / Отчет об анализе книги
Рис. 6. Фрагмент листа Summary отчета об анализе книги
Видно, что книга содержит 323 ошибки в формулах. Перейдя на лист Error Formulas, вы найдете полный список этих ошибок (рис. 7), с указанием листа, ячейки, формулы и значения в ячейке.
Рис. 7. Фрагмент листа Error Formulas отчета
Некоторые дополнительные сведения можно найти на сайте Microsoft в разделе Анализ книги.
Сравнение двух книг выполняется с помощью команды Compare Files (см. рис. 5). Для начала откройте две книги в Excel. У меня для этих целей есть хороший пример. Для работы с сайтом я веду своеобразный каталог планируемых к публикации и уже опубликованных материалов. Так вот у меня есть текущая и архивная версии файла. Открываю их и жму Compare Files. Появляется окно выбора файлов сравнения (рис. 8). Жму Compare.
Рис. 8. Выбор файлов для сравнения
Результаты сравнения (рис. 9) представлены в нескольких окнах. Наверху имеется лента, позволяющая выполнить новое сравнение, экспортировать результаты сравнения в отдельный файл, задать ряд опций сравнения. В первом ряду расположены два окна с фрагментами сравниваемых файлов. Вы можете перемещаться по листу и переходить от листа к листу. Цветом выделяются различия по типу содержимого, например, по введенным значениям, формулам, именованным диапазонам, форматам. Во втором ряду в левом окне вы можете управлять отображаемыми различиями.
Рис. 9. Результат сравнения
Функция особенно удобна, когда вы получили от коллеги, ранее отправленный ему файл, и хотите понять, какие изменения были внесены.
Любопытно, что на сайте Microsoft на страничке Возможности надстройки Spreadsheet Inquire говорится: «Подробнее о средстве сравнения электронных таблиц и сравнении файлов читайте в статье Сравнение двух версий книги». К сожалению, указанная статья на сайте MS отсутствует…
Отображение связей книги. В книгах, связанных с другими книгами с помощью ссылок легко запутаться. Создайте интерактивную графическую карту зависимостей, образованных ссылками между файлами. Для этого откройте анализируемый файл, перейдите на вкладку Inquire и кликните на команду Workbook Relationship (см. рис. 5). В схеме связей вы можете выбирать элементы и находить о них дополнительные сведения. Например, при наведении курсора на пиктограмму файла Посещаемость.xlsx, появилось сообщение о месте размещения файла, и о проблемах со связями (рис. 10). Кстати желтый цвет файла как раз сигнализирует о том, что со связями есть проблемы. Возможно, они не обновлены. Белый крестик на пиктограмме (см. рис. 10, верхний ряд, справа) также сигнал. На этот раз о том, что файл отсутствует.
Рис. 10. Схема представления связей активной книги
Аналогично предыдущей команда Worksheet Relationship покажет связи между листами активной книги (рис. 11), а команда Cell Relationship – между выбранной ячейкой и другими ячейками (рис. 12). Остановимся на последней опции подробнее.
Рис. 11. Схема представления связей между листами активной книги
Рис. 12. Схема представления связей между выбранной ячейкой и другими ячейками
В отличие от связей книги и листа, при представлении связей ячейки диаграмма появляется не сразу, а предлагается диалоговое окно для определения опций (рис. 13).
Рис. 13. Опции представления связей ячейки
В окне Cell Relationship Diagram Options можно установить следующие три группы параметров:
1) использовать для анализа только текущий лист 
, другие листы 
, и другие листы и другие книги 
;
2) показывать только влияющие ячейки (другие ячейки, от которых зависит текущая ячейка) 
, зависимые ячейки (ячейки, которые зависят от текущей ячейки) 
, или и те, и другие 
;
3) показывать определенное количество уровней отношений ячеек, например, 2 
, или все 
. Например, на рис. 12, если ячейка T9 зависит от ячейки D58 (на листе заметок), которая, в свою очередь, зависит от ячеек заметок!С47:С58 — это двухуровневое отношение ячеек. Кстати, будьте осторожны с опцией All. Мне удалось «обрушить» надстройку Inquire, задав эту опцию для весьма безобидной, на первый взгляд, ячейки…
Некоторые полезные нюансы работы со связями ячейки можно также найти в статье Просмотр отношений между ячейками на сайте Microsoft.
Очистка лишнего форматирования ячеек на листе. Форматирование ячеек на листе позволяет выделить нужные сведения, чтобы их было легко заметить, но при этом форматирование неиспользуемых ячеек (особенно целых строк и столбцов) может привести к быстрому росту размера файла рабочей книги. У меня на сайте есть весьма популярная заметка – Excel «тормозит». Что делать? К ней масса комментариев, и однажды мне прислали файл, который практически не хотел работать – простой переход с ячейки на ячейку занимал несколько секунд. Выяснилось, что была отформатирована последняя ячейка на одном из листов F1048576.
Используйте команду Clean Excess Cell Formatting / Удалить лишнее форматирование ячеек (см. рис. 5). Появится окно выбора: очистить от форматирования только активный лист или все листы в книге. Сделайте свой выбор и нажмите Ok. Если лишнее форматирование отображается на экране, вы сразу же увидите работу надстройки. Если лишнее форматирование «далеко», работа надстройки пройдет визуально незаметно. После выполнения операции очистки Excel предложит нажать Да для сохранения изменений или Нет, чтобы отменить сохранение. Не верьте Excel’ю! В любом случае, изменения сохраняться. Причем Ctrl-Z их не берет! Изменения не обратимы.
При очистке лишнего форматирования из листа удаляются отформатированные ячейки, расположенные после последней непустой ячейки. Например, если вы применили условное форматирование к целому столбцу А, но ваши данные располагаются только до А20, условное форматирование будет удалено из строк, расположенных за строкой 20.
В настоящем разделе под форматированием понимается выделение ячеек цветом, установление границ ячеек, условное форматирование, установление цвета или формата текста и многие другие «шалости», которые я рекомендую никогда не вводить для целых строк и столбцов.
Управление паролями. Если вы используете надстройку Inquire для выполнения анализа и сравнения книг, защищенных паролем, вам нужно добавить пароль книги в список паролей, чтобы надстройка могла открыть сохраненную копию книг. Используйте команду Workbook Passwords / Пароли книги, чтобы добавить пароли, которые будут сохранены на компьютере. Эти пароли шифруются и доступны только вам.
[1] По материалам книги Джона Уокенбаха «Excel 2013. Трюки и советы», а также официальных материалов Microsoft.
