Анализ и обработка данных с помощью сводных таблиц

Сводная таблица (pivot table) – представляют собой динамически изменяемые перекрестные таблицы данных. Они позволяют выполнять сложный анализ и обработку данных.

Предположим, что база данных содержит сведения о сотрудниках бюро научно-технической информации (БHTИ), которые выполняют обслуживание клиентов, предоставляя им услуги по поиску научно-технической информации, редактированию, изданию научно-технической документации.

Сводные таблицы обеспечивают обработку сложных запросов к базам данных.

В нашем примере с помощью сводных таблиц можно получить сведения о том, кто из сотрудников БНТИ выполнил те или иные услуги, какова стоимость этих услуг, каким клиентам и из каких организаций были оказаны эти услуги. Кроме того, изменяя сводные таблицы («перекручивая» их), можно определить, какова общая стоимость услуг, кто из клиентов получил услуг на большую сумму, какие услуги наиболее выгодно оказывать, какие услуги чаще всего приходится оказывать, дату оказания услуги, сколько клиентов было обслужено в тот или иной день и т.д.

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

Базу данных, сохраненную в рабочей книге, называют табличной.

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

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

Поля-категории – описания данных: наименование услуги, фамилия сотрудника, оказавшего услугу, фамилия клиента, который был обслужен, дата обслуживания.

 

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

Сводные таблицы создают с помощью мастера.

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

Итак, пусть база данных имеет вид, представленный на рис.8.1.

Задание 8.1. Исследуйте механизм создания сводной таблицы, выполнив следующие операции:

1. Дважды щелкните по ярлыку рабочего листа Лист1. Переименуйте его, назвав именем Исходные данные. Разместите на листе базу данных (рис.8.1).

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

3. В этом же диалоговом окне определяется место расположения сводной таблицы: на существующем листе или на новом. Выберите переключатель На новый лист и нажмите кнопку ОК.

4. Excel создаст пустую сводную таблицу и откроет боковую панель Список полей сводной таблицы. В ней поля базы данных отображаются в виде флажков. Каждое поле следует разместить (перетащить с помощью «мыши») в одну из 4 областей: Фильтр отчета, Названия строк, Названия столбцов, Суммарные значения. Помимо этого появятся контекстные инструментальные ленты Работа со сводными таблицами: Параметры и Конструктор.

 

Рисунок 8.1.Исходная табличная база данных

 

Рисунок 8.2. Диалоговое окно мастера сводных таблиц

Рисунок 8.3. Средства для создания сводной таблицы

 

.

 

 

5. Щелкните левой кнопкой манипулятора мышь по полю Наименование и удерживая кнопку мыши перетащите поле в область Фильтр отчета;

6. Щелкните левой кнопкой манипулятора мышь по полю Клиент и перетащите его в область Названия столбцов.

7. Щелкните левой кнопкой манипулятора мышь по полю Сотрудник и перетащите поле в область Названия строк.

8. Щелкните левой кнопкой манипулятора мышь по полю Количество и перетащите его в область Значения (Данные). В результате получится макет сводной таблицы, который представлен на рис.8. 4.

 

Рисунок 8.4. Мастер сводных таблиц после размещения на нем полей

 

9. Щелкните дважды по ярлычку рабочего листа со сводной таблицей. Переименуйте рабочий лист, выбрав имя Услуги БНТИ. Вы получили первую сводную таблицу. Содержание сводной таблицы полностью определяется тем, какое поле и в какую область вы поместите.

10. Сводную таблицу можно рассматривать как многомерную информационную структуру. Содержание сводной таблицы можно конкретизировать, указав конкретные услуги, сотрудников или клиентов. Например, раскройте список Наименование и выберите в нем Ксерокопирование.

 

 

11. Вы получите сводную таблицу, содержащую следующие сведения

 

Рисунок 8.5. Сводная таблица Услуги БНТИ

 

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

1. Определить какие услуги оказывались каждому из клиентов и в какие дни декабря;

2. Определить общий объем услуг, оказанных БНТИ фирме «Кверти»;

3. Определить даты оказания услуг клиенту Ястребову и общее количество дней, в которые он получал услуги.

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

Изменение структуры таблицы

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

Задание 8.3. Исследуйте средства изменения структуры сводной таблицы, выполнив следующие операции:

1. Перемещение строк и столбцов:

- Откройте сводную таблицу Услуги БНТИ (рис.8.5). Скопируйте таблицу на следующий лист и откройте боковую панель Список полей сводной таблицы. На боковой панели щелкните по полю Сотрудник в области Названия строк и удерживая левую кнопку мыши перетащите поле в область Названия столбцов;

- Таким же образом перетащите поле Наименование услуги в область Названия столбцов;

- Перетащите поле Клиент в область Фильтр отчета;

- Выберите в раскрывающемся списке поля Клиент значение (все). В этом случае сводная таблица отобразит данные о том, какие услуги были оказаны каждым сотрудником БНТИ всем клиентам.

2. Добавление полей в сводную таблицу. Откройте Список полей сводной таблицы, щелкните левой кнопкой мыши по полю Дата оказания услуги и перетащите поле в область строк (или столбцов). Теперь мы по сводной таблице можете определить, в какие дни были оказаны те или иные услуги каждым из сотрудников.

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

· Добавьте в область строк поле Организация, предварительно открыв Список полей сводной таблицы. В результате будет получена новая сводная таблица.

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

 

 

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

· Щелкните левой кнопкой мыши по любой ячейке сводной таблицы. В результате появятся контекстная инструментальная лента Работа со сводными таблицами: Параметры, Конструктор. Выберете вкладку Параметры, команду Формулы, Вычисляемое поле. В результате появится диалоговое окно Вставка вычисляемого поля.

 

· В окне Вставка вычисляемого поля напишите в строке Имя название вычисляемого поля: Денежная сумма. В строке Формула напишите формулу. Для этого в списке Поля следует щелкнуть левой кнопкой мыши по имени поля Стоимость услуги, а потом нажать кнопку Добавить поле. Указанное поле появится в строке Формула. Затем следует ввести знак «*», выбрать поле Количество, снова нажать кнопку Добавить поле. В результате в строке Формула будет записана формула: Стоимость* Количество. Далее следует нажать кнопку Добавить, после чего в сводной таблице появятся данные Сумма по полю Денежная сумма.

· Добавить в таблицу вычисляемое поле Денежная сумма с учетом НДС. Вычисления выполнить по формуле: Денежная сумма+ Денежная сумма*0,2.

5. Обновление данных. При изменении исходных данных следует выполнить команду Обновить (Работа со сводными таблицами, Параметры, Данные, Обновить). В результате данные в сводной таблице, в том числе и итоговые (К примеру, данные в строке и в столбце «Общий итог») будут изменены, т.е. вычислены заново.

• Щелкните по ярлыку рабочего листа Исходные данные, замените услуги Ламинирование на Ксерокопирование. Измените значение поля Стоимость услуг, установив значение 1.
• Щелкните по ярлыку рабочего листа Услуги БНТИ. Щелкните по кнопке Обновить. Убедитесь, что данные в сводной таблице, в том числе и итоговые показатели, изменились.

 

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

 

6. Группирование элементов сводной таблицы. В Excel предусмотрено средство, которое позволяет группировать определенные элементы полей сводной таблицы. К примеру, если поле состоит из дат, то для каждой даты в сводной таблице будет отведена строка или столбец. Иногда это бывает неудобно, поскольку сводная таблица содержит слишком много деталей. В этом случае даты объединяют по кварталам или месяцам. Вернемся к нашей базе данных, представленной на рис.8. 1. и к сводной таблице Услуги БНТИ.

· Откройте сводную таблицу и в списке значений поля Наименование (см. рис.5) выберем значение ( Все ).

· Предположим, что нас интересует, на какую сумму услуги были предоставлены частным предпринимателям. Из базы данных мы знаем, что таковыми являются Грачев и Дятлов. Выделите эти элементы поля Клиенты. Поскольку элементы поля расположены не рядом, то выделяют их, нажав предварительно клавишу Ctrl.

· Выполните команду Работа со сводными таблицами, Параметры, Группировка, Группировка по выделенному. Появится новый столбец Группа 1.

·

7. Автоматическое создание группы.

· Создайте новую сводную таблицу, разместив в области Суммарные значения поле Наименование услуг, в области Названия строк поле Сотрудник, в области Фильтр отчета поле Клиент, в области столбцов поле Дата оказания услуги. Полученная сводная таблица отображает количество услуг, которые конкретный сотрудник оказывает каждому из клиентов в указанном временном диапазоне;

· Щелкните правой кнопкой мыши по элементу поля Дата оказания услуги;

· Выберите в контекстном меню команду Группировать. Далее в диалоговом окне Группирование, оставив шаг группирования в месяц, нажмите кнопку OK. Будет выполнена группировка по месяцам.

 

 

 

Выводы:

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

2. Методика применения сводной таблицы предполагает следующие этапы:

- формулировка вопроса на языке предметной области;

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

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

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

 

Список литературы

1. Г., Харт-Дэйвис. Excel 2007. Полное руководство. Секреты профессионалов. Москва: НТ: Пресс, 2008.

2. А.Н., Васильев. Финансовое моделирование и оптимизация средствами Excel 2007. Санкт-Петербург: Питер, 2009.

3. Хелдман К., Хелдман У. Excel 2007. Руководство менеджера проекта. Мастер-класс. Москва: Эксмо, 2008.

 

Оглавление

1. Создание информационной среды для решения пользовательской задачи и ввод исходных данных 4

1.1. Особенности работы внутри главного окна Excel 2007. 4

1.1.1. Запуск Excel 4

1.1.2. Главное окно Excel 2007. 4

1.2. Ввод данных и перемещение по листу. 13

2. Основные приемы редактирования и форматирования электронных документов с помощью Microsoft Excel 16

2.1. Особенности редактирования и форматирования электронных таблиц. 16

2.2. Средства форматирования электронных таблиц. 19

2.2.1. Ввод последовательностей чисел и дат. 19

2.2.2. Копирование формата с помощью кнопки Формат по образцу. 20

2.2.3. Форматирование чисел. 20

2.3. Создание числового формата. 22

2.4. Создание формата с помощью условного форматирования. 22

3. Особенности форматирования финансовых документов. 23

3.1. Постановка задачи на разработку электронного документа и основные этапы его создания 23

3.2. Ввод и форматирование реквизитов документа. 26

3.3. Использование абсолютной адресации ячеек. 27

3.4. Вставка графики и защита документа. 28

3.4.1. Создание диаграмм для визуализации данных. 28

3.4.2. Ограничение доступа и защита рабочих книг. 34

4. Средства анализа данных в электронных таблицах. 37

4.1. Разработка документа для анализа. 37

4.2. Оперирование данными. Сортировка данных. 39

4.3. Поиск, фильтрация, редактирование в списках и базах данных. 40

4.3.1. Автофильтрация. 40

4.3.2. Расширенный фильтр. 42

4.3.3. Фильтрация и помощью вычисляемого критерия. 44

4.3.4. Скрытие ненужных столбцов и строк таблицы.. 44

4.4. Анализ документа с помощью диаграммы.. 44

5. Таблица подстановки и диспетчер сценариев как средства решения задач экономического характера 46

5.1. Электронная таблица как динамическая модель. 46

5.2. Таблица подстановки с одной ячейкой исходных данных. 47

5.3. Таблица подстановки с двумя ячейками исходных данных. 50

5.4. Диспетчер сценариев. 52

6. Создание связей между таблицами. 58

6.1. Специальная вставка. 58

6.2. Вычисления с помощью специальной вставки. 60

6.3. Консолидация данных. 61

6.3.1. Консолидация по физическому расположению.. 62

6.3.2. Консолидация по заголовкам.. 63

7. Поиск решения и подбор параметров. 65

7.1. Поиск решения. 65

7.2. Решение уравнений с помощью утилиты Подбор параметра. 72

8. Анализ и обработка данных с помощью сводных таблиц. 74

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

8.2. Изменение структуры таблицы.. 77

Список литературы.. 81

 

 

[1] Если команды Поиск решения на вкладке Данные нет, то ее нужно активизировать, выполнив следующие операции: нажмите кнопку Office и выберите команду Параметры Excel. Откройте вкладку Надстройки и нажмите кнопку Перейти. В диалоговом окне Надстройки установите флажок Поиск решения.