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

Содержание
Занятие 1 — Определение инерционныххарактеристик главного вала горячештамповочногоавтомата
Занятие 2 — Обработка эксперимента по определениюприведенного модуля объемной упругости жидкости
Занятие 3 — Расчет коэффициентовматематической модели на примере исследования операции вырубки листовыхобразцов
Занятие 4 — Исследование операции вытяжки цилиндрическогостакана из плоской заготовки
Занятие 5 — Обработка данных экстремальных экспериментов на примере исследования операциивытяжки листовых образцов
Занятие 6 — Исследование операции обжима
Занятие 7 — Оптимизация раскроя листовогоматериала
Занятие 8 — Обработка данных экспериментов с несколькими выходными переменными
Занятие 1 — Определение инерционных характеристик главноговала горячештамповочного автоматаЦели работы:
·  познакомитьсяс основными понятиями электронной таблицы
·  освоитьосновные приемы заполнения и редактирования таблицы
·  научитьсясохранять и загружать таблицыПостановка задачи:
Определить массу и момент инерции главного вала горячештамповочногоавтомата:
/>
/>
 
m — масса цилиндра; J — момент инерции цилиндра
r=7850кг/м3 — плотность
V — объем; D — наружный диаметр; d — внутреннийдиаметр; h — высота
r0 — расстояние отоси цилиндра до оси вращенияПоследовательность выполнения
1. Запустить EXCEL
2. Развернуть окно EXCEL во весь экран и рассмотреть его
(Используйте стандартные кнопки управления окном, расположенныев правом верхнем углу окна)
2.1. Основные элементы окна:
·  верхняястрока — заголовок окна с кнопками управления
·  втораястрока — меню EXCEL
·  третьяи четвертая строки — панели инструментов Стандартная и Форматирование
·  пятаястрока — строка редактирования формул
·  строкимежду пятой и предпоследней — рабочее поле таблицы, состоящее из пронумерованныхячеек
·  втораяснизу — ярлыки листов (для переключения между рабочими листами) и полоса прокрутки
·  последняястрока — строка состояния
2.2. прочитать назначение кнопок панели Стандартная и Форматирование, медлено перемещая курсор по кнопкам.
3. Произвести настройку экрана
3.1. В меню Вид, установить флажки b (если их нет) щелчком мыши в следующих элементах:Строка формул, Строка состояния
3.2. В меню Вид выбрать команду Панелиинструментов4,в открывшемся подменю установить флажки bв следующих элементах: Стандартная, Форматирование.
3.3. В меню Сервис, выбрать командуПараметры… и раскрыть вкладку Вид,
3.4. Проверить установку флажков и переключателей
В группе Отображать — Строка формул, строка состояния
В группе Примечания — Только индикатор
В группе Объекты — Отображать.
В группе Параметры окна — все кроме Авторазбиение на страницы, Формулы
Раскрыть вкладку Общие и проверить установкупереключателя Защита от макровирусов, установить Листов в новой книге — 3.
Нажать кнопку ОК
4. Основные понятия электронных таблиц
4.1. Рабочее поле — 16384строки, 256столбцов. Каждое пересечениестроки и столбца образует ячейку, в которую можно вводить данные (текст, число илиформулу)
4.2. Номер строки (числа на левой границе рабочего поля), буквастолбца на верхней границе рабочего поля (A-Z,AA-AZ,BA-BZ…)
4.3. Ячейка — пересечение строки и столбца — может содержать текст, число,формулу, имеет уникальный адрес, состоящий из буквы столбца и номера строки(например B3)
4.4. Указатель ячейки — жирный черный прямоугольник, с точкой в правомнижнем углу, определяющий текущую ячейку. Может перемещаться мышью (щелчок левойклавишей мыши по ячейке) и клавишами управления курсором (на клавиатуре)
· Выделите ячейку D4 — мышью, а затем вернитесьв ячейку A1 с помощью клавиш управления курсором.
4.5. Блок — прямоугольная область смежных ячеек, Адрес блока состоитиз координат противоположных углов, разделенных двоеточием (B13: C19)
4.6. Книга — программа на EXCEL, состоит из (по умолчанию) рабочих листов(по умолчанию — 3 для Excel97 и 16 для Excel7), переход от одного листа к другому — щелчком по ярлыку
· перейти к Лист3, вернуться к Лист1
5. Выделение основных элементов
5.1. мышью
строки (столбца) — щелкнуть мышью по цифре (букве) — именистроки (столбца)
нескольких смежных строк (столбцов) — щелкнуть по цифре(букве) первой строки (столбца) и не отпуская кнопку мыши протащить курсор до последнейстроки (столбца)
блок — щелкнуть мышью по угловой ячейке блока (например,левый верхний угол) и не отпуская кнопки протащить курсор до противоположной угловойячейки (например, правый нижний угол), либо щелкнуть по первой угловой ячейке блока,а затем с нажатой клавишей Shift попротивоположной угловой ячейке блока.
несмежные ячейки (блоки) — выделить первый блок (ячейку,столбец, строку), а затем выделить последующие с нажатой клавишей CTRL
· выделить блок B2: E12 — первым способом, D3:F14 — вторым,
· выделить строки, столбцы 3, F, 1: 5, J: M
· выделить несмежные блоки (A2: B5; D3: E8),(8: 12; D: E)
5.2. клавиатурой: выделение блока-
перейти в ячейку, занимающую угол блока,
нажать SHIFT и раздвигать область клавишами управления курсором
· выделить блок C4: F10
5.3. весь лист — нажать на пустую кнопку в левом верхнем углу на пересечении номерови строк
6. Основные приемы заполнения таблиц данными (текст, число, дата, время, формула)- общий принцип — переместиться в нужную ячейку, набрать данные (не более 240 символов),нажать ENTER, или перейти в другую ячейку мышью или клавишами
6.1. Текст — если первый символ — буква, то EXCEL автоматически считает данные- текстом, если текст начинается с цифры, то нужно сначала ввести символ апостроф‘ (в латинском режиме). По умолчанию текст прижимается к левой границе. При вводеданных в правую ячейку изображение текста будет обрезано, но сам он сохранится
· ввести в ячейку A1 текст:
· Расчет массы и момента инерции главного валаавтомата AO339 (в дальнейшем подобные задания будут записаны как:
· A1 ® Расчет массы и момента инерции главного валаавтомата AO339)
· A2 ® Выполнен
6.2. Числа — начинаются со знака или без него. Если число очень большое (или маленькое)то EXCEL отображает его в экспоненциальной форме (3Е10 — означает 3´1010). Если необходимо ввестидесятичное число — то в качестве разделителя используется либо десятичная точка,либо десятичная запятая (в зависимости от настройки Windows).По умолчанию число прижимается к правому краю.
Занесите в ячейки данные, необходимые для расчета инерционныхпараметров венца зубчатого колеса
· A4®1.780 (с точкой!) (это будет наружный диаметр участка (вметрах))
· B4®1,560 (с запятой!) (это — внутренний диаметр)
Проверьте в какой ячейке введено десятичное чисто (оно будетприжато к правому краю), исправьте число в ячейке, в которой десятичный разделительбыл введен неправильно. Для правильного ввода десятичного разделителя, независимоот настройки, следует пользоваться точкой справа на дополнительной клавиатуре.
Исправления можно выполнять либо непосредственно в ячейке, предварительноуказав на нее курсором и выполнив двойной щелчок правой клавишей мыши, либо в строкеформул, для чего необходимо сначала одинарным щелчком выделить нужную ячейку, азатем перевести курсор в строку формул, щелкнув правой клавишей мыши в позиции редактирования.
· С4 ® 0,29 (это — высота участка)
· D4 ® 0 (это — расстояние до оси вращения)
6.3. Дата — через дробь дд/мм/гг, дд-мм-гг, (либо месяц буквами, в зависимостиот настройки английским или русскими) — после фиксации в качестве разделителя используетсяточка.
6.4. EXCEL преобразует дату в число равное количеству прошедших суток от 1 января 1990 г
· B2 ® текущая дата (например для 12 марта: 12-04)
6.5. Формула — арифметическое выражение, состоящее из последовательности чисел,ссылок на ячейки и функций, соединенных арифметическими знаками и начинающихся сознака =. Завершение ввода формулы — нажатие клавиши ENTER,либо щелчок мышью по кнопке b (зеленогоцвета) в строке формул. В ячейке после этого появляется результат. Формула отображаетсяв строке формул.
Можно вводить ссылки на ячейки вручную, а можно путем указанияна соответствующую ячейку мышью.
Например для ввода в ячейку Е4 формулы для определения массызубчатого венца =3,14*7850* (A4^2-B4^2) *C4/4 необходимо выполнитьследующую последовательность действий:
установить курсор в ячейку Е4
набрать =3,14*7850* (с клавиатуры щелкнуть по ячейке A4 набрать ^2- щелкнуть по ячейке B4 набрать ^2) * щелкнуть по ячейке C4 набрать /4 щелкнуть по кнопкеb (зеленого цвета) в строке формул
6.6. Попробуйте заменить какие-нибудь числа в ячейках A4:C4 и убедитесь, что значения в ячейке E4также изменяются. Проверьте на калькуляторе правильность записанной формулы.
6.7. Число p=3.14¼ можнозаменить его точным значением, использовав встроенную функцию ПИ (). Для этого необходимодвойным щелчком выделить число 3,14 и на этом месте ввести с клавиатуры ПИ (), либо воспользоваться мастером функций, выполнив следующуюпоследовательность действий:
щелкнуть по кнопке fx на панели — Стандартная
в открывшемся окне в группе Категория выбрать — Математические
в группе Функции выбрать функцию ПИ
нажать на кнопку ОК в нижней части окна
щелкнуть по кнопкеb(зеленого цвета) в строке формул
7. Заполните ячейки A5¼D12 значениямиразмеров соответствующих участков вала, разбив его на цилиндрические участки и поместивв столбец A значения наружного диаметра,в столбец B — внутреннего, C — высотыцилиндра.
8. Основные приемы редактирования таблиц
8.1. Операции с ячейками, строками, столбцами, блоками.
Прежде, чем произвести какие-либо действия с элементом его надовыделить.
Действия по перемещению, копированию, удалению и очистке блокаможно производить несколькими способами:
Äс помощью кнопок панели инструментов (Вырезать,Копировать, Вставить)
Äчерез меню (в меню Правка команды Вырезать, Копировать, Вставить,Удалить, Очистить, Заполнить)
Äс помощью мыши, методом Drag and Drop (перетащи и брось) —
Äс помощью контекстно-зависимого меню (меню правой кнопки мыши прищелчке по выделенному элементу)
8.2. Копирование
Äметодом Drag and Drop
выделить ЧТО (диапазон ячеек которые копируются),
перевести указатель мыши на границу выделенного диапазона так,чтобы указатель превратился в стрелку, направленную влево под углом
нажать левую клавишу мыши и удерживать клавишу CTRL на клавиатуре.Рядом с указателем появится маленький знак + признак операции копирования.
мышью выделенный диапазон перетащить в необходимый диапазон книги.В процессе перетаскивания границы копируемого диапазона будут отображаться на экране,что облегчает позиционирование копируемого диапазона на новое место.
после позиционирования копируемого диапазона на новом месте следуетсначала отпустить левую клавишу мыши, а затем клавишу CTRL на клавиатуре.
Ä кнопками панели инструментов- выделить ЧТО, кнопка Копировать, выделить КУДА, кнопка Вставить
· скопировать A1 ® в A14 методом Drag and Drop
· скопировать A4: E4 в A15: E15 с помощью кнопок панелиинструментов. Обратить внимание на изменение формулы в ячейке E15 — ссылки на ячейки с исходными данными автоматически изменились.При выполнении операции копирования, в том случае, если в ячейке записана формула,то ссылки на ячейки в формуле изменяются в соответствии с расстоянием (по столбцами строкам) на которое был перемещен диапазон.
8.3. Очистка — выделить необходимы диапазон и нажать DEL
· очистить ячейки в 14 и 15 строках
8.4. Перемещение выполняется аналогично копированию, только в методе Drag and Drop не надодержать нажатой клавишу CTRL, а при использовании кнопокпанели инструментов вместо кнопки Копировать следует использоватькнопку Вырезать.
· переместить A4: E12 на один столбец вправо.
8.5. Заполнение — выделенные ячейки копируются несколько раз за одно действие.Методика заполнения: Выделить блок, навести курсор на маркер заполнения (жирныйквадрат в нижнем правом углу выделенного блока или ячейки), маркер заполнения долженпревратиться в крестик, затем раздвигать выделенную область
· E4 ® E4: E12 (весь столбец должен автоматически заполнитьсянулями)
· F4 ® F4: F12 (ячейки должны заполниться значениями,вычисленными по скопированным формулам)
· Занесите в ячейку G4 формулу для вычислениямомента инерции =F4* (B4^2+C4^2) /8+F4*E4^2 и заполните ею диапазон G4: G12.
· В строчке, соответствующей участку шейки кривошипазанесите в ячейку столбца D значение эксцентриситета. Убедитесь, что значение моментаинерции автоматически пересчиталось.
8.6. Удаление — в отличие от очистки удаленные ячейки схлопываются — т.е. другиеячейки занимают место удаленных. Осуществляется через меню Правка,Удалить (или контекстное меню — щелчок правой кнопкой мышипо нужной ячейке и из предложенных действий выбрать нужное).
8.7. Примечание: Если Вы случайно совершили неправильное действие (например удалилилишнюю ячейку), с помощью кнопки Отменить на панели инструментовили команды Отменить меню Правка можноотменить последнее действие (для EXCEL5,7) или несколькопоследних действий (для EXCEL97)
· Удалить строку 2 через меню Правка
· удалить ячейку A1 через контекстно-зависимоеменю
· отменить удаление
8.8. Автозаполнение — позволяет быстро создавать различные типовые последовательности.Работает также, как и обыкновенное заполнение. EXCEL анализирует выделенные ячейкии если находит зависимость, то интерполирует ее на другие ячейки
ÄАрифметическая прогрессия
· A4® 1; A5 ® 2; выделить A4: A5; маркер заполнения протащитьдо A12
ÄДни недели
· A17® Понедельник, Заполнить до H17
ÄМесяцы
· A18® Январь, Заполнить до K18
ÄДаты
· A19® Скопировать дату из ячейки B2, Заполнить доH19
· Удалить строки 17: 19
8.9. Изменение ширины столбцов и высоты строк.
С помощью мыши — Плавное изменение (подвести указательк правой границе столбца, указатель изменил вид, зацепился — потащил, отпустил)и подбор минимального значения (подвести указатель к правой границе столбца, указательизменил вид, дважды щелкнуть левой клавишей мыши).
С помощью меню — Формат, Строка, Высота (Автоподпорвысоты) или Столбец, Ширина(Автоподбор ширины)
· Подберите мышью ширину столбцов таблицы в соответствиисо своим вкусом
· Строка 9 ® высота 30, а затемобратно ®12.75 (через меню Формат)
9. Абсолютная относительная и смешанная адресация ячеек и блоков.
Использовавшаяся до сих пор адресация — относительная. При ееиспользовании EXCEL запоминает расположение относительно текущей ячейки и при копированииавтоматически меняет адрес.
Иногда при копировании необходимо сохранить ссылку на конкретнуюячейку (столбец, строку) — абсолютная адресация. Перед буквой столбца и номеромстроки следует вставить символ $ (можно после вставки относительного адреса нажатьF4 тогда символы $ вставятся автоматически) — такая адресацияназывается абсолютной
Иногда при копировании необходимо изменять только один параметрадреса. Тогда символ $ ставится только там, где он необходим (только перед буквойстолбца или номером строки) — такая адресация называется смешанной.
ÄВ нашем примере при определении массы в формулу вставляли конкретноезначение плотности материала 7850кг/м3. В том случае, если бы вал сделалииз материала, имеющего другую плотность, нам нужно было бы менять каждую формулудля определения массы. Попытаемся обобщить таблицу на случай произвольного материала
· В ячейку A2 ввести текст Плотность материала
· В ячейку B2 внести значение 7850
· Выделить ячейку F3. В строке формул появитсяформула для вычисления массы.
· Выделить в формуле число 7850 двойным щелчкомлевой клавиши мыши, затем щелкнуть по ячейке B2 (в формуле значение 7850 должнозамениться на B2) и нажать на клавишу F4 в верхнем ряду клавиатуры (B2 должно изменитьсяна $B$2). После этого щелкнуть по кнопкеb (зеленого цвета) в строке формул.
· Заполнить формулой в ячейке F4 диапазон F4:F12 и убедиться что в каждой формуле есть ссылка $B$2 на адрес ячейки со значениемплотности.
· Изменить плотность материала и убедиться, чтопроизведен пересчет во всех ячейках одновременно.
· Переместить диапазон A3: G12 на одну строчкувниз и убедиться, что абсолютная адресация в формулах при перемещении и копированиисохраняется, в отличии от относительной.
10.Определим массу и момент инерции вала.Для этого необходимо просуммировать значения, стоящие в соответственно в столбцахF и G. Длясуммирования столбца F необходимовыделить ячейку F13, нажать кнопку Автосуммирование(S) на панели инструментов, убедиться,что диапазон суммирования выбран программой правильно и нажать клавишу Enter на клавиатуре. В ячейке F13 появитсязначение суммы масс по участкам.
· Просуммировать значения в столбце G
11.Добавьте заголовки к столбцам таблицыи заголовок для строки с результатами суммирования
12.Операции с таблицей в целом — сохранить,сохранить как, открыть, создать. Выполняются либо из меню Файл,либо используя соответствующие кнопки панели инструментов.
· сохранить work1. xls в своем каталоге
13.Завершить работу
14.Запустить EXCEL, вернуться к документу work1. xls ипредъявить его преподавателю.
15.Итоги. Проверьте
15.1. знаете ли Вы, что такое: строка, столбец, ячейка, лист, книга, контекстно-зависимоеменю, панель инструментов, абсолютная, относительная и смешанная адресация
15.2. умеете ли Вы: вводить текст, редактировать данные, изменять размеры ячеек,перемещать, копировать, заполнять, удалять, сохранять
16.Предъявить преподавателю краткий конспектзанятия.Занятие 2 — Обработка эксперимента по определению приведенногомодуля объемной упругости жидкостиЦели работы:
·  закреплениеосновных приемов заполнения и редактирования таблицы
·  освоениеосновных приемов форматирования таблицы (внешнего вида)
·  освоениеметодов построения точечных графиков
·  получениеначальных сведений для построения регрессионных зависимостейПостановка задачи:
Создать электронную таблицу для обработки данных экспериментапо определению приведенного модуля объемной упругости жидкости.
Последовательность выполнения опыта:
1. Аккумулятор винтового пресса-молота заряжается до начального давления 25МПА.
2. Открывается вентиль, через который жидкость (минеральное масло) начинаетвытекать в мерный объем (мензурку)
3. Фиксируется объем вытекшего масла через определенные промежутки текущегодавления в аккумуляторе.
4. Опыт повторяется 3 раза
Модуль объемной упругости жидкости определяли путем обработкирезультатов эксперимента по следующему алгоритму:
1. Определяется объем вытекшего масла на каждом интервале замеров давления:
2. i=1¼9числоточек фиксации объема вытекшего масла pi=20,15,10,5,4,3,2,1,0[МПа] — давления, при которых фиксируется объем масла Vi — объем вытекшего масла, соответствующийi-мудавлению в аккумулятореDVi=Vi+1-Vi, (1) приi=1¼8,DV9=0
3. Вычисления по п1 выполняют для каждого из трех опытов
4. Вычисляют среднее значение изменения объема на каждом интервале:
5. DVсрi= (DV1,i+DV2, i+DV3,i) /3, (2)  где индексы1, i 2, i 3, i соответствуют номеру опыта и номеру интервала
6. Вычисляют среднее значение объема масла в точках фиксации давления по результатамтрех опытов:
7. Vсрi=Vсрi+1+DVi(3) приi=8¼1,Vср9=0
8. Строится график P=f (Vср) и визуальноопределяют точку n переходаот нелинейного начального к линейному конечному участку графика
9. Результаты соответствующие точкам i=1¼n аппроксимируют прямой p=b0+b1*Vср (4)
10. Определяют приведенный модуль объемной упругости жидкости по формуле:
11. k=V0* dp/dVср (5) гдеV0=1,1577*104 см3 — объемаккумулятораdp/dVср=tana= b1, a- угол наклона прямой, аппроксимирующей линейный участок зависимостиP=f(Vср)Последовательность выполнения
1. Запустить EXCEL
2. Создать таблицу с исходными данными эксперимента и поясняющими надписямив соответствие с прилагаемой ниже таблицей. Обратить внимание, что текст, целикомне помещающийся в ячейку вводится в ячейку, номер которой соответствует началу текста.Например, весь текст «Экспериментальное определение модуля объемнойупругости» вводится в ячейку А1
При заполнении, там где это целесообразно, пользуйтесь методамиавтозаполнения и копирования, изученными на предыдущем занятии
Символы DV втексте пояснений в таблице заменяют символы в DV формулах A B C D E F G H I J K 1 Экспериментальное определение модуля объемной упругости 2 Величина №эксп Экспериментальные данные 3 p, МПа 20 15 10 5 4 3 2 1 4 1 116 155 190 228 233 245 250 268 280 5 DV, см3 2 130 164 202 235 243 251 260 272 290 6 3 110 146 179 215 224 228 240 248 270 7 1 8 DV, см3 2 9 3 10 DVср, см3 11 Данные для построение графика результатов эксперимента 12 Vср, см3 13 p, МПа 20 15 10 5 4 3 2 1 14 погрешность max 15 min 16 Построение линейной регрессии 17 коэффициенты m1 b 18 19 Линейная аппроксимация 20 p, МПа 21 Объем аккумулятора 22 1,1577E4 V0, см3 23 Приведенный модуль объемной упругости 24 МПа
3. Занести в ячейку С7 формулу (1), которая будет иметьвид =D4-C4 и распространить ее на диапазон C7: J9.
4. Занести в ячейку С10 формулу для определения среднегозначения изменения объема DVсрi Для этого можно воспользоваться встроенной функцией СРЗНАЧ. Рассмотрим порядок ввода формул с использованием встроенныхфункций на этом примере:
Äустановить курсор в ячейку C10
Äнажать на кнопку /> (Мастер функции илиВставить функцию) на стандартной панели инструментов
Äв открывшемся окне выбрать Категория: Математические,Функция: СРЗНАЧ и щелкните по кнопке ОК(для Excel97) или Шаг> (для Excel5.0,7.0)
Äв открывшемся окне диалога в пункте Число1 вместопредложенного мастером функций диапазона занесите диапазон ячеек, для которых вывычисляете среднее значение C7: C9 и щелкните по экраннойкнопке OK (Закончить) Ввод диапазонаможно осуществлять как вручную (обратите внимание на то, что клавиатура должна находитьсяв латинском регистре), так и мышью — что более предпочтительно.
ÄДля ввода диапазона с помощью мыши необходимо просто выделить мышьюнужный вам диапазон в таблице (если нужный диапазон закрыт окном диалога, то окноможно передвинуть в другое место экрана методом Drag and Drop)
5. Распространить формулу в ячейке С10 на диапазон
6. Для того, чтобы увидеть точное значение вычисленных величин (если Excel округлил их до целых), выделитедиапазон С10: J10 и несколько раз нажмите кнопку /> (Увеличитьразрядность) на панели инструментов Форматирование.
7. В ячейку J12 внести формулу (3), которая будет иметьвид=K12+J10 и распространить ее на диапазон J12: C12. Если это необходимо, то увеличьте разрядность отображениявеличин.
8. В ячейки C14 и C15 занесите формулыдля определения максимальной и минимальной погрешности вычисления объема, которыебудут иметь вид:
microsoft excel электронная таблица
9. для C14: =МАКС (С7: C9) — C10для C15: =C10-МИН (C7: C9) Функции МИН и МАКС находятся в категории Статистическиемастера функций.
10.Распространите формулы из диапазонаC13: C14 на диапазон C13: K14
11.Построить график зависимостиP=f (Vср):
Äвыделите диапазон ячеек B12: K13, на основаниикоторого вы будете строить диаграмму.
Äщелкните по кнопке Мастер диаграмм стандартнойпанели инструментов (в зависимости от версии Excel она имеетразличный внешний вид, поэтому найдите ее сами с помощью всплывающих подсказок)либо воспользуйтесь командой меню Вставка-Диаграмма.
Äследуйте указаниям Мастера диаграмм в каждомдиалоговом окне, возникающим последовательно после нажатия экранных кнопок Далее> (или Шаг> для ранних версий).Ниже приведена последовательность действий для Excel97(для ранних версий Excel последовательность и содержание оконнесколько другое, но получаемый результат будет идентичен). Если Вас что-то не устраиваетв построенной диаграмме, то в последующем ее можно изменить:
Ø Шаг1: Щелчком мыши выберите тип диаграммы Точечная,а вид — со значениями, соединенными сглаживающими линиями. Нажмите кнопку Далее.
Ø  Шаг2: Еслидиапазон данных в окне ввода Диапазон указан верно, а вид диаграммы тот, которыйвы ожидаете, нажмите кнопку Далее. В противном случае в окнеДиапазон введите нужный диапазон данных
Ø  Шаг3: В окноНазвание диаграммы сотрите содержащиеся там данные; в окнеОсь X введите: V, см3; в окне Ось Y введите: p, МПа. Нажмите кнопку Далее.
Ø  Шаг4: В окнеПоместить диаграмму на листе выберите пункт Имеющемся, рядомс которым выберите в открывающемся списке пункт Лист2. Нажмитекнопку Готово.
12.Как правило, вид созданной диаграммымастером диаграмм бывает неудовлетворительным, поэтому диаграмму следует отформатировать.Выполните форматирование диаграммы в соответствие со следующими указаниями:
ÄЩелкните мышью по области диаграммы. Область диаграммы выделится чернойрамкой, по углам которой и в середине сторон имеются черные квадратики — размерныемаркеры. Кроме того в меню Excel добавитсяпункт Диаграмма Для изменения размеров диаграммы необходимобуксировать размерные маркеры. Перед буксировкой необходимо навести указатель мышина один из размерных маркеров. Указатель мыши при этом примет вид двунаправленнойстрелки. После чего необходимо нажать левую клавишу мыши и перемещать (буксировать)маркер. Буксировка маркера, расположенного в середине стороны, позволяет изменятьгоризонтальные и вертикальные размеры диаграммы. Буксировка углового маркера позволяетизменять одновременно вертикальные и горизонтальные размеры.
ÄДля перемещения диаграммы необходимо навести указатель мыши на областьдиаграммы (указатель примет вид белой стрелки, направленной под углом влево-вверх)и отбуксировать ее на новое место
ÄОтбуксируйте диаграмму в левый верхний угол листа, а затем изменитеее размеры так, чтобы она занимала примерно ¾ пространства листа.
ÄДиаграмма состоит из нескольких элементов. К ним, в частности, относятся:
—   областьдиаграммы (весь чертеж)
—   областьпостроения диаграммы
—   легенда
—   рядыданных
—   названияосей и т.п.
Для редактирования элемента его необходимо выделить. Это можносделать нажатием стрелок перемещения курсора ↑ ↓ на клавиатуре, либоодиночным щелчком левой клавиши мыши по соответствующему элементу. При выделениив поле имени (крайнее левое поле в строке формул) появляется название элемента.Выделенный элемент отмечается размерными маркерами.
Редактирование выделенного элемент осуществляется через менюФормат, в котором появляется пункт для редактирования соответствующегоэлемента, либо нажатием клавиатурной комбинации Ctrl-1,которая позволяет произвести форматирование текущей ячейки, диапазона, элементадиаграммы или объекта.
Выделенный элемент можно перемещать, менять его размеры методомбуксировки, а также удалять, нажав клавишу Del.
ÄВыделите диаграмму, щелкнув по области диаграммы
ÄПоследовательно нажимая клавишу ↑ на клавиатуре обратите вниманиена элементы диаграммы, присутствующие в построенной вами диаграмме.
ÄУдалите легенду
ÄОтбуксируйте название оси X (категорий) вправый нижний угол области диаграммы
ÄИзмените размеры области построения диаграммы так, чтобы она занималамаксимальный размер внутри области диаграммы. Выберите подходящий цвет заливки областипостроения (по нашему мнению для черно-белых принтеров наиболее целесообразно применятьпрозрачную заливку).
ÄОтобразите на диаграмме погрешности вычисления. Для этого выделитекривую (она имеет название Ряд «p, МПа»), нажмитеклавиатурную комбинацию Ctrl-1. Затем в окне Формат ряда данных выберите вкладку X-погрешности, в который выберитевид показа — обе планки погрешностей, величина погрешностей — пользовательская,в графе "+" введите диапазон положительной погрешности, вычисленной Вами=Лист1! $C$14: $K$14 (это можносделать и мышью, выделяя соответствующий диапазон в таблице), а в графе "-"=Лист1!$C$15: $K$15
ÄДобавьте (если их нет) линии сетки для значений по оси X. Для этого в меню Диаграмма выберите пунктПараметры диаграммы и на вкладке Линии сеткищелкните по пункту ОсьX — основные линии, после чего нажмитекнопку OK
ÄОтформатируйте осьY так,чтобы график занимал всю область построения диаграммы. Для этого после выделенияоси и перехода в окно редактирования одним из указанных выше способов (напримерс помощью клавиатурной комбинации Ctrl-1) выберите вкладкуШкала и в графе Максимальное значениепоставьте максимальное значение давления в исходной таблице — 20. Посмотрите другиевкладки в окне форматирования оси. Возможно Вы захотите изменить и другие параметрыи внешний вид элементов оси.
ÄИзмените название оси категорий так, чтобы размерность объема былапоставлена правильно: не см3, а см3. Для это выделите название оси, затемвыделите цифру 3 в размерности, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта щелкните мышьюпо пункту Верхний индекс раздела Эффекты.
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 на DV. Для этоговыделите D в соответствующих ячейках,нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окнеформатирования шрифта выберите шрифт Symbol в окне Шрифт
18.Посмотрите, поместится ли созданнаяВами таблица на один лист при печати. Для этого нажмите кнопку /> (Предварительныйпросмотр) на стандартной панели инструментов. Измените ширину столбцов A-K таким образом,чтобы все столбцы поместились на одной странице, и при этом все данные и надписив столбцах были бы видны. Для плавного изменения ширины столбца достаточно щелкнутьпо букве столбца мышью (выделить весь столбец), навести указатель курсора на границустолбца (указатель примет вид двунаправленной стрелки) и отбуксировать границу внужное место.
19.Проверьте себя: приблизительный внешнийвид таблиц и диаграммы приведен в приложении
20.Завершить работу,сохранив ее в файле work2. xls.
21.Запустить EXCEL, вернуться к документу work2. xls ипредъявить его преподавателю.
22.Предъявить преподавателю краткий конспектзанятия.
/>
/>Занятие 3 — Расчет коэффициентов математической моделина примере исследования операции вырубки листовых образцовЦели работы:
Ä  закреплениеосновных приемов создания и форматирования таблицы
Ä  закреплениеметодов построения точечных графиков
Ä  освоение основныхметодов обработки многофакторных экспериментовПостановка задачи [1]:
Исследуется влияние величины зазора Z между пуансоном и матрицейна качество среза и силу P, необходимую для вырубки листовых образцов.
Заготовки после вырубки осматривают и оценивают качество срезапо 3-х бальной шкале. Наилучшим срезом, оцениваемым в 3 бала, считается состоящийиз трех зон (I — зона скругления, II- блестящий поясок, III — зона скола) При этом зона I должна иметь незначительный размер.Если эта зона возрастает по сравнению с наименьшей, полученной при вырубке заготовкииз данного материала, или вырубленный образец имеет заметный прогиб, то качествусреза присваивают оценку 2 бала. Если же поверхность рваная, с дополнительными поясками,то качеству среза присваивают оценку 1 балл.
Вырубаются кружки из стали 45 и меди в матрице диаметром 25 мм. Толщина заготовок 7 мм. Уровни варьирования зазора, по отношению к толщине заготовки составляют0.03, 0.05 и 0.1.
По результатам опыта необходимо построить зависимость качествареза и силы вырубки от зазора между пуансоном и матрицей для каждого из используемыхматериалов и определить оптимальные величины зазоров.Методы решения с использованием Excel:
При использовании методов планирования эксперимента изучаемыйобъект представляют в виде некоторого «черного ящика», выходные параметрыкоторого зависят от входных параметров. Математическая модель, отражающая связьмежду выходными и контролируемыми входными параметрами записывают в виде полиномаследующего вида:
/> (1)
Задачей обработки эксперимента является определение значенийкоэффициентов регрессионной модели. Расчет коэффициентов производят на основе методанаименьших квадратов, путем минимизации суммы квадратов разностей между экспериментальнымии рассчитанными по модели значениями. Естественно, что количество опытов в экспериментедолжно быть не меньше количества неизвестных коэффициентов в модели.
Для нахождения коэффициентов моделей типа (1) в Excel применяют встроенную функцию ЛИНЕЙН.Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьшихквадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимируетимеющиеся данные. Функция возвращает массив, который описывает полученную прямую.Поскольку возвращается массив значений, функция должна задаваться в виде формулымассива. Уравнение для прямой линии имеет следующий вид:
y = b+m1d1 + m2d2 +. (2)
где зависимое значение y является функцией независимыхзначений di. Значения mi — это коэффициенты, соответствующие каждой независимойпеременной di, а b — этопостоянная.
Сравнение формул (1) и (4) показывает, что если в качестве переменныхdi использовать значения переменных xi, а такжеразличные функции от xi, то коэффициент b в формуле (2)имеет смысл коэффициента b0 в формуле(1), а коэффициенты mi — соответственнокоэффициентов bi, bij, bii
Функция ЛИНЕЙН возвращает массив значений коэффициентов в обратномпорядке {mn; mn‑1;.; m1; b}. ЛИНЕЙН можеттакже возвращать дополнительную регрессионную статистику.
Синтаксис функции: ЛИНЕЙН (Y; D; K; C)
Здесь: Y — множество (обычно столбец)известных значений y
D — множество (обычно диапазон) известныхзначений d. Если множество Y — столбец, то диапазонD должен иметь столько же строк,сколько столбец значений Y. Количество столбцов диапазонаD определяет количество n неизвестных коэффициентов mi регрессионной модели
K — логическое значение, которое указывает,требуется ли, чтобы константа b была равна 0. Если K=1(имеет значение ИСТИНА), то b вычисляется обычным образом. Если K=0 (имеет значение ЛОЖЬ), то b полагается равным 0.
С — логическое значение, которое указывает, требуется ли вернутьдополнительную статистику по регрессии. Если С=1 (ИСТИНА), то функция ЛИНЕЙН возвращаетдополнительную регрессионную статистику (дополнительную информацию о регрессионнойстатистике можно получить воспользовавшись справкой Excel).Если С=0 (ЛОЖЬ) то функция ЛИНЕЙН возвращает только коэффициенты m и постояннуюb.
В настоящей работе математическая модель качества среза y1 и силы вырубки y2 записывают в видеполинома следующего вида:
/> (3)
Здесь x1 — значениезазора в кодовом масштабе, x2 — код материала,z1 — квадратичная функция от x1.
В натуральном масштабе матрица плана эксперимента выглядит следующимобразом: Номер опыта
X1
X2
Качество среза, y1
Сила вырубки [кН], y2 1 0,03 2 197 2 0,05 1 195 3 0,1 1 192 4 0,03 1 3 100 5 0,05 1 2 98 6 0,1 1 1 96
Для обработки данных эксперимента переходят к кодированному масштабу.В данной задаче по методике, изложенной в [1] получены следующие формулы для переходаот натурального к кодированному масштабу:
/> (4)
Оптимизацию полученных регрессионных моделей можно осуществитьдвумя способами: либо визуально, построив графики, либо используя методы оптимизации,реализованные в Excel.Последовательность выполнения
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 5 2 0,05 6 3 0,1 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формулами для подсчета произведений x1x2 и x2z1
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
Ä  Нажмите кнопкуВыполнить
Ä  Excel выполнит поиск оптимального решенияс заданными ограничениями, после чего появится окно Результаты поиска решений, вкотором следует нажать кнопку OK. Убедитесь, что Excel самостоятельно нашел правильноерешение.
Ä  Попытайтесьнайти самостоятельно зазор, обеспечивающий наихудшее качество
12. Постройте графики для Меди повторив пункты 9-11 на листе Медь (столбец x2 должен иметь значение 1, соответствующее коду меди). Пункт12 можно выполнить проще. Если догадаетесь как — то сэкономите себе время.
13. Отформатируйте таблицы и графики так, как это показано в приложении. Еслисможете — сделайте внешний вид полученных таблиц более привлекательным.
14. Завершить работу, сохранив ее в файле work3. xls.
15. Запустить EXCEL, вернуться к документу work3. xls и предъявить его преподавателю.
16. Предъявить преподавателю краткий конспект занятия.
Приложение
Лист Модель:
/>
Лист Сталь:
/>
Лист Медь:
/>Занятие 4 — Исследование операции вытяжки цилиндрическогостакана из плоской заготовкиЦели работы:
Ä  закреплениеосновных приемов создания и форматирования таблицы
Ä  освоение методовпостроения пространственных графиков и графиков с областями
Ä  освоение основныхметодов поиска решений средствами Excel
Ä  исследованиевлияния силы прижима, радиуса матрицы и толщины заготовки на напряжение в опасномсечении.Постановка задачи [2]:
Исследуется влияние силы прижима, радиуса матрицы и толщины заготовкина напряжение в опасном сечении на основании классических формул, полученных Е.А.Поповым.
Максимальную величину растягивающего напряжения srmax, действующую на границе пластически деформируемойчасти заготовки, без учета упрочнения материала можно определить по формуле
/> (1)
Здесь ss — напряжение текучести, k=R/r — коэффициент вытяжки, R — радиус заготовки, r=0,5 (dп+s) — радиус вытягиваемогостакана, dп — диаметр пуансона, s — толщина заготовки, Q — сила прижима,rм — радиус кромки матрицы, m — коэффициент трения.
Первое слагаемое в скобках отражает влияние напряженно-деформированногосостояния во фланце заготовки, второе — влияние сил трения на фланце заготовки отсилы прижима, третье — влияние изгиба-спрямления на кромке матрицы. Дополнительныймножитель 1+1,6m учитывает силы трения на кромке матрицы.
Сила прижима Q препятствуетпотере устойчивости заготовки во фланце (складкообразование) одновременно увеличиваянапряжения в опасном сечении.
Различные исследователи предлагают различные эмпирические формулыдля определения рациональной силы прижима. Так, например, И.А. Норицыным предложенапростая оценка рациональной силы прижима[3],как ¼ от силы вытяжки.
/> (2)
С учетом упрочнения формула (1) принимает следующий вид[4]:
/> (3)
Здесь sВ — предел прочности материала, yш — относительное уменьшение площади поперечного сечения к моменту образованияшейки.
При проведении занятия необходимо средствами Excel решить следующие задачи:
1. Для заданных геометрических параметров заготовки, матрицы и пуансона, а такжесвойств материала определить, исходя из формулы (1) максимальную силу прижима, прикоторой отсутствует разрушение, а также, исходя из формул (1) и (2) — рациональнуюсилу прижима.
2. Проанализировать вклад различных составляющих формулы (1), отражающих влияниенапряженно-деформированного состояния во фланце, сил трения во фланце и изгиба-спрямленияна кромке матриц, при изменении силы прижима от »Qр до »Qmax с шагом 10000 Н
3. Определить максимальный коэффициент вытяжки для заданных условий по формуле(3)
4. Построить пространственный график взаимного влияния толщины заготовки и радиусаматрицы на величину максимального напряжения в опасном сечении при рациональномзначении силы прижима.Методы решения с использованием Excel:
Для поиска решений в Excel применяются два основных средства, доступных из меню Сервис: Подбор параметра… и Поиск решения….
Средство Подбор параметра используют втом случае, когда необходимо найти определенный числовой результат в какой-либоячейке путем подбора значения другой ячейки. Типичный пример — решение алгебраическихуравнений: необходимо подобрать такое значение неизвестного, при котором праваяи левая часть уравнения равны между собой. В нашем случае это средство в наилучшейстепени подходит для решения задачи определения максимальной и рациональной силыприжима.
При выборе этого средства раскрывается диалоговое окно, имеющеенесколько полей:
—   В полеУстановить в ячейке введите ссылку на ячейку, содержащую необходимую формулу.
—   В полеЗначение введите искомый результат.
—   В полеИзменяя значение ячейки введите ссылку на ячейку, содержащую подбираемоезначение.
Средство Поиск решения имеет более общееназначение. Процедуру поиска решения можно использовать для определения значенийвлияющих ячеек, которое соответствует экстремуму зависимой ячейки (например, максимальныйкоэффициент вытяжки). Влияющие и целевая ячейки должны быть связаны формулой наодном листе, иначе при изменении значения одной не будет изменяться другая. НадстройкаПоиск решения позволяет задавать большое количество ограничений как на зависимую,так и на влияющие ячейки, что делает его мощным орудием анализа данный в Excel.
При выборе пункта Поиск решения из менюСервис открывается диалоговое окно следующего вида:
/>
В поле Установить целевую необходимо занестиссылку на зависимую ячейку — т.е. на ту ячейку, значение которой будет подбиратьсяпутем изменения значений во влияющих ячейках — их адреса заносятся в поле Изменяя ячейки. Можно осуществить поиск значений влияющих ячеексообщающих целевой ячейке максимальное, минимальное значения, либо значения, равныеконкретному числу (аналог Подбора параметра). Для этого необходимо выбрать нужныйпункт в группе радиокнопок Равной. В поле Ограниченияследует записать ограничения на влияющие и целевую ячейки. Добавление новых ограниченийосуществляется после нажатия на экранную кнопку Добавить.Открывающееся диалоговое окно позволяет накладывать ограничения в виде равенстви неравенств на значения в ячейках, а также требовать их целочисленности. Послезаполнения всех полей следует нажать экранную кнопку Выполнить.
При оформлении таблиц в заголовках часто приходится использоватьгреческие символы. К сожалению, в Excel отсутствует команда меню Вставить…Символ, как в Word. Поэтому рационально использовать следующую методику:
—   Вместогреческих символов ввести латинский эквивалент (наиболее распространенные греческиесимволы на латинской клавиатуре имеют следующий эквивалент:
—   a — a;b — b;d — d;D — D;e — e;h — h;g — g;l — l;m — m;n — n;w — w;j — j;p — p;
—   y — y;r — r;s — s;t — t;q — q;x — x).
—   Выделитьнабранный символ и воспользовавшись полем Шрифт панели инструментов Форматированиеизменить для этого символа тип шрифта на Simbol
Для создания верхних и нижних индексов следует воспользоватьсяаналогичной методикой. Сначала набрать символы, помещаемые в индексы, затем выделитьих в строке формул, нажать клавиатурную комбинацию Ctrl-1(либо выполнить команду меню Формат — Ячейки)и в открывшемся диалоговом окне щелкнуть мышью по флажку нижний индекс вгруппе Эффекты, после чего нажать экранную кнопку OK.Последовательность выполнения
17. Запустить EXCEL
18. На листе 1 создать таблицу с исходными данными эксперимента и поясняющиминадписями по следующему образцу (при заполнении таблиц пользуйтесь способами копированияи автозаполнения): A B C D E F G H I 1 Исследование операции вытяжки 2 Исходные данные 3
ss, МПа 200
sв, МПа 250
yш 0,35 4 D, мм 62 s, мм 2 5
dп, мм 37
rм, мм 3 m 0,15 6 Результаты расчета 7 d k 1+1,6*m 8 Qmax, Н
sф
sп
sм
srmax 9 10 Qрац, Н
sф
sп
sм
srmax P, Н 0.25*P d 11 12 Q, Н
sф
sп
sм
srmax фланец% прижим% матрица% 13 20000 14 30000 15 40000 16 50000 17 60000 18 70000 19 80000 20 k Qрац, Н
sф
sп
sм
srmax P, Н 0.25*P d
19. Занести в следующие ячейки формулы для определения диаметра стаканчика икоэффициента вытяжкиB7 =B5+D4 D7 =B4/B7
20. Занести в ячейку F7 формулу для определения коэффициента1+1,6*m, в выражениях (1) и(3).
21. В следующие ячейки занести формулы для определения составляющих максимальногонапряжения в опасном сечении по формуле (1) (предварительно внесите в ячейку A9 значение 10000, которое будет являться начальным для подборамаксимальной силы прижима) Ячейка Формула Вид формулы в Excel Значение в ячейке (для контроля правильности ввода) B9
/> =$B$3*LN ($D$7) *$F$7 114,966 C9
/> =$F$5*A9*2/ (ПИ () *$B$4 *$D$4) *$F$7 9,549 D9
/> продумать самостоятельно
22. В ячейку Е9 занести формулу для вычисления максимального напряжения в опасномсечении как сумму значений в ячейках B9: D9
23. Подобрать значение Qmax, воспользовавшись командойСервис-Подбор параметра. В открывшемся диалоговом окне заполнить поля следующимобразом
/> 
24. Значение 250 соответствует пределу прочности материала, что определяет предельноесостояние в опасном сечении. Нажать экранную кнопку ОК. После подбора параметрав окне Результаты подбора параметра также нажать экранную кнопку ОК
25. Подобрать рациональное значение силы прижима, для чего предварительно выполнитьследующие действия:
25.1. Скопировать формулы и значения из диапазона A9: E9 в диапазон A11: E11.
25.2. Занести в ячейку F11 формулу =ПИ () *$B$4*$D$4*E11для определения максимальной силы вытяжки по формуле (2)
25.3. Занести в ячейку G11 формулу для определения силыприжима как ¼ от силы вытяжки.
25.4. Занести в ячейку H11 формулу для определения ошибки,как разницы между предположенным значением силы прижима в ячейке A11 и полученным значением рациональной силы прижима в ячейкеG11.
25.5. С помощью Сервис — Подбор параметра определить величину рациональной силыприжима, поставив задачу следующим образом: «Изменяя значение предположеннойвеличины силы прижима в ячейке A11 добиться того, чтобыошибка вычисления силы была равна 0»
26. В ячейках A13: H19 подготовитьданные для построения графика степени влияния различных слагаемых в формуле (1)на величину максимального напряжения в опасном сечении при изменении силы прижимав диапазона 10000…80000 Н с шагом 10000Н. Указания:
Ä  для заполненияячеек B13: E19 воспользуйтесь ужеготовыми формулами в ячейках A11: E11;
Ä  для быстротызаполнения ячеек F13: H19 воспользуйтесьсмешанной адресацией введя в ячейку F13 следующую формулудля вычисления относительного влияния фланца =B13/$E13;
Ä  распространитеформулу в ячейке F13 на диапазон F13: H19;
Ä  задайте вдиапазоне F13: H19 процентный формат представления числа, воспользовавшись кнопкой% на панели Форматирование.
27. Отобразите графически результаты, представленные в ячейках F13: H19, воспользовавшисьследующей последовательностью операций:
27.1. Выделите диапазон F12: H19и нажмите экранную кнопку Мастер диаграмм на Основной панели инструментов.
27.2. В открывшемся диалоговом окне выберите Тип диаграммы: С областями, Вид: Нормированнаядиаграмма (третья слева в верхнем ряду), нажмите кнопку Далее.
27.3. На вкладке Ряд (Шаг 2 мастера диаграмм) в поле Подписи оси Х внесите диапазон:=Лист1! $A$13: $A$19, нажмите кнопку Далее;
27.4. На вкладке Подписи данных (Шаг 3 мастера диаграмм) щелкните мышью в пунктекатегория группы Подписи значений; на вкладке Легенда уберите флажок в пункте Добавитьлегенду; на вкладке Заголовки в поле Ось Х наберите: Q,Н, нажмите экранную кнопку Готово
27.5. Переместите полученную диаграмму в диапазон A22:F39, изменив размеры с помощью размерных маркеров.
27.6. Измените цвет заливки областей так, чтобы были хорошо видны надписи внутри.Для этого сначала необходимо щелкнуть мышью по области так, чтобы она выделилась,а затем использовать кнопку Цвет заливки на панели инструментов Форматирование.Примерный вид получившейся диаграммы приведен ниже:
/>
28. Определите максимальное значение коэффициента вытяжки для заданных значенийпараметров материала, параметров заготовки и матрицы, используя формулу (3). Дляэтого выполните следующую последовательность действий:
28.1. В ячейки A21 и B21 занеситеначальные значения коэффициента вытяжки и величины силы прижима (соответственно1,5 и 10000).
28.2. В ячейки С21: E21 занесите следующие формулы: Ячейка Формула Вид формулы в Excel Значение в ячейке (для контроля правильности ввода) C21
/> =$D$3* (LN ($A$21) * ( (1+$A$21) /2) ^$F$3) ^ (1/ (1-$F$3)) *$F$7 87,17572 D21
/> продумать самостоятельно 9,549297 E21
/> продумать самостоятельно 77,5
28.3. Ввести в ячейки F21: I21формулы аналогично ячейкам E11: H11.Результаты в ячейках должны получиться следующими:
srmax P, Н 0.25*P d 174,225 67870,66 16967,66 -6967,66
28.4. Воспользоваться надстройкой Поиск решения. В диалоговом окне Поиск решениявнести следующие данные:
/>
28.5. Нажать кнопку Выполнить и после окончания процесса поиска решения, которыйможет продлиться несколько секунд, нажать кнопку ОК в окне Результаты поиска решения.
29. Построить пространственный график влияния толщины заготовки и радиуса кромкиматрицы на величину напряжения в опасном сечении, для чего выполнить следующие подготовительныедействия:
29.1. Скопировать данные и формулы из диапазона A1: H11 листа 1 в тот же диапазон на листе 2 (воспользуйтесь кнопкамиКопировать и Вставить на панели инструментов Основная). Все дальнейшие действияпроизводить на листе 2
29.2. Удалите строки с 8 по 9
29.3. Перенесите (не копировать!) содержимое следующих ячеек: из C5 в I8; из D5в I9
29.4. В формуле, содержащейся в ячейке D9 замените абсолютныйадрес ячейки $I$9 на смешанный $I9(это позволит в дальнейшем воспользоваться приемом автозаполнения).
29.5. В ячейки I9: I13 занеситеварьируемые значения радиуса скругления кромки матрицы 3; 5; 7; 9; 11
29.6. Используя прием автозаполнения, распространите формулы и значения из диапазонаA9: H9 на диапазон A9: H13.
29.7. Убедитесь в том, что значения силы прижима для радиусов матрицы отличныхот 3 нерациональны (ошибка ¹0). Дляпостроения требуемых зависимостей следует подобрать рациональное значение силы прижимадля всех радиусов матрицы, в противном случае результаты будет трудно сравнивать.Для подбора рациональных значений силы прижима можно воспользоваться средством Подборпараметра, как это было сделано выше. Однако быстрее результат будет получен, еслииспользовать Поиск решения:
29.7.1. Занесите в ячейку H14 формулу суммарной ошибки определениясилы прижима для всех 5-и значений радиусов матриц
29.7.2. Сформулируйте задачу поиска решения так, как это показано ниже и выполнитепоиск. Убедитесь в том, что нужное решение было найдено сразу для всех значенийрадиусов матриц.
/>
29.8. Создайте в ячейках A15: G21следующую вспомогательную таблицу A B C D E F G 15 1 2 3 4 5 s, мм 16 3 17 5 18 7 19 9 20 11 21
rм, мм
29.9. Скопируйте значения (а не формулы!) из диапазона E9:E13 в диапазон C16: C20 (поскольку значения максимального напряжения в опасном сечениибыли получены для толщины заготовки 2 мм — см. значение в ячейке D4). Для копирования значений пользуются следующим приемом:
29.9.1. Выделите копируемый диапазон E9: E13 и нажмите кнопку копировать
29.9.2. Перейдите в ячейку C16 (начальная ячейка диапазонакуда копируют) и выполните команду меню Правка-Специальная вставка
29.9.3. В открывшемся диалоговом окне щелкнуть мышью в пункте Значения группы Вставитьи нажать экранную кнопку ОК.
29.10. Меняя последовательно значения толщины заготовки в ячейке D4 на 1, 3,4,5 выполните подбор силы прижима в соответствие сп.12.7.2 и скопируйте полученные значения напряжений в соответствующий столбец таблицып12.8 по методике 12.9.
30. Отобразите графически результаты, представленные в ячейках B16: F20, воспользовавшись следующей последовательностьюопераций:
30.1. Выделите диапазон A15: F20и нажмите экранную кнопку Мастер диаграмм на Основной панели инструментов.
30.2. В открывшемся диалоговом окне выберите Тип диаграммы: Поверхность, Вид: Поверхность(пиктограмма в верхнем левом углу группы вид), нажмите кнопку Далее.
30.3. Ничего не меняя на Шаге 2 мастера диаграмм, нажмите кнопку Далее;
30.4. На вкладке Заголовки (Шаг 3 мастера диаграмм) в поле Ось Х наберите: s, мм, в поле Ось Y — r, мм и нажмите экранную кнопку Готово
30.5. Переместите полученную диаграмму в диапазон A22:F39, изменив размеры с помощью размерных маркеров.
30.6. Выполните форматирование полученной диаграммы:
30.6.1. Измените диапазон изменения по оси Z. Щелкните мышью по оси так, чтобы онавыделилась
30.6.2. Выполните команду меню Формат — Выделенная ось.
30.6.3. В открывшемся диалоговом окне на вкладке Шкала в поле Минимальное значениевведите 140, в поле максимальное значение — 240, в поле цена основных делений — 20. Нажмите экранную кнопку OK.
30.6.4. Щелкните правой клавишей мыши по пустому полю диаграммы вблизи правого верхнегоугла. В открывшемся контекстном меню выберите пункт Объемный вид…
30.6.5. Изменяя значения в полях Возвышение, Перспектива и Поворот (это можно сделатьс помощью экранных кнопок с соответствующими пиктограммами) добейтесь наиболее информативноговида диаграммы. Результаты изменения объемного вида можно просмотреть не закрываядиалогового окна, нажав на экранную кнопку применить. По нашему мнению неплохойвид диаграмма получит при значениях Возвышение=15, Перспектива=30, Поворот=160.Примерный вид диаграммы приведен ниже
/>
30.6.6. Попытайтесь поэкспериментировать с другими возможностями форматирования объемнойдиаграммы, не забывая, что прежний вид всегда можно вернуть, нажав на экранную кнопкуОтменить (не более 3-х шагов назад)
31. Завершить работу, сохранив ее в файле work3. xls.
32. Запустить EXCEL, вернуться к документу work3. xls и предъявить его преподавателю.
33. Предъявить преподавателю краткий конспект занятия.
Занятие 5 — Обработка данных экстремальных экспериментовна примере исследования операции вытяжки листовых образцовЦели работы:
Ä  закреплениеосновных приемов создания и форматирования таблицы
Ä  освоение методовпоиска решений с помощью встроенных средств ExcelПостановка задачи [5]:
Рассматривается задача отыскания максимального коэффициента вытяжкицилиндрического стаканчика из листовой заготовки с использованием метода крутоговосхождения Бокса-Уилсона.
Напомним, что коэффициентом вытяжки k=D/d, где D — диаметрисходной заготовки, d — диаметр вытянутого из этой заготовкистаканчика. Предельная величина коэффициента вытяжки за один переход ограниченавеличиной максимальных напряжения srmax во фланце заготовки. Разрушениезаготовки произойдет тогда, когда srmax достигнет предела прочностиматериала sВ.При этом значение коэффициента вытяжки является максимальным. На величину предельногокоэффициента вытяжки, помимо других факторов, влияет радиус скругления кромки матрицы(rм) и скорость деформирования (V).
Идея экстремальных экспериментов заключается в линейной аппроксимациигиперповерхности отклика, оценке составляющих градиента и проведении серии«мысленных» опытов (т.е. без выполнения реального эксперимента) в направленииоптимума.
Метод планирования эксперимента Бокса-Уилсона включает в себяпостроение линейной модели исследуемого объекта в виде y=b0+b1x1+b2x2+¼+bnxn,где n — количество факторов. В этом случае оценкамисоставляющих вектора градиента являются коэффициенты полинома. Для движения по градиентунеобходимо менять факторы пропорционально величинам коэффициентов. Такая процедураназывается крутым восхождением. При движении по градиенту факторы изменяют с определеннымшагом. Шаги изменения рассчитывают в натуральном масштабе.
Таким образом процедура решения задачи сводится к следующей последовательностишагов:
Ø  проведениенатурного эксперимента, для получения линейной модели y=b0+b1x1+b2x2+b3x3 (1), где y=srmax; x1, x2, x3 — факторы k, rм,V в кодовом масштабе(результаты натурного эксперимента заданы в качестве исходных данных);
Ø  определениюкоэффициентов bi линейноймодели;
Ø  определениюсоставляющих градиента в натуральном масштабе;
Ø  осуществлениикрутого восхождения — т.е. подбора такого шага в направлении градиента из центраплана, при котором в формуле (1) y =sВ.Предел прочности материала, используемого в опытах 310 МПаМетоды решения с использованием Excel:
Для определения коэффициентов линейной модели (1) достаточнопровести 4 опыта. Интервалы варьирования факторов DXi относительно центра плана Xi0в проведенном натурном эксперименте приведены в таблице 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
(srmax, МПа) 1 -1 -1 -1 189 2 1 -1 1 236 3 -1 1 1 167 4 1 1 -1 223
Коэффициенты регрессионной модели (1) определяют с помощью ужеизвестной вам функции ЛИНЕЙН. Если вы забыли синтаксис функции ЛИНЕЙН, воспользуйтесьвстроенной справкой Excel.
Исходная точка для крутого восхождения — центр плана с координатамив натуральном масштабе:
X1о=1.3, X2о=3, X3о=0.5(3)
Составляющие градиента вычисляются по формуле
 
gi=biDXi(4)
Шаг крутого восхождения в натуральном масштабе по каждой из координат
 
Xi=Xi0+gi*S (5)
Подбор такой кратности шагов S, одинаковойдля всех координат, при котором y =sВосуществляют с помощью команды меню Excel «Сервис-Подборпараметра».Последовательность выполнения
34. Запустить EXCEL
35. Переименуйте Лист1 книги в «Крутое восхождение». Остальные листыкниги удалите. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листаи в открывшемся контекстном меню выбрать пункт Удалить.
36. На листе Крутое восхождение создать таблицу с исходными данными экспериментаи поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесьспособами копирования и автозаполнения): 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
srmax, МПа 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) /DXi
 
  23
S
X1
X2
X3
x1
x2
x3
srmax 24
37. В диапазон A14: D14 введите формулу для определениякоэффициентов регрессии для модели напряжений srmax,используя функцию ЛИНЕЙН и мастер функций. Последовательность действий приведенаниже:
Ä  Выделить A14:D14
Ä  Меню Вставка-Функция(или кнопка Вставка функции)
Ä  Категория- Статистические, Функция — ЛИНЕЙН, Кнопка OK
Ä  Окно Изв_знач_y- диапазон известных значений srmax
Ä  Окно Изв_знач_x — диапазон значений xi в опытах
Ä  Окно Константа- 1
Ä  Окно Стат- 0
Ä  Нажать клавиатурнуюкомбинацию Ctrl-Shift-Enter
Ä  Формула массивавставится в выделенный диапазон и в нем появятся значения коэффициентов
38. Введите в диапазон A17: C17формулы (4) для определения составляющих градиента. Обратите внимание, что порядокследования составляющих градиента gi в диапазоне A17: C17и порядок следования коэффициентов bi в диапазоне A14: С14 не совпадают.
39. Обратите внимание, что полученные значения составляющих градиента для факторов2 и 3 имеют отрицательные значения. Это означает, что шаги в этом направлении приведутк уменьшению srmax.Поэтому мы должны двигаться в направлении антиградиента по этим факторам (напомним,что направление градиента — это направление увеличения функции). Таким образом вячейки A20: C20 следует внести следующиеформулы:
Ä  A20 à =A17
Ä  B20 à = — C17
Ä  C20 à = — C17
40. Занесите в ячейку A24 значение 1, являющееся начальнымзначением кратности шагов S
41. Занесите в диапазон B24: D24формулы (5) для определения значений варьируемых факторов в натуральном масштабе.Составляющие градиента расположены в ячейках A20: C20. Значения центра плана Xi0 — в ячейках B4: D4. Для использованияавтозаполнения в формулах следует вводить абсолютный адрес ячейки $A$24
42. В ячейки E24: G24 внеситеформулы (2) для перехода от натурального масштаба к кодированному.
43. В ячейку H24 введите формулу для определения srmaxпо значениям факторовxi в ячейках E24: G24 в соответствии с моделью (1).Коэффициенты модели bi были определены вами ранее в ячейках A14:D14. При вводе формулы обратите внимание на обратный порядок значений коэффициентовbi в диапазоне A14: D14. Обратите внимание, что величина напряженияполучилась значительно большей, чем максимально возможное значение 310 МПа.
44. Меняя значение в ячейке A24 попытайтесь вручную подобратьтакую кратность шагов S, при которой значение напряженияв ячейке H24 было бы равно 310 МПа.
45. Верните в ячейку A24 значение 1 и подберите точноезначение с помощью встроенных в Excel средствавтоматического подбора значений. Для этого:
Ä  Выполнитекоманду меню Сервис-Подбор параметра
Ä  В открывшемсяокне Подбор параметра введите следующие значения, используя мышь или клавиатуру:
Ø  в поле Установитьв ячейке: $H$24
Ø  в поле Значение:310
Ø  в поле Изменяязначение ячейки: $A$24
Ä  Нажмите кнопкуOK. Убедитесь, что решение найдено.
46. Решение поставленной задачи можно сформулировать так: “Может быть достигнуткоэффициент вытяжки, равный значению в ячейке B24, при использованииматрицы, радиус скругления которой в мм приведен в ячейке C24и проведении вытяжки со скоростью деформирования, значение которой в мм/с приведенов ячейке C24».
47. Отформатируйте таблицы и графики так, как это показано в приложении. Еслисможете — сделайте внешний вид полученных таблиц более привлекательным.
48. Завершить работу, сохранив ее в файле work5. xls.
49. Запустить EXCEL, вернуться к документу work4. xls и предъявить его преподавателю.
50. Предъявить преподавателю краткий конспект занятия.
Приложение:
/>Занятие 6 — Исследованиеоперации обжимаЦели работы:
Ä  закреплениеосновных приемов создания и форматирования таблицы, построения графиков различноговида и поиска решений в Excel
Ä  создание модулядля автоматизации обработки результатов эксперимента по исследованию операции обжима.Постановка задачи:
В ходе настоящей работы необходимо создать электронную таблицу,позволяющую автоматизировать результаты обработки экспериментальных данных по исследованиюоперации обжима и используя теоретические зависимости исследовать влияние отдельныхпараметров на напряжение в опасном сечении. Схема операции обжима с образованиемцилиндрической части приведена ниже.
/>
Максимальную величину сжимающих напряжений srmax, действующих в недеформируемых стенках заготовки,без учета упрочнения материала можно определить по формуле
/> (1)
Максимальная сила деформирования
/> (2)
Здесь ss — напряжение текучести, m — коэффициент трения,
r0=0,25 (dн+dв), (3),Rз=0,5 (Dн+s0). (4)
При проведении эксперимента по обжиму трубы Dн=28 мм, s0=2 мм были полученыследующие результатыa, градусы
dн, мм
dв, мм
Pmax, Н 10 22,8 18 27800 15 22,4 17,4 30000 20 22,5 17,8 29000 25 22 17 35900 30 21,6 16,4 41000
При проведении занятия необходимо средствами Excel решить следующие задачи:
5. Используя формулу (2) определить экспериментальное значение для различныхуглов конусности матрицы srmax.
6. Используя формулу (1) для ss =250 МПа, m=найтирасчетные значения srmax
7. Определить ошибку расчета по сравнению с экспериментом.
8. Построить графики изменения максимального напряжения от угла конусности порезультатам расчета и эксперимента
9. Определить оптимальное значение угла конусности для значения r0=10 мм исходя из минимального значения напряженийпо формуле (1)
10. Построить пространственный график взаимного влияния толщины заготовки и радиусаобжима r0на величину максимального напряженияпри найденном оптимальном значении угла конусности.Методы решения с использованием Excel:
Методы решения поставленных задач с использованием Excel описаны в предыдущих работах. Внастоящей работе все действия по созданию и форматированию таблицы производятсяс самостоятельноПоследовательность выполнения
51. Запустить EXCEL
52. На лист 1 занести заголовок, исходные данные для расчета (см. приложение).
53. Определить радиус заготовкиRз по формуле (4)
54. Занести в таблицу результаты эксперимента (см. приложение)
55. Для a=10° определить
55.1. r0по формуле (3)
55.2. srmaxэкспериментальноепо формуле (2)
55.3. srmaxрасчетноепо формуле (1) (обратить внимание на необходимость применения как абсолютной, таки относительной адресации, в противном случае будет невозможно пользоваться методомавтозаполнения)
55.4. определить относительную ошибку расчета e в %
56. Распространить формулы, записанные в строке для a=10° на строки с остальнымизначениями углов. Прежде, чем двигаться дальше, сравните Ваши результаты с даннымипреподавателя, и исправьте ошибки, если они есть.
57. На основании полученных данных постройте графики изменения максимальных напряженийв зависимости от угла конусности матрицы по результатам расчета и эксперимента.(см. приложение).
58. Используя команду Сервис-Поиск решения определите оптимальный угол конусностидля r0=10 по формуле (1).
59. Создайте вспомогательную таблицу для построения пространственного графиказависимости максимального напряжения от толщины заготовки s0=1,2,3,4,5мм и радиуса обжима r0=8,9,10,11,12 мм. Внешнийвид такой таблицы приведен в приложении.
60. Заполните вспомогательную таблицу. Это можно сделать записав в одной из ячеекобщую формулу с использованием абсолютных, относительных и смешанных адресов, азатем распространив эту формулу на всю таблицу. Сравните полученные результаты сданными преподавателя и исправьте ошибки если они есть.
61. Постройте пространственный график по данным таблицы
62. Отформатируйте таблицу и графики так, как это показано в приложении.
63. Завершить работу, сохранив ее в файле work6. xls.
64. Запустить EXCEL, вернуться к документу work6. xls и предъявить его преподавателю.
65. Предъявить преподавателю краткий конспект занятия.
Приложение: Пример форматирования итоговой таблицы и графиков(результаты в таблицах не показаны).
/>
/>/>Занятие 7 — Оптимизацияраскроя листового материалаЦели работы:
Ä  освоение методоврешения задач оптимизации с использованием ExcelПостановка задачи:
В ходе настоящей работы необходимо создать электронную таблицу,позволяющую произвести оптимизацию раскроя листа для вырубки круглых заготовок.Схема, поясняющая постановку задачи приведена ниже.
/>
Необходимо выбрать наилучшие размеры и найти наилучший вариантраскроя листа шириной B и длинойL. Диапазон возможного изменения длин листа: L=2000…3000 мм с шагом 100 мм, диапазон возможного изменения ширины листа: В=600…1100 мм с шагом 50 мм. Предварительно выбрана схема двухрядногокосого раскроя, что определяется размерами штампового пространства используемогопрессового оборудования.
Наилучший вариант раскроя обеспечивает наибольший коэффициентиспользования металла, который может быть рассчитан следующим образом:
/>
Здесь N — количество заготовок, помещающихсяна листе.
Для сравнения вариантов, одинаковых по величине коэффициентаиспользования металла, можно использовать дополнительный показатель — площадь концевогоотхода, остающегося при раскрое полосы. Можно ожидать, что чем больше эта площадь,тем с большей эффективностью можно использовать концевой отход для других производственныхнужд. Для приведения этого критерия к безразмерному виду можно отнести величинуплощади концевого отхода к максимальной площади листа, который может быть использован.Как следует из изложенного выше, максимальная площадь листа составляет Bmax´Lmax=1100´3000мм2. Тогда критерий площади концевого отхода может быть рассчитан как:
/>
В качестве обобщенной целевой функции F(критерия качества), может быть использована аддитивная функция, в которой критерийплощади концевого отхода z взят свесовым коэффициентом 0.1, как менее важный, по сравнению с критерием использованияматериала h.
/>
В такой постановке целевая функция зависит от ширины и длиныисходного листа, которые могут изменяться с определенным шагом и угла косого раскрояa, который может изменяться вобщем случае в пределах от 0 до 90 градусов непрерывно.
Таким образом, задача оптимизации сформулирована следующим образом(см. курс «Основы автоматизированного проектирования»):
—  Целевая функция: F® max
—   Векторуправляемых параметров: B, L, a
—   Ограниченияна управляемые параметры: B=600…1100 с шагом 50, L=2000…3000 с шагом 100, 0£a£90
Ниже приведен возможный алгоритм вычисления целевой функции врамках поставленной задачи. Для определенности варьируемым (управляемым) параметрамзаданы начальные значения. Величина ширины перемычек a и a1 (см. чертеж) приняты постояннымидля диаметров детали в пределах 100…200 мм. Наименование Формула (пояснение) 1
Кратность по ширине *
iB=5 (варьируемый параметр iB=0…10) 2 Кратность по длине *
iL=5 (варьируемый параметр iL=0…10) 3 Угол косого раскроя a=60 (варьируемый параметр 0£a£90) 4 Ширина листа
B=600+50iB, 5 Длина листа
L=2000+100iL, 6 Диаметр детали
D=141 7 Ширина перемычек
a1=2 8
a=2,5 9 Мин. ширина полосы
/> 10 Количество полос
/>, округлить до меньшего целого 11
Шаг между заготовками в ряду **
/> 12 Количество заготовок в ряду
/>, округлить до меньшего целого 13 Длина ряда
/> 14
Наличие дополнительной заготовки в ряду ***
/> 15 Количество заготовок в полосе
Nзп=2Nзр+Nдз 16 Количество заготовок в листе
N=NпNзп 17 Коэффициент использования металла
/> 18 Ширина концевого отхода
Bo=B-NпBпmin 19 Целевая функция
/>
Пояснения к алгоритму:
* Поскольку ширина и длина листа могут изменятьсяне непрерывно, а с определенным шагом, то алгоритмически проще варьировать количествомтаких шагов (их кратностью) несколько преобразовав формулу для определения соответственноширины и длины шага (см. пп4,5)
** Шаг между заготовками в ряду при углах косого раскрояменее 60° увеличивается за счет того, чтозаготовки из соседних рядов при величине перемычки равной a1начинают накладываться друг на друга.
*** Дополнительную заготовку можно разместить в нижнемряду в том случае, если длина ряда окажется меньше длины полосы на величину диаметрас учетом перемычки
При проведении занятия необходимо средствами Excel решить следующие задачи:
11. Используя алгоритм, приведенный выше, составить электронную таблицу, позволяющуюрассчитать целевую функцию для произвольного диаметра D варьируя размерами листа и углом косого раскроя.
12. Проанализировать влияние угла раскроя на величину коэффициента использованияметалла, ширину концевого отхода и обобщенную целевую функцию при значения диаметразаготовки D=141 мм и D=120 мм, построивсоответствующие графики.
13. Используя методы поиска решений в Excel определить все параметры для нахожденияоптимального значения варьируемых параметров (угол раскроя и размеры листа)
14. Найти оптимальный раскрой листа для значения диаметра заготовки, заданногопреподавателем.Методы решения с использованием Excel:
Для решения задач оптимизации в Excel используют уже изученную нами надстройку Поиск решения, диалоговоеокно которой вызывается по команде Сервис-Поискрешения (см. работу 3). Здесь мы поясним дополнительные возможности настройкиалгоритма поиска решения. Окно настройки вызывают нажатием экранной клавиши Параметры диалогового окна Поиск решения.
/>
Диалоговое окно «Параметры поиска решения» служит дляизменения условия и вариантов поиска решения для линейных и нелинейных задач, атакже для загрузки и сохранения оптимизируемых моделей. Значения и состояния элементовуправления, используемые по умолчанию, подходят для решения большинства задач.
Максимальное время — Служит для ограничения времени, отпускаемогона поиск решения задачи. В поле можно ввести время (в секундах) не превышающее 32767;значение 100, используемое по умолчанию, подходит для решения большинства простыхзадач.
Итерации — Служит для управления временем решения задачи, путемограничения числа промежуточных вычислений. В поле можно ввести время (в секундах)не превышающее 32767; значение 100, используемое по умолчанию, подходит для решениябольшинства простых задач.
Точность — Служит для задания точности, с которой определяетсясоответствие ячейки целевому значению или приближение к указанным границам. Поледолжно содержать число из интервала от 0 (нуля) до 1. Чем меньше введенное число,тем меньше точность. Высокая точность увеличит время, которое требуется для того,чтобы сошелся процесс оптимизации.
Допустимое отклонение — Служит для задания допуска на отклонениеот оптимального решения, если множество значений влияющей ячейки ограничено множествомцелых чисел. При указании большего допуска поиск решения заканчивается быстрее.
Сходимость — Когда относительное изменение значения в целевойячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость,поиск прекращается. Сходимость применяется только к нелинейным задачам, условиемслужит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большееколичество десятичных знаков ѕ например, 0,0001 ѕ это меньшее относительное изменение,чем 0,01. Лучшая сходимость требует больше времени на поиск оптимального решения.
Линейная модель — Служит для ускорения поиска решения линейнойзадачи оптимизации или линейной аппроксимации нелинейной задачи.
Показывать результаты итераций — Служит для приостановки поискарешения для просмотра результатов отдельных итераций.
Автоматическое масштабирование — Служит для включения автоматическойнормализации входных и выходных значений, качественно различающихся по величиненапример, максимизация прибыли в процентах по отношению к вложениям, исчисляемымв миллионах рублей.
Значения не отрицательны — Позволяет установить нулевую нижнююграницу для тех влияющих ячеек, для которых она не была указана в поле Ограничениедиалогового окна Добавить ограничение.
Оценка — Служит для указания метода экстраполяции (линейная иликвадратичная) используемого для получения исходных оценок значений переменных вкаждом одномерном поиске.
Линейная — Служит для использования линейной экстраполяции вдолькасательного вектора.
Квадратичная — Служит для использования квадратичной экстраполяции,которая дает лучшие результаты при решении нелинейных задач.
Производные — Служит для указания метода численного дифференцирования(прямые или центральные производные) который используется для вычисления частныхпроизводных целевых и ограничивающих функций.
Прямые — Используется для гладких непрерывных функций.
Центральные — Используется для функций, имеющих разрывную производную.Не смотря на то, что данный способ требует больше вычислений, он может помочь приполучении итогового сообщения о том, что процедура поиска решения не может улучшитьтекущий набор влияющих ячеек.
Метод — Служит для выбора алгоритма оптимизации (метод Ньютонаили сопряженных градиентов) для указания направление поиска.
Ньютона — Служит для реализации квазиньютоновского метода (методвторого порядка), в котором запрашивается больше памяти, но выполняется меньше итераций,чем в методе сопряженных градиентов.
Сопряженных градиентов — Служит для реализации метода сопряженныхградиентов (метод первого порядка), в котором запрашивается меньше памяти, но выполняетсябольше итераций, чем в методе Ньютона. Данный метод следует использовать, если задачадостаточно велика и необходимо экономить память, а также если итерации дают слишкоммалое отличие в последовательных приближениях.
Загрузить модель — Служит для отображения на экране диалоговогоокна Загрузить модель, в котором можно задать ссылку на область ячеек, содержащихзагружаемую модель.
Сохранить модель — Служит для отображения на экране диалоговогоокна Сохранить модель, в котором можно задать ссылку на область ячеек, предназначеннуюдля хранения модели оптимизации. Данный вариант предусмотрен для хранения на листеболее одной модели оптимизации (первая модель сохраняется автоматически).
Дополнительно, к использованным ранее тригонометрическим функциям,функции ПИ () и функции РАДИАНЫ (), в данной задаче будут полезны следующие функции:
ЦЕЛОЕ (число): округляет число вниз до ближайшегоцелого.
Аргумент число, может быть числом, для которого вы хотитенайти следующее наименьшее целое, либо ссылкой на ячейку, в которой вычисляетсяокругляемое число, либо формулой, в результате которой будет получено округляемоечисло.
ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь): служит для получения в ячейке результата,зависящего от некоторых условий.
Аргумент логическое_выражение служит для записи условия,в котором сравниваются числа, функции, формулы. Любое логическое выражение должносодержать по крайней мере один оператор сравнения, который определяет отношениемежду элементами логического выражения. Такими операторами могут быть: > (больше),= (больше либо равно),(не равно). В качестве аргументов логического выражения можно использовать числа,ссылки не другие ячейки, другие функции, а также формулы.
Аргументы значение_если_истина изначение_если_ложьпредставляют собой числа или формулы для вычисления чисел, которые должны использоватьсядля расчета значений в ячейке если значение логического выражения является соответственноистиной, либо ложью.Последовательность выполнения
66. Запустить EXCEL
67. На листе 1 создать таблицу для вычисления целевой функции в соответствиис алгоритмом. Внешний вид таблицы вместе с исходными данными и результатами (длясправки) приведен в приложении.
68. Создать рядом с основной таблицу для варьирования величиной угла раскрояв пределах от 55 до 90 градусов с шагом в 5 градусов.
69. Определить значения коэффициента использования металла, целевой функции иширины концевого отхода в зависимости то угла раскроя для D=141при iВ=iL=5.Результаты поместить во вспомогательную таблицу на Лист2 (копируйте значения, ане формулы).
70. Повторить вычисления для D=120 при iВ=iL=5. Результатытакже поместить на Лист2.
71. Построить графики изменения полученных величин. Вид графиков, которые должныполучиться приведены в приложении 2.
72. Задать необходимые параметры для поиска оптимального решения. Целевая ячейка- в которой вычисляется F. Изменяемые ячейки — те в которыхпомещены значения, iB, iL,a. Ограничения: 0£a£90, 0£iВ£10, 0£iL£10, iB, iL — целые. Рекомендуемыпараметры окна настройки поиска решения приведены на рисунке выше.
73. Найти оптимальное решение для D=141 и D=120 мм. При поиске решения следует иметь ввиду, что целеваяфункция является многоэкстремальной. Для таких функций невозможно гарантироватьполучение глобального экстремума из любой начальной точки. Поэтому используетсяметод пробных начальных точек. Выбирают несколько начальных точек в различных областяхпространства управляемых параметров. Производят оптимизацию из каждой начальнойточки и за глобальный оптимум принимают наилучшее из полученных решений. В качественачальных пробных точек в данной задаче рекомендуется использовать следующие: Номер начальной точки 1 2 3 4 5 a 70 70 70 70 70
iВ 1 10 5 10 10
iL 1 10 5 1 1
74. Найти оптимальное решение для значения диаметра, предложенное преподавателем.
75. Построить для найденных оптимальных размеров листа графики изменения коэффициентаиспользования металла, целевой функции и ширины концевого отхода на листе 3.
76. Завершить работу, сохранив ее в файле work7. xls.
77. Запустить EXCEL, вернуться к документу work5. xls и предъявить его преподавателю.
Приложение 1: Внешнийвид таблицы и результаты вычислений (для справки) Кратность по ширине
iB 2 Кратность по длине
iL 2 Угол косого раскроя a 60 55 60 65 70 75 80 85 90 Ширина листа B 700 Длина листа L 2200 Диаметр детали D 141 Ширина перемычек a1 2 a 2,5 ` Мин. ширина полосы Bmin 269,8416 Коичество полос Nп 2 Шаг в ряду S 143,0000 Количество заготовок в ряду Nзр 14 Длина ряда Lр 2005 Наличие дополнительной заготовки в ряду Nдз 1 Количество заготовк в полосе Nзп 29 Количество заготовок в листе N 58 Коэффициент использования металла h 0,5881 Ширина концевого отхода Bо 160,3167 Целевая функция F 0,5988

Приложение 2:
/> />
/>Занятие 8 — Обработка данных экспериментов с несколькимивыходными переменнымиЦели работы:
Ä  освоение методовобработки сложных экспериментов с помощью средств Excel
Ä  освоение методовпостроения пространственных диаграмм в ExcelПостановка задачи [6]:
Рассматривается задача отыскания методов обработки бронзы А10Ж3Мц1,5(температуры и скорости деформирования), которые бы обеспечивали наилучший комплексмеханических свойств (предела текучести s, пределапрочности s, относительного удлинения d, относительного сужения Ψ).
Для оптимизации комплекса свойств следует рассмотреть обобщеннуюфункцию желательности, которая в нашем случае имеет вид:
/> (1)
где di — частные функциижелательности по каждому из показателей комплекса свойств.
/> (2)
Здесь y — условнаяшкала, имеющая линейную связь со значениями показателей комплекса свойств s
/> (3)
/>
Коэффициенты a0, a1определяют по двум «реперным» точкам,в которых исследователь задает соответствие истинным значениям комплекса свойствопределенное с его точки зрения значение функции желательности. Шкала функции желательностивыглядит следующим образом: d=0…0,368 (y= — ∞…0) — недопустимый уровенькачества; d=0,368…0,63 (y=0…0,77) — допустимый уровень качества; d=0,63…0,8 (y=0,77…1,5) — хорошийуровень качества; d=0,8…1 (y=1,5…∞)- превосходный уровень качества. Зависимость d=f (y) приведена на рисунке.
Очевидны следующие соотношения:
/> (4)
Здесь. y,s — пары реперных точек. В нашем случае предлагаетсяследующие значения реперных точек: Характеристика
sT
sB
d
Y Значение, s 25 21,5 44 29,6 23 26,2 45 60 Функция желательности, d 0,368 0,63 0,368 0,8 0,368 0,8 0,368 0,95 Условная шкала, y 0,77 1,5 1,5 3
Для обобщенной желательности строится в кодированном масштабематематическая модель вида
/> (5)
где z2, q2, n2 — следующие функции от x2:
/> (6)
Уравнения связи между факторами в натуральном и кодированноммасштабах
/> (7)
Фактор X1 — скоростьрастяжения мм/с, X2 — температура нагреваоС испытываемого на растяжение образца. X1ср=27,5;.k1=22,5 мм/с; X2ср=350;k2=100оС.
Матрица плана эксперимента и результаты в натуральном масштабеприведена в таблице:
№
опыта
скорость,
мм/с
температура,
оС
sT
sB
d
 1 5 150 29,3 61,5 21,9 36,1 2 5 250 35,1 58,1 20,5 45,2 3 5 350 34,7 54,4 20,2 37 4 5 450 29,2 34,5 27,6 47,5 5 5 550 14,3 17 25,5 57,3 6 50 150 28,5 61,2 21,7 37,6 7 50 250 39,6 58,7 19,7 48,3 8 50 350 36 54,7 19,9 43,8 9 50 450 32 43,5 22,4 42,7 10 50 550 20,8 24,6 24,7 52,9 Методы решения с использованием Excel:
Решение задачи разбивается на несколько шагов с созданием соответствующихтаблиц:
1. Определение коэффициентов соответствия механических свойств условной шкалев формуле (3) по зависимостям (4) с учетом данных таблицы 1.
2. Построения матрицы плана эксперимента в кодовом масштабе по формулам (7)(6)
3. Определение значений условной шкалы для результатов каждого опыта по формулам(3), частных функций желательности по формуле (2), а затем обобщенной функции желательностипо формуле (1).
4. Определение значений коэффициентов b в модели (5) с использованием функции ЛИНЕЙН.
5. Построение графика изменения функции желательности D по уравнению (5) в зависимости от двух варьируемых параметров- скорости и температуры и определения области допустимых режимов обработки.Последовательность выполнения
78. Запустить EXCEL
79. На листе1 создать таблицы с исходными данными эксперимента и поясняющиминадписями по следующему образцу (при заполнении таблиц пользуйтесь способами копированияи автозаполнения): A B C D E F G H I 1 Определение оптимального интервала штамповки 2 Определение коэффициентов перехода к условной шкале 3 Характеристика
sT
 
sB
 
d
 
 4 Значение, s 25 21,5 44 29,6 23 26,2 45 60 5 Условная шкала, y 0,77 1,5 1,5 3 6 Коэффициенты перехода
a0
a1
a0
a1
a0
a1
a0
a1 7 8 Варьируемые параметры 9
ki
Xiср 10 Скорость растяжения, мм/с
X1 22,5 27,5 11
Температура нагрева, oC
X2 100 350
80. На листе 2 создать таблицы с исходными данными эксперимента и поясняющиминадписями по следующему образцу (при заполнении таблиц пользуйтесь способами копированияи автозаполнения).