Сводные таблицы в Access. Создание сводных таблиц. Построение диаграмм. Microsoft Office
Требования. Для выполнения этого занятия в системе должны быть установлены следующие компоненты.
• Для поддержки нужд анализа используются показатели объема продаж в реляционном хранилище данных InernetSales на сервере SQL Server compsrvПоскольку сводные таблицы Access одновременно работают только с одной таблицей или запросом, то в базе данных InernetSales создано представление dbo.vProductSales, объединяющее все необходимые для анализа таблицы (см. ПРИЛОЖЕНИЕ).
• Пользователь должен иметь разрешения на получение данных из базы данных
InternetSales
Это занятие содержит следующие задачи:
1. Создание сводных таблиц
2. Построение диаграмм
1. Создание сводных таблиц
1. Вставьте в Access анализируемую таблицу, подключившись к базе данных InernetSales на сервере compsrvи выбрав в качестве источника данных dbo.vProductSales:
2. Выберите на ленте Главная à Представления à Сводная таблица.
Для создания сводной таблицы нужно сообщить Access о том, какие поля пользовать в каждой части таблицы. Все сводные таблицы формируются из пяти компонентов:
• поля строк применяются для группировки записей в строках;
• поля столбцов используются для группировки записей в столбцах;
• поля итогов применяются для вычисления итоговых значений в каждой группе; в полях итогов или деталей обычно выводится на экран итоговая информация;
• поля фильтра применяются для сокращения числа записей сводной таблицы на основании заданного критерия.
3. Из окна Список полей сводной таблицы перетащите с помощью мыши
• поле ProductName в область Перетащите сюда поля строк;
• поле CalendarYear в область Перетащите сюда поля столбцов;
• поле SalesAmount (анализируемые данные) в область Перетащите сюда поля итогов или деталей;
• поле SalesTerritoryCountry в область Перетащите сюда поля фильтра.
4. Добавим итоговые вычисления. Щелкните правой кнопкой мыши в сводной таблице поле SalesAmount (подойдет любое поле SalesAmount) и выберите вариант из меню Автовычисления à Сумма.
Для удаления добавленных с помощью Автовычисления итогов щелкните любой итог правой кнопкой мыши и выберите команду Удалить.
Для отображения подробной информации либо отображения только итоговых данных щелкните кнопкой мыши соответствующий квадратик со знаком +/-.
5. Поля строк, столбцов и фильтра можно менять местами, отбуксировав мышкой на место соответствующего поля элементы.
2. Построение диаграмм
1. Перейдите в режим сводной диаграммы, выбрав на ленте Работа со сводными таблицами | Конструктор à Представления à Сводная диаграмма.
2. Выберите на ленте Работа со сводными таблицами | Конструктор à Тип диаграммы и опробуйте варианты визуализации данных.
Построение диаграммы
Выделяем диапазон I3: I7 м нажимаем кнопку «Мастер диаграмм» на панели инструментов «Стандартная». На первом шаге Мастера диаграмм выбираем тип «Гистограмма» и вид «Объемный вариант обычной диаграммы» (рис.2.8), нажимаем кнопку «Далее».
Рис.2.8 Выбор типа диаграммы
На втором шаге переходим на вкладку «Ряд» и в пункте «Имя» устанавливаем значение «=Лист1! $I$1» (имя ряда), а в пункте «Подписи оси Х» — значение «=Лист1! $A$2: $A$5» (рис.2.9). Нажимаем кнопку «Далее».
Рис.2.9 Шаг второй Мастера диаграмм
На третьем шаге на вкладке «Заголовки» вводим названия оси категорий и оси значений (рис.2.10), на вкладке «Легенда» отказываемся от отображения легенды, т.к в ней нет смысла при формировании диаграммы, состоящей из одного ряда, на вкладке «Подписи данных» устанавливаем флажок в пункте «Включить в подписи … значения» (рис.2.11).
Рис.2.10 Ввод заголовков осей
Рис.2.11 Установка отображения значений
На последнем шаге указываем поместить диаграмму на отдельном листе (рис.2.12).
Рис.2.12. Последний шаг Мастера диаграмм
Диаграмму распечатываем и приводим в Приложении А.
Access
Задана база данных «Учет выпускаемой продукции», состоящая из шести таблиц:
1. Список выпускаемых изделий
Код единицы измерения
3. Справочник единиц измерения
* Код единицы измерения
4. План выпуска изделий
2. Список выпускающих цехов
* Номер выпускающего цеха
5. Цеховая накладная сдачи продукции на склад
Номер выпускающего цеха
Количество по плану
* Номер цеховой накладной
6. Товарно-транспортная накладная
Пояснения по выполнению задания:
готовое изделие закреплено за одним складом готовой продукции, но может выпускаться несколькими цехами;
каждое изделие имеет только одну единицу измерения;
один цех может выпускать несколько наименований изделий;
на одном складе хранится несколько наименований готовых изделий;
количество изделий измеряется целым числом;
выпуск цехом готовой продукции планируется помесячно;
одно и то же изделие может быть запланировано к выпуску в разные месяцы квартала;
накладная цеха на сдачу готовой продукцию на склад может содержать несколько наименований изделий, ее номер уникален для каждого цеха.
Количество цехов должно быть не менее 4 и не более 6, изделий — не менее 7 и не более 9.
1. Используя таблицу «План выпуска изделий» и «Список выпускаемых изделий», вынесите в запрос «Номер выпускающего цеха», «Месяц выпуска», «Код изделия», «Наименование изделия», «Цена», «Количество по плану».
2. На основе запроса № 1 создайте форму, используя все поля, имеющиеся в запросе.
3. На основе запроса № 1 создайте запрос с вычисляемым полем, в котором будет определяться стоимость изделий по плану.
4. По запросу №3 составьте отчет.
Создание и связывание таблиц
Запускаем Microsoft Access. Для создания таблиц указанной структуры используем команду «Конструктор» на панели инструментов базы данных (3.1).
Рис.3.1 Запуск конструктора таблиц
Далее в окне конструктора таблиц вводим заданные названия полей и выбираем для них наиболее подходящие по содержательному смыслу полей типы.
Во всех таблицах, в которых это необходимо, устанавливаем ключевые поля. Так, например, в таблице «Список выпускаемых изделий» выделяем поле «Код изделия» и выполняем команду его контекстного меню «Ключевое поле» (рис.3.2).
Рис.3.2 Создание макета таблицы
Вводим в созданные таблицы произвольные данные. Затем выполняем создание схемы данных. Для этого выполняем команду меню «Сервис — Схема данных». В появившемся окне поочередно выбираем все таблицы и нажимаем кнопку «Добавить» (рис.3.3).
Рис.3.3 Включение таблиц в схему данных
Нажав кнопку «Закрыть», переходим к созданию связей между таблицами. Чтобы связать, например, таблицы «Список выпускаемых изделий» и «Справочник единиц измерения» по одноименным полям «Код единицы измерения», «перетаскиваем» поле из одной таблицы в другую. В открывшемся окне «Изменение связей» (рис.3.4) устанавливаем флажок в поле «Обеспечение целостности данных» и видим, что создан тип отношения «один-ко-многим».
Рис.3.4 Связывание таблиц
Закончив связывание таблиц, сохраняем схему данных распечатываем ее, выполняя команду «Печать схемы данных». Получаемый при этом отчет также сохраняем.
Построение диаграмм в формах
Диаграммы используются для наглядного представления информации из базы данных. В Access диаграмма как отдельный объект не существует, а может являться элементом формы либо отчета.
Для построения диаграмм в СУБД Access используется модуль MSGraph, в который передаются все исходные данные для построения диаграммы с помощью механизма обмена данными в Windows. Для передачи данных можно использовать Мастер диаграмм, существующий в Access.
2.7.1 Элементы диаграмм и подготовка исходных данных
Исходными данными для построения диаграмм могут быть данные таблиц либо запросов. Реальные таблицы в базах данных содержат огромное количество записей. Если при построении диаграммы не ограничить количество отображаемых в ней данных, то она будет загромождена излишними деталями. Поэтому чаще всего диаграммы строят по результатам запросов к базе данных.
Удобнее для построения диаграмм использовать итоговые или перекрестные запросы. Например, можно построить диаграмму по результату итогового запроса, подсчитывающего средний балл каждого студента за прошедшую сессию.
Основные элементы диаграмм Access показаны на рисунке 14 .
Рис. 14 Элементы диаграмм MS Access
Построение диаграммы с помощью Мастера диаграмм
Для создания диаграммы с помощью Мастера диаграмм нужно перейти на вкладку Формы и нажать кнопку Создать. В окне диалога Новая форма выбрать тип Диаграмма и указать источник данных для нее (таблицу или запрос). Сразу после этого начинается процесс построения диаграммы.
Во первом окне Мастера диаграмм нужно указать поля, необходимые при построении диаграммы. Для этого нужно скопировать их из списка Доступные поля в список Поля диаграммы.
Второе окно Мастера диаграмм служит для выбора типа диаграммы. Правильный выбор типа диаграммы имеет большое значение, т.к. неудачный выбор может привести к ложным выводам.
В третьем окне Мастера диаграмм можно изменить способ представления данных на диаграмме, меняя с помощью мыши положение кнопок с именами полей, расположенных в правой части диалогового окна. Результат построения диаграммы можно просмотреть, нажав кнопку Образец.
В последнем окне Мастера диаграмм вводится название диаграммы. На этом процесс построения диаграммы завершен.
Редактирование диаграмм
Поскольку возможности Мастера диаграмм ограничены, для оформления и редактирования диаграмм лучше использовать MS Graph, запуск которого осуществляется двойным щелчком мыши на диаграмме в форме, открытой в режиме Конструктора.
Каждый элемент диаграммы имеет определенный набор параметров, значения которых устанавливаются в соответствующем окне редактирования, которое открывается двойным щелчком мыши на необходимом элементе.
Отредактировать текст легенды или сами данные можно через таблицу данных, которая также отображается в режиме MS Graph.
4. Создание запросов на выборку к однотабличным и многотабличным субд access” Понятие запроса
При работе с таблицами можно в любой момент выбрать из базы данных необходимую информацию с помощью запросов.
Запрос — это обращение к БД для поиска или изменения в базе данных информации, соответствующей заданным критериям.
С помощью Accessмогут быть созданы следующие типы запросов: запросы на выборку, запросы на изменение, перекрестные запросы, запросы с параметром.
Одним из наиболее распространенных запросов является запрос на выборку, который выполняет отбор данных из одной или нескольких таблиц по заданным пользователем критериям, не приводящий к изменениям в самой базе данных.