Использование программы MS Excel в решении инженерных задач

13. Проанализируйте, начиная с какой точки график изменения объема становится линейным. По-видимому это будет точка, соответствующая давлению 4 МПа. Определите параметры прямой, которой можно аппроксимировать кривую изменения давления на линейном участке. Для этого следует воспользоваться встроенной функцией ЛИНЕЙН, позволяющей построить функцию вида y=b+m1*x1+mi*xi на основе некоторого массива исходных данных методом наименьших квадратов. Функция ЛИНЕЙН относится к категории статистических. В данном случае воспользуемся "ручным" набором, без использования мастера функций.

Синтаксис функции

ЛИНЕЙН (известные_значения_y; известные_значения_x; константа; статистика)

• Константа - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если константа имеет значение ИСТИНА (1), то b вычисляется обычным образом.
• Статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если статистика имеет значение ЛОЖЬ (0), то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

14. Выделите диапазон ячеек C18: D18, затем в строке формул наберите следующую формулу =ЛИНЕЙН (C13: G13; C12: G12; 1; 0) и нажмите клавиатурную комбинацию Ctrl-Shift-Enter (по этой клавиатурной комбинации вводится единая формула для всего массива). Обратите внимание, что после ввода клавиатурной комбинации Ctrl-Shift-Enter набранная формула заключается в фигурные скобки, что означает, что это формула массива, а не одной ячейки, а в ячейках C18: D18 появляются значения.

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

• В ячейку C20 введите формулу =$D$18+$C$18*C12 (вспомните, что для превращения относительной ссылки в абсолютную не обязательно вручную вводить знаки $ - достаточно нажать клавишу F4)

• Распространите формулу из ячейки C20 на диапазон C20: K20
• Выделите диаграмму на Листе2, затем в меню Диаграмма выберите пункт Добавить данные
• Для ввода в окно Диапазон нового ряда данных щелкните по закладке Лист1, в нем курсором мыши выделите диапазон C20: J20 и нажмите кнопку OK - на диаграмме появится график прямой.

16. Вычислите приведенный модуль объемной упругости рабочей жидкости, для чего в ячейку A24 введите формулу (5), которая будет иметь вид =A22*C18

17. Отформатируйте таблицу.

• Расположите заголовке по центру диапазона столбцов. Для этого выделите нужный диапазон столбцов и нажмите кнопку (Центрировать по выделению) на панели инструментов Форматирование. Проделайте это для диапазонов A1: K1; C2: K2; A11: K11; A19: K19

• В необходимых местах добавьте линии границ ячеек. Выделите ячейки A2: K10, нажмите клавиатурную комбинацию Ctrl-1, выберите вкладку Границы, отметьте Все внешние и внутренние границы. Для ячеек K5,K8 уберите верхнюю и нижнюю границу (выделите ячейки, Ctrl-1, убрать границы). Аналогично оформите таблицы в диапазонах A12: K15; A17: D18; B20: K20.
• Измените формат показателей степени в размерностях см3. Для это нужной ячейке выделите цифру 3 в размерности, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта щелкните мышью по пункту Верхний индекс раздела Эффекты. Повторите это для ячеек A5, A8, B12, B22.
• В ячейках A8, A10 измените символы DV на V. Для этого выделите D в соответствующих ячейках, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта выберите шрифт Symbol в окне Шрифт

18. Посмотрите, поместится ли созданная Вами таблица на один лист при печати. Для этого нажмите кнопку (Предварительный просмотр) на стандартной панели инструментов. Измените ширину столбцов A-K таким образом, чтобы все столбцы поместились на одной странице, и при этом все данные и надписи в столбцах были бы видны. Для плавного изменения ширины столбца достаточно щелкнуть по букве столбца мышью (выделить весь столбец), навести указатель курсора на границу столбца (указатель примет вид двунаправленной стрелки) и отбуксировать границу в нужное место.

19. Проверьте себя: приблизительный внешний вид таблиц и диаграммы приведен в приложении
20. Завершить работу, сохранив ее в файле work2. xls.

 

 

Обработка данных экстремальных экспериментов на примере исследования операции вытяжки листовых образцов

Постановка задачи ¹:

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

Напомним, что коэффициентом вытяжки k=D/d, где D - диаметр исходной заготовки, d - диаметр вытянутого из этой заготовки стаканчика. Предельная величина коэффициента вытяжки за один переход ограничена величиной максимальных напряжения во фланце заготовки. Разрушение заготовки произойдет тогда, когда напряжение достигнет предела прочности материала G_В. При этом значение коэффициента вытяжки является максимальным. На величину предельного коэффициента вытяжки, помимо других факторов, влияет радиус скругления кромки матрицы (r_м) и скорость деформирования (V).

Идея экстремальных экспериментов заключается в линейной аппроксимации гиперповерхности отклика, оценке составляющих градиента и проведении серии "мысленных" опытов (т.е. без выполнения реального эксперимента) в направлении оптимума.

Метод планирования эксперимента Бокса-Уилсона включает в себя построение линейной модели исследуемого объекта в виде y=b₀+b₁x₁+b₂x₂+G+b_nx_n, где n - количество факторов. В этом случае оценками составляющих вектора градиента являются коэффициенты полинома. Для движения по градиенту необходимо менять факторы пропорционально величинам коэффициентов. Такая процедура называется крутым восхождением. При движении по градиенту факторы изменяют с определенным шагом. Шаги изменения рассчитывают в натуральном масштабе.

Таким образом процедура решения задачи сводится к следующей последовательности шагов:

• проведение натурного эксперимента, для получения линейной модели y=b₀+b₁x₁+b₂x₂+b₃x₃ (1), где y= G_Bmax; x₁, x₂, x₃ - факторы k, r_м, V в кодовом масштабе (результаты натурного эксперимента заданы в качестве исходных данных);
• определению коэффициентов b_i линейной модели;
• определению составляющих градиента в натуральном масштабе;
• осуществлении крутого восхождения - т.е. подбора такого шага в направлении градиента из центра плана, при котором в формуле (1) y = G_В. Предел прочности материала, используемого в опытах 310 МПа

Методы решения с использованием Excel:

Для определения коэффициентов линейной модели (1) достаточно провести 4 опыта. Интервалы варьирования факторов X_i относительно центра плана X_i0 в проведенном натурном эксперименте приведены в таблице 1. Матрица плана исходного натурного эксперимента в кодированном масштабе приведена в таблице 2. Кодированные значения факторов связаны с натуральными соотношениями вида:

 

(2)

 

Таблица 1. Уровни варьирования факторов

Факторы	1	2	3
	k	rм, мм	V, мм/с
Xi0	1,3	3	0,5
DXi	0,1	1	0,2

 

 

 

 

Таблица 2. Матрица плана исходного эксперимента

№ опыта	x1	x2	x3	y (Gs max, МПа)
1	-1	-1	-1	189
2	1	-1	1	236
3	-1	1	1	167
4	1	1	-1	223

 

Коэффициенты регрессионной модели (1) определяют с помощью уже известной вам функции ЛИНЕЙН. Если вы забыли синтаксис функции ЛИНЕЙН, воспользуйтесь встроенной справкой Excel.

Исходная точка для крутого восхождения - центр плана с координатами в натуральном масштабе:

 

X_1о=1.3, X_2о=3, X_3о=0.5 (3)

 

Составляющие градиента вычисляются по формуле

 

g_i=b_iGX_{i (}4)

 

Шаг крутого восхождения в натуральном масштабе по каждой из координат

 

X_i=X_i0+g_i*S (5)

 

Подбор такой кратности шагов S, одинаковой для всех координат, при котором y = G_В осуществляют с помощью команды меню Excel "Сервис-Подбор параметра".

Последовательность выполнения

1. Запустить EXCEL
2. Переименуйте Лист1 книги в "Крутое восхождение". Остальные листы книги удалите. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Удалить.
3. На листе Крутое восхождение создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):

 

	A	B	C	D	E	F	G	H
1	Определение максимального коэффициента вытяжки
2	Уровни варьирования факторов
3		1	2	3
4	Xi0	1,3	3	0,5
5	DXi	0,1	1	0,2
6	Матрица плана исходного эксперимента в кодированном масштабе
7	№ опыта	x1	x2	x3	Gs max, МПа
8	1	-1	-1	-1	189
9	2	1	-1	1	236
10	3	-1	1	1	167
11	4	1	1	-1	223
12	Коэффициенты регрессионной модели
13	b3	b2	b1	b0
14
15	Составляющие градиента
16	g1	g2	g3
17
18	С учетом направления восхождения
19	g1	g2	g3
20
21	Крутое восхождение (с помощью "Сервис-Подбор параметра. ")
22		Xi=Xi0+S*gi			xi= (Xi-Xi0) /SXi
23	S	X1	X2	X3	x1	x2	x3	xmax
24

 

4. В диапазон A14: D14 введите формулу для определения коэффициентов регрессии для модели напряжений G _{s max}, используя функцию ЛИНЕЙН и мастер функций. Последовательность действий приведена ниже:

• Выделить A14: D14

• Меню Вставка-Функция (или кнопка Вставка функции)
• Категория - Статистические, Функция - ЛИНЕЙН, Кнопка OK
• Окно Изв_знач_y - диапазон известных значений G_{s max}
• Окно Изв_знач_x - диапазон значений x_i в опытах
• Окно Константа - 1
• Окно Стат - 0
• Нажать клавиатурную комбинацию Ctrl-Shift-Enter
• Формула массива вставится в выделенный диапазон и в нем появятся значения коэффициентов

5. Введите в диапазон A17: C17 формулы (4) для определения составляющих градиента. Обратите внимание, что порядок следования составляющих градиента g_i в диапазоне A17: C17 и порядок следования коэффициентов b_i в диапазоне A14: С14 не совпадают.

6. Обратите внимание, что полученные значения составляющих градиента для факторов 2 и 3 имеют отрицательные значения. Это означает, что шаги в этом направлении приведут к уменьшению G_{s max}. Поэтому мы должны двигаться в направлении антиградиента по этим факторам (напомним, что направление градиента - это направление увеличения функции). Таким образом в ячейки A20: C20 следует внести следующие формулы:

• A20 à =A17

• B20 à = - C17
• C20 à = - C17

7. Занесите в ячейку A24 значение 1, являющееся начальным значением кратности шагов S

8. Занесите в диапазон B24: D24 формулы (5) для определения значений варьируемых факторов в натуральном масштабе. Составляющие градиента расположены в ячейках A20: C20. Значения центра плана X_i0 - в ячейках B4: D4. Для использования автозаполнения в формулах следует вводить абсолютный адрес ячейки $A$24
9. В ячейки E24: G24 внесите формулы (2) для перехода от натурального масштаба к кодированному.
10. В ячейку H24 введите формулу для определения G_{s max} по значениям факторов x_i в ячейках E24: G24 в соответствии с моделью (1). Коэффициенты модели b_i были определены вами ранее в ячейках A14: D14. При вводе формулы обратите внимание на обратный порядок значений коэффициентов b_i в диапазоне A14: D14. Обратите внимание, что величина напряжения получилась значительно большей, чем максимально возможное значение 310 МПа.
11. Меняя значение в ячейке A24 попытайтесь вручную подобрать такую кратность шагов S, при которой значение напряжения в ячейке H24 было бы равно 310 МПа.
12. Верните в ячейку A24 значение 1 и подберите точное значение с помощью встроенных в Excel средств автоматического подбора значений. Для этого: