Методика использования MS excel для расчета уравнений регрессии полного факторного эксперимента при исследовании свойств жидкостекольных смесей Текст научной статьи по специальности «Нанотехнологии»

дгггг^ г: кгцумттг. /907

-3 (67), 2012 / ЛАМ Л

It is shown that the use of MS Excel enables reduce time to find the regression coefficients in the calculations of similar matrices, and also simplifies the process of charting the influence of factors on the studied properties of the experiments.

С. Д. ДИНГИЛЕВСКИй, бнту

Научный руководитель Ю. Ю. ГУМИНСКИй, БНТУ

УДК 004. (07.07)

методика использования ms excel для расчета уравнений регрессии полного факторного эксперимента при исследовании свойств жидкостекольных смесей

Метод расчета уравнений регрессии полного факторного эксперимента разработан для исследования технологических свойств жидкостекольных формовочных и стержневых смесей.

Для оптимизации и упорядочения проведения экспериментов была построена матрица планирования полного факторного эксперимента для жид-костекольных формовочных смесей. Уровни факторов и интервалы варьирования приведены в таблице.

Таблица

Фактор Уровень Интервал варьирования Размерность

-1 0 +1

xi 180 210 240 30 °С

х2 10 20 30 10 мин

х3 3 6 9 3 мин

х4 3,5 5,0 6,5 1,5 %

х5 0,1 0,3 0,5 0,2 %

ж к ч • □

Буфер обмена г„ \_Шрифт

П р и м е ч а н и е: х0 - фактор, необходимый для расчетов коэффициентов уравнения; xj - температура сушки образца в печи; х2 - время выдержки образца в печи; х3 - время перемешивания компонентов в смесителе; х4 - содержание жидкого стекла в смеси, %; х5 - содержание наноструктурирован-ного порошка в смеси, %.

В ходе экспериментов испытывали образцы на прочность при растяжении Y, МПа. Матрица планирования экспериментов 25 была построена в соответствии с рекомендациями [1]. При построении план-матрицы эксперимента выполняли кодирование переменных факторов. В MS Excel по результатам опытов строилась таблица (рис. 1).

Также была построена таблица (рис. 2), где определялся знак («+» или «-»), проставляемый

перед значениями результатов опыта, для нахождения определенного коэффициента [2].

Если высчитывался коэффициент регрессии 50, то значения приобретали только положительный знак. Для знак поочередно менялся с «+» на «-» и т. д. Для коэффициентов комбинированного влияния факторов, таких, как В12, В23...В12345 знаки находились путем перемножения соответствующих ячеек. Так, например, для х12 перемножались первые ячейки столбцов Х1 и Х2 (рис. 2), для Х123 - первые ячейки столбцов Х1, Х2, Х3 и т. д. [3].

Например, для х12 формула имеет вид:

= В39С39. (1)

Знаки в остальных ячейках столбцов получали протягиванием первых ячеек.

Далее строили еще 29 таблиц (для матрицы 25), аналогичных таблице, показанной на рис. 1. Значения в таблицах получали путем перемножения ячеек столбца с результатами опыта в таблице, показанной на рис. 1, с ячейками соответствующих столбцов в таблице, представленной на рис. 2.

С7 -С"

А В

1 Ns Опыта Прочность

2 1 0,69

3 2 0,72

4 3 0.77

5 4 0,49

6 5 0,85

7 6 0,97

8 7 0,61

9 8 0,28

10 9 0,32

11 10 0,43

12 11 0,39

13 12 0.22

14 13 0,34

15 14 0,45

16 15 0,42

17 16 0,24

18 17 1,43

19 18 1,04

20 19 0.75

21 20 0,38

22 21 1,74

23 22 1,29

24 23 0,96

25 24 0,38

26 25 0,31

27 26 0,41

28 27 0,41

29 28 0,22

30 29 0,4

31 30 0,39

32 31 0,49

33 32 0,51

34 33 0.8

35 34 0,77

36 В. 0,603125

37

14 > и Лист! , Лист2 /ЛисгЗ

Готово |

Рис. 1. Результаты опытов

Ш/г: гл^ггтллтгггггт

/ 3 (67), 2012-

Е 1 Главная Вставка Разметка страницы Формулы Данные Рецензирование Вид Надстройки й О □ ^

--Н *

* СаМЬп |11 - А* а' а' Перенос текста • а§ ю 1 а Й- в« а 1 1Й

Вставить ^ ж к н - □ • а»- д- =: ш := Объединить и поместит ь.цгтр • 1 9 . «/„ идо *.о .00 Условное Форматировать Стили Вставить Удалить Формат Сортировка Найти и

Буфер обмена п Шрифт г; | Выравнивание ■-■1 Число г. Стили Ячейки Редактирование

Р29

А В С О Е ? в н 1 J К 1. м N О р О К в

38 N4 опыта XI Х2 ХЗ Х4 Х5 Х1*Х2 Х2*ХЗ ХЗ*Х4 Х4*Х5 Х1-ХЗ Х1*Х4 Х1*Х5 Х2*Х4 Х2'Х5 ХЗ*Х5 Х1*Х2*ХЗ Х2*ХЗ*Х4 ХЗ*Х4*Х5

39 1 * * ♦ ♦ ♦ ♦ ♦ ♦ ♦ * * ♦

40 2 ♦ ♦ ♦ * * * *

41 3 ♦ ♦ ♦ ♦ * ♦ *

42 4 ♦ ♦ ♦ ♦ ♦ *

43 5 * * ♦ * *

44 6 ♦ ♦ * ♦ *

45 7 * ♦

46 8 ♦ + ♦ *

47 9 * ♦ * ♦ ♦

48 10 * ♦ * ♦

49 11 ♦ * * * *

50 12 * ♦ * ♦

51 13 * * ♦ ♦ *

52 14 * + * * *

53 15 ♦ ♦ ♦ *

54 16 ♦ * ♦ ♦ ♦ ♦ *

55 17 * * * ♦ * * *

56 18 ♦ ♦ ♦

57 19 * ♦ ♦

58 20 ♦ ♦ *

59 21 * ♦ * * *

60 22 * * ♦ ♦ * *

61 23 * * *

62 24 * ♦ ♦ ♦ ♦

63 25 * * * * ♦

64 26 * ♦ *

65 27 ♦ ♦ * *

66 28 ♦ * ♦ *

67 29 * * *

68 30 * ♦ ♦

69 31 ♦ *

70 32 * ♦ ♦ ♦

Рис. 2. Знаки для нахождения коэффициентов регрессии

Главная Вставка Разметка страницы Формулы Данные Рецензирование Вид Надстройки

в Вставить . - & Буфер обмена ь Cal.br, -11 - А* А* = = =■ ¡З^ Перенос текста Ш Ш Щ ^ ^ Объединить и поместить в центре " Выравнивание г* Общий т т т Условное Форматировать Стили форматирование" как таблицу» ячеек" Стили 5 Ш Е Вставить Уд; Яч.

ж к ч • Ш • <2»- Д • Шрифт г. ^ • % ООО *6°о Число Ъ

их Ч- «I

Л А В С 0 Е Р б н 1

72

73 Уровни XI Х2 ХЗ Х4 Х5 № Прочность

74 -1 180 10 3 3,5 0,1 1 0,363

75 0 210 20 6 5 0,3 0,47

76 1 240 30 9 6,5 0,5 0,577

77 78

-1 2 0, 372 603

79 80 81 0 0,

1 0, 834

82 -1 3 0, 381 736

83 84 0 0,

1 1,091

Рис. 3. Результаты расчетов У

В таблице для коэффициента регрессии В1 формула (1), имеет следующий вид:

= В2В39. (2)

Коэффициент регрессии рассчитывали по формуле для определения среднего значения. Например, таблице для В0 данная формула имеет вид:

= СРЗНАЧ(£2 : ад (3)

Для нахождения коэффициентов Вь В2, ..., В12345 проводили аналогичные действия.

После определения коэффициентов В0, В2, ..., В12345 строили уравнение регрессии, где коэффициенты В1, В2, ..., В12345 сравнивали с коэффици-

ентом Во. Коэффициенты, значения которых были меньше В00 на 2 порядка и более, исключали.

В результате было получено следующее уравнение регрессии:

Y = 0,603 + 0,133х2 + 0,231х4 + 0,124х2х4. (4)

По уравнению (4) определяли фактор, который наиболее существенно влияет на прочность образцов при разрыве. Этим фактором оказался х4 (содержание жидкого стекла, %).

Графики строили на основании данного фактора, вставляя поочередно на место х1, х2, х3, х4, х5 значения уровней. Сначала всем факторам, кроме х4, задавали значения только «-1», далее «0» и «1».

_г г^шгггптг /ооо

-3 (67),2012 I ЪЧМчМ

Главная ] вставка Разметка страницы Формулы Данные Рецензирование Вид Надстройки

Рис. 4. Графики прочности жидкостекольных смесей

На одном графике меняли значения Х4 от -1 до 1. Уравнение (4) в ячейку 174 (рис. 3) записывалось в виде:

= 0,603 + 0,133-$Л$74 + 0,23Ы74 +

0,124-$Л$74-Л74. (5)

Результаты расчетов приведены на рис. 3. На основании результатов вычисления были построены графики для исследуемого свойства жидкостекольной смеси (рис. 4).

При увеличении количества исследуемых свойств для каждого свойства проводятся аналогичные расчеты.

Использование MS Excel дает возможность сокращения времени на нахождение коэффициентов регрессии при проведении расчетов аналогичных матриц, а также упрощает процесс построения графиков влияния факторов эксперимента на исследуемые свойства.

Литература

1. Планирование эксперимента при поиске оптимальных условий / Ю. П. Адлер, Е. В. Макаров, Ю. В. Грановский. М., 1983 .

2 . MS Ехсе1 2010. Учебно-методическое пособие для студентов и слушателей системы повышения квалификации и переподготовки». М.

3 . Ш и б у т М. С. Технологии работы с текстами и электронными таблицами / Под ред. И. Ф. Богдановой. Минск: Молодежное научное общество, 2000.