Лабораторна робота на тему "Розв'язання задач з різних предметних галузей"

Лабораторна робота 4

Тема. Розв'язання задач з різних предметних галузей

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

Обладнання: комп’ютер, програмний засіб — табличний процесор.

Хід роботи

Під час роботи з комп’ютером дотримуйтесь правил безпеки.

Завдання 1: за допомогою надбудови Розв’язувач розв’язати таку транспортну задачу: 

Загальна умова транспортної задачі:

Є N пунктів виробництва і M пунктів споживання продукції. Вартість перевезення одиниці продукції з I-го пункту виробництва в J-й центр споживання Cij зазвичай дається в таблиці, де рядки — це пункти виробництва — певні фірми, а стовпці — пункти споживання. Крім того, в цій таблиці в I-му рядку дається обсяг виробництва I-ї фірми, а в J-му стовпці зазначений попит в J-му центрі споживання. Необхідно скласти план перевезень від фірм до пунктів споживання з мінімізацією сумарних транспортних витрат.

На рис. 1 наведена таблиця MS Excel із конкретними значеннями умови задачі. Із таблиці видно, що є 4 фірми виробництва і 4 пунктів споживання. У стовпці F вказано обсяг виробництва, а в рядку 7 граничний обсяг споживання. Діапазон В3 : Е6 містить значення вартості перевезень від кожної фірми до кожного пункту споживання.

Рис. 1. Таблиця із значеннями умови транспортної задачі

Вказівки до виконання

1. Уведіть у таблицю Excel дані умови.

2. Складіть математичну модель розв’язання задачі.

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

 16 (4 × 4 = 16).

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

Врахуйте дані задачі в обмеженнях:

• Загальна кількість перевезень від кожної фірми має бути меншою або дорівнювати відповідно 20, 30, 50 і 20.

• Загальна кількість перевезень до кожного пункту споживання має бути меншою або дорівнювати відповідно 30, 20, 60 і 15.

• Кількість перевезень — ціле число, яке більше або дорівнює 1.

1. У діапазон А8 : G13 додайте значення відповідно до рис. 2:

Рис. 2

1) Діапазон В9 : Е12 (на рисунку ці клітинки виділено блакитним кольором) призначений для оптимізаційних параметрів — в кожну клітинку введіть по 1-ці;

2) у клітинку F9 введіть формулу підрахунку вартості перевезень від фірми 1 до всіх споживачів =B9*B3+C9*C3+D9*D3+E9*E3;

3) скопіюйте формулу з клітинки F9 у F10 : F12;

4) у клітинку В13 уведіть формулу підрахунку перевезень до пункту споживання 1 від усіх фірм =B9*B3+B10*B4+B11*B5+B12*B6;

5) скопіюйте формулу з клітинки В13 у С13: Е13;

6) у клітинку F13 введіть формулу підрахунку суми F9 : F12.

2. Перейдіть до стрічки Дані,

3. Запустіть метод Розв’язувач (Виконайте команду СервисПоиск решений) 

4. У вікні пошуку рішення введіть посилання на клітинку з цільовою функцією $F$13.

5. Зазначте метод оптимізації — До мінімуму.

6. Укажіть діапазон клітинок із параметрами $B$9:$E$12.

7. Введіть обмеження на значення параметрів відповідно до рис. 3

Рис. 3

8. Активізуйте кнопку Розв’язувач.

9. Порівняйте результат  з рис. 4.

 Рис. 4 

Завдання 2. На Листі 2 створити таблицю „Аналіз персоналу”

Таблиця 1 ­– Аналіз персоналу

Порожні комірки таблиці заповнити розрахунковими даними.

Рядок «Всього» порахувати за допомогою Автосуми, стовпчик «Відсоток до підсумку, %» порахувати так:

План: Чисельність, план / Всього, план;

Факт: Чисельність, факт / Всього, факт.

Побудувати кругову діаграму „Відсоток чисельності працівників за фактом до підсумку ”та довільно відформатувати її.

Приклад діаграми наведений на рисунку 5.

Рисунок 5 - Кругова діаграма „Відсоток чисельності працівників за фактом до підсумку ”

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