Решение оптимизационных задач в среде электронных таблиц. (Учитель Дзиова М.П.)

Практическая работа

по теме: «Microsoft Excel».

Решение оптимизационных задач в среде электронных таблиц

Цель работы:

Нахождение оптимальных путей управления развития системы.

Задача №1:

Некой компьютерной фирме необходимо оптимально спланировать доставку заказчику 14 компьютеров (системный блок плюс монитор), при условии, что для перевозки может использоваться единственный автомобиль. Известно, что существует три варианта погрузки коробок в автомобиль:

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

Решение:

Пусть Х1 – количество рейсов автомобиля, загруженного по варианту 1;

Х2 – количество рейсов автомобиля, загруженного по варианту 2;

Х3 – количество рейсов автомобиля, загруженного по варианту 3;

Тогда целевая функция (количество рейсов автомобиля) примет вид:

F=X1+X2+X3

Как видим, функция является линейной. Поэтому задача имеет смысл, только при наличии ограничений на параметры Х1,Х2,Х3. Эти ограничения у нас как раз имеются – это фиксированное количество коробок с мониторами и системными блоками, которые необходимо привезти (по условию задачи – 14). Т.е. должны выполняться два неравенства:

3*Х1+2*Х2+1*Х3=14

1*Х1+3*Х2+4*Х3=14

Кроме этого, ясно, что количество рейсов не может быть отрицательным числом, т.е. должны выполняться неравенства: Х1=0; Х2=0; Х3=0.

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

Загрузите программу Microsoft Excel. Заполните ячейки таблицы исходными данными, например, так:

Ячейки таблицы B2,C2,D2 – выделены для хранения значение параметров Х1,Х2,Х3. В ячейку B4 вводится формула для вычисления целевой функции; в ячейку B7 – формула для вычисления количества коробок с мониторами, а в ячейку B8 – формула для вычисления количества коробок с системными блоками.

Поскольку ячейки B2,C2,D2 пока пусты, ваша таблица будет выглядеть следующим образом:

Теперь воспользуемся надстройкой электронных таблиц, которая называется Поиском решения.

Для активизации надстройки в Главном меню программы найдите меню Сервис, а в нём Надстройки и поставьте флажок в окошке рядом со словами Поиск решений, нажмите ОК.

Далее в меню Сервис необходимо найти Поиск решений и в появившемся диалоговом окне установить:

• адрес целевой ячейки (в нашем случае это B4);

• вариант оптимизации значения целевой ячейки (здесь надо выбрать пункт Минимальное значение);

• адреса ячеек, значения которых изменяются в процессе поиска решения, в них хранятся значения параметров (в нашем случае это блок ячеек B2:D2 – выделяется с помощью мыши);

• ограничения (а нашем случае должны выполняться следующие ограничения B2=0; C2=0; D2=0, B7=14, B8=14); для установки каждого из перечисленных ограничений в окне Поиск решения воспользуйтесь кнопкой Добавить.

Щелкните по кнопке Выполнить. При этом ваша таблица должна принять следующий вид:

Таким образом, для перевозки 14 коробок с мониторами и 14 коробок с системными блоками автомобилю потребуется совершить 6 рейсов, при этом 2 из них должны быть загружены по первому варианту и 4 рейса – по второму варианту (выполнять рейсы с загрузкой по третьему варианту не требуется).

Задача решена.

Задача №2: (самостоятельно) В-1

а) Для изготовления батона хлеба надо 0,5 кг муки и 0,02 кг сахара, а для изготовления сдобной булки надо 0,2 кг муки и 0,05 кг сахара. Батон стоит 3 рубля. Булка стоит 4 рубля. У нас в наличии 4 кг муки и 0,2 кг сахара. Сколько надо испечь батонов и сколько надо испечь булок, чтобы заработать больше денег? (не забудьте предусмотреть целочисленный формат ячеек, предусмотренных для ответов на вопрос задачи).

б) …Если в наличии 6 кг муки и 0,5 кг сахара?

Практическая работа

по теме: «Microsoft Excel».

Решение оптимизационных задач в среде электронных таблиц

Цель работы:

Нахождение оптимальных путей управления развития системы.

Задача №1:

Некой компьютерной фирме необходимо оптимально спланировать доставку заказчику 14 компьютеров (системный блок плюс монитор), при условии, что для перевозки может использоваться единственный автомобиль. Известно, что существует три варианта погрузки коробок в автомобиль:

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

Решение:

Пусть Х1 – количество рейсов автомобиля, загруженного по варианту 1;

Х2 – количество рейсов автомобиля, загруженного по варианту 2;

Х3 – количество рейсов автомобиля, загруженного по варианту 3;

Тогда целевая функция (количество рейсов автомобиля) примет вид:

F=X1+X2+X3

Как видим, функция является линейной. Поэтому задача имеет смысл, только при наличии ограничений на параметры Х1,Х2,Х3. Эти ограничения у нас как раз имеются – это фиксированное количество коробок с мониторами и системными блоками, которые необходимо привезти (по условию задачи – 14). Т.е. должны выполняться два неравенства:

3*Х1+2*Х2+1*Х3=14

1*Х1+3*Х2+4*Х3=14

Кроме этого, ясно, что количество рейсов не может быть отрицательным числом, т.е. должны выполняться неравенства: Х1=0; Х2=0; Х3=0.

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

Загрузите программу Microsoft Excel. Заполните ячейки таблицы исходными данными, например, так:

Ячейки таблицы B2,C2,D2 – выделены для хранения значение параметров Х1,Х2,Х3. В ячейку B4 вводится формула для вычисления целевой функции; в ячейку B7 – формула для вычисления количества коробок с мониторами, а в ячейку B8 – формула для вычисления количества коробок с системными блоками.

Поскольку ячейки B2,C2,D2 пока пусты, ваша таблица будет выглядеть следующим образом:

Теперь воспользуемся надстройкой электронных таблиц, которая называется Поиском решения.

Для активизации надстройки в Главном меню программы найдите меню Сервис, а в нём Надстройки и поставьте флажок в окошке рядом со словами Поиск решений, нажмите ОК.

Далее в меню Сервис необходимо найти Поиск решений и в появившемся диалоговом окне установить:

• адрес целевой ячейки (в нашем случае это B4);

• вариант оптимизации значения целевой ячейки (здесь надо выбрать пункт Минимальное значение);

• адреса ячеек, значения которых изменяются в процессе поиска решения, в них хранятся значения параметров (в нашем случае это блок ячеек B2:D2 – выделяется с помощью мыши);

• ограничения (а нашем случае должны выполняться следующие ограничения B2=0; C2=0; D2=0, B7=14, B8=14); для установки каждого из перечисленных ограничений в окне Поиск решения воспользуйтесь кнопкой Добавить.

Щелкните по кнопке Выполнить. При этом ваша таблица должна принять следующий вид:

Таким образом, для перевозки 14 коробок с мониторами и 14 коробок с системными блоками автомобилю потребуется совершить 6 рейсов, при этом 2 из них должны быть загружены по первому варианту и 4 рейса – по второму варианту (выполнять рейсы с загрузкой по третьему варианту не требуется).

Задача решена.

Задача №2: (самостоятельно) В-2

а) Школьный кондитерский цех готовит пирожки и пирожные. в силу ограниченности ёмкости склада за день можно приготовить в совокупности не более 700 изделий. рабочий день в кондитерском цехе длиться 8 часов. На изготовление пирожного необходимо времени в четыре раза больше, чем на изготовление одного пирожка (на один пирожок затрачивается 0,48 мин). стоимость пирожного вдвое выше, чем пирожка. требуется составить дневной план производства, обеспечивающий кондитерскому цеху наибольшую выручку.

б) Представьте, что в школе учатся неисправимые сладкоежки. И, кроме всех прочих ограничений, перед кондитерским цехом ставится обязательное условие: число пирожных должно быть не меньше числа пирожков. Как при этом изменится план, что вы можете сказать о прибыли?

Ответы к заданию №2

Оптимизационная задача №2 В-1

а. муки 4 кг, сахара 0,2 кг

б. муки 6 кг, сахара 0,5 кг

Оптимизационная задача №2 В-2

а.

б.