Порядок выполнения работы. 1. В ячейку А1 введите ВНП, в ячейку В1 — Экспорт

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

Переименуйте лист 1 в «Исходные данные», щелкнув правой кнопкой мыши на названии «Лист 1» и выбрав опцию переименование или двойным щелчком левой кнопкой мыши в поле «Лист 1». Сохраните открытый файл «Книга 1» под названием «Фамилия - регрессия» в папке «Мои документы» (Файл – Сохранить как – Папка Мои документы – Имя файла Фамилия – регрессия – Сохранить).

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

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

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

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

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

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

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

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

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

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

Нажмите ОК.

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

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

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

· Метки — установите флажок;

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

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

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

· Остатки — установите флажок;

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

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

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

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

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

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

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

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

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

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

· Степени свободы 2 — установите курсор в поле и выделите ячейку В13 столбца df таблицы «Дисперсионный анализ». Нажмите «ОК».

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

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

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

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

· Степени свободы — введите 20-1-1, где 20 — число наблюдений, 1 — число факторов (x) в уравнении регрессии, 1 — число свободных членов (b₀) в уравнении регрессии. ОК.

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

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

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

· Метки — установите флажок;

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

· Парето — оставьте пустым;

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

· Вывод графика — установите флажок. Нажмите «ОК». Растяните диаграмму вниз.

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

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

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

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

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

· установите флажки Итоговая статистика, уровень надежности (95%). Нажмите«ОК».

9. Откройте новый лист и переименуйте его в «Критерий Пирсона». Скопируйте данные столбца Карман и Частоты вместе с названием из листа «Гистограмма остатков», начиная с ячейки А1.

Удалите Еще и в этой же ячейке введите формулу =3*В13, где ячейку В13 (значение максимума) выделите на листе Числовые характеристики, нажмите Enter (увеличили максимум в три раза).

В ячейки В8, В9, В10 скопируйте значения Среднее, Стандартное отклонение и Счет из листа Числовые характеристики остатков соответственно.

В ячейку С1 введите значение 0.

В ячейки С2:Сn (n – номер ячейки Еще) введите формулу массива: 1) выделите ячейки С2:Cn; 2) нажмите клавишу F2; 3) в ячейку С2 введите формулу (лучше через Мастер функций – статистические)

=HOPMPACП(А2:Аn;В8;В9;ИСТИНА)

и нажмите комбинацию клавиш Ctrl+Shift+Enter (одновременно). В строке формул будет запись

{=HOPMPACП(А2:Аn;В8;В9;ИСТИНА)}

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

В ячейки D2:Dn введите формулу массива {=С2:Cn-С1:C(n-1)} (во втором массиве ячейку Cn не включать).

В ячейки Е2:En введите формулу массива {=В10*D2:Dn}.

В ячейки F2:Fn введите формулу массива {=(B2:Вn-Е2:Еn)^2/Е2:En}.

В ячейку В11 введите формулу =СУММ(F2:Fn) (для вычисления хи-квадрат набл).

В ячейку В12 введите формулу (используя Мастер функций – статистические) =ХИ2ОБР(0,05; k-2-1), где k =количество значений в кармане плюс 1 (число интервалов для вычисления хи-квадрат кр).

10. Вернитесь на лист «Регрессия» и в пустой ячейке С52 листа введите формулу

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

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

=С52-С50*В7 — левый конец интервала прогноза;

=С52+С50*В7 — правый конец интервала прогноза.

Варианты заданий