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

Є три постачальники й чотири споживачі. Потужність постачальників і попит споживачів, а також витрати на перевезення одиниці вантажу для кожної пари «постачальник - споживач» зведені в таблицю постачань (таблиця 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) буде мінімальним.

Приклад завдання «Реалізація задач планування економічних процесів засобами пакета 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 зазначений оптимальний розподіл перевезень вантажів для кожної пари «постачальник - споживач».

Література

1. Спіцина Н. М., Шабельник Т.В. Інформатика та комп’ютерна техніка. Частина 1. [Текст] : навч. посіб. по базовій підготовці студ. рівня бакалавр і спеціаліст ден. і заоч. форм навчання / М-во освіти і науки України, Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, каф. інформ. систем і технологій упр.; Н. М. Спіцина, Т. В. Шабельник;. – Донецьк: [ДонНУЕТ], 2009.- 160 c.

2. Войтюшенко Н.М. Інформатика і комп’ютерна техніка: Навч. пос. з баз. підготовки для студ. екон. і техн. спеціальностей ден. і заоч. форм навчання/ Н.М. Войтюшенко, А.І. Остапець. – К.: Центр навчальної літератури, 2006 – 568 с.

3. Бизнес-анализ с помощью Microsoft Excel / Конрад Карлберг ; Пер. с англ. Л.Б. Тавровской, Под ред. В.В. Александрова . ─ М. : Вильямс, 2002 . ─ 446 с. : ил.

4. ВЕЙССКОПФ ДЖ. Excel 2000 : Базовый курс/Пер. с англ.. - Киев, М., СПб. : Век, Энтроп, Корона- Принт, 2000. - 400 с.

5. Гарнаев, А.Ю. Excel, VBA, Internet в экономике и финансах / Андрей Гарнаев . ─ СПб. и др : БХВ-Петербург, 2001 . ─ 795 с. : ил.

6. Excel : Cборник примеров и задач / С.М.Лавренев . ─ М. : Финансы и статистика, 2002 . ─ 334 с. ─ ( Диалог с компьютером ).

7. Інформатика та комп’ютерна техніка: Навчальний посібник. – К.: НМЦ “Укоопосвіта”, 2000 – 335 с.

8. Информатика для экономистов : Учебник для студ. вузов поспец. "Прикл. информатика (по областям) и др. экон. спец. ─ М. : ФОРУМ ; М. : ИНФРА-М, 2006 . ─ 446 с.

9. Microsoft Excel 2000 : Справочник / Под ред. Б.Карпова . ─ 2-е изд. ─ СПБ. : Питер, 2001 . ─ 510 с.

10. Microsoft Excel 2000 : Руководство для начинающих и опытных пользователей / Владислав Пузырев . ─ М. : Майор, 2001 . ─ 175 с.

11. Основи роботи в Microsoft Excel XP : Навч. посібник для вищ. навч. закладів / В.І. Юдін, В.С. Рижиков, В.В. Ровенська . ─ К. : Центр учбової літ., 2007 . ─ 271 с.

12. Работа с текстовой информацией. Microsoft Office Word 2003 / О.Б. Калугина, В.С. Люцарев ; Интернет-университет информационных технологий . ─ М. : Интернет-ун-т информ. технологий, 2005 . ─ 143 с.

13. Работа с текстовым процессором MS Word : Учеб. пособие для вузов / Г.А. Новиков, П.А. Новиков, М.В. Орлова, А.Н. Пылькин . ─ М. : Горячая линия - Телеком, 2005 . ─ 198 с.

14. Робота з Microsort Excel 2000 : Навч. посібник / О.Л. Вальдрат, Р.Б. Чаповська . ─ К. : Фітосоціоцентр, 2002 . ─ 185 с.

15. Статистические функции MS Excel в экономико-статистических расчетах : Учебник для вузов / А.Ю.Козлов, В.С.Мхитарян, В.Ф.Шишов ; Под ред. В.С.Мхитаряна . ─ М. : ЮНИТИ, 2003 . ─ 231 с.

1. Управление данными с помощью Microsoft Excel / Конрад Карлберг ; Пер. с англ. и ред. Н.Н.Селиной . ─ М. и др. : Вильямс, 2005 . ─ 446 с. ─ ( Бизнес-решения ) .

2. Экономическая информатика : Учебник для вузов / В.П. Косарев, Л.В. Еремин, О.В. Машникова и др. ; Под ред. В.П. Косарева . ─ 2-е изд, перераб. и доп. ─ М. : Финансы и статистика, 2005 . ─ 590 с.

3. Эффективная работа с Microsoft Excel 2000 / Марк Додж, Крейг Стинсон . ─ СПб. и др. : Питер, 2001 . ─ 1052 с. ─ ( Эффективная работа )

Варіант 1

ПІБ__________________________________ Група__________________

№	Завдання/питання	Відповіді
	Записати рівняння, використовуючи символи арифметичних операцій:
	Намалювати блок – схему рішення наступного математичного виразу: y =
	Які додаткові пристрої ви знаєте?
	Маєте ви власний комп’ютер?	Так	Ні
	Конфігурація вашого ПК?
	Скільки годин ви приділяєте роботі на ПК?
	Чим ви займаєтесь на ПК?
	Вивчали в ЗОШ предмет “Інформатика” ?	Так	Ні
	Вивчали язики програмування?
	Які програми ви вивчали самостійно?
	Користуєтесь Інтернет?	Так	Ні
	Маєте дома Інтернет?	Так	Ні
	Які програми вивчали в ЗОШ?
	Ви оцінюєте свої навички роботи з ПК:	відмінно	добре	задовільно	незадовільно
	Вам сподобається працювати на ПК?	Так	Ні
	Маєте дома принтер?	Так	Ні
	Маєте дома сканер?	Так	Ні

Варіант 2

ПІБ__________________________________ Група__________________

№	Завдання/питання	Відповіді
	Записати рівняння, використовуючи символи арифметичних операцій:
	Намалювати блок – схему рішення наступного математичного виразу: ax + x2 , якщо х ³ 3 y = y = ах3 , якщо х = 1,2,3…n bx3, якщо х < 3
	З яких частин складається базовий комплект персонального комп'ютера.
	Маєте ви власний комп’ютер?	Так	Ні
	Конфігурація вашого ПК?
	Скільки годин ви приділяєте роботі на ПК?
	Чим ви займаєтесь на ПК?
	Вивчали в ЗОШ предмет “Інформатика” ?	Так	Ні
	Вивчали язики програмування?
	Які програми ви вивчали самостійно?
	Користуєтесь Інтернет?	Так	Ні
	Маєте дома Інтернет?	Так	Ні
	Які програми вивчали в ЗОШ?
	Ви оцінюєте свої навички роботи з ПК:	відмінно	добре	задовільно	незадовільно
	Вам сподобається працювати на ПК?	Так	Ні
	Маєте дома принтер?
	Маєте дома сканер?

№ п/ч	Щоб виділити	Виконайте дії
1.	Слово	Двічі клацніть слово.
2.	Рисунок	Клацніть рисунок.
3.	Речення	Утримуючи натиснутою клавішу CTRL, клацніть на реченні.
4.	Рядок тексту	Перемістіть покажчик на смугу виділення*** перед реченням і клацніть кнопкою миші.
5.	Абзац	Перемістіть покажчик на смугу виділення*** перед абзацом, і двічі клацніть кнопкою миші. Інший спосіб: тричі клацніть абзац.
6.	Кілька абзаців	Перемістіть покажчик на смугу виділення*** перед абзацом, і двічі клацніть кнопкою миші, а потім перетягніть покажчик нагору чи вниз.
7.	Невеликий фрагмент тексту	Використовуйте перетаскування. Установіть курсор у початок фрагмента, натисніть клавішу SHIFT і утримуючи її натискайте відповідні клавіші управління курсором ­®¯
8.	Великий блок тексту	Клацніть початок фрагмента, прокрутіть документ так, щоб на екрані з'явився кінець фрагмента, а потім клацніть його, утримуючи натиснутою клавішу SHIFT. Інший спосіб. Встановіть курсор у початок фрагмента, натисніть клавішу SHIFT і утримуючи її натискайте відповідні клавіші управління курсором ­®¯
9.	Весь документ	Перемістіть покажчик на смугу виділення*** перед текстом, після чого тричі клацніть кнопкою миші.
10.	Колонтитули	У звичайному режимі виберіть Вид - Колонтитули. У режимі розмітки двічі клацніть неяскравий текст колонтитула. Перемістіть покажчик на смугу виділення*** перед колонтитулом, після чого тричі клацніть кнопкою миші.
11.	Вертикальний блок тексту (крім тексту усередині чарунки таблиці)	Утримуючи натиснутою клавішу ALT, перетягніть покажчик.

^***Смуга виділення - ліве поле документа. На смузі виділення покажчик миші приймає вид білої стрілки, спрямованої вправо.

Реакцією на неправильне введення формул є повідомлення Excel про помилки, тобто значення в чарунках, що починаються зі знаку #. Щоб легше було знаходити й усувати помилки у формулах, у Excel передбачена найпростіша діагностика помилок, а саме: помилки розділяються по категоріях, і кожній категорії відповідає своє повідомлення. Список можливих значень помилок:

† ##### – найбільш часте значення помилки в роботі в початківців, що з'являється, коли ширина чарунки недостатня для розміщення в ній числа, дати чи часу. Щоб усунути помилку, потрібно розширити комірку або змінити формат числа.

† #ИМЯ? – неможливість розпізнати ім'я, яке використовується. Це значення помилки виникає, коли неправильно зазначене ім'я об'єкта або мається посилання на ім'я, що було вилучено; коли невірно записана функція; коли при записі адрес замість латині використані кирилиця і т.ін.

† #ЗНАЧ! – спроба некоректного використання функції. Звичайною помилкою є невідповідність даних установленому формату, наприклад, замість числа або дати в аргументі записано текст. Це ж значення помилки буде з'являтися, коли для функції або оператора, що вимагають одного значення аргументу, записують декілька.

† #ЧИСЛО! – значення помилки, що означає проблему, зв'язану з представленням або з використанням чисел. Не виключено, що у функції з числовим аргументом використовується аргумент нечислового формату. Можливо також, що в чарунку уведена формула, що повертає занадто велике значення по модулю (понад 10³⁷).

† #ССЫЛКА! – означає наявність проблеми з інтерпретацією посилань, що маються у формулі. Можливо, що формула містить посилання на чарунку, що уже вилучена, чи посилання на чарунку, у яку скопійований вміст інших чарунок.

† #ДЕЛ/0! – спроба ділення на нуль. Така ситуація частіше виникає не через те, що в чарунку записано явне ділення на нуль (оператор /0), а як наслідок використання посилання на порожню чарунку або чарунку, що містить нульове значення.

† #ПУСТО! – значення помилки, що з'являється у випадку завдання в посиланні порожньої безлічі чарунок.

† #Н/Д – скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводиться в чарунки, щоб запобігти обчислення, що не можуть бути зроблені через відсутність даних.

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

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

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

ü Для часткової зміни вмісту чарунки можна, обравши її, натиснути клавішу <F2>, або клацнути мишею у рядку формул, або двічі клацнути безпосередньо на чарунці.

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

Відносні посилання використовуються в Excel за умовчанням при завданні посилання на чарунку або діапазон методом вказування. Відносне посилання сприймається програмою як вказівка маршруту (напрямки руху та відстані) до адресованої чарунки від чарунки, що містить формулу.

Абсолютне посилання задає абсолютні координати чарунки у робочому листі (щодо лівого верхнього кута таблиці). Можна наказати Excel інтерпретувати номери рядка та (або ) стовпчика як абсолютні шляхом введення символу долара ($) перед іменами рядка та (або) стовпчика. Наприклад, $A$7. При переміщенні або копіюванні формули абсолютне посилання на чарунку (або діапазон чарунок) змінене не буде, і на новому місці скопійована формула буде посилатися на ту ж саме чарунку (діапазон чарунок).

· Символ & проставляється перед буквою, яка може використовуватися в сполученні з клавішею Alt +[символ латинського алфавіту] для виконання дій, що відповідають командній кнопці