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

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

• 1. Организация экономических данных в Excel и методы подготовки их к анализу
• 1.1 Общие информационные методы обработки данных в Excel
• 1.2 Источники и организация данных в Excel
• 1.2.1 Внутренние источники данных
• 1.2.2 Организация данных
• 1.3 Методики подготовки данных к анализу средствами Excel
• 1.3.1 Обработка списков типа «один объект» — «одна запись»
• 1.3.2 Обработка списков типа «один объект» — «несколько записей»
• Список использованных источников

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

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

организационной,

плановой,

учетной,

анализа,

контрольной,

стимулирования.

В последнее время в сфере управления все активнее применяется понятие «принятие решения» .

Принятие решения — акт целенаправленного воздействия на объект управления, основанный на анализе ситуации, определении цели, разработке программы достижения этой цели.

Таким образом, в жизненном цикле принятия любого решения можно выделить следующие основные моменты:

планирование;

организация;

контроль (учет, оценка, анализ);

регулирование.

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

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

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

1.1 Общие информационные методы обработки данных в Excel

Информационные методы обработки данных напрямую связаны с основными компонентами информационной технологии обработки данных. Это:

сбор и хранение данных;

обработка данных;

получение отчетов.

В соответствии с ними все информационные методы обработки данных могут быть условно классифицированы как методы организации и хранения данных;

подготовки данных к анализу;

анализа данных;

создания качественных отчетов.

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

Методы и способы подготовки данных к анализу зависят от способа их организации и осуществляются путем а) сжатия данных (вертикальное удаление) с помощью сортировки фильтрации механизма «Промежуточные итоги»

механизма «Сводная таблица»

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

Анализ данных предполагает использование логических, математических и статистических функций вычисляемого критерия в расширенном фильтре фильтрации данных сортировки группировки механизма «Сводная таблица»

механизма «Промежуточные итоги»

таблиц подстановки создания различных сценариев построения графиков и диаграмм.

Схема обработки данных в Excel представлена на рис. 1.

Итак, чтобы в совершенстве овладеть одним из самых мощных средств анализа данных, необходимо:

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

владеть всеми механизмами, предоставляемыми им;

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

в случае хранения данных вне рабочих книг Excel, уметь принять и организовать их для последующей работы;

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

В данной работе рассмотрены вопросы представления данных (п.2), подготовки их к анализу (п.3).

Рис. 1. Основные компоненты информационной технологии обработки данных в Excel

1.2 Источники и организация данных в Excel

1.2.1 Внутренние источники данных

Относительно Excel все источники данных могут быть определены как внутренние и внешние.

Внутренними являются данные, размещенные в одной рабочей книге Excel. Они могут быть организованы в виде:

базы данных;

списка;

таблицы;

диапазона ячеек.

С точки зрения организации данных дадим следующие определения.

База данных - это специальным образом организованные исходные данные.

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

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

1) Верхняя строка должна содержать заголовки полей. Заголовок поля может располагаться только в одной ячейке.

Соблюдение этого правила необходимо для правильной работы таких механизмов Excel, как «Сортировка», «Форма данных», «Автофильтр», «Расширенный фильтр», «Промежуточные итоги», «Сводная таблица», «Мастер диаграмм» .

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

наличие внутри базы данных или списка пустых строк или столбцов;

примыкание к базе данных или списку других данных.

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

На рис. 2 и 3 представлены фрагменты базы данных и списка соответственно. В данном примере список отличается от базы тех же данных наличием расчетных показателей «Прирост» и «Доля» .

Рис. 2. Пример базы данных «Состав и динамика основного капитала Таблица — это исходные и расчетные данные специальным образом оформленные.

Специальное оформление данных может быть связано с введением совмещенных заголовков граф, строки с номерами граф и графы «Номер по порядку», подножия таблицы, специального форматирования данных, (выравнивание, представление, выделение и т. д.).

Пример таблицы с теми же данными, что и в списке (рис.3) представлен на рис. 4.

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

Как правило, данные диапазона ячеек в анализе не используются.

Итак, организация данных всех четырех типов внутренних источников данных имеет следующие общие особенности: в строке могут располагаться разнородные данные, а в столбце (поле, графе) — только однородные. Причем, только в диапазоне ячеек имя поля или заголовок отсутствует.

Во-вторых, для хранения данных используют организацию данных в виде базы данных, т.к. хранение расчетных данных — это расточительно.

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

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

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

1.2.2 Организация данных

Возможны два варианта организации данных в Excel:

" один объект" - «одна запись»

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

Определим следующие понятия.

Информационный объект - это совокупность сведений об объекте материального или нематериального мира. Каждый объект характеризуется свойствами (реквизитами), которые позволяют выделить его из множества других. Например, объект «Автомобиль» может иметь реквизиты: цвет, вес, марка номер, фамилия владельца.

Запись - строка списка.

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

Для списка, представленного на рис. 5, главным ключом является поле «Студент», поскольку значения его однозначно идентифицируют отдельную запись списка. В этом случае один объект описывается одной записью.

Рис. 5. Пример списка типа «один объект» — «одна запись»

Рис. 6. Пример списки типа «один объект» — «несколько записей»

В списке рис. 6. главным ключом является группа полей: «Студент» + «Предмет». Объект — это студент и информация и нем. Каждому объекту (студенту) в этом случае соответствует несколько записей: столько, сколько предметов изучал студент. В этом случае имеет место тип организации списка «один объект» — «несколько записей». Следует отметить, что этот список может быть обработан по тому же ключу, но в качестве объекта выбраны значения поля «Предмет». В этом случае каждому объекту будет соответствовать столько записей, сколько студентов изучало конкретный предмет.

Добавим в список рис. 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.