II. Решение задачи распределения ресурсов в EXCEL.

1) Ввод данных примера 1 в таблицу EXCEL (рис.4).

Рис.4

На рис.4 «краска 1» обозначает краску для внутренних работ, «краска 2» – краску для наружных работ.

Для переменных задачи x₁ и x₂ отведены ячейки B3 и C3. Эти ячейки называются рабочими или изменяемыми ячейками. В изменяемые ячейки ничего не заносится и в результате решения задачи в этих ячейках будет оптимальные значения переменных.

В ячейку D4 вводится формула для вычисления целевой функции задачи (дохода) Z=2x₁+x₂. Чтобы сделать это надо выполнить следующие действия:

курсор в D4;

курсор на кнопку f_x (мастер функций);

В появившемся окне выбрать “Математические” и “СУММПРОИЗВ” (рис. 5).

Рис.5.

В окне мастера функций нажать Далее>, в появившемся окне (рис.6) в поле “массив 1” ввести (протаскивая курсор мыши по ячейкам) адреса изменяемых ячеек B3:C3. В поле “массив 2” вводятся адреса ячеек содержащих цены на краски B4:C4, после нажать Готово.

Рис.6

В ячейку D7 вводится формула для вычисления израсходованного количества продукта А: x₁+2x₂, а в ячейку D8 вводится формула для израсходованного количества продукта B: 3x₁+x₂. Обе формулы вводятся аналогично целевой функции (рис.7 и 8).

Рис.7

Рис.8

Проверить результаты ввода можно следующим образом: при установке курсора в ячейку D4 в строке ввода должно появиться: “=СУММПРОИЗВ(B3:C3; B4:C4)”; в ячейки D7: “=СУММПРОИЗВ(B3:C3; B7:C7)”; в ячейки D8: “=СУММПРОИЗВ(B3:C3; B8:C8)”.

Окончательно после ввода формул и данных экран имеет вид (рис.9):

Рис.9

2) Работа в окне “Поиск решения”

В меню “Сервис” выбираем процедуру “Поиск решения”

В появившемся окне (рис.10) нужно установить адрес целевой ячейки D4, значение целевой ячейки: максимальное, адреса изменяемых ячеек B3:C3.

Рис.10

Чтобы ввести ограничения задачи, нажать кнопку «Добавить». В появившемся диалоговом (рис.11) окне слева ввести адрес D7 (израсходованное количество продукта А), затем выбрать знак <= и в правой части количество продукта А на складе, равное 3 (или адрес ячейки E7).

Рис.11

После ввода нажать кнопку «Добавить» и аналогично ввести второе ограничение: D8 <= 3. Снова нажать кнопку «Добавить» и ввести ограничение: B3:C3 >= 0 (соответствующее ограничению x₁, x₂ >= 0). После ввода последнего ограничения нажать ОК. После ввода ограничений окно «Поиска решений имеет» будет иметь вид (рис. 12):

Рис.12

3) Настройка параметров решения задачи.

В окне «Поиск решения» нажать «Параметры» в появившемся окне (рис. 13) установить флажок в пункте «Линейная модель». В этом случае при решении задачи будет использоваться симплекс - метод. Остальные значения можно оставить без изменения. После нажать кнопку ОК

Рис.13

Для решения задачи в окне «Поиск решения» нажать кнопку «Выполнить». Если решение найдено появляется окно (рис.14):

Рис.14

Для просмотра результатов выбираем тип отчета: «Результаты» и нажимаем кнопку ОК. В появившихся трех таблицах (рис.15) приводятся результаты поиска. Из этих таблиц видно, что в оптимальном решении:

производство краски 1 = B3 = 0.6;

производство краски 2 = С3 = 1.2;

при этом доход = D4 = 2.4;

расход ресурса A = D7 = 3;

расход ресурса B = D8 = 3;

таким образом, оба ресурса дефицитные (соответствующие ограничения называются связанными).

Целевая ячейка (Макс)
	Ячейка	Имя	Исходно	Результат
	$D$4	Доход	2,4	2,4
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$3	Краска 1	0,6	0,6
	$C$3	Краска 2	1,2	1,2
Ограничения
	Ячейка	Имя	Значение	Формула	Состояние	Разница
	$D$7	A Расход		$D$7<=$E$7	связанное
	$D$8	B Расход		$D$8<=$E$8	связанное
	$B$3	Краска 1	0,6	$B$3>=0	не связан.	0,6
	$C$3	Краска 2	1,2	$C$3>=0	не связан.	1,2

Рис.15

«Отчет по результатам» состоит из трех таблиц (рис.15):

в таблице 1 приводятся сведения о целевой функции;

в таблице 2 приводятся значения переменных задачи;

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

Первоначальная таблица EXCEL заполняется результатами, полученными при решении (на рис.16 появившиеся значения в темных ячейках).

Рис.16

Пример 1. Определение оптимального ассортимента продукции

Предприятие изготавливает два вида продукции П1 и П2, которая поступает в оптовую продажу. Для производства используются два вида сырья и . Максимально возможные запасы сырья в сутки составляют 9 и 13 единиц соответственно. Расход сырья на единицу продукции приведен в таблице.

Таблица 2.1

Сырье	Расход сырья на единицу продукции	Запас сырья, ед.
П1	П2

Маркетинговые исследования показали, что суточный спрос на продукцию П1 не превышает спрос на продукцию П2 более чем на
1 ед. Кроме того, известно, что спрос на продукцию П2 не превышает 2 единиц в сутки.

Оптовые цены единицы продукции равны для П1 3 д.е., для
П2- 4 д.е. Какое количество продукции каждого вида должно производить предприятие, чтобы доход от реализации продукции был максимальным?

Решение

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

Доход от реализации продукции (целевая функция) составит

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

Проведем решение задачи в Excel.

Введем данные на рабочий лист так, как показано на Рис 2.1.

Искомые значения переменных будут располагаться в ячейках A10 и B10 соответственно, целевая функция – в ячейке E10.

Рис. 2.1

В ячейки A3, A4 введем левые части функций – ограничений: =2*A10+3*B10 и = 3*A10+2*B10 соответственно. В ячейку C10 введем левую часть третьей функции-ограничения: =A10-B10.

Далее, запускаем пакет Поиск решения (Сервис ® Поиск решения) и устанавливаем целевую и изменяемые ячейки, а также вводим необходимые ограничения (Рис.2.2)

Рис. 2.2 Окно диалога Поиск решения

Поиск решения дает ответ

Пример 2.Использование мощностей оборудования

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

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

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

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

Ограничение по заданному количеству продукции имеет вид

Задача решается на минимум затрат на производство

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

Проведем решение задачи в Excel. Введем данные на рабочий лист так, как показано на Рис 2.3.

В ячейки B7:E7 введем формулы для ограничений по объему выпускаемой продукции

()

в диапазон ячеек F19:F21 – формулы для ограничений по времени работы машин

()

В качестве целевой ячейки выберем H11 и введем в нее формулу минимизируемой функции.

информационный оптимизация линейный модель

Рис. 2.3. Данные для решения примера 2

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

	Время работы Xij
Машина
	803,92			196,07

Искомое значение минимальных затрат на производство составляет 725,32 д.е.

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

Пример 3. Оптимизация производственной программы

Автомобилестроительный завод выпускает три модели автомобилей, которые изготавливаются последовательно в трех цехах. Мощность цехов составляет 300, 250 и 200 человеко-дней в декаду. В первом цехе для сборки одного автомобиля первой модели требуется 6 человеко-дней, второй модели 4 и третьей модели – 2 человеко-дня в неделю соответственно. Во втором цехе трудоемкость равна 3, 4 и 5 человеко-дней соответственно, в третьем – по 3 человеко-дня на каждую модель. Прибыль, получаемая от продажи автомобиля каждой модели, составляет соответственно 15, 13 и 10 тыс. д.е. Требуется построить модель оптимального плана и определить оптимальные количества моделей каждого типа, т.е. такие, при которых прибыль завода будет максимальной.

Решение. Пусть - количество выпускаемых автомобилей -й модели в течение декады (). Модель может быть описана следующей целевой функцией и системами ограничений

(2.5)

Решение

Введем данные на рабочий лист так, как показано на Рис. 2.4.

Искомые значения переменных будут размещаться в ячейках A10:B10, целевая функция – в ячейке E10.

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

С помощью Поиска решения получим ответ

Рис. 2.4 Данные для решения примера 3

Пример 4. Размещение проектов на предприятиях

Имеется инвестиционных возможностей (вариантов проектов), которые можно реализовать на предприятиях. Эффективность реализации каждой инвестиции на каждом из объектов задана в таблице 2.2.

Таблица 2.2

Инвестиционные проекты ()	Объекты ()
I	II	III	IV	V
	0.12	0.02	0.50	0.43	0.15
	0.71	0.18	0.81	0.05	0.26
	0.84	0.76	0.26	0.37	0.52
	0.22	0.45	0.83	0.81	0.65
	0.49	0.02	0.50	0.25	0.27

Целевой функцией, подлежащей оптимизации, является функция

где - искомые распределения инвестиций по объектам.

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

означающие, что на каждом объекте может быть реализован лишь один проект, и

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

Решение

Введем данные на рабочий лист (Рис.2.5.).

В ячейку B17 введем формулу =СУММ(B12:B16) и скопируем эту формулу в диапазон C17:F17. Аналогично, введем формулу =СУММ(B12:F12) в ячейку G12 и скопируем ее в диапазон G13:G16. Введем в ячейку для целевой функции (I13) формулу

=СУММПРОИЗВ(B4:F8;B12:F16)

Рис. 2.5 Данные для решения примера 4

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

Поиск решения дает ответ

(остальные ), .

ТЕОРЕТИЧЕСКИЕ ВОПРОСЫ, КОТОРЫЕ БУДУТ ВЫДАНЫ НА МОДУЛЬНОЙ КОНТРОЛЬНОЙ ПО ДИСЦИПЛИНЕ АРМ

1. Назвіть передумови створення АРМ.

2. Охарактеризуйте структурні елементи АРМ.

3. У чому полягає технічне, інформаційне і програмне забезпечення АРМ?

4. Які принципи створення АРМ?

5. Охарактеризуйте методологію і етапи створення АРМ.

6. Охарактеризуйте інформаційну модель АРМ.

7. У чому полягає автоматизоване управління документообігом?

8. Опишіть архітектуру технічного забезпечення АРМ.

9. Які основні елементи електронного офісу?

10. Як здійснюється процес взаємодії керівника і спеціалістів.

11. Опишіть інформаційне забезпечення АРМ.

12. Охарактеризуйте модель інформаційного забезпечення АРМ.

13. У чому полягає система класифікації і кодування?

14. Які елементи складають базу даних?

15. Назвіть принципи організації інформаційного забезпечення АРМ.

16. Які СУБД-платформи застосовуються у АРМ?

17. Охарактеризуйте системи програмування 1С, БЕСТ, Office Tools.

18. Які особливості застосування програми Project Expert?

19. У чому призначення застосування інтегрованих систем програмування?

20. Опишіть структуру програм Галактика, Парус, SAP, Baan.

21. Визначити сутність АРМ – як елемента децентралізованої АІС.

22. Опишіть структуру АРМ управління товарно-матеріальними цінностями та складськими операціями.

23. Охарактеризуйте сутність автоматизації маркетингової діяльності

24. У чому полягають особливості автоматизації управління персоналом?

25. Опишіть структуру АРМ фінансової діяльності.

26. Принципи і напрямок використання в процесі автоматизації роботи менеджера прикладних програм:

а) табличного процесора “Excel”;

б) текстового редактора “Word”;

в) генератора презентацій “Power Point”;

г) планування роботи “Outlock”;

д) автоматизованого упорядкування на ЕОМ плану робіт проекту MS “Project”;

е) програми переносу даних із зовнішніх джерел MS “Query”;

ж) роботи в Інтернет “Internet Explorer”.

27. WEB-сторінка – засіб презентації підприємства в електронному середовищі.

28. Які основні модулі програми Microsoft Office?

29. Охарактеризуйте програми Word і Excel.

30. Охарактеризуйте застосування генератора презентацій Power Point у АРМ менеджера.

31. Охарактеризуйте програми Outlock Express і Internet Explorer.

32. У чому полягає концепція створення Web-сторінки?

33. У чому полягає системний підхід до проектування АРМ?

34. Назвіть принцип проектування АРМ.

35. Які етапи проектування АРМ?

36. Охарактеризуйте методологію проектування АРМ – IDEF.

Охарактеризуйте етапи впровадження АРМ

37. У чому полягає проблема ефективності АРМ?

38. Опишіть технологію оцінки ефективності АРМ.

39. Які фактори визначають підвищення ефективності в умовах АРМ?

40. Охарактеризуйте рівні соціальної ефективності АРМ?

41. Охарактеризуйте показники економічної ефективності АРМ.

42. Призначення, структура та особливості використання в ИСиТУО інтегрованих систем програмного забезпечення: “Галактика” і “Парус”.

43. Використання арм для автоматизації документообігу і діловодства в організації. Структурна схема арм постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

44. Використання АРМдля автоматизації рекламної та презентаційної діяльності менеджера. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

45.. Використання АРМ для комп'ютерної підтримки планування особистої роботи менеджера та електронного контролю за виконанням його планів. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

46. Використання АРМ для автоматизації виробничого менеджменту. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

47. Використання АРМ для автоматизації обліку та аудиту в організації. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

48. Використання АРМ для автоматизації обліку бухгалтерської звітності. Інформаційне забезпечення. Програма 1С-Бухгалтерія. Приклади роботи з довідником, планами розрахунків, журналом проводок і операцій.

49. Використання АРМ для автоматизації фінансового менеджменту. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

50. Використання АРМ для автоматизації управління торгово-технологічними процесами. Постановка основних задач та їх інформаційне забезпечення. Програма «1С – Торгівля». Приклади роботи з каталогом, провідником товарів, створення накладних, ведення рахунків і фіксація отримання коштів.

51. Використання АРМ для автоматизації управління персоналом організації. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

52. Використання АРМ для автоматизації маркетингової діяльності. Постановка основних задач з аналізу ринку, вибору асортименту продукції та його розширення. Інформаційне, програмне забезпечення, комп'ютерна технологія.

53. Використання АРМ для автоматизації комерційної та підприємницької діяльності. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

54. Використання комп'ютерної мережі Інтернет для підвищення ефективності менеджерської діяльності. Комп'ютерна технологія збору, передачі та отримання інформації. Пошук даних для визначення індексу ділової активності. Електронна комерція.

55. Використання АРМ для комплексного аналізу господарської діяльності організацій. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

56. Використання АРМ для стратегічного планування менеджерської діяльності. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

57. Використання АРМ для комп'ютерної підтримки прийняття управлінських рішень. Постановка основних задач з оптимізації виробничо-господарської діяльності, інформаційне, програмне забезпечення, комп'ютерний вибір оптимальних стратегій та управлінських рішень.

58. Використання АРМ для управління проектами менеджменту. Постановка основних задач. Програма «Microsoft Project».

59. Використання засобів комп'ютерної бази даних «Microsoft Access» для реалізації АРМ. Постановка основних задач. Створення таблиць за допомогою функцій «Майстер» та «Конструктор». Комп'ютерна технологія роботи з даними в режимах: «таблиць», «запитів», «звітів». Використання макросів.

60. Використання АРМ для управління бізнес-процесами.

61. Структурна схема АРМ, постановка основних задач, інформаційне, програмне забезпечення, комп'ютерна технологія.

Дата добавления: 2015-08-20