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

76

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

Источник модели данных

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

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

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

Форматировать как таблицу [Format As Tables].

3.Указать расположение данных таблицы, OK.

Имя можно изменить:

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

2.На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы и нажать Enter .

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

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

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

3.В диалоговом окне Создание сводной таблицы [Create PivotTable] в поле Таблица или диапазон [Table/Range] будет

указано имя таблицы на основании которой строится отчет.

Выбрать место размещения сводной таблицы: На новый лист [New Worksheet] или На существующий лист [Existing Worksheet]

Установить флажок Добавить

эти данные в модель данных и [Add this data to the Data Model]. OK.

В области Поля сводной таблицы [PivotTable Fields] будет указано имя диапазона: имя таблицы или Диапазон1, Диапазон2 и т.д.

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

Экспресс-просмотр с детализацией данных

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

77

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

При выделении ячейки в области макета ЗНАЧЕНИЯ [ VALUES] справа появляется смарттег Экспресс-просмотр [Quick Explore], который позволит выбрать интересующее поле для детализации:

Результат мгновенно будет показан вместо существующего отчета на этом же листе:

Подсчет количества различных элементов

Использование модели данных открывает еще одну операцию для обработки данных – Число различных элементов [Distinct Count]. С ее помощью можно подсчитать сколько уникальных значений есть в исходной таблице при каких-нибудь критериях. Например, можно рассчитать по каждому товару сколько всего штук было поставлено, можно подсчитать сколько было поставок всего, а можно подсчитать количество уникальных поставок (заказов) – т.е. заказов с различным количеством по каждому товару:

78

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

ПРАКТИКУМ:

Создание

Открыть файл 03 Сводные таблицы из текущей папки.

По данным таблицы с листа ПРОДАЖИ НАБОРЫ построить сводную таблицу, позволяющую проанализировать суммы продаж каждого наименования по клиентам в зависимости от выбранного города. Создать отчет на новом листе. Расположить данные по клиентам в

названия строк, наименования – в названия столбцов, суммы – в Значения, а город – в фильтр отчета.

Переименовать лист в ОТЧЕТ НАБОРЫ.

Изменение макета

На листе ОТЧЕТ НАБОРЫ, сделать следующие изменения макета:

Переместить поле Наименование из области названия столбцов в названия строк.

Изменить расположение полей в области названия строк, чтобы просматривать данные по клиентам в зависимости от наименования.

Переместить поле Город из области фильтр отчета в область названия столбцов.

Включить флажок Отложить обновление макета и сделать преобразование макета отчета:

Удалить поле Наименование из отчета.

Переместить поле Город из области названия столбцов в названия строк.

Добавить поле Количество в область Значения.

Выполнить обновление макета.

Снять флажок Отложить обновление макета и добавить в отчет поле Дата, разместив его в фильтре отчета.

Фильтры и срезы

На листе ОТЧЕТ НАБОРЫ предоставить данные для анализа в период с 23 по 25 марта 2006 г только клиентами, в названии которых есть слово компания.

Очистить условия фильтрации.

Удалить поле Город из отчета сводной таблицы.

Вставить срезы по полям Город и Наименование.

Применить к срезам разные стили оформления.

Отобразить данные только по Москве, затем только по Казани. Очистить фильтр.

Отобразить данные продаж в городе Санкт-Петербурге наименований набор "Доброта",

набор "Мечта" и набор "Радость".

Очистить все фильтры.

Удалить срез Город и Наименование.

Настройка полей

79

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

На листе ОТЧЕТ НАБОРЫ настроить параметры полей:

Для Суммы – операция Сумма, имя Сумма RUB, числовой формат денежный.

Для Количества – операция Сумма, имя Кол-во, шт.

Удалить из отчета поле Клиент, расположив вместо него поле Наименование.

Добавить в отчет данные для анализа общих продаж по всем наименованиям:

Вычисление доли суммы продаж каждого наименования от общей суммы продажи. Назвать поле Доля продаж.

Рейтинга продаж каждого наименования. Максимальная сумма продажи должна соответствовать позиции 1. Назвать поле Рейтинг.

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

Результат вычислений можно сравнить с образцом:

Вычисляемые поля

На листе ОТЧЕТ НАБОРЫ добавить в отчет вычисляемые поля на основе существующего поля Сумма, руб:

СуммаEUR – при курсе 1€=41 р.

СуммаUSD – при курсе 1$=29 р.

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

Изменить формулу в вычисляемых полях, считая, что курс 1$=30 р., а 1€=40 р.

Сравнить полученный результат с образцом:

80

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

Форматирование отчета

Построить на новом листе отчет по данным таблицы с листа БЫТОВАЯ ТЕХНИКА для анализа количества брака (шт), который выявили Приемщики по каждому наименованию и каждому производителю.

Переименовать лист – АНАЛИЗБРАКА.

Отформатировать отчет:

Применить стиль сводной таблицы Средний в любой цветовой схеме.

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

Расположить все промежуточные итоги в нижней части группы.

На листе ПОСТАВКИ оформить отчет сводной таблицы:

Изменить макет отчета, чтобы показать в форме структуры.

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

Добавить расчет общих итогов как по строкам и столбцам.

Вставить пустую строку после каждого элемента.

Настроить повторение всех подписей элементов.

Обновление данных

В исходной таблице на листе БЫТОВАЯ ТЕХНИКА необходимо сделать изменения в исходных данных. Один из приемщиков сменил фамилию: была Толерантная Т.Т. – стала теперь Тихая Т.Т. Обновить данные отчета на листе АНАЛИЗБРАКА.

Отчет на листе ПОСТАВКИ построен на основе таблицы с листа БЫТОВАЯ ТЕХНИКА, в которую были добавлены записи. Обновить источник данных отчета, чтобы добавленные значения были учтены (в отчете после обновления должно появиться наименование Весы).

Группировка данных

По данным таблицы на листе КОМПЛЕКТЫ, построить отчет на этом же листе, разместив результат начиная с ячейки Н1, в котором можно видеть суммы продаж каждого наименования по всем годам.

По данным таблицы с листа БЫТОВАЯ ТЕХНИКА, предоставить данные поставок по количеству брака и его стоимости за следующие периоды: месяц, квартал, год. Отчет разметить на новом листе, который затем переименовать в БРАК.

На листе ЗАКАЗЫ по данным таблицы, построить отчет на этом же листе, разместив результат начиная с ячейки Н1, в котором можно проанализировать недельные суммы продаж подарочных наборов, начиная с 1-го понедельника года – 04.01.2010.

По данным таблицы на листе СТРАХОВАНИЕ, определить средние затраты сотрудников на страхование в зависимости от пола, начиная с 25-ти лет с шагом в 5 лет. Результат разместить на этом же листе, начиная с ячейки F1. Расчетные значения представить целым числом с обозначением знака $.