Планирование производства в Excel

Курсовая работа

по курсу: «Информационные технологии в металлургии»

на тему: «Решение задач планирования производства в Excel»

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

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

Введение

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

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

1. Основные приемы работы в EXCEL

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

Данные в EXCEL хранятся в рабочих книгах.

Рабочая книга (WorkBook) — это универсальный аналог картотеки.

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

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

Столбцы рабочего листа именуются буквами, а строки — цифрами (рис 3.4)

Пересечение столбца и строки рабочего листа образует ячейку (CELL). Например, столбец, А и строка 1 образуют ячейку с адресом А1 (рис 3.4.).

Замечание: Русские буквы в обозначении столбцов использовать нельзя.

Ячейка — это электронный аналог одной клетки таблицы.

В каждую ячейку может быть записано число, текст или формула. Запись формулы должна начинаться со знака=.

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

Блоком ячеек (RANGE) называется совокупность смежных ячеек, образующих прямоугольную область.

Адрес блока ячеек состоит из адреса верхней левой ячейки блока и адреса правой нижней ячейки блока, разделенных знаком:

На рисунке 1 показан блок ячеек с адресом В2: D 4.

Рисунок 1

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

1.1 Селекция ячейки

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

Для селекции ячейки необходимо:

1. Установить курсор на требуемую ячейку;

2. Щелкнуть левой клавишей мыши.

Визуально селекция сопровождается появлением рамки вокруг ячейки, в нижнем правом углу которой имеется маленький квадрат — маркер заполнения (File handle).

1.2 Селекция блока ячеек

Селекция блока ячеек — это выбор блока ячеек, с которым будет работать пользователь или программные средства EXCEL.

Для селекции блока ячеек необходимо:

1. Установить курсор на левую верхнюю ячейку блока;

2. Нажать левую клавишу мыши;

3. Удерживая левую клавишу мыши нажатой, переместить курсор на правую нижнюю ячейку блока.

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

1.3 Ввод данных в ячейки

Для ввода в ячейку числа, текста или формулы необходимо:

1. Селектировать ячейку;

2. Набрать вводимое данное на клавиатуре;

3. Нажать клавишу ENTER.

1.4 Копирование формул

Копирование формул является мощным средством автоматизации вычислений в EXCEL. Оно позволяет распространить влияние формулы с первой ячейки некоторого блока ячеек на остальные ячейки этого блока. При этом автоматически происходит индексация адресов ячеек, содержащих аргументы копируемых формул.

Для копирования формулы необходимо:

1. Селектировать первую ячейку блока, содержащую формулу;

2. Установить курсор на маркер заполнения (маленький квадрат в правом нижнем углу ячейки). При этом курсор должен принять вид крестика, т. е. + (рисунок 2).

3. Нажать левую клавишу мыши и, удерживая ее, переместить курсор к последней ячейке блока.

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

$ C 6 — фиксируется столбец С;

C $ 6 — фиксируется строка 6;

$ C $ 6 — фиксируется ячейка С6.

Фиксация адреса ячейки происходит также при присвоении ячейке имени.

Рисунок 2

1.5 Установка границ ячеек и блоков ячеек

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

Для установки границ вокруг ячеек или блоков ячеек необходимо:

1. Селектировать ячейку или блок ячеек;

2. Навести курсор на стрелку правее кнопки «границы» и щелкнуть левой клавишей мыши (см. рис. 4.3);

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

Рисунок 3

1.6 Присвоение имен ячейкам и блокам ячеек

Для автоматизации вычислений часто бывает необходимо присвоить имена отдельным ячейкам и блокам ячеек средствами EXCEL.

Для присвоения имени ячейке или блоку ячеек необходимо:

1. Селектировать ячейку или блок ячеек;

2. Навести курсор на стрелку справа от окна имени и щелкнуть левой клавишей мыши;

3. Набрать на клавиатуре имя, например _ x ;

4. Нажать клавишу Enter.

При задании имени следует учитывать следующие правила:

1. Имя должно начинаться с буквы или знака подчеркивания. В качестве остальных символов могут использоваться буквы, цифры и знак подчеркивания;

2. Имя не должно совпадать с адресами ячеек и блоков, например А5; В5: С6.

1.7 Изменение ширины столбца

При вводе в ячейку текстовых данных иногда возникает необходимость изменения ширины столбца. Для изменения ширины столбца необходимо:

1. Установить курсор на правую границу заголовка столбца. При этом курсор примет вид .

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

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

Для этого необходимо:

1.Селектировать ячейку (блок ячеек);

2.Установить курсор на стрелку правее кнопки «цвет заливки» и щелкнуть левой клавишей мыши;

3.В меню «цвет заливки» выбрать курсором желаемый цвет, например — изумрудный, и щелкнуть левой клавишей мыши.

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

2. Решение транспортной задачи

Рассмотрим следующую транспортную задачу. Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно каждый из заводов может изготовить 100, 150 и 50 условных единиц кирпича (предложение поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 75, 80, 60 и 85 условных единиц (спрос потребителей). Тарифы перевозок одной условной единицы кирпича с каждого из заводов к каждому из строящихся объектов задаются матрицей транспортных расходов С.

Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.

Для решения транспортной задачи на персональном компьютере с использованием EXCEL необходимо:

Ввести исходные данные в ячейки рабочего листа EXCEL;

Разметить блоки ячеек на рабочем листе EXCEL, необходимые для моделирования объемов перевозок, а также для формирования элементов математической модели и целевой функции;

Сформировать на рабочем листе EXCEL элементы математической модели и целевую функцию;

Настроить программу «Поиск решения» и выполнить ее.

2.1 Ввод исходных данных

Исходными данными для решения транспортной задачи являются:

еxcel ячейка формула столбец Напомним, что для ввода данного в ячейку рабочего листа EXCEL необходимо:

Селектировать ячейку;

Набрать вводимое данное на клавиатуре;

Нажать клавишу Enter.

Для наглядности блоки ячеек с введенными данными желательно обвести рамками.

Рабочий лист EXCEL с введенными исходными данными для решения транспортной задачи показан на рисунке 4.

Рисунок 4

2.2 Разметка блоков ячеек рабочего листа EXCEL

Кроме исходных данных на рабочем листе EXCEL для решения транспортной задачи необходимо предусмотреть:

1. Блок ячеек «Матрица перевозок», в котором будут моделироваться объемы перевозок;

2. Блок ячеек «Фактически реализовано», в котором будет моделироваться фактическая реализация продукции;

3. Блок ячеек «Фактически получено», в котором будет моделироваться фактическое удовлетворение спроса;

4. Блок ячеек «Транспортные расходы по потребителям», в котором будут подсчитываться транспортные расходы по каждому потребителю;

5. Ячейку «Итого расходы», в которой будут моделироваться итоговые транспортные расходы по всем потребителям (целевая ячейка).

Для наглядности указанные блоки ячеек целесообразно обвести рамками. Выполните эту операцию, называемую разметкой блоков ячеек. Рабочий лист EXCEL с размеченными блоками ячеек показан на рисунке 5.

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

Рисунок 5

2.3 Формирование элементов математической модели

Элементами математической модели транспортной задачи являются следующие суммы:

— фактически реализовано i-ым поставщиком i = ;

— фактически получено j-ым потребителями j =

Для нашей задачи m=3, n=4.

Рассмотрим процесс формирования этих сумм на рабочем листе EXCEL.

Вначале сформируем, i = в блоке «Фактически реализовано».

1. Заполните ячейки блока «Матрица перевозок» (С14:F16) числом 0,01.

2. Селектируйте первую ячейку блока «Фактически реализовано» (ячейка I14);

3. Наведите курсор на кнопку — автосуммирование и щелкните левой клавишей мыши;

4. Нажмите клавишу Delete;

5. Селектируйте первую строку блока «Матрица перевозок» (строка С14: F14);

6. Нажмите клавишу Enter;

7. Скопируйте формулу=СУММ (С14:F14) из первой ячейки блока «Фактически реализовано» на все остальные ячейки этого блока.

Сформируем теперь j = - в блоке «Фактически получено» .

Для этого выполните следующие действия:

1. Селектируйте первую ячейку блока «Фактически получено» (ячейка С18);

2.Наведите курсор на кнопку — автосуммирование и щелкните левой клавишей мыши;

3.Нажмите клавишу Delete;

4.Селектируйте первый столбец блока «Матрица перевозок» (Столбец С14: C16);

5.Нажмите клавишу Enter;

6.Скопируйте формулу=CУММ (С14:С16) из первой ячейки блока «Фактически получено» на остальные ячейки этого блока.

2.4 Формирование целевой функции Для формирования целевой функции введем вначале формулы, отражающие транспортные расходы по каждому потребителю, т. е. формулы:

j = в ячейки блока «Транспортные расходы по потребителям»

Для ввода этих формул выполните следующие действия:

1. Селектируйте первую ячейку блока «Транспортные расходы по потребителям» (ячейка С21);

2. Наведите курсор на кнопку — автосуммирование и щелкните левой клавишей мыши;

3. Нажмите клавишу «Delete «;

4. Селектируйте первый столбец блока «Матрица Транспортных расходов» (столбец С6: С8);

5. Нажмите клавишу *;

6. Селектируйте первый столбец блока «Матрица превозок» (столбец С14: С16);

7. Активируйте строку формул, наведя на неё курсор и щелкнув затем левой клавишей мыши;

8. Нажмите одновременно три клавиши: «CTRL"+"SHIFT"+"ENTER»;

9. Скопируйте формулу {=СУММ (С6:С8*С14:С16)} в остальные ячейки блока «Транспортные расходы по потребителям»;

Сформируем теперь целевую функцию транспортной задачи, выражаемую формулой, в ячейку «Итого расходы». Для этого:

Селектируйте ячейку «Итого расходы» (ячейка I21);

1. Наведите курсор на кнопку — автосуммирование и щелкните левой клавишей мыши;

2. Нажмите клавишу «Delete»;

3. Селектируйте блок ячеек «Транспортные расходы по потребителям"(С21:F21);

4. Нажмите клавишу «Enter»;

После формирования элементов математической модели и целевой функции транспортной задачи рабочий лист EXСEL примет вид, показанный на рисунке 6.

Теперь можно приступить к настройке программы «Поиск решения».

Рисунок 6

2.4 Настройка программы Поиск решения

Для настройки программы «Поиск решения» на решение транспортной задачи выполните следующие действия:

1. Селектируйте целевую ячейку «Итого расходы» (ячейка I21);

2. Установите курсор в строке главного меню на пункте «Сервис» и щелкните левой клавишей мыши;

3. Установите курсор на пункт «Поиск решения» меню «Сервис», щелкните левой клавишей мыши и убедитесь, что в поле «Установить целевую ячейку» окна диалога программы «Поиск решения» указана ячейка $I$ 21 (рисунок 7)

Рисунок 7

4. Установите курсор на переключатель «Равной Минимальному значению» и щелкните левой клавишей мыши;

5. Установите курсор в поле «Изменяя ячейки» и щелкните левой клавишей мыши;

6. Селектируйте блок ячеек «Матрица первозок» (блок С14: F16);

7. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

Появившееся окно диалога команды «Добавление ограничения» показано на рисунке 8.

Рисунок 8

8. Селектируйте блок ячеек «Фактически реализовано» (блок I14: I16);

9. Убедитесь, что оператор сравнения <=уже выбран;

10. Установите курсор на поле «Ограничение"и щелкните левой клавишей мыши;

11. Селектируйте блок ячеек «Предложение поставщиков» (блок I6: I8) и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 9.

Рисунок 9

12. Установить курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

13. Селектируйте блок ячеек «Фактически получено» (блок С18: F18);

14. Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;

15. Установите курсор на значение >= (больше или равно) и щелкните левой клавишей мыши;

16. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

17. Селектируйте блок ячеек «Спрос потребителей» (блок С10: F10) и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 10.

Рисунок 10

18. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

19. Селектируйте блок ячеек «Матрица перевозок» (блок С14: F16);

20. Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;

21. Установите курсор на значение >= (больше или равно) и щелкните левой клавишей мыши;

22. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

23. Наберите на клавиатуре цифру 0 и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 11.

Рисунок 11

24. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

25. Установите курсор на кнопку «Отмена» и щелкните левой клавишей мыши;

26. Убедитесь, что появившееся окно программы «Поиск решения» имеет вид, показанный на рисунке 12.

Рисунок 12

27. Установите курсор на кнопку «Параметры» и щелкните клавишей мыши;

28. В появившемся окне диалога «Параметры поиска решения» (рисунок 13), установите курсор на флажок «Линейная модель» и щелкните левой клавишей мыши;

29. Установите курсор на кнопку «ОК» о щелкните левой клавишей мыши;

30. В появившемся окне «Поиск решения» установите курсор на кнопку «Выполнить» и щелкните левой клавишей мыши.

Рисунок 13

31. Убедитесь, что на рабочем листе EXCEL в блоке «Матрица перевозок» появляется решение транспортной задачи, показанное на рисунке 13.

Рисунок 14

В появившемся диалоговом окне «Результаты поиска решения» установите курсор на переключатель «Восстановить исходные значения» и щелкните левой клавишей мыши. Для завершения расчетов щелкните на кнопке ОК. (рисунок 15).

Рисунок 15

Замечание. Выполнение пунктов 19−24 можно заменить установкой флажка «Неотрицательные значения» в окне диалога «Параметры поиска решения» .

3. Решение задачи определения наиболее прибыльного объема выпуска продукции

Рассмотрим следующую задачу. На машиностроительном предприятии для изготовления четырех видов продукции используется токарное, фрезерное, сверлильное, расточное и шлифовальное оборудование, а также комплектующие изделия. Кроме того, для сборки готовой продукции требуется выполнение определенных сборочно-наладочных работ. Нормы расхода ресурсов на изготовление одного изделия каждого вида приведены в таблице на рисунке 16. В этой же таблице указаны: имеющиеся в наличие ресурсы, ограничения, обусловленные спросом на выпуск продукции второго и третьего видов, и прибыль от реализации одного изделия. В отличие от будем предполагать, что в общем случае прибыль с увеличением выпуска продукции может уменьшаться. Степени влияния объема выпуска на прибыль по каждому изделию также приведены в таблице. Заметим, что если степень влияния равна единице, то увеличение объема выпуска изделия не приводит и уменьшению прибыли. Требуется определить такой объем выпуска продукции, который обеспечивает предприятию наибольшую прибыль.

Рисунок 16

Для решения задачи на персональном компьютере с использованием EXCEL необходимо:

1. Ввести исходные данные в ячейки рабочего листа EXCEL;

2. Разметить блоки ячеек, необходимые для моделирования объема выпуска продукции, а также для формирования элементов математической модели и целевой функции;

3. Сформировать на рабочем листе EXCEL элементы математической модели и целевую функцию;

4. Настроить программу «Поиск решения» и выполнить ее.

3.1 Ввод исходных данных Исходными данными для решения задачи определения наиболее прибыльного объема выпуска продукции являются:

имеющиеся в наличии ресурсы;

нормы расхода ресурсов на выпуск одного изделия;

максимальная и минимальная величина спроса на изделия;

прибыль от реализации одного изделия;

степень влияния объема выпуска изделия на прибыль.

Напомним, что для ввода данного в ячейку рабочего листа EXCEL необходимо:

1. Селектировать ячейку;

2. Набрать вводимое данное на клавиатуре;

3. Нажать клавишу Enter.

Рабочий лист EXCEL c введенными исходными данными для решения задачи показан на рисунке 17.

Рисунок 17

3.1 Разметка блоков ячеек рабочего листа Excel

Кроме исходных данных, на рабочем листе EXCEL для решения задачи определения наиболее прибыльного объема выпуска продукции необходимо предусмотреть:

1. Блок ячеек «Оптимальный выпуск», в котором будет моделироваться объем выпуска продукции;

2. Блок ячеек «Фактическое использование», в котором будет моделироваться фактическое использование ресурсов;

3. Блок ячеек «Прибыль по изделиям», в котором будет моделироваться получение прибыли от реализации каждого вида продукции.

4. Ячейку «Итоговая прибыль», в которой будет моделироваться получение прибыли от реализации всей продукции.

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

Рабочий лист EXCEL с размеченными блоками ячеек показан на рисунке 18. Теперь в этих блоках ячеек можно формировать элементы математической модели и целевую функцию.

Рисунок 18

3.2 Формирование элементов математической модели

Элементами математической модели задачи определения наиболее прибыльного объема выпуска продукции являются следующие суммы:

— фактическое использование i-го ресурса .

Для нашей задачи n=4, m=7.

Перед формированием этих сумм на рабочем листе EXCEL целесообразно блоку ячеек «Оптимальный выпуск», в котором будет моделироваться выпуск готовой продукции, присвоить имя, например, _Х.

1. Селектировать блок ячеек «Оптимальный выпуск» (блок В18: E18);

2. Навести курсор на стрелку справа от окна имени и щелкнуть левой клавишей мыши;

3. Набрать на клавиатуре _Х;

4. Нажать клавишу Enter.

Для формирования, выполните следующие действия:

1. Заполните ячейки блока «Оптимальный выпуск (блок В18: E18) числами 0,01;

2. Селектируйте первую ячейку блока «Фактически использовано» (ячейка G5);

3. Наведите курсор на кнопку — автосуммирование и щелкните левой клавишей мыши;

4. Нажмите клавишу Delete;

5. Селектируйте блок «Оптимальный выпуск» (блок В18: E18);

6. Нажмите клавишу *;

7. Селектируйте первую цифровую строку блока «Нормы расхода ресурсов на одно изделие» (блок В5: Е5);

8. Активируйте строку формул, наведя на нее курсор, и щелкните левой клавишей мыши;

9. Нажмите одновременно три клавиши «Ctrl"+"Shift"+"Enter»;

10. Скопируйте из ячейки G5 формулу {=СУММ (_х*B5:E5)} в остальные ячейки блока «Фактически использовано» (блок G5: G11).

3.3 Формирование целевой функции

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

Для формирования целевой функции выполните следующие действия:

1. Селектируйте первую ячейку блока «Прибыль по изделиям» (ячейка В20);

2. Введите с клавиатуры формулу=В14*МАКС (В18;0)^В16;

3. Нажмите клавишу Enter;

4. Скопируйте формулу из ячейки В20 на все остальные ячейки блока «Прибыль по изделиям» (блок В20: E20);

5. Селектируйте ячейку «Итоговая прибыль» (ячейка G22);

6. Наведите курсор на кнопку - автосуммирование и щелкните левой клавишей мыши;

7. Нажмите клавишу Delete;

8. Cелектируйте блок «Прибыль по изделиям» (блок В20: E20);

9. Нажмите клавишу Enter.

После формирования элементов математической модели и целевой функции задачи определения наиболее прибыльного объема выпуска продукции рабочий лист EXCEL примет вид, показанный на рисунке 19.

Рисунок 19

Теперь можно приступить к настройке программы «Поиск решения».

3.4 Настройка программы Поиск решения

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

1. Селектируйте целевую ячейку «Итоговая прибыль» (ячейка G22);

2. Установите курсор в строке главного меню на пункте «Сервис» и щелкните левой клавишей мыши;

3. Установите курсор на пункте «Поиск решения» меню «Сервис» и щелкните левой клавишей мыши;

4. Убедитесь, что в поле «Установить целевую ячейку» окна диалога программы «Поиск решения» указана ячейка $G$ 22 (рисунок 18);

5. Убедитесь, что переключатель установлен на значение «Равной максимальному значению» (рисунок 20);

Рисунок 20

7. Установите курсор в поле «Изменяя ячейки» и щелкните левой клавишей мыши;

8. Селектируйте блок ячеек «Оптимальный выпуск» (блок В18: Е18);

9. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши. Появится окно команды «Добавление ограничения», показанное на рисунке 21.

Рисунок 21

10. Селектируйте блок ячеек «Фактически использовано» (блок G5: G11);

11. Убедитесь, что оператор сравнения <= уже выбран;

12. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

13. Селектируйте блок ячеек «Наличие ресурсов» (блок F5: F11) и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 22;

Рисунок 22

15. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

16. Селектируйте блок ячеек «Оптимальный выпуск» (блок В18: Е18);

17. Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;

18. Установите курсор на значение, >= и щелкните левой клавишей мыши;

19. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

20. Наберите на клавиатуре цифру 0 и убедитесь, что окно команды «Добавление ограничения» имеет вид, показанный на рисунке 23.

Рисунок 23

21. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

22. Селектируйте ячейку С18;

23. Установите курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;

24. Установите курсор на значение >=и щелкните левой клавишей мыши;

25. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

26. Селектируйте ячейку С12 и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 24.

Рисунок 24

27. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

28. Селектируте ячейку D18;

29. Убедитесь, что оператор сравнения <=уже выбран;

30. Установите курсор на поле «Ограничение» и щелкните левой клавишей мыши;

31. Селектируйте ячейку D13 и убедитесь, что окно диалога команды «Добавление ограничения» имеет вид, показанный на рисунке 25.

Рисунок 25

32. Установите курсор на кнопку «Добавить» и щелкните левой клавишей мыши;

33. Установите курсор на кнопку «Отмена» и щелкните левой клавишей мыши;

34. Убедитесь, что появившееся окно программы «Поиска решения» имеет вид, показанный на рисунке 26;

Рисунок 26

35. Установите курсор на кнопку «Выполнить» и щелкните левой клавишей мыши;

36. Убедитесь, что на рабочем листе EXCEL в блоке «Оптимальный выпуск» появляется решение задачи определения наиболее прибыльного объема выпуска продукции, показанное на рисунке 27;

Рисунок 27

37. В появившемся диалоговом окне «Результаты поиска решения» установите курсор на переключатель «Восстановить исходные значения» и щелкните левой клавишей мыши (рисунок 28);

Рисунок 28

38. Для завершения расчетов щелкните на кнопке «ОК».

Замечание. Выполнение пунктов 14−19 можно заменить установкой флажка «неотрицательные значения» в окне диалога «Параметры поиска решения» .

Если в рассматриваемой задаче все величины (степени влияния объема выпуска продукции на прибыль) равны 1, т. е. если задача линейная, то результат ее решения можно увидеть на рисунке 28.

Заметим, что если задача является линейной, то перед ее решением целесообразно в окне диалога «Параметры поиска решения» установить флажок «Линейная модель».

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

Рисунок 29

Заключение

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

1. Встроенная справка Microsoft Excel.

2. М. Хелворсон, М. Янг «Эффективная работа с Microsoft Office».

3. http://www.microsoft.com/rus/ - Российская страница компании Microsoft.