Организация экономических данных в Excel и методы подготовки их к анализу (стр. 2 из 2)

Добавим в список рис.5 новое поле "Группа" (рис.7). В данном случае главным ключом будет группа полей: "Группа" + "Студент". Если в качестве объекта взять значения того же поля "Студент", что и в примере рис.5, то получим с точки зрения организации списка тот же тип: "один объект" - "одна запись". Но поскольку главным ключом является группа полей, то в качестве объекта можно взять значения и другого поля: "Группа". В этом случае объекту по полю "Группа" будет соответствовать несколько записей - столько, сколько студентов учится в данной группе. Т.е. присутствует тип организации "один объект" - "несколько записей".

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

1.3 Методики подготовки данных к анализу средствами Excel

1.3.1 Обработка списков типа "один объект" - "одна запись"

В данном случае подготовка к анализу в основном сводится к сжатию с помощью фильтрации, сортировке и дополнению исходных данных расчетными. Исключением является случай использования механизма "Сводная таблица", поскольку он сам осуществляет фильтрацию данных (область СТРАНИЦА) и их автоматическую сортировку при составлении отчета. Однако использование его не всегда возможно.

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

Последовательность действий:

- в строке заголовков полей вводится новый заголовок "средний балл" (ячейка Е1);

- в ячейку Е2 вводится формула:

=СРЗНАЧ (В2: D2)

- формула копируется с ячейки Е3 и до конца списка.

Этот вариант отличает очевидная простота. Однако он требует достаточно большого объема ручной работы.

1.3.2 Обработка списков типа "один объект" - "несколько записей"

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

Решим пример 1 с учетом организации данных в виде списка рис.6.

Вариант а). Ввод расчетной формулы "средний балл" невыполним.

Вариант б). Использование механизма "Промежуточные итоги".

Необходимо:

- отсортировать данные по двум ключам "Студент" и "Предмет". Если этого не сделать, получим неверный ответ: по ст. Иванову И.И. и Петрову П.П. будет выдано 2 средних оценки - после 1-ой и 3-ей, 2-ой и 6-ой записями соответственно;

- обратиться к механизму "Промежуточные итоги"

- заполнить диалоговое окно:

"При каждом изменении в ": Студент

"Операция": среднее

"Добавить итоги по ": Оценка

Результат представлен на рис.8.

Рис.7. Результат решения примера 1 с помощью механизма Вариант в).

Использование механизма "Сводная таблица".

"Промежуточные итоги"

Необходимо: вызвать Мастер сводной таблица (выделить любую ячейку списка, Данные-Сводная таблица); последовательно заполнить диалоговые окна; структура таблицы: область Строка – Студент, область Данные – Оценка, операция - среднее

Результат представлен на рис.9.

Рис. 8. Результат решения примера 1 с помощью механизма "Свободная таблица"

Пример 2. Данные организованы в виде списка, представленного на рис.10. Необходимо составить отчет о результатах сдачи сессии студентами по семестрам.

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

Рис.9.Список для примера 2

Вариант а). Использование механизма "Промежуточные итоги".

Действия:

отсортировать список по полям: "Семестр", "Группа", "Предмет".

- обратиться к механизму "Промежуточные итоги". Заполнить диалоговое окно:

"При каждом изменение в " - Семестр

"Операция" - среднее

"Добавить итоги по " - Оценка

- еще раз обратиться к механизму "Промежуточные итоги". Заполнить диалоговое окно:

"При каждом изменении в " - Группа

"Операция" - среднее

"Добавить итоги по" - Оценка

"Заменить текущие итоги" - снять флажок.

- скрыть детальные данные.

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

"Заменить текущие итоги" - с необходимостью сохранения результатов предыдущего подведения итогов. Результат представлен на рис.11.

Вариант б). Использование механизма "Сводная таблица".

Действия:

- обратиться к механизму "Сводная таблица"

- последовательно заполнить диалоговые окна. Структура таблицы:

область СТРОКА: Семестр, Группа

область ДАННЫЕ: Оценка

Функция: среднее.

Результат представлен на рис.12.

Рис.10. Результат решения примера 2 с помощью механизма "Промежуточные итоги"

Рис.11.Результат решения примера 2 с помощью механизма "Сводная таблица"

Список использованных источников

1. Рон Персон. Excel 7.0 для WINDOWS 95: пер. с англ. - СПб.: BHV-Санкт-Петербург, 1996.

2. Уокербах Джон. Библия пользователя Exsel 97. Пер. с англ.К. - Диалектика, 1997.

3. Винтер Р., Винтер П. Microsoft Office 97 в подлиннике. Т.2. - СПб.: "BHV - Санкт-Петербург", 1997.

4. Водополова Н.В. Практическое пособие: Excel - как средство анализа данных / Водополова Н.В., Косинов Г.П., Шибеко В.Н. - Гомель, 2002.