Практична робота на тему Застосування МS Еxcel для рішення фахових завдань. Використання засобів оптимізації, НУДПСУ

 Практична робота

Тема. Застосування МS Еxcel для рішення фахових завдань.Використання засобів оптимізації

Мета.  Ознайомитися з технологіями розв’язування оптимізаційних задач: створенням оптимізаційної моделі, застосуванням засобу «Поиск решения», виконанням аналізу результату на прикладі розв’язування практичної задачі:

Є підприємство, яке спеціалізується на закупівлі та продажу через власну мережу магазинів товарів аудіо- та відеотехніки. Підприємство має три склади, які розташовані в Києві, Харкові та Львові. Замовлення на перевезення товарів надходять з 6 роздрібних магазинів, розташованих по всій території України.

Скласти оптимальний план перевезення товарів, дотримання якого б задовольнило потреби магазинів в товарах і зробило б витрати підприємства на  перевезення товарів мінімальними.

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

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

 Технологія виконання роботи

1. Викликати табличний процесор MS Excel і створити нову книгу у файлі з іменем Завдання10_<Власне прізвище>.xls.

2. Назвіть перший робочий аркуш Пошук рішення.

3. До комірки C1 введіть заголовок таблиці «Таблиця вартості перевезення одиниці товару» і відцентруйте його відносно стовпчиків С:F за допомогою кнопки Объединить и поместить в центре панелі форматування. До комірок D2:F2 введіть відповідно текст «Львів», «Київ», «Харків».

4. До комірки C3 введіть текст «Магазин №1» і використовуючи засіб автозаповнення введіть до комірок C4:C8 назви магазинів відповідно «Магазин №2» – «Магазин №6».

5. В області D3:F8 введіть таблицю вартості перевезень.

6. Встановіть для комірок D3:F8 грошовий формат. Для цього виділіть ці комірки та з їх контекстного меню оберіть команду Формат ячеек, вкладка Число, числовий формат — Денежный. Встановіть кількість десяткових знаків «нуль», та оберіть позначення «грн.».

7. В комірку С10 введіть текст «Потрібно товарів» таким чином, щоб він розташувався у двох рядках. Для цього з контекстного меню комірки С10 оберіть команду Формат ячеек, перейдіть до вкладки Выравнивание і включіть опцію Переносить по словам.

8. Схожим чином введіть до комірки G10 текст «Доставлено товарів».

9. Об’єднайте комірки D10:F10 і вирівняйте вміст комірки по центру як по вертикалі, так і по горизонталі. Введіть в комірку D10 текст «Кількість перевезеного товару».

10. Скопіюйте вміст комірок D2:F2 і вставте його до комірок D11:F11. Аналогічно скопіюйте вміст комірок C3:C8 і вставте його до комірок B12:B17. В комірку B18 введіть текст «Всього».

11. В комірках C12:C17 побудуйте таблицю потреб в товарах для кожного магазину.

12. Значення комірок D12:F17 будуть змінюватися в результаті виконання процедури пошуку рішення. Встановіть у всіх цих комірках початкове значення 25. Для цього виділіть комірки D12:F17, введіть в рядок формул число 25 і, утримуючи клавішу <Ctrl>, натисніть клавішу <Enter>.

13. До комірки G12 введіть формулу =СУММ(D12:F12), яка підраховує кількість доставлених товарів до магазину №1. Використовуючи засіб автозаповнення розповсюдьте її на комірки G13:G17 (тобто для магазинів №2 – №6).

14. В комірці C18 підрахуйте сумарну кількість необхідних товарів за допомогою формули =СУММ(С12:С17) і, використовуючи засіб автозаповнення, розповсюдьте її на комірки D18:G18.

15. Об’єднайте комірки B20, C20 та введіть до комірки B20 текст «Товарів на складі». В комірки D20:F20 введіть дані з таблиці «Запаси товарів на складах».

16. До комірки D21 введіть формулу =D20–D18, яка обчислює кількість товарів, що залишилася на складі в м. Львові. Заповніть комірки E21 та F21 аналогічними формулами використовуючи автозаповнення.

17. До комірки D24 введіть формулу =СУММПРОИЗВ(D3:D8; D12:D17), яка підраховує вартість перевезень товарів зі складу у м. Львові до всіх магазинів. За допомогою автозаповнення введіть аналогічні формули до комірок E24 та F24. В комірці G24 введіть формулу =СУММ(D24:F24), яка обчислює сумарну вартість перевезень.

18. Запустіть процедуру Поиск решения.

19. Заповніть параметри Поиск решения: цільова комірка $G$24, задача мінімізації, змінюючи комірки $D$12:$F$17. Створіть такі обмеження: всі потреби магазинів в товарах повинні бути виконані, тобто кількість потрібного товару для кожного магазину (комірки C12:C17) повинна дорівнювати кількості перевезеного товару (комірки G12:G17); не можна перевозити від’ємну кількість товарів, тобто значення в комірках, що змінюються (D12:F17) не можуть бути від’ємними; кількість запасів товарів на кожному складі не може бути від’ємною (комірки D21:F21).

20. Розв’яжіть наведену транспортну задачу, натиснувши кнопку Выполнить в діалоговому вікні Поиск решения.

21. Підрахуйте вартість перевезень за старим планом перевезень. Для цього в комірках C26:F33 побудуйте таблицю «Старий план перевезень». При цьому числові дані таблиці розмістіть в області D28:F33. Для знаходження вартості перевезень за старим планом в комірку G35 введіть формулу =СУММПРОИЗВ(D28:F33;D3:F8).

22. Порівняйте два розглянутих плани перевезень і визначте економічний ефект від впровадження нового плану в комірці G37 за формулою =G35-G24.

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

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

1. Для розв’язку задач якого типу використовують Поиск решения? Наведіть приклади.

2. Як інсталювати Поиск решения?

3. Як запустити Поиск решения?

4. Що таке цільова комірка?

5. Що таке змінні параметри?

6. Як ввести обмеження задачі?

7. Які є види обмежень?

8. Як можна контролювати точність пошуку розв’язку?

9. Що таке моделі оптимізації і як їх використовувати?

10. Які є види звітів за результатами пошуку?

З повагою ІЦ “KURSOVIKS”!