Решение уравнений с помощью команды подбора параметров и методом простой итерации, решение задачи оптимизации с помощью программы Excel

Добавить 3 листа с помощью команды ВставкаàЛист и назвать их

٭  Лист 4 – Автоматический расчёт.

٭  Лист 5 – График.

٭  Лист 6 – Оптимизация.

3.  Задача 1. Найти решение уравнения x³-3x²+x=-1.

Перейдите на лист Подбор параметра.  В ячейку А1 внесите заголовок "Решение уравнения x³-3x²+x=-1". В ячейку В1 х, в ячейку В2 у, в ячейку А3 значение 0. Занесите в ячейку В3 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А3. Выберите команду СервисàПодбор параметра. В поле Установить в ячейке укажите В3, в поле Значение задайте –1, в поле Изменяя значение в ячейки укажите А3.

4.  Задача 2. Решение уравнения x = cos(x) методом простой итерации.

Перейдите на лист  Ручной расчет. Выберите команду Сервис®Параметры. Откройте вкладку Вычисления. Включите режим Вручную. Отключите флажок Пересчет перед сохранением. Сделайте значение поля Предельное  число итераций равным 1.Относительную погрешность 0,001.Щелкните на кнопке ОК.

Введите в ячейку А1 строку Решение уравнения x = cos(x) методом простой итерации.

Теперь необходимо создать таблицу с начальным значением и флагом инициализации. Флаг инициализации переводит лист в заданное начальное состояние.

В ячейку А3 введите текст "Начальное значение", в ячейку А4 текст "Начальный флаг", в ячейку В3 значение 0, в ячейку В4 слово Истина.

Присвойте ячейкам В3 и В4 имя соответственно НАЧ_ЗН и НАЧ  с помощью команды ВставкаàИмяàПрисвоить.

В ячейке В6 будет выполняться проверка, равна ли ИСТИНА значению ячейки НАЧ. Если это так, х будет установлено равным начальному значению,  в противном случае равным ячейке В7, т.е. косинусу х. В ячейке В7 вычисляется косинус ячейки В6, и тем самым организуется циклическая ссылка.

В ячейку А6 введите х, в А7  cos(x).  В ячейку В6 формулу 

= ЕСЛИ (НАЧ; НАЧ_ЗН; В7). В ячейку В7 формулу = COS(B6).

Далее следует вычислить погрешность – разницу между  х и cos(x),  которая будет служить критерием сходимости процесса последовательных приближений.

В ячейку А9 введите слово Погрешность.

В ячейку В9 введите формулу = В7-В6.

С помощью команды Формат®Ячейки (вкладка Число) преобразуйте ячейку В9 в экспоненциальный формат с двумя цифрами после запятой.

Теперь организуем вторую циклическую ссылку – для подсчета количества итераций.В ячейкуА11 введите текст Количество итераций.

В ячейку В11 введите формулу  = ЕСЛИ (НАЧ; 0; В12+1) .

В ячейку В12 введите = В11. Для выполнения расчета установите табличный курсор в ячейку В4 и нажмите клавишуF9 (Вычислить)  для запуска решения  задачи.

Измените значение начального флага на ЛОЖЬ и снова нажмите клавишу F9. При каждом нажатии клавиши F9 выполняется одна итерация и вычисляется следующее приближенное значение х.

Нажимайте клавишу F9 до тех пор, пока значение х не достигнет необходимой точности.

Точность полученного приближенного значения х проверяется путем его сравнения со значением f(x); разность между ними отображается в ячейке В9. К этому моменту рабочий лист должен выглядеть так, как на рис.1.  Значение х, являющееся корнем уравнения, приведено в ячейках В6 и В7.

Рис.1. Применение метода последовательных приближений

для решения уравнения Cos(x) = x

5. Перейдите на лист  Автоматический расчет.

Создайте таблицу, аналогичную таблицы листа Ручной расчёт, только в ячейку В4 введите значение ЛОЖЬ.

Выберите команду Сервис®Параметры вкладку Вычисления.

Установите значение поля Предельное число итераций равным 100, а значение поля Относительная погрешность 0,0000001.

Включите режим  Автоматически и щелкните на кнопке ОК.

6.  Перейдите на лист График и постройте график функции y=x и y=cos(x) на отрезке  [0;1] с шагом  0,1. Проверить по графику, соответствует ли найденное значение корня с помощью метода простой итерации, точке пересечения графиков функций.

7.  Задача3. Завод производит электронные приборы трёх видов (прибор А, прибор В, прибор С), используя при сборке микросхемы трёх видов (тип1, тип2, тип3).

Расход микросхем задаётся следующей таблицей:

Стоимость изготовленных приборов одинакова. Ежедневно на склад завода поступает 500 микросхем типа 1 и по 400 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

Перейдите на лист Оптимизация. Создайте следующую таблицу:

В ячейку С4 введите формулу =$D$7*D4+$E$7*E4+$F$7*F4, затем маркером автозаполнения протянуть до С6. В ячейку G7 введите формулу  =СУММ(D7:F7). Затем выберите СервисàПоиск решения. В поле Установить целевую указать ячейку G7, установить переключатель