Файл: Методические указания для выполнения лабораторных работ по теме анализ и моделирование деятельности организации с целью принятия управленческих решений.doc
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 11.04.2024
Просмотров: 29
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
, другой сводной таблицы - выбрано внешний источник данных, поскольку, будем строить сводную таблицу, используя базу данных 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. Вот появляющееся сообщение о возникших у него трудностях:
Рис. 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.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. Анализ данных
Теперь, когда таблица построена, и начинается самый важный для пользователя этап содержательного анализа данных. Как уже говорилось, сводная таблица очень мощное и вместе с тем очень простое в использовании средство анализа данных. Вот лишь некоторые ее возможности:
-
Изменение структуры таблицы. Поля таблицы можно добавлять и удалять, путем перетаскивания их с инструментальной панели "Сводные таблицы". Можно изменять ориентацию измерений, меняя местами поля строк, столбцов и страниц. -
Фильтрация данных. Практически по каждому полю можно вводить фильтры. Так, например, выбрав в поле "Город" значение "Тверь", получаем фильтр для всех заказчиков из города Тверь. Выбрав для поля "Сотрудник" фамилию интересующего нас сотрудника, получаем соответствующий фильтр. -
Вычисляемые поля. Некоторые поля таблицы можно объявить вычисляемыми и задать различные функции, подводящие итоги. Для числовых полей это может быть сумма значений поля, максимум, минимум, среднее, среднеквадратическое отклонение или другая подобная функция из некоторого фиксированного набора. -
Визуализация данных. По сводной таблице можно построить сводную диаграмму, которая перестраивается в ответ на изменения, происходящие со сводной таблицей. Верно и обратное, можно перестраивать диаграмму, что отражается в перестроении сводной таблицы. -
Другие изменения. Их можно производить, используя контекстное меню, появляющееся при нажатии правой кнопки мыши, или используя возможности инструментальной панели "Сводные таблицы".
