Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013

Рубрика: 7. Полезняшки Excel

Как-то так случилось, что в свое время я не обратил особого внимания на новые возможности Excel 2010. В частности, это относилось и к сводным таблицам. А когда в начале 2013-го я перешел на Excel2013, то решил «не размениваться» на изучение книг по Excel2010, и дождаться выхода книг, посвященных Excel2103.

Сводные таблицы – одна из любимых моих тем, потому об особенностях их использования в последней версии Excel я расскажу подробнее. Настоящая заметка открывает серию статей о сводных таблицах, и, во-первых, содержит перечень тем, которым посвящены отдельные посты, а во-вторых, некоторые отдельные замечания, которые мне показались интересными во время прочтения первых трех глав книги. Сразу должен извиниться перед начинающими пользователями сводных таблиц: я не буду рассказывать о базовых понятиях. Речь пойдет только о том, что мне (продвинутому пользователю) показалось интересным.

Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. – М.: Вильямс, 2014. – 448 с.

Джелен. Сводные таблицы в Microsoft Excel 2013. Обложка

Скачать краткий конспект в формате Word или pdf

Введение

Новые возможности сводных таблиц в Excel 2013

Глава 1. Основы сводных таблиц

Глава 2. Создание простейшей сводной таблицы

Excel2013. Основы работы с рекомендуемыми сводными таблицами

Excel2013. Срезы сводных таблиц; создание временной шкалы

Excel2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?

Новые инструменты для работы со сводными таблицами в Excel 2010

Глава 3. Настройка сводной таблицы

Создание стиля сводной таблицы в Excel 2010

Вычисления в сводной таблице (в области значений) в Excel 2013

Глава 4. Группировка, сортировка и фильтрация данных сводной таблицы

Группировка данных сводной таблицы в Excel 2013

Управление панелью Поля сводной таблицы

Сортировка данных сводной таблицы в Excel 2013

Фильтрация данных сводной таблицы в Excel 2013

Глава 5. Вычисления в сводных таблицах

Вычислительные поля и вычислительные элементы в Excel 2013

Глава 6. Сводные диаграммы и другие средства визуализации данных

Сводные диаграммы в Excel 2013

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

Глава 7. Использование нескольких источников данных в сводных таблицах

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

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

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

Глава 8. Совместное использование сводных таблиц

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

Глава 9. Кубы данных OLAP

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

Глава 11. Надстройка PowerView

Глава 12. Использование макросов для улучшения отчетов сводных таблиц

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

Глава 13. Использование VBA для создания сводных таблиц

VBA в сводных таблицах

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

Глава 15. Двуликая функция ПОЛУЧИТЬ. ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ

Введение. Новые возможности сводных таблиц в Excel 2013.

Находясь в одной из ячеек области исходных данных, можно щелкнуть правой кнопкой мыши, и выбрать Экспресс-анализ, перейти в область Таблицы и выбрать одну из четырех рекомендуемых сводных таблиц (рис. 1).

Рис. 1. Экспресс-анализ

Рис. 1. Экспресс-анализ; одна из четырех рекомендуемых сводных таблиц

Находясь в одной из ячеек области исходных данных пройдите по меню ВставкаРекомендуемые сводные таблицы. Если вы не знаете, каким образом создать сводную таблицу, в этом диалоговом окне вы сможете выбрать подходящую (рис. 2).

Рис. 2. Команда Вставка → Рекомендуемые сводные таблицы

Рис. 2. Команда ВставкаРекомендуемые сводные таблицы

В версии Excel 2010 начали применяться визуальные фильтры, называемые срезами. Ну а в Excel 2013 появился особый вид срезов, называемых временными шкалами и предназначенных для фильтрации сводных таблиц по полям дат. Для активации среза встаньте на ячейку, принадлежащую сводной таблице, и пройдите по меню Работа со сводными таблицами → Анализ → (область) Фильтр → Вставить временную шкалу (рис. 3).

Рис. 3. Срез в форме временной шкалы

Рис. 3. Срез в форме временной шкалы

Пользователи Excel 2013 могут создавать сводные таблицы на основе нескольких рабочих листов (чего нельзя было сделать в предыдущих версиях; см., например, заметку об Excel2007 Сводная таблица на основе нескольких листов). Для этого предусмотрена специальная опция (рис. 4).

Рис. 4.  Создание сводных таблиц на основе нескольких рабочих листов

Рис. 4.  Создание сводных таблиц на основе нескольких рабочих листов

Пользователи версий Office 365 либо Excel Professional Plus могут установить надстройку PowerPivot. С ее помощью можно связывать таблицы, рабочие листы, источники данных SQL Server и другие объекты. Надстройка PowerPivot расширила возможности вычисляемых полей.

Надстройка PowerView позволяет анимировать сводные таблицы с помощью инструмента создания произвольных запросов.

В Excel 2013 сохранилась поддержка возможностей, которые впервые появились в Excel 2010:

  • Функции, применяемые для определения ранга, процента от родительского столбца/строки и процента от суммы с нарастающим итогом в поле.
  • Для заполнения пустых ячеек, находящихся в крайних полях столбцов сводных таблиц, теперь применяется команда Повторять все подписи элементов.
  • С помощью срезов обеспечивается создание визуальных фильтров.

Все файлы примеров из книги можно загрузить здесь.

Глава 2. Создание простейшей сводной таблицы

Некоторые пользователи любят «красиво» форматировать исходные данные (рис. 5). К сожалению, после этого они совершенно не подходят для использования в качестве источника данных для сводной таблицы.

Существует весьма хитрый метод заполнения пустых ячеек (только в английской версии программы. – Примеч. ред.). Выделите весь диапазон данных. Перейдите на вкладку Главная ленты и щелкните в разделе Редактирование на кнопке Найти и выделить. В раскрывающемся меню выберите команду Перейти. В диалоговом окне Переход щелкните на кнопке Выделить. В диалоговом окне Выделение группы ячеек установите переключатель Пустые ячейки. Выделив все пустые ячейки (эта ситуация как раз и отражена на рис. 5), начните ввод формулы нажатием =, затем нажмите клавишу ↑ (стрелка вверх, расположенная между буквенной и цифровой частями клавиатуры), а после этого комбинацию клавиш <Ctrl+Enter>, чтобы занести формулу во все пустые ячейки (фактически это ввод формулы массива). После этого не забудьте скопировать и выполнить специальную вставку значения для преобразования формул в значения. (Проверил, действительно не работает в русском Excel. – Прим. Багузина)

Рис. 5.  «Красивый» отчет не подходит в качестве источника данных для сводной таблицы

Рис. 5.  «Красивый» отчет не подходит в качестве источника данных для сводной таблицы

Скучаете по старому доброму методу перетаскивания полей? Один из методов управления сводными таблицами, которым уже невозможно воспользоваться, начиная с версии Excel 2007, — это перетаскивание полей непосредственно в отчет сводной таблицы. Отныне перетаскивание полей осуществляется только в пределах окна области задач Список полей сводной таблицы. Но для вас есть и хорошая новость, которая заключается в том, что компания Microsoft включила в Excel 2010 поддержку классического макета сводной таблицы, характерного для предыдущих версий программы. Чтобы перейти к классическому интерфейсу управления макетом сводной таблицы, щелкните в любом ее месте правой кнопкой мыши и выберите в контекстном меню команду Параметры сводной таблицы. В появившемся на экране диалоговом окне перейдите на вкладку Вывод и установите флажок Классический макет сводной таблицы (разрешено перетаскивание полей). Щелкните ОК (рис. 6).

Рис. 6. Для возможности перетаскивания полей непосредственно в отчете сводной таблицы установите флажок Классический макет

Рис. 6. Для возможности перетаскивания полей непосредственно в отчете сводной таблицы установите флажок Классический макет…

Глава 3. Настройка сводной таблицы

Замена пустых ячеек нулями. Хорошим тоном разработки сводных таблиц считается отсутствие пустых ячеек в области числовых значений рабочего листа. Чтобы заменить в сводной таблице пустые ячейки нулями, выполните следующие действия.

  1. Выделите ячейку в сводной таблице.
  2. Откройте диалоговое окно Параметры сводной таблицы. Для этого: (а) на вкладке Анализ щелкните на кнопке Параметры, находящейся в группе Сводная таблица, и выберите в раскрывающемся меню команду Параметры; (б) щелкните правой кнопкой мыши и выберите пункт Параметры сводной таблицы.
  3. На вкладке Макет и формат диалогового окна Параметры сводной таблицы в разделе Формат поставьте галочку на поле Для пустых ячеек отображать и введите значение 0 (по умолчанию ноль там уже стоит) (рис. 7).
  4. Щелкните ОК, чтобы применить внесенные в настройки изменения.

Рис. 7. Замена пустых ячеек нулями в окне Параметры сводной таблицы

Рис. 7. Замена пустых ячеек нулями в окне Параметры сводной таблицы

Изменение имени поля сводной таблицы. Просто встаньте на ячейку и введите требуемое имя. Помните, что использовать имя, уже имеющееся в сводной таблице в качестве названия поля, нельзя. Чтобы обойти это ограничение, введите в конце названия пробел. Например, поле «Доход˽» (с пробелом в конце) и «Доход» (без пробела) программа Excel будет рассматривать как разные объекты сводной таблицы. Данная хитрость не видна пользователям сводной таблицы, а потому о ней будете знать только вы, ее создатель.

Заполнение пустых ячеек. Если информация, выведенная в сводной таблице, будет скопирована в новую область и будет использоваться в дальнейшей обработке, пустые ячейки нежелательны (рис. 8а). Перейдите на вкладку Конструктор в область Макет и щелкните на кнопке Макет отчета. В выпавшем меню щелкните на Повторять все подписи элементов. Пустые ячейки в области Строк сводной таблицы заполняться (рис. 8б).

Рис. 8. Заполнение пустых ячеек

Рис. 8. Заполнение пустых ячеек с помощью опции Повторять все подписи элементов: (а) исходный вид сводной таблицы; (б) после включения опции

Чтобы выделить сводную таблицу целиком воспользуйтесь комбинацией клавиш Ctrl+Shift+* (к сожалению, область Фильтров не выделяется).

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

Рис. 9. Перемещение промежуточных сумм в верхнюю часть группы данных

Рис. 9. Перемещение промежуточных сумм в верхнюю часть группы данных позволяет уменьшить общий размер сводной таблицы: (а) промежуточные итоги внизу; (б) промежуточные итоги вверху

Отключение не всех промежуточных итогов, а только по одному полю. Если в отчете содержится много полей строк, вывод промежуточных итогов может сбить вас с толку. Например, как показано на рис. 10, промежуточные итоги не требуются для каждого рынка сбыта отдельно, поскольку за каждый рынок отвечает единственный менеджер.

Рис. 10. Затрудняющие восприятие промежуточные итоги

Рис. 10. Затрудняющие восприятие промежуточные итоги

Кликните правой кнопкой мыши на ячейку в столбце Рынок сбыта, выберите опцию Параметры поля, перейдите на вкладку Промежуточные итоги и фильтры, и в области Итоги, установите Нет (рис. 11).

Рис. 11. Отключите промежуточные итоги только для поля Рынки сбыта

Рис. 11. Отключите промежуточные итоги только для поля Рынки сбыта

Получившаяся сводная таблица выглядит более профессионально (рис. 12).

Рис. 12. Сводная таблица с отключенными промежуточными итогами по полю Рынки сбыта

Рис. 12. Сводная таблица с отключенными промежуточными итогами по полю Рынки сбыта; промежуточные итоги по полю Регион по-прежнему отражаются

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

Рис. 13. Установите пять видов промежуточных итогов для поля Регион

Рис. 13. Установите пять видов промежуточных итогов для поля Регион

Получившаяся сводная таблица (рис. 14) несколько громоздка, зато весьма информативна.

Рис. 14. Сводная таблица с пятью промежуточными итогами для поля Регион

Рис. 14. Сводная таблица с пятью промежуточными итогами для поля Регион

Обратите внимание, что все промежуточные итоги рассчитываются не от чисел, видимых в сводной таблице, а от исходных данных, на основе которых сформирована сводная таблица.

Добавление пустых строк. Раскрывающееся меню Пустые строки (вкладка Конструктор, область Макет) позволяет добавлять пустые строки между группами данных (рис. 15). Эта настройка может применяться только к сводным таблицам, включающим более одного поля в области строк. Пустые строки облегчают восприятие больших сводных таблиц и выглядят профессионально!

Рис. 15. Пустые строки, отделяющие блоки сводной таблицы

Рис. 15. Пустые строки, отделяющие блоки сводной таблицы

Если несколько сводных таблиц расположены одна под другой, то в случае изменения ширины столбца одной сводной таблицы может измениться ширина столбцов другой сводной таблицы. В результате в сводных таблицах могут появиться символы ####, показывающие, что содержимое ячейки не поместилось, и не может быть отражено полностью. Подобное поведение объясняется тем, что по умолчанию Excel автоматически изменяет ширину столбцов, подгоняя ее под содержимое последней обновленной сводной таблицы. Чтобы изменить заданное по умолчанию поведение, щелкните правой кнопкой мыши на каждой сводной таблице (из тех что расположены друг под другом), и выберите пункт Параметры сводной таблицы. На вкладке Макет и формат снимите галочку Автоматически изменять ширину столбцов при обновлении (см. рис. 7).

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

Будьте предельно внимательны при перетаскивании полей в область Значения. Если полученные суммы кажутся вам подозрительно маленькими, обязательно проверьте, какой тип функции применяется в итоговых вычислениях. Вполне возможно, что вместо суммы по полю вычисляется количество записей. Для изменения неправильной формулы выделите одну из ячеек в столбце сводной таблицы. Щелкните правой кнопкой мыши. Выберите опцию Итоги по, и Сумма (рис. 16).

Рис. 16. Выберите Сумма в качестве операции, обрабатывающей исходные данные

Рис. 16. Выберите Сумма в качестве операции, обрабатывающей исходные данные

 

Комментарии: 3 комментария

На самом деле фишка с заполнением пустых ячеек работает. Просто надо сначала прописать эту формулу в одной пустой ячейке, а потом уже выполнить все пункты, указанные выше. И в конце нажать Enter. Все пустые ячейки заполнятся формулой.

Прописать формулу и скопировать её*

Спасибо за статью! Также нашел хороший материал про сводные таблицы и примеры использования на сайте excelhack.ru


Прокомментировать