Ускоряем работу в Excel - полезные плагины

Разделение данных по таблицам

Если вам часто приходиться формировать на базе одной таблицы несколько новых, включая в них некоторую информацию из исходной таблицы, то стоит обратить внимание на выше упоминавшиеся плагины Advanced Consolidation Manager, Tables Transformer for Excel и ASAP Utilities. Плагин Advanced Consolidation Manager окажется полезным при копировании данных в разные файлы с группировкой их по страницам - "в переводе на русский" это означает, что первые листы с данными из исходных файлов будут последовательно объединены в первой книге, вторые - во второй и т.д. Технология подобного разделения данных, в принципе, ничем не отличается от вышерассмотренного примера с объединением данных через этот же плагин - единственное, что выбирать в первом окне нужно будет режим "Копировать данные из нескольких файлов с группировкой по имени страницы". Плагин Tables Transformer for Excel также поможет быстро разделить одну таблицу на несколько новых. Но принципы разделения тут совершенно иные - в зависимости от настроек, плагин руководствуется либо указанным пользователем количеством строк, либо уникальными значениями ячеек в определенных столбцах. В первом варианте достаточно лишь указать программе, через какое число строк следует начинать новую таблицу. В результате такого разделения, в самой первой таблице окажутся самые верхние строки исходной таблицы, во второй - следующие за ними строки и т.д. Названия столбцов в каждой новой таблице могут быть продублированы, а новые таблицы окажутся размещенными в новой либо рабочей книгах (на текущем или новом листах) Подобный вариант разбиения может пригодиться, например, для получения на базе одного годового отчета по продажам с данными за каждый месяц четырех квартальных отчетов. Второй режим разбиения (с учетом уникальных значений ячеек в выбранном столбце), на наш взгляд, может потребоваться на практике чаще - скажем, он будет полезен для получения отдельных таблиц с данными по соответствующим уникальным значениям, например, по конкретным производителям, наименованиям продукции и т.п. Взяв за основу таблицу с данными о продукции от множества производителей, попробуем создать отдельные таблицы по каждому производителю. Для этого нужно открыть исходную книгу, щелкнуть по кнопке плагина, выбрать режим "Разделить таблицу", указать лист и диапазон с данными, выбрать в качестве способа разделения вариант "По уникальным значениям в столбцах" и указать столбец для разделения (в нашем случае это будет столбец "Vendor"). Возможности плагина ASAP Utilities в этом плане гораздо скромнее - им можно воспользоваться для формирования новых Excel-документов на основе отдельных листов исходной книги. Иными словами, каждый из листов исходной книги будет помещен в отдельный файл. Производится эта операция по команде "ASAP Utilities" > "Export" > "Export worksheets as separate files".

Использование автоформатирования и стилей

Как известно, в Excel предусмотрено автоматическое форматирование сводных таблиц (команда "Формат" > "Автоформат"), однако выбор предустановленных шаблонов там невелик. Поэтому чаще всего приходится прибегать к форматированию сводных таблиц вручную, но при их перестройке (например, при добавлении или удалении полей) часть сделанных настроек форматирования может теряться - в итоге, приходится затрачивать дополнительное время на повторное форматирование. Еще более актуально то, что каждую новую сводную таблицу вновь приходится форматировать, поскольку запомнить сделанные настройки форматирования в виде шаблона в Excel невозможно - подобной функциональности там просто нет. Поэтому при активном использовании сводных таблиц явно нелишней окажется установка плагина AutoFormat for PivotTables. В поставку плагина AutoFormat for PivotTables (3,76 Мб; 29,95 долл.; 15-дневная демо-версия) входит некоторый набор предустановленных и довольно привлекательных шаблонов, список которых может быть расширен путем скачивания дополнительных шаблонов с сайта программы либо за счет самостоятельной разработки. Последнее как раз и представляется самым интересным, поскольку после разработки нужных в работе шаблонов (понятно, что с последующим их сохранением в базе шаблонов), форматирование вновь создаваемых таблиц уже не будет отнимать львиную долю времени. Для его проведения достаточно будет просто запустить плагин, выбрать нужный шаблон из списка и щелкнуть на кнопке "Apply". Количество создаваемых пользовательских шаблонов не ограничено, и для удобства работы с ними шаблоны можно объединять в группы. Поддерживается импорт и экспорт пользовательских шаблонов, что позволяет восстанавливать их после переустановки системы и переносить между компьютерами. Кстати, по заверениям разработчиков, этот плагин может быть получен бесплатно теми пользователями, которые пришлют им свою коллекцию шаблонов. Что касается стилей, то широкое применение их на практике позволяет не только добиваться единообразного оформления материалов, но и существенно ускоряет процесс настройки внешнего вида таблиц. Увы, встроенные в Excel возможности работы со стилями довольно ограничены - их немного, а создание пользовательских стилей хотя и допускается, но использовать такие стили можно только внутри той книги, где они были созданы. Если же необходимо применить пользовательский стиль в другой книге (то есть не в той, где он был создан), то придется открывать обе книги и затем копировать стиль из одной книги в другую, что долго и неудобно. Если такое положение дел вас не устраивает, то стоит обратить внимание на плагин Style Manager for Excel. Плагин Style Manager for Excel (3,08 Мб; 19,95 долл.; 15-дневная демо-версия) поможет легко и быстро оформлять таблицы с помощью стилей, управление которыми производится через дополнительную панель "Style Manager", включаемую/выключаемую нажатием комбинации клавиш "Ctrl+Shift+S". Список входящих в поставку стилей достаточно велик (имеются стили для оформления заголовков, исходных данных, результирующих эти данные ячеек и т.п.). Более того, данный список без проблем может быть расширен за счет пользовательских стилей, создать которые совсем несложно - достаточно настроить параметры форматирования в одной из ячеек, по своему вкусу подобрав цвета, шрифты, границы и прочие настройки форматирования, щелкнуть на кнопке "Add" и ввести имя нового стиля. Созданные таким образом пользовательские стили в дальнейшем без ограничений могут быть использованы в любой книге. Все стили (как встроенные, так и пользовательские) отображаются на панели "Style Manager" в древовидной структуре, причем, для удобства применения, их несложно сгруппировать по папкам. При необходимости, любой из стилей можно перетащить из одной папки в другую, переименовать или удалить. Наложение на выделенные ячейки стиля не составляет ни малейшего труда - достаточно щелчка мыши по выбранному стилю.

Быстрый доступ к избранным книгам

Активно работающим в Excel пользователям часто приходится открывать по множеству книг, которые могут находиться в самых разных папках на различных дисках, включая сетевые. Поэтому перемещение по соответствующим папкам нередко оказывается весьма утомительным. Способов решения данной проблемы множество. Можно, к примеру, прямо на рабочем столе разместить ведущие к документам ярлыки, однако это не самая лучшая идея. Гораздо разумнее воспользоваться бесплатной Excel-надстройкой Favorite Bookmarks (509 кб). С ее помощью несложно создать нужный набор закладок на избранные папки с документами либо отдельные файлы, каждый раз щелкая на кнопке "Add" и указывая файл либо папку. Это позволит в дальнейшем открывать соответствующие папки либо загружать напрямую файлы всего одним кликом мыши. Созданным набором закладок можно управлять, перемещая их в желаемом порядке. Кроме того, для удобства ориентации, разрешается вставлять между закладками пустые строки либо метки.

Эффективная работа с книгами

Если одновременно приходится работать со множеством книг, постоянно переключаясь между ними, то имеет смысл обзавестись плагином Explorer for Microsoft Excel. Данный плагин (3,54 Мб; 19,95 долл.; 15-дневная демо-версия) обеспечивает удобное визуальное управление открытыми книгами и листами в них, отображая и те, и другие на специальной панели в виде дерева. Эта панель располагается в левой части экрана и скрывается/отображается выбором из меню Ablebits.com либо нажатием комбинации клавиш "Ctrl+Shift+N" (правда, при тестировании последней версии плагина в нашем случае названная комбинация почему-то работать перестала, чему объяснения мы так и не нашли). Через панель Explorer for Excel очень удобно переключаться между листами и книгами, просто подсвечивая их на дереве. Можно также переименовывать и распечатывать листы и книги, создавать, удалять листы и перетаскивать их между книгами. Одним кликом несложно выявить ошибки во всех открытых документах, ссылки, имена, комментарии, гиперссылки и ячейки, имеющие условное форматирование. Перечень допустимых действий в отношении этих объектов различен - скажем, условные форматы можно очистить, имена изменить, ошибки проигнорировать и т.д. Во многих таблицах в ряде ячеек значения вычисляются на основе данных из других листов либо даже других книг (файлов-источников), поскольку Excel позволяет связывать рабочие листы и отображать данные из одного рабочего листа в другом, вне зависимости от его расположения через внешние ссылки. Подобная возможность совершенно незаменима в случае регулярного обновления информации в файле-источнике либо необходимости обобщения данных из различных рабочих книг. За примерами такого рода задач далеко ходить не надо - скажем, требуется подготовить отчет по продажам, в котором некоторые исходные данные для вычислений (хотя бы текущие цены, которые ныне меняются не один раз на дню) берутся из связанного с рабочей книгой файла. А этот файл хранится в общей папке на сервере компании. При создании отчета можно пойти двумя путями - либо вручную каждый раз копировать в отчет изменившиеся цены, либо брать их напрямую из той самой книги, что находится в общей папке на сервере. Первый вариант весьма зануден, во втором достаточно будет лишь однажды настроить правильно формулы - и "дело в шляпе": в отчете всегда будут использоваться в расчетах самые последние данные. Но тут есть одно "но" - если кому-то придет в голову мысль переименовать исходный файл с ценами или переместить его в другую папку (пусть и находящуюся в той же самой общей папке), то все ссылки окажутся неработоспособными, а все связи, соответственно, нарушенными. Если такая ситуация приключилась, то придется выяснять, что произошло с исходным файлом (а таковых ведь может быть и несколько), и затем вручную вновь указать эти файлы во всех расчетных формулах, что потребует времени и нервов. Более быстрый способ решения проблемы - воспользоваться плагином Fix Broken Links for Excel. С помощью этого плагина (2,71 Мб; 1600 руб.; 20-дневная демо-версия), нарушенные связи могут быть восстановлены в автоматическом режиме. Это означает, что вручную указывать новые файлы-источники в формулах не придется - программа сделает это сама. Технически это будет выглядеть следующим образом - после запуска плагина выбирается режим "Восстановление связей", затем в пустой изначально список в следующем окне добавляют исходный файл, ссылки в котором необходимо восстановить. После этого указывают папки, в которых должен быть произведен поиск файла-источника, на который имеются ссылки в формулах исходного файла, указанного на предыдущем шаге. В итоге всего этого, плагин либо найдет файл-источник либо нет, а найдет он его лишь в случаях, если одна из указанных папок (но не подпапка) содержит файл-источник с исходным именем. Так что тут особо обольщаться не стоит - "чудес в этом мире не бывает" - и вам все равно нужно знать местонахождение файла-источника и его нынешнее имя. В случае безуспешного поиска в автоматическом режиме, придется указать файл-источник вручную - для этого на последнем шаге мастера, в режиме автоматического восстановления связей, в окне "Восстановление связей" нужно щелкнуть правой кнопкой мыши на ненайденном файле-источнике, в контекстном меню выбрать "Ручное восстановление" и самостоятельно указать файл. Конечно, в итоге, восстановление связей получается не в полном смысле автоматическим, но все равно это оказывается намного быстрее, чем восстановление таких нарушенных связей вручную средствами только Excel. Также стоит заметить, что плагин Fix Broken Links for Excel дополнительно умеет копировать и переносить связанные файлы с сохранением работоспособности связей. Данная возможность предотвратит проблемы с нарушением связей при изменении структуры папок на рабочем компьютере и в случае использования связанных документов на нескольких компьютерах - например, рабочем компьютере и ноутбуке. А значит, и избавит от неизбежных проблем восстановления связей.

Заключение

Мы остановились лишь на некоторых популярных расширениях для Microsoft Excel, отобрав наиболее интересные в плане функциональности инструменты для широкого пользователя и отбросив дорогостоящие наборы в лице, скажем, Productivity Suite for Microsoft Excel. При этом, далеко не в каждом продукте нами были полностью указаны все его возможности, поскольку своей задачей мы ставили не перечисление всех без исключения функций того или иного расширения, а решение конкретного спектра задач, часто возникающих при работе с большими объемами данных в Excel. И, разумеется, признаем, что у каждого активного Excel-пользователя имеется свой набор установленных плагинов, позволяющих оптимально решать какой-то конкретный спектр задач.