Построение регрессионного уравнения
С использованием надстройки Поиск решения
Цель работы:
1. Получить практические навыки использования функции Поиск решения в приложенииExcel.
2. Научиться строить диаграммы, содержащие графики разных типов.
Задание:
1. Для набора точек (xi, yi), где xi – величина оборотных средств, yi – прибыль, найти коэффициенты b0 и b1 прямой линии y = b0*x +b1, наилучшим образом аппроксимирующей эти данные с использованием надстройки “Поиск решения...”.
2. Построить диаграмму исходных данных и результатов расчетов по полученному уравнению.
Варианты задания приведены в табл. 1.4.
Порядок выполнения.
1. Поместите исходные данные в диапазон A2:B6(рис. 1.9).
2. В ячейки A8 и B8 поместите текст “b0” и “b1” соответственно, а в ячейки A9 и B9 начальные значения коэффициентов, равные нулю.
3. Вычислите значения yi = b0*xi +b1 для каждой точки. Для этого выделите ячейку C2 и запишите в ней формулу =A$9*A2+B$9.
4. Скопируйте формулу вниз до ячейки C6, используя Маркер заполнения (диапазон C2:C6 заполнится нулями).
5. Вычислите остатки для каждой точки. Для этого выделите ячейку D2 и запишите в ней формулу =B2–C2.
6. Скопируйте формулу вниз до ячейки D6, используя маркер автозаполнения (диапазон D2:D6 заполнится значениями: –52, –72, –86, –310, –352).
7. Вычислите сумму квадратов остатков. Для этого в ячейку D9 введите формулу =СУММКВ(D2:D6), используя Мастер функций (в ячейке D9 появится число 235288).
8. Выполните минимизацию суммы квадратов остатков с использованием функции Поиск решения путем изменения A9:B9 без ограничений. Для этого:
|
|
|
· выполните команду СЕРВИС-Поиск решения…;
· в диалоговом окне Поиск решения (рис. 1.10) установите следующие параметры: целевую ячейку – $D$9 (щелкните по ячейке на рабочем листе) равной – минимальному значению (установите переключатель); в поле Изменяя ячейки введете диапазон $A$9:$B$9 (обведите диапазон на рабочем листе);
· нажмите кнопку Выполнить.
9. В диапазоне A9: B9 появятся значения коэффициентов b0 и b1.
10. Построение графика.
· выделите диапазон A2:C6;
· вызовите Мастер диаграмм;
· выберите Тип диаграммы – Точечная диаграмма со значениями, соединенными сглаживающими линиями;
· нажмите два раза кнопку Далее;
· в диалоговом окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы на вкладке Заголовки дайте названия диаграмме и осям, на вкладке Линии сетки снимите флажок Ось Y (значений): основные линии, на вкладке Легенда установите Размещение внизу;
· нажмите кнопку Далее;
· поместите диаграмму на имеющемся листе и нажмите кнопку Готово;
· дважды щелкните левой клавишей по линии y (прибыль);
· в диалоговом окне Формат ряда данных на вкладке Вид установите переключатель Линия отсутствует и выберите Тип маркера– точку размером 5 пт;
|
|
|
Таблица 1.4
| № вар. | 1 | 2 | 3 | 4 | 5 | 6 | |||
| 1 | X | 11,3 | 13,6 | 18,8 | 22,2 | 24,0 | 30 | ||
| Y | 13,2 | 17,2 | 20,2 | 22,4 | 23,8 | 27,0 | |||
| 2 | X | 0 | 1 | 2 | 3 | 4 | 5 | ||
| Y | 1,2 | 2,5 | 3,9 | 5,2 | 6,4 | 7,7 | |||
| 3 | X | 1 | 3 | 6 | 8 | 10 | 12 | ||
| Y | 1,6 | 2,4 | 2,8 | 3,2 | 3,3 | 3,5 | |||
| 4 | X | 0 | 10 | 20 | 30 | 40 | 50 | ||
| Y | 348,5 | 248 | 185 | 142 | 106 | 79,0 | |||
| 5 | X | 54,05 | 49,02 | 42,74 | 37,04 | 26,11 | 18,94 | ||
| Y | 52,4 | 62,0 | 75,2 | 87,6 | 118,8 | 145,2 | |||
| 6 | X | -14,051 | -12,86 | -12,166 | -12,057 | -11,883 | -10,187 | ||
| Y | 233 | 223 | 217 | 216 | 216 | 133 | |||
| 7 | X | 1 | 2 | 5 | 10 | 30 | 60 | ||
| Y | 3,81 | 3,86 | 3,88 | 4,02 | 4,98 | 6,51 | |||
| 8 | X | 30,0 | 31,0 | 32,0 | 33,0 | 34,0 | 35,0 | ||
| Y | 9,01 | 9,09 | 9,15 | 9,25 | 9,34 | 9,41 | |||
| 9 | X | 18,0 | 22,0 | 33,0 | 60,0 | 73,0 | 83,0 | ||
| Y | 41,75 | 49,5 | 58,05 | 70,35 | 79,0 | 97 | |||
| 10 | X | 8,3 | 12,3 | 18,8 | 22,9 | 23,1 | 24,0 | ||
| Y | 0,32 | 0,46 | 1,10 | 1,32 | 1,26 | 1,44 | |||
| 11 | X | 4,0 | 8,0 | 12,5 | 16,0 | 20,0 | 25,0 | ||
| Y | 3,7 | 7,8 | 12,1 | 15,6 | 19,8 | 24,5 | |||
| 12 | X | 32,4 | 33,0 | 34,1 | 34,8 | 36,7 | 37,2 | ||
| Y | 1,69 | 1,75 | 1,48 | 1,89 | 1,73 | 2,11 | |||
| 13 | X | -3 | -2 | -1 | 0 | 1 | 2 | ||
| Y | -1,70 | -1,01 | -0,21 | 0,52 | 0,73 | 1,30 | |||
| 14 | X | 0,2 | 0,5 | 0,8 | 1,1 | 1,4 | 1,7 | ||
| Y | 5 | 7 | 9,5 | 11 | 13 | 15 | |||
| 15 | X | 0,8 | 1,1 | 1,4 | 1,7 | 2,0 | 2,3 | ||
| Y | 37 | 32 | 30 | 28 | 22,6 | 20 | |||
| 16 | X | 12,9 | 16,8 | 22,0 | 23,7 | 24,2 | 26,0 | ||
| Y | 15,6 | 18,8 | 23,3 | 23,0 | 24,4 | 30,0 | |||
| 17
| X | 12,0 | 17,6 | 24,0 | 53,0 | 65,0 | 78,0 | ||
| Y | 32,95 | 38,8 | 48,8 | 69,0 | 75,2 | 87,6 | |||
| 18 | X | 12,3 | 16,8 | 20,5 | 22,9 | 23,1 | 24,0 | ||
| Y | 0,46 | 1,10 | 1,20 | 1,32 | 1,26 | 1,44 | |||
| 19 | X | 0 | 10 | 20 | 30 | 40 | 50 | ||
| Y | 79,0 | 70,0 | 64 | 58 | 51 | 47 | |||
| 20 | X | 23,1 | 24,0 | 24,9 | 25,3 | 26,1 | 27,5 | ||
| Y | 1,26 | 1,44 | 2,05 | 2,34 | 2,91 | 3,15 |
· нажмите OК;
· дважды щелкните по области построения диаграммы;
· в диалоговом окне Формат области построения установите переключатель Заливка обычная;
· нажмите OК.
Результаты расчетов и график приведены на рис. 1.9.
Рис. 1.9. Результаты построения регрессионного уравнения с использованием надстройки Поиск решения
Рис. 1.10. Диалоговое окно Поиск решения
Часть 2
ПРОГРАММИРОВАНИЕ НА VISUAL BASIC FOR APPLICATIONS (VBA)
Лабораторная работа 2.1
ВЫЧИСЛЕНИЕ АРИФМЕТИЧЕСКИХ ВЫРАЖЕНИЙ
Цель работы:
1. Получить практические навыки записи арифметических выражений.
2. Приобрести знания по составлению простейших программ с выводом результатов на рабочий лист рабочей книги Excel.
Задание:
1. Изучить теоретический материал соответствующего раздела учебного пособия «Информатика в пищевой биотехнологии (часть 2)»
|
|
|
2. Составить программу вычисления арифметического выражения.
3. Исходные данные и результаты расчета разместить на рабочем листе рабочей книги Excel, назначить макросу кнопку.
4. Написать отчет, содержащий:
- задание;
- текст программы;
- результаты расчета.
Варианты задания приведены в табл. 2.1.
Создание макроса на языке VBA
Создать макрос «Выражение», который вычисляет значение арифметического выражения с проверкой области определения функций.
Порядок выполнения (рис. 2.1):
1. Запустить приложение Excel.
2. Выделить ячейку A2 и ввести в нее “a”.
3. Выделить ячейку B2 и ввести в нее число 4.
4. Выделить ячейку A3 и ввести в нее “b”.
5. Выделить ячейку B3 и ввести в нее 1.
6. Выделить ячейку A4 и ввести в нее “x”.
7. Выделить ячейку B4 и ввести в нее 2.
8. Выберите в главном меню Сервис – Макрос – Начать запись. В появившемся диалоговом окне (рис. 2.2) в поле Имя макроса введите имя макрокоманды, например Выражение. Нажмите на кнопку ОК.
Таблица 2.1
| № | Функция | № | Функция |
| 1 | 
| 2 | 
|
| 3 | 
| 4 | 
|
| 5 | 
| 6 | 
|
| 7 | 
| 8 | 
|
| 9 | 
| 10 | 
|
| 11 | 
| 12 | 
|
| 13 | 
| 14 | 
|
| 15 | 
| 16 | 
|
| 17 | 
| 18 | 
|
| 19 | 
| 20 | 
|
Рис. 2.1. Исходные данные
| Рис. 2.2. Окно «Запись макроса» |
9. Выберите в главном меню Сервис – Макрос – Остановить запись, затем Сервис – Макрос – Макросы. В появившемся окне (рис. 2.3) в левой части выделите имя вашего макроса, в опции Находится в: из ниспадающего меню выберите Эта книга и нажмите кнопку Войти.
Рис. 2.3. Окно «Макрос»
10. В открывшемся редакторе Microsoft Visual Basic выполните ввод исходного кода программы
Sub Выражение()
'Активация рабочего листа
Sheets("Лист1").Select
'Ввод исходных данных из ячеек книги Excel
a = ThisWorkbook.ActiveSheet.Cells(2, 2)
b = ThisWorkbook.ActiveSheet.Cells(3, 2)
x = ThisWorkbook.ActiveSheet.Cells(4, 2)
'Выполнение вычислений
c0 = Exp(x + a * b)
c1 = a * b + x ^ 2 * Sin(a) ^ 2
If c1 < 0 Then GoTo 1
c2 = a * a - b * b
If c2 = 0 Then GoTo1
c3 = Sqr(c1) / c2
If c3 <= 0 Then GoTo 1
y = c0 + Log(c3) + x
'Вывод результата в ячейку книги Excel
ThisWorkbook.ActiveSheet.Cells(6, 1) = "y"
ThisWorkbook.ActiveSheet.Cells(6, 2) = y
Exit Sub
1: ThisWorkbook.ActiveSheet.Cells(6, 2) = "Функция не определена"
End Sub
11. Выполните команду Файл – Сохранить, дайте файлу имя и вернитесь в окно Excel.
12. Введите команду Сервис – Макрос – Макросы…. На открывшейся диалоговой панели Макросы выберите макрос Выражение и нажмите кнопку Выполнить (рис. 2.4).
Рис. 2.4. Результат выполнения макроса
13. Введите новые исходные данные: a=0,6; b=5; x=4,45 и выполните макрос (рис. 2.5).
Рис. 2.5. Результат выполнения макроса
14. Текст макроса и результаты вычислений запишите в тетрадь.
Назначить макросу кнопку.
Для этого необходимо выполнить следующие действия:
- выбрать на панели рисования любую фигуру (прямоугольник, круг или др.) и разместить ее на рабочем листе;
- закрасить фигуру любым цветом;
- вызвать контекстное меню, выполнить команду Добавить текст и ввести текст «Вывод значения алгебраического выражения»;
- используя команду контекстного менюФормат автофигуры,выполнить форматирование текста по горизонтали и вертикали – по центру, размер шрифта – 14 пт, начертание – полужирный;
- выбрать из контекстного меню команду Назначить макрос, из появившегося списка окна «Назначить макрос объекту» выбрать имя макроса Выражение, в поле Находится в: выбрать Эта книга и нажать OK;
Поместите указатель мыши на созданную кнопку и нажмите левую клавишу.
Лабораторная работа 2.2
ВЫЧИСЛЕНИЕ СЛОЖНОЙ ФУНКЦИИ
Цель работы:
Приобрести навыки по использованию в программе условного оператора If … Then … Else …
Задание:
1. Составить программу вычисления значений функции, заданных несколькими условиями с использованием условного оператора If … Then … Else …
2. Исходные данные и результаты расчета разместить на рабочем листе рабочей книги Excel, назначить макросу кнопку.
3. Написать отчет, содержащий:
- задание;
- текст программы;
- результаты расчета.
Варианты задания приведены в табл. 2.2.
Таблица 2.2
| № | Функция |
| 1 | 
|
| Продолжение табл. 2.2 | |
| № | Функция |
| 2 | 
|
| 3 | 
|
| 4 | 
|
| 5 | 
|
| 6 | 
|
| 7 | 
|
| 8 | 
|
| Продолжение табл. 2.2 | |
| № | Функция |
| 9 | 
|
| 10 | 
|
| 11 | 
|
| 12 | 
|
| 13 | 
|
| 14 | 
|
| Окончание табл. 2.2 | |
| № | Функция |
| 15 | 
|
| 16 | 
|
| 17 | 
|
| 18 | 
|
| 19 |  
|
| 20 |  
|
Лабораторная работа 2.3
Дата добавления: 2018-04-05; просмотров: 331; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!