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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 1.

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

Месяц	Марка автомобиля	Количество проданных
Январь	ВАЗ 2101
Январь	ВАЗ 2102
Январь	ВАЗ 2103
Январь	ВАЗ 2104
Январь	ВАЗ 2105
Февраль	ВАЗ 2101
Февраль	ВАЗ 2102
Февраль	ВАЗ 2103
Февраль	ВАЗ 2104
Февраль	ВАЗ 2105
Март	ВАЗ 2101
Март	ВАЗ 2102	-
Март	ВАЗ 2103	-
Март	ВАЗ 2104
Март	ВАЗ 2105
Апрель	ВАЗ 2101
Апрель	ВАЗ 2102
Апрель	ВАЗ 2103	-
Апрель	ВАЗ 2104	-
Апрель	ВАЗ 2105
…	…	…

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

Таблица 2.

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

Марка   Месяц	ВАЗ 2101	ВАЗ 2102	ВАЗ 2103	ВАЗ 2104	ВАЗ 2105
Январь
Февраль
Март		-	-
Апрель			-	-
…	…	…	…	…	…

Как видно из примера, перекрестная таблица представляет собой более мощное и удобное средство, позволяющее максимально упростить задачу анализа данных из таблиц. Перекрестную таблицу можно расширить, добавив итоговую информацию по строкам и столбцам. В этом случае данные приобретут большую наглядность, занимая при этом гораздо меньше места на носителе, чем исходная таблица (Если исходная таблица содержит данные о продаже 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.

Дата добавления: 2015-05-09