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

1. Запустить EXCEL

2. Переименуйте листы книги следующим образом: Лист1 - Модель, Лист2 - Сталь, Лист3 - Медь. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Переименовать.

3. На листе Модель создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):

 

	A	B	C	D	E	F	G	H
1	Исследование операции вырубки листовых образцов
2	Матрица плана в натуральном масштабе
3	N опыта	X1	X2
4	1	0,03	0
5	2	0,05	0
6	3	0,1	0
7	4	0,03	1
8	5	0,05	1
9	6	0,1	1
10	Матрица плана в кодированном масштабе
11	N опыта	x1	x2	z1	x1x2	x2z1	y1	y2, кН
12		d1	d2	d3	d4	d5
13	1						2	197
14	2						1	195
15	3						1	192
16	4						3	100
17	5						2	98
18	6						1	96
19	Коэффициенты регрессионных моделей вида
20	y=b+m1*d1+m2*d2+m3*d3+m4*d4+m5*d5
21		m5	m4	m3	m2	m1	b
22	y1
23	y2

 

4. На листе Сталь и Медь

 

	A	B	C	D	E	F	G	H
1	Варьирование зазором
2	X1	x1	x2	z1	x1x2	x2z1	y1	y2, кН
3	0,1
4	0,09
5	0,08
6	0,07
7	0,06
8	0,05
9	0,04
10	0,03
11	Поиск оптимального зазора
12	X1	x1	x2	z1	x1x2	x2z1	y1	y2, кН

 

5. Заполните диапазон B13: D18 формулами (4) для перехода от натурального масштаба к кодированному. Для ячейки B13 формула будет выглядеть следующим образом: =100* (B4‑0,06)

6. Заполните диапазон E13: F18 формулами для подсчета произведений x₁x₂ и x₂z₁

7. В диапазон B22: G22 введите формулу для определения коэффициентов регрессии для модели качества среза (y1) с помощью мастера функций. Последовательность действий приведена ниже:

Ä Выделить B22: G22

Ä Меню Вставка-Функция (или кнопка Вставка функции)

Ä Категория - Статистические, Функция - ЛИНЕЙН, Кнопка OK

Ä Окно Изв_знач_y - G13: G18 (мышью или с клавиатуры)

Ä Окно Изв_знач_x - B13: F18 (мышью или с клавиатуры)

Ä Окно Константа - 1

Ä Окно Стат - 0

Ä Нажать клавиатурную комбинацию Ctrl-Shift-Enter

Ä Формула массива вставится в выделенный диапазон и в нем появятся значения коэффициентов

8. Аналогично введите формулу для определения коэффициентов модели для силы вырубки (y2) в диапазон B23: G23. В качестве диапазона известных значений y используйте столбец со значениями y2 в матрице плана в кодированном масштабе.

9. Постройте графики изменения качества реза и силы деформирования для стали на основании полученной модели. Для этого сначала необходимо заполнить таблицу данных на Листе Сталь. Используйте следующую последовательность действий:

Ä В ячейку Сталь! B3 занесите формулу =100* (A3-0,06) для перехода в кодированный масштаб

Ä В диапазон Сталь! С3: C10 занесите значение - 1 (минус 1), соответствующее коду стали в кодированном масштабе.

Ä Скопируйте формулы из диапазона Модель! D13: Модель! F13 в диапазон Сталь! D3: Сталь! F3, для чего:

Ø Выделите диапазон D13: F13 на листе Модель и нажмите кнопку Копировать

Ø Выделите ячейку D3 на листе Сталь и нажмите кнопку Вставить

Ä Распространите формулы диапазона B3: F3 на диапазон B3: F10

Ä Занесите в ячейку G3 на листе Сталь формулу для определения качества реза:

Ä =Модель! $G$22+Модель! $F$22*B3+Модель! $E$22*C3+Модель! $D$22*D3+ Модель! $C$22*E3+Модель! $B$22*F3

Ä Аналогично занесите в ячейку H3 на листе Сталь формулу для определения силы вырубки (вид формулы продумайте сами)

Ä Распространите формулы из диапазона G3: H3 на диапазон G3: H10

Ä Проверьте себя: для строк со значениями x1=4,-1,3 величины y1 и y2 должны точно совпадать с исходными данными

10. Постройте точечные графики y1=f (X1), y2=f (X1) на различных диаграммах и расположите их на том же листе Сталь и отформатируйте так, как показано в приложении. Область диаграммы y1=f (X1) должна занимать диапазон A15: D28, а y2=f (X1) - диапазон E15: H28. Указание: Прежде чем начать строить диаграммы с помощью мастера диаграмм выделите сначала диапазон значений аргумента A3: A10, а затем держа нажатой клавишу Ctrl на клавиатуре - диапазон значений аргумента (соответственно G3: G10 для y1 и H3: H10 для y2). Выделение с нажатой клавишей Ctrl позволяет выделить несмежные диапазоны данных.

11. Проанализируйте график качества среза. Очевидно, что наилучшее качество достигается при минимальном зазоре 0.03. Значение оптимального зазора можно было найти не прибегая к построению графика, воспользовавшись встроенными в Excel средствами поиска оптимальных решений.

Ä Скопируйте формулы из диапазона B3: H3 в диапазон B13: H13

Ä Выполните команду меню Сервис-Поиск решения

Ä В открывшемся окне Поиск решения занесите в окошки ввода следующие величины (мышью или с клавиатуры):

Ø Установить целевую - $G$13

Ø Равной - максимальному значению

Ø Изменяя ячейки - $A$13

Ø Ограничения - $A$13>=0.03; $A$13<=0.1 (Воспользуйтесь кнопкой Добавить справа от окна Ограничения. При вводе десятичной точки строго пользуйтесь точкой на основной клавиатуре)

Ä Нажмите кнопку Выполнить

Ä Excel выполнит поиск оптимального решения с заданными ограничениями, после чего появится окно Результаты поиска решений, в котором следует нажать кнопку OK. Убедитесь, что Excel самостоятельно нашел правильное решение.

Ä Попытайтесь найти самостоятельно зазор, обеспечивающий наихудшее качество

12. Постройте графики для Меди повторив пункты 9-11 на листе Медь (столбец x2 должен иметь значение 1, соответствующее коду меди). Пункт 12 можно выполнить проще. Если догадаетесь как - то сэкономите себе время.

13. Отформатируйте таблицы и графики так, как это показано в приложении. Если сможете - сделайте внешний вид полученных таблиц более привлекательным.

14. Завершить работу, сохранив ее в файле work3. xls.

15. Запустить EXCEL, вернуться к документу work3. xls и предъявить его преподавателю.

16. Предъявить преподавателю краткий конспект занятия.

Приложение

Лист Модель:

 

 

Лист Сталь:

 

Лист Медь: