Планируемый эксперимент с использованием надстройки «PLExp»
Н.Ю. Батурина Донской государственный технический университет, Ростов-на-Дону
Аннотация: Разработано приложение «PLExp», являющееся надстройкой для Excel и Access на базе VBA. Приложение предназначено для автоматизации планирования и проведения многофакторных экспериментов, включающее следующие блоки: планирование; проведение эксперимента; регрессионный и дисперсионный анализ; прогнозирование; оценка погрешностей. Применение приложения позволяет повысить эффективности исследований, связанных с проведением натурных или модельных экспериментов, так как дает возможность уменьшить количество требуемых экспериментов, автоматизировать процессы хранения, извлечения, обработки информации и отыскания оптимальных решений.
Ключевые слова: планирование, фактор, эксперимент, приложение, VBA, регрессия, дисперсия, прогноз, ошибка аппроксимации, функция отклика, запрос, моделирование.
Несмотря на разработанность методов и подходов к планируемым экспериментам [1-3], в последнее время сохраняется поток публикаций, посвященных применению этих методов в разных областях производства, экономики, управления [4-8], включая вычислительные эксперименты на моделях. Является естественным изменение направленности исследований. Первоначально ориентированные на дорогостоящие натурные эксперименты, в настоящее время методы планирования экспериментов развиваются благодаря применению информационных технологий, автоматизации вычислений.
В исследованиях используются различные вычислительные инструменты и инструменты анализа от стандартных математических и статистических пакетов общего назначения (MATHCAD, S T ATIS TIC A, S T ADIA, STATGRAPHICS и др.) до специализированных авторских пакетов, как правило, разработанных на одном из объектно-ориентированных языков, ориентированных на выбранную предметную область [3,6,8,9]. Необходимость обращения к стандартным статистическим пакетам, например, таким как STATISTICA[9], обусловлена тем, набор статистических функций MicrosoftExcelне позволяет решать весь комплекс задач, связанных с плани-
рованием экспериментов. Однако не всегда имеется возможность использования статистических пакетов из-за лицензионных требований. Кроме того, возможности этих пакетов обширны и зачастую даже избыточны, так как они не ориентированы исключительно на планирование эксперимента, а позволяют решать широкий круг задач. Вместе с тем Microsoft Office, включающий Excel,Access, встроенный язык VBA, есть практически на каждом компьютере. При проведении экспериментов, не привязываясь к какой-либо предметной области, на первый план выходит проблема автоматизации вычислений, обработки и хранения результатов экспериментов. Специфика расчетов в этой области такова, что их удобно проводить в Excel, а для хранения результатов и исходных данных использовать Access. Возможности VBAкак объектно-ориентированного языка позволяют создать удобную надстройку для автоматизации расчетов и обмена данными.
Анализ публикаций, относящихся к автоматизации планирования экспериментов, показал, что исследования, касающиеся разработки приложений средствами Excel,Access,VBA, практически отсутствуют. Автору представляется это неоправданным, так как это наиболее доступные для пользователей инструменты и вместе с тем достаточные для решения поставленных задач.
Предлагаемый автором подход описан в ранее опубликованной работе [10]. Исследования в направлении совершенствования приложения продолжаются (использование различных планов, совершенствование методов регрессионного и дисперсионного анализа). Приложение тестируется на примерах их из разных областей: исследовались результаты виброакустических измерений [10, 11], имитационные модели систем массового обслуживания.
В исследованиях, проводимых ранее, значимость уравнения регрессии в целом оценивалась по критерию Фишера. Анализ полученных регрессионных моделей показал недостаточность этих оценок и необходимость включе-
м
ния дополнительного критерия. В качестве такого критерия выбрана средняя ошибка аппроксимации, вычисляемая по формуле
ЕггогАрд =
У'-у
]ехр
у]ехр
Кроме того, добавлен дополнительный блок оценки погрешностей, позволяющий по любому количеству экспериментальных данных проверить их соответствие полученной регрессионной модели по критериям Фишера и средней ошибки аппроксимации (рис. 1). Результаты оценки сохраняются в таблице ЕКЯОКбазы данных, в которой формируется запись, включающая уникальный ГО, количество экспериментов, значения критериев и оценки значимости уравнения.
Рис. 1. - Блок «Оценка погрешностей» при двух факторах Разработанное приложение предназначено для решения комплекса задач. После ввода исходных данных каждая из задач может выполняться независимо. Приведем описание основных задач, решаемых с помощью приложения.
1. Выбирается количество факторов и задаются диапазоны изменения факторов, по ним рассчитываются точки плана для проведения экспе-
риментов (группа экспериментов), результаты заносятся в базу Access (форма «Планирование эксперимента»).
2. В точках выбранного плана проводятся эксперименты, результаты (значения функций отклика-критериев) передаются в Access, а затем в Ехсе1 (форма «Проведение эксперимента»).
3. По выбранной группе экспериментов строится регрессионная модель в виде аппроксимационного полинома (используются центральное композиционное или ротатабельное планирование второго порядка), оценивается ее качество по критерию Фишера и средней ошибке аппроксимации в точках плана, результаты передаются в Access (форма «Регрессионный и дисперсионный анализ» ).
4. Задаются произвольные значения факторов для выбранной модели в пределах заданных диапазонов, рассчитываются прогнозируемые значения критериев, результаты передаются в Access (форма «Прогнозирование»).
5. По выбранной группе экспериментов (модели) задаются произвольные точки со значениями факторов в пределах заданных диапазонов, оценивается ее качество по критерию Фишера и средней ошибке аппроксимации в точках плана, результаты передаются в Access (форма «Оценка погрешности»).
Перечисленные задачи относятся к случаю, когда изначально известны диапазоны изменения факторов и эксперименты требуется спланировать и провести. Возможна другая постановка задачи (прогностическая), когда эксперименты в достаточном количестве уже проведены и диапазоны изменения факторов можно определить. Требуется построить модель, которая позволяет прогнозировать значения функций отклика при произвольных значениях факторов в пределах выбранных диапазонов, а также оценивать качество модели.
■ Инженерный вестник Дона, №2(2017) ivdon.ru/ru/magazine/arcliive/xxx2017/4177
Такая задача решается с помощью приложения следующим образом. В АссеББпередается весь набор данных экспериментов. С помощью запросов на обновление, выборку и объединение формируются точки плана и значения критериев в них. Далее решается весь комплекс задач, описанных выше.
bÉ V к'
а Все таблицы т «
Поиск...
Ьггог2 й
+¡i*| FifOrt
qrexp? Я
i gnxp'¿
result2 Я
result2
Перемещен ияО ü й
^^ Пгргигир'нииО/} : i.ifuiHi^i .. *T 0бновленнеПерем«щинаЦ0 9 XPLN2 u V
XPLN2_U00102_095 Я
XPLN2 U00102 095 ОбновлениеХР1.М2_ииаииС1КЦ... Перемещен им 1 b й
^^ Пгргысидгнки! Ji : i.ifuiHi^i .. *T ОбновлениеПерем«щииаи1 5 XPLN2 u001006 Oüb й
XPlN?_uí»1006_005 f ОбновленмеХР1.М2 lAaUOOlOO... XPLN2 UOOW12 ОУЬ й
+¡i*| XPJN?_U00fi01?_(W!5
f ОбновленмеХР1.М2 <AaU00601... X1]LN2 UÜ1202 Oüb й
XPJN7_u0t?0?.(M5 f ОбновленмеХР1.М2 <AaU01202... Перемещении й
Рис. 2. - Определение значений критерия по запросам
Рассматривалась прогностическая задача по обработке результатов виброакустических измерений. Были проведены замеры перемещений и в разные моменты времени (первый фактор-параметр Х1 — t) при различном расположении трещины относительно края плиты (второй фактор-параметр Х2 — а). Общее количество замеров (экспериментальных точек) равнялось 200.
На рис. 2 представлена таблица экспериментальных значений, извлеченных из таблицы «Перемещения0_9» с использованием запросов.
Установлено, что не удается получить описание перемещений и, представленных двухстами точками с диапазоном изменения факторов t 6 [0,001; 0,02], а е [0; 0,95] с помощью одной регрессионной модели. Опреде-
YPIN_U2_0J>\ number - Avg-u
1 -0,00000091 Л7М
? -O,OOOOOO.W^fil
3 -0,00000091 2Q56 Л -0,00000039541? 5 -0,00000101 ?79 G -0,00000043751ÍV
7 -о,оооооосо??яя
R -0,0000006005
4 -o,ooooooG<i5G57
лены допустимые пределы изменения фактора ¿для получения регрессионных моделей со значением средней ошибки аппроксимации < 8%: [0,001; 0,006], [0,006; 0,012], [0,012; 0,02]. На Рис.3 представлен отчет, формируемый в Access, содержащий коэффициенты аппроксимационных полиномов и оценки значимости в целом соответствующих уравнений регрессии.
Значения коэффициентов "
—1 17:57:01
IP ысш bint ыDj с 10.; ым bias
и00102_.1»Ь -3,49 Ё-07 '9,67E-08 5,13 E-10 1.65E-10 5Д2Ё-0? 4,<WE'0S ЗН-ЗЧ no Фишеру
uOOtOOG^OiS ■G.10F07 •5,10F-07 B.G4E 10 7.74F tO 7.53E 08 5,34E'0S Фишеру
U0O6OI2_O95 -e.Sfif-07 2r37E-0? 1.30F-D9 -3,33C-W -7,J5E-09 М7С-0Й Фишеру
□01702.095 -4.33F4J7 l,7GE-07 6,S9f'10 -i,65f-oa 4,J4E-0B Фишеру
tr2_712j(10 9,9flt+0l ar33£-oi -В, 33E-01 lr25t+00 -8ДЗЕ-01 -8,33E-0l не shot пи-Фнше|»у
tr2_712_614 9jWE+Ql 4,i7E'00 4,50E»00 S.OOEtOO 3,83£tGO 2.83E • 00 не эиач oot^mepv
tr?_713_937 9,361401 7,67E+00 -S,67E+00 6,50 e+OQ -6rb7J4G0 1.Л1Г+00 Фишеру
tf2_712J320 Sj7JE+01 1,33E»01 ^,17EtOO 4,75 E+00 7f67E+00 ■3jl7E+00 ан4Ч ПО Фишеру
u001005_09b -5,36 i-07 7, new 6,«E-10 1,606-09 4r44E-08 Фишеру
U001004 09S ■S,3SE'07 •4tS4E-0 7 7,31E-10 6,65£10 l,60t 08 4,44E-03 эжн по Фиш«ру
Рис. 3. -Формирование отчета по коэффициентам выбранных регрессионных
моделей Литература
1. Налимов В. Н.,Чернова Н. А. Статистические методы планирования экстремальных экспериментов. М.: Наука, 1965. 340 с.
2. Melas V.B., Pepelyshev A.N., Cheng R.C.H. Designs for estimating an extremal point of quadratic regression models in a hyperball // Metrika, 2003, № 58. pp. 193-208.
3. Мелас В.Б. О работах по математической теории планирования эксперимента URL: statmod.ru/vega/melas/melas.htm (дата обращения: 22.05.2017).
4. Яценко Н.Д.,Спасибова В.С., Закарлюка С.Г., Гончаров И.А., Яцен-коА.И. Разработка составов фриттованных глазурей для керамической черепицы с применением метода математического планирования эксперимента-инноваций // Инженерный вестник Дона, 2016, №4 URL: ivdon.ru/ru/magazine/archive/n4y2016/3889/.
* Инженерный вестник Дона, №2(2017) ivdon.ru/ru/magazine/arcliive/xxx2017/4177
5. Инюшкин Н.В. Щелчков И.П., Аитова А.И., Шевченко Е.А., Маньков М.Г., Перфилов С.А., Токарева Н.А.Планирование эксперимента по улавливанию летучей золы ТЭС в электроциклоне// Инженерный вестник Дона, 2013, №2 URL: ivdon.ru/ru/magazine/archive/n2y2013/1603/.
6. ЛощевА.Г. Оптимизация технологических комплексов на основе методов планирования эксперимента: дис. ... канд. техн. Наук. М., 2010. 225 с.
7. Нечаев К.Н. Теория планирования многофакторных экспериментов -резерв повышения эффективности технологических процессов // Российский научно-технический журнал "Инструмент и технологии", 2008, №23. С. 140145.
8. Евельсон Л.И., Заикин А.Н., Рыжикова Е.Г. Комплекс программ «Коп-тРЕГ» // Информационные технологии в науке, образовании и производстве. Брянск: БГИТА, 2014. С. 103-106.
9. Продукты линейки STATISTICAURL: statsoft.ru/products(дата обращения: 22.05.2017).
10. Батурина Н.Ю. Автоматизация планирования эксперимента // Международный научно-исследовательский журнал,2016, № 11-4. С. 14-17.
11. Uglova E. V., TiraturyanA. N. ,LiapinA. A. Integrated approach to studying characteristics of dynamic deformation on flexible pavement surface using nondestructive testing // PNRPU Mechanics Bulletin, 2016, №2. pp. 111-130.
References
1. Nalimov V. N., Chernova N. A. Statisticheskie metody planirovanija jek-stremal'nyh jeksperimentov [Statistical methods of planning of extreme experiments]. M.: Nauka, 1965. 340 p.
2. Melas V.B., Pepelyshev A.N., Cheng R.C.H. Designs for estimating an extremal point of quadratic regression models in a hyperball.Metrika, 2003, № 58. pp. 193-208.
■ Инженерный вестник Дона, №2(2017) ivdon.ru/ru/magazine/arcliive/xxx2017/4177
3. Melas V.B. o rabotah po matematicheskoj teorii planirovanija jeksperi-menta URL: statmod.ru.vega.melas.melas.htm (data obrashhenija: 22.05.2017).
4. Jacenko N.D., Spasibova V.S., Zakarljuka S.G., Goncharov I.A., Jacenko A.I. Inzenernyj vestnik Dona (Rus), 2016, №4 URL: iv-don.ru.magazine.archive.n4y2016.3889.
5. Injushkin N.V. Shhelchkov I.P., Aitova A.I., Shevchenko E.A., Man'kov M.G., Perfilov S.A., Tokareva N.A. Inzenernyj vestnik Dona (Rus), 2013, №2 URL: ivdon.ru.magazine.archive.n2y2013.1603.
6. Loshhev A.G. Optimizacijatehnologicheskihkompleksovnaosnovemeto-dovplanirovanijajeksperimenta [Optimization of technological complexes on the basis of methods of planning of an experiment]: dis. ... kand. tehn. Nauk. M., 2010. 225 p.
7. Nechaev K.N. Rossijskij nauchno-tehnicheskij zhurnal "Instrument itehno-logii", 2008, №23. P. 140-145.
8. Evel'son L.I.,Zaikin A.N., Ryzhikova E.G. Informacionnye tehnologii v nauke, obrazovanii i proizvodstve [Information technologies in science, education and production]. Brjansk: BGITA, 2014. P. 103-106.
9. ProduktylinejkiSTATISTICA URL: statsoft.ru.products (data obrashhenija: 22.05.2017).
10. BaturinaN.Ju. Mezhdu-narodnyjnauchno-issledovatel'skij zhurnal, 2016, № 11-4. p. 14-17.
11. Uglova E. V., Tiraturyan A. N. ,LiapinA. A. Integrated approach to studying characteristics of dynamic deformation on flexible pavement surface using nondestructive testing. PNRPU Mechanics Bulletin, 2016, № 2. pp. 111-130.