Урок 8. анализ данных

Урок 8. анализ данных

Вы научитесь:

группировать данные и вычислять итоговые значения;

изменять заголовки столбцов;

выполнять расчеты с помощью запросов;

использовать перекрестные запросы;

представлять результаты запроса в виде диаграмм.

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

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

Начало урока

Запустите программу Microsoft Access, если она еще не загружена. Откройте базу данных Сластена Лили. Разверните " окно базы данных до размеров полного экрана.

За дополнительной информацией об открытии базы данных обратитесь к уроку 1.

Создание запросов с группировкой данных

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

Запросы с группировкой данных очень удобны для анализа информации. Список, содержащий сведения о продажах наборов, может состоять из тысяч строк. Составить по нему представление о ходе продаж практически невозможно, вследствие его объема. Чтобы оценить спрос на тот или иной набор, необходимо вычислить общее число продаж каждого набора (иначе говоря, сгруппировать список продаж по наборам).

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

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

Создайте запрос на основе существующего

1. В окне базы данных щелкните на вкладке Запросы (Queries) и выделите запрос Информация о заказах.

2. Щелкните на кнопке Конструктор (Design View). Запрос Информация о заказах откроется в режиме Конструктор (Design View).

3. В меню Файл (File) щелкните на команде Сохранить как/экспорт (Save As/ Export). Появится диалоговое окно Сохранение объекта (Save as).

4. Убедитесь, что выделен вариант В текущей базе данных (With The Current Database As). В поле Новое имя (New Name) введите Итоги по странам и затем щелкните на кнопке ОК. Наименование Итоги по странам отобразится в строке заголовка запроса, а также в списке запросов в окне базы данных. Ваш экран должен выглядеть, как на следующей иллюстрации.

Добавьте и удалите поля в запросе

Присвоив имя запросу, определите поля, содержащие нужные данные.

1. В бланке запроса щелкните вверху столбца Фамилия, чтобы выделить его и затем нажмите клавишу ( Del |. Столбец Фамилия будет удален.

Бланк запроса также именуют сеткой QBE (Query By Example).

2. Удалите из запроса поля Код заказчика и Дата заказа.

Можно выделить оба поля сразу, выделив вначале столбец Код заказчика и протащив указатель мыши направо, чтобы выделить поле Дата заказа.

3. Перетащите поле Страна из таблицы Заказчики поверх поля Код заказа в бланке запроса. Поле Страна образует первый столбец на бланке запроса, сместив вправо Код заказа.

Выполните вычисления и группировку данных

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

1. На панели инструментов щелкните на кнопке Групповые операции (Totals).

На бланке запроса появится строка Групповая операция (Total), содержащая в каждом столбце значение «Группировка».

2. Щелкните в ячейке строки Групповая операция (Total) в столбце Код заказа и затем щелкните на стрелке.

3. В ниспадающем списке выберите пункт Count.

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

4. Щелкните на стрелке кнопки Вид (View) и затем щелкните на пункте Режим таблицы (Datasheet View).

Рисунок на кнопке Вид (View) зависит от выбранного режима. Результаты запроса отобразятся в режиме таблицы.

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

Сгруппируйте данные по двум полям

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

1. Щелкните на кнопке Вид (View) и затем щелкните на пункте Конструктор (Design View).

2. Перетащите поле Штат/провинция из таблицы Заказчики в бланк запроса поверх ячейки Код заказа. Между столбцами Страна и Код заказа появится новый столбец Штат/провинция, содержащий значение «Группировка» в строке Групповая операция (Total).

3. Щелкните на стрелке кнопки Вид (View) и выберите Режим таблицы (Datasheet View), чтобы посмотреть результаты запроса.

Microsoft Access сгруппирует данные вначале по странам (поскольку это первое поле группировки на бланке запроса), а затем по штатам или провинциям, для которых и вычисляются итоговые значения.

Присвоение столбцам содержательных заголовков

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

Измените заголовок столбца

В предыдущих упражнениях Microsoft Access автоматически присвоил столбцу заголовок «Соип1_Код заказа». Можно заменить его более понятным, например, «Число заказов».

1. Щелкните на кнопке Вид (View) и затем щелкните на пункте Конструктор (Design View).

2. На бланке запроса щелкните слева от наименования поля Код заказчика, чтобы поместить туда курсор. Введите Число заказов: (включая двоеточие).

Совет. Для наглядности после двоеточия можно ввести пробел. Пробел не отображается в других режимах представления при использовании данного поля.

3. Щелкните на стрелке кнопки Вид (View) и выберите Режим таблицы (Datasheet View), чтобы посмотреть результаты запроса.

4. Закройте запрос и щелкните на кнопке Да (Yes) в ответ на вопрос, хотите ли вы сохранить изменения.

Анализ данных с помощью запросов с вычислениями

В предпринимательской деятельности постоянно возникает вопрос: «А что, если?». Компания «Сластена Лили» не является исключением. Так например, учитывая широкий спрос на продукцию компании, менеджер по продажам хотел знать, как изменятся доходы в целом, если увеличить стоимость наборов на несколько процентов.

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

Создание запросов

1. Убедитесь, что вкладка Запросы (Queries) открыта, и щелкните на кнопке Создать (New).

2. В диалоговом окне Новый запрос (New Query) выделите пункт Конструктор (Design View) и щелкните на кнопке ОК.

3. В диалоговом окне Добавление таблицы (Show Table) выделите таблицу Наборы и щелкните на кнопке Добавить (Add). Эта таблица содержит исходные данные для запроса.

4. Закройте окно Добавление таблицы (Show Table).

5. Перетащите поля Название набора и Цена набора в бланк запроса. В запросе будут перечислены наборы с указанием их цен.

6. Щелкните на стрелке кнопки Вид (View) и выберите пункт Режим таблицы (Datasheet View), чтобы посмотреть результаты запроса.

На экране отобразятся текущие цены на каждый набор, как показано на следующем рисунке.

7. Сохраните запрос, присвоив ему имя «Рост цен».

Добавьте вычисляемое попе

Включите в запрос вычисляемое поле, которое будет содержать увеличенные на 5\% цены.

1. Переключитесь в режим Конструктора (Design View) и затем в строке Поле (Field) щелкните в пустой ячейке справа от столбца Цена набора, чтобы поместить в нее курсор.

2. Введите [Цена набора] *1.05 и затем нажмите клавишу Enter.

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

Умножение цены на 1,05 соответствует ее повышению на 5 процентов. После нажатия клавиши Enter Microsoft Access присваивает новому полю название «Выражение!». Это имя появится в заголовке столбца в режиме таблицы.

3. Щелкните дважды на правой границе области выделения нового поля Выражение1: [Цена набора]* 1,05, чтобы отобразить выражение полностью. (Область выделения поля располагается над его названием.)

4. Переключитесь в Режим таблицы (Datasheet View), чтобы посмотреть результаты. Запрос должен выглядеть, как показано на следующем рисунке.

Измените свойства вычисляемого поля

Наименование «Выражение! » присвоено полю автоматически и не отражает его содержания. Чтобы сделать запрос более наглядным, можно изменить заголовок столбца и представить новые цены в денежном формате. Кроме того, существует возможность снабдить поле описанием, которое поясняет, какие данные в нем представлены. В этом упражнении воспользуйтесь окном свойств, чтобы изменить свойства полей запроса.

1. Переключитесь в режим Конструктор (Design View) и щелкните дважды на имени поля Выражение1, чтобы выделить его.

2. Введите Новая цена. Поле теперь будет иметь имя Новая цена.

3. Щелкните на кнопке Свойства (Properties). Появится диалоговое окно Свойства поля (Field Properties).

4. Чтобы изменить формат отображение новых цен в результатах запроса, щелкните в поле Формат поля (Format) и затем щелкните на стрелке, чтобы вывести список форматов.

5. Выделите пункт списка Денежный (Currency), чтобы представить цены в рублях. (Так как в таблице Наборы цены заданы в тысячах рублей, можно ввести в поле свойства Формат значение #"тыс.руб." и задать значение 2 для свойства Число десятичных знаков. Тогда новые цены отобразятся в том же виде, что и цены наборов).

6. Щелкните в поле Описание (Description) и затем введите Цены на наборы, увеличенные на 5 процентов.

7. Закройте окно Свойства поля (Field Properties).

8. Переключитесь в Режим таблицы (Datasheet View), чтобы посмотреть на результаты запроса после изменения свойств поля.

Новые цены представлены в денежном формате, а в заголовке столбца указано «Новая цена».

9. Щелкните в столбце Новая цена. Описание поля появится в строке статуса внизу окна Microsoft Access.

10. Закройте запрос и щелкните на кнопке Да (Yes), чтобы сохранить изменения.

Использование перекрестных запросов для анализа данных

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

Создайте перекрестный запрос

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

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

1. Убедитесь, что в окне базы данных открыта вкладка Запросы (Queries), и щелкните на кнопке Создать (New), чтобы создать новый запрос.

2. Выделите пункт Перекрестный запрос (Crosstab Query Wizard) и щелкните на кнопке ОК.

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

3. Выделите вариант Запросы (Queries). Данный запрос будет строиться на основании существующего запроса.

4. Выделите запрос Продажи наборов и щелкните на кнопке Далее.

5. Выделите поле Название набора и щелкните на кнопке выбора, чтобы переместить поле из списка Доступные поля (Available Fields) в список Выбранные поля (Selected Fields). Щелкните на кнопке Далее (Next).

Поле Название набора будет заголовком строк перекрестного запроса. Окно Создание перекрестных таблиц (Crosstab Query Wizard) должно выглядеть, как показано на следующем рисунке.

6. Выделите поле Дата заказа из списка полей и щелкните на кнопке Далее (Next).

Дата заказа является теперь заголовком столбца, как это можно видеть на образце перекрестного запроса.

7. В списке возможных интервалов выделите значение Дата и щелкните на кнопке Далее (Next).

Поле Дата заказа будет сгруппировано по дате.

8. Выделите Количество в качестве поля, содержащего исходные данные для вычислений, и задайте тип вычислений, выделив функцию Sum. Щелкните на кнопке Далее (Next).

9. Введите имя запроса Перекрестный запрос по продажам наборов и щелкните на кнопке Готово (Finish).

Просмотрите результаты перекрестного запроса

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

1. В качестве начальной даты введите 1-ноя-96, а в качестве конечной Ю-ноя-96.

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

2. Закройте запрос.

Представление данных в виде диаграммы

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

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

Создайте диаграмму на базе запроса

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

Важно. Чтобы выполнить это упражнение, вам понадобится компонента Microsoft Chart (или Microsoft Graph 97), которая не входит в типовой комплект установки. Запустите программу Setup и задайте установку только этого компоненты.

1. Убедитесь, что в окне базы данных открыта вкладка Отчеты (Reports), и щелкните на кнопке Создать (New), чтобы создать новый отчет.

2. Выделите Мастер диаграмм (Chart Wizard) и затем щелкните на стрелке поля Выберите в качестве источника данных таблицу или запрос (Tables And Queries), чтобы вывести список таблиц и запросов, которые будут использоваться для построения диаграммы.

3. В списке выделите запрос Продажи наборов и щелкните на кнопке ОК. Откроется окно мастера Создание диаграмм (Chart Wizard).

4. Выберите поля Дата заказа, Название набора и Количество, поочередно выделив каждое из них и щелкнув на стрелке выбора, чтобы переместить их из списка Доступные поля (Available Fields) в список Поля диаграммы (Fields For Chart). Щелкните на кнопке Далее (Next). Мастер диаграмм выдаст перечень различных типов диаграмм.

5. Щелкните на кнопке График (Line Chart) и затем щелкните на кнопке Далее (Next). Следующее окно мастера позволяет задать способ вычисления значений, представленных на диаграмме. Кроме того, с помощью кнопки Образец можно осуществить предварительный просмотр диаграммы. По умолчанию на диаграмме отображаются суммы выбранного числового поля, которым в данном случае является Количество. Предположим, что вы решили воспользоваться другой функцией, чтобы вычислить нужное значение.

6. Щелкните дважды на кнопке Сумма_Количество (SumofQuantity), чтобы открыть диалоговое окно Вычисление итоговых значений (Summarize) и затем выделите Число (Count). Щелкните на кнопке ОК, чтобы вернуться в окно мастера.

7. Щелкните дважды на кнопке Дата заказа по месяцам (Order Date by Month) и затем в диалоговом окне Группировка (Group) выделите вариант по неделям. Щелкните на кнопке ОК.

8. Щелкните на кнопке Образец (Preview Chart), чтобы получить представление об общем виде диаграммы. Появится диалоговое окно Введите значение параметра (Enter Parameter Value). Поскольку запрос содержит данные, разнесенные по датам, необходимо задать нужный период.

9. Введите 1-ноя-96 и щелкните на кнопке ОК. Затем введите ЗО-ноя-96 и щелкните на кнопке ОК. На экране отобразится образец диаграммы.

10. Щелкните на кнопке Закрыть (Close), чтобы вернуться в окно мастера.

11. Щелкните на кнопке Далее (Next). В следующем окне мастера присвойте название диаграмме.

12. Убедитесь, что выбраны варианты Показать условные обозначения, Да (Yes, Display A Legend) и Открытие отчета с диаграммой (Open The Report With The Chart Displayed On It), и щелкните на кнопке Готово (Finish).

13. Введите 1-окт-96 и щелкните на кнопке ОК. Затем введите 31-дек-96 и щелкните на кнопке ОК. На экране появится отчет, включающий диаграмму.

14. Щелкните на кнопке Закрыть (Close). В ответ на запрос, хотите ли вы сохранить изменения, щелкните на кнопке Да (Yes) и введите наименование отчета Диаграмма продаж наборов.

Примечание. Если вы хотите получить дополнительные сведения по теме урока, выполните упражнения раздела «Шаг вперед». В противном случае переходите к разделу «Окончание урока».

Шаг вперед: как задать параметры, ограничивающие перекрестный запрос

Довольно часто возникает необходимость получить данные в определенном диапазоне. Так, например, запросы по заказам могут быть практически необозримы из-за большого числа записей в таблице Заказы. Можно ограничить число элементов перекрестного запроса, указав параметры (или условия) отбора записей. В сущности, вы уже имели дело с параметрами, задавая период для перекрестного запроса по продажам наборов. Когда данные отбираются по датам, Microsoft Access автоматически устанавливает диапазон дат в качестве параметра запроса.

Задайте параметр

Предположим, что руководство компании «Сластена Лили» хотело бы получить перекрестный запрос по продажам товаров только для определенных наборов конфет. Чтобы ограничить запрос, необходимо задать соответствующий параметр для столбца Названия наборов. Тогда пользователь получит возможность указать, какие наборы его интересуют.

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

2. В строке Условие отбора (Criteria) в столбце Название набора введите [Введите название набора], чтобы задать параметр.

3. В меню Запрос (Query) щелкните на команде Параметры (Parameters).

4. В верхней строке столбца Параметр (Parameters) введите: Введите название набора.

5. В столбце Тип данных (Data Type) щелкните в первой ячейке, чтобы определить тип данных, которые допускается вводить в диалоговое окно параметров.

По умолчанию задается Текстовый тип данных.

6. Щелкните на кнопке ОК

7. Переключитесь в Режим таблицы (Datasheet View). При появлении запроса на ввод параметров введите Вечернее настроение в столбец Название набора и затем щелкните на кнопке ОК.

8. При появления запроса на ввод параметров введите даты 1-ноя-96 и 5-ноя-96. На экране отобразятся сведения о ходе продаж набора «Вечернее настроение» за указанный период.

9. В меню Файл (File) щелкните на команде Сохранить как/экспорт (Save as/Export). В поле Новое имя (File Name) введите Продажи отдельных наборов и затем щелкните на кнопке ОК.

Окончание урока

1. Чтобы перейти к следующему уроку, щелкните на команде Закрыть (Close) в меню Файл (File).

2. Если вы завершили работу с программой Microsoft Access, щелкните на команде Выйти (Exit) в меню Файл (File).

Краткое содержание урока