Уведення формули 3 страница

Вартість 1 кг корму I і II відповідно дорівнює 4 і 6 грн.

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

Складемо оптимізаційну модель задачі.

Позначимо:

x₁, x₂ – кількість кормів I і II, що входять у денний раціон.

Тоді цей раціон буде включати (3* x₁+1* x₂) одиниць живильної речовини S₁, (1* x₁+2* x₂) одиниць речовин S₂, (1* x₁+6* x₂) одиниць живильної речовини S₃. У наслідок того, що кількість живильних речовин S₁, S₂, S₃ у раціоні мусить бути не менш, відповідно 9, 8 і 12 одиниць, то маємо систему обмежень:

, (1)

Змінні позитивні x₁³0, x₂³0.

Загальна вартість раціону F складе:

. (2)

Позначимо:

x_j (j=1,2,…,n) – число одиниць корму n-го виду;

b_i (i=1,2,…,m) – необхідний мінімум змісту в раціоні живильної речовини S_i;

a_ij – число одиниць живильної речовини S_ij в одиниці корму j-го виду;

c_j - вартість одиниці корму j - го виду.

Математична модель задачі складання раціону в загальній постановці прийме наступний вид.

Знайти такий раціон X=(x₁, x₂,…,x_j,…,x_n),задовольняючій системі:

і умові

,

при якому функція

приймає мінімальне значення.

Задача оптимізації використання потужностей (задача про завантаження устаткування, складання розкладу).

Підприємству заданий план виробництва продукції за часом і номенклатурою: потрібно за час Т випустити n₁, n₂,…,n_k одиниць продукції P₁,P₂,…,P_k. Продукція виробляється на верстатах S₁,S₂,…,S_m. Для кожного верстата відомі продуктивність a_ij і витрати b_ij на виготовлення продукції P_j на верстаті S_i в одиницю часу.

Необхідно скласти такий план роботи верстатів (тобто так розподілити випуск продукції між верстатами), щоб витрати на виробництво всієї продукції були мінімальні.

Позначимо x_ij – час, протягом якого верстат S_i – буде зайнятий виготовленням продукції P_j. У наслідок того, що час роботи кожного верстата обмежене й не перевищує Т, то справедливі обмеження:

(3)

Для реалізації плану випуску по номенклатурі необхідно, щоб виконувалися наступні обмеження:

(4)

При цьому,

(5)

Витрати на виробництво продукції виражаються функцією:

. (6)

Тоді математична модель задачі про використання потужностей у загальній постановці прийме наступний вид.

Знайти таке рішення X=(x₁₁,x₁₂,…,x_mk), задовольняюче системам (3-4) і умові (5), при якому функція (6) приймає мінімальне значення.

Задача оптимізації розкрою матеріалів.

Для виготовлення брусів довжиною 1,2 м, 3 м і 5 м у співвідношенні 2:1:3 на розпил надходять 195 колод довжиною 6 м. Визначити план розпилу, що забезпечує максимальне число комплектів. Можливі способи розпила колод, число одержуваних при цьому брусів наведені в табл. 6.3.

Таблиця 6.3 – Умовні дані задачі оптимізації розкрою матеріалів

Спосіб розпила	Число одержуваних брусів довжиною, м
1,2
		*	*
			*
	*		*
	*	*

Складемо оптимізаційну модель задачі.

Позначимо:

x_i – число колод, розпилених i - м способом;

x – число комплектів брусів.

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

при обмеженнях:

Сформулюємо математичну модель задачі про розкрій матеріалів у загальній постановці:

Нехай кожна одиниця j-го матеріалу (j=1,2,…,m) може бути розкроєна n різними способами, причому використання i-го способу (i=1,2,…,n) дає a_ijk одиниць k-го виробу (k=1,2,…,l), а запас j-го матеріалу дорівнює a_j одиниць. Позначимо x_ij – число одиниць j-го матеріалу, що розкроюється i-м способом.

Необхідно знайти таке рішення X=(x₁₁, x₁₂,…,x_nm), задовольняюче системі:

і умові x_ij ≥0, при якому функція F=x приймає максимальне значення.

Задача оптимізації транспортних витрат.

Є три постачальники й чотири споживачі. Потужність постачальників і попит споживачів, а також витрати на перевезення одиниці вантажу для кожної пари «постачальник - споживач» зведені в таблицю постачань (таблиця 4).

Таблиця 6.4 – Таблиця постачань

Постачальники	Потужності постачальників	Споживачі і їхній попит

У лівому верхньому куті довільної (i,j) клітки є коефіцієнт витрат – витрати на перевезення одиниці вантажу від i -го постачальника до j-го споживача.

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

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

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

Очевидно, що обсяг перевезеного вантажу не може бути негативним, тому варто ввести обмеження не заперечності змінних:

x_ij ≥0.

Сумарні витрати F на перевезення виражаються через коефіцієнти витрат у такий спосіб:

Для математичної постановки транспортної задачі в загальній постановці позначимо через с_ij коефіцієнти витрат, через M_i – потужності постачальників, через N_j – потужності споживачів, (i=1,2,…,m), (j=1,2,…,n), m –число постачальників, n – число споживачів. Тоді система обмежень прийме вид:

(7)

Система (7) містить у собі рівняння балансу по рядках і по стовпцях.

При цьому сумарна потужність постачальників дорівнює сумарної потужності споживачів, тобто

Цільова функція в цьому випадку наступна:

(8)

Таким чином, на множині позитивних рішень системи обмежень (7) необхідно знайти таке рішення, при якому значення цільової функції (8) буде мінімальним.

6.8.2. Приклад завдання «Реалізація задач планування економічних процесів засобами пакета Microsoft Excel»

Для реалізації побудованих оптимізаційних задач планування економічних процесів в Microsoft Excel існує надбудова «Поиск решения».

«Поиск решения» доставляє не заздалегідь відомий конкретний результат для цільової функції, а відшукує оптимальне (мінімальне або максимальне) з можливих рішення. Для складних задач «Поиск решения» може генерувати множину різних рішень. Шаблон задач планування економічних процесів, для рішення яких можна скористатися надбудовою, повинен мати ряд загальних властивостей:

1. Існує єдиний цільовий осередок, що містить формулу.

2. Формула в цільовому осередку містить посилання (прямі або непрямі) на ряд змінюваних осередків.

3. Може бути задана деяка кількість обмежень.

Прокоментуємо рішення задачі оптимізації управління ресурсами підприємства. Першою дією необхідно ввести вхідні дані. Уведемо на робочий аркуш вхідну таблицю з даними як це показано на рис. 6.5.

Для формування обмежень задачі в осередок Е5 уводимо функцію: СУММПРОИЗВ (C10:D10;C5:D5).

Виклик функції здійснюється наступними командами:

Вставка – Функция – Математические – СУММПРОИЗВ - Массив 1 (C10:D10) (невідомі значення плану виробництва) – Массив 2 (C5:D5) (ресурси одиниці продуцкії). Копіюємо функцію в осередки С6:С8 за допомогою маркеру копіювання.

В осередок С11 уводимо цільову функцію СУММПРОИЗВ (C10:D10;C9:D9).

Перейдемо тепер до постановки задачі для надбудови «Поиск решения». Для розв'язуваної задачі цільовою функцією буде функція в осередку $С$11, змінюваними даними - діапазон $С$10:$D$10, що містить число видів продукції, діапазон $Е$5:$Е$8 - використовується для визначення обмежень задачі.

Для звертання до надбудови «Поиск решения» використовується команда меню Сервис. Але може бути так, що команда Поиск решения в цьому меню відсутня. Тоді необхідно виконати наступну команду: Сервис / Надстройки, навпроти Поиск решения поставити галочку. Після активації команди Поиск решения, необхідно заповнити вікно діалогу за зразком (рис.6.6).

Рисунок 6.5. Шаблон рішення задачі оптимізації управління ресурсами

Установити цільовий осередок $С$11 рівним максимальному значенню. Змінюючи осередки $С$10:$D$10. Для ведення обмежень потрібно натиснути на кнопку Добавить.

1. Обмеження по ресурсах:

Посилання на осередок: = $Е$5:$Е$8, вид обмеження: <=, Обмеження: посилання на осередок: = $В$5: $В$8. Добавить.

2. Обмеження на не заперечність змінних:

Посилання на осередок: = $С$10:$D$10, вид обмеження: >=, Обмеження 0. Добавить.

3. Обмеження на цілісність змінних:

Посилання на осередок: = $С$10:$D$10, вид обмеження: цел. OК.

Рисунок 6.6. Вікно діалогу Поиск решения

Після натискання по кнопці Выполнить надбудова «Поиск решения» приступає до ітерацій, після обчислень відкриває діалогове вікно Результаты поиска решения (рис.6.7), у якому виводиться повідомлення про рішення задачі.

Рисунок 6.7. Результати пошуку рішення

У результаті рішення задачі можна зробити наступний висновок. Максимальний прибуток становитиме 24, продукції типу P₁ необхідно виробляти 6, продукції типу P₂ – 4.

Прокоментуємо рішення задачі оптимізації транспортних витрат. Представимо дані для рішення задачі в матричній формі. Формується матриця 3:4. Шаблон подання даних для реалізації задачі представлений на рис. 6.8.

Рисунок 6.8. Шаблон з вхідними даними для рішення задачі оптимізації транспортних витрат

Уводимо в діапазон B2:E4 коефіцієнти витрат на перевезення, у діапазон B5:E5 уводимо потреби у вантажах, у діапазон F2:F4 уводимо потужності постачальників.

В осередок F5 уводимо формулу (=СУММ(F2:F4)) для перевірки типу транспортної задачі. Якщо задача закрита, то сума потужностей постачальників дорівнює сумі попиту споживачів. Якщо балансу не дотримується, то необхідно додати фіктивного постачальника або споживача, як коефіцієнти витрат у доданому стовпці або рядка вводиться значення - 0.

Для формування шаблона рішення задачі необхідно ввести наступні розрахункові формули.

Уводимо в осередок В10 формулу =СУММ(В7:В9), в осередки C10:E10 копіюємо дану формулу методом протягання (у такий спосіб сформоване обмеження з попиту споживачів).

Уводимо в осередок F7 формулу =СУММ(B7:E7), в осередки F8:F9 копіюємо дану формулу методом протягання (у такий спосіб сформоване обмеження по потужностям постачальників).

Уводимо в осередок В11 формулу для розрахунку значення цільової функції (=СУММПРОИЗВ(В2:Е4;В7:Е9)).

Далі звертаємося до надбудови «Поиск решения». Заповнюємо вікно діалогу як показане на рис. 6.9.

Рисунок 6.9. Вікно діалогу Поиск решения

Установлюємо цільовий осередок $B$11 рівним мінімальному значенню. Змінюючи осередки $B$7:$E$9. Далі приступаємо до уведення обмежень, натискаючи кнопку Добавить.

1. Обмеження з попиту:

Посилання на осередок: =$B$10:$E$10, вид обмеження: =, Обмеження $B$5:$E$5. Добавить.

2. Обмеження на цілісність змінних:

Посилання на осередок: =$B$7:$E$9, вид обмеження: = цілий. Добавить.

3. Обмеження на не заперечність змінних:

Посилання на осередок: =$B$7:$E$7, вид обмеження: >=, Обмеження 0. Добавить.

4. Обмеження по потужностях постачальників:

Посилання на осередок: =$F$2:$F$4, вид обмеження: >=, Обмеження $F$7:$F$9. ОК.

Оптимальне рішення отримано після натискання по кнопці Выполнить. На рис. 4 у діапазоні B7:E9 зазначений оптимальний розподіл перевезень вантажів для кожної пари «постачальник - споживач».

Прокоментуємо рішення задачі оптимізації графіка роботи персоналу.

Відомо, що кожний зі співробітників повинен мати п'ять робочих днів на тиждень і два вихідних підряд.

При цьому існують деякі вимоги до мінімальної кількості працюючих співробітників для кожного із днів тижня: понеділок - 17, вівторок - 13, середа - 14, четвер - 15, п'ятниця - 18, субота - 24, неділя - 22. Весь персонал має заробітну плату рівну 60 грн. на день. Необхідно скласти графік роботи персоналу, наданих їм вихідних, так щоб фонд тижневої заробітної плати був мінімальним.

Представимо дані для рішення задачі в матричній формі. Формується матриця 7:7, у якій одиницями відзначені робочі дні, нулями - вихідні. Шаблон подання даних для реалізації задачі представлений на рис. 6. 10

Рисунок 6.10. Шаблон з вхідними даними для рішення задачі оптимізації графіка роботи персоналу

У діапазоні B7:B13 уведений список вихідних днів, у діапазоні D7:D13 - кількість співробітників, у діапазоні F7:L13 - матриця вихідних і робочих днів. У діапазон F17:L17 вводимо необхідну кількість робітників. В осередок D19 вводимо денну заробітну плату.

Помістимо в осередок D15 формулу загальної кількості співробітників (=СУММ(D7:D13)). Далі введемо формули для працюючих у відповідний день тижня. В осередок F15 уводимо формулу - =СУММПРОИЗВ(F7:F13;$D$7:$D$13). Потім копіюємо дану формулу методом протягання в діапазон G15:L15. Для розрахунку загальної тижневої заробітної плати в осередок D20 уведемо формулу =D15*D19.

Потім звертаємося до команди меню Сервис – Поиск решения. Заповнюємо вікно діалогу як показано на рис. 6.11.

Установлюємо цільовий осередок D20 рівним мінімальному значенню. Змінюючи осередки D7:D13. Далі преступаємо до уведення обмежень. Для уведення обмежень необхідно натиснути кнопку Добавить.

Рисунок 6.11. Вікно діалогу «Поиск решения» задачі оптимізації графіка роботи персоналу

Водимо обмеження (рис. 6.12):

Посилання на осередок: =$D7$7:$D$13, вид обмеження: = целое;

Посилання на осередок: =$D$7:$D$13, вид обмеження: >=, Обмеження 0;

Посилання на осередок: =$F$15:$L$15, вид обмеження: >=, Обмеження $F$17:$L$17.

Рисунок 6.12. Діалогове вікно додавання обмеження

Оптимальне рішення отримано після натискання по кнопці Выполнить. На рис. 6.13 у діапазоні D7:D13 зазначений оптимальний розподіл вихідних.

Рисунок 6.13. Результат рішення задачі оптимізації графіка роботи персоналу

6.9. Контрольні питання за темою “Табличний процесор MS Exсel”

1. Табличний процесор MS Excel. Основні поняття.

2. Що таке адреса чарунки? Де відображається адреса активної чарунки?

3. Маркер заповнення. Створення автосписків. Автозаповнення.

4. Робота з листами. Переміщення по листу.

5. Поняття діапазону. Види діапазонів? Виділення діапазонів. Виділення несуміжних діапазонів.

6. Типи даних у чарунках. Як вирівнюються різні типи даних у чарунках?

7. Форматування чарунок. Редагування вмісту чарунок.

8. Робота з формулами. Діагностика помилок у формулах. Типи посилань у формулах. Як вивести таблицю у режимі формул?

9. Діаграми. Створення діаграм.

10. Форматування діаграм. Як додати легенду до створеної діаграми? Як змінити вид діаграми?

11. Що таке зведена таблиця? Для чого потрібні зведені таблиці? Створення зведених таблиць. Як вибрати математичну операцію для поля, що обчислюється? Як відновити дані в зведеній таблиці?

12. Як побудувати діаграму по зведеній таблиці? Як змінити вид діаграми?

13. Функція. Види функцій. Формати логічних функцій.

14. Знайдіть у формулі помилки =ЕСЛИ((В3 “долар;S4*5,05;S4*6,2

15. Друк таблиць. Зміна орієнтації таблиці. Як центрувати таблицю для друку. Як додати колонтитули.

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

17. Які загальні властивості притаманні оптимізаційним моделям задач економічного планування?

18. За якими критеріями вирішуються задачі економічного планування?

19. Для чого формується шаблон рішення задач економічного планування?

20. За допомогою якого інструменту Microsoft Excel можна вирішити задачі економічного планування?

21. Які дії необхідно виконати для активації надбудови «Поиск решения»?

22. Які властивості мусить мати шаблон задач планування економічних процесів?

23. Яку посилання мусить мати цільова чарунка осередок у надбудові «Поиск решения»?

24. Які типи обмежень реалізує надбудова «Поиск решения»?

25. Що означають змінювані осередки у надбудові «Поиск решения»?

26. Яким позиціям у надбудові «Поиск решения» може дорівнювати цільова чарунка?

Дата добавления: 2014-11-06