Шарстнёв В.Л. Компьютерные информационные технологии
Подождите немного. Документ загружается.
81
ее отсутствии, и, наоборот, малое рассеяние указывает на наличие достаточно
сильной зависимости.
Коэффициент детерминации (по другому – детерминированности)
2
r
показывает, на сколько процентов (
%
100
r
2
⋅ ) найденная функция регрессии
описывает связь между исходными значениями параметров X и Y:
(
)
( )
∑
∑
=
=
−
−
=
n
1
i
2
i
n
1i
2
р
i
2
yy
yy
r ,
где
(
)
2
р
i
yy − – объясненная вариация;
(
)
2
i
yy − – общая вариация.
0
X
i
x
Необъясненная вариация
Объясненная
Общая
вариация
вариация
Y
р
i
y
i
y
y
)x(fy
x
=
Графическая интерпретация коэффициента детерминации для случая
линейной регрессии
Соответственно, величина %100)r1(
2
⋅− показывает, сколько процентов
вариации параметра Y обусловлены факторами, не включенными в
регрессионную модель. При высоком (
%
75
r
2
≥ ) значении коэффициента
детерминации можно делать прогноз )x(fy
**
= для конкретного значения
*
x
.
Линейная регрессия
Коэффициенты линейной регрессии xaay
1
0
+
=
вычисляются по
следующим формулам (все суммы берутся по n парам исходных данных):
(
)
( )
( )
;
xxn
xyxyn
a
2
i
2
i
iiii
1
∑∑
∑
∑
∑
−
−
=
PDF created with pdfFactory Pro trial version www.pdffactory.com
82
( )
∑∑
−=
i1i0
xay
n
1
a .
Нелинейная регрессия
Рассмотрим наиболее простые случаи нелинейной регрессии: гиперболу,
экспоненту и параболу. При нахождении коэффициентов гиперболы и
экспоненты используют прием приведения нелинейной регрессионной
зависимости к линейному виду. Это позволяет использовать для вычисления
коэффициентов функций регрессии формулы линейной зависимости.
Гипербола
При нахождении гиперболы
x
a
ay
1
0
+= вводят новую переменную
x
1
z = , тогда уравнение гиперболы принимает линейный вид zaay
1
0
+
=
.
После этого используют формулы для нахождений линейной функции, но
вместо значений
i
x используются значения
i
i
x
1
z = :
(
)
( )
( )
2
i
2
i
iiii
1
zzn
zyzyn
a
∑∑
∑
∑
∑
−
−
= ;
( )
∑∑
−=
i1i0
zay
n
1
a .
Экспонента
Для приведения к линейному виду экспоненты
xa
0
1
eay = проводят
логарифмирование:
(
)
xa
0
1
ealnyln = ;
(
)
xa
0
1
elnalnyln += ;
xaalnyln
1
0
+
=
.
Введя переменные
0
0
alnb
=
и
1
1
ab
=
, получают xbbyln
1
0
+
=
, откуда
следует, что можно применять формулы линейной зависимости, в которых
вместо значений
i
y надо использовать
i
yln :
[
]
(
)
( )
( )
2
i
2
i
iiii
1
xxn
xylnxylnn
b
∑∑
∑
∑
∑
−
−
= ;
( )
∑∑
−=
i1i0
xbyln
n
1
b .
При этом получаем численные значения коэффициентов
0
b и
1
b , от которых
надо перейти к
0
a и
1
a , используемые в модели экспоненты. Исходя из
введенных обозначений и определения логарифма, получаем
0
b
0
ea = ,
1
1
ba
=
.
PDF created with pdfFactory Pro trial version www.pdffactory.com
83
Парабола
Для нахождения коэффициентов параболы
2
2
1
0
xaxaay ++=
необходимо решить линейную систему из трех уравнений:
(
)
(
)
( )
( ) ( )
( )
( ) ( ) ( ) ( )
=++
=++
=++⋅
∑∑∑∑
∑∑∑∑
∑∑∑
.xyaxaxax
,xyaxaxax
,yaxaxan
2
i
i2
4
i
1
3
i
0
2
i
ii2
3
i
1
2
i
0i
i2
2
i
1i0
При вычислении коэффициента детерминации экспоненты все значения
параметра Y (исходные, регрессионные, среднее) необходимо заменить на их
логарифмы, например,
р
i
y – на
(
)
р
i
yln и т.д.
Информационная поддержка процессов моделирования и управления
Информационная поддержка процессов моделирования и управления
может осуществляться с использованием самых разнообразных программных
средств. Назовем и охарактеризуем некоторые из них:
• универсальные и специализированные языки программирования;
• стандартные офисные программные продукты;
• системы компьютерной математики;
• системы управления проектами;
• CASE-технологии;
• специализированные статистические пакеты.
Универсальные и специализированные языки программирования
Существующие языки программирования, безусловно, позволяют
осуществить построение модели любого вида и любой сложности. Однако для
этого от экономиста (управленца) требуются профессиональные знания и
навыки программирования. В случае разработки собственного программного
средства целесообразнее возложить исполнение этой задачи на
профессионального программиста.
Стандартные офисные программные продукты
К наиболее известным программным продуктам позволяющим
моделировать процессы управления, можно отнести:
• Microsoft Office;
• Star Office;
• Lotus;
• Open Office и т.д.
PDF created with pdfFactory Pro trial version www.pdffactory.com
84
Наиболее известной компонентой является Microsoft Excel, в состав
которой входят функции для построения различного вида моделей. Помимо
этого, имеется возможность отыскания оптимального решения при заданных
ограничениях.
В стандартном наборе функций Excel имеются функции, которые
позволяют осуществить построение моделей с использованием метода
среднеквадратического отклонения на основании следующих зависимостей:
• линейного приближения;
• экспоненциального приближения.
Линейное приближение
В состав функций, позволяющих осуществить построение и анализ по
методу линейного приближения, относятся:
ЛИНЕЙН
ТЕНДЕНЦИЯ
ПРЕДСКАЗ
ЛИНЕЙН
Рассчитывает статистику для ряда с применением метода наименьших
квадратов, чтобы вычислить прямую линию, которая наилучшим образом
аппроксимирует имеющиеся данные. Функция возвращает массив, который
описывает полученную прямую. Поскольку возвращается массив значений,
функция должна задаваться в виде формулы массива.
Уравнение для прямой линии имеет следующий вид:
y = m*x + b или y = m
1
*x
1
+ m
2
*x
2
+ ... m
n
*x
n
+ b (в случае нескольких
диапазонов значений x), где зависимое значение y — функция независимого
значения x, значения m — коэффициенты, соответствующие каждой
независимой переменной x, а b — постоянная. Заметим, что y, x и m могут быть
векторами. Функция ЛИНЕЙН возвращает массив {m
n
; m
n-1
; ...; m
1
; b}.
ЛИНЕЙН может также возвращать дополнительную регрессионную
статистику.
Синтаксис
ЛИНЕЙН(известные_значения_y; известные_значения_x; константа;
статистика)
Известные_значения_y — множество значений y, которые уже известны
для соотношения y = m*x + b.
•
•
•
Если массив известные_значения_y имеет один столбец, то каждый
столбец массива извест
ные_значения_x интерпретируется как
отдельная переменная.
•
•
•
Если массив известные_значения_y имеет одну строку, то каждая
строка массива извест
ные_значения_x интерпретируется как
отдельная переменная.
PDF created with pdfFactory Pro trial version www.pdffactory.com
85
Известные_значения_x — необязательное множество значений x, которые
уже известны для соотношения y = m*x + b.
•
•
•
Массив известные_значения_x может содержать одно или
неско
лько множеств переменных. Если используется только одна
переменная, то известные_значения_y и известные_значения_x
могут иметь любую форму, при условии, что они имеют
одинаковую размерность. Если используется более одной
переменной, то известные_значения_y
должны быть вектором (то
есть интервалом высотой в одну строку или шириной в один
столбец).
•
•
•
Если известные_значения_x опущены, то предполагается, что это
массив {1;2;3;...} такого же размера, как и известные_значения_y.
Константа — логическое значение, которое указывает, требуется ли, чтобы
константа b была равна 0.
•
•
•
Если константа имеет значение ИСТИНА или опущено, то b
вычисляется обычным образом.
•
•
•
Если аргумент константа имеет значение ЛОЖЬ, то b полагается
равным 0 и значения m подбира
ются так, чтобы выполнялось
соотношение y = m*x.
Статистика — логическое значение, которое указывает, требуется ли
вернуть дополнительную статистику по регрессии.
•
•
•
Если аргумент статистика имеет значение ИСТИНА, то функция
ЛИНЕЙН возвращает д
ополнительную регрессионную статистику,
так что возвращаемый массив будет иметь вид
{m
n
; m
n
-
1
; ...; m
1
; b: S
en
; S
en
-
1
; ...; S
e1
; S
eb
: R
2
; S
ey
: F; df: SS
reg
; SS
resid
}.
•
•
•
Если аргумент статистика имеет значение ЛОЖЬ или опущен, то
функция ЛИНЕЙН возвращ
ает только коэффициенты m и
постоянную b.
На приведенном ниже рисунке показано, в каком порядке возвращается
дополнительная регрессионная статистика.
PDF created with pdfFactory Pro trial version www.pdffactory.com
86
Дополнительная регрессионная статистика
Величина Описание
S
e1
, S
e2
,..., S
en
Стандартные значения ошибок для коэффициентов
m
1
,m
2
,...,m
n
.
S
eb
Стандартное значение ошибки для постоянной b (S
eb
=
#Н/Д, если константа имеет значение ЛОЖЬ).
R
2
Коэффициент детерминированности. Сравниваются
фактические значения y и значения, получаемые из
уравнения прямой; по результатам сравнения вычисляется
коэффициент детерминированности, нормированный от 0
до 1. Если он равен 1, то имеет место полная корреляция с
моделью, т. е. нет различия между фактическим и
оценочным значениями y. В противоположном случае,
если
коэффициент детерминированности равен 0, то уравнение
регрессии неудачно для предсказания значений y.
S
ey
Стандартная ошибка для оценки y.
F F-статистика, или F-наблюдаемое значение. F-статистика
используется для определения того, является ли
наблюдаемая взаимосвязь между зависимой и независимой
переменными случайной или нет.
df Степени свободы. Степени свободы полезны для
нахождения F-критических значений в статистической
таблице. Для определения уровня надежности модели
нужно сравнить значения в таблице с F-статистикой,
возвращаемой функцией ЛИНЕЙН.
SS
reg
Регрессионная сумма квадратов.
SS
resid
Остаточная сумма квадратов.
Пример
Построим модель зависимости рентабельности работы предприятия (y) от
удельного веса рабочих в структуре персонала (x
1
), удельного веса покупных
изделий (x
2
), коэффициента сменности оборудования (x
3
), среднегодовой
PDF created with pdfFactory Pro trial version www.pdffactory.com
87
численности персонала (x
4
), среднегодовой стоимости основных
производственных фондов (x
5
). Предположим линейную функциональную
зависимость вида
y= m
1
x
1
+ m
2
x
2
+ m
3
x
3
+ m
4
x
4
+ m
5
x
5
+b
Заметим, что функцию ЛИНЕЙН в этом примере необходимо ввести как
формулу массива, предварительно выделив диапазон размерностью 5*(n+1), где
n-количество независимых переменных X.
Для нашего примера размерность массива будет 5*6, так как n=5. Для
ввода формулы массива необходимо одновременно активизировать клавиши
CTRL+SHIFT+ENTER. В результате расчета получаем следующие данные:
На основании полученной модели выполним прогноз рентабельности для
следующих данных:
удельный вес рабочих в структуре персонала (x
1
) = 0,67;
удельный вес покупных изделий (x
2
) = 0,41;
PDF created with pdfFactory Pro trial version www.pdffactory.com
88
коэффициент сменности оборудования (x
3
) = 1,15;
среднегодовая численность персонала (x
4
) = 14100;
среднегодовая стоимость основных производственных фондов (x
5
) =
103,50.
Рентабельность = 30,00*0,67+7,89*0,41-5,04*1,15+0,0003*14100-0,027*103,50-
6,52 = 12,4544
ТЕНДЕНЦИЯ
Возвращает значения в соответствии с линейным трендом.
Аппроксимирует прямой линией (по методу наименьших квадратов) массивы
известные_значения_y и известные_значения_x. Возвращает значения y, в
соответствии с этой прямой для заданного массива новые_значения_x.
Синтаксис
ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x;
новые_значения_x; константа)
Известные_значения_y — множество значений y, которые уже известны
для соотношения y = m*x + b.
•
•
•
Если массив известные_значения_y имеет один столбец, то каждый
столбец массива известные_значения_x интерпретируется ка
к
отдельная переменная.
•
•
•
Если массив известные_значения_y имеет одну строку, то каждая
строка массива извест
ные_значения_x интерпретируется как
отдельная переменная.
Известные_значения_x — необязательное множество значений x, которые
уже известны для соотношения y = m*x + b.
•
•
•
Массив известные_значения_x может содержать одно или
несколько множеств переменных. Если используется только одна
переменная, то известные_значения_y и известные_значения_x
могут иметь любую форму, при условии, что они и
меют
одинаковую размерность. Если используется более одной
переменной, то известные_значения_y должны быть вектором (то
есть интервалом высотой в одну строку или шириной в один
столбец).
•
•
•
Если известные_значения_x опущены, то предполагается, что это
массив {1;2;3;...} такого же размера, как и известные_значения_y.
Новые_значения_x — новые значения x, для которых ТЕНДЕНЦИЯ
возвращает соответствующие значения y.
•
•
•
Новые_значения_x должны содержать столбец (или строку) для
каждой независимой пере
менной, как и известные_значения_x.
Таким образом, если известные_значения_y —
это один столбец, то
PDF created with pdfFactory Pro trial version www.pdffactory.com
89
известные_значения_x и новые_значения_x должны иметь такое же
количество столбцов. Если известные_значения_y —
это одна
строка, то известные_значения_x и но
вые_значения_x должны
иметь такое же количество строк.
•
•
•
Если новые_значения_x опущены, то предполагается, что они
совпадают с параметром известные_значения_x.
•
•
•
Если опущены оба массива известные_значения_x и
новые_значения_x, то предполагается, ч
то это массив {1;2;3;...}
такого же размера, что и известные_значения_y.
Константа — логическое значение, которое указывает, требуется ли, чтобы
константа b была равна 0.
•
•
•
Если константа имеет значение ИСТИНА или опущено, то b
вычисляется обычным образом.
•
•
•
Если константа имеет значение ЛОЖЬ, то b полагается равным 0, и
значения m подбираются та
ким образом, чтобы выполнялось
соотношение y = m*x.
ПРЕДСКАЗ
Вычисляет или предсказывает будущее значение по существующим
значениям. Предсказываемое значение — это y-значение, соответствующее
заданному x-значению. Известные значения — это x- и y-значения, а новое
значение предсказывается с использованием линейной регрессии. Эту функцию
можно использовать для предсказания будущих продаж, потребностей в
оборудовании или тенденций потребления.
Синтаксис
ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x)
x — это точка данных, для которой предсказывается значение.
Известные_значения_y — это зависимый массив или интервал данных.
Известные_значения_x — это независимый массив или интервал данных.
Экспоненциальное приближение
В состав функций, позволяющих осуществить построение и анализ по
методу экспоненциального приближения, относятся:
ЛГРФПРИБЛ
РОСТ
ЛГРФПРИБЛ
В регрессионном анализе вычисляется экспоненциальная кривая,
аппроксимирующая данные и возвращается массив значений, описывающий
эту кривую. Поскольку данная функция возвращает массив значений, она
должна вводиться как формула массива.
PDF created with pdfFactory Pro trial version www.pdffactory.com
90
Уравнение кривой имеет вид y = b*m
x
или y = (b*(m
1
x1
)*(m
2
x2
)*...*(m
n
xn
)) (в
случае нескольких значений x), где зависимые значения y являются функцией
независимых значений x. Значения m являются основанием, возводимым в
степень x, а значения b постоянны. Заметим, что y, x и m могут быть векторами.
Функция ЛГРФПРИБЛ возвращает массив {m
n
;m
n-1
;...;m
1
;b}.
Синтаксис
ЛГРФПРИБЛ(известные_значения_y; известные_значения_x;
константа; статистика)
Известные_значения_y — множество значений y, которые уже известны в
соотношении y = b*m
x
.
Если массив известные_значения_y имеет один столбец, то каждый
столбец массива известные_значения_x интерпретируется как отдельная
переменная.
Если массив известные_значения_y имеет одну строку, то каждая строка
массива известные_значения_x интерпретируется как отдельная переменная.
Известные_значения_x — необязательное множество значений x, которые уже
известны для соотношения y = b*m
x
.
Массив известные_значения_x может включать одно или более множеств
переменных. Если используется только одна переменная, то
известные_значения_y и известные_значения_x могут быть диапазонами любой
формы, если только они имеют одинаковые размерности. Если используется
более одной переменной, то аргумент известные_значения_y должен быть
диапазоном ячеек высотой в одну строку или шириной в один столбец (так
называемым вектором).
Если известные_значения_x опущены, то предполагается, что это массив
{1;2;3;...} такого же размера, как и известные_значения_y.
Константа — логическое значение, которое указывает, требуется ли, чтобы
константа b была равна 1.
Если константа имеет значение ИСТИНА или опущено, то b вычисляется
обычным образом.
Если константа имеет значение ЛОЖЬ, то b полагается равным 1 и
значения m подбираются так, чтобы удовлетворить соотношению y = m
x
.
Статистика — логическое значение, которое указывает, требуется ли вернуть
дополнительную статистику по регрессии.
Если статистика имеет значение ИСТИНА, то функция ЛГРФПРИБЛ
возвращает дополнительную статистику по регрессии, то есть возвращает
массив {m
n
; m
n-1
; ...; m
1
; b: S
en
; S
en-1
; ...; S
e1
; S
eb
: R
2
; S
ey
: F; df: SS
reg
; SS
resid
}.
Если статистика имеет значение ЛОЖЬ или опущено, то функция
ЛГРФПРИБЛ возвращает только коэффициенты m и константу b.
Важно! Методы, которые используются для проверки уравнений, полученных с
помощью функции ЛГРФПРИБЛ, такие же, как и для функции ЛИНЕЙН.
PDF created with pdfFactory Pro trial version www.pdffactory.com