Урок "Возможности табличного процессора Excel для решения задачи линейного программирования"

Урок

Тема урока: Возможности табличного процессора Excel для решения задачи линейного программирования.

Цель урока: ознакомление с назначением и возможностями электронной таблицы для решения задачи линейного программирования; обучение основным приёмам работы и организации простых табличных расчётов с помощью формул и функций в табличном процессоре.

Ход урока

Изучение новой темы.

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

Это средство информационных технологий, позволяющее решать целый комплекс задач для математического моделирования. Использование математических формул позволяет представить взаимосвязь между различными параметрами системы. Благодаря мгновенному пересчёту формул при изменении значений входящих в них операндов, таблица представляет собой удобный интерфейс для организации численного эксперимента: подбора параметров, прогноза поведения моделируемой системы, анализа зависимостей, планирования, графического представления данных.  Для решения задач на компьютере с помощью табличного процессора MS Excel предоставляет большие возможности. В нем имеется надстройка «Поиск решения», которая дает возможность решать задачи для нахождения оптимального значения наибольших и наименьших значений (наилучших) при заданных ограничениях. 

В окне «Поиск решения» требуется указать:

Ø В поле «Установить целевую ячейку» указать ячейку, содержащую оптимизируемое решение – целевую функцию;

Ø В поле «Равной» требуется установить переключатель, выбрав вариант оптимизации (максимальное, минимальное или значение равное какомулибо числу);

Ø В поле «Изменяя ячейки» задать диапазон подбираемых параметров (неизвестных);

Ø В поле «Ограничения» указать получившиеся ограничения.

После того, как всё заполнено, нажать кнопку «Выполнить». Откроется диалоговое окно «Результаты поиска решения», где требуется установить переключатель в положение «Сохранить найденное решение», после чего нажать кнопку «Ок».

Для того, чтобы найти оптимальное решение задачи с помощью надстройки «Поиск решения», сначала в MS Excel нужно построить таблицу, куда требуется внести плановые показатели, ресурсы и цель задачи.

В таблице перед нахождением оптимального решения при ограничениях и целевой функции следует ссылаться на конкретные ячейки.

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

Решение задачи оптимального планирования.

Разберем и решим задачу оптимального планирования, запишем математическую формулировку и решим на компьютере.

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

Постановка задачи оптимального планирования имеет:

•      Плановые показатели: x, y, и др.;

•      Ресурсы (практически всегда ограничены): R1, R2, и др.;  Цель, зависящую от показателей.

Значит, оптимальный план - определение значения плановых показателей с учетом ограниченности ресурсов при условии достижения цели

Для того, чтобы решить задачу оптимального планирования на компьютере требуется построить математическую модель, т.е., все показатели, ресурсы и цель, перевести на язык чисел и формул.

Рассмотрим пример, который продемонстрирует представление к решению задачи оптимального планирования. 

Пример: Школьный кондитерский цех готовит пирожки и пирожные. В силу ограниченности емкости склада за день можно приготовить в совокупности не более 700 штук изделий. Рабочий день в кондитерском цехе длится 8 часов. Поскольку производство пирожных более трудоемко, то если выпускать только их, за день можно произвести не более 250 штук, пирожков же можно произвести 1000 штук (если при этом не выпускать пирожных). Стоимость пирожных вдвое выше, чем стоимость пирожка. Требуется составить такой дневной план производства, чтобы обеспечить наибольшую выручку кондитерского цеха. 

Математической формулировкой задачи будут: x  – дневной план выпуска пирожков; y – дневной план выпуска пирожных.  Что будет являться ресурсами производства? 

Длительность рабочего дня – 8 часов;

Вместимость складского помещения – 700 мест.

Обозначим, t – время изготовление одного пирожка, а на пирожное времени уходит в 4 раза больше, следовательно, время изготовления одного пирожного – 4t. Значит, общее время на изготовление x пирожков и y пирожных равно:

tx+4ty=(x+4y)t. Время не может быть больше продолжительности рабочего дня: (x+4y)t<=8*60 или (x+4y)t<=480.

По какой формуле посчитать, сколько тратится времени на изготовление одного пирожка?

Т.к. за рабочий день может быть изготовлено 1000 пирожков, то на изготовления пирожка тратится 480/1000=0,48 мин. Полученное значение нужно подставить в неравенство. Что мы получим? (x+4y)*0,48<=480 или x+4y<=1000.

В задаче сказано, что вместимость складского помещения -700 мест. Как математически записать ограничение на общее число изделий? 

Дневной план выпуска пирожков и дневной план выпуска пирожных не должен превышать 700: x + y <=700. Количество пирожков и пирожных не могут быть отрицательными значениями.  x>=0; y>=0. В итоге, получилась система неравенств:  

x+4y<=1000;

x + y<=700;                                 (1)

x>=0; y>=0. Целью производства является получение максимальной выручки. Пусть r рублей – цена одного пирожка, а цена пирожного в 2 раза выше, следовательно, 2r рублей. Т.о. стоимость всей произведенной продукции за день: rx+2ry=r(x+2y) или рассмотрим, как функцию от x, y: F (x, y) = r (x+2y). Это будет целевой функцией. Т.к. значение r – константа, то максимальное значение F (x, y) будет достигнуто при максимальной величине выражения (x+2y), т.о. в качестве целевой функции можно приять следующую формулу: f (x, y) = x+2y. (2) Следовательно, требуется найти значения плановых показателей x и y, удовлетворяющих данной системе неравенств (1) и придающих максимальное значение целевой функции.

Математическая формулировка задачи построена. 

Следует самостоятельно решить задачу на компьютере с помощью табличного процессора MS Excel.

(Заполнить окно «Поиск решения». В результате должно получиться: 600 пирожков и 100 пирожных. А максимальная выручка 800 руб. 

Т. о. оптимальный план дневного производства кондитерского цеха: нужно выпускать 600 пирожков и 100 пирожных. При этом максимальная выручка от производства продукции будет равна 800 руб).  

Домашнее задание: § 20 изучить; разобрать вопросы к параграфу. Решить данную задачу с измененным условием: число пирожных должно быть не меньше числа пирожков.