Oc-windows.ru

IT Новости из мира ПК
16 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Средняя ошибка аппроксимации в excel

Тогда средняя ошибка аппроксимации равна

Таблица 3.1 – Исходные данные

ОбластьСредний размер назначенных ежемесячных пенсий, у.д.е., уПрожиточный минимум в среднем на одного пенсионера в месяц, у.д.е., х
Орловская
Рязанская
Смоленская
Тверская
Тульская
Ярославская

Эмпирические коэффициенты регрессии b, b1 будем определять с помощью инструмента «Регрессия» надстройки «Анализ данных» табличного процессораMS Excel.

Алгоритм определения коэффициентов состоит в следующем.

1. Вводимисходные данные в табличный процессор MS Excel.

2. Вызываемнадстройку Анализ данных(рисунок 2).

3.Выбираем инструмент анализа Регрессия(рисунок 3).

4. Заполняем соответствующие позиции окна Регрессия (рисунок 4).

5. Нажимаем кнопку ОК окна Регрессия и получаем протокол решения задачи (рисунок 5)

Рисунок 2 – Активизация надстройки Анализ данных

Рисунок 3 – Выбор инструмента Регрессия


Рисунок 4 – Окно Регрессия

Рисунок 5 – Протокол решения задачи

Из рисунка 5 видно, что эмпирические коэффициенты регрессии соответственно равны

b1 = 0, 0088.

Тогда уравнение парной линейной регрессии, связывающая величину ежемесячной пенсии у с величиной прожиточного минимумахимеет вид

.(3.2)

Далее, в соответствии с заданием необходимо оценить тесноту статистической связи между величиной прожиточного минимума х и величиной ежемесячной пенсии у. Эту оценку можно сделать с помощью коэффициента корреляции . Величина этого коэффициента на рисунке 5 обозначена как множественный R и соответственно равна 0,038. Поскольку теоретически величина данного коэффициента находится в пределахот –1 до +1, то можно сделать вывод о не существенности статистической связимежду величиной прожиточного минимума х и величиной ежемесячной пенсии у.

Параметр «R – квадрат», представленныйна рисунке 5 представляет собой квадрат коэффициента корреляции и называется коэффициентом детерминации. Величина данного коэффициента характеризует долю дисперсии зависимой переменной у, объясненную регрессией (объясняющей переменной х). Соответственно величина 1- характеризует долю дисперсии переменной у, вызванную влиянием всех остальных, неучтенных в эконометрической модели объясняющих переменных. Из рисунка 5 видно, что доля всех неучтенных в полученной эконометрической модели объясняющих переменных приблизительно составляет 1- 0,00145 = 0,998 или 99,8%.

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

. (3.3)

Следовательно, при изменении прожиточного минимума на 1% величина ежемесячной пенсии изменяется на 0,000758%.

Далее определяем среднюю ошибку аппроксимации по зависимости

. (3.4)

Для этого исходную таблицу 1 дополняем двумя колонками, в которых определяем значения, рассчитанные с использованием зависимости (3.2) и значения разности .

Таблица 3.2. Расчет средней ошибки аппроксимации.

ОбластьСредний размер назначенных ежемесячных пенсий, у.д.е., уПрожиточный минимум в среднем на одного пенсионера в месяц, у.д.е., х
Орловская0,032
Рязанская0,045
Смоленская0,021
Тверская0,012
Тульская0,028
Ярославская0,017
S=0,155

Тогда средняя ошибка аппроксимации равна

.

Из практики известно, что значение средней ошибки аппроксимации не должно превышать (12…15)%

На последнем этапе выполним оценкустатистической надежности моделирования спомощью F – критерия Фишера. Для этого выполним проверку нулевой гипотезы Н о статистической не значимости полученного уравнения регрессиипо условию:

если при заданном уровне значимости a = 0,05 теоретическое (расчетное) значение F-критерия больше его критического значения Fкрит (табличного), то нулевая гипотеза отвергается, и полученное уравнение регрессии принимается значимым.

Из рисунка 5 следует, что Fрасч = 0,0058. Критическое значение F-критерия определяем с помощью использования статистической функции FРАСПОБР (рисунок 6). Входными параметрами функции является уровень значимости (вероятность) и число степеней свободы 1 и 2. Для модели парной регрессии число степеней свободы соответственно равно 1 (одна объясняющая переменная) и n-2 = 6-2=4.

Рисунок 6 – Окно статистической функции FРАСПОБР

Из рисунка 6 видно, что критическое значение F-критерия равно 7,71.

Средняя относительная ошибка аппроксимации

Курсовая работа

по дисциплине «Эконометрика»

«Комплексный анализ взаимосвязи финансово-экономических показателей деятельности предприятий»

студент группы ЭЭТ-312

1. Составление корреляционной матрицы. Отбор факторов

2. Построение уравнения множественной линейной регрессии. Интерпретация параметров уравнения

3. Коэффициент детерминации, множественный коэффициент корреляции

4.Оценка качества уравнения множественной линейной регрессии

4.1.Средняя относительная ошибка аппроксимации

4.2.Проверка статистической значимости уравнения множественной регрессии в целом с помощью F-критерия Фишера

4.3.Проверка статистической значимости параметров уравнения множественной регрессии. Интервальные оценки параметров

5.Применение регрессионной модели

5.2.Частные коэффициенты эластичности и средние частные коэффициенты эластичности

6.Анализ остатков регрессионной модели (проверка предпосылок теоремы Гаусса-Маркова)

6.1.Оценки математического ожидания остатков

6.2.Проверка наличия автокорреляции в остатках

7.Критерий Грегори Чоу

Постановка задачи

Заданы значения 6 показателей, характеризующих экономическую деятельность 53 предприятий. Требуется:

1. Составить корреляционную матрицу. Скорректировать набор независимых переменных (отобрать 2 фактора).

2. Построить уравнение множественной линейной регрессии. Дать интерпретацию параметров уравнения.

3. Найти коэффициент детерминации, множественный коэффициент корреляции. Сделать выводы.

4. Оценить качество уравнения множественной линейной регрессии:

4.1. Найти среднюю относительную ошибку аппроксимации. Сделать выводы.

4.2. Проверить статистическую значимость уравнения множественной регрессии в целом с помощью F-критерия Фишера. Сделать выводы

4.3. Проверить статистическую значимость параметров уравнения множественной регрессии. Построить интервальные оценки параметров. Сделать выводы.

5. Применение регрессионной модели:

5.1. Используя построенное уравнение, дать точечный прогноз. Найти значение исследуемого параметра y, если значение первого фактора (наиболее тесно связанного с у) составит 110% от его среднего значения, значение второго фактора составит 80% от его среднего значения. Дать экономическую интерпретацию результата.

5.2. Найти частные коэффициенты эластичности и средние частные коэффициенты эластичности. Интерпретировать результаты. Сделать выводы.

6. Провести анализ остатков регрессионной модели (проверить требования теоремы Гаусса-Маркова):

6.1. Найти оценки математического ожидания остатков.

6.2. Проверить наличие автокорреляции в остатках. Сделать вывод.

7. Разделите выборку на две равные части. Рассматривая первые и последние наблюдения как независимые выборки, проверить гипотезу о возможности объединения их в единую выборку по критерию Грегори-Чоу.

Составление корреляционной матрицы. Отбор факторов

№ предприятияY3X10X12X5X7X13
13,261,45167,690,781,37
10,161,3186,10,751,49
13,721,37220,450,681,44
12,851,65169,30,71,42
10,631,9139,530,621,35
9,121,6840,410,761,39
25,831,94102,960,731,16
23,391,8937,020,711,27
14,681,9445,740,691,16
10,052,0640,070,731,25
13,991,9645,440,681,13
9,681,0241,080,741,1
10,031,85136,140,661,15
9,130,8842,390,721,23
5,370,6237,390,681,39
9,861,09101,780,771,38
12,621,647,550,781,35
5,021,5332,610,781,42
21,181,4103,250,811,37
25,172,2238,950,791,41
19,41,3281,320,771,35
1,4867,260,781,48
6,570,6859,920,721,24
14,192,3107,340,791,40
15,811,37512,60,771,45
5,231,5153,810,81,4
7,991,4380,830,711,28
17,51,8259,420,791,33
17,162,6236,960,761,22
14,541,7591,430,781,28
6,241,5417,160,621,47
12,082,2527,290,751,27
9,491,07184,330,711,51
9,281,4458,420,741,46
11,421,459,40,651,27
10,311,3149,630,661,43
8,651,12391,270,841,5
10,941,16258,620,741,35
9,870,8875,660,751,41
6,141,07123,680,751,47
12,931,2437,210,791,35
9,781,4953,370,721,4
13,222,0332,870,71,2
17,291,8445,630,661,15
7,111,2248,410,691,09
22,491,7213,580,711,26
12,141,7563,990,731,36
15,251,46104,550,651,15
31,341,6222,110,821,87
11,561,4725,760,81,17
30,141,3829,520,831,61
19,711,4141,990,71,34
23,561,3978,110,741,22
Читать еще:  Как связать excel и powerpoint

1.Составить корреляционную матрицу. Скорректировать набор независимых переменных (отобрать 2 фактора).

Рассмотрим результативный признак Y3 и факторные признаки Х10, X12, Х5, Х7, Х13.

Составим корреляционную матрицу с помощью опции «Анализ данных→Корреляция» в MS Excel:

Y3X10X12X5X7X13
Y31,00000,36530,01850,28910,17360,0828
X100,36531,0000-0,2198-0,0166-0,2061-0,0627
X120,0185-0,21981,00000,23920,37960,6308
X50,2891-0,01660,23921,00000,41470,0883
X70,1736-0,20610,37960,41471,00000,1939
X130,0828-0,06270,63080,08830,19391,0000

Отбираем 2 фактора по критериям:

1) связь Y и X должна быть максимальной

2) связь между Xми должна быть наименьшей

r_Y3,X10=0,3653 связь между Y3 и X10 слабая
r_Y3,X12=0,0185 связь между Y3 и X12 практически отсутствует
r_Y3,X5=0,2891 связь между Y3 и X5 слабая
r_Y3,X7=0,1736 связь между Y3 и Х7 практически отсутствует
r_Y3,X13=0,0828 связь между Y3 и Х13 практически отсутствует
r_X10,X12=-0,2198 связь между Х10 и Х12 слабая обратная
r_X10,X5=-0,0166 связь между Х10 и Х5 практически отсутствует обратная
r_X10,X7=-0,2061 связь между Х10 и Х7 слабая обратная
r_X10,X13=-0,0627 связь между Х10 и Х13 практически отсутствует обратная

Таким образом, в следующих пунктах работа будет производиться с факторами X10, X5.

Построение уравнения множественной линейной регрессии. Интерпретация параметров уравнения.

2. Построить уравнение множественной линейной регрессии. Дать интерпретацию параметров уравнения.

Составим регрессионную модель с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel:

Коэффициенты
Y-20,7163
X 105,7169
X 534,9321

Уравнение регрессии будет выглядеть следующим образом:

1) b10 положительный;

2) b5 положительный;

Коэффициент детерминации, множественный коэффициент корреляции

3. Найти коэффициент детерминации, множественный коэффициент корреляции. Сделать выводы.

В регрессионном анализе, выполненном с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel, найдём таблицу «Регрессионная статистика»:

Регрессионная статистика
Множественный R0,469667774
R-квадрат0,220587818
Нормированный R-квадрат0,189411331
Стандартная ошибка5,669239125
Наблюдения

Множественный R-связь между Y3 и X10,X5 слабая

R-квадрат-22,05% вариации признака Y объясняется вариацией признаков X10 и X5

Оценка качества уравнения множественной линейной регрессии

4. Оценить качество уравнения множественной линейной регрессии:

Средняя относительная ошибка аппроксимации

4.1. Найти среднюю относительную ошибку аппроксимации. Сделать выводы.

Рассчитаем прогнозные значения для каждого наблюдения или воспользуемся столбцом «Предсказанное У» в таблице «Вывод остатка» в регрессионном анализе, выполненном с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel)

Вычислим относительные ошибки для каждого наблюдения по формуле:

Вычислим среднюю относительную ошибку аппроксимации по формуле:

Задача

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

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

2. Вычислить остатки; найти остаточную сумму квадратов; оценить дисперсию остатков S2ε ; построить график остатков.

3. Проверить выполнение предпосылок МНК.

4. Осуществить проверку значимости параметров уравнения регрессии с помощью t-критерия Стьюдента (α=0,05).

5. Вычислить коэффициент детерминации, проверить значимость уравнения регрессии с помощью f-критерия Фишера (α=0,05), найти среднюю относительную ошибку аппроксимации. Сделать вывод о качестве модели.

6. Осуществить прогнозирование среднего значения показателя Y при уровне значимости α=0,1, если прогнозное значение фактора X составит 80% от его максимального значения.

7. Представить графически фактическое и модельное значение Y точки прогноза.

8. Составить уравнения нелинейной регрессии:

Привести графики построенных уравнений регрессии.

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

Задача 1. Уравнение линейной регрессии имеет вид: . А значения параметров а и b линейной модели можно определить по данным формулам:

, .

С помощью ППП Excel найдем параметры уравнения линейной регрессии. Порядок выселения следующий:

1. Активизируем инструмент Пакет анализа:

1.1. Сервис →Настройки;

1.2. В диалоговом окне Настройки отметим пункт Пакет анализа→ ОК.

2. Ведем исходные данные;

Рис. 1. Исходные данные

3. Сервис → Анализ данных → Регрессия→ОК;

4. Заполним диалоговое окно ввода данных и параметров вывода:

Рис. 2. Диалоговое окно ввода параметров инструмента Регрессия

Результаты регрессионного анализа для данных представлены на рис. 3.

Рис. 3. Результат применения инструмента Регрессия

В ячейках В17 и В18 расположены значения параметров а и b соответственно. Итак, уравнение регрессии имеет вид: .

Коэффициент регрессии b показывает, что с ростом капиталовложений на 1 млн. руб. выпуск продукции увеличивается в среднем на 2,40 млн. руб.

Задача 2. Остатки определяются по формуле: . Соответственно остаточная сумма квадратов определяется по формуле: .

На рис. 3. в ячейках С25:С34 уже вычислены остатки. А остаточную сумму квадратов найдем с помощью ППП Excel, использую функцию ПРОИЗВЕД. Результаты вычислений приведены на рис. 6.

Рис. 4. Остаточная сумма квадратов

Итак, остаточная сумма квадратов равна 25,96– она также вычислена с помощью Регрессии (ячейка D13).

Дисперсия остатков определяется по формуле: .

Поскольку остаточная сумма квадратов вычислена и равна 25,96, а количество наблюдений 10, то можно найти дисперсию остатков. Результат вычисления приведен на рис. 4 в ячейке В37.

Итак, дисперсия остатков составляет 25,96 (она также вычислена с помощью Регрессии – рис. 3, ячейка D13).

Читать еще:  Асимметричность в excel

График остатков уже построен с помощью инструмента Анализа данных Регрессия (рис. 3). Приведем график остатков в отдельный вид.

Рис. 5. График остатков

Задача 3. Проверим выполнение предпосылок МНК. Свойства коэффициентов регрессии существенным образом зависят от свойств случайной составляющей. Для того чтобы МНК давал наилучшие результаты, должны выполняться условия Гаусса — Маркова.

Условие 1. Математическое ожидание случайной составляющей в любом наблюдении должно быть равно нулю: М(εi)=0.

В нашем случае уравнение регрессии включает постоянный член и, следовательно, это условие выполняется автоматически.

Условие 2. Случайная составляющая (εi) или зависимая переменная (yi)есть величины случайные, а независимая величина (xi)– величина неслучайная: . Проверим выполнение данного условия с помощью критерия поворотных точек, для этого постоим дополнительную таблицу.

Р — число поворотных точек. В нашем примере Р=6.

;

;

;

;

Р> 2,99; т. е. 6>2,99. Следовательно, условие выполняется.

Итак, случайная составляющая (εi) или зависимая переменная (yi) есть величины случайные.

Условие 3. Случайная переменная в любых двух наблюдениях независима.

Чтобы проверить выполнение данного условия, с помощью ППП Excel вычислим dw-критерий Дарбина — Уотсона: .

Т. к. остатки и остаточная сумма квадратов уже вычислены (рис. 5),то для нахождения dw-критерий Дарбина – Уотсона нужно найти (εi-εi-1) и (εi-εi-1)2.

Рис. 6. Вычисление dw-критерия Дарбина-Уотсона

Итак, dw=1,70. Поскольку dw > d2 (d2 = 1,36) , но dw tтабл и tb>tтабл, то параметры a и b уравнения регрессии значимы.

Осуществим проверку значимости параметров уравнения регрессии с помощью t-критерия Стьюдента (α=0,1) с помощью функции СТЬЮДРАСПОБР.

Рис. 16. Результат вычисления tтабл (α=0,1)

Итак, табличное значение t-критерия при уровне значимости и степенях свободы составляет 1,. Так как tа>tтабл и tb>tтабл, то параметры a и b уравнения регрессии значимы.

Задача 5. В случае линейной зависимости между переменными парный коэффициент корреляции является показателем тесноты связи и определяется по формуле:

.

Коэффициент корреляции в нашем примере уже вычислен с помощью инструмента Excel Регрессии (рис. 3, стр. 4) – ячейка В4, который равен 0,98405.

По шкале Чеддока коэффициент корреляции попал в интервал от 0,9 до 1, следовательно, это говорит о весьма высокой связи.

Долю дисперсии, объясняемую регрессией в общей дисперсии результативного признака y, характеризует коэффициент детерминации:

.

Коэффициент детерминации в нашем примере уже вычислен с помощью инструмента анализа Регрессии (рис. 3, ячейка В5) и составляет 0,96836.

Значимость уравнения регрессии y=13,89+2,40x определяется с помощью F-критерия Фишера (α=0,05) используя данную формулу: .

.

Табличное значение F-критерия Фишера при доверительной вероятности 0,05 при ν1=1 и ν2=8 уже вычислено с помощью функции FРАСПОБР и составляет 5,31766. Поскольку Fрасч>F табл, уравнение регрессии следует признать значимым.

Коэффициент эластичности для линейной функции определяется по формуле:

.

Таким образом, .

Это значит, что если фактор измениться на 1%, то в среднем на 0,87% измениться результат.

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

.

Вычислим относительную ошибку аппроксимации с помощью Excel.

Рис.17. Результаты вычислений относительной ошибки аппроксимации

Итак, относительная ошибка аппроксимации составила 3,86%, что говорит о качественной модели.

Задача 6. Осуществим прогнозирование среднего значения показателя Y при уровне значимости α=0,1, если известно, что прогнозное значение фактора Х составит 80% от его максимального значения.

Прогнозное значение переменной y получается при подстановке в уравнение регрессии ожидаемого значения x: , где .

В нашем случае .Отсюда .

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

.

Ширина доверительного интервала определяется по формуле:

.

Величина уже вычислена (рис. 3, ячейка В7) и равна 5,0958. Коэффициент Стьюдента для m=8 степеней свободы и уровня значимости 0,1 равен 1, Произведем дополнительные расчеты:

Рис. 18. Дополнительные расчеты

.

Аппроксимация в Excel

(Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.

Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.

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

«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

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

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

Читать еще:  Работа с базой данных в excel

Аппроксимация в Excel статистических данных аналитической функцией.

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

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

1. Включаем Excel и помещаем на лист таблицу с данными статистики.

2. Далее строим и форматируем точечную диаграмму, в которой по оси X задаем значения аргумента – количество переработанных уголков в тоннах. По оси Y откладываем значения исходной функции – общий выпуск металлоконструкций в месяц, заданные таблицей.

О том, как построить подобную диаграмму, подробно рассказано в статье «Как строить графики в Excel?».

3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».

4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».

5. Далее на вкладке «Параметры» ставим 2 галочки и нажимаем «ОК».

6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.

Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R 2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!

7. Строим линии тренда, используя степенную, логарифмическую, экспоненциальную и полиномиальную аппроксимации по аналогии с тем, как мы строили линейную линию тренда.

Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R 2 .

Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…. Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл. Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!

Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.

8. Удаляем все линии тренда с поля диаграммы, кроме логарифмической функции. Для этого щелкаем правой кнопкой мыши по ненужным линиям и в выпавшем контекстном меню выбираем «Очистить».

9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.

10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.

Аналогичным образом форматируются любые другие объекты диаграммы в Excel!

Окончательный результат диаграммы представлен на следующем снимке экрана.

Итоги.

Результатом всех предыдущих действий стала полученная формула аппроксимирующей функции y=-172,01*ln (x)+1188,2. Зная ее, и количество уголков в месячном наборе работ, можно с высокой степенью вероятности (±4% — смотри планки погрешностей) спрогнозировать общий выпуск металлоконструкций за месяц! Например, если в плане на месяц 140 тонн уголков, то общий выпуск, скорее всего, при прочих равных составит 338±14 тонн.

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

Из практики скажу, что хорошим результатом следует считать нахождение аппроксимирующей функции с коэффициентом достоверности R 2 >0,87. Отличный результат – при R 2 >0,94.

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

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

Затронутый вопрос аппроксимации функции одной переменной имеет широкое практическое применение в разных сферах жизни. Но гораздо большее применение имеет решение задачи аппроксимации функции нескольких независимых переменных…. Об этом и не только читайте в следующих статьях на блоге.

Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы.

Не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку «Спам»).

С интересом прочту Ваши комментарии, уважаемые читатели! Пишите!

P.S. (04.06.2017)

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

Вас не устраивают полученные точность аппроксимации (R 2 2 =0,9963.

Ссылка на основную публикацию
Adblock
detector