Использование инструмента «Сводные таблицы» MS Excel для подготовки аналитических отчетов

Лабораторная работа №6

Цель работы – приобретение практических навыков обработки статистических данных и формирования аналитических отчетов с помощью инструмента «сводные таблицы»

Краткая теоретическая справка

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

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

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

Для получения аналитической таблицы рекомендуется воспользоваться инструментом MS Excel «Сводные таблицы», выполнив следующую последовательность действий:

1. Поместить курсор мыши на область исходной таблицы и в меню «Вставка» выбрать пункт «Сводная таблица», щелкнув соответствующую пиктограмму:

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

Рисунок 1.1 - Диалоговое окно построения сводной таблицы

В результате выполнения данной операции появится макет сводной таблицы (см.рис.1.2), который необходимо заполнить.

Рисунок 1.2 – Макет сводной таблицы

Рисунок 1.3 – Фрагмент рабочего поля

Для формирования аналитической таблицы необходимо перемещать названия полей сводной таблицы из списка, расположенного в правом верхнем поле рабочего листа во вспомогательные поля «Фильтр отчета», «Названия столбцов», «Названия строк», «Значения».

Рассмотрим на примере:

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

· «Вид тарифа» поместить в поле «Фильтр отчета»,

· «Регион» - в поле «Названия строк»,

· «Абонентская плата» - в поле «å значения»

Рисунок 1.4 – Фрагмент рабочего поля

При перемещении поля «Абонентская плата» в область «å значения» по умолчанию будет задана операция суммирования. Щелкнув левой кнопкой мыши по соответствующему маркеру, появится раскрывающийся список, в котором необходимо выбрать пункт «Параметры полей значений».

Рисунок 1.5 – Фрагмент диалогового окна

В появившемся диалоговом окне может быть выбрана необходимая операция (например, расчет среднего значения).

Рисунок 1.6 – Установка параметров полей значений

Для повышения наглядности результатов воспользуйтесь инструментом «Условное форматирование». Результат запроса представлен на рисунке 1.7.

Рисунок 1.7 – Сводная таблица (результат формирования)

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

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

Для примера установите фильтр значений для Брянской, Воронежской и Владимирской областей.

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

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

Рисунок 1.8 – Аналитический отчет

Для проведения конкурентного анализа указанную информацию можно представить по каждому из провайдеров региональных рынков. Для этого в поле «Провайдер» переместите в область «Название строк».

Рисунок 1.9 – Установка полей сводной таблицы

Результаты запроса представлены на рис.1.10.

Рисунок 1.10 – Результаты запроса

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

Рисунок 1.11 – Запуск инструмента «параметры сводной таблицы»

Далее в появившемся диалоговом окне снять метку около поля «Автоматически изменять ширину столбцов при обновлении».

Рисунок 1.12 – Диалоговое окно определения параметров сводной таблицы

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

Важным преимуществом сводной таблицы является возможность использования вычисляемых полей. Рассмотрим на примере.

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

Для выполнения данного запроса необходимо построить сводную таблицу с вычисляемым полем. Для этого:

- в меню «Параметры» выбрать пункт «Формулы», щелкнув соответствующую пиктограмму ;

- в появившемся меню выбрать пункт «Вычисляемое поле»;

- в диалоговое окно ввести желаемое наименование поля и формулу.

Рисунок 1.14 – Вставка вычисляемого поля