Лекция 4. Анализ информации в списках Excel С использованием сводных таблиц

 

План лекции

4.1 Основные сведения о сводных таблицах

4.2 Управление сводными таблицами

 

4.1 Основные сведения о сводных таблицах

 

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

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

 

А)	В)
Б)
Рисунок 4.1 – А) доступ к процедуре «сводная таблица»; Б) список меню Сводная таблица; В) диалоговое окно «Создание сводной таблицы»

 

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

На следующем шаге процедуры «Создание сводной таблицы» происходит переход к тому месту рабочей книги Excel, которое было указано в диалоговом окне на рис. 4.1. Вид рабочего листа после следующего шага процедуры «Создание сводных таблиц» приведен на рис. 4.2 (справа – макет сводной таблицы).

Рисунок4.2 - Построение отчета сводной таблицы

 

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

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

 

 

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

- нет

- отличие

- доля

- и др.

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

Рисунок4.3 - Выбор функций (операций) для итоговых расчетов в сводной таблице

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

Решение

Ответить на задание примера 6 можно под различными углами зрения, которые зависят от знаний и навыков менеджера. Рассмотрим 2 из них.

1) Создадим сводную таблицу, в которой будут отражены финансовые потоки для поставщиков проекта. На рис. 4.4 А) и Б) приведен макет и сводная таблица с перечнем поставщиков и денежных сумм, оплаченных поставщикам проекта.

Если необходимо рассмотреть отток денежных средств по датам, то в макете сводной таблицы нужно поле Поставщик заменить полем Дата. Результат построения такой сводной таблицы приведен на рис. 4.4.В).

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

А)	Б)
В)
Рисунок 4.4 – А) подготовка макета и Б) результаты построения простой сводной таблицы

 

Чтобы создать рассматриваемую сводную таблицу нужно сначала создать макет. Один из вариантов макета приведен на рис. 4.5.

Рисунок 4.5 - Макет сводной таблицы для примера 6

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

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

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

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

 

А)

Б)

Рисунок 4.6 - Сводные таблицы: А) таблица с полной информацией; Б) таблица со свернутыми наименованиями товаров

 

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

 

 

Рисунок 4.7 – Сводная диаграмма для таблицы из рис. 4.4 Б.

 

Кнопки сводных диаграмм можно удалить из графика. Это можно сделать с использование контекстного меню или из опции менюАнализировать, которая появляется при активизации сводной диаграммы (рис. 4.8.

 

А)	Б)
Рисунок 4.8 – Возможности скрыть кнопки сводной диаграммы: А) из контекстного меню; Б) из меню Анализировать

На рис. 4.9 А и 4.9 Б приведены уже отредактированные сводные диаграммы (на них отсутствуют кнопки сводных диаграмм, легенды и заголовки), на которых отражены материальные и денежные потоки системы снабжения проекта.

А)

Б)

Таким образом, задание примера 6 выполнено.

 

Т.к. списки Excel являются базами данных, то они могут подвергаться изменениям. Однако изменения в исходном списке не приводят к автоматическому обновлению (перерасчету) в сводной таблице. Чтобы изменения в списке отразились в результатах сводной таблицы нужно при активной сводной таблице на вкладке Параметры щелкнуть кнопку Обновить (см. рис. 4.10).

 

	Рисунок 4.10 - Обновление сводных таблиц

Вопросы для самопроверки

 

1. Сформулируйте различия между сводными таблицами и промежуточными итогами списковExcel.

2. Можно ли сводную таблицу интерпретировать как запрос к базе данных (списку)Excel?

3. Что такое макет сводной таблицы?

4. Как управлять макетом сводной таблицы?

5. Где находится область фильтра в макете сводной таблицы?

6. Можно ли изменить положение столбцов и строк в сводной таблице на рабочем листе?

7. В каких сводных таблицах появляются знаки структуризации?

8. Как построить сводную диаграмму?

9. Что представляют собой кнопки сводной диаграммы?

10. Как удалить или добавить кнопки сводной диаграммы к графику?

11. В какой момент в меню Excelпоявляется опцияАнализировать?

12. Происходят ли изменения в сводной таблице автоматически после изменения информации в списке?