Термины и определения. Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой

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

Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму (максимуму или минимуму).

Добавление надстройки Поиск решения на вкладку Данные.

1. Щелкнуть по кнопке Microsoft Office, выбрать Параметры Excel.

2. Выбрать категорию Надстройки, в поле Управление выбрать Надстройки Excel и нажать Перейти.

3. В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения и нажать ОК.

Задача 4.

Школа планирует открытие хозрасчетного кружка «Волшебная глина», обучаться в котором будутобучаться 200 0 учеников в год. Для этого необходимо закупить оборудование для мастерской, которое предлагают различные фирмы. Известны стоимость, производительность и надежность оборудования каждой фирмы (табл.1). Сколько единиц оборудования надо закупить у разных фирм, чтобы с минимальными затратами обеспечить обучение учащихся с надежностью оборудования 75%?

Создать таблицу по образцу, в ячейках B3: Е5 – исходные данные; в ячейках B6: Е6 – выходные данные – количество единиц оборудования;

табл.1

в ячейках G3: G4 – ограничения;

в ячейке F5 – целевая функция;

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

2. В ячейках B4: F4 установить процентный формат.

3. В ячейку F3 записать формулу: =СУММПРОИЗВ(B3: E3; B6: E6)

4. В ячейку F4 записать формулу: =СУММПРОИЗВ(B6: E6; B4: E4; B3: E3)/СУММПРОИЗВ(B6: E6; B3: E3)

5. В ячейку F5 записать формулу: =СУММПРОИЗВ(B6: E6; B5: E5)

6. Вызвать инструмент Поиск решения (вкладка Данные, в разделе Анализ – Поиск решения)и заполнить поля формы.

· Установить целевую ячейку: $F$5

· Равной: минимальному значению

· Изменяя ячейки: $В$6: $Е$6

· Ограничения: $В$6: $Е$6> =0, $В$6: $Е$6=целое, $F$3> =$G$3, $F$4> =$G$4

Результат Поиска решения:

Самостоятельно:

Задача 5. Три детских лагеря «Орленок», «Созвездие», «Туристёнок» (Крым) ежедневно принимает соответственно 30, 20 и 16 человек. Экскурсионная программа каждой группы состоит из посещения аквапарка, посещения Ласточкиного гнезда и путешествия на гору Ай - Петри. Стоимость экскурсий на человека для разных лагерей приведена в таблице 1. При этом существуют ограничения на количество человек в экскурсии: Аквапарк – 25 человек, Ласточкино гнездо – 20 человек, Ай-Петри – 30 человек. От каждого лагеря на каждую экскурсию должно быть послано не менее 5 человек. Определить оптимальное количество учеников для участия в каждой экскурсии при заданных ограничениях, чтобы суммарные расходы родителей были минимальны.

Задача 6. По результатам экзаменов и заданному плану приёма абитуриентов вычислить значение проходного балла, необходимого для обеспечения требуемого количества студентов.

Задача 7. Необходимо произвести закупку школьных принадлежностей на определённую сумму. Известна цена каждого товара. Даны ограничения на максимальное и минимальное количество закупаемого товара. Определить оптимальное количество закупаемых товаров.

Задача 8. При получении школой нового компьютерного класса необходимо оптимально спланировать использование единственного легкового автомобиля для перевозки 15 компьютеров. Каждый компьютер упакован в 2 коробки (монитор и системный блок). Существует три варианта погрузки коробок в автомобиль:

Необходимо выбрать оптимальное сочетание вариантов погрузки для того, чтобы перевезти 15 коробок с мониторами и 15 коробок с системными блоками за минимальное количество рейсов автомобиля.