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

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

Примечание 1: Ячейки А1 и В1 в данном случае представляют заголовки столбцов и в дальнейших действиях, производимых со столбцами, не участвуют.

Примечание 2: в столбце А (пример, рис. 6) в качестве даты первого полета вводится только год (без указания дня, месяца первого полета), например, 1964

Примечание 3: Все введенные данные по годам должны располагаться строго в порядке возрастания дат первых полетов. Для этого после ввода двух столбцов (рис. 6) необходимо провести сортировку данных, приведенных в приложениях:

−выделяем два столбца левой клавишей «мыши» без заголовков,

−«кликаем» левой кнопкой «мыши» по вкладке меню Данные (в верхней части окна) и

−нажимаем левой кнопкой «мыши» значок сортировки (от

минимального к максимальному значению) .

После этого все строки по параметру (годы) расположатся строго по их возрастанию (подробная процедура сортировки объяснена. в п. 2 методики выполнения лабораторного занятия № 2.1.

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

«мыши» на значок (рис. 7)

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

131

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

После проделанных действий появится диаграмма (рис. 8).

Примечание 1: Построенную диаграмму можно редактировать, обозначать названия осей и корректировать масштаб, форму и цвет маркера. Подробно данные действия указаны в п. 4 методики выполнения лабораторного занятия № 2.1.

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

3. Далее сформируем столбец расчета сигмоиды в виде функции arctg (сигмоида 1 в табл. 1 теоретической части лабораторного занятия). Для этого введем в ячейку (рис. 9) после заголовка столбца С ( в нашем случае это ячейка С2) после знака «=» уравнение из табл. 1(колонка 5). Функция арктангенса в системе MS Excel обозначена ATAN. Вместо переменой t указываем адрес ячейки (рис. 9), соответствующей ячейке первого года полетов самолета в столбце Первый полет, год (в нашем случае это ячейка А2). После заполнения первой ячейки в столбце С (рис. 9),

132

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

подводим курсор «мыши» к правому нижнему углу ячейки с формулой (в нашем случае это ячейка С2, которая выделена жирной рамкой) и после появления значка «+», «протягиваем» курсор вниз до конца ряда чисел столбца С (рис. 9), в результате чего система автоматически заполнит все ячейки столбца.

Примечание: Подробное описание формирования столбцов с формулами смотри в методике выполнения лабораторного занятия № 2.1.

Рис. 9. Построение столбца с расчета тангенциальной функции

4. Для того чтобы добавить полученный столбец (рис. 9), соответствующей сигмоиде вида arctg на уже имеющейся диаграмме (рис.8) необходимо нажать правой кнопкой «мыши» по области диаграммы и «кликнуть» левой кнопкой «мыши» по строке Выбрать данные. Появится диалоговое окно (рис. 10), где указываются данные по осям и вписывается название нового ряда.

133

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

Примечание: Подробное описание добавления рядов данных смотри в методике выполнения лабораторного занятия № 2.1.

Рис. 10. Добавление нового ряда в имеющуюся диаграмму

5. Так как система MS Excel построила новый ряд в виде функциии arctg по умолчанию в точечном виде, то нужно соединить точки сглаженной линией регрессии. Для этого «кликнем» правой кнопкой «мыши» по маркеру нового ряда (рис. 11) и выберем строку Изменить тип диаграммы для ряда. Появится новое диалоговое окно (рис. 12), где находим второй тип точечной диаграммы (выделен рамкой) и нажимаем по нему левой кнопкой «мыши» (рис. 12). Далее нажимаем кнопку ОК в нижней части текущего диалогового окна. После чего диаграмма примет вид, показанный на рис. 13.

134

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

Рис. 11. Вызов окна изменения типа диаграммы

Рис. 12. Окно изменения типа диаграммы

135

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

Рис. 13. Диаграмма функции arctg для анализа развития двигателей дозвуковых самолетов-истребителей

6. Далее рассчитаем критерий согласия (коэффициент достоверности– R2, определяемый по формуле (14), для анализа сходимости сигмоиды:

				__
	R	2	=	∑( yx − y)2	,	(14)
				__
				∑( y − y)2
где yx –	зависимая переменная уравнения функции арктангенса;
__	среднее значение всех зависимых переменных;
y –
y –	табличные значения.

Для расчетов вначале определим среднее значение зависимой переменной (в нашем случае это столбец Тангенциальная функция), для этого напишем (рис. 14) в ячейке ниже ряда данных в С17 знак «=»и нажмем на значок fx. После этого появится диалоговое окно Мастер функций (рис. 15-16). На первом шаге работы с этим окном находим из перечня типовых функций в категории «Полный алфавитный перечень» функцию, которая возвращает (определяет) среднее арифметическое значение аргумен-

136

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

тов – СРЗНАЧ (рис. 15). Далее нажимаем кнопку ОК в нижней части текущего диалогового окна. После этого (на втором шаге) появляется окно задания аргументов анализируемой функции (рис. 16). Далее необходимо, либо согласиться с указанным по умолчанию системой MS Excel диапазоном аргументов (в данном случае среднее значение находим по всему ряду значений данного столбца тангенциальной функции), либо изменить его, если это необходимо, после чего нажимаем кнопку в нижней части текущего окна ОК.

Рис. 14. Ввод формулы в строку для расчета среднего значения функции

Рис. 15. Диаловое окно «Мастер функций (шаг 1)» – выбор функции

137

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

Рис. 16. Диаловое окно «Мастер функций (шаг 2)» – задание аргументов функции

7. Далее в следующем столбце (например, столбец D,рис. 17) формируем числитель формулы для расчета критерия согласия (14). Для этого после заголовка (рис. 17) печатаем знак «=» и в строке fx вводим формулу, как показано на рисунке, что соответствует числителю формулы (14). Так как среднее значение является константой регрессии и одинаково для всех аргументов функции, его необходимо «зафиксировать» значком $ (рис.17). Это можно сделать, либо нажимая на клавиатуре сочетание клавиш Shift 4, либо путем нажатия на клавишу F4. Для ввода в степень используется сочетание клавиш Shift 6. После заполнения первой ячейки столбца числителя (на рис. 17 она выделена рамкой), подводим курсор «мыши» к правому нижнему углу ячейки

сформулой (в нашем случае это ячейка D2), после появления значка «+», «протягиваем» курсор вниз до конца ряда чисел (рис. 18). Далее в ячейке D17 система автоматически определит сумму

спомощью функции СУММ (см. подробную работу с данной функцией в лабораторном занятии № 2.1). Реализация таких действий приведена на рис. 18.

138

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

Рис. 17. Формирование столбца для расчета числителя формулы R2

Рис. 18. Формирование столбца для расчета знаменателя формулы R2

8. Аналогичным образом сформируем столбец (в нашем случае это столбец Е на рис.18) для вычисления знаменателя формулы (14). Для этого вводим формулу соответствующую знаменателю уравнения (14).рис. 18. После заполнения первой ячейки столбца расчета знаменателя (рис. 18), подводим курсор «мыши» к правому нижнему углу ячейки Е2 (выделена рамкой) и после появ-

139

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

ления значка «+», «протягиваем» курсор вниз до конца ряда чисел, рис. 19. Далее в ячейке Е17 система автоматически определяет сумму с помощью функции СУММ. Реализация названных действий приведена на рис. 19.

Рис. 19. Расчет суммы для определения критерия согласия (14)

9. Далее рассчитываем итоговое значение критерия согласия R2, для этого необходимо сумму числителя разделить на сумму знаменателя дроби в формуле (14). Для выполнения таких действий в ячейке (в нашем случае – это Е19 на рис.20) после ввода знака «=» вписываем в fx формулу деления значения числителя на значение знаменателя (рис. 20). Для этого «кликаем» левой кнопкой «мыши» по ячейке, соответствующей сумме числителя (в нашем случае это ячейка D17), нажимаем на клавиатуре знак деления « / » и «кликаем» левой кнопкой «мыши» по ячейке, соответствующей сумме знаменателя (Е17) (рис. 20). После этого система MS Excel автоматически выведет в ячейку (в нашем случае Е19) итоговое значение критерия согласия R2 для анализируемой сигмоиды. Это значение всегда меньше единицы, в нашем случае R2 составляет округленно 0,92, что соответствует приемлемой достоверности результатов построения линии регрессии. Полученное значение R2 внести в табл. 1 (колонка 6).

140