Решение. 1. Введите данные в рабочую таблицу: стоимость пачки порошка — в диапазон А1:А8; курс доллара в диапазон В1:В8 (заметим

1. Введите данные в рабочую таблицу: стоимость пачки порошка — в диапазон А1: А8; курс доллара в диапазон В1: В8 (заметим, что знаку запятой, отделяющей целую часть от дробной, соответствует «запятая»),

2. В пункте меню Сервис выберите строку Анализ данных и далее укажите курсором мыши на строку Регрессия.

3. В появившемся диалоговом окне (рис. 6.23) задайте Входной интервал Y. Для этого наведите указатель мыши на верхнюю ячейку столбца зависимых данных (А1), нажмите левую кнопку мыши и, не отпуская ее, протяните указатель мыши к нижней ячейке (А8), затем отпустите левую кнопку мыши. (Обратите внимание, что зависимые данные — это те данные, которые предполагается вычислять.)

4. Так же укажите Входной интервал X, то есть введите ссылку па диапазон независимых данных В1: В8. (Независимые данные — это те данные, которые будут измеряться или наблюдаться.)

5. Установите флажок в поле График подбора.

6. Далее укажите выходной диапазон. Для этого поставьте переключатель в положение Выходной интервал (наведите указатель мыши и щелкните левой кнопкой), затем наведите указатель мыши на правое поле ввода Выходной интервал и, щелкнув левой кнопкой мыши, указатель мыши наведите на левую верхнюю ячейку выходного диапазона (С1). Щелкните левой кнопкой мыши (рис. 6.23). Нажмите кнопку ОК.

Результаты анализа. В выходном диапазоне появятся следующие результаты и график подбора (рис. 6.24).

Рис. 6.24. Результаты анализа и график соответствия экспериментальных точек и предсказанных по регрессионной модели из примера 6.15

Интерпретация результатов. В таблице Дисперсионный анализ оценивается общее качество полученной модели: ее достоверность по уровню значимости критерия Фишера — р, который должен быть меньше, чем 0, 05 (строка Регрессия, столбец Значимость F, в примере- 1, 58Е-07 (0, 000000158), то есть р = 0, 000000158 и модель значима) и степень точности описания моделью процесса — Д-квадрат (вторая строка сверху в таблице Регрессионная статистика, в примере R-квадрат = 0, 992). Поскольку R-квадрат > 0, 95, можно говорить о высокой точности аппроксимации (модель хорошо описывает явление (рис. 6.24)).

Далее необходимо определить значения коэффициентов модели. Они определяются из таблицы в столбце Коэффициенты — в строке Y-пересечение приводится свободный член; в строках соответствующих переменных приводятся значения коэффициентов при этих переменных. В столбце р-значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда р > 0, 05, коэффициент может считаться нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную и коэффициент может быть убран из уравнения.

Отсюда выражение для определения стоимости пачки порошка в рублях будет иметь следующий вид: -0, 83 + 0, 847*(Курс доллара, руб./USD).

Полученная модель с высокой точностью позволяет определять стоимость пачки стирального порошка (R² = 99, 2%).

Воспользовавшись полученным уравнением, можно рассчитать ожидаемую стоимость пачки стирального порошка при изменениях курса доллара. Например, для расчета при курсе доллара 35 руб./USD необходимо поставить табличный курсор в любую свободную ячейку (А10); ввести с клавиатуры знак =, щелкнуть указателем мыши по ячейке D17, ввести с клавиатуры знак +, щелкнуть по ячейке D18, ввести с клавиатуры знак * и число 35. В результате в ячейке А10 будет получена ожидаемая стоимость пачки порошка — 28, 8 руб. >

Пример 6.16. Построить регрессионную модель для предсказания изменений уровня заболеваемости органов дыхания (Y) в зависимости от содержания в воздухе двуокиси углерода (X₁) и степени запыленности (Х₂). В таблице приведены данные наблюдений в течение 29 месяцев.