Электронные таблицы, табличные процессоры

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

Табличный процессор – это пакет программ, предназначенный для следующих целей:

- проведения математических, экономических, финансовых, статистических и других расчетов,

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

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

- получения сводных таблиц,

- создания БД,

- построения диаграмм и графиков по данным таблиц,

- печати созданных таблиц и отчетов,

- решения оптимизационных задач и т.д.

Табличные процессоры различаются набором выполняемых функций и удобством интерфейса. Наиболее популярными электронными таблицами для персональных компьютеров являются табличные процессоры: Excel (фирма Microsoft), Lotus 1-2-3 (фирма Lotus Developnet), Quattro Pro (фирма WordPerfekt – Novell Application Group).

4.3. Работа в табличном процессоре Microsoft Excel

Microsoft Excel обеспечивает анализ данных и подготовку решений на основе экономико-математических моделей. В среде Microsoft Excel можно создавать комплексные информационные технологии для поддержки и принятия решений. В отдельном приложении интегрируются функции обработки различных программ в виде дополнительных пользовательских команд или специальных надстроек, между компонентами поддерживаются стандартные интерфейсы. Информационная технология OLE 2.0 позволяет включать в приложение Microsoft Excel объекты других приложений Microsoft Office.

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

К стандартным технологиям анализа данных, реализуемым в Microsoft Excel, относятся:

• представление исходных данных в виде списков (базы данных) Microsoft Excel для целей анализа,

• фильтрация списков (базы данных) Microsoft Excel по различным условиям

• использование встроенных функций Microsoft Excel для формирования экономико-математических моделей,

• подбор параметров модели по заданному значению функционала,

• многовариантные расчеты и анализ чувствительности модели с помощью сценариев,

• подстановка табличных значений параметров в функционал модели,

• методы математического программирования для решения оптимизационных задач,

• статистическая обработка информации с помощью Пакета анализа,

• графические методы решения экономических задач и представления результатов анализа,

• агрегирование и своды исходных данных в виде списков (базы данных) Microsoft Excel и др

Экономико-математические модели, реализуемые в среде Microsoft Excel, могут основываться на встроенных функциях Microsoft Excel, а также функциях пользователей на языке Visual Basic Наиболее популярны для использования в экономико-математических моделях встроенные функции категории Финансовые.

Microsoft Excel обеспечивает решение задач линейного и нелинейного программирования ограниченной размерности. Для реализации оптимизационных расчетов в Microsoft Excel необходимо установить надстройку Поиск решения с помощью команды меню Сервис -> Надстройки и правильно подготовить данные оптимизационной модели на листе. Модель задачи задается в диалоговом окне Поиск решения. Модель использует целевую функцию, которая записывается в виде формулы в отдельной ячейке. Для целевой функции указывается: максимизация, минимизация или равенство фиксированному значению. В процессе поиска решения изменяются значения в указанных ячейках, соответствующих переменным, при соблюдении ограничений.

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

Для решений экономических, финансовых и деловых задач в Excel имеется множество шаблонов.

Встроенные функции категории «Статистические»

Для статистического анализа данных можно использовать встроенные функции Microsoft Excel категории «Статистические». Например, для вычисления числовых характеристик вариационного ряда:

для средней выборочной           =СРЗНАЧ(),

для моды                                     =МОДА(),

для медианы                               =МЕДИАНА(),

для среднего линейного отклонения =СРОТКЛ(),

для выборочной дисперсии        =ДИСП(),

для среднего квадратического отклонения =СТАНДОТКЛОН(),

для коэффициента асимметрии =СКОС(),

для эксцесса                                 =ЭКСЦЕСС().

Построение тренда, прогнозирование

Для определения тренда в ряде динамики используется Мастер диаграмм: в готовой гистограмме Диаграмма – Добавить линию тренда.

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

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

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

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

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

В состав Microsoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. В Microsoft Excel представлено большое число статистических, финансовых и инженерных функций. Некоторые из них являются встроенными, другие доступны только после установки пакета анализа (Сервис – Пакет анализа – Анализ данных).

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

2) Корреляционный анализ. Функции КОРРЕЛ и ПИРСОН вычисляют коэффициент корреляции между двумя переменными измерений, когда для каждой переменной измерение наблюдается для каждого субъекта N (пропуск наблюдения для субъекта приводит к игнорированию субъекта в анализе). Корреляционный анализ иногда применяется, если имеется более двух переменных измерений для каждого субъекта N. В результате выдается таблица, корреляционная матрица, показывающая значение функции КОРРЕЛ (или ПИРСОН) для каждой возможной пары переменных измерений. Коэффициент корреляции, как ковариационный анализ, характеризует область, в которой два измерения «изменяются вместе». Корреляционный анализ дает возможность установить, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция).

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

4) Регрессия. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Регрессия использует функцию ЛИНЕЙН.

4.4. Статистическая обработка данных в системе Statistica

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

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

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

Statistica включает графический модуль, содержащий удобные инструменты для эффективной и наглядной визуализации данных, проведения графического анализа. Более 10 000 различных типов графиков, имеющих богатые возможности редактирования, интерактивной настройки (вращение, масштабирование, прозрачность и др).

StatSoft является партнером Microsoft, поэтому Statistica полностью соответствует стандартам Microsoft. Это позволяет  интегрировать новые модули в существующие системы; строить на основе Statistica интеллектуальную систему принятия решений, используя процедуры Statistica как готовые элементы.

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

Система Statistica организована по модульному принципу – методы статистической обработки разбиты на несколько групп – модулей. Среди них содержатся следующие: «Основные статистики и таблицы», «Непараметрические методы», «Однофакторный дисперсионный анализ», «Анализ временных рядов и прогнозирование», «Кластерный анализ» и другие.

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

Важным этапом исследования является визуализация данных, что позволяет сформулировать гипотезы о природе данных, построить уникальные графики. Получение всестороннего визуального представления данных на всех этапах исследования позволяет выбирать следующий шаг анализа, выдвигать гипотезы, которые невозможно было бы выдвинуть, имея только численные представления. В Statisticе имеются следующие графические средства: диаграмма рассеяния и гистограмма. Гистограмма является популярным статистическим графиком, позволяющим удобно представить частоту попадания величин в определенные интервалы. Дополнительно можно рассчитать таблицу частот, задав число равных интервалов. В таблице будут подсчитаны абсолютные частоты, накопленные значения, проценты. Кнопка «Гистограмма» выводит на экран гистограммы для каждой переменной вместе с наложенными на них кривыми нормального распределения. Для проверки нормальности распределения используются несколько критериев согласия: критерий Колмогорова-Смирнова, Лиллиерфорса, Шапиро-Уилка. Значения критериев и соответствующие доверительные вероятности приводятся одновременно с гистограммами. Кроме того, нормальность распределения приближенно можно оценить графически по нормальным вероятностным графикам.

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

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

Контрольные вопросы:

1. Какие методы можно применять для анализа и обработки данных?

2. Какие задачи позволяет решить правовая статистика при принятии управленческих решений? Какими признаками она обладает?

3. Какие разделы можно выделить в правовой статистике?

4. Назовите методы правовой статистики.

5. Какие задачи по анализу и обработке данных можно решить с применением табличных процессоров? Какими технологиями анализа данных обладает приложение Microsoft Excel?

6. Назовите статистические встроенные функции в Microsoft Excel.

7. Что такое прогнозирование, тренд? Как они реализованы в Microsoft Excel?

8. Какие методы анализа содержатся в пакете «Анализ данных» Microsoft Excel?

9. Какие возможности по обработке данных заложены в системе Statistica?

10.  Для чего используется кластерный анализ при обработке данных?

Литература по теме: [20-23,25-27,29-38].