Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

71

Microsoft Excel 2013. Уровень 2. Расширенные возможности

В группе Стили сводной таблицы [PivotTable Styles] выбрать нужное форматирование.

В группе Параметры стилей сводной таблицы [PivotTable Styles Options] можно настроить отличие в оформлении, выбирая Заголовки строк [Row Headers], Заголовки столбцов [column Headers], Чередующиеся строки [Banded Rows], Чередующиеся столбцы [Banded Columns].

В группе Макет [Layout] можно выбрать:

Пустые строки [Blank Rows]– вставить или удалить пустую строку после каждого элемента.

Макет отчета [Report Layout]– в сжатом виде [Compact Form], в форме структуры [Outline Form] или в табличном виде [Tabular Form]. Если макет выбран в табличном виде или в форме структуры, то можно повторять все подписи элементов.

Общие итоги [Grand Totals]– выбрать расположение итогов: в строках и/или столбцах.

Промежуточные итоги [Subtotals] – наличие и место отображения промежуточных итогов

Данные сводной таблицы можно форматировать и с использованием условного форматирования. Для этого достаточно выделить ячейку поля и на вкладке Главная [Home], в группе Стили [Styles], раскрыть кнопку Условное форматирование [Conditional Formatting] и выбрать, например, команду

Гистограммы [Data Bars] или Наборы значков [Icon Sets].

Справа от выделенной ячейки появится смарт-тег Параметры форматирования [Formatting Options], раскрыв который можно выбрать нужный вариант применения правила форматирования.

Если возникает необходимость самостоятельно выделить ряд данных в отчете, то такая возможность существует. Следует подвести указатель мыши слева (сверху) от названия ряда и, увидев, черную стрелочку, щелкнуть – ряд будет выделен, затем его можно будет оформить.

Если черная стрелочка не появляется, то необходимо на вкладке Анализ [Analyze] в группе Действия [Actions], раскрыть кнопку Выделить [Select] и выбрать команду

Разрешить выделение [Enable Selection].

72

Microsoft Excel 2013. Уровень 2. Расширенные возможности

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

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

Для обновления данных отчета, щелкнуть правой кнопкой мыши по ячейке сводной таблицы и выбрать Обновить [Refresh Data] или на вкладке Анализ [Analyze] в группе

Данные [Data] нажать кнопку Обновить [Refresh].

Для настройки автоматического обновления при открытии файла:

1.Щелкнуть правой кнопкой мыши в область отчета сводной таблицы и выбрать Параметры сводной таблицы [PivotTable Options] или на вкладке Анализ [Analyze] в группе Сводная таблица [PivotTable] нажать Параметры [Options].

2.В диалоговом окне Параметры сводной таблицы [PivotTable Options] перейти на вкладку

Данные [Data], установить флажок Обновить при открытии файла [Refresh data when opening the file], ОК.

Чтобы посмотреть и при необходимости изменить диапазон исходных данных, надо на вкладке Анализ [Analyze], в группе Данные [Data], выбрать Источник данных [Data Source].

Если исходные данные постоянно изменяются, то для определения автоматического размера исходного диапазона, необходимо диапазон ячеек преобразовать в Таблицу:

1.Выделить любую ячейку таблицы данных.

2.На вкладке Вставка [Insert], выбрать Таблица [Table], ОК.

При выполнении команды Обновить все добавляемые строки/столбцы будут автоматически включены в источник.

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

Всводных таблицах можно группировать поля, которые расположены в области макета СТРОКИ

[ROWS] или КОЛОННЫ [Columns].

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

Группировка поля типа Дата/Время

1.Щелкнуть правой кнопкой мыши по любой ячейке нужного поля и выбрать Группировать

[Group].

2.Выбрать диапазон группировки данных: начиная с [Starting at] и по [Ending at], выделить требуемый шаг группировки в поле с шагом [By]: Дни [Days], Месяцы [Months], Кварталы

[Quarters], Годы [Years].

73

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Результат группировки даты по годам:

Группировка числового поля

1.Щелкнуть правой кнопкой мыши по любой ячейке нужного поля и выбрать Группировать

[Group].

2.Выбрать диапазон группировки данных: начиная с [Starting at] и по [Ending at], ввести требуемый шаг группировки в поле с шагом [By]

Результат группировки по числовому полю с шагом 5:

74

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Группировка текстового поля

1.Выделить в области СТРОКИ [ROWS] или КОЛОННЫ [COLUMNS] нужные элементы:

Выделить нужные ячейки поля с нажатой клавишей Ctrl .

Установить фильтр для отбора нужных записей, затем выделить результат отбора.

2.Щелкнуть правой кнопкой мыши по любой выделенной ячейке и выбрать Группировать

[Group].

3.Ввести имя группы с клавиатуры вместо имени Группа 1 [Group1].

Чтобы изменить имя полученного поля, надо щелкнуть правой кнопкой мыши по его ячейке и выбрать Параметры поля [Field Settings], затем ввести с клавиатуры в поле Пользовательское имя

[Custom name]

Для быстрой работы с группами данных, можно по ячейке, расположенной в области СТРОКИ [ROWS] или КОЛОННЫ [COLUMNS] щелкнуть правой кнопкой мыши и выбрать

Развернуть/свернуть [Exspande/Collapse], а затем нужный вариант:

Развернуть [Expand] – текущий элемент.

Свернуть [Collapse] – текущий элемент.

Развернуть все поле [Expand Entire Field] – развернуть все до уровня исходного поля.

Свернуть все поле [Collapse Entire Field] – свернуть все до уровня поля-группировки.

Свернуть до «Имя поля-группировки» [Collapse to «Имя поля-группировки»]

Развернуть до «Имя исходного поля» [Expand to «Имя исходного поля»]

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

Существует два способа построения сводных диаграмм: построение на основе диапазона исходных данных (автоматически строится макет сводной таблицы вместе со сводной диаграммой) или сводная таблица строится на основе построенной сводной таблицы. Второй вариант используется значительно чаще.

75

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Построение сводной диаграммы на основе построенной сводной таблицы:

1.Выделить ячейку сводной таблицы.

2.На вкладе Анализ [Analyze], в группе Сервис [Options], выбрать Сводная диаграмма

[PivotChart].

3.Выбрать тип диаграммы, нажать ОК.

Для фильтрации данных сводной таблицы можно использовать отчет сводной таблицы или удобно выполнять отбор данных на сводной диаграмме, используя фильтры:

ФИЛЬТРЫ [FILTERS]

ОСЬ (КАТЕГОРИИ) [AXIS (CATEGORIES)] – соответствуют области СТРОКИ [ROWS] в макете сводной таблицы – соответствуют области КОЛОННЫ [COLUMNS] в макете сводной таблицы.

ЛЕГЕНДА (РЯДЫ) [LEGEND (SERIES)]

Изменения, сделанные в отчете сводной таблицы (фильтрация, изменения макета строки и колонны), автоматически отображаются на сводной диаграмме и наоборот.

Работа со сводными таблицами аналогична работе с обычными диаграммами. При выделении сводной диаграммы появляются вкладки для работы со сводными диаграммами.

Построение сводных таблиц c использованием модели данных

В Excel 2013 версии анализ табличных данных удобно делать с использованием модели данных. Модель данных расширяет возможности анализа, добавляя экспресс-просмотр с детализацией данных и подсчет количества различных элементов.

Следует помнить, что в отчете, построенном по модели, действует ряд ограничений:

Нельзя будет применить Группировку полей.

Нельзя создать Вычисляемое поле.