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

-Отформатируйте осьYтак, чтобы график занимал всю область построения диаграммы. Для этого после выделения оси и перехода в окно редактирования одним из указанных выше способов (например с помощью клавиатурной комбинации Ctrl-1) выберите вкладку Шкала и в графе Максимальное значение поставьте максимальное значение давления в исходной таблице - 20. Посмотрите другие вкладки в окне форматирования оси. Возможно Вы захотите изменить и другие параметры и внешний вид элементов оси.

-Измените название оси категорий так, чтобы размерность объема была поставлена правильно: не см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+m₁d₁ + m₂d₂ +. (2)

где зависимое значение y является функцией независимых значений d_i. Значения m_i - это коэффициенты, соответствующие каждой независимой переменной d_i, а b - это постоянная.

Сравнение формул (1) и (4) показывает, что если в качестве переменных d_i использовать значения переменных x_i, а также различные функции от x_i, то коэффициент b в формуле (2) имеет смысл коэффициента b₀ в формуле (1), а коэффициенты m_i - соответственно коэффициентов b_i, b_ij, b_ii

Функция ЛИНЕЙН возвращает массив значений коэффициентов в обратном порядке {m_n; m_n‑1;.; m₁; b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис функции: ЛИНЕЙН (Y; D; K; C)

Здесь: Y - множество (обычно столбец) известных значений y

D - множество (обычно диапазон) известных значений d. Если множество Y - столбец, то диапазон Dдолжен иметь столько же строк, сколько столбец значений Y. Количество столбцов диапазона Dопределяет количество nнеизвестных коэффициентов m_iрегрессионной модели

K - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если K=1 (имеет значение ИСТИНА), то b вычисляется обычным образом. Если K=0 (имеет значение ЛОЖЬ), то b полагается равным 0.