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

61

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

ПРАКТИКУМ:

В открытом файле 02_7 Импорт данных выполнить ряд настроек для работы с внешними данными:

Удалить связь с источником «Сотрудники компании».

Настроить обновление с интернет-ресурса при открытии файла.

Сохранить изменения в файле и закрыть его.

62

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

Модуль 3. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ

ТАБЛИЦ

Наиболее удобным встроенным инструментом для всестороннего и эффективного анализа больших табличных данных является сводная таблица.

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

Создание сводных таблиц

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

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

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

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

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

Рекомендуемые сводные таблицы [Recommended PivotTables].

3.Щелкнуть по макету сводной таблицы в списке слева, чтобы увидеть в режиме предварительного просмотра справа. Найдя подходящий, нажать OK.

63

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

Создание отчета вручную

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

2.На вкладке Вставка[Insert], в группе Таблицы [Table], выбрать Сводная таблица

[PivotTable].

3.В диалоговом окне Создание сводной таблицы [Create PivotTable] проверить правильность выделения диапазона данных или выделить новый источник данных в поле Таблица или диапазон [Table/Range], определить место размещения сводной таблицы: На новый лист

[New Worksheet] или На существующий лист [Existing Worksheet], нажать OK.

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

4.Сформировать отчет сводной таблицы.

На листе появилась графическая область с указанием имени сводной таблицы - Сводная таблица 1

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

Чтобы начать работу с отчетом сводной таблицы, щелкните в этой области [Click in this area to work with the PivotTable report],

при активной ячейке в области отчета надпись заменяется на

Чтобы построить отчет, выберите поля из списка полей сводной таблицы [To build a

report, choose fields the PivotTable Field List].

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

Макет отчета состоит из 4 частей:

ФИЛЬТРЫ [FILTERS] – фильтр отчета сводной таблицы. Если установлен фильтр, то построение и расчет данных сводной таблицы ведется для заданного значения.

64

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

СТРОКИ [ROWS] – формируют заголовки строк сводной таблицы, если размещено несколько полей, то они размещаются в макете сверху вниз, обеспечивая группирование данных сводной таблицы по иерархии полей (для каждого элемента внешнего поля, элементы внутреннего поля повторяются).

КОЛОННЫ [COLUMNS] –

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

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

ЗНАЧЕНИЯ [ VALUES] – обязательная область макета для размещения полей, по которым подводятся итоги, согласно выбранной функции. Размещаемые здесь поля могут быть произвольных типов. Если в расчетной области расположено несколько полей, то в области макета КОЛОННЫ [COLUMNS] автоматически появляется полеЗначения [ Values], которое можно при необходимости переместить в область

СТРОКИ [ROWS].

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

Добавить в фильтр отчета [Add to Report Filter], Добавить в названия строк [Add to Row Labels], Добавить в названия столбцов [Add to Column Labels], Добавить в значения [Add to Values] или перетащить поле в нужную область макета.

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

В любой момент макет сводной таблицы можно изменить. Для этого необходимо наличие области Поля сводной таблицы [PivotTable Fields]. Если при активной ячейке отчета сводной таблицы, список полей сводной таблицы не отображается, то его необходимо вернуть – щелкнуть правой кнопкой мыши по ячейке сводной таблицы и выбрать Показать список полей [Show Field List] или на вкладке Анализ [Analyze], в группе Показать [Show], выбрать Список полей [Field List].

Добавить поле в нужный раздел макета – перетащить поле из списка полей в нужную область раздела макета сводной таблицы.

Переместить поле из одного раздела макета в другую – перетащить поле в нужную область раздела макета.

65

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

Удалить поле из отчета – убрать флажок в списке полей, щелкнуть по полю в разделе макета и выбрать Удалить поле [Delete Field] или перетащить поле из раздела макета в список полей.

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

1.В области Поля сводной таблицы [PivotTable Fields] включить флажок Отложить обновление макета [Defer Layout Update].

2.Изменить макет.

3. Нажать кнопку Обновить [Refresh] для построения и расчета измененного отчета.

4. Убрать флажок Отложить обновление макета [Defer Layout Update].

Фильтрация данных

Фильтры

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

Если необходимо выполнить фильтрацию не помещая поле в отчет, то это можно сделать в области Поля сводной таблицы [PivotTable Fields], раскрыв список справа напротив имени поля.

Срезы

Улучшенная функция фильтрации позволяет анализировать большие объемы данных. Помимо фильтров, которые находят в фильтре отчета, а так же в строках и столбцах, для фильтрации данных в сводных таблицах можно также использовать срезы.

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