Перейти к содержимому

7. Полезняшки Excel

Надстройка Power View в Excel 2013

Пользователи версии Excel 2013 Pro Plus могут активизировать надстройку Power View, позволяющую создавать панели с интерактивными элементами на основе данных из PowerPivot. [1] Чтобы проверить, какая версия программы у вас установлена, пройдите по меню Файл — Учетная запись. Например, дома у меня установлен Microsoft Office для дома и учебы 2013, а на работе – Microsoft Office профессиональный плюс 2013.

Вы сможете на одном интерактивном полотне собрать множество карт, сводных диаграмм и таблиц. Каждый элемент этой «мозаики» можно просмотреть в полный размер, щелкнув на нем мышью. Например, стоит лишь выделить категорию одной диаграммы, как тут же отобразятся соответствующие точки данных на другой диаграмме. Пользователям версии Office 2013 Pro Plus (и выше) надстройка Power View, как и PowerPivot, доступна бесплатно.

Подробнее »Надстройка Power View в Excel 2013

Обработка данных с помощью модуля PowerPivot

Бесплатно распространяемая надстройка PowerPivot появилась в версии Excel 2010. Она включает шесть весьма полезных инструментов и создана независимыми разработчиками. В Excel 2013 функции PowerPivot наряду с моделью данных встроены непосредственно в ядро программы. [1]

Модель данных, которая рассматривалась ранее, предоставляет доступ лишь к некоторым возможностям PowerPivot, а для получения доступа ко всем инструментам придется установить саму надстройку PowerPivot, входящую в комплект поставки Office 2013 Professional Plus. Надстройку можно также получить, подписавшись на Office 365 Business. Если же вы пользуетесь версией Office 2013 Home and Student либо Standard, механизм PowerPivot по-прежнему доступен, однако окно PowerPivot заблокировано, а доступ к некоторым возможностям закрыт.

Подробнее »Обработка данных с помощью модуля PowerPivot

Макросы в сводных таблицах

Макрос представляет собой последовательность действий, которая записана и сохранена для дальнейшего использования. Сохраненный макрос можно воспроизвести по специальной команде. Другими словами, вы можете записать свои действия в макросе, сохранить его, а затем разрешить другим пользователям воспроизводить сохраненные в макросе действия простым нажатием клавиши. Это особенно удобно при распространении отчетов сводных таблиц. [1]

Предположим, вы хотите предоставить своим клиентам возможность группировать отчеты сводных таблиц по месяцам, кварталам и годам. Технически процесс группирования может выполнить любой пользователь, но некоторые из ваших клиентов не сочтут нужным разбираться в этом. В подобном случае можно записать один макрос группирования по месяцам, другой — по кварталам и третий — по годам. Затем создайте три кнопки — по одной на каждый макрос. Тогда вашим клиентам, не имеющим опыта работы со сводными таблицами, потребуется лишь щелкнуть на кнопке, чтобы должным образом сгруппировать отчет сводной таблицы.

Главное преимущество использования макросов в отчетах сводных таблиц состоит в предоставлении клиентам возможности быстро выполнять в сводных таблицах такие операции, которые они не могут выполнить в обычной ситуации. Благодаря этому существенно повышается эффективность анализа предоставляемых данных.

Подробнее »Макросы в сводных таблицах

Советы по работе со сводными таблицами

В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. [1] Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?

Совет 1. Автоматическое обновление сводных таблиц

Иногда требуется, чтобы сводные таблицы обновлялись автоматически. Предположим, вы создали сводную таблицу для менеджера. Вряд ли вы сможете регулярно обновлять ее, разве что менеджер допустит вас к своему ноутбуку. Можно включить автоматическое обновление сводной таблицы, которое будет выполняться всякий раз при открытии книги:

  1. Щелкните правой кнопкой мыши на сводной таблице и в контекстном меню выберите пункт Параметры сводной таблицы.
  2. В появившемся диалоговом окне Параметры сводной таблицы выберите вкладку Данные.
  3. Установите флажок Обновить при открытии файла.

Рис. 1. Включите опцию Обновить при открытии файла

Рис. 1. Включите опцию Обновить при открытии файла

Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.

Подробнее »Советы по работе со сводными таблицами

Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ

Ранее я уже рассказывал о том, что при ссылке на ячейку сводной таблицы вместо обычной ссылки возвращается функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (см. Как научить Excel ссылаться на ячейку в сводной таблице, как на обычную). Если вас интересует как преодолеть это неудобство, рекомендую обратиться к упомянутой заметке. Если же вам интересно, почему так происходит, а также, какие положительные аспекты есть у функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, то предлагаю фрагмент книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013 (глава 15). Рассматриваемая методика позволит справиться со многими проблемами, вызывающими головную боль у пользователей сводных таблиц, в частности:

  • При обновлении сводной таблицы исчезает ранее примененное форматирование. Теряются числовые форматы. Пропадают результаты настройки ширины столбцов.
  • Не существует простого способа создания асимметричной сводной таблицы. Единственный вариант — использование именованных наборов, но этот метод доступен только для тех, кто использует сводные таблицы модели данных, а не обычные сводные таблицы.
  • Программа Excel не может запоминать шаблоны. Если нужно снова и снова создавать сводные таблицы, придется повторно выполнять группировку, применять вычисляемые поля и элементы и выполнять ряд других подобных задач.

На самом деле все, что здесь описано, не ново. Более того, подобные методики применяются начиная с версии Excel 2002. Однако мое общение с пользователями показывает, что менее 1% знакомы с ними. Единственный вопрос, возникающий у пользователей, заключается в том, как отключить странную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. А жаль…

Подробнее »Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ

Интерактивная веб страница на основе книги Excel

Если вы хотите предоставить доступ к сводной диаграмме другим пользователям, но не хотите, чтобы они изменяли структуру сводной таблицы либо получали доступ к исходным данным, отличным решением будет интерактивная веб страница, размещенная в облаке OneDrive.[1]

На рис. 1 показан лист Excelс отчетом Первые N заказчиков, включающий ряд срезов. Исходные данные и сводная таблица находятся на других листах.

Рис. 1. Рабочий лист Excel, отображаемый в виде веб-страницы

Рис. 1. Рабочий лист Excel, отображаемый в виде веб-страницы

Подробнее »Интерактивная веб страница на основе книги Excel

Внешние источники данных для создания сводной таблицы

Программа Excel является прекрасным средством обработки и анализа данных. По сути, сводные таблицы сами по себе являются доказательством аналитической мощи Excel. Однако, говоря обо всех достоинствах программы, нельзя не упомянуть и об одном существенном упущении. Программа Excel построена на относительно простой платформе управления данными, обладающей тремя недостатками. [1]

  • Размер набора данных сильно влияет на скорость обработки данных в сводной таблице. Это накладывает серьезные ограничения на эффективность использования сводных таблиц как самодостаточных структур. Причина подобного поведения программы — в специфике управления оперативной памятью. Файл при открытии в Excel полностью перемещается в оперативную память для более быстрой обработки и доступа к данным. Но при этом в программе не реализован надежный механизм оптимального управления оперативной памятью при извлечении из нее даже небольшого фрагмента данных. Несмотря на то что в Excel 2013 предусматривается использование до 1 млн. строк и 16 тыс. столбцов, даже средние по размеру наборы данных приводят к значительным задержкам при обработке.
  • Отсутствие реляционной модели данных вынуждает нас использовать «плоские» таблицы, которые хранят избыточные данные и увеличивают вероятность появления ошибок.
  • Отсутствие индексации полей данных в Excel для оптимизации процесса извлечения больших объемов данных.

Подробнее »Внешние источники данных для создания сводной таблицы

Сводная таблица на основе внутренней модели данных

В Excel 2013 появился новый аналитический механизм: модель данных. Каждая рабочая книга располагает собственной внутренней моделью данных, упрощающей анализ разрозненных источников данных. [1] Идея, заложенная в основу модели данных, проста. Предположим, что в вашем распоряжении имеются две таблицы: Заказы (рис. 1) и Сотрудники (рис. 2). В таблице Заказы содержится информация о сделках (код сотрудника, дата и сумма счета-фактуры, период продаж). В таблице Сотрудники находится информация о сотрудниках: код, фамилия, имя и должность. Если нужно проанализировать суммы продаж в зависимости от должности сотрудника, следует объединить информацию, содержащуюся в двух таблицах. Чтобы ваши данные имели вид Таблицы – инструмента Excel(поэтому пишется с заглавной буквы) – кликните на любой ячейке таблицы данных и выполните команду Создать Таблицу: Ctrl+T.

Рис. 1. Информация о сделках

Рис. 1. Информация о сделках

Подробнее »Сводная таблица на основе внутренней модели данных

Сводная таблица на основе нескольких листов или диапазонов консолидации

Нередко исходные данные хранятся не в одном диапазоне данных, а в нескольких, или на разных листах, а то и в различных книгах… Не говоря уже данных, хранящихся не в Excel, а в текстовых файлах, таблицах Access или SQL Server. В этой заметке будет рассмотрены приемы работы с множественными диапазонами, т.е. с отдельными наборами данных, расположенными в одной рабочей книге. Эти наборы либо разделены пустыми ячейками (рис. 1), либо находятся на разных рабочих листах. [1] В следующей заметке будут рассмотрено создание сводной таблицы на основе внешних источников данных.

Рис. 1. Лист с тремя диапазонами данных, которые нужно свести вместе, чтобы проанализировать их как единую группу

Рис. 1. Лист с тремя диапазонами данных, которые нужно свести вместе, чтобы проанализировать их как единую группу

Подробнее »Сводная таблица на основе нескольких листов или диапазонов консолидации

Условное форматирование в сводных таблицах

В версиях Excel, предшествующих Excel 2007, условное форматирование позволяло всего лишь динамически изменять цвета или текстовое форматирование значений в ячейках в зависимости от заранее заданных условий. [1] В Excel 2007 средства условного форматирования были значительно расширены возможностями визуализации, включая использование в ячейках гистограмм, цветовых шкал и наборов значков. Эти новые средства позволяют создавать окна в стиле управляющих консолей, позволяющих быстро найти информацию, выделенную самыми различными цветами и оттенками. И что более важно, теперь условное форматирование эффективно применяется в сводных таблицах. В частности, это означает применимость условного форматирования не только к данным, но и к структуре сводной таблицы в целом.

Прочитав эту заметку, вы научитесь применять средства условного форматирования в сводных таблицах, что позволит создавать красочные интерактивные презентации, не требующие применения сводных диаграмм. Начнем с простого примера сводной таблицы (рис. 1). Предположим, требуется в графическом виде получить отчет, который позволил бы менеджерам ознакомиться с объемами продаж, совершенными в каждом временном периоде. Для решения этой задачи можно создать сводную диаграмму либо воспользоваться условным форматированием. В рассматриваемом примере обратимся к упрощенному сценарию и выберем условное форматирование (гистограммы).

Рис. 1. Исходная сводная таблица

Рис. 1. Исходная сводная таблица

Подробнее »Условное форматирование в сводных таблицах