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

НИМБ, Дмитриев И.С.

Нижегородский институт менеджмента и бизнеса

 

 

 

 

 

 

 

 

Реферат на тему:

«Использование  программы MS Excel в решении инженерных задач»

 

 

 

 

 

 

 

 

 

Выполнил студент                                                                                                           Дмитриев И.С.

 

Проверил                                                                                                                               …………………

 

 

 

 

 

 

 

г. Нижний Новгород,2012

 

Содержание

 

 

1. Введение……………………………………………………………….3                                                                                                                                
2. ОБРАБОТКА ЭКСПЕРИМЕНТА ПО ОПРЕДЕЛЕНИЮ

ПРИВЕДЕННОГО  МОДУЛЯ ОБЪЕМНОЙ УПРУГОСТИ ЖИДКОСТИ…….....5

3. ОБРАБОТКА ДАННЫХ ЭКСТРЕМАЛЬНЫХ ЭКСПЕРИМЕНТОВ

 НА ПРИМЕРЕ  ИССЛЕДОВАНИЯ ОПЕРАЦИИ ВЫТЯЖКИ

ЛИСТОВЫХ ОБРАЗЦОВ……………………………………………….…………13

4. Исследование операции обжима………………………………....21
5. Заключение…………………………………………………………..25
6. Список литературы………………………………………………….26

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение 

Программы  цикла Microsoft Office давно уже прочно вошли в различные области техники, анализа, быта и сферы услуг. Они используются в различных отчетах, подсчетах, вычислениях, базах данных, презентациях и во многом другом. Остановимся подробнее на одной программе из цикла Microsoft Office, а именно Microsoft Exсel.

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

         Microsoft Excel - это много функциональная программа, позволяющая производить различные операции с большими объемами информации, представленных в виде текстовой информации, чисел и др.

Табличный процессор обеспечивает работу с масштабными числовыми таблицами.

          Табличный процессор - программное средство для проектирования электронных таблиц, поэтому они дают возможность не только создавать разнообразные таблицы, но и автоматически обрабатывать табличные данные с помощью языка формул.

Сталкиваясь с  необходимостью обработки и анализа  большого объема данных, будь то описания, названия, цифры, где нужны несложные, циклически повторяемые операции предпочтительно задействать Excel. С помощью данной программы возможно не просто выполнять математические действия, но и структурировать данные, размещая их в отдельные строки, столбцы и ячейки таблиц Microsoft Excel. Информация в форме электронных таблиц вполне предназначена для анализа.

Обработка эксперимента по определению приведенного модуля объемной упругости жидкости

Постановка задачи:

Создать электронную таблицу для обработки данных эксперимента по определению приведенного модуля объемной упругости жидкости.

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

1. Аккумулятор винтового пресса-молота заряжается до начального давления 25 МПА.
2. Открывается вентиль, через который жидкость (минеральное масло) начинает вытекать в мерный объем (мензурку)
3. Фиксируется объем вытекшего масла через определенные промежутки текущего давления в аккумуляторе.
4. Опыт повторяется 3 раза

Модуль объемной упругости жидкости определяли путем обработки результатов эксперимента по следующему алгоритму:

1. Определяется объем вытекшего масла на каждом интервале замеров давления:
2. i=1,2,3 - число точек фиксации объема вытекшего масла p_i=20,15,10,5,4,3,2,1,0 [МПа] - давления, при которых фиксируется объем масла V_i - объем вытекшего масла, соответствующий i-му давлению в аккумуляторе DV_i=V_i+1-V_i, (1) при i=18, V₉=0
3. Вычисления по п1 выполняют для каждого из трех опытов
4. Вычисляют среднее значение изменения объема на каждом интервале:
5. Vср_i= (V_{1, i}+V_{2, i}+V_{3, i}) /3, (2) где индексы 1, i 2, i 3, i соответствуют номеру опыта и номеру интервала
6. Вычисляют среднее значение объема масла в точках фиксации давления по результатам трех опытов:
7. Vср_i= Vср_i+1+V_i ( 3) при i=8¼1, Vср₉=0
8. Строится график P=f (Vср) и визуально определяют точку n перехода от нелинейного начального к линейному конечному участку графика
9. Результаты соответствующие точкам i=1¼n аппроксимируют прямой p=b0+b1*Vср (4)
10. Определяют приведенный модуль объемной упругости жидкости по формуле:
11. k=V₀* dp/dVср (5) где V₀=1,1577*10⁴ см³ - объем аккумулятора dp/dVср=tana= b1, a - угол наклона прямой, аппроксимирующей линейный участок зависимости P=f (Vср)

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

1. Запустить EXCEL
2. Создать таблицу с исходными данными эксперимента и поясняющими надписями в соответствие с прилагаемой ниже таблицей. Обратить внимание, что текст, целиком не помещающийся в ячейку вводится в ячейку, номер которой соответствует началу текста. Например, весь текст "Экспериментальное определение модуля объемной упругости" вводится в ячейку А1

При заполнении, там где это целесообразно, пользуйтесь методами автозаполнения и копирования, изученными на предыдущем занятии

Символы DV в тексте пояснений в таблице заменяют символы в V формулах

 

 

 

 

 

 

 

 

 

 

 

 

 

	A	B	C	D	E	F	G	H	I	J	K
1	Экспериментальное определение модуля объемной упругости
2	Величина	№экс	Экспериментальные данные
3	p, МПа		20	15	10	5	4	3	2	1	0
4	DV, см3	1	116	155	190	228	233	245	250	268	280
5	DV, см3	2	130	164	202	235	243	251	260	272	290
6	DV, см3	3	110	146	179	215	224	228	240	248	270
7	DV, см3	1									0
8	DV, см3	2									0
9	DV, см3	3									0
10	DVср, см3										0
11	Данные для построение графика результатов эксперимента
12		Vср, см3									0
13		p, МПа	20	15	10	5	4	3	2	1	0
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 формулу для определения среднего значения изменения объема Vср_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 занесите формулы для определения максимальной и минимальной погрешности вычисления объема, которые будут иметь вид:таблица
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 в размерности, нажмите клавиатурную комбинацию 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 на V. Для этого выделите D в соответствующих ячейках, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта выберите шрифт Symbol в окне Шрифт

18. Посмотрите, поместится ли созданная Вами таблица на один лист при печати. Для этого нажмите кнопку (Предварительный просмотр) на стандартной панели инструментов. Измените ширину столбцов A-K таким образом, чтобы все столбцы поместились на одной странице, и при этом все данные и надписи в столбцах были бы видны. Для плавного изменения ширины столбца достаточно щелкнуть по букве столбца мышью (выделить весь столбец), навести указатель курсора на границу столбца (указатель примет вид двунаправленной стрелки) и отбуксировать границу в нужное место.

19. Проверьте себя: приблизительный внешний вид таблиц и диаграммы приведен в приложении
20. Завершить работу, сохранив ее в файле work2. xls.

 

 

Обработка данных экстремальных экспериментов на примере исследования операции вытяжки листовых образцов

Постановка задачи ¹:

Рассматривается задача отыскания максимального коэффициента вытяжки цилиндрического стаканчика из листовой заготовки с использованием метода крутого восхождения Бокса-Уилсона.

Напомним, что коэффициентом вытяжки k=D/d, где D - диаметр исходной заготовки, d - диаметр вытянутого из этой заготовки стаканчика. Предельная величина коэффициента вытяжки за один переход ограничена величиной максимальных напряжения во фланце заготовки. Разрушение заготовки произойдет тогда, когда напряжение достигнет предела прочности материала G_В. При этом значение коэффициента вытяжки является максимальным. На величину предельного коэффициента вытяжки, помимо других факторов, влияет радиус скругления кромки матрицы (r_м) и скорость деформирования (V).

Идея экстремальных экспериментов заключается в линейной аппроксимации гиперповерхности отклика, оценке составляющих градиента и проведении серии "мысленных" опытов (т.е. без выполнения реального эксперимента) в направлении оптимума.

Метод планирования эксперимента Бокса-Уилсона включает в себя построение линейной модели исследуемого объекта в виде y=b₀+b₁x₁+b₂x₂+G+b_nx_n, где n - количество факторов. В этом случае оценками составляющих вектора градиента являются коэффициенты полинома. Для движения по градиенту необходимо менять факторы пропорционально величинам коэффициентов. Такая процедура называется крутым восхождением. При движении по градиенту факторы изменяют с определенным шагом. Шаги изменения рассчитывают в натуральном масштабе.

Таким образом процедура решения задачи сводится к следующей последовательности шагов:

• проведение натурного эксперимента, для получения линейной модели y=b₀+b₁x₁+b₂x₂+b₃x₃ (1), где y= G_Bmax; x₁, x₂, x₃ - факторы k, r_м, V в кодовом масштабе (результаты натурного эксперимента заданы в качестве исходных данных);
• определению коэффициентов b_i линейной модели;
• определению составляющих градиента в натуральном масштабе;
• осуществлении крутого восхождения - т.е. подбора такого шага в направлении градиента из центра плана, при котором в формуле (1) y = G_В. Предел прочности материала, используемого в опытах 310 МПа

Методы решения с использованием Excel:

Для определения коэффициентов линейной модели (1) достаточно провести 4 опыта. Интервалы варьирования факторов X_i относительно центра плана X_i0 в проведенном натурном эксперименте приведены в таблице 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 (Gs max, МПа)
1	-1	-1	-1	189
2	1	-1	1	236
3	-1	1	1	167
4	1	1	-1	223

 

Коэффициенты регрессионной модели (1) определяют с помощью уже известной вам функции ЛИНЕЙН. Если вы забыли синтаксис функции ЛИНЕЙН, воспользуйтесь встроенной справкой Excel.

Исходная точка для крутого восхождения - центр плана с координатами в натуральном масштабе:

 

X_1о=1.3, X_2о=3, X_3о=0.5 (3)

 

Составляющие градиента вычисляются по формуле

 

g_i=b_iGX_{i (}4)

 

Шаг крутого восхождения в натуральном масштабе по каждой из координат

 

X_i=X_i0+g_i*S (5)

 

Подбор такой кратности шагов S, одинаковой для всех координат, при котором y = G_В осуществляют с помощью команды меню Excel "Сервис-Подбор параметра".

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

1. Запустить EXCEL
2. Переименуйте Лист1 книги в "Крутое восхождение". Остальные листы книги удалите. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Удалить.
3. На листе Крутое восхождение создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):

 

	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	Gs max, МПа
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) /SXi
23	S	X1	X2	X3	x1	x2	x3	xmax
24

 

4. В диапазон A14: D14 введите формулу для определения коэффициентов регрессии для модели напряжений G _{s max}, используя функцию ЛИНЕЙН и мастер функций. Последовательность действий приведена ниже:

• Выделить A14: D14

• Меню Вставка-Функция (или кнопка Вставка функции)
• Категория - Статистические, Функция - ЛИНЕЙН, Кнопка OK
• Окно Изв_знач_y - диапазон известных значений G_{s max}
• Окно Изв_знач_x - диапазон значений x_i в опытах
• Окно Константа - 1
• Окно Стат - 0
• Нажать клавиатурную комбинацию Ctrl-Shift-Enter
• Формула массива вставится в выделенный диапазон и в нем появятся значения коэффициентов

5. Введите в диапазон A17: C17 формулы (4) для определения составляющих градиента. Обратите внимание, что порядок следования составляющих градиента g_i в диапазоне A17: C17 и порядок следования коэффициентов b_i в диапазоне A14: С14 не совпадают.

6. Обратите внимание, что полученные значения составляющих градиента для факторов 2 и 3 имеют отрицательные значения. Это означает, что шаги в этом направлении приведут к уменьшению G_{s max}. Поэтому мы должны двигаться в направлении антиградиента по этим факторам (напомним, что направление градиента - это направление увеличения функции). Таким образом в ячейки A20: C20 следует внести следующие формулы:

• A20 à =A17

• B20 à = - C17
• C20 à = - C17

7. Занесите в ячейку A24 значение 1, являющееся начальным значением кратности шагов S

8. Занесите в диапазон B24: D24 формулы (5) для определения значений варьируемых факторов в натуральном масштабе. Составляющие градиента расположены в ячейках A20: C20. Значения центра плана X_i0 - в ячейках B4: D4. Для использования автозаполнения в формулах следует вводить абсолютный адрес ячейки $A$24
9. В ячейки E24: G24 внесите формулы (2) для перехода от натурального масштаба к кодированному.
10. В ячейку H24 введите формулу для определения G_{s max} по значениям факторов x_i в ячейках E24: G24 в соответствии с моделью (1). Коэффициенты модели b_i были определены вами ранее в ячейках A14: D14. При вводе формулы обратите внимание на обратный порядок значений коэффициентов b_i в диапазоне A14: D14. Обратите внимание, что величина напряжения получилась значительно большей, чем максимально возможное значение 310 МПа.
11. Меняя значение в ячейке A24 попытайтесь вручную подобрать такую кратность шагов S, при которой значение напряжения в ячейке H24 было бы равно 310 МПа.
12. Верните в ячейку A24 значение 1 и подберите точное значение с помощью встроенных в Excel средств автоматического подбора значений. Для этого: