Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010

Предложена методика обработки результатов однофакторных экспериментов с использованием программы Microsoft Excel 2010. На примере модифицирования литейного алюминиевого сплава АК9ч рассмотрены этапы расчета однофакторной математической модели при помощи функции «Линия тренда» программы MS Excel 2010...

Повний опис

Збережено в:

Бібліографічні деталі
Дата:	2013
Автори:	Чернега, Д.Ф., Рыбак, В.Н.
Формат:	Стаття
Мова:	Russian
Опубліковано:	Фізико-технологічний інститут металів та сплавів НАН України 2013
Назва видання:	Металл и литье Украины
Онлайн доступ:	http://dspace.nbuv.gov.ua/handle/123456789/143753
Теги:	Додати тег Немає тегів, Будьте першим, хто поставить тег для цього запису!
Назва журналу:	Digital Library of Periodicals of National Academy of Sciences of Ukraine
Цитувати:	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010 / Д.Ф. Чернега, В.Н. Рыбак // Металл и литье Украины. — 2013. — № 7. — С. 31-37. — Бібліогр.: 6 назв. — рос.

Репозитарії

Digital Library of Periodicals of National Academy of Sciences of Ukraine

id	irk-123456789-143753
record_format	dspace
spelling	irk-123456789-1437532018-11-11T01:23:20Z Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010 Чернега, Д.Ф. Рыбак, В.Н. Предложена методика обработки результатов однофакторных экспериментов с использованием программы Microsoft Excel 2010. На примере модифицирования литейного алюминиевого сплава АК9ч рассмотрены этапы расчета однофакторной математической модели при помощи функции «Линия тренда» программы MS Excel 2010. Применив всего одну программу, за относительно короткое время удалось выполнить целый комплекс расчетов однофакторного эксперимента, а именно: определить средние значения откликов каждой точки и их доверительные интервалы; подобрать из нескольких вариантов наилучшую математическую модель и рассчитать ее коэффициенты; построить графическую зависимость процесса в соответствии со всеми требованиями нормативных документов; проверить математическую модель на адекватность; провести оптимизацию математической модели и прогнозирование при ее помощи. Приведенную методику обработки результатов однофакторного эксперимента могут использовать отечественные исследователи и специалисты для обработки результатов любых однофакторных экспериментов металлургических и неметаллургических процессов. Запропоновано методику обробки результатів однофакторних експериментів з використанням програми Microsoft Excel 2010. На прикладі модифікування ливарного алюмінієвого сплаву АК9ч розглянуто етапи розрахунку однофакторної математичної моделі за допомогою функції «Лінія тренду» програми MS Excel 2010. Застосувавши всього лише одну програму, за відносно короткий час виконали цілий комплекс розрахунків однофакторного експерименту, а саме: визначили середні значення відгуків кожної точки та їх довірчі інтервали; відібрали із декількох варіантів найкращу математичну модель та розрахували її коефіцієнти; побудували графічну залежність процесу у відповідності до всіх вимог нормативних документів; перевірили математичну модель на адекватність; провели оптимізацію математичної моделі та прогнозування за її допомогою. Наведену методику обробки результатів однофакторного експерименту можуть використовувати вітчизняні науковці та фахівці для обробки результатів будь-яких однофакторних експериментів металургійних і неметалургійних процесів. The technique of processing of results of one-factorial experiments by means of the Microsoft Excel 2010 program is offered. On an example of modifying of foundry aluminum alloy АК9ч stages of calculation of one-factorial mathematical model by means of function «Trendline» the MS Excel 2010 programs were considered. Thus, using only one program for rather short time it was possible to execute the whole complex of calculations of one-factorial experiment, namely: to define average values of responses of each point and their confidential intervals, to pick up from several options the best mathematical model and to calculate its factors, to construct graphic dependence of process according to all requirements of normative documents, to check mathematical model on adequacy, to optimize mathematical model, to carry out forecasting by means of mathematical model. The given technique of processing of results of one-factorial experiment can be used by domestic researchers and experts for processing of results of any one-factorial experiments of metallurgical and not metallurgical processes. 2013 Article Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010 / Д.Ф. Чернега, В.Н. Рыбак // Металл и литье Украины. — 2013. — № 7. — С. 31-37. — Бібліогр.: 6 назв. — рос. 2077-1304 http://dspace.nbuv.gov.ua/handle/123456789/143753 621.74:519.25 ru Металл и литье Украины Фізико-технологічний інститут металів та сплавів НАН України
institution	Digital Library of Periodicals of National Academy of Sciences of Ukraine
collection	DSpace DC
language	Russian
description	Предложена методика обработки результатов однофакторных экспериментов с использованием программы Microsoft Excel 2010. На примере модифицирования литейного алюминиевого сплава АК9ч рассмотрены этапы расчета однофакторной математической модели при помощи функции «Линия тренда» программы MS Excel 2010. Применив всего одну программу, за относительно короткое время удалось выполнить целый комплекс расчетов однофакторного эксперимента, а именно: определить средние значения откликов каждой точки и их доверительные интервалы; подобрать из нескольких вариантов наилучшую математическую модель и рассчитать ее коэффициенты; построить графическую зависимость процесса в соответствии со всеми требованиями нормативных документов; проверить математическую модель на адекватность; провести оптимизацию математической модели и прогнозирование при ее помощи. Приведенную методику обработки результатов однофакторного эксперимента могут использовать отечественные исследователи и специалисты для обработки результатов любых однофакторных экспериментов металлургических и неметаллургических процессов.
format	Article
author	Чернега, Д.Ф. Рыбак, В.Н.
spellingShingle	Чернега, Д.Ф. Рыбак, В.Н. Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010 Металл и литье Украины
author_facet	Чернега, Д.Ф. Рыбак, В.Н.
author_sort	Чернега, Д.Ф.
title	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010
title_short	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010
title_full	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010
title_fullStr	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010
title_full_unstemmed	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010
title_sort	обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "линия тренда" программы microsoft excel 2010
publisher	Фізико-технологічний інститут металів та сплавів НАН України
publishDate	2013
url	http://dspace.nbuv.gov.ua/handle/123456789/143753
citation_txt	Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010 / Д.Ф. Чернега, В.Н. Рыбак // Металл и литье Украины. — 2013. — № 7. — С. 31-37. — Бібліогр.: 6 назв. — рос.
series	Металл и литье Украины
work_keys_str_mv	AT černegadf obrabotkarezulʹtatovodnofaktornogoéksperimentametallurgičeskogoprocessapripomoŝifunkciiliniâtrendaprogrammymicrosoftexcel2010 AT rybakvn obrabotkarezulʹtatovodnofaktornogoéksperimentametallurgičeskogoprocessapripomoŝifunkciiliniâtrendaprogrammymicrosoftexcel2010
first_indexed	2025-07-10T17:55:31Z
last_indexed	2025-07-10T17:55:31Z
_version_	1837283556859052032
fulltext	�0 �1МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�0 �1МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 УДК 621.74:519.25 Д. Ф. Чернега, В. Н. Рыбак Национальный технический университет Украины «КПИ», Киев Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции «Линия тренда» программы Microsoft Excel 2010 Предложена методика обработки результатов однофакторных экспериментов с использованием програм- мы Microsoft Excel 2010. На примере модифицирования литейного алюминиевого сплава АК9ч рассмотрены этапы расчета однофакторной математической модели при помощи функции «Линия тренда» программы MS Excel 2010. Применив всего одну программу, за относительно короткое время удалось выполнить целый комплекс расчетов однофакторного эксперимента, а именно: определить средние значения откликов каж- дой точки и их доверительные интервалы; подобрать из нескольких вариантов наилучшую математическую модель и рассчитать ее коэффициенты; построить графическую зависимость процесса в соответствии со всеми требованиями нормативных документов; проверить математическую модель на адекватность; провести оптимизацию математической модели и прогнозирование при ее помощи. Приведенную методику обработки результатов однофакторного эксперимента могут использовать отечественные исследователи и специалисты для обработки результатов любых однофакторных экспериментов металлургических и неметаллургических процессов. Ключевые слова: однофакторный эксперимент, Microsoft Excel 2010, математическая модель, доверительный интервал, адекватность, оптимизация, прогнозирование В ведение. Основная задача любого научного ис- следования состоит в том, чтобы на основе экс- периментальных данных некоторого процесса получить формулу (математическую модель), которая наилучшим образом описывала бы дан- ный процесс�� Полученную математическую модель можно использовать для нахождения оптимальных параметров процесса, исследования влияния разно- образных факторов на процесс, создания реального оборудования и т�� д�� Однофакторная математическая модель пред- ставляет собой зависимость одного отклика (зави- симого параметра) от одного фактора (независимого параметра) при постоянности других факторов (па- раметров)�� Например: зависимость относительного удлинения сплава от количества введенного в рас- плав модификатора при неизменной температуре модифицирования и разливки, времени выдержки и т�� д�� Процесс расчета любой математической модели является довольно трудоемкой работой, особен- но при наличии большого числа эксперименталь- ных данных�� Но при помощи пакета MS Excel 2010 и некоторых навыков работы на компьютере дан- ный процесс может занять всего несколько минут�� Кроме расчета математической модели, программа MS Excel 2010 дает возможность также проверить ее на адекватность (соответствие) и определить оптимальные параметры процесса�� Анализ результатов экспериментов последних лет, опубликованных в отечественных профессио- нальных журналах разнообразных направлений исследований, показал, что большинство ученых и специалистов не используют для обработки резуль- татов своих экспериментов такую мощную и удобную программу, как MS Excel 2010�� Поэтому знакомство отечественных ученых и специалистов с процедурой обработки результатов однофакторных эксперимен- тов металлургических и других процессов при помо- щи функции «Линия тренда» программы MS Excel 2010 – задача довольно актуальная�� Постановка задачи. Цель данной работы состоит в ознакомлении отечественных ученых и специалис- тов с процедурой обработки результатов однофак- торных экспериментов при помощи функции «Линия тренда» программы MS Excel 2010 на примере об- работки литейного алюминиевого сплава АК9ч мо- дификатором�� При этом, в данную задачу не входит обучение основам работы в MS Excel 2010�� Для по- лучения первичных навыков работы с программой рекомендовано ознакомиться с соответствующей литературой, например [1, 2]�� В табл�� 1 приведены экспериментально получен- ные зависимости временного сопротивления разры- ву алюминиевого сплава АК9ч от количества введен- ного в расплав модификатора�� Таблица 1 Результаты исследований Параметры номер опыта 1 2 3 4 5 6 7 8 Количество модификатора, % 0 0,10 0,17 0,28 0,31 0,35 0,39 0,47 Временное сопротивление разрыву, МПа 1 153 163 185 183 175 168 – 159 2 162 164 177 180 – 164 163 – 3 156 174 – 186 176 160 158 – 4 155 166 181 – – – 161 157 5 159 – 183 – 180 – 164 155 �2 ��МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�2 ��МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 У разрывной машины, при помощи которой про- водили исследования временного сопротивления разрыву, ошибка измерения составила ±3 % при до- верительной вероятности 95 %�� Как видно по табл�� 1, количество эксперименталь- ных точек равно 8, а параллельных исследований для разных точек – меняется от 3 до 5�� Итак, в работе поставлена задача найти мате- матическую модель данного металлургического про- цесса, проверить ее на адекватность и упростить в случае необходимости, а также выбрать опти- мальные параметры процесса (количество модифи- катора, при котором конечный сплав будет иметь максимальное значение временного сопротивления разрыву)�� Методика проведения расчетов Последовательность расчета любой однофактор- ной математической модели состоит из следующих этапов: – проведение серии исследований некоторого однофакторного процесса с целью получения зна- чений нескольких экспериментальных точек. На качество моделирования влияет общее количе- ство таких точек, а также количество параллельных исследований�� Чем больше количество экспери- ментальных точек и параллельных исследований в каждой точке, тем качественнее будет модель�� В любом случае количество экспериментальных то- чек не должно быть меньше пяти, а параллельных исследований в каждой точке – не меньше трех; – расчет математического ожидания. На дан- ном этапе необходимо рассчитать математические ожидания (средние значения) откликов параллель- ных исследований каждой из точек и построить гра- фик зависимости отклика от фактора (зависимого параметра от независимого); – определение доверительных интервалов. Рас- считали доверительные интервалы каждой из точек и нанесли их на график (имея право на ошибку – уровень значимости)�� Для технических расчетов уровень значимости обычно принимают в преде- лах 0,05…0,10 (95…90 % доверительной вероят- ности) [3]; – выбор общего вида математической модели. С помощью построенного графика на данном этапе необходимо определить общий вид функции (мате- матической модели)�� В большинстве случаев любую сложную функцию можно представить в виде суммы простых�� При определении общего вида матема- тической модели нужно помнить о том, что общее количество коэффициентов модели не должно пре- вышать количество экспериментальных точек�� Это означает, что если количество экспериментальных точек равно пяти, то математическая модель не мо- жет иметь более пяти коэффициентов, включая сво- бодный коэффициент [4]; – вычисление коэффициентов выбранной мате- матической модели при помощи одного из числен- ных методов. В большинстве случаев для расчета коэффициентов математической модели используют метод наименьших квадратов; – проверка адекватности полученной матема- тической модели. Для каждой точки на данном этапе рассчитывают значение зависимого параметра при помощи полученной математической модели, ко- торое сравнивают с соответствующим эксперимен- тальным значением�� В качестве критериев адекват- ности математической модели можно использовать критерии Фишера, Стьюдента, а также прохождение рассчитанной математической кривой в пределах границ доверительных интервалов�� В случае не- адекватности математической модели необходимо изменить ее общий вид или усложнить путем добав- ления дополнительных функций�� После этого пунк- ты 5 и 6 необходимо повторить еще раз; – упрощение математической модели. При получении адекватной, но сложной математиче- ской модели нужно ее упростить, исключая некото- рые простые функции (коэффициенты) с помощью критериев Стьюдента, после чего пункты 5-7 надо повторить [5]; – оптимизация полученной модели. Оптими- зацию конечной модели проводят с целью опреде- ления оптимальных параметров процесса, для ко- торого построена математическая модель, то есть значений независимого параметра (фактора), при котором достигается наибольшее (наименьшее) зна- чение зависимого параметра (отклика)�� Этот этап можно считать одним из основных этапов расчета математической модели, так как практически все исследования проводят именно для определения самых лучших параметров процесса [6]�� При использовании функции «Линия тренда» про- граммы MS Excel 2010 задача выбора общего вида математической модели значительно упрощается, а расчет коэффициентов происходит автоматически�� Недостатком данной функции является ограничен- ность в выборе вида математической модели�� Перед проведением расчетов в программе MS Ex- cel 2010 ее необходимо запустить и проверить уста- новку пакетов «Анализ данных» и «Поиск решения»�� Пакет «Анализ данных» используют для проверки адекватности модели, а пакет «Поиск решения» – для определения оптимальных параметров процесса�� В версии MS Excel 2010 пакеты «Анализ данных» и «Поиск решения» должны находиться в верхнем меню «Данные» вкладки «Анализ»�� Если данные па- кеты в меню «Данные» отсутствуют, надо запустить подменю «Файл» → «Параметры» → «Надстройки» и нажать на кнопку «Перейти»�� В открывшемся ме- ню нужно отметить надстройки «Пакет анализа» и «Поиск решения» и нажать на кнопку «Ok»�� После появления в меню «Данные» на вкладке «Анализ» пакетов «Анализ данных» и «Поиск решения» мож- но приступать к расчету математической модели�� Порядок проведения расчетов На первом этапе на новом листе рабочей книги MS Excel 2010 необходимо создать таблицу с экспе- риментальными данными (рис�� 1)�� Для наглядности, независимый параметр (коли- чество модификатора) обозначают X, зависимый (временное сопротивление разрыву) – Y1, Y2, Y3, �2 ��МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�2 ��МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 Y4, Y5 (по количеству параллельных исследований), а среднее арифметическое (математическое ожида- ние) – Y�� Затем добавляют еще пять столбиков для расчета относительной ошибки измерения разрыв- ной машины (относительная ошибка средства изме- рения (СИ)), абсолютной ошибки измерения разрыв- ной машины (абсолютная ошибка СИ), стандартного отклонения случайной величины (СВ), доверитель- ного интервала СВ и доверительного интервала ре- зультатов измерений (РИ)�� Далее вводят расчетные формулы в ячейки, на которые указывают сплошные стрелки, после чего введенные формулы копируют в ячейки, обозначенные пунктирными стрелками�� По полученным данным нужно построить гра- фик зависимости средних значений временного сопротивления разрыву от количества введенного в расплав модификатора и обозначить на нем до- верительные интервалы�� При построении графи- ка необходимо учитывать то, что шаг сетки должен быть одинаковым и кратным 1 ∙ 10n; 2 ∙ 10n или 5 ∙ 10n (n – натуральное число), а для этого выбрать коман- ду меню «Вставка» → «Точечная» → «Точечная с маркерами»�� Затем нажать на области графика пра- вой кнопкой мыши, выбрать команду «Выбрать дан- ные» и в открывшемся окне кликнуть на кнопку «До- бавить», ввести в поле «Значения X» диапазон ячеек C8:C15, в поле «Значение Y» – диапазон ячеек I8:I15 и нажать «Ok» (рис�� 2)�� Еще раз кликнув «Ok» получают график в виде маркеров (рис�� 3), который нужно отформатировать�� Для начала можно удалить надпись «Ряд 1» или по- менять ее название�� Далее нажать на правую кнопку мыши на горизонтальной оси X�� В контекстном ме- ню необходимо выбрать параметр «Добавить основные линии сет- ки»�� В случае необходимости мож- но поменять параметры маркеров�� Для этого надо дважды кликнуть по одному из маркеров�� В открывшем- ся меню нужно перейти на вкладку «Параметры маркера», установить опцию «Встроенный», выбрать ему тип и размер�� На вкладке «Заливка маркера» выбрать тип и цвет мар- кера, после чего нажать на кнопку «Закрыть»�� При необходимости можно из- менить шаг, минимальное и мак- симальное значения оси X и оси Y�� Для этого надо дважды кликнуть по нужной оси�� В данном случае изменять параметры оси X не нужно, а для оси Y расширить диапазон для того, чтобы доверитель- ные интервалы поместились на графике�� Чтобы из- менить параметры оси Y необходимо дважды клик- нуть по ней и в открывшемся меню установить нуж- ные параметры отображения (рис�� 4)�� После этого для каждого маркера нужно доба- вить доверительные интервалы, для чего клик- нуть левой кнопкой мыши на области диаграммы�� В верхней части документа появится меню «Работа с диаграммами», в котором надо выбрать параметр Рис. 1. Ввод экспериментальных данных в рабочий лист MS Excel 2010 120 Рис. 1. Ввод экспериментальных данных в рабочий лист MS Excel 2010 Рис. 2. Выбор источника данных для построения графикаРис. 2. Рис. 3. Рис. 2. Рис. 3. 185 180 175 170 165 160 155 0 0,1 0,2 0,3 0,4 0,5 Рис. 3. Начальный вид графика ♦ Ряд 1 Рис. 2. Рис. 3. Рис. 4. Меню форматирования оси Y �� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 «Макет» → «Планки погрешно- стей» → «Дополнительные пара- метры планок погрешностей»�� В параметре «Вертикальные план- ки погрешностей» нужно выбрать «Величина погрешности» → «Поль- зовательская» и нажать на кнопку «Укажите значение»�� В ячейках «Положительное значение ошиб- ки» и «Отрицательное значение ошибки» указать диапазон дове- рительных интервалов O8:O15 и нажать на кнопку «Ok»�� В случае надобности, на вкладке «Тип ли- нии» можно увеличить толщину линий планок ошибок�� После всех необходимых изменений нажать на кнопку «Закрыть»�� Если кроме вертикальных планок погрешнос- тей на графике появятся еще и го- ризонтальные, последние можно удалить, выбрав их кнопкой мыши и нажав на клавишу «Delete»�� Для добавления надписей под осями X и Y нужно кликнуть левой кнопкой мыши по области графика, в верхнем меню выбрать команду «Работа с диаграммами» → «Ма- кет» → «Названия осей» → «На- звание основной горизонтальной оси» → «Название под осью»�� То же самое повторить и для верти- кальной оси, выбрав команду «Ра- бота с диаграммами» → «Макет» → «Названия осей» → «Название основной вертикальной оси» → «Повернутое наз- вание»�� На графике возникнет два поля, в которые надо ввести соответствующие названия�� В результа- те проведенных операций появится график (рис�� 5)�� Для добавления линии тренда необходимо клик- нуть правой кнопкой мыши по одной из точек и в контекстном меню выбрать команду «Добавить ли- нию тренда», после чего можно будет увидеть меню (рис�� 6)�� В меню выбора линии тренда нужно выбрать вид зависимости, которая наилучшим образом подходит для описания процесса и обязательно отметить оп- ции «Показывать уравнение на диаграмме» и «По- местить на диаграмму величину достоверности ап- проксимации (R^2)»�� После этого нажать на кнопку «Закрыть»�� На графике появится выбранная линия тренда, уравнение линии и величина достоверно- сти аппроксимации�� В случае неудовлетворительной аппроксимации линию тренда можно удалить, клик- нув по ней правой кнопкой мыши и нажав на кнопку «Delete»�� Эту операцию также можно осуществить через контекстное меню, которое вызывают нажати- ем правой кнопкой мыши по линии тренда и выбором команды «Удалить»�� При возникновении сложностей с выбором наи- лучшей зависимости можно перебрать все и ос- тавить свой выбор на той, которая соответствует следующим критериям: величина достоверности аппроксимации максимально приближена к 1; линия тренда довольно близко проходит к эксперимен- тальным точкам и не выходит за границы их дове- рительных интервалов; формула математической модели является простой�� Таблица 2 Параметры разных функций номер зависимости Зависимость Величина достоверности аппроксимации (R2) линия тренда в доверительных интервалах Сложность формулы 1 экспоненциальная 0,006 нет средняя 2 линейная 0,0061 нет простая 3 логарифмическая – – средняя 4 полиномиальная (степень) 2 0,763 нет средняя 5 3 0,7835 да сложная 6 4 0,9722 да сложная 7 5 0,9757 да очень сложная 8 6 0,9949 да очень сложная 190 185 180 175 170 165 160 155 150 0 0,1 0,2 0,3 0,4 0,5 Рис. 5. График в виде экспериментальных точек В ре м ен но е со пр от ив ле ни е ра зр ы ву , М П а Количество модификатора, % Рис. 6. Меню выбора линии тренда �� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 В данном случае при переборе зависимостей получили следующие результаты�� Указанным выше критериям соответствуют за- висимости № 5-8, а № 3 и 9 рассчитать не удалось из-за наличия среди аргументов X нулевого зна- чения�� Наиболее простой среди зависимостей № 5-8 является зависимость № 5 (полиномиальная зависимость со степенью 3), график которой пред- ставлен на рис�� 7�� Таким образом, полученная ма- тематическая модель описывается уравнением (1): y = 513,32 x3 – 796,17x2 + 259,64x + 154,92, (1) где y – временное сопротивление разрыву сплава АК9ч, МПа; x – количество введенного в расплав мо- дификатора, %�� Для проверки адекватности полученной матема- тической модели необходимо рассчитать значения, которые она возвращает в известных точках�� Для этого нужно добавить таблицу для рассчитанных значений, таблицу с коэффициентами, ввести фор- мулу математической модели и скопировать ее для всех известных точек (рис�� 8)�� Далее надо вызвать меню «Дан- ные» → «Анализ данных» → «Двухвыборочный F-тест для дисперсии»�� В поле «Интервал переменной 1» ввести диапазон ячеек Q8:Q15, в поле «Интервал переменной 2» – диапазон ячеек I8:I15, выбрать α = 0,05 и нажать кнопку «Ok»�� Появится новый лист (рис�� 9), на котором нужно найти значение F-критерия (F ≈ 0,79) и F-критическое (Fкр ≈ 0,26)�� В случае F < Fкр дисперсии считаются одинаковыми, а при F ≥ Fкр – разными�� Далее вызывают меню «Дан- ные» → «Анализ данных» → «Двух- выборочный t-тест с разными дис- персиями» (в случае F < Fкр – с одинаковыми)�� В поле «Интервал переменной 1» необходимо ввести диапазон ячеек Q8:Q15, а в поле «Интервал переменной 2» – диапазон ячеек I8:I15 и нажать «Ok»�� Появится новый лист (рис�� 10), на котором нужно найти значение t-статистики (t ≈ 0,006) и t-критическое (tкр ≈ 1,76)�� При /t/ < tкр полученная математическая модель считается адекватной, а в случае /t/ ≥ tкр – неадек- ватной�� В случае неадекватности математической модели надо выбрать более сложную зависимость (с большим значением величины достоверности ап- проксимации) или провести большее количество ис- следований�� 190 185 180 175 170 165 160 155 150 0 0,1 0,2 0,3 0,4 0,5 Рис. 7. Полиномиальная зависимость со степенью 3 В ре м ен но е со пр от ив ле ни е ра зр ы ву , М П а Количество модификатора, % Рис. 8. Таблица значений, рассчитанных при помощи получен- ной математической модели Рис. 9. Расчет F-критерия Рис. 10. Расчет t-статистики �� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 В данном случае модель является адекватной и не требует упрощения, потому что линия тренда полиномиальной зависимости второй степени уже не попадает в доверительные интервалы�� Как видно на рис�� 7, математическая модель до- стигает максимального значения временного со- противления разрыву в диапазоне от 0,1 до 0,3 % модификатора�� Для более точного определения оп- тимальных параметров обработки нужно провести оптимизацию данного процесса�� Для этого на том же самом листе создают дополнительную таблицу и вводят в нее необходимые формулы (рис�� 11)�� После проведенных действий вызывают меню «Данные» → «Поиск решения»�� В поле «Оптими- зировать целевую функцию» вводят координаты ячейки Q18, выбирают опцию «Максимум», в поле «Изменяя ячейки переменных» – координаты ячей- ки O18�� Также надо добавить 2 ограничения поиска оптимального фактора x (x ≥ xmin; x ≤ xmax) и нажать кнопку «Найти решение» (рис�� 12)�� В окне появится оптимальное значение количе- ства модификатора (x = 0,203 %), при котором вре- менное сопротивление разрыву сплава АК9ч будет достигать максимального значения – 179,1 МПа (рис�� 13)�� Используя полученную математическую модель можно также проводить прогнозирование времен- ного сопротивления разрыву для любого количе- ства модификатора в диапазоне от 0 до 0,47 %�� Для этого достаточно записать в ячейку O18 необходи- мое значение количества модификатора, и в ячей- ке Q18 автоматически появится рассчитанное зна- чение временного сопротивления разрыву�� Напри- мер, введя в ячейку O18 значение x = 0,05, в ячейке Q18 получают значение y = 165,98 МПа (рис�� 14)�� Выводы На примере модифицирования литейного алю- миниевого сплава АК9ч рассмотрены этапы расчета однофакторной математической модели при помощи функции «Линия тренда» программы MS Excel 2010�� При этом, используя всего одну программу, за отно- сительно короткое время выполнили целый комплекс расчетов однофакторного эксперимента, а именно: определили средние значения откликов каждой точ- ки и их доверительные интервалы; отобрали среди нескольких вариантов наилучшую математическую модель и рассчитали ее коэффициенты; построили графическую зависимость процесса в соответствии со всеми требованиями нормативных документов; проверили математическую модель на адекватность; провели оптимизацию математической модели и ее прогнозирование с помощью математической мо- дели�� Таким образом, приведенную методику можно ис- пользовать для обработки результатов любых одно- факторных экспериментов�� Рис. 11. Подготовка математической модели к оптимизации Рис. 12. Установка параметров оптимизации Рис. 13. Результат оптимизации Рис. 14. Результат прогнозирования �� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013�� МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 МЕТАЛЛ И ЛИТЬЕ УКРАИНЫ № 7 (242) ’2013 ЛИТЕРАТУРА 1�� Волков В. Б. Понятный самоучитель Excel 2010�� – СПб��: Питер, 2010�� – 256 с�� 2�� Microsoft Excel 2010 для квалифицированного пользователя: Учеб�� пособие�� – М��: Академия Айти, 2011�� – 244 с�� 3�� Митин И. В., Русаков В. С. Анализ и обработка экспериментальных данных: Учеб��-метод�� пособие для студентов младших курсов�� – М��: НЭВЦ ФИПТ, 1998�� – 48 с�� 4�� Светозаров В. В. Основы статистической обработки результатов измерений: Учеб�� пособие�� – М��: МИФИ, 2005�� – 40 с�� 5�� Берк К., Кэйри П. Анализ данных с помощью Microsoft Excel: Пер�� с англ�� – М��: Издательский дом «Вильямс», 2005�� – 560 с�� 6�� Данилин Г. А., Курзина В. М., Курзин П. А. Математическое программирование с Excel: Учеб�� пособие для всех специальностей МГУЛа�� – М��: МГУЛ, 2005�� – 113 с�� Чернега Д. Ф., Рибак В. М. Обробка результатів однофакторного експерименту металургійного процесу за допомогою функції «Лінія тренду» програми MS Excel 2010 Запропоновано методику обробки результатів однофакторних експериментів з використанням програми Microsoft Excel 2010. На прикладі модифікування ливарного алюмінієвого сплаву АК9ч розглянуто етапи розрахунку однофак- торної математичної моделі за допомогою функції «Лінія тренду» програми MS Excel 2010. Застосувавши всього лише одну програму, за відносно короткий час виконали цілий комплекс розрахунків однофакторного експеримен- ту, а саме: визначили середні значення відгуків кожної точки та їх довірчі інтервали; відібрали із декількох варіантів найкращу математичну модель та розрахували її коефіцієнти; побудували графічну залежність процесу у відповід- ності до всіх вимог нормативних документів; перевірили математичну модель на адекватність; провели оптимізацію математичної моделі та прогнозування за її допомогою. Наведену методику обробки результатів однофакторного експерименту можуть використовувати вітчизняні науковці та фахівці для обробки результатів будь-яких однофак- торних експериментів металургійних і неметалургійних процесів. Анотація one-factorial experiment, microsoft excel 2010, matehematical model, confidential in- terval, adefuacy, optimization, predictionKeywords Chernega D. F., Rybak V. N. Processing of results of one-factorial experiment of metallurgical process by means of function «Trendline» of the Microsoft Excel 2010 program The technique of processing of results of one-factorial experiments by means of the Microsoft Excel 2010 program is offered. On an example of modifying of foundry aluminum alloy АК9ч stages of calculation of one-factorial mathematical model by means of function «Trendline» the MS Excel 2010 programs were considered. Thus, using only one program for rather short time it was possible to execute the whole complex of calculations of one-factorial experiment, namely: to define average values of responses of each point and their confidential intervals, to pick up from several options the best mathematical model and to calculate its factors, to construct graphic dependence of process according to all require- ments of normative documents, to check mathematical model on adequacy, to optimize mathematical model, to carry out forecasting by means of mathematical model. The given technique of processing of results of one-factorial experiment can be used by domestic researchers and experts for processing of results of any one-factorial experiments of metal- lurgical and not metallurgical processes. Summary Поступила 30��07��13 однофакторний експеримент, Мicrosoft Excel 2010, математична модель, довірчий інтервал, адекватність, оптимізація, прогнозуванняКлючові слова

Обработка результатов однофакторного эксперимента металлургического процесса при помощи функции "Линия тренда" программы Microsoft Excel 2010

Репозитарії

Схожі ресурси