СТАТИСТИЧЕСКИЕ ФУНКЦИИ ДЛЯ ЦЕЛЕЙ ПРОГНОЗИРОВАНИЯ

Регрессионный анализ — имеет своей целью определение (идентификацию) уравнения регрессии, включая статистическую оценку его параметров. Уравнения регрессии позволяют найти среднее значение зависимой переменной, если величина независимой (или независимых) переменной известна.

Практически речь идет о том, чтобы, анализируя множество точек на графике, т.е. множество статистических данных, найти заключенную в этом множестве тенденцию — линию регрессии. Зная уравнение регрессии, можно использовать его для целей прогнозирования.

Предположим, что ряд X — это затраты труда некой фирмы в тысячах человекочасов, а ряд Y— выпуск продукции за период п. Пусть нам известно значение Xдля периода п + 1. Можно ли оценить (спрогнозировать) выпуск продукции для периода (п + 1)?

Допустим, что выпуск продукции зависит только от затрат труда некой фирмы и что зависимость междуХи Улинейна, т.е. имеет вид Y= а + ЪХ.

Нам также известны значения X я Y за период п и новое значение Z для периода (п + I). В этом случае мы можем найти расчетное значение Удля периода (п + 1), используя функцию ТЕНДЕНЦИЯ. ;

ТЕНДЕНЦИЯ (TREND). Назначение этой функции — вычисление нового (прогнозного) значения Y по одному или нескольким факторам.

Аргумент: изв зн У — известные значения ряда Уза период п;

изв_зн_Х — известные значения ряда (радов) X за период п;

нов_зн_Х — новое значение Xза период (п + 1); константа — если отсутствует или равна «ИСТИНА» (TRUE), то рассматривается зависимость Y= а + ЬХ, если константа есть «ЛОЖЬ» (FALSE), то Y= ЬХ.

Пример. Используем для расчетов данные, представленные на рис. 3.6.1. В нашем примере, если затраты труда некой фирмы в тысячах человекочасов в период (п + 1) составят 7,5, то выпуск продукции в этом периоде может ожидаться на уровне 9,006 тыс. ден. ед. (рис. 3.6.2).

Рис.

Однако функция ТЕНДЕНЦИЯ оставила без расшифровки значения а и Ь в уравнении Y = а + ЬХ, а также не вывела данных для статистического оценивания полученного результата.

Для нахождения свободного члена а и коэффициента b (для многофакторного уравнения — коэффициентов b_p Ь₂, ..., Ъ_п) используется функция ЛИНЕЙН.

ЛИНЕЙН (LINEST). Назначение этой функции — определение параметров одно- или многофакторного уравнения регрессии, включая их статистическую оценку.

Аргумент: изв_зн_У — известные значения ряда Yза период л;

изв_зн_Х — известные значения ряда (рядов) X за период л;

константа — если отсутствует или равна «ИСТИНА» (TRUE), то рассматривается зависимость Y= а + ЬХ; если константа есть «ЛОЖЬ» (FALSE), то Y= ЬХ; стат — если равна «ИСТИНА», то выводится таблица с данными для статистической оценки параметров уравнения регрессии.

Пример. Используем для расчетов исходные данные, представленные на рис. 3.6.1. Необходимо найти свободный член уравнения, его коэффициенты и показатели для их статистической оценки.

Для этого необходимо:

• выделить блок ячеек размером в 2 столбца на 5 строк (если факторов л, то блок должен быть размером (п + 1) на 5);

• вызвать функцию ЛИНЕЙН и задать ее аргументы (рис. 3.6.3);

• далее необходимо щелкнуть на клавише «Готово»;

Рис. 3.6.3. Пример задания аргументов функции ЛИНЕЙН

• перейти в режим редактирования (клавиша «F2»);

• завершить ввод, одновременно нажав клавиши «Ctrl»+«Shift»+ +«Enter».

В выделенном блоке появятся цифры:

Появившиеся цифры означают следующее:

Коэффициент детерминации г^л2 — это коэффициент корреляции в квадрате.

F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.

Степени свободы (df) полезны для нахождения /^-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.

В приведенной выше таблице показано, в каком порядке выводится дополнительная регрессионная статистика. Если уравнение регрессии многофакторное, то таблица будет расширена влево. В первой строке справа налево будут¹ выведены свободный член и коэффициенты b_v b_v ..., b_n.

Наше уравнение регрессии имеет вид У — 5,9 + 0,4Х

Стандартные ошибки параметров и стандартная ошибка уравнения невелики. Коэффициент детерминации близок к единице. Найденное значение jF-статистики необходимо сравнить со значением F- критическим для восьми степеней свободы, которое можно найти в справочнике по математической статистике.

Если в целом статистическая оценка параметров уравнения регрессии окажется удовлетворительной, то найденное уравнение регрессии можно использовать для целей прогнозирования.

ДИСП МЕДИАНА

Дисперсионный анализ МИН

Дисперсия РАНГ

ДИСПР Регрессионный анализ

KBАД РОТКЛ СРЗНАЧ

КОРРЕЛ СТАНДОТКЛОН

Корреляционный анализ СЧЕТ

Коэффициент корреляции СЧЕТЗ

ЛИНЕЙН ТЕНДЕНЦИЯ

МАКС Уравнение регрессии

Вопросы для самоконтроля

1. C помощью какой функции можно посчитать количество пятерок в заданном диапазоне?

2. Какие функции позволяют вычислить прогнозные значения по одному или нескольким факторам?

3. Какая функция позволяет определить порядок числа в заданном диапазоне?

4. Что такое дисперсия?

5. Какова разница между средним значением и медианой?

Задания для самостоятельной работы

Определите внешнеторговый оборот России за 2003 г., используя данные за 1992—2002 гг. и соответствующие функции Excel: ^{I. [44]}

СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

2.

3. Информатика: Учебник / Под ред. Н.В. Макаровой. — M.: Финансы и статистика, 2000. — С. 528—559.

4. СимволоковЛ.В. Решение бизнес-задач в Microsoft Office. — M.: БИНОМ, 2001. - С. 270-338.