!_МУ для лаб. Методические указания для выполнения лабораторных работ по теме анализ и моделирование деятельности организации с целью принятия управленческих решений

• Название: Методические указания для выполнения лабораторных работ по теме анализ и моделирование деятельности организации с целью принятия управленческих решений
• Дата: 03.02.2023
• Размер: 2.84 Mb.
• Имя файла: !_МУ для лаб.doc
• Тип: Методические указания; #918480
• страница: 4 из 13

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

Сводные таблицы обычно строятся вручную специальным инструментом - Мастером сводных таблиц.

Рассмотрим на конкретном примере построение таблицы с использованием Мастера сводных таблиц.

2.1. Создадим базу данных

Для примера откроем уже имеющеюся базу данных «dbPP2000» со структурой на схеме рис.2.4. Добавим в базу еще 2-е таблицы Заказано и Заказы

задание: создать их и ввести по 6- записей данных


Рис. 2.4.  Схема данных базы "dbPP2000"

• 
  Заказы. Эта таблица имеет следующие поля: КодЗаказа, Заказчик, Сотрудник, ДатаЗаказа, Стоимость. Код заказа играет роль ключа и является уникальным полем, автоматически заполняемым в момент записи заказа в базу данных. Следующие два поля совпадают с соответствующими полями таблиц "Заказчики" и "Сотрудники" и, тем самым, связывают эти таблицы. Каждый заказ хранит информацию не только о заказчике, но и о сотруднике офиса РР, оформляющего этот заказ. Следующие два поля - ДатаЗаказа и Стоимость (общая стоимость заказа) не требуют особых пояснений. Так выглядит общая информация о заказе.
  
• 
  Заказано. Эта таблица имеет следующие поля: КодЗаказа, НазваниеКниги, Количество, Стоимость. В этой таблице одному заказу будет соответствовать, как правило, несколько записей. Каждая запись - строка таблицы - содержит данные об одной из заказываемых книг. Все записи одного заказа будут иметь один и тот же код заказа, совпадающий с кодом заказа таблицы "Заказы". Поле "КодЗаказа" связывает между собой таблицы "Заказы" и "Заказано". Заметьте, повторяющийся код заказа не может быть ключом для таблицы "Заказано". Роль ключа в этой таблице играют два поля - КодЗаказа и НазваниеКниги, которые в совокупности являются уникальными для каждой записи таблицы "Заказано", хотя каждое из них в отдельности уникальным полем не является и может многократно повторяться в записях таблицы. Поля этой таблицы - Количество и Стоимость - задают количество экземпляров заказываемой книги и их суммарную стоимость. Как выглядит содержание этих таблиц в Конструкторе Access показано на рис. 2.5 – 2.6.
  

Рис. 2.5.   Определение таблицы "Заказы" в конструкторе


Рис. 2.6.  Определение таблицы "Заказано" в конструкторе

Задание: Добавьте в новые таблицы записи, например, Заказы:


Рис. 2.7 Таблица "Заказы" в Excel


Заметьте, все связи между таблицами имеют тип "один ко многим". Так, например, одному коду заказа из таблицы "Заказы" соответствуют несколько записей с аналогичным кодом в таблице "Заказано". Аналогично, одной фамилии (полю ФИО) из таблицы "Сотрудники" соответствует несколько записей в таблице "Заказы", поскольку понятно, что один сотрудник может оформлять множество заказов.

Итак, база создана!

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

• 
  Как шли продажи в стоимостном и количественном исчислении за те или иные периоды времени?
  
• 
  Какие книги продавались наиболее успешно?
  
• 
  Кто из сотрудников офиса оформлял наибольшее число заказов?
  
• 
  С кем из заказчиков шла наиболее успешная работа?
  
• 
  С какими городами шло наиболее успешное сотрудничество?
  

Сводная таблица должна позволять менеджеру, проводящему анализ отвечать на все эти вопросы. Вся необходимая информация хранится в таблицах: "Заказы", "Заказано", "Заказчики". Поля из этих таблиц и будут использоваться при построении сводной таблицы.

2.2. Построим сводную таблицу

Последовательность действий

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


Рис. 2.8.  Первое окно Мастера сводных таблиц и диаграмм

Заметьте, из четырех возможностей задания разных типов источников данных - списков Excel, внешних источников, нескольких диапазонов, другой сводной таблицы - выбрано внешний источник данных, поскольку, будем строить сводную таблицу, используя базу данных Access. Вторая группа переключателей позволяет задать желаемый вид отчета - сводную таблицу или сводную диаграмму, построенную на основе сводной таблицы. Пример с диаграммой приведем чуть позже, а сейчас займемся чисто сводными таблицами. Сделав выбор, остается нажать кнопку "Далее", чтобы сделать очередной шаг. Вот окно, открываемое на втором шаге:


Рис. 2.9.  Окно второго шага Мастера сводных таблиц

Получать данные из внешних источников сам Мастер сводных таблиц не умеет, а посему он предлагает на этом шаге обратиться к другому инструментальному средству - Microsoft Query, предназначенному для работы с запросами баз данных, широко используемому в Access и всюду в Office, где приходится работать с базами данных. Нажатие кнопки "Получить данные" запускает этот инструментарий, и первое появившееся окно позволяет выбрать тип источника данных:


Рис. 2.10.  Выбор источника данных при запуске Microsoft Query

Здесь тоже идет речь о выборе типа источника данных, но уже на другом уровне. Три вкладки: "Базы данных", "Запросы", "Кубы OLAP" позволяют сделать выбор на этом уровне. В данном случае выберем вкладку "Базы данных" и в открывшемся списке укажем тип базы данных - MS Access Database. Заметьте, включен флажок "Использовать мастер запросов". По нажатию кнопки "OK" открывается окно, где можно задать путь к базе данных:



Рис. 2.11.  Задание пути к базе данных
В следующем окне Мастера запросов начинается собственно формирование запроса на основании таблиц и запросов базы данных и содержащихся в них полей, называемых здесь столбцами. В открывающемся списке показаны все таблицы и все запросы базы данных "dbPP2000". Каждый элемент списка можно раскрыть, нажав значок "плюс", и перенести нужные поля таблицы в запрос, на основании которого строится сводная таблица. Включим в запрос (сводную таблицу) данные из трех таблиц:

• 
  поля "НазваниеКниги", "Количество", "Стоимость" из таблицы "Заказано",
  
• 
  поле "Город" из таблицы "Заказчики",
  
• 
  поля "Заказчик", "Сотрудник", "ДатаЗаказа" из таблицы "Заказы".
  

Взгляните на результат работы:


Рис. 2.12.  Выбор полей базы данных для включения их в сводную таблицу.

Нажатие кнопки "Next" заставляет Мастера запросов перейти к очередному шагу. Что будет выполняться на следующем шаге, зависит от того, сумеет ли Мастер запросов извлечь требуемые данные из таблиц базы данных. Если проблем у него не возникает, то Мастер запросов предложит создать фильтр для отбираемых данных. Вот как выглядит соответствующее окно в несколько более простой ситуации, когда не включено поле "Город" в число полей, запрашиваемых для построения сводной таблицы.


Рис. 2.13.  Запрос на построение фильтров
Окно, следующее за построением фильтров, позволяет задать требуемый порядок сортировки данных. Здесь его не приводим.

На рис. 2.14 показано окно, завершающее построение запроса для рассматриваемой ситуации:


Рис. 2.14.  Завершающий шаг построения запроса
На этом шаге можно сохранить запрос, нажав соответствующую командную кнопку. Здесь также следует сделать выбор одной из трех возможностей:

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

Обычная практика состоит в том, что выбирается первый пункт, и данные возвращаются в Excel. Пока рассмотрим исходную ситуацию, когда поле "Город" включено в запрос. Эта ситуация при построении запроса оказалась чуть более сложной, и у Мастера построения запросов возникли некоторые трудности, - он оказался не в состоянии разобраться в связях между таблицами базы данных, и попросил выполнить эту работу вручную, перейдя в Microsoft Query. Вот появляющееся сообщение о возникших у него трудностях:


Рис. 2.15.  Сообщение о возникших трудностях у мастера запросов

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

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


Рис. 2.16.  Установление связей между таблицами

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




Рис. 2.17.  Новое состояние окна Мастера сводных таблиц на втором шаге

Заметьте, что теперь, в отличие от рис. 2.9, наряду с уведомлением о получении данных стала доступной кнопка "Далее", которую нажмем для перехода к последнему шагу работы Мастера:


Рис. 2.18.  Заключительный шаг работы Мастера сводных таблиц

На заключительном шаге работы можно указать рабочий лист и ячейку, начиная с которой будет располагаться сводная таблица. Заметьте, наряду с кнопкой "Готово", нажатие которой завершает работу Мастера, в нашем распоряжении есть и другие кнопки, в частности, кнопка "Макет". Которую и нажмем! Вот как выглядит окно макета сводной таблицы:


Рис. 2.19  Макет сводной таблицы
На макете представлена схема сводной таблицы, - четыре области таблицы, озаглавленные соответственно "Страница", "Строка", "Столбец" и "Данные". На макете также представлены поля, отобранные для построения сводной таблицы. Каждое из полей может быть перетащено в одну из областей таблицы. Заметьте, вовсе не обязательно перетаскивать сразу все поля. Как уже говорилось, одно из достоинств сводных таблиц состоит в том, что их структуру можно легко перестраивать в зависимости от целей, которые менеджер, работающий с таблицей, ставит при анализе данных.

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

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




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

В данном примере разместим все поля и приведем некоторые аргументы в пользу выбранного варианта размещения полей:

• 
  В область данных поместим поля "Стоимость" и "Количество". Это, наверное, совершенно естественный выбор, когда речь идет об анализе продаж какого либо товара. Область данных в этом случае отображает данные о продажах в количественном и стоимостном выражении.
  
• 
  В область столбцов поместим поле с названиями книг. По сути, это названия продаваемых товаров.
  
• 
  В область строк поместим два поля - "ДатаЗаказа" и "Сотрудники".
  
• 
  В область страниц поместим два поля - "Заказчики" и "Город", задающий расположение заказчиков.
  

Вот, что получилось в результате этих действий:




Рис. 2.21.  Сформированная сводная таблица

В таком виде таблица содержит полную информацию о продажах, ее единственный недостаток - в ней слишком много подробностей.

Чаще всего, для понимания сути явления нужно уметь скрывать детали.

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

Выделив в сводной таблице поле "Дата" и нажав правую кнопку мыши, из появившегося контекстного меню выберем пункт "Группа и структура" и подпункт "Группировать", где и зададим группировку дат по годам, кварталам и месяцам:

Рис. 2.22.  Группировка дат

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




Рис. 2.23.  Сводная таблица в результате группировки дат
2.4. Анализ данных

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

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

Инструментальная панель "Сводные таблицы"

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

Вид инструментальной панели показан на предшествующих рисунках, например, на рис. 2.23. Как уже говорилось, в ней можно выделить две части - в одной из них расположен набор инструментальных кнопок, задающих те или иные функции, во второй - набор полей, доступных при формировании структуры сводной таблицы. Рассмотрим более подробно, что можно делать с помощью кнопок инструментальной панели:

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

Сводные диаграммы

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




Рис.2.24.  Лист со сводной диаграммой
Заметьте, на лист диаграмм вынесены и поля таблицы, что позволяет задавать фильтры, мгновенно изменяя диаграмму. При работе с листом диаграммы доступны и кнопки инструментальной панели.

Отметим, что диаграмма и таблица жестко связаны, -любые изменения в диаграмме и сводной таблице взаимосвязаны!