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

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

Чтобы создать срез поместите указатель мыши в область сводной таблицы, выберите контекстную вкладку ленты Анализ и щелкните на кнопке Вставить срез (рис. 1). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Срез.

Рис. 1. Создание среза: шаг 1 – команда Вставить срез

Скачать заметку в формате Word или pdf, примеры в формате Excel

Появится диалоговое окно Вставка срезов (рис. 2). Устанавливаемые в этом окне флажки определяют критерии фильтрации. В рассматриваемом случае выполняется фильтрация по срезу Категория оборудования. Можно задать одновременно несколько срезов.

Рис. 2. Создание среза: шаг 2 – выбор критерия, по которому осуществляется фильтрация с помощью среза

Когда срез создан (рис. 3) можно щелкнуть на значениях фильтра для выполнения фильтрации сводной таблицы. Для выбора нескольких фильтров во время щелчка мышью удерживайте клавишу Ctrl.

Рис. 3. Настройка фильтра с помощью среза

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

Рис. 4. Подключение одного фильтра к нескольким сводным таблицам

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

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

Заметьте, что показанный на рис. 4 список сводных таблиц выглядит довольно абстрактно (СводнаяТаблица2, СводнаяТаблица3). Эти обобщенные имена автоматически присваиваются сводным таблицам и применяются для идентификации. На самом деле довольно сложно распознать нужную вам сводную таблицу на основе подобных обобщенных названий. Поэтому лучше выбирать более осмысленные названия, облегчающие идентификацию сводных таблиц. Чтобы изменить название сводной таблицы, установите указатель мыши в произвольном ее месте, выберите контекстную вкладку Анализ и введите понятное имя в поле Имя сводной таблицы, находящемся в левой части ленты (рис. 5).

Рис. 5. Присвоение сводной таблицы «говорящего» имени

Создание временной шкалы

Временная шкала появилась в Excel 2013. Этот фильтр напоминает стандартный срез тем, что для фильтрации сводной таблицы используется механизм визуального выбора фильтра вместо прежних полей фильтра. Различие же заключается в том, что временная шкала предназначена исключительно для работы с полями даты и обеспечения визуального способа фильтрации и группирования дат в сводной таблице.

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

Чтобы создать временную шкалу, поместите указатель мыши в область сводной таблицы, выберите вкладку ленты Анализ и щелкните на значке Вставить временную шкалу (рис. 6). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Временная шкала.

Рис. 6. Создание временной шкалы – шаг 1 – Вставить временную шкалу

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

Рис. 7. Создание временной шкалы – шаг 2 – выбор полей, для которых будут созданы временные шкалы

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

Рис. 8. Щелкните на выбранной дате для фильтрации сводной таблицы

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

Если вам нужно отфильтровать сводную таблицу по кварталам, щелкните на раскрывающемся списке временного периода и выберите параметр Кварталы. В случае необходимости можно также выбрать параметр Годы или Дни (рис. 9).

Рис. 9. Быстрое переключение между параметрами Годы, Кварталы, Месяцы или Дни

Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.

Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.

Макет любого среза можно изменить, кликнув на срезе и перейдя на контекстную вкладку Параметры из набора контекстных вкладок Инструменты для среза. Количество колонок с полями, отображаемыми в области среза, можно увеличить. Это особенно полезно, если элементов много. Перейдите в область Кнопки контекстной вкладки Параметры. С помощью кнопки-счетчика Столбцы увеличьте количество столбцов среза. Воспользуйтесь маркерами изменения размера для расширения либо сужения среза. Для каждого поля среза можно выбрать другой цвет с помощью коллекции Стили срезов (рис. 10).

Рис. 10. Форматирование срезов

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

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

[1] Заметка написана на основе официальных материалов Microsoft: Использование срезов для фильтрации данных сводной таблицы и Создание временной шкалы сводной таблицы для фильтрации дат, а также книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 2.