Регрессия
В экономике и технике часто возникает задача подбора функциональной зависимости для двух наборов данных. Независимые переменные х, называют факторами, а зависимые у, — откликами. Функция y = F(x) позволяет предсказывать значения отклика для значений фактора, не входящих в исходную совокупность. Задача регрессии включена в "Пакет анализа".Метод наименьших квадратов
Решим следующую задачу. Дан набор точек (xi,yi), i=1,...,n. Пусть имеется класс функций F (линейные, квадратичные, экспоненциальные и т.д.). Требуется найти функцию у = f(x) из F, такую, чтобы ее значения f(xi) наилучшим образом приближали значения уi. Что означают слова "наилучшим образом"? Нужно выбрать критерий, насколько одна функция лучше другой. Для этого рассмотрим набор остатков ei=yi–f(xi). Выбором функции f(x) нужно сделать их как можно меньшими. Но для сравнения качества приближения необходимо свернуть ei, в одну функцию. Просто сложить остатки нельзя, ведь они могут иметь разные знаки, и тогда ошибки могут взаимно компенсироваться. Поэтому надо выбирать либо сумму абсолютных значений остатков, либо сумму квадратов остатков. По ряду причин удобнее всего выбрать минимизацию суммы квадратов остатков:
Пример
Дан набор точек (хi, yi): (0; 3), (1; 1), (2; 6), (3; 3), (4; 7). Найти коэффициенты m и b прямой линии y=mx+b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.
Решение
- В А2:В6 разместим координаты точек хi, yi.
- В С2:С6 вычислим mxi +b.
- В D2:D6 вычислим остатки yi-(mxi+b) и дадим этому блоку имя Остатки.
- В А9 и В9 поместим начальные значения коэффициентов m и b и дадим ячейкам имена.
- В D9 вычислим сумму квадратов остатков (для этого воспользуйтесь функцией СУММКВ(Остатки)). Проще воспользоваться формулой =СУММКВРАЗН(В2:В6,С2:С6) и тогда не надо использовать блок D2:D6. Но анализ остатков очень полезен, и их всегда надо вычислять.
|
|
А |
В |
С |
D |
|
1 |
xi |
yi |
mxi+b |
yi-(mxi+b) |
|
2 |
0 |
3 |
0 |
3 |
|
3 |
1 |
1 |
0 |
1 |
|
4 |
2 |
6 |
0 |
6 |
|
5 |
3 |
3 |
0 |
3 |
|
6 |
4 |
7 |
0 |
7 |
|
7 |
|
|
|
|
|
8 |
m |
b |
|
СУММКВ(Остатки) |
|
9 |
0 |
0 |
|
104 |
А теперь решим задачу оптимизации. Выделим ячейку D9, вызовем Решатель и поставим задачу минимизации D9 путем изменения А9:В9. Ограничений нет. Результат представлен ниже.
|
А |
В |
С |
D | |
|
1 |
xi |
yi |
mxi+b |
yi-(mxi+b) |
|
2 |
0 |
3 |
2 |
1 |
|
3 |
1 |
1 |
3 |
-2 |
|
4 |
2 |
6 |
4 |
2 |
|
5 |
3 |
3 |
5 |
-2 |
|
6 |
4 |
7 |
6 |
1 |
|
7 |
|
|
|
|
|
8 |
m |
b |
|
СУММКВ(Остатки) |
|
9 |
1 |
2 |
|
14 |