Обработка статистических данных средствами электронных таблиц Excel

                       2.3. Экспертные статистические системы.

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

Первое поколение  статистических экспертных систем, разработанное  в 80-х годах обеспечивало помощь пользователю по следующему кругу вопросов:

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

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

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

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

специализированных  пакетов для статистического  анализа). Особенность таких пакетов  в том, что они используют терминологию и критерии, принятые в какой-либо конкретной области знания. Такие  пакеты помогают пользователю уточнить цель исследования, выбрать наиболее подходящий метод обработки, подобрать математическую модель и т.п. Это направление сравнительно быстро развивается в экономике и в медицине, где средства, затраченные на разработку таких пакетов, могут принести прибыль. В других областях знаний таких пакетов разрабатывается значительно меньше.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

  Среди типичных экономико-математических приложений Excel могут быть названы: 
- структуризация и первичная логическая обработка данных;

- статистическая  обработка данных, анализ и прогнозирование;

- проведение  финансово-экономических расчетов;

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

        3.1 Средства структуризации и первичной обработки данных. 
Ранее уже отмечалось, что к принципиальным преимуществам электронных таблиц как формы организации данных относится гибкое сочетание возможностей, которые дает привязка информации к ячейкам таблицы, со свободой принятия решений о том, какую именно ячейку выбрать для размещения информации. Однако данная "свобода" имеет и обратную сторону: зачастую за нее приходится платить на дальнейших этапах работы, когда возникают проблемы с выполнением тех или иных операций по автоматизированной обработке данных. Не секрет, что логика таких операций предполагает наличие жестко формализованной структуры у исходной информации. Одним из способов решения этой проблемы в Excel являются списки.

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

1) выполнить команду меню Данные > Сортировка;

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

Рис. 3.1.1 Задание параметров сортировки данных

Если из общего списка необходимо выбрать лишь часть  информации, соответствующую некоторым  условиям, то следует провести фильтрацию данных. Для простых условий фильтрации часто бывает достаточно средств  Автофильтра. 
      Ограничимся кратким описанием последовательности действий: 
1. Выделить всю область списка (включая заголовок). 
2. Выполнить команду меню Данные > Фильтр > Автофильтр, после чего в строке заголовков появятся кнопки с раскрывающимися списками значений. 
3. С помощью появившихся элементов управления кнопок могут быть заданы стандартные критерии отбора (Все, Германия..., все строки с конкретным значением), см. рис. 3.1.2

Рис. 3.1.2 Задание фильтра

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

         3.2. Статистическая обработка данных и прогнозирование 
  Функции, реализующие статистические методы обработки и анализа данных, в Excel реализованы в виде специального программного расширения - надстройки Пакет анализа, которая входит в поставку данного программного продукта и может устанавливаться (или не устанавливаться) по желанию пользователя.

   Установка надстройки Пакет анализа производится точно так же, как и установки прочих надстроек, то есть через меню Сервис > Надстройки, после чего в диалоговом окне Надстройки необходимо пометить пункт Пакет анализа и нажать кнопку ОК (рис. 3.2.1).

                                Рис. 3.2.1 . Установка пакета анализа  
   Если процесс установки Завершается успешно, то в меню Сервис появляется еще один пункт - Анализ данных (рис. 3.2.2), а также при создании формул становится доступной новая группа функций - статистические.

Рис. 3.2.2. Окно Анализ данных, вызываемое из меню Данные > Анализ данных 
   Проблема изучения взаимосвязей различного рода показателей является одной из важнейших в экономическом анализе. В конечном счете, основное содержание любой экономической политики может быть сведено к регулированию экономических переменных, осуществляемому на базе выявленной тем или иным образом информации об их взаимовлиянии. 
   Целью статистического исследования является обнаружение и исследование соотношений между статистическими (экономическими) данными и их использование для изучения, прогнозирования и принятия решений. 
   Любые экономические данные представляют собой количественные характеристики каких-либо экономических объектов. Они формируются под действием множества факторов, не все из которых доступны внешнему контролю. Неконтролируемые факторы могут принимать случайные значения из некоторого множества значений и тем самым обусловливать случайность данных, которые они определяют. Стохастическая природа экономических данных обусловливает необходимость применения специальных статистических методов для их анализа и обработки. 
    Поэтому фундаментальными понятием статистического анализа являются понятия вероятности и случайной величины.

   Конечно, Excel не предназначен для комплексного статистического анализа и обработки данных (в отличие от специального статистического программного обеспечения, такого как STATISTICA, Eviews, TSP, SPSS, Microfit и др.).       Однако и на базе электронных таблиц можно провести некоторую статистическую обработку данных. В частности, в рамках Excel с помощью команд, доступных из окна Анализ данных (рис. 3.2.2.), можно провести: 
- описательный статистический анализ (Описательная статистика);

- ранжирование  данных (Ранг и персентиль);

- графический  анализ данных (Гистограмма);

- прогнозирование  данных (Скользящее среднее, Экспоненциальное 

глаживание); 
- регрессионный анализ (Регрессия) и др.

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

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

Таблица  2. Статистические функции для регрессионного анализа

Функция	Назначение функции и ее аргументы	Тип
ЛИНЕЙН	Определяет параметры линейного  тренда для заданного массива  ЛИНЕЙН(знач.У; знач. X; констанста; стат.)	Встроенная
ТЕНДЕНЦИЯ	Определяет предсказанные значения в соответствии с линейным трендом  для заданного массива (метод  наименьших квадратов)  ТЕНДЕНЦИЯ(знач.У; знач. X; новые знач.Х; константа;)	Встроенная
ПРЕДСКАЗ	Определяет предсказанное значение функции в заданной точке на основе линейной регрессии  ПРЕДСКАЗ(Х; знач.У; знач. X)	Встроенная
НАКЛОН	Определяет коэффициент для независимой  переменной в уравнении парной регрессии  НАКЛОН(знач.У; знач. X)	Встроенная
ОТРЕЗОК	Определяет отрезок, отсекаемый на оси  ординат линией линейной регрессии  ОТРЕЗОК(знач.У; знач. X)	Встроенная
КВПИРСОН	Определяет квадрат коэффициента корреляции Пирсона  КВПИРСОН(знач.У; знач. X)	Встроенная
ПИРСОН	Определяет коэффициент корреляции Пирсона (степень линейной зависимости  между двумя множествами данных)  ПИРСОН(массив 1 ; массив 2)	Встроенная
СТОШУХ	Определяет стандартную ошибку предсказанных  значений У для каждого X  СТОШУХ(знач.У; знач. X)	Встроенная
РОСТ	Аппроксимирует данные экспоненциальной кривой   РОСТ(знач.У; знач. X; новые знач.Х; константа;)	Встроенная

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

ПРАКТИЧЕСКАЯ  ЧАСТЬ.

Задача 1. Произвести экономический анализ для заданных статистических данных и сделать вывод.

 

Таблица 1. – Заданные статистические данные

X	1,01	1,51	2,02	2,51	3,01	3,49	3,98	4,48	4,99	5,49
Y	5,02	5,92	7,14	8,32	9,02	9,58	11,06	11,96	12,78	13,98

 

Решение

1. Вводим значения X и Y, оформляя таблицу;

2. По данным таблицы строим точечную диаграмму (см. рис. 1);

3. Выполнив пункты вставка – Добавить точечную диаграмму, получаем диаграмму (см. рис. 1);

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

y = 1.9733x + 3.0667 – уравнение зависимости;

R² = 0.9962 – величина достоверности аппроксимации;

4. Для обоснования сделанного выбора оформим таблицу 2. – сравнительный анализ принятых и заданных значений параметра Y.

В этой таблице:

Y₁ – значение параметра Y, согласно принятой гипотезе;

Y – значение параметра Y, согласно заданным данным.

ε – величина арифметического отклонения ε = Y - Y₁;

 

Рис. 1 – график зависимости у=f(x)

 

Таблица 2 – Сравнительный  анализ заданных и принятых значений Y

X	1.01	1.51	2.02	2.51	3.01	3.49	3.98	4.48	4.99	5.49
Y	5.02	5.92	7.14	8.32	9.02	9.58	11.06	11.96	12.78	13.98
Y1	5.06	6.05	7.05	8.02	9.01	9.95	10.92	11.91	12.91	13.90
E	-0.04	-0.13	0.09	0.30	0.01	-0.37	0.14	0.05	-0.13	0.08