Лабораторная работа № 4

Тема: Создание перекрестных таблиц и диаграмм

Цель: Изучить создание сложных выборок данных. Научиться проводить анализ взаимосвязанных данных. Изучить возможности по графическому представлению анализируемых данных.

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

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

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

Создание перекрестных таблиц

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

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

Для создания простейших одномерных перекрестных таблиц можно использовать обычный итоговый запрос. Для построения двумерных и более сложных перекрестных таблиц в Visual FoxPro используется мастер создания перекрестных таблиц (Cross-Tab Wizard). С помощью этих средств можно не только просматривать и печатать построенную таблицу, но и сохранять ее в виде электронной таблицы.

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

Двумерные перекрестные таблицы

Двумерная перекрестная таблица группирует информацию по двум факторам. Например, имеется таблица с данными о продажи автомобилей по месяцам:

Таблица 1.

Фрагмент таблицы с данными о продажи автомобилей

Месяц	Марка автомобиля	Количество проданных
Январь	ВАЗ 2101	17
Январь	ВАЗ 2102	29
Январь	ВАЗ 2103	3
Январь	ВАЗ 2104	12
Январь	ВАЗ 2105	15
Февраль	ВАЗ 2101	7
Февраль	ВАЗ 2102	13
Февраль	ВАЗ 2103	16
Февраль	ВАЗ 2104	5
Февраль	ВАЗ 2105	10
Март	ВАЗ 2101	10
Март	ВАЗ 2102	-
Март	ВАЗ 2103	-
Март	ВАЗ 2104	15
Март	ВАЗ 2105	3
Апрель	ВАЗ 2101	12
Апрель	ВАЗ 2102	10
Апрель	ВАЗ 2103	-
Апрель	ВАЗ 2104	-
Апрель	ВАЗ 2105	2
…	…	…

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

Таблица 2.

Фрагмент перекрестной таблицы по данным о продаже автомобилей

Марка Месяц	ВАЗ 2101	ВАЗ 2102	ВАЗ 2103	ВАЗ 2104	ВАЗ 2105
Январь	17	29	3	12	15
Февраль	7	13	16	5	10
Март	10	-	-	15	3
Апрель	12	10	-	-	2
…	…	…	…	…	…

Как видно из примера, перекрестная таблица представляет собой более мощное и удобное средство, позволяющее максимально упростить задачу анализа данных из таблиц. Перекрестную таблицу можно расширить, добавив итоговую информацию по строкам и столбцам. В этом случае данные приобретут большую наглядность, занимая при этом гораздо меньше места на носителе, чем исходная таблица (Если исходная таблица содержит данные о продаже 10 марок автомобилей за три года, то она будет содержать 3*12*10=360 строк, что вряд ли поместится даже на четырех страницах. Перекрестная таблица при этом будет состоять из 10 столбцов и 36 строк, что уже вполне приемлемо для восприятия).

Возможно создание перекрестных таблиц, группирующих данные по трем и более факторам. Но в этом случае возникают проблемы с визуализацией конечного результата. Не совсем понятно, как можно изобразить n-мерный гиперкуб на плоскости, хотя построить его достаточно просто. Существуют средства, ориентированные на поддержку OLAP-технологии, которые позволяют строить произвольные перекрестные таблицы по произвольному числу факторов. Visual FoxPro может работать только с одно и двумерными перекрестными таблицами, однако этого достаточно для решения множества возможных задач по анализу данных из таблиц.

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

Рассмотрим создание перекрестной таблицы в интерактивном режиме с помощью мастера Cross-Tab Wizard. Мастеру необходимо указать исходную таблицу, которая может быть таблицей из базы данных или являться результатом выборки данных из нескольких таблиц. Предварительно мастер на основе исходной таблицы создает итоговую выборку, в которой группирует и сортирует данные по полям, используемым в качестве строки и столбца перекрестной таблицы, а также вычисляет суммарное значение по заданному полю данных. Затем вызывается программа, наименование которой хранится в системной переменной _GENXTAB. В Visual FoxPro такой программой является VFPXTAB.PRG. (В качестве построителя перекрестных таблиц можно указывать программу пользователя. Для этого необходимо соответствующим образом изменить значение переменной _GENXTAB.).

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

Для демонстрации возможностей мастера построения перекрестных таблиц необходимо создать вначале источник данных, в качестве которого используем представление данных, содержащее выборку из таблиц OrdSaled, OrdSalem и Goods. Для этого необходимо:

1. Создать представление данных.

2. Добавить в него таблицы OrdSaled, OrdSalem и Goods.

3. В качестве результирующих полей выбрать номер заказа и название товара. Также необходимо создать вычисляемое поле Summa, в котором должна храниться сумма, на какую заказан каждый товар (рис. 1).

4. Сохранить представление данных в базе данных под именем test_view.

Если все создано нормально, то в базе данных появится представление данных, содержащее в себе информацию о проданных товарах по каждому заказу и о сумме, на которую был заказан каждый товар (рис. 2). Обратите внимание, что после просмотра представления данных оно остается открытым в сессии данных, хотя окно просмотра и закрыто. Также остаются открытыми и все используемые в представлении таблицы. Все внесенные изменения в эти таблицы не будут отражены в представлении, до тех пор, пока представление не будет закрыто и повторно открыто. Для закрытия представления можно воспользоваться окном «Data Session» (рис. 3), которое вызывается кнопкой на панели инструментов, или использовать команду CLOSE TABLES в командном окне.

Рис. 1. Окно конструктора представлений данных при создании представления Test_view

Рис. 2. Окно просмотра результатов представления данных Test_View

Рис. 3. Окно Data Session c открытым представлением Test_view

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

1. Выбрать команду Tools | Wizards | Query. Далее из окна диалога «Wizard Selection» запустить мастер создания перекрестных таблиц Cross-Tab Wizard.

2. Сначала требуется выбрать исходное представление данных Test_view и три поля, которые будут использоваться в качестве строки, столбца и поля данных перекрестной таблицы (рис. 4). Для перехода к следующему этапу необходимо нажать кнопку Next.

Рис.4. Окно выбора исходной таблицы и полей

3. В следующем окне диалога (рис. 5) необходимо указать:

IcdOrder – в качестве поля, значения которого будут отображаться в наименованиях столбцов

CnmGoods – в качестве поля, значения которого будут отображаться в наименованиях строк

Summa – в качестве поля, по которому будут группироваться значения

Рис. 5. Определение строк, столбцов и поля данных

Для этого нужно перенести поле icdOrder в область «Column», поле cnmGoods – в «Row» и поле Summa – в «Data». Перенос следует осуществлять перемещением мышью при нажатой левой кнопке. Для этого щелкните левой кнопкой мыши на названии поля и не отпускайте кнопку. Затем поместите указатель в необходимое поле и только после этого отпустите кнопку мыши. После этого окно диалога будет выглядеть как на рис. 6. Для перехода к следующему шагу необходимо нажать кнопку Next.

Рис. 6. Окно диалога после определения строк, столбцов и поля данных

4. Visual FoxPro при вычислении групповых значений по полю данных позволяет использовать следующие типы группировок, которые можно выбрать в окне диалога «Cross-Tab Wizard» (рис. 7).

Тип группировки	Назначение
Sum	Итоговые значения равны сумме значений для каждой пары строка/столбец
Count	Итоговые значения равны количеству значений для каждой пары строка/столбец
Average	Итоговые значения равны количеству записей для каждой пары строка/столбец
Min	Итоговые значения равны наименьшему из всех значений для каждой пары строка/столбец
Max	Итоговые значения равны наибольшему из всех значений для каждой пары строка/столбец

Установите опцию Sum.

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

Тип группировки	Назначение
Sum of data	Итоговые значения равны сумме значений для текущей строки
Number of cells containing data	Итоговые значения равны количеству ячеек, содержащих значения
Percentage of table total	Итоговые значения равны сумме значений для текущей строки, выраженной в процентах по отношению к итоговой сумме
None	Итоговый столбец не создается

На данном этапе установите опцию Percentage of table total. Для перехода к следующему этапу необходимо нажать клавишу Next.

5. На завершающей стадии (рис.8) можно предварительно просмотреть результаты работы, воспользовавшись кнопкой Preview. Если результаты работы удовлетворительны, необходимо выбрать один из вариантов завершения создания перекрестной таблицы.

Значение	Назначение
Save cross-tab query	Сохраняет созданный запрос
Save and run cross-tab query	Сохраняет созданный запрос и запускает его на выполнение
Save cross-tab query and modify in the Query Designer	Сохраняет созданный запрос и открывает его в окне конструктора запросов

Выключатель Display null values управляет отображением символа NULL. в полях данных перекрестной таблицы, для пар строка/столбец которой нет значений в таблице-источнике.

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

SELECT Test_view.cnmgoods, Test_view.icdorder, SUM(Test_view.summa);

FROM sales!test_view;

GROUP BY Test_view.cnmgoods, Test_view.icdorder;

ORDER BY Test_view.cnmgoods, Test_view.icdorder;

INTO CURSOR SYS(2015)

DO (_GENXTAB) WITH 'Query',.t.,.t.,.t.,,,,.t.,2,.f.

BROWSE NOMODIFY

Рис. 7. Определение типа группировки и итогового столбца

Рис. 8. Завершающая стадия создания перекрестной таблицы

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

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

• в случае «ручного открытия», обязательно наличие ключевого слова SHARED в команде открытия базы данных (OPEN DATABASE <имя базы> SHARED);

• в случае «автоматического открытия» в окне менеджера проекта следует убедиться, что флаг Open exclusive в настройках системы снят. Убедиться в состоянии флага можно выбрав листовую вкладку Data в пункте меню Tools | Options… Если флаг стоит, следует его снять, закрыть базу данных или проект и открыть их снова. Перед повторным открытием рекомендуется перепроверить состояние флага.

Диаграммы

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

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

В Visual FoxPro для построения диаграмм используется отдельная программа Microsoft Graph, в которую передаются данные с помощью механизма обмена данными между приложениями Windows. Для передачи данных можно использовать мастер по построению диаграмм, который выбирается при создании нового запроса, или вызвать приложение WZGRAPH.APP.

Использование мастера предоставляет возможность создания диаграммы в интерактивном режиме. При создании законченного приложения может потребоваться механизм создания диаграммы, не требующий от пользователя активных действий. В этом случае необходимо использовать функции Visual FoxPro, которые позволяют передавать данные и команды для построения диаграммы другим приложениям Windows (Microsoft Graph, Microsoft Excel и другим) с помощью механизма OLE.

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

Мастеру создания диаграмм необходимо указать исходную таблицу, которая может быть таблицей из базы данных или являться результатом выборки данных из нескольких таблиц. Вначале мастер на основе исходной таблицы и выбранных полей создает выборку, в которую выбирает из исходной таблицы указанные поля. Затем вызывается приложение, наименование которого хранится в системной переменной _GENGRAPH. В Visual FoxPro таким приложением по умолчанию является WZGRAPH.APP. Данное приложение передает в Microsoft Graph выбранные данные и создает диаграмму указанного типа.

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

1. Выполнить команду Tools | Wizards | Query. Далее из окна диалога «Wizard Selection» запустить мастер создания диаграмм Graph Wizard.

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

3. В окне диалога (рис. 10) необходимо указать поля, используемые в качестве рядов данных и категорий. Категории указываются в поле Axis, а ряды данных – в поле Data series. Указание производится перетаскиванием мышью соответствующих полей. После завершения указания данных необходимо нажать кнопку Next для перехода к следующему этапу.

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

5. На завершающей стадии можно задать заголовок диаграммы, установить флажок добавления в диаграмму легенды и предварительно просмотреть результат работы, используя кнопку Preview (рис. 11). Если результат работы удовлетворителен, то возможны два варианта завершения работы:

Значение	Назначение
Save graph to a table	Сохраняет созданную диаграмму в таблице, содержащей поле типа General
Save and create query with graph	Сохраняет созданный запрос и открывает его в окне конструктора запросов

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

Обратите внимание, что не во всех версиях Visual FoxPro корректно поддерживается сохранение диаграммы в таблице (при неправильной регистрации OLE-сервера), а также не всегда правильно распознается кодировка символов в легенде диаграммы для русского языка. Все необходимые настройки можно произвести непосредственно в программе Microsoft Graph.

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

Рис. 9. Выбор исходной таблицы и полей

Рис. 10. Окно определения категорий и рядов данных

Рис. 11. Окно завершающей стадии создания диаграммы

Задания

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

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

3. Создайте две перекрестных таблиц по заказам в разрезе количества заказанных товаров и сумм заказанных товаров. На основании них проведите анализ и отметьте результат о спросе на различные товары.

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

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

5. По всем построенным перекрестным таблицам постройте диаграммы произвольного типа (тип необходимо выбрать самим, руководствуясь стремлением к наибольшей наглядности и удобства для анализа).

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