1. Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Þ Программы Þ Microsoft Excel. | ||
2. Введем в ячейки данные задачи как указано на рис. 1. 3. В столбце C рассчитаем значение f(x) = ax+b. Для этого в ячейку C2 введем формулу: = $B$16*A2+$B$17 Введем формулу методом автозаполнения по столбцу. 4. В столбце D рассчитаем остатки. Для этого в ячейку D2 введем формулу: = B2-C2 Введем формулу методом автозаполнения по столбцу. 5. В ячейке D16 подсчитаем сумму квадратов остатков. Для этого в ячейку D16 введем формулу: = СУММКВ(D2:D14) | рис. 1 | |
6. Далее решим задачу оптимизации. Целевая функция – ячейка D16, которую следует минимизировать путем изменений a и b без ограничений. Решение задачи оптимизации см. на рис. 2. Целевая ячейка D16 – стремится к минимуму. Изменяемые ячейки В16 и В17 (коэффициенты а и b). Ограничений нет. Функция прямой будет иметь вид: f(x)=ax+b=0,1x-185,207 | рис. 2 | |
7. Построим диаграмму с исходными данными и приближающим их линейным графиком f(x) = ax+b. Для этого выполним нижеуказанную последовательность шагов.
|
|
Нажмем кнопку Мастер диаграмм .
Шаг 1. Выбор типа и вида диаграммы.
ü Во вкладке Стандартные выберем Тип – Точечная, вид – Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.
ü Прочтем описание выбранного вида диаграммы.
ü Нажмем кнопку Далее>.
Шаг 2. Источник данных диаграммы.
ü Во вкладке Диапазон данных активизируем опцию Ряды в столбцах.
ü Переключимся на вкладку Ряд.
ü Щелкнем по кнопке Добавить.
ü В поле Имя введем текст - Реальные данные.
ü В поле Значения X нажмем кнопку для возврата в таблицу и выделим там диапазон с годами, т.е. диапазон A2:A14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.
ü В поле Значения Y нажмем кнопку для возврата в таблицу и выделим там диапазон B2:B14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.
ü Щелкнем по кнопке Добавить для добавления второго ряда данных.
ü В поле Имя введем текст - Расчетные данные.
ü В поле Значения X укажем диапазон A2:A14.
ü В поле Значения Y диапазон С2:С14.
ü Нажмем кнопку Далее>.
Шаг 3. Параметры диаграммы.
ü Введем с клавиатуры Название диаграммы – Урожайность зерновых в СССР в 1922-1934 гг. ü Введем с клавиатуры Ось X (категорий) – год ü Введите с клавиатуры Ось Y (значений) – ц/га ü Переключимся на вкладку Линии сетки и активизируем опции, как указано на рис. 3. Переключимся на вкладку Легенда и активизируем опции, как указано на рис. 4. ü Нажмем кнопку Далее>. Шаг 4. Размещение диаграммы. ü Выберем размещение диаграммы на отдельном листе и укажем имя листа Диаграмма. ü Щелкнем по кнопке Готово. Результат см. на рис. 5. | рис. 3 рис. 4 |
|
|
рис. 5
10. Сохраните результат в файле под именем регрессия.xls.