Использование возможностей Microsoft Excel в решении производственных задач (стр. 7 из 12)
- распространите формулу в ячейке 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 - количество факторов. В этом случае оценками составляющих вектора градиента являются коэффициенты полинома. Для движения по градиенту необходимо менять факторы пропорционально величинам коэффициентов. Такая процедура называется крутым восхождением. При движении по градиенту факторы изменяют с определенным шагом. Шаги изменения рассчитывают в натуральном масштабе.