 |
Методика выполнения задания
|
|
|
|
1. Запустить систему Microsoft Excel для работы с электронными таблицами, которая предоставляет возможности выполнения статистических расчетов. Для этого дважды «кликаем» левой кнопкой «мыши» по значку на рабочем столе или в корневой директории (нажав левой клавишей «мыши» меню Пуск -> Microsoft Office -> Microsoft Excel 2007 или Microsoft Excel 2010) (также возможен вариант запуска системы с диска), после чего появится главное окно системы (рис. 8).
Рис. 8. Рабочее окно системы Microsoft Excel
2. В первых двух столбцах (например, А и В) вводим информацию о датах первых полетов и величинах максимальных скоростей самолетов-истребителей (или тяги их реактивных двигателей) по данным, приведенным в приложении (табл. П1–4). Введите данные (дату первого полета и максимальную скорость (тягу) для каждого самолета (двигателя), относящегося к определенному поколению технологий (рис. 9). Ячейки А1 и В1 в данном случае представляют заголовки столбцов и в дальнейших действиях, производимых со столбцами, не участвуют. Для примера (рис. 9) рассмотрен наиболее простой случай формирования столбцов по данным самолетов-истребителей вертикального взлета и посадки (табл. П3).
Рис. 9. Ввод данных для построения диаграммы
Примечание 1: в столбце А (пример, рис. 9) в качестве даты первого полета вводится только год (без указания дня, месяца первого полета), например, 1964. Примечание 2: Все введенные данные по годам должны располагаться строго в порядке возрастания дат первых полетов. Для этого после ввода двух столбцов (рис. 9) необходимо провести сортировку данных, приведенных в приложениях: выделяем два столбца левой клавишей «мыши» без заголовков (рис. 10), и «кликаем» левой кнопкой «мыши» по вкладке меню Данные (в верхней части окна) и нажимаем левой кнопкой «мыши» значок сортировки (от минимального к максимальному значению) (рис. 11). После этого все строки по параметру (годы) расположатся строго по их возрастанию.
|
|
|
Рис. 10. Выделение ячеек, необходимых для построения диаграммы
Рис. 11. Сортировка элементов столбца от минимального к максимальному значению элементов
3. Для построения диаграммы по введенным данным, необходимо «кликнуть» левой кнопкой «мыши» по вкладке меню Вставка (в верхней части окна) (рис. 12) и нажать левой кнопкой «мыши» значок Точечная в разделе Диаграммы и выбрать первое исполнение точечной диаграммы – нажать левой кнопкой «мыши» на значок (рис. 12) После проделанных действий появится диаграмма (рис. 13).
Рис. 12. Построение точечной диаграммы в системе Microsoft Excel
Рис. 13. Диаграмма, построенная по двум столбцам
4. Для редактирования имеющийся диаграммы необходимо нажать правой кнопкой «мыши» на область диаграммы и выбрать нужный пункт из списка (см. рис. 14). Нажав левой кнопкой «мыши» на вкладке меню Макет (в верхней части окна) (рис. 15), находим значок Названия осей и нажимаем по нему левой кнопкой «мыши». Далее выбираем (левой кнопкой мыши) название основной горизонтальной оси и название основной вертикальной оси -> Название под осью и печатаем с клавиатуры название осей в появившемся месте на диаграмме (рис. 15).
Рис. 14. Редактирование построенной диаграммы
Рис. 15. Обозначение осей на диаграмме
Примечание: можно задать необходимый шрифт, подкорректировать его размер, расположение на области диаграммы, «кликнув» левой кнопкой мыши по названиям осей и заголовка диаграммы, также представляется возможным менять их форму, цвет и размер. Нажав правой кнопкой «мыши» на линии оси и выбрав Формат оси, предоставляется возможность редактирования параметров выбранной оси.
|
|
|
5. Для построения графика сигмоиды в виде функции арктангенса: у(х) = А∙arctg(x+B)+C необходимо выделить 3 ячейки, куда будут вписаны начальные значения коэффициентов А, В и С (рис. 16), которые в дальнейшем система MS Excel автоматически изменит на соответствующие для построения линии регрессии.
Рис. 16. Задание начальных значений коэффициентов уравнения А, В и С
Примечание 1: константы А, В и С уравнения не путать с буквенными обозначениями столбцов системы Excel.
Примечание 2: Коэффициенты уравнения выполняют следующие функции, характеризующие форму линии регрессии:
− A – коэффициент, увеличивающий (уменьшающий) масштаб S -образной кривой;
− B – коэффициент, характеризующий точку перегиба кривой, берется со знаком «–»;
− C – коэффициент сдвигает кривую вверх (вниз) в зависимости от знака перед ним (в нашем случае это знак «+»).
6. Далее сформируем столбец с уравнением вида у(х) = А∙arctg(x+B)+C в рассматриваемом примере это столбец F (рис. 17)). Для этого печатаем заголовок столбца («линия регрессии» (рис. 17)), а в ячейке ниже (в данном случае следующая строка– F2) печатаем знак «=». Для того чтобы ввести сигмоидальную зависимость в данном случае в виде функции арктангенса, необходимо выполнить следующие действия: левой кнопкой «мыши» кликнуть по значку f(x) (рис. 13) – в верхней части рабочего окна MS Excel. Появится диалоговое окно (рис. 17), где выбрав левой клавишей «мыши» из всплывающего списка категорию математических функций – арктангенс – ATAN (рис. 17), нажимаем ОК. В открывшимся новом диалоговом окне (рис. 18) на вкладке Число необходимо напечатать с клавиатуры индекс ячейки, где расположены сведения по датам первых полетов (год), в нашем случае это А 2 (рис. 18) или поставить курсор напротив строки Число и затем «кликнуть» по ячейке с индексом А 2 (это более приемлемый вариант, во избежание ошибок применения букв русского и латинского алфавитов), далее нажимаем ОК. Реализация этого действия приведена на рис. 18.
Рис. 17. Ввод функции arctg(x)
Рис. 18. Здание аргумента функции arctg(x)
7. Далее для ввода данных по другим константам уравнения (п. 6) в столбец с формулой необходимо дописать три коэффициента – А, В и С (рис. 19). Для этого в ячейке F 2 (см. рис. 18) или, поставив курсор справа fx после знака «=», добавляем адреса ячеек, соответствующих коэффициентам уравнения: коэффициент А имеет адрес С 2 (рис. 19), соответственно коэффициент В – D 2, а С – E 2.
|
|
|
Примечание: Адрес ячейки автоматически заносится в строку формул fx, для этого нужно «кликнуть» левой клавишей «мыши» по необходимой ячейке с находящимся в ней соответствующим коэффициентом. Эти данные при выполнении лабораторного занятия следует рассматривать как вспомогательные.
После добавления адреса ячейки с коэффициентом А печатаем с клавиатуры в строке формул fx знак «*», далее в той же строке в скобках печатаем с клавиатуры знак «–» и указываем адрес ячейки со значением коэффициента В – адрес D 2 (рис. 19) и далее за скобками печатаем с клавиатуры знак «+» и «кликаем» на ячейку со значением коэффициента С (в нашем случае это Е2), после чего нажимаем клавишу Enter. Реализация этих действий приведена на рис. 19.
Рис. 19. Ввод коэффициентов уравнения S -образной кривой (сигмоиды)
8. Чтобы «растянуть» столбец с уравнением искомой функции арктангенса (рис. 21), по которому в заключительной части лабораторного занятия будет строиться искомая линия регрессии (рис. 30), необходимо «зафиксировать» ячейки с коэффициентами А, В и С. Для этого предназначен значок «$» (рис. 20), который необходимо проставить в строке формул. Для этого курсор «мыши» поставим в позицию искомого коэффициента (см. рис. 20) и нажимаем клавишу F 4 на клавиатуре, после чего система MS Excel автоматически зафиксирует адрес выбранных коэффициентов (рис. 20). После их «фиксации» в строке формул (рис. 20) нажимаем клавишу Enter.
Рис. 20. Фиксация коэффициентов в строке формул
Далее подводим курсор «мыши» в правый нижний угол начальной ячейки формулы (в нашем случае – F 2) (рис. 21) до появления значка «+». После чего «кликаем» левой кнопкой «мыши» и, не отпуская ее, «протягиваем» курсор вниз до конца имеющегося столбца данных (в нашем случае до ячейки F 5), что позволяет получить предварительный ряд значений первого шага итерации.
|
|
|
Рис. 21. Формирование столбца с формулой уравнения арктангенса
9. Для того чтобы воспользоваться функцией Поиск решения для построения линии регрессии, наиболее приближенной к наилучшей теоретической функции и дальнейшего расчета «критерия согласия», необходимо найти целевую ячейку (см. рис. 24), в которой будет рассчитана сумма «квадрата разности» экспериментальных и теоретических значений функции (линии регрессии), (столбец G, рис. 22). Для формирования данного столбца G в ячейке (после заголовка столбца), печатаем с клавиатуры знак «=». Для выполнения расчетов вводим формулу как на рис. 22 (из ячеек столбца V max вычитаем построчно значения ячеек столбца линия регрессии и возводим полученную разность в квадрат. Для этого можно воспользоваться комбинацией клавиш Shift + 6.
Рис. 22. Формирования столбца квадрата разности для нахождения целевой ячейки
10. Далее для заполнения нижележащих ячеек столбца G растягиваем c помощью курсора столбец до конца ряда ячеек столбца (рис. 23) аналогично п. 8 описания лабораторного занятия. Целевая ячейка – это сумма квадратов разности, т.е. сумма значений полученного столбца G. Далее нажав «мышкой» ячейку ниже столбца G 6, кликаем левой кнопкой мыши по значку формул. Выбираем из всплывающего списка категории математических формул СУММ. В запрашиваемом диалоговом окне подтверждаем диапазон G 2: G 5 и нажимаем ОК, после чего в выделенной ячейке будет автоматически получена сумма всех значений рассматриваемого столбца G (рис. 24).
Рис. 23. Нахождение суммы столбца для нахождения целевой ячейки
Рис. 24. Целевая ячейка квадрата разности для осуществления функции Поиск решения
11. Далее для построения линии регрессии необходимо воспользоваться пакетом системы MS Excel Поиск решения. Указанный пакет находится во вкладке меню Данные -> Поиск решения (рис. 25).
| Рис. 25. Окно надстроек MS Excel Примечание: если в указанной вкладке Данные отсутствует ссылка на пакет Поиск решения, необходимо сделать следующее: − нажимаем левой кнопкой «мыши» в верхней левой части рабочего окна MS Excel Файл; − в появившемся окне нажимаем Параметрыили Параметры MS Excel; |
| − в появившемся окне находим и нажимаем на строку Надстройки; |
− находим из всплывающего списка строку Поиск решения и нажимаем на нее левой кнопкой «мыши» и ниже в правой части диалогового окна нажимаем левой кнопкой «мыши» Перейти;
− в появившемся окне (рис. 25) надстроек MS Excel ставим левой кнопкой «мыши» галочку напротив строки Поиск решения и нажимаем ОК. После проделанных действий во вкладке меню Данные появится строка-ссылка Поиск решения.
|
|
|
Нажимаем на строку-ссылку Поиск решения. В результате появляется диалоговое окно (рис. 26). Устанавливаем в нем целевую ячейку – ее адрес – сумма квадрата разности
| (рис. 23 – это ячейка G6), нажимаем маркер (точку) на «равной минимальному значению», (рис. 26). Вносим изменяемые данные ячеек – это коэффициенты А, В и С искомого уравнения регрессии. В о кне (рис. 26) вносим три ограничения. Для этого нажимаем Добавить: |
| 1. Все коэффициенты >= 0; |
| 2. Коэффициент В <= последнему значению (года) в столбце Первый полет, год; |
| 3. Коэффициент В >= первому значению (году) в столбце Первый полет, год.
|
| Рис. 26. Диалоговое окно пакета Поиск решения Далее после добавления ограничений нажимаем ОК и возвращаемся в окно поиск решения (рис. 26). Нажимаем Выполнитьв верхней правой части окна (рис. 26), в результате появляется диалоговое окно (рис. 27) – ставим маркер (точку) на Сохранитьнайденное решение. |
| 12. После сохранения результатов (рис. 27) найденного системой MS Excel решения по определению неизвестных коэффициентов А, В, С первоначальные значения в ячейках, соответствующих этим коэффициентам, изменятся автоматически (рис. 28). Далее необходимо построить полученный тренд изменения максимальных скоростей самолетов (или тяги реактивных двигателей) в имеющейся диаграмме (рис. 29). Для внесения нового ряда данных на имеющуюся диаграмму, щелкаем правой кнопкой «мыши» на области внутри диаграммы, и из всплывающего списка «кликаем» левой кнопкой «мыши» Выбрать данные -> Ряд -> Добавить (рис. 28). После таких действий всплывает диалоговое окно (рис. 29), где заносятся имя ряда и массивы данных по осям абсцисс X и ординат Y. В рассматриваемом примере это следующие данные: имя ряда – линия регрессии, значения по оси X – A2:A5, по оси Y – F2:F5, что продемонстрировано на рис. 29. Имя ряда в первой строке окна печатается с клавиатуры, массивы данных по осям вводятся поочередно по X и Y c помощью левой кнопкой «мыши», путем выделения соответствующих рядов данных (рис. 29). Далее нажимаем левой кнопкой «мыши» ОК и смотрим результат (рис. 30). Для соединения получившихся экспериментальных точек сплошной линией нажимаем правой кнопкой «мыши» на одну из построенных точек только что построенного ряда чисел в области диаграммы и выбираем из всплывающего списка Тип диаграммы. Делаем клик мышкой по соответствующей картинке ( |
| точки, соединенные линией) и нажимаем ОК. Таким образом можно получить окончательный вид диаграммы с искомой линией регрессии (рис. 30). 13.В случае если результат нас не устраивает, делаем «клик» левой кнопкой «мыши» на ячейках, которые соответствуют коэффициентам А, В, и С (в нашем примере это ячейки С2, D2 и Е2) и редактируем значения коэффициентов до достижения необходимого результата по коэффициенту сходимости статистических данных или еще раз производим процедуру Поиска решения, как указано в п. 11 методики выполнения лабораторного занятия (рис. 26, 27). Результат с откорректированными коэффициентами автоматически отражается в имеющейся диаграмме. В данном примере установлено, что наилучшим образом для построения сигмоиды развития самолетов (двигателей) подходят следующие эмпирические коэффициенты: A=497,46, B=1964, C=852,85 (рис. 28).
|
| Рис. 28. Добавление нового ряда данных в имеющуюся диаграмму |
Рис. 29. Добавление или изменение ряда параметров технического уровня (самолетов или двигателей) для отображения его на диаграмме
Рис. 30. Автоматически построенная S -образная кривая развития самолетов-истребителей вертикального взлета и посадки
Примечание: Для корректировки вида S -образной кривой нажимаем на нее правой кнопкой «мыши» и из всплывающего списка выбираем вкладку Формат области построения (рис. 28). Эта возможность позволяет изменять размер, форму и цвет точек данной кривой, а также задавать толщину и цвет сглаживающей (аппроксимирующей) линии.
14. Оформить отчет о выполненной работе.
5. Контрольные вопросы
1. Какие научные законы инноватики вы знаете?
2. Какие участки можно выделить внутри S -образной кривой развития техники (технологии)?
4. В чем сущность технологий «STEALTH TECHNOLOGY» и технологий «STOVL»?
3. В чем сущность закона смены поколений техники и технологий?
5. Какие технологии называют критическими? 6. Какие вы знаете примеры новейших образцов техники и технологий гиперзвуковой авиации?
6. Требования к отчету Отчет должен содержать титульный лист, цель, результаты работы в виде графиков с обозначением поколений самолетов – истребителей и указанием полученных уравнений.
Отчет оформляется в соответствии с требованиями СТО УГАТУ 016-2007
|
|
|
