Метод наименьших квадратов и поиск решения в Excel. Применение метода наименьших квадратов в Excel Расчет методом наименьших квадратов xls

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

Как включить надстройку «поиск решения»

Для начала разберемся, как эту надстройку включить.

1. Идем в меню «Файл» и выбираем пункт «Параметры Excel»

2. В появившемся окне выбираем «Поиск решения» и нажимаем «перейти».

3. В следующем окне ставим галочку напротив пункта «поиск решения» и нажимаем «ОК».

4. Надстройка активирована — теперь ее можно найти в пункте меню «Данные».

Метод наименьших квадратов

Теперь вкратце о методе наименьших квадратов (МНК) и о том, где его можно применять.

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

Мы хотим это влияние описать математически, чтобы потом этой формулой пользоваться и знать, что, если мы поменяем величину Х на столько-то, получим величину Y такую-то...

Возьму супер-простой пример (см. рис.).

Ежу понятно, что точки расположились друг за другом как будто по прямой, а потому мы смело предполагаем, что наша зависимость описывается линейной функцией y=kx+b. При этом мы точно уверены, что при X равном нулю значение Y тоже равно нулю. Значит, функция, описывающая зависимость, будет еще проще: y=kx (вспоминаем школьную программу).

В общем, нам предстоит найти коэффициент k. Вот это мы и сделаем с помощью МНК с применением надстройки «поиск решения».

Метод заключается в том, чтобы (здесь — внимание: нужно вдуматься) сумма квадратов разностей экспериментально полученных и соответствующих расчетных значений была минимальной. То есть когда X1=1 реально измеренное значение Y1=4,6, а расчетное y1=f (x1) равно 4, квадрат разности будет (y1-Y1)^2=(4-4,6)^2=0,36. Со следующими так же: когда X2=2, реально измеренное значение Y2=8,1, а расчетное у2 равно 8, квадрат разности будет (y2-Y2)^2=(8-8,1)^2=0,01. И сумма всех этих квадратов должна быть минимально возможной.

Итак, приступим к тренировке по использованию МНК и надстройки Excel «поиск решения» .

Применение надстройки поиск решения

1. Если не включили надстройку «поиск решения», то возвращаемся к пункту Как включить надстройку «поиск решения» и включаем 🙂

2. В ячейку А1 введем значение «1». Эта единица будет первым приближением к реальному значению коэффициента (k) нашей функциональной зависимости y=kx.

3. В столбце B у нас расположились значения параметра X, в столбце C — значения параметра Y. В ячейках столбца D вводим формулу: «коэффициент k умножить на значение Х». Например, в ячейке D1 вводим «=A1*B1», в ячейке D2 вводим "=A1*B2" и т.д.

4. Мы считаем, что коэффициент к равен единице и функция f (x)=у=1*х – это первое приближение к нашему решению. Можем рассчитать сумму квадратов разностей между измеренными значениями величины Y и рассчитанными по формуле y=1*х. Можем все это сделать вручную, вбивая в формулу соответствующие ссылки на ячейки: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... и т.д. В конце концов ошибаемся и понимаем, что потеряли кучу времени. В Excel для расчета суммы квадратов разностей есть специальная формула, «СУММКВРАЗН», которая все за нас и сделает. Введем ее в ячейку А2 и зададим исходные данные: диапазон измеренных значений Y (столбец C) и диапазон рассчитанных значений Y (столбец D).

4. Сумму разностей квадратов рассчитали – теперь идем во вкладку «Данные» и выбираем «Поиск решения».

5. В появившемся меню в качестве изменяемой ячейки выбираем ячейку A1 (та, что с коэффициентом k).

6. В качестве целевой выбираем ячейку A2 и задаем условие «установить равной минимальному значению». Помним, что это ячейка, где у нас производится расчёт суммы квадратов разностей расчетного и измеренного значений, и сумма эта должна быть минимальной. Нажимаем «выполнить».

7. Коэффициент k подобран. Теперь можно убедиться, что рассчитанные значения теперь очень близки к измеренным.

P.S.

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

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

Метод наименьших квадратов используется для оценки параметров уравнение регрессии.

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

выбор формы связи (вида аналитического уравнения регрессии);
оценку параметров уравнения;
оценку качества аналитического уравнения регрессии.

Наиболее часто для описания статистической связи признаков используется линейная форма. Внимание к линейной связи объясняется четкой экономической интерпретацией ее параметров, ограниченной вариацией переменных и тем, что в большинстве случаев нелинейные формы связи для выполнения расчетов преобразуют (путем логарифмирования или замены переменных) в линейную форму.
В случае линейной парной связи уравнение регрессии примет вид: y i =a+b·x i +u i . Параметры данного уравнения а и b оцениваются по данным статистического наблюдения x и y . Результатом такой оценки является уравнение: , где , - оценки параметров a и b , - значение результативного признака (переменной), полученное по уравнению регрессии (расчетное значение).

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

Задача оценивания параметров линейного парного уравнения методом наименьших квадратов состоит в следующем: получить такие оценки параметров , , при которых сумма квадратов отклонений фактических значений результативного признака - y i от расчетных значений – минимальна.
Формально критерий МНК можно записать так: .

Классификация методов наименьших квадратов

Метод наименьших квадратов.
Метод максимального правдоподобия (для нормальной классической линейной модели регрессии постулируется нормальность регрессионных остатков).
Обобщенный метод наименьших квадратов ОМНК применяется в случае автокорреляции ошибок и в случае гетероскедастичности.
Метод взвешенных наименьших квадратов (частный случай ОМНК с гетероскедастичными остатками).

Проиллюстрируем суть классического метода наименьших квадратов графически . Для этого построим точечный график по данным наблюдений (x i , y i , i=1;n) в прямоугольной системе координат (такой точечный график называют корреляционным полем). Попытаемся подобрать прямую линию, которая ближе всего расположена к точкам корреляционного поля. Согласно методу наименьших квадратов линия выбирается так, чтобы сумма квадратов расстояний по вертикали между точками корреляционного поля и этой линией была бы минимальной.

Математическая запись данной задачи: .
Значения y i и x i =1...n нам известны, это данные наблюдений. В функции S они представляют собой константы. Переменными в данной функции являются искомые оценки параметров - , . Чтобы найти минимум функции 2-ух переменных необходимо вычислить частные производные данной функции по каждому из параметров и приравнять их нулю, т.е. .
В результате получим систему из 2-ух нормальных линейных уравнений:
Решая данную систему, найдем искомые оценки параметров:

Правильность расчета параметров уравнения регрессии может быть проверена сравнением сумм (возможно некоторое расхождение из-за округления расчетов).
Для расчета оценок параметров , можно построить таблицу 1.
Знак коэффициента регрессии b указывает направление связи (если b >0, связь прямая, если b <0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формально значение параметра а – среднее значение y при х равном нулю. Если признак-фактор не имеет и не может иметь нулевого значения, то вышеуказанная трактовка параметра а не имеет смысла.

Оценка тесноты связи между признаками осуществляется с помощью коэффициента линейной парной корреляции - r x,y . Он может быть рассчитан по формуле: . Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: .
Область допустимых значений линейного коэффициента парной корреляции от –1 до +1. Знак коэффициента корреляции указывает направление связи. Если r x, y >0, то связь прямая; если r x, y <0, то связь обратная.
Если данный коэффициент по модулю близок к единице, то связь между признаками может быть интерпретирована как довольно тесная линейная. Если его модуль равен единице ê r x , y ê =1, то связь между признаками функциональная линейная. Если признаки х и y линейно независимы, то r x,y близок к 0.
Для расчета r x,y можно использовать также таблицу 1.

Для оценки качества полученного уравнения регрессии рассчитывают теоретический коэффициент детерминации – R 2 yx:

,
где d 2 – объясненная уравнением регрессии дисперсия y ;
e 2 - остаточная (необъясненная уравнением регрессии) дисперсия y ;
s 2 y - общая (полная) дисперсия y .
Коэффициент детерминации характеризует долю вариации (дисперсии) результативного признака y , объясняемую регрессией (а, следовательно, и фактором х), в общей вариации (дисперсии) y . Коэффициент детерминации R 2 yx принимает значения от 0 до 1. Соответственно величина 1-R 2 yx характеризует долю дисперсии y , вызванную влиянием прочих неучтенных в модели факторов и ошибками спецификации.
При парной линейной регрессии R 2 yx =r 2 yx .

4.1. Использование встроенных функций

Вычисление коэффициентов регрессии осуществляется с помощью функции

ЛИНЕЙН (Значения_y ; Значения_x ; Конст ; статистика ),

Значения_y - массив значений y,

Значения_x - необязательный массив значений x , если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y ,

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y=ax.

Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент Статистика имеет значение ИСТИНА , то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент Статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b .

Необходимо помнить, что результатом функций ЛИНЕЙН() является множество значений – массив.

Для расчета коэффициента корреляции используется функция

КОРРЕЛ (Массив1 ;Массив2 ),

возвращающая значения коэффициента корреляции, где Массив1 - массив значений y , Массив2 - массив значений x . Массив1 и Массив2 должны быть одной размерности.

ПРИМЕР 1 . Зависимость y (x ) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции .

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 2.

Для того чтобы рассчитать значения коэффициентов регрессии а и b выделимячейки A7:B7, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН . Заполним появившееся диалоговое окно так, как показано на рис. 3 и нажмем ОK .

В результате вычисленное значение появится только в ячейке A6 (рис.4). Для того чтобы значение появилось и в ячейке B6 необходимо войти в режим редактирования (клавиша F2) , а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER .

Для расчета значения коэффициента корреляции в ячейку С6 была введена следующая формула:

С7=КОРРЕЛ(B3:J3;B2:J2) .

Зная коэффициенты регрессии а и b вычислим значения функции y =ax +b для заданных x . Для этого введем формулу

B5=$A$7*B2+$B$7

и скопируем ее в диапазон С5:J5 (рис. 5).

Изобразим линию регрессии на диаграмме. Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные . В появившемся диалоговом окне (рис. 5) выберем вкладку Ряд и щелкнем по кнопке Добавить . Заполним поля ввода, так как показано на рис. 6 и нажмем кнопку ОК . К графику экспериментальных данных будет добавлена линия регрессии. По умолчанию ее график будет изображен в виде точек, не соединенных сглаживающими линиями.

Рис. 6

Чтобы изменить вид линии регрессии, выполним следующие действия. Щелкнем правой кнопкой мыши по точкам, изображающим график линии, выберем команду Тип диаграммы и установим вид точечной диаграммы, так как показано на рис. 7.

Тип линии, ее цвет и толщину можно изменить следующим образом. Выделить линию на диаграмме, нажать правую кнопку мыши и в контекстном меню выбрать команду Формат рядов данных… Далее сделать установки, например, так как показано на рис. 8.

В результате всех преобразований получим график экспериментальных данных и линию регрессии в одной графической области (рис. 9).

4.2. Использование линии тренда.

Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы – линия тренда .

ПРИМЕР 2 . В результате эксперимента была определена некоторая табличная зависимость.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

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

Решение задачи можно разбить на следующие этапы: ввод исходных данных, построение точечного графика и добавление к этому графику линии тренда.

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

Появившееся диалоговое окно позволяет построить аппроксимирующую зависимость.

На первой вкладке (рис. 11) этого окна указывается вид аппроксимирующей зависимости.

На второй (рис. 12) определяются параметры построения:

· название аппроксимирующей зависимости;

· прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда);

· показывать ли точку пересечения кривой с прямой y=const ;

· показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме);

· помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

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

Аналогично с помощью линии тренда можно подобрать параметры таких зависимостей как

· линейная y =a∙x +b ,

· логарифмическая y =a∙ln (x )+b ,

· экспоненциальная y =a∙e b ,

· степенная y =a∙x b ,

· полиномиальная y =a∙x 2 +b∙x +c , y =a∙x 3 +b∙x 2 +c∙x+d и так далее, до полинома 6-й степени включительно,

· линейная фильтрация.

4.3. Использование решающего блока

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

ПРИМЕР 3 . В результате эксперимента получена зависимость z(t) представленная в таблице

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Подобрать коэффициенты зависимости Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных

Рассмотрим процесс решения задачи оптимизации (рис. 14).

Пусть значения А , В , С , D и К хранятся в ячейках A7:E7 . Рассчитаем теоретические значения функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K для заданных t (B2:J2 ). Для этого в ячейку B4 введем значение функции в первой точке (ячейка B2 ):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7 .

Скопируем эту формулу в диапазон С4:J4 и получим ожидаемое значение функции в точках, абсциссы которых хранится в ячейках B2:J2 .

В ячейку B5 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

B5=(B4-B3)^2,

и скопируем ее в диапазон С5:J5 . В ячейке F7 будем хранить суммарную квадратичную ошибку (10). Для этого введем формулу:

F7 = СУММ(B5:J5) .

Воспользуемся командой Сервис®Поиск решения и решим задачу оптимизации без ограничений. Заполним соответствующим образом поля ввода в диалоговом окне, показанном на рис. 14 и нажмем кнопку Выполнить . Если решение будет найдено, то появится окно, изображенное на рис. 15.

Результатом работы решающего блока будет вывод в ячейки A7:E7 значений параметров функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K . В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка .

Изобразить экспериментальные точки и подобранную линию в одной графической области можно, если выделить диапазон B2:J4 , вызвать Мастер диаграмм , а затем отформатировать внешний вид полученных графиков.

Рис. 17 отображает рабочий лист MS Excel после проведенных вычислений.

5. СПИСОК ЛИТЕРАТУРЫ

1. Алексеев Е.Р., Чеснокова О.В., Решение задач вычислительной математики в пакетах Mathcad12, MATLAB7, Maple9. – НТ Пресс, 2006.–596с. :ил. –(Самоучитель)

2. Алексеев Е.Р., Чеснокова О.В., Е.А. Рудченко, Scilab, решение инженерных и математических задач. –М., БИНОМ, 2008.–260с.

3. Березин И.С., Жидков Н.П., Методы вычислений.–М.:Наука, 1966.–632с.

4. Гарнаев А.Ю., Использование MS EXCEL и VBA в экономике и финансах. – СПб.: БХВ - Петербург, 1999.–332с.

5. Демидович Б.П., Марон И А., Шувалова В.З., Численные методы анализа.–М.:Наука, 1967.–368с.

6. Корн Г., Корн Т., Справочник по математике для научных работников и инженеров.–М., 1970, 720с.

7. Алексеев Е.Р., Чеснокова О.В. Методические указания к выполнению лабораторных работ в MS EXCEL. Для студентов всех специальностей. Донецк, ДонНТУ, 2004. 112 с.

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

Постановка задачи на конкретном примере

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

Итак, пусть X — торговая площадь продовольственного магазина, измеряемая в квадратных метрах, а Y — годовой товарооборот, определяемый в миллионах рублей.

Требуется сделать прогноз, какой товарооборот (Y) будет у магазина, если у него та или иная торговая площадь. Очевидно, что функция Y = f (X) возрастающая, так как гипермаркет продает больше товаров, чем ларек.

Несколько слов о корректности исходных данных, используемых для предсказания

Допустим, у нас есть таблица, построенная по данным для n магазинов.

Согласно математической статистике, результаты будут более-менее корректными, если исследуются данные по хотя бы 5-6 объектам. Кроме того, нельзя использовать «аномальные» результаты. В частности, элитный небольшой бутик может иметь товарооборот в разы больший, чем товарооборот больших торговых точек класса «масмаркет».

Суть метода

Данные таблицы можно изобразить на декартовой плоскости в виде точек M 1 (x 1 , y 1), … M n (x n , y n). Теперь решение задачи сведется к подбору аппроксимирующей функции y = f (x), имеющей график, проходящий как можно ближе к точкам M 1, M 2, .. M n .

Конечно, можно использовать многочлен высокой степени, но такой вариант не только труднореализуем, но и просто некорректен, так как не будет отражать основную тенденцию, которую и нужно обнаружить. Самым разумным решением является поиск прямой у = ax + b, которая лучше всего приближает экспериментальные данные, a точнее, коэффициентов - a и b.

Оценка точности

При любой аппроксимации особую важность приобретает оценка ее точности. Обозначим через e i разность (отклонение) между функциональными и экспериментальными значениями для точки x i , т. е. e i = y i - f (x i).

Очевидно, что для оценки точности аппроксимации можно использовать сумму отклонений, т. е. при выборе прямой для приближенного представления зависимости X от Y нужно отдавать предпочтение той, у которой наименьшее значение суммы e i во всех рассматриваемых точках. Однако, не все так просто, так как наряду с положительными отклонениями практически будут присутствовать и отрицательные.

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

Метод наименьших квадратов

В Excel, как известно, существует встроенная функция автосуммы, позволяющая вычислить значения всех значений, расположенных в выделенном диапазоне. Таким образом, ничто не помешает нам рассчитать значение выражения (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

В математической записи это имеет вид:

Так как изначально было принято решение об аппроксимировании с помощью прямой, то имеем:

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

Для этого требуется приравнять к нулю частные производные по новым переменным a и b, и решить примитивную систему, состоящую из двух уравнений с 2-мя неизвестными вида:

После нехитрых преобразований, включая деление на 2 и манипуляции с суммами, получим:

Решая ее, например, методом Крамера, получаем стационарную точку с некими коэффициентами a * и b * . Это и есть минимум, т. е. для предсказания, какой товарооборот будет у магазина при определенной площади, подойдет прямая y = a * x + b * , представляющая собой регрессионную модель для примера, о котором идет речь. Конечно, она не позволит найти точный результат, но поможет получить представление о том, окупится ли покупка в кредит магазина конкретной площади.

Как реализоавать метод наименьших квадратов в Excel

В "Эксель" имеется функция для расчета значения по МНК. Она имеет следующий вид: «ТЕНДЕНЦИЯ» (известн. значения Y; известн. значения X; новые значения X; конст.). Применим формулу расчета МНК в Excel к нашей таблице.

Для этого в ячейку, в которой должен быть отображен результат расчета по методу наименьших квадратов в Excel, введем знак «=» и выберем функцию «ТЕНДЕНЦИЯ». В раскрывшемся окне заполним соответствующие поля, выделяя:

диапазон известных значений для Y (в данном случае данные для товарооборота);
диапазон x 1 , …x n , т. е. величины торговых площадей;
и известные, и неизвестные значения x, для которого нужно выяснить размер товарооборота (информацию об их расположении на рабочем листе см. далее).

Кроме того, в формуле присутствует логическая переменная «Конст». Если ввести в соответствующее ей поле 1, то это будет означать, что следует осуществить вычисления, считая, что b = 0.

Если нужно узнать прогноз для более чем одного значения x, то после ввода формулы следует нажать не на «Ввод», а нужно набрать на клавиатуре комбинацию «Shift» + «Control»+ «Enter» («Ввод»).

Некоторые особенности

Регрессионный анализ может быть доступен даже чайникам. Формула Excel для предсказания значения массива неизвестных переменных — «ТЕНДЕНЦИЯ» — может использоваться даже теми, кто никогда не слышал о методе наименьших квадратов. Достаточно просто знать некоторые особенности ее работы. В частности:

Если расположить диапазон известных значений переменной y в одной строке или столбце, то каждая строка (столбец) с известными значениями x будет восприниматься программой в качестве отдельной переменной.
Если в окне «ТЕНДЕНЦИЯ» не указан диапазон с известными x, то в случае использования функции в Excel программа будет рассматривать его как массив, состоящий из целых чисел, количество которых соответствует диапазону с заданными значениями переменной y.
Чтобы получить на выходе массив «предсказанных» значений, выражение для вычисления тенденции нужно вводить как формулу массива.
Если не указаны новые значения x, то функция «ТЕНДЕНЦИЯ» считает их равным известным. Если и они не заданы, то в качестве аргумента берется массив 1; 2; 3; 4;…, который соразмерен диапазону с уже заданными параметрами y.
Диапазон, содержащий новые значения x должен состоять из такого же или большего количества строк или столбцов, как диапазон с заданными значениями y. Иными словами он должен быть соразмерным независимым переменным.
В массиве с известными значениями x может содержаться несколько переменных. Однако если речь идет лишь об одной, то требуется, чтобы диапазоны с заданными значениями x и y были соразмерны. В случае нескольких переменных нужно, чтобы диапазон с заданными значениями y вмещался в одном столбце или в одной строке.

Функция «ПРЕДСКАЗ»

Реализуется с помощью нескольких функций. Одна из них называется «ПРЕДСКАЗ». Она аналогична «ТЕНДЕНЦИИ», т. е. выдает результат вычислений по методу наименьших квадратов. Однако только для одного X, для которого неизвестно значение Y.

Теперь вы знаете формулы в Excel для чайников, позволяющие спрогнозировать величину будущего значения того или иного показателя согласно линейному тренду.