Информатика и компьютерная техника

Тема 19. Электронные таблицы MS Excel Средство Поиск развязку

 В практике часто встречается задача нахождения таких значений параметров [1], при которых некоторая другая величина приобретает наибольшего или наименьшего значения.Такими задачами, например, являются:

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

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

- Задача оптимального распределения товаров по магазинам с минимальными транспортными затратами

Этот перечень можно продолжить. Задачи подобного типа и методы их решению будут подробно рассматриваться в курсе Математические методы в экономике. [2] Здесь мы рассмотрим решение только одной задачи с помощью средства Поиск развязку в MS Excel, а именно - задачи планирования выпуска продукции при ограниченных ресурсах (задачи линейного программирования).

Задача линейного программирования

 Пусть предприятие выпускает  n  видов продукции: W 1 , W 2 , ..., W N . При ее изготовлении используется  m  видов сырья: Х 1 , Х 2 , ..., Х m . На единицу продукции вида W J расходуется сырья вида Х i  в количестве  IJ . Запасы сырья видов Х 1 , Х 2 , ..., Х  на предприятии соответственно составляют  1 , 2 , ..., M . Прибыль от реализации одного изделия [3] вида W 1  составляет   грн., изделия вида W 2 - составляет  2  грн., ..., изделия вида W N  - составляет  N  грн.

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

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

Обозначим через  1 ,  2 , ...,   количество единиц продукции вида W 1 , W 2 , ..., W N  соответственно. Таким образом, набор представляет собой определенный план выпуска продукции. Прибыль, которую предприятие получит от реализации такого плана, равна   =  1 + 2 + ... + N . Понятно, что чем больше единиц продукции выпустит предприятие, тем больше прибыль. Но количество единиц продукции, которое может выпустить предприятие, ограничивается имеющимися сырьевыми запасами.

Количество сырья вида X и  ( i = 1, ..., m ), которая будет потрачена на реализацию такого плана, обозначим через  и . Тогда можно записать, что  и 1 1 + и 2 2 + ... + In X N=  и  ( i = 1, ..., m ). Количество затраченного  и го материала не должна превышать его имеющийся запас, то есть  и  £  и .

 Таким образом, получаем следующую математическую модель:

Найти  1 ,  2 , ...,  N  такие, что

 равна   =  1 + 2 + ... + N  принимает максимальное значение, и при этом, удовлетворяется система неравенств

В общем случае эту систему неровностей нужно дополнить условием неотрицательности  1 ,  2 , ...,  N :. Если продукция является поштучной, то на нужно еще наложить условия целочисленности. Функция называется целевой функцией.

 Такого вида математическая модель называется задачей линейного программирования. В задачи линейного программирования ограничения представляют собой систему линейных уравнений или неравенств, а целевая функция является линейной функцией. [4]

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

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

Пусть предприятие выпускает три вида изделий А, Б, В. считать, что изделия являются штучным товаром, то есть единица количества является штука, а не  м ,  кг ,  л  и тому подобное. Прибыль от реализации одного изделия составляет соответственно для изделия А, Б и В - 126 грн., 123 грн., 127 грн. На изготовление каждого изделия расходуется сырье 5-ти видов: М1, М2, М3, М4, М5. При этом:

сырья М1 на одно изделие А идет 1,4 кг, на изделие Б - 1,6 кг, на изделие В - 1,2 кг;

сырья М2 на одно изделие А идет 1,5 кг, на изделие Б - 2,2 кг, на изделие В - 1,6 кг;

сырья М3 на одно изделие А идет 3,2 кг, на изделие Б - 3,1 кг, на изделие В - 3,3 кг;

сырья М4 на одно изделие А идет 1,9 кг, на изделие Б - 2,1 кг, на изделие В - 1,8 кг;

сырья М5 на одно изделие А идет 2,4 кг, на изделие Б - 3,3 кг, на изделие В - 3,5 кг.

Запас сырья М1 составляет 500 кг, сырья М2 - 480 кг, сырья М3 - 640 кг, сырья М4 - 490 кг, сырья М5 - 600 кг.

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

 Составим математическую модель.

 Обозначим через  1  количество изделий вида А, через  2  - количество изделий вида Б, а через  3  - количество изделий вида В. Тогда целевая функция будет иметь вид   = 126 1 +123 2 +127 3 , а система ограничений запишется как

 1,4 1 + 1,6 2 + 1,2 3 £ 500

1,5 1 + 2,2 2 + 1,6 3 £ 480

3,2 1 +3,1 2 +3,3 3 £ 640

1,9 1 + 2,1 2 + 1,8 3 £ 490

2,4 1 +3,3 2 + 3,5 3 £ 600

1 ³0,  2 ³0,  3 ³0,

где  х 1 ,  х 2 ,  х 3  - цели.

 Нужно найти  х 1 ,  х 2 ,  х 3  такие, что целевая функция достигает максимума.

 Находим решение этой математической модели с помощью средства Поиск решения.

 Занесем в ячейки A1, B1, C1 произвольные положительные числа или нули. Это наш первоначальный план выпуска продукции. В ячейке А1 будет количество единиц изделия А, которую мы собираемся выпускать. Аналогично, в ячейке В1 - количество единиц изделий вида Б, а в ячейке С1 - количество единиц изделий В. Для определенности, занесем в эти ячейки по единице.

 В ячейки A2, B2, C2 занесем доходы от реализации единицы изделия вида А, Б, В соответственно, то есть числа 126, 123, 127.

 В ячейку D2 занесем формулу для вычисления суммарного прибыли от реализации изготовленных (согласно первоначальному плану) изделий:

= A1 * A2 + B1 * B2 + C1 * C2

После ввода формулы в ячейке появится число 376 - суммарная прибыль от реализации трех изделий (по одному изделию каждого из видов А, Б, В).

 Занесем в ячейки А3, В3, С3 расхода сырья М1 на единицу продукции вида А, Б, В соответственно. Аналогично, в ячейки А4, В4, С4 занесем расхода сырья М2, и т.д.

 В ячейки D3, D4, D5, D6, D7 занесем формулы для вычисления суммарных количеств сырья каждого вида, которая будет потрачена на изготовление запланированного количества продукции. То есть, в ячейку D3 занесем формулу

 = A3 * A1 + B3 * B1 + C3 * C1

в ячейку D4 - занесем формулу

 = A4 * A1 + B4 * B1 + C4 * C1 и т.д.

 Наконец, в ячейки Е3 .., Е7 занесем величину запасов сырья каждого вида - в ячейку Е3 заносим 500, в ячейку Е4 - 480 и т.д.

 В результате мы получим следующую таблицу:

 В режиме отображения формул [5] таблица (то есть рабочий лист) имеет следующий вид:

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

Теперь, чтобы найти решение, то есть план выпуска продукции, который обеспечит максимальную прибыль при имеющихся запасах сырья, применяем средство Поиск Решения.Для этого из главного меню выбираем Сервис Þ Поиск решения .... На экране открывается диалоговое окно Поиск решения:

 В поле Установить целевую ячейку нужно записать адрес ячейки, где находится суммарная прибыль от реализации произведенной продукции, то есть D2. Вместо записи, можно щелкнуть мышью на этой ячейке.

 Далее устанавливаем переключатель в положение, соответствующее надписи - максимальном значению, как на рисунке вверху (если он находится в другом положении).

 Щелкаем мышью в поле Изменяя ячейки, после чего, выделяем ячейки, где находится первоначальный план выпуска продукции, то есть ячейки A1, B1, C1. Щелкаем на кнопку Добавить. На экране открывается диалоговое окно Добавление ограничения:

 Записываем ограничения, накладываемые на план выпуска продукции ограниченными запасами сырья. В ячейках D3: D7 находятся расхода сырья вида М1, ..., М5 соответственно, которые будут использованы при реализации плана выпуска продукции, записанном в ячейках А1: С1. Расходы не должны превышать имеющихся ресурсов, то есть содержимого ячеек Е3: Е7. Для записи этих ограничений, щелкаем мышью на ячейке D3 [6]. В поле Ссылка на ячейку появляется абсолютный адрес этой ячейки - $ D $ 3. В следующем поле, если там не стоит знак <= (меньше-равно), устанавливаем его, выбрав из списка. Список раскрывается с помощью кнопки. Далее, щелкаем мышью в поле Ограничение, чтобы там появился курсора - на ячейке Е3. В поле появляется формула = $ E $ 3. Таким образом, мы записали ограничения: расходы сырья М1 не должны превышать ее имеющихся запасов. Далее щелкаем на кнопке Добавить, чтобы написать ограничения на расходы сырья М2: в поле Ссылка на ячейку указываем ячейку D4, а в поле Ограничение - ячейку Е4. Аналогично записываем ограничения на

расходы сырья вида М3, М4, М5, после чего щелкаем на кнопке Добавить.

Теперь нужно наложить ограничения на составляющие плана выпуска продукции - количество единиц продукции каждого вида должна быть ³ 0 (отрицательной) и, кроме того, выражаться целым числом. Итак, положительными должны соответствовать значениям, которые находятся в ячейках А1, В1, С1. Задаем это в диалоговом окне Добавление ограничения. В поле Ссылка на ячейку указываем ячейку А1, щелкнув на кнопке раскрытия списка, выбираем  > =  :

Щелкнув в поле Ограничение, записываем в нем 0. Это соответствует условию А1³0. Аналогично записываем условия В1³0 и С1³0. Далее, наложим условие целочисленности на составляющие плана, то есть на содержимое ячеек А1, В1, С1. Для этого, опять в поле Ссылка на ячейку, укажем ячейку А1, а в списке выберем цел. В поле Ограничение автоматически будет установлена ​​целое. Аналогично задаем целочисленность В1 и С1. После ввода условия целочисленности содержимого ячейки С1, вместо щелчка на кнопке Добавить, щелкаем на кнопке ОК. Окно Добавление ограничения исчезает, а вместо этого опять появляется окно Поиск решения:

Для нахождения плана, который обеспечит максимальную прибыль, достаточно щелкнуть на кнопке Выполнить. В таблице, в ячейках А1, В1, С1 будет находиться оптимальный план выпуска продукции (вместо первоначального). Кроме того, на экран будет выведено диалоговое окно Результаты поиска решения:

 

Если выбрать опцию Сохранить найденное решение и щелкнуть на кнопке ОК, то на рабочем листе зафиксируется найденное решение. То есть, рабочий лист будет выглядеть как на рисунке вверху. Если выбрать опцию Восстановить исходные значения, то мы вернемся к тому виду рабочего листа, который он имел до применения средства Поиск решения.

 При выполнении 8-го задания контрольной работы, нужно выбрать опцию Сохранить найденное решение . В случае отсутствия развязку (средство Поиск решения выводит в таком случае соответствующее сообщение) нужно в рабочем листе, в свободной ячейке, например, А8 вставка текста "Решение не найдено".

 Таким образом, решением задачи 8 является рабочий лист, содержащий найденное решение. Так, для приведенного выше примера, это

 Прокомментируем теперь найденное решение.

 В ячейках А1, В1, С1 находится оптимальный план. Согласно этому плану, для достижения максимальной прибыли, нужно выпустить 81 изделие вида А, 122 изделия вида Б, а изделия вида В не выпускать вообще. При реализации такого плана предприятие получит прибыль 25212 грн. В ячейке D3 будут находиться количество сырья вида М1, которая пойдет на реализацию плана; аналогично в ячейках D4, ..., D7 - затраты сырья соответствующего вида. 


[1] есть, независимых величин, фигурирующих в задаче.

[2] Курс может называться, конечно, и другим образом, например, Экономико-математические методы и т.п.

[3] точнее единицы продукции, поскольку продукция не обязательно должна быть поштучной - ее количество может измеряться в кг, м и т.п.

[4] Линейная функция в общем случае имеет вид  y = a + a 2 x 2 + ... + A N X N + a 0 , где  1 ,  2 , ...,  N  - некоторые стали . Когда говорят о линейной систему уравнений или неравенств, то это значит, что правая и левая часть каждого уравнения (неравенства) являются линейными функциями.

[5] Режим отображения формул отражает в ячейках кое значение, вычисленные по формулам, а сами формулы. Для перехода в этот режим нужно нажать комбинацию клавиш Ctrl + `. Для возврата в обычный нужно еще раз нажать эту комбинацию клавиш.

[6] Если в поле Ссылка на ячейку форуме курсора, нужно сначала щелкнуть мышью на этом поле.

 

Змiст

Тема 1. Основные понятия информатики.

Тема 2. Устройство компьютера и принципы его работы.

Тема 3. Периферийные устройства

Тема 4. Программное обеспечение

Тема 5. Операционная система Windows 95/98:

Тема 6. Операционная система Windows 95/98:

Тема 7. Операционная система Windows 95/98: Настройка

Тема 8. Стандартные программы Windows 95/98:

Тема 9. Стандартные программы Windows 95/98:

Тема 10. Редактор MS Word: Основы работы с редактором

Тема 11. Редактор MS Word: Форматирование документов

Тема 12. Редактор MS Word: Работа с таблицами.

Тема 13. Редактор MS Word: Дополнительные возможности

Тема 14. Электронные таблицы MS Excel: Основы работы

Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул

Тема 16. Электронные таблицы MS Excel: Построение диаграмм. Географические карты

Тема 17. Электронные таблицы MS Excel: Сводные таблицы. Таблицы данных.

Тема 18. Электронные таблицы MS Excel:

Тема 19. Электронные таблицы MS Excel Средство Поиск развязку


Нові надходження

Всього підручників:

292