Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 336 Методичні вказівки до лабораторної роботи №8 на тему Інформаційна технологія розв'язку задач планування діяльності підприємства, НУДПСУ

Вказівки до лабораторної роботи №8 на тему Інформаційна технологія розв'язку задач планування діяльності підприємства

« Назад

336 Методичні вказівки до лабораторної роботи №8 на тему Інформаційна технологія розв'язку задач планування діяльності підприємства, НУДПСУ

Лабораторна робота № 8. Інформаційна технологія розв'язку задач планування діяльності підприємства

Мета. Проробити на персональному комп'ютері основні питання організації автоматизованої обробки даних на АРМ планувальника в умовах функціонування інтегрованої системи обробки економічної інформації на підприємствах споживчої кооперації; розвинути у студентів навики в застосуванні отриманих тео­ретичних знань при розв'язку на персональних комп'ютерах комплексу економічних завдань розглядуваного АРМ.

Короткі теоретичні відомості

Засіб Подбор параметра

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

Для рішення цієї задачі необхідно виконати наступні дії:

  • ввести формулу в необхідну комірку, натиснути кнопку Ввод в рядку формул;

  • виконати команду Сервис/Подбор параметра;

  • у полі Установить в ячейке ввести посилання на комірку, в якій введено формулу;

  • у поле Значение ввести значення, яке необхідно отримати по заданій формулі;

  • у поле Изменяя ячейку ввести посилання на комірку, в якій знаходиться значення змінюваного параметра;

  • натиснути ОК.

Приклад. Нехай дано рівняння х2 +3х - 2 = а, де а - необхідний результат формули, х - шуканий параметр. Необхідно знайти таке значення параметра х, при якому а=20.

Для цього виконаємо наступні дії:

• введемо формулу =А1 ^2+3 *А1-2 у клітинку В1.

виконаємо команду Сервис/Подбор параметра і у діалоговому вікні, що з' явиться введемо.

• натиснемо кнопку ОК. Результат.

 

Засіб Поиск решения

У попередньому прикладі ми розглянули задачу пошуку значення параметра, що дає змогу досягти конкретної мети.

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

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

Такі задачі в ЕхсеІ розв'язують за допомогою Поиска решения.

Якщо математична модель досліджуваного процесу й обмеження на значення її параметрів лінійні, то задача досягнення мети є задачею лінійного програмування. У процедурі пошуку розв'язку Microsoft Excel використовується алгоритм нелінійної оптимізації Generalized Reduced Gradient (GRG2) і алгоритми симплексного методу і методу «branch-and-bound» для розв'язку лінійних і цілочислових задач з обмеженнями.

Постановка задачі й оптимізація моделі за допомогою процедури пошуку розв'язку.

План вигідного виробництва. Припустимо, що ми вирішили виробляти кілька видів цукерок. Назвемо їх умовно «А», «В» і «С». Відомо, що реалізація десяти кілограмів цукерок «А» дає прибуток 9 грн, «В» — 10 грн і «С» — 16 грн.

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

Норми витрати сировини на виробництво 10 кг цукерок кожного вигляду наведені нижче (табл. 27).

Таблиця 27

Сировина

Норми витрат сировини

Запас сировини

а

Ь

с

Наповнювач

20

15

12

360

Цукор

6

4

8

192

Какао

5

3

3

180

Прибуток

10

10

16

 

Нехай XI — планове виробництво цукерок «А»; Х2 — цукерок «В» і ХЗ — цукерок «С» (у десятках кілограмів). Результат шпигнемо у табл. 28.

Таблиця 28

Обмеження

А

В

С

Знак порівняння

Права частина обмеження

Перша нерівність

20X1

15X2

12X3

<=

360

Друга нерівність

6X1

4X2

8X3

<=

192

Третя нерівність

5X1

3X2

3X3

<=

180

Цільова функція

10X1

10X2

16X3

→

mах

Рішення:

•  Введемо початкові дані і формули в електрону таблицю

У меню Сервис виберемо команду Поиск решения і встановимо наступні параметри

Натиснемо кнопку Параметри і у діалоговому вікні, що з' явиться виконаємо необхідні установки

Натиснемо кнопку ОК і у наступному діалоговому вікні кнопку Вьіполнить.

У результаті з' явиться діалогове вікно.

у якому, щоб зберегти знайдений розв'язок, встановимо переми­кач в положення Сохранить найденное решение. На робочому аркуші з' явиться результат пошуку рішення.

Із розв'язку видно, що оптимальний план випуску передбачає виготовлення 80 кг цукерок «В» і 200 кг цукерок «С». Цукерки «А» проводити не варто. Отриманий Вами прибуток становить 400 грн.

Примітка. Діалогове вікно «Поиск решения» містить наступні об'єкти:

Установить целевую ячейку — поле, що служить для вказівки цільової комірки, значення якої потрібно максимізувати, мінімізувати або встановити рівним заданому числу. Ця комірка має містити формулу.

Равной — перемикач, що служить для вибору варіанту оптимізації значення цільової комірки (максимізація, мінімізація або підбір заданого числа). Щоб встановити число, введіть його в полі.

Изменяя ячейки — поле, що служить для вказівки комірок, значення яких змінюються в процесі пошуку розв'язку доти, поки не будуть виконані накладені обмеження й умова оптимізації значення комірок, вказаних у полі Установить целевую ячейку.

Предположить — кнопка, що використовується для автоматичного пошуку комірок, що впливають на формулу, посилання на яку подано в полі Установить целевую ячейку. Результат пошуку відображається в полі Изменяя ячейки.

Ограничения — поле, що служить для відображення списку граничних умов поставленої задачі.

Добавить — кнопка, що використовується для відображення діалогового вікна Добавить ограничение.

Изменить — кнопка, що використовується для відображення діалогового вікна Изменить ограничение.

Удалить — кнопка, що використовується для зняття вкааі« ного обмеження.

Выполнить — кнопка, що використовується для запуску пошуку розв'язку поставленої задачі.

Закрить — кнопка, що використовується для виходу з вікна діалогу без запуску пошуку розв'язку поставленої задачі, При цьому зберігаються установки, зроблені у вікнах діалогу, що з'являлися після натиснень на кнопки Параметри, Добавить, Изменить або Удалить.

Параметри — кнопка, що використовується для відображення діалогового вікна Параметри поиска решения, в якому можна завантажити або зберегти модель, що оптимізується, і вказати передбачені варіанти пошуку розв'язку.

Восстановить — кнопка, що використовується для очищення полів вікна діалогу і відновлення значень параметрів пошуку розв'язку, що використовуються за замовчуванням.

Підсумкові повідомлення процедури пошуку розв'язку:

Якщо пошук розв'язку успішно завершено, в діалоговому вікні Результати поиска решения виводиться одне з таких повідомлень: Розв'язок знайдено; Усі обмеження й умови оптимальності виконані; Усі обмеження дотримані зі встановленою точністю і знайдено задане значення цільовій комірки; Пошук звівся до поточного розв'язку; Усі обмеження виконані;

Відносна зміна значення в цільової комірці за останніх п'ять ітерацій стала менше встановленого значення параметра Сходимость у діалоговому вікні Параметри поиска решения. Щоб знайти більш точний розв'язок, вста­новіть менше значення параметра Сходимость, але це забере більше часу.

Якщо пошук не може знайти оптимальне розв'язок, в діалоговому вікні Результати поиска решения виводиться одне з наступних повідомлень:

Пошук не може поліпшити поточний розв'язок; усі обмеження виконані.

У процесі пошуку розв'язку не можна знайти такий набір значень впливових комірок, який би був кращий за поточний розв'язок. Приблизний розв'язок знайдено, але або подальше уточнення неможливе, або задана похибка дуже висока. Змініть погрішність на менше число і запустіть процедуру пошуку розв'язку знову.

Пошук зупинений (закінчився заданий на пошук час).

Час, відпущений на розв'язання задачі, вичерпаний, але досягти задовільного розв'язку не вдалося. Щоб при наступному запуску процедури пошуку розв'язку не повторювати виконані обчислення, встановіть перемикач Сохранить найденое решение або Сохранить сценарий.

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

Проведено дозволене число ітерацій, але досягти задовільного розв'язку не вдалося. Збільшення числа ітерацій може допомогти, проте треба розглянути результати, аби зрозуміти причини зупинки. Щоб при наступному запуску процедури пошуку розв'язку не повторювати виконані обчислення, встановіть перемикач Сохранить найденное решение або натисніть к. і юпку Сохранить сценарий.

Значення цільової комірки не збігаються.

Значення цільової комірки необмежено збільшується (або зменшується), навіть якщо всі обмеження дотримані. Можливо в задачі варто зняти одне обмеження або відразу кілька обмежень. Вивчіть процес розбіжності розв'язку, перевірте обмеження і запустіть задачу знову.

Пошук не може знайти відповідного розв'язку.

У процесі пошуку розв'язку не можна зробити ітерацію, яка задовольняла б усі обмеження при заданій точності. Ймовірно, обмеження суперечливі. Досліджуйте аркуш на предмет можливих помилок у формулах обмежень або у виборі обмежень.

Умови для лінійної моделі не задовольняються.

Встановлений прапорець Линейная модель, проте підсумковий перерахунок породжує такі значення, які не узгоджуються з лінійною мод елю. Це означає, що розв'язок недійсний для даних формул аркуша. Щоб перевірити лінійність задачі, встановіть прапорець Автоматическое масштабирование і повторно запустіть задачу. Якщо це повідомлення знову з'явиться на екрані, зніміть прапорець Линейная модель і знову запустіть задачу.

При пошуку розв'язку знайдено помилкове значення у цільовій комірки або в комірці обмеження.

Замало обсягу пам'яті для розв'язування задачі.

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

 

Завдання для лабораторних занять і самостійної роботи

ЗАВДАННЯ 1. Директор універмагу районної споживчої спілки повинен скласти штатний розпис, тобто визначити, скільки працівників, на яких посадах і з яким окладом він повинен прийняти на роботу. Загальний місячний фонд зарплати складає 30000 грн. Розробити постановку задачі і алгоритм її розв'язку, за допомогою програмних засобів МS Ехсеl розв'язати задачу на персональному комп‘ютері і проаналізувати результати, результатну інформацію видати на друк.

Початкові дані. Для нормальної роботи універмагу необхідно 9 продавців, 7 старших продавців, 10 менеджерів для роботи з товарами, 3 завідувачі відділами, 1 менеджер з вивчення кон'юнктури ринку, 1 завідувач складом, 1 головний менеджер, 1 директор. Рада універмагу вирішила встановити продавцеві оклад в розмірі 450 грн., старший продавець повинен отримувати в 1,5 рази більше продавця, менеджер по роботі з товарами — в 3 рази більше від продавця, заввідділом — на 30 грн. більше, ніж менеджер по роботі з товарами, менеджер з вивчення кон'юнктури ринку — в 2 рази більше від продавця, завскладом на 40 грн. більше від продавця, головний менеджер — в 4 рази більше від продавця, директор універмагу — на 20 грн. Більше від головного менеджера.

ЗАВДАННЯ 2. Скласти оптимальний план завантаження обладнання, яке переробляє зерно в муку, за критерієм максимиму прибутку при наявності обмежень на ресурси. Розробити постановку задачі і алгоритму її розв'язку. За допомогою програмних засобів МS Ехсеl розв'язати задачу на персональному комп'ютері проаналізувати результати і результуючу ознаку видати на друк.

Початкові дані. Підприємство споживчого товариства займається переробкою пшениці на борошно. Технологічне обладнання підприємства працює в дві зміни, тривалість якої 7 годин протягом 300 робочих днів в році.

В районі діяльності підприємства можна закупити лише 3200 т пшениці за ціною 150 дол. за 1 тону. На підприємстві можна запровадити 3 варіанти завантаження технологічного обладнання. При першому варіанті за 1 годину можна переробити 1 т пшениці і вихід борошна складає: вищого ґатунку 25%, 1 сорту — 20 %, 2 сорту — 45 %, висівок — 10 %. Застосування другого варіанту дозволяє переробити за 1 годину 0,6 т пшениці і вихід борошна

складає: вищого ґатунку 50%, 1 сорту — 27 %, 2 сорту — 13%, висівок — 10%. Застосування третього варіанту дозволяє переробити за 1 годину 0,3 т пшениці і вихід борошна складає: вищого ґатунку 63 %, 1 сорту — 18 %, 2

сорту — 9 %, висівок — 10 %. Ціна 1 т борошна вищого ґатунку складає 320 дол., 1 сорту — 260 дол., 2 сорту — 220 дол., висівок — 60 $.

Затрати на складування і тару при переробці 1 т пшениці за годину складають 25 дол., інші змінні затрати переробки 1 т пшениці — 13 дол. Прибуток, отриманий за 1 годину роботи обладнання визначається як різниця між виручкою від реалізації борошна (його вартістю) і змінними затратами, які складаються із затрат на закупівлю зерна, складування і тару та інших змінних витрат (собівартістю борошна).

 

ЗАВДАННЯ 3. Скласти оптимальний план товарообігу універмагу за критерієм максимуму прибутку. Розробити постановку задачі і алгоритму її розв'язку. За допомогою програмних засобів MS Excel розв'язати задачу на персональному комп'ютері проаналізувати результати і результуючу ознаку видати на друк.

Початкові дані. Універмаг реалізує товари декількох груп: А, В, С. Відомі нормативи затрат ресурсів в розрахунку на одиницю товару по кожний групі (табл. 28) і відповідні величини ресурсів.

Таблиця 28

ЗАТРАТИ РЕСУРСІВ НА РЕАЛІЗАЦІЮ ТОВАРІВ

Ресурси

Нормативи затрат ресурсів із продажу товарів

Обмежені обсяги ре­сурсів, bі

А

В

с

Робочий час, люд.-год.

А11 =0,1

а12= 0,2

А13 =0,4

b1= 1100

Площа торгових залів, м2

а21 = 0,05

а22 = 0,02

а23=0,02

b2 =120

Витрати обігу, грн.

А31 = 3

А32 = 1

А33 =2

bз=3000

Прибуток в розрахунку на одиницю товару, грн.

С1 = 3

С2 = 5

С3= 4

 

План продажу товарів, од.

Х1= ?

Х2= ?

Х3 = ?

 

Методичні вказівки до завдання 1

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

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

При розв'язку задачі 1 необхідно врахувати, що за основу береться оклад продавця, а всі інші обчислюються через нього — у скільки разів або на скільки більше, тобто кожний оклад є лінійною функцією від окладу продавця:

А*П + В,

де П — оклад продавця, А і В — коефіцієнти, які для кожної посади визначаються рішенням ради трудового колективу.

Так, із умови завдання видно, що коефіцієнти А і В приймають наступні значення:

А= 1,5        B=0

А = 3          B=0

А = 3          B=30

А = 2          B=0

А = 1,5       B=40

А = 4          B=0

A = 4          B=20

Задавши кількість людей на кожну посаду, можна скласти рівняння: N1 * (А1 * П + В1) + N2 * (А2 * П + В2) + ... + N8 * (А8 * П + В8), де N1 — кількість продавців, N2 — кількість старших продавців, А1...А8 і В1...В8 — коефіцієнти для кожної посади.

В цьому рівнянні нам відомі величини А1...А8 і В1...В8, а не відомі — П і N1…N8. Розв'язати (тобто знайти всі його розв'язки) таке рівняння відомими методами неможливо, але деякі його розв'язки можна знайти за допомогою програм Goal Seek (Підбір параметра) та Solver (Пошук рішень) МS Еxсеl. В ході розв'язку цієї задачі необхідно створити розрахункову таблицю сценарій та звіт.

Сценарій — це множина вхідних даних, які використовуються як єдиний набір значень робочого аркушу. При цьому кожний сценарій відрізняється набором початкових значень. В результаті розв'язку задачі може бути створений звіт, який містить зведення про початкові і підсумкові значення отриманого рішення.

1. Для розв'язку задачі, сформульованої в завданні 1, створіть таблицю такої форми (табл. 30):

Таблиця 30

 

A

B

C

D

E

F

G

H

1

Коефіцієнт А

Коефіцієнт В

Посада

Зарплата

Кількість працівників

Сумарна зарплата

 

Зарплата продавця

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3

 

 

Продавець

 

 

 

 

450,00

4

 

 

Ст. продавець

 

 

 

 

 

5

 

 

Менеджер по роботі з товарами

 

 

 

 

 

6

 

 

Менеджер вивчення кон'юктури ринку

 

 

 

 

 

7

 

 

Зав. складом

 

 

 

 

 

8

 

 

Гол. менеджер

 

 

 

 

 

9

 

 

Зав. відділом

 

 

 

 

 

10

 

 

Директор

 

 

 

 

 

11

 

 

 

 

 

 

 

 

12

Сумарний місячний фонд зарплати

 

 

 

 

  • Заповніть шапку таблиці.

  • Відведіть для кожної посади один рядок і занесіть в стовпчик С назви посад.

  • В стовпчиках А і В вкажіть коефіцієнти А і В, які відповідають кожній посаді.

  • В клітинку НЗ занесіть значення заробітної плати продавця (450) і встановіть для неї формат 0,00 — два знаки після коми.

2. В стовчику B обчисліть заробітну плату для кожної посади:

  • В клітинку D3 занесіть формулу =АЗ*$Н$3+В3.

  • Скопіюйте формулу із клітинки D3 на діапазон D4:D10.

3. В стовпчику Е вкажіть кількість працівників на посадах.

4. В стовпчику Р обчисліть заробітну плату всіх працівників, які
займають дану посаду:

  • В клітинку F3 занесіть формулу =D3*Е3 (зарплата * кількість працівників).

  • Скопіюйте формулу із клітинки F3 на діапазон F4:F10.

  • Встановіть для даних в стовпчиках D і F формат 0,00 —два знаки після коми.

5.Визначіть сумарний місячний фонд заробітної плати:

• Просумуйте дані в стовпчику F, використовуючи інструмент автосумування.

  • Перемістіть значення суми в клітинки F12 і зробіть до неї підпис «Сумарний місячний фонд заробітної плати».

  • Складіть штатний розпис. Внесіть зміни в зарплату продавця або міняйте кількість співробітників в клітинках ЕЗ:Е5 до того часу, поки отриманий сумарний місячний фонд заробітної плати не буде дорівнювати заданому 30000 грн. (в комірці F12 необхідно отримати значення приблизно 30000 грн.)

6. Складіть штатний розпис з використанням операції автоматизації розрахунків Goal Seek (Подбор параметра).

  • Виберіть з меню Тооls (Сервис) кнопку Goal Seek.

  • Вкажіть в полі Set Cell (Установить в ячейке) адресу цільової клітинки $F$12.

  • Вкажіть в полі То value (Значение) — 30 000.

  • Вкажіть в полі Ву changing cell (Изменяя ячейку) адресу клітинки із заробітною платою продавця $Н$3 і натисніть на клавішу ОК.

7. Збережіть таблицю в особистому каталозі під іменем яке задаєте самі.

  • Підготуйте таблицю до друку.

  • Виведіть відредаговану таблицю на друк.

 

Методичні вказівки до завдання 2

Підприємствам споживчої кооперації в перехідний період ринкової економіки все більше властиві функції переробки сільськогосподарської продукції, її заготівель і менше — функції торговельної діяльності. Це обумовлено тим, що ці галузі є високо-ліквідними, оскільки інвестиції вкладені в них швидко скуповуються. Тому розв'язок другого завдання є актуальним на даний момент часу.

Оптимальне планування полягає в пошуку найкращого варіанту плану із множини можливих. Для його реалізації виділяються ресурси, тому планування пов'язане з розподілом ресурсів. Найкращий розподіл ресурсів здійснюється при співставленні варіантів плану за вибраним критерієм оптимальності, за яким і визначається ступінь досягнення поставленої мети. Таким критерієм є прибуток. У зв'язку з цим оптимальним вважається такий план, який забезпечує максимальний прибуток (розв'язок задачі на ма­ксимум).

Всі економічні показники і чинники можна розділити на неке-
ровані(z1, z2, z3, …, zm) і керовані: (x1, x2, x3, …, xn, оптимальне значення яких ми і повинні знайти. На цій підставі цільову функцію в загальному виді можна записати так:

у = F{х1,х2,х3,...,xn;z1,z2, z3,…,zm) -> ехtr

Завдання полягає у визначенні такого оптимального завантаження обладнання, що переробляє зерно в муку, яке б забезпечило максимум прибутку     підприємства     згідно     з     цільовою     функцією     виду:

де і — індекс видів виробничих ресурсів (і = 1, 2,   т);

j - індекс виробництва продукції - j -го виду (муки, висівок);

xj— обсяг виробництва продукції ї-го виду (муки, висівок);

bjk наявність затраченого часу на виробництво одиниці продукції j-го при к-ому варіанті технологічного процесу;

В — максимальне значення ресурсу часу роботи технологічного обладнання;

aj — кількість виробничих ресурсів, затрачених за годину переробки продукції j-го виду;

Сj — ціна одиниці продукції j-го виду.

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

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

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

За четвертою умовою кількість тон пшениці, перероблюваної з допомогою кожного варіанту завантаження, є невід'ємною величиною.

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

Розв'язуючи друге завдання, необхідно створити дві таблиці і заповнити їх даними. Для цього необхідно:

1. Перейменувати Листі на «Дані» та створити на ньому таблицю за наступним зразком:

2. На аркуші Лист2 створити наступну таблицю:


В клітинку L6 занесіть формулу для обчислення змінних затрат, а саме вартості зерна, яке переробляється за 1 годину (при першому варіанті завантаження обладнання): F6Дані!$В$9. Цю формулу слід поширити на діапазон клітинок L6:L8 для двох інших варіантів завантаження обладнання. Аналогічно в клітинки М6 і N6 занесіть формули:

=F6*Дані!$В$15

=F6*Дані!$В$17 і розмножте їх на діапазон клітинок М6:М8 та N6:N8 відповідно.

Відмітивши діапазон клітинок L6:N6, скористайтеся інструментом автосумування. В результаті цього в клітинку О6 система занесе формулу Sum(L6:N6). Скопіюйте цю формулу на весь діапазон .O6:O8.

В клітинку G6 занесіть формулу, яка визначає вартість борошна вищого гатунку, одержаного від переробки зерна за 1 год: =F6*В6*Дані! $В$4 і розмножте її на діапазон G6:G8. Аналогічно побудуйте формули для визначення вартості борошна 1 сорту, 2 сорту і висівок в клітинки Н6, I6 та j6:

=F6*С6*Дані!$В$5

=F6*Б6*Дані!$В$6

=F6*Е6*Дані!$В$7

Формули, побудовані для першого варіанту завантаження обладнання далі потрібно розмножити на діапазон клітинок G6:G8, Н6:Н8, I6:I8, та J6:J8 відповідно.

Відмітивши діапазон клітинок G6:J6 і скориставшись інструментом автосумування, занесіть в клітинку К6 сумарну вартість муки, одержаної від переробки зерна за 1 годину при першому варіанті завантаження обладнання. Розмножте формулу з клітинки К6 на діапазон К6:К8.

В клітинку Q6 занесіть формулу =Р6*F6 і розмножте її на діапазон клітинок Q6:Q8.

В клітинку R6 занесіть формулу К6-О6, а в клітинку S6 — формулу R6/F6 Ці формули далі розмножте на діапазони клітинок R6:R8 та S6:S8 відповідно.

В клітину Т6 занесіть формулу =Q6*S6 і розмножте її на діапазон клітинок Т6:Т8. Таким чином, прибуток (клітинки Т6:Т8) залежить лише від одного параметра — кількості зерна, яка переробляєгься за тим чи іншим варіантом завантаження обладнання (клітинки Q6:Q8).

В клітинку Т9 можна занести формулу =МАКС(Т6:Т8), а в клітинку U6 — формулу =ЕСЛИ(Т6=Т9;" Оптимальне значення";"Не оптимальне"). Поширимо останню формулу на діапазон клітинок U6:U8. Тоді з варіантів завантаження обладнання, який забезпечує найбільший прибуток, буде відмічений справа в таблиці з поміткою «Оптимальне значення».

 

Знаходження оптимального значення прибутку за допомогою засобу Поиск решений

Виберіть команду меню Тооls —> Solver (Сервис —> Поиск решений). У полі Set Target cell (Установить целевую ячейку) вкажіть клітинку $Т$6.

У полі Ву changing cell (Изменяя ячейки) вкажіть діапазон клітинок, значення яких програма змінює для побудови оптимального значення (клітинки $Q$6). Після активізації цього поля за допомогою миші необхідно виділити клітинку Р6 або з клавіатури ввести її адресу.

Обмеження задачі задайте в полі Subjects to constaints (Ограничения) діалогового вікна Solver. В це поле вводять наступні обмеження:

$Q$6<=Дані!$В$11,

$Р$6<=Дані!$В$13,

$Q$6=іnt,

$Q$6>=0.

Щоб задати обмеження, скористайтеся кнопкою Аdd (Добавить), після чого з'явиться наступне діалогове вікно — Add Constraint (Добавление ограничений) з трьома полями.

У лівому полі, Cell reference (Ссылка на ячейку), цього вікна вказується адреса клітинки, вміст якої повинен відповідати одному із заданих типів обмежень. Тип обмежень задається в середньому полі.

Праве поле, Соnstraint (Ограничение), цього вікна призначене для введення значення обмеження або у вигляді константи, або у вигляді адреси клітинки, яка містить це значення. Діалогова клавіша Аdd використовується для переходу до введення наступного обмеження.

Закінчивши введення всіх параметрів задачі, натисніть на діалогову клавішу ОК.

Для внесення змін і вилучення обмежень використайте, відповідно, кнопки Сhange (Изменить) та Delete (Удалить) діалогового вікна програми Solver (Поиск решения). При натисненні у вікні Silver кнопки Орtion (Параметри поиска решения) можна задати параметри програми Solver.

Кнопкою Solve (Исполнить) запустіть процес пошуку розв'язку. Якщо оптимальне рішення буде знайдено, то обчислені значення будуть вставлені в таблицю і на екрані з'явиться діалогове вікно solver Results (Результати поиска решений) з інформацією про закінчення оптимізаційного процесу.

Однак, оптимізаційна задача не завжди має розв'язок. У такому випадку в діалоговому вікні замість повідомлень “Solver found a soution”, “All constraints and optimality conditions are satisfied”  (Решения найдено, Все ограничения и условия оптимальности вьшолненьї) буде повідомлення Поиск не может найти оптимальное решение.

Якщо рішення знайдено, то виберіть один із режимів Кеер Solver Solution (Сохранить найденное решение) або Restore Original Values (Восстановить исходное значение) і задайте видачу звітів, які потрібні для проведення аналізу оптимального рішення. Можна задати видачу звітів трьох типів: Аnswer (Результати), Sensitivity (Устойчивость), Limits (Пределы).

Збережіть таблицю в особистому каталозі під тим же іменем.

Повторіть процес оптимізації для другого та третього варіантів завантаження обладнання.

В другому варіанті цільова функція буде задаватися в клітинці Т7, а незалежний параметр — в клітинці Q7. Обмеження задайте таким чином:

$Q$7<=Дані!$В$11,

$Р$6<=Дані!8В$13,

$Q$7=іnt,

$Q$7>=0.

Для третього варіанту цільова функція предсталена клітинкою Т8, а незалежний параметр — клітинкою Q8. Обмеження задайте таким чином:

$Q$8<=Дані!$В$11,

$Р$8<=Дані!$В$13,

$Q$8=іnt,

$Q$8>=0.

 

Методичні вказівки до завдання 3

Планування товарообігу підприємствами споживчої кооперації є їх важливою функцією, оскільки від складеного плану товарообігу в багатьох випадках залежать результати торгово-господарської діяльності підприємств. За даними завдання 3 побудуємо математичну модель задачі:

3х1+5х2 +4х3 → mах;

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

Для застосування програми Solver побудуйте розрахункову схему, яка задає обмеження та цільову функцію.

1. Модель оптимізації товарообігу можна представити у вигляді трьох секцій (табл. 31):

  • секції управляючих змінних;

  • секції цільової функції;

  • секції функціональних обмежень.

Таблиця 31

МОДЕЛЬ ОПТИМІЗАЦІЇ ТОВАРООБІГУ

А

В

С

Б

Е

Цільова функція

Назва

Поточне значення

 

 

 

 

 

 

 

 

Змінні

Назва

Поточні значення

Межі

 

 

 

Макс.

Мін.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Функціональні обмеження

Назва

Поточні значення

Межі

 

 

Макс.

Мін.

=

 

 

 

 

 

Клітинка стовпчика В секції цільової функції називається цільовою клітинкою. Це клітинка, у якій відображається значення показника, що оптимізується. Цільова клітинка повинна містити формулу (або адресу клітинки з формулою), значення якої змінюється залежно від значень клітинок стовпчика В секції змінних. Програма Solver змінює значення клітинок секції змінних доти, доки у цільовій клітинці не з'явиться оптимальний результат, який задовольняє всім обмеженням.

 

Контрольні питання

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

2.   Перелічіть та стисло охарактеризуйте завдання АРМа пла-
нувальника.

3.   Які види початкової інформації використовуються в процесі
розв'язку задач за допомогою АРМа планувальника?

4.   Опишіть постановку основних задач планування торговельної
діяльності підприємств і організацій споживчої кооперації.

5.   Поясніть алгоритми і технологію автоматизації розрахунків плану
оптового товарообігу і його товарного забезпечення.

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

7.   Яка методика розрахунку плану фонду заробітної плати працівників
торговельного підприємства? Опишіть алгоритм та технологію
автоматизації розрахунків.

8.   Дайте характеристику пакетів прикладних програм, які ви-
користовуються для автоматизації розв'язку задач планування
торговельно-господарської діяльності підприємств.

З повагою ІЦ "KURSOVIKS"!