Пример 8.

Рассмотрим пример оценки прогноза стоимости квартир с определением ошибки прогноза. Данные стоимости квартир приведены в табл.6.10.

Таблица 6.10

Исходные данные для примера 8

Вычислим прогнозную стоимость квартир на декабрь месяц. Для этого используем функцию ПРЕДСКАЗ, которая имеет следующий синтаксис:

ПРЕДСКАЗ (x; известные_значения_ y; известные_значения_ x)

x — это точка данных, для которой предсказывается значение (в примере x = 12);

известные_значения_y — это зависимый массив или интервал данных (в примере это стоимости квартир);

известные_значения_x — это независимый массив или интервал данных (в примере это порядковые номера месяцев).

Итак, функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y- значение, соответствующее заданному x- значению. Известные значения — это x - и y -значения, а новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для предсказания будущих продаж, потребностей в оборудовании или тенденций потребления.

В табл.6.11 приведен результат реализации функции ПРЕДСКАЗ по определению стоимости квартир в декабре месяце.

Таблица 6.11

Результат прогноза функции ПРЕДСКАЗ

Стандартная ошибка полученного результата определится с помощью функции СТОШYX, которая имеет следующий синтаксис:

СТОШYX (известные_значения_ y; известные_значения_ x)

Результат расчета ошибки следующий:

Стандартная ошибка оценки стоимости квартиры,

53, 64

Далее выполняется анализ полученного результата, который заключается в том, что оценивается вероятность прогноза. Из статистики известно, что ожидаемый прогноз с вероятностью 68, 3% попадает в диапазон , с вероятностью 95, 5% - в диапазон и с вероятностью 99, 7% в диапазон . В примере, таким образом, имеем, что стоимость квартиры в декабре попадет в диапазон 11172 ± 54 у.е. с вероятностью 68, 3%, т.е. стоимость квартиры составит от 11118 у.е. до 11226 у.е. С вероятностью 95, 5% стоимость квартиры будет в диапазоне от 11064 у.е. до 11280 у.е.

Регрессия ( линейная )

Регрессионный анализ заключается в определении аналитического выражения связи зависимой случайной величины Y (называемой результативным признаком) с независимыми случайными величинами X₁, X₂, …, X_m (называемые факторами). Форма связи результативного признака Y с факторами X₁, X₂, …, X_m получила название уравнения регрессии. Различают линейную и нелинейную (квадратичная, экспоненциальная, логарифмическая и т.д.), парную и множественную (многофакторную) регрессии.

В Excel используется ряд функций, связанных с регрессионным анализом.

Функция ЛИНЕЙН рассчитывает массив данных, описывающих уравнение линейной парной регрессии на основе метода наименьших квадратов. Синтаксис функции следующий:

ЛИНЕЙН (известные_значения_ y; известные_значения_ x; конст; статистика).

Аргументы имеют следующее значение:

- известные_значения_ y – множество значений результативного признака Y;

- известные_значения_ x - множество значений факторных признаков Xi;

- конст – логическое значение, которое указывает, требуется ли, чтобы свободный член a₀ был равен 0;

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

Функция ЛИНЕЙН находит коэффициенты для линейной регрессии . Порядок вычисления следующий:

- вводятся исходные данные или открывается существующий файл, содержащий анализируемые данные;

- выделяется область пустых ячеек (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1Í 2 – для получения только оценок коэффициентов регрессии;

- активизируется инструмент «Мастер функций» и заполняются аргументы функции ЛИНЕЙН.

В левой верхней ячейки выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, надо нажать на клавишу F2, а затем ‑ на комбинацию клавиш Ctrl+Shift+Enter. Регрессионная статистика для парной регрессии выводится в следующем порядке, приведенном в табл.6.12.

Таблица 6.12

Порядок вывода регрессионной статистики при использовании функции ЛИНЕЙН

Значение коэффициента	Значение коэффициента
Среднеквадратическое отклонение	Среднеквадратическое отклонение
Коэффициент детерминации R2	Среднеквадратическое отклонение
F - статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Инструмент РЕГРЕССИЯ находит коэффициенты для уравнения прямой линии (регрессионного линейного уравнения). Это уравнение имеет вид

,

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

Для его использования должен быть установлен «Пакет анализа», через действие «Сервис\Надстройки…». В открывшемся окне необходимо отметить опцию «Пакет анализа». После такой активации в меню «Сервис» появится действие «Анализ данных», в котором можно найти механизм РЕГРЕССИЯ, предназначенный для множественной регрессии. Окно этого режима приведено на рис.6.13.

Рис.6.13. Окно режима «Регрессия», вызываемого действием Сервис\Пакет анализа…\Регрессия

Входной интервал Y окна «Регрессия» на рис.6.13 содержит данные по результативному признаку. Этот диапазон должен состоять из одного столбца. Входной интервал X содержит факторные признаки. Максимальное число входных диапазонов (столбцов) равно 16. Опцию «Метка» делают активной в том случае, если при выделении входного и выходного интервалов выделяются не только цифровые данные, но и наименования столбцов