Порядок выполнения:

1) В ячейку А1 введите название ВНП, в ячейку В1 – название Экспорт. В ячейки А2, А3, …, А21 введите данные первого столбца выбранного варианта задания, в ячейки В2, В3, …, В21 – данные второго столбца выбранного варианта.

Присвойте листу 1 название «Исходные данные».

2) На листе «Исходные данные» выполните следующие действия:

– на панели инструментов активизируется кнопка Мастер диаграмм (шаг 1 из 4), в одноименном диалоговом окне (рисунок 3.17) среди стандартных типов выбирается Точечная и верхний левый вид диаграммы и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 2 из 4), в котором во вкладке Диапазон данных в поле Диапазон вводится ссылка на диапазон ячеек A2:В21; нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 3 из 4), в котором во вкладке Заголовки в поле Ось Х(категорий) вводится название «ВНП», в поле Ось Y(значений) – название «Экспорт»; во вкладке Легенда снимается флажок Добавить легенду и нажимается кнопка Далее>;

– открывается диалоговое окно Мастер диаграмм (шаг 4 из 4) в поле имеющемся устанавливается флажок и нажимается кнопка Готово.

Рис. 3.17. Диалоговое окно «Мастер диаграмм (шаг 1 из 4)»

3) В меню Сервис выберите дополнение Анализ данных, в предложенных инструментах анализа выделите Ковариация, нажмите кнопку ОК. Установите значения параметров в появившемся диалоговом окне (рисунок 3.18) следующим образом:

Рис. 3.18. Диалоговое окно «Ковариация»

• Входной интервал – введите ссылки на ячейки А1:В21 (курсор установите в поле «Входной интервал», указатель мыши поместите в ячейку А1, удерживая нажатой левую клавишу, протяните указатель мыши до ячейки В21);

• Группирование – флажок по столбцам устанавливается автоматически;

• Метки в первой строке – установите флажок щелчком левой кнопки мышки;

• Параметры вывода – установите флажок на Новый рабочий лист, поставив курсор в поле напротив, введите название «Ковариация».

Нажмите ОК.

Вернитесь на лист «Исходные данные». В меню Сервис выберите опцию Анализ данных и выделите Корреляция. Установите в диалоговом окне (рисунок 3.19) следующим образом значения параметров:

Рис. 3.19. Диалоговое окно «Корреляция»

• Входной интервал – введите ссылки на ячейки, содержащие исходные данные А1:В21 (курсор установите в поле «Входной интервал», указатель мыши поместите в ячейку А1, удерживая нажатой левую клавишу, протяните указатель мыши до ячейки В21);

• Группирование – установите флажок по столбцам;

• Метки в первой строке – установите флажок;

• Параметры вывода – установите флажок на Новый рабочий лист, введите название «Корреляция».

Нажмите ОК.

Значение линейного коэффициента корреляции находится на листе «Корреляция» в ячейке В3.

4) Вернитесь на лист «Исходные данные». В меню Сервис выберите дополнение Анализ данных укажите Регрессия. Нажмите кнопку ОК. Установите в диалоговом окне (рисунок 3.20) следующим образом значения параметров:

• Входной интервал Y – введите ссылки на ячейки В1:В21;

• Входной интервал X – введите ссылки на ячейки А1:А21;

• Метки – установите флажок;

• Уровень надежности – установите флажок;

• Константа ноль – не активизируйте;

• Параметры вывода – установите флажок на Новый рабочий лист и в поле напротив введите имя «Регрессия»;

• Остатки – установите флажок;

• Стандартизованные остатки – оставьте пустым;

• График остатков – установите флажок;

• График подбора – установите флажок;

• График нормальной вероятности – оставьте пустым.

Нажмите ОК.

Рис. 3.20. Диалоговое окно «Регрессия»

Расположите диаграммы рядом (на поле диаграммы нажмите левую кнопку мыши, затем поместите курсор на белое поле и при нажатой левой кнопке передвигайте диаграмму вниз) и растяните их (на поле диаграммы нажмите левую кнопку мыши, нижнюю линию границы диаграммы при нажатой левой клавише протяните вниз).

Точечные оценки коэффициентов регрессии a и b находятся на листе «Регрессия» в ячейках В17 и В18 соответственно. Нижняя и верхняя границы доверительного интервала вычислены на листе «Регрессия» в ячейках F17 и G17 для коэффициента a и в ячейках F18 и G18 для коэффициента b.

5) Значение коэффициента детерминации находится на листе «Регрессия» в ячейке В5. Наблюдаемое значение F-критерия Фишера

находится на листе «Регрессия» в ячейке E12.

Вычислите критическое значение F_кр в свободной ячейке E15 следующим образом:

– нажмите на f_x (вставка функций);

– в поле Категория окна Мастер функций выберите статистические, из предложенных ниже функций выделите FРАСПОБР и нажмите ОК.

Откроется окно Аргументы функций. Заполните поля так:

• Вероятность – наберите значение 0,05;

• Степени свободы 1 – установите курсор в поле и выделите ячейку В12 столбца df таблицы «Дисперсионный анализ»;

• Степени свободы 2 – установите курсор в поле и выделите ячейку В13 столбца df таблицы «Дисперсионный анализ».

Нажмите ОК.

6) Значение стандартной ошибки регрессии находятся на листе «Регрессия» в ячейке В7.

7) Наблюдаемые значения -статистики коэффициентов регрессии a и b находятся на листе «Регрессия» в ячейках D17 и D18 соответственно.

Вычислите критическое значение t_кр в свободной ячейке D19 следующим образом:

– нажмите на f_x (вставка функций);

– в поле «Категория» окна Мастер функций выберите статистические, из предложенных ниже функций выделите СТЬЮДРАСПОБР и нажмите ОК. Откроется окно «Аргументы функций». Заполните поля:

• Вероятность – наберите значение 0,05;

• Степени свободы – введите 20–1–1, где 20 – число наблюдений, 1 – число факторов (x) в уравнении регрессии, 1 – число свободных членов ( ) в уравнении регрессии.

Нажмите ОК.

8) На листе регрессия в меню Сервис выберите Анализ данных, укажите Гистограмма. Нажмите кнопку ОК. Значения параметров в появившемся диалоговом окне (рисунок 3.21) установите следующим образом:

• Входной интервал – введите ссылки на ячейки С24:С44 (столбец Остатки с названием);

• Интервал карманов – не заполняйте;

• Метки – установите флажок;

• Выходной диапазон – введите ссылку на новый рабочий лист «Гистограмма»;

• Парето – оставьте пустым;

• Интегральный процент – оставьте пустым;

• Вывод графика – установите флажок.

Нажмите ОК. Растяните диаграмму вниз.

Рис. 3.21. Диалоговое окно «Гистограмма»

Вернитесь на лист «Регрессия». Выберите в опциях меню Сервис → Анализ данных → Описательная статистика, нажмите ОК. Значения параметров в диалоговом окне (рисунок 3.22) установите следующим образом:

Рис. 3.22. Диалоговое окно «Описательная статистика»

• Входной интервал – введите ссылки на ячейки С24:С44 (столбец Остатки с названием);

• Группирование – установите флажок по столбцам;

• Метки – установите флажок в первой строке;

• Выходной диапазон – установите флажок на Новый рабочий лист и в поле напротив введите название «Статистика остатков»;

• установите флажки Итоговая статистика, уровень надежности (95%).

Нажмите ОК.

9) Вернитесь на лист «Регрессия» и в пустой ячейке E22 листа введите формулу

= В17+В18*2500 – точечный прогноз.

На листе «Регрессия» в пустой ячейке E23 вычислите значение по формуле , где – стандартная ошибка регрессии.

В пустых ячейках E24 и F24 введите формулы

=E22–D19*E23 – левый конец интервала прогноза;

=E22+D19*E23 – правый конец интервала прогноза.

Приложение: Отчет о результатах вычислений и анализа