Выполнение задания №1.
· Создать новую книгу и дать ей имя.
· На листе «Лист1» создать таблицу для расчета калорийности (рис.6.1).
· В ячейку B3 ввести формулу: = B7*50.
· В ячейку D3 вводим формулу =B3*C3/100.
· Затем эту формулу копируем соответственно в ячейки B4 и B5.
· Значения калорийности каждого продукта суммируем и получаем калорийность всего блюда. Для данных пропорций она равна 478,2 ккал.
· Чтобы получить калорийность равную 350 ккал выберем команду Сервис®Подбор параметра. В поле «Установить в ячейке» должна появится ссылка на целевую ячейку D6, в поле «Значение» ввести искомое значение 350, в поле «Изменяя значение ячейки» ввести ссылку на ячейку B7, в которой указано количество яиц (Рис.6.2).
Рис.6.1
Рис.6.2
· Нажмите кнопку Оk. В результате в ячейке В7 получим 2,29, что составляет примерно 2 яйца.
Выполнение задания №2
· На листе «Лист2» создаем таблицу исходных данных (рис.6.3).
Рис.6.3
· В ячейку В3 введите формулу = sin(A3).
· Выберите команду Сервис®Подбор параметра.
· В поле «Установить в ячейке» введите ссылку на ячейку B3.
· В поле «Значение» введите то значение 0.5.
· В поле «Изменяя значение ячейки» введите ссылку на ячейку А3 (рис.6.4)
· Нажмите кнопку Оk.
Рис.6.4
После завершения процесса подбора, в ячейке А3 увидите результат – значение x в радианах (Рис.6.5).
Рис.6.5
Переведите значение x в градусы с помощью функции ГРАДУСЫ Должно получиться x=29,950608 (x»30 градусов).
Примеры лабораторного задания
4.1. Используя данные задания №1, получить калорийность 400 ккал, изменяя количество молока.
4.2. Используя данные задания №1, получить калорийность 500 ккал, изменяя количество масла.
4.3. Используя данные задания №1, получить калорийность 400 ккал, изменяя количество яиц.
4.4. Решить уравнения cos(x)=0,5; tg(x)=1; sin(x)=1.
Контрольные вопросы
5.1. Сколько одновременно ячеек может изменять Подбор параметра?
5.2. Для решения каких задач применяется Подбор параметра?
5.3. Что содержится в поле Значение?
5.4. Что содержится в поле Установить в ячейке?
5.5. Можно ли найти с помощью Подбор параметра два корня уравнения?
Лабораторная работа №7
Инструментальное средство «Поиск решения»
Цель работы
Освоение встроенного средства Excel Поиск решения, позволяющего находить оптимальное решение сложных задач, в которых требуется изменять более одного параметра.
Теоретические сведения
Задачи, которые включают взаимозависимые переменные, практически невозможно решить вручную. Средство Поиск решения облегчает решения сложных задач со многими неизвестными и ограничениями.
Опишем элементы диалогового окна «Поиск решения».
Установить целевую ячейку. Служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу.
Равно. Служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле.
Изменяя ячейки. Служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку.
Предположить. Используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки.
Ограничения. Служит для отображения списка граничных условий поставленной задачи.
Добавить. Служит для отображения диалогового окна Добавить ограничение.
Изменить. Служит для отображения диалогового окна Изменить ограничение.
Удалить. Служит для снятия указанного ограничения.
Выполнить. Служит для запуска поиска решения поставленной задачи.
Закрыть. Служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.
Параметры. Служит для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения.
Восстановить. Служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
Задания к работе
№1. С помощью средства Поиск решения решить следующую задачу. Фирма, выпускает три вида продукции А,В,С. Себестоимость выпуска единицы каждого вида продукции, прибыль на единицу от их реализации и максимальный уровень спроса на продукцию известны; они представлены в таблице 7.1. Фирма располагает денежными ресурсами в 20000сомов. Определить, в каких объемах следует выпускать продукцию каждого вида, чтобы прибыль от ее реализации была максимальной.
Таблица 7.1
| Вид продукции | Себестоимость (сом) | Прибыль на ед. выпуска (сом) | Емкость рынка (шт.) |
| А | |||
| В | |||
| С |
№2. Используя Поиск решения, определить минимум функции
f(x) = 3x2+4x+1.
