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

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.  Предъявить преподавателю краткий конспект занятия.

Цели работы:

Ä  закрепление основных приемов создания и форматирования таблицы

Ä  освоение методов поиска решений с помощью встроенных средств 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. Уровни варьирования факторов

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

Коэффициенты регрессионной модели (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.  На листе Крутое восхождение создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11