Главная->Економіка->Содержание->3.    Статистический анализ и прогноз рядов наблюдений

3.    Статистический анализ и прогноз рядов наблюдений

 

Excel  позволяет Пользователю использовать более 70 функций Рабочего листа [3] (практически готовых решений) для оценки статистических характеристик ряда наблюдений. Используя их  различные сочетания можно решить практически любую задачу Стат. Анализа, используя в качестве помощника Мастер функций.

 

 

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

 

 

3.1.             Линейная и экспоненциальная аппроксимация данных

 

Для вычислений коэффициентов уравнения прямой линии, которая наилучшим образом аппроксимирует данные наблюдений используется функция ЛИНЕЙН(). Уравнение для прямой линии имеет вид

 

Y=m1*x1+m2*x2+.....b       или Y=m*x+b

 

Для вычисления коэффициентов уравнения экспоненциальной кривой, используемой для сглаживания данных наблюдения  используется функция  ЛГРФПРИБЛ(). Уравнение кривой имеет вид

  Y = ( b*(m1^x1)*(m2^x2)*.....)  или  Y =b*m^x

 где Y (зависимое значение) является функцией независимого  значения х.

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

 

Функции аппроксимации ЛИНЕЙН()  и ЛГРФПРИБЛ() позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую Ваши данные наблюдений. Однако, Вам самим необходимо решить, какой из полученных результатов Вас удовлетворяет в большей степени.

 

Проводя регрессионный анализ, Excel вычисляет для каждой точки

·       квадрат разности между рассчитанным и фактическим  значением Y. Сумма квадратов этих разностей называется остаточной суммой квадратов.

·       сумму квадратов разностей между фактическими значениями Y и средним значением Y, которая называется общей суммой квадратов ( регрессионная сумма квадратов + остаточная сумма квадратов)

FЧем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности ( корреляции) R², который показывает насколько  полученное регрессионное уравнение соответствует фактическим наблюдениям ( насколько тесно связаны  переменные)

функция ЛИНЕЙН()

 

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

 

Создание формулы массива[5]

·        Выделите диапазон ячеек, в которые следует ввести формулу.

·        Наберите формулу.

·        Нажмите клавиши CTRL+SHIFT+ENTER.

 

 

Уравнение для прямой линии имеет следующий вид:

y = mx + b или

y = m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x),

где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН() возвращает массив {mn;mn-1;...;m1;b}. Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику

 

 

Синтаксис  функций

 

ЛИНЕЙН(известные значения Y; известные значения х; константа; статистика)

ЛГРФПРИБЛ(известные значения Y; известные значения х; константа; статистика)

Агрументы

«Известные значения Y» - это множество уже известных           (заданных) значений Y

Если массив «известные значения Y» имеет один столбец (строку), то каждый столбец (строка) массива «Известные значения х»  интерпретируется как отдельная переменная

 

«Известные значения х» - это необязательное[6] множество уже известных (заданных) значений х

Массив данных «Известные значения х»  может содержать одно или несколько множеств переменных, В том случае, если используется только одна переменная ( зависимость вида Y=b+mx  или Y=b*m^x), то массивы «Известные значения Y» и «Известные значения X» могут иметь любую форму (но обязательно одну размерность). Если же в уравнение входят несколько переменных, то массив «Известные значения Y» должен быть представлен в виде вектора (занимать интервал ячеек в одной строке или столбце)

 

Константа- это логическое значение, которое указывает, требуется ли чтобы константа b была равна нулю

FЕсли «константа» имеет значение «1» (истина), то b вычисляется обычным образом. 

Если «константа» имеет значение «0» (ложь), то b приравнивается нулю и рассчитываются коэффициенты для  уравнения кривой, проходящей через начало координат.

Статистика - это логическое значение, которое указывает, требуется ли вывести ( вернуть) на рабочем листе Excel дополнительную статистику по уравнению регрессии ( линии тренда)

FЕсли «Статистика» имеет значение «1» (истина), то на рабочем листе ( в выделенном диапазоне ячеек) выводится дополнительная статистика по уравнению регрессии в виде массива (m_n; m_n-1;...m₁; b;se_n;se_n-1;....se₁;se_b;r²;se_y;F;df;ss_reg;ss_resid)

Если «Статистика» имеет значение «0» (ложь) или опущена, рассчитываются только коэффициенты m и b

 

Дополнительная статистика

m1; m2;…mn;b	Коэффициенты уравнения регрессии
se1;se2,.....sen	Стандартные значения ошибок для коэффициентов m1;m2;...mn
seb	Стандартное отклонение ошибки для постоянной b (если «константа» имеет значение «Ложь», то выводится признак ошибки «#Н.Д»
r2	Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y.
see	Стандартная ошибка для оценки Y
F	F - статистика ( Критерий Фишера). Используется для оценки того, являются ли связь между зависимой и независимой переменными случайной или нет. Оценка производится найденной величины F  с табличным значением при заданном количестве степеней свободы
df	Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН
ssreg	Регрессионная сумма квадратов
ssresid	Остаточная сумма квадратов

 

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

 

mn	mn	....	m2	m1	b
sen	sen-1	....	se2	se1	seb
r	sey
F	df
ssrtg	ssresid

 

F                                      Важно!  Методы, которые используются для проверки уравнений, полученных с помощью функции ЛГРФПРИБЛ(), такие же, как и для функции ЛИНЕЙН(). Однако, дополнительная статистика, которую возвращает функция ЛГРФПРИБЛ(), основана на следующей линейной модели:

 

ln y = х₁* ln (m1) + ... + x_т *ln(m_n) + ln(b)

Это следует помнить при оценке дополнительной статистики, особенно значений sei  и seb, которые следует сравнивать с ln(m_i) и ln (b), а не с m_i и b. Для получения более подробной информации, см. любой справочник по математической статистике.

 

Замечания:

Формулы, которые возвращают массивы, должны быть введены как формулы массивов.[7]

 

Создание формулы массива[8]

·        Выделите диапазон ячеек, в которые следует ввести формулу.

·        Наберите формулу.

·        Нажмите клавиши CTRL+SHIFT+ENTER.

Пример 1

В таблице приведены данные по продаже электробытовых товаров          ( холодильники, пылесосы, обогреватели и др.)1978 - 1985гг., полученные  на основе опроса населения

 

Необходимо

найти уравнение регрессии  наилучшим образом описывающее данные опроса, на 1990 и 1995 годы полагая, что процессы определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.

 

 

 

Внимание: В том случае, если в качестве независимой переменной используется дата, то  введите дополнительную строку с номером этой даты ( в нашем примере ячейки A2:I2) и используйте ее в дальнейшем в качестве независимой переменной. Дело в том, что русифицированные версии Excel очень часто дают ошибку при использовании даты в качестве независимой переменной.

 

·        На рабочем листе Excel  выделите  область ячеек A6:B10,

·        Вызовите мастер функций ЛИНЕЙН()

·        В открывшемся диалоговом окне:

o       В окно «Известные значения Y» введите адреса ячеек, содержащих эти значения (B4:I4)

o       В окно «Известные значения Х» введите адреса ячеек, содержащих эти значения (B2:I2);

o       В окно «статистика» введите 1;

·        Нажмите клавиши CTRL+SHIFT+ENTER

 

 

·        введем формулу =ЛИНЕЙН(B3:I3;B2:I2;1;1) и нажмем клавиши «Ctrl+Shift+Enter»

 

FВ результате выполненных действий, в выделенный интервал ячеек будут возвращены значения статистики уравнения линейной регрессии.

 

Уравнение линейной регрессии для нашего примера будет иметь вид

Y = 0.998*X + 1.041

R² = 0,93

Подобным же образом может быть найдено уравнение логарифмического приближения (функция ЛГРФПРИБЛ()).

Рис. 4. Рабочий лист Excel с примером расчета функции ЛИНЕЙН()

 

 

 

12 3 4 5 6 7 8 9 10 11 12

Схожі підручники

• Стратегічний маркетінг
• Cправочник терапевта
• Социология
• Биржевой рынок риска
• Методы нейроинформатики
• К вопросу об исхождении Святого Духа