Лабораторный практикум

Раздел II. Теория инновационной деятельности

Рис. 10. Выделение ячеек, необходимых для построения диаграммы

Рис. 11. Сортировка элементов столбца от минимального к максимальному значению элементов

3.Для построения диаграммы по введенным данным необходимо «кликнуть» левой кнопкой «мыши» по вкладке меню Вставка

(в верхней части окна) (рис. 12) и нажать левой кнопкой «мыши» значок Точечная в разделе Диаграммы и выбрать

первое исполнение точечной диаграммы – нажать левой кнопкой «мыши» на значок (рис. 12) После проделанных действий появится диаграмма (рис. 13).

71

Раздел II. Теория инновационной деятельности

Рис. 12. Построение точечной диаграммы в системе Microsoft Excel

Рис. 13. Диаграмма, построенная по двум столбцам

4. Для редактирования имеющийся диаграммы необходимо нажать правой кнопкой «мыши» на область диаграммы и выбрать

нужный пункт из списка (см. рис. 14). Нажав левой кнопкой «мыши» на вкладке меню Макет (в верхней части окна) (рис. 15),находим значок Названия осей и нажимаем по нему левой

кнопкой «мыши». Далее выбираем (левой кнопкой мыши)

72

Раздел II. Теория инновационной деятельности

название основной горизонтальной оси и название основной вертикальной оси -> Название под осью и печатаем с

клавиатуры название осей в появившемся месте на диаграмме

(рис.15).

Рис. 14. Редактирование построенной диаграммы

Рис. 15. Обозначение осей на диаграмме

Примечание: можно задать необходимый шрифт, подкорректировать его размер, расположение на области диаграммы, «кликнув» левой кнопкой мыши по названиям осей и заголовка диаграммы, также представляется возможным менять их форму, цвет и размер. Нажав правой кнопкой «мыши» на линии оси и выбрав Формат оси, предоставляется возможность редактирования параметров выбранной оси.

73

Раздел II. Теория инновационной деятельности

5. Для построения графика сигмоиды в виде функции арктангенса: y(x) = A × arctg(x + B) + C необходимо выделить 3 ячейки, куда будут вписаны начальные значения коэффициентов А, В и С (рис. 16), которые в дальнейшем система MS Excel автоматически изменит на соответствующие для построения линии регрессии.

Рис. 16. Задание начальных значений коэффициентов уравнения А, В и С

Примечание1: константы А,В и С уравнения не путать с буквенными обозначениями столбцов системы Excel.

Примечание 2: Коэффициенты уравнения выполняют следующие функции, характеризующие форму линии регрессии:

−A – коэффициент, увеличивающий (уменьшающий) масштаб S- образной кривой;

−B – коэффициент, характеризующий точку перегиба кривой, берется со знаком «–»;

−C – коэффициент сдвигает кривую вверх (вниз) в зависимости от знака перед ним (в нашем случае это знак «+»).

6. Далее сформируем столбец с уравнением вида y(x) = A × arctg(x + B) + C (в рассматриваемом примере это столбец F (рис. 17)). Для этого печатаем заголовок столбца («линия регрессии» (рис. 17)), а в ячейке ниже (в данном случае следующая строка– F2) печатаем знак «=».

Для того чтобы ввести сигмоидальную зависимость в данном случае в виде функции арктангенса, необходимо выполнить следующие действия: левой кнопкой «мыши» кликнуть по значку f(x) (рис. 13) – в верхней части рабочего окна MS Excel. Появится диалоговое окно (рис.17), где выбрав левой клавишей «мыши» из всплывающего списка категорию математических функций – арктангенс – ATAN (рис. 17), нажимаем ОК. В открывшимся новом диалоговом окне (рис. 18) на вкладке Число необходимо напечатать с клавиатуры индекс

74

Раздел II. Теория инновационной деятельности

ячейки, где расположены сведения по датам первых полетов (год), в нашем случае это А2 (рис. 18) или, поставить курсор напротив строки Число и затем «кликнуть» по ячейке с индексом А2 (это более приемлемый вариант, во избежание ошибок применения букв русского и латинского алфавитов), далее нажимаем ОК. Реализация этого действия приведена на рис. 18.

Рис. 17. Ввод функции arctg(x)

Рис. 18. Здание аргумента функции arctg(x)

75

Раздел II. Теория инновационной деятельности

7. Далее для ввода данных по другим константам уравнения (п.6) в столбец с формулой необходимо дописать три коэффициента – А, В и С (рис. 19). Для этого в ячейке F2 (см рис.18) или поставив курсор справа от после знака «=») добавляем адреса ячеек, соответствующих коэффициентам уравнения: коэффициент А имеет адрес С2 (рис. 19), соответственно коэффициент В – D2, а С – E2.

Примечание: Адрес ячейки автоматически заносится в строку формул , для этого нужно «кликнуть» левой клавишей «мыши» по необходимой ячейке с находящимся в ней соответствующим коэффициентом. Эти данные при выполнении лабораторного занятия следует рассматривать как вспомогательные.

После добавления адреса ячейки с коэффициентом А печатаем с клавиатуры в строке формул знак «*», далее в той же строке в скобках печатаем с клавиатуры знак «–» и указываем адрес ячейки со значением коэффициента В – адрес D2 (рис. 19) и далее за скобками печатаем с клавиатуры знак «+» и «кликаем» на ячейку со значением коэффициента С (в нашем случае – это Е2), после чего нажимаем клавишу Enter. Реализация этих действий приведена на рис. 19.

Рис. 19. Ввод коэффициентов уравнения S-образной кривой (сигмоиды)

8. Чтобы «растянуть» столбец с уравнением искомой функции арктангенса (рис. 21), по которому в заключительной части лабораторного занятия будет строится искомая линия регрессии (рис. 30), необходимо «зафиксировать» ячейки с коэффициентами А, В и С. Для этого предназначен значок «$»( рис.20), который необходимо проставить в строке формул. Для этого курсор «мыши» поставим в позицию искомого коэффициента (см.рис. 20) и нажимаем клавишу F4 на клавиатуре, после чего система MS Excel автоматически

76

Раздел II. Теория инновационной деятельности

зафиксирует адрес выбранных коэффициентов (рис. 20). После их «фиксации» в строке формул (рис. 20) нажимаем клавишу Enter.

Рис. 20. Фиксация коэффициентов в строке формул

Далее подводим курсор «мыши» в правый нижний угол начальной ячейки формулы (в нашем случае – F2) (рис. 21)до

появления значка «+». После чего «кликаем» левой кнопкой «мыши» и, не отпуская ее, «протягиваем» курсор вниз до конца

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

Рис. 21. Формирование столбца с формулой уравнения арктангенса

9. Для того чтобы воспользоваться функцией Поиск решения для построения линии регрессии наиболее приближенной к наилучшей теоретической функции и дальнейшего расчета «критерия согласия» необходимо найти целевую ячейку (см. рис. 24), в которой будет рассчитана сумма «квадрата разности» экспериментальных и теоретических значений функции (линии регрессии),(столбец G, рис. 22). Для формирования данного столбца G в ячейке (после заголовка столбца), печатаем с клавиатуры знак «=». Для выполнения расчетов вводим формулу как на рис. 22 (из ячеек столбца Vmax вычитаем построчно значения ячеек столбца линия регрессии и возводим полученную

77

Раздел II. Теория инновационной деятельности

разность в квадрат. Для этого можно воспользоваться комбинацией клавиш Shift + 6.

Рис. 22. Формирования столбца квадрата разности для нахождения целевой ячейки

10. Далее для заполнения ниже лежащих ячеек столбца G растягиваем c помощью курсора столбец до конца ряда ячеек столбца (рис. 23) аналогично п.8 описания лабораторного занятия. Целевая ячейка – это сумма квадратов разности, т.е. сумма значений полученного столбца G. Далее нажав «мышкой» ячейку ниже столбца – G6 – кликаем левой кнопкой мыши по значку формул . Выбираем из всплывающего списка категории математических формул СУММ. В запрашиваемом диалоговом окне подтверждаем диапазон G2: G5 и нажимаем ОК, после чего в выделенной ячейке будет автоматически получена сумма всех значений рассматриваемого столбца G (рис. 24).

Рис. 23. Нахождение суммы столбца для нахождения целевой ячейки

Рис. 24. Целевая ячейка квадрата разности для осуществления функции

78

Раздел II. Теория инновационной деятельности

Поиск решения

11. Далее для построения линии регрессии необходим воспользоваться пакетом системы MS Excel Поиск решения. Указанный пакет находится во вкладке меню Данные -> Поиск решения (рис. 25).

Рис. 25. Окно надстроек MS Excel

Примечание: если в указанной вкладке Данные отсутствует ссылка на пакет Поиск решения необходимо сделать следующее:

−нажимаем левой кнопкой «мыши» в верхней левой части рабочего окна MS Excel Файл;

−в появившемся окне нажимаем Параметры или Параметры MS Excel;

−в появившемся окне находим и нажимаем на строку Надстройки;

−находим из всплывающего списка строку Поиск решения и

нажимаем на нее левой кнопкой «мыши» и ниже в правой части диалогового окна нажимаем левой кнопкой «мыши» Перейти;

−в появившемся окне (рис. 25) надстроек MS Excel ставим левой кнопкой «мыши» галочку напротив строки Поиск решения и

нажимаем ОК. После проделанных действий во вкладке меню Данные появится строка-ссылка Поиск решения.

Нажимаем на строку-ссылку Поиск решения. В результате появляется диалоговое окно рис. 26. Устанавливаем в нем целевую ячейку – ее адрес – сумма квадрата разности (рис. 23 – это ячейка G6), нажимаем маркер (точку) на «равной

79

Раздел II. Теория инновационной деятельности

минимальному значению», рис.26. Вносим изменяемые данные ячеек – это коэффициенты А, В и С искомого уравнения

регрессии. В окне (рис.26) вносим три ограничения Для этого нажимаем Добавить:

1.Все коэффициенты >= 0;

2.Коэффициент В <= последнему значению (года) в столбце

Первый полет, год;

3.Коэффициент В >= первому значению (году) в столбце

Первый полет, год.

Рис. 26. Диалоговое окно пакета Поиск решения

Далее после добавления ограничений нажимаем ОК и

возвращаемся в окно поиск решения (рис. 26). Нажимаем Выполнить в верхней правой части окна (рис. 26), в результате

появляется диалоговое окно рис. 27 – ставим маркер (точку) на Сохранить найденное решение.

80