Завдання для олімпіади з ІКТ

 

Завдання: Оптимізація виробництва та аналіз продажів

1. Постановка задачі

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

  1. Оптимізувати виробництво (використовуючи Пошук рішення/Solver) для максимізації прибутку, враховуючи обмежені ресурси.

  2. Проаналізувати продажі за регіонами та місяцями (використовуючи Пошук підсумка/Subtotals).

2. Частина 1: Оптимізація виробництва (Пошук рішення/Solver)

$\rightarrow$ Мета: Максимізувати загальний прибуток.

Дані для електронної таблиці:

ABCDE
ПоказникПечиво "Класичне"Печиво "Шоколадне"Обмеження (Доступно)Використано
1. Кількість вироблених пачок (змінна)=B6=C6--
2. Витрати борошна на пачку (кг)0.20.15250=B2*B3+C2*C3
3. Витрати цукру на пачку (кг)0.10.1150=B2*B4+C2*C4
4. Прибуток на пачку (грн)1520--
5. Загальний прибуток (цільова функція)=B2*B5+C2*C5---

(Примітка: Припустимо, що дані розміщені у діапазоні A1:E6)

Кроки виконання (Пошук рішення):

  1. Введіть вихідні дані у таблицю (комірки A1:D5).

  2. Задайте змінні (комірки B2 та C2) — це кількість пачок, яку потрібно знайти. Введіть туди довільні початкові значення (наприклад, 100).

  3. Введіть формули для Використано (E3, E4) та Загального прибутку (B6).

    • $E3$ (Використано борошна): =B2*B3+C2*C3

    • $E4$ (Використано цукру): =B2*B4+C2*C4

    • $B6$ (Загальний прибуток): =B2*B5+C2*C5

  4. Активуйте надбудову "Пошук рішення" (Solver) (якщо вона ще не активована: Файл $\rightarrow$ Параметри $\rightarrow$ Надбудови $\rightarrow$ Надбудови Excel $\rightarrow$ Перейти $\rightarrow$ Встановити прапорець "Пошук рішення").

  5. Запустіть "Пошук рішення" (Дані $\rightarrow$ Аналіз $\rightarrow$ Пошук рішення або Solver).

  6. Налаштуйте параметри:

    • Установити цільову функцію: Вкажіть комірку $B6$ (Загальний прибуток).

    • До: Виберіть Максимум.

    • Змінюючи комірки змінних: Вкажіть діапазон $B2:C2$ (Кількість пачок "Класичне" та "Шоколадне").

    • Відповідно до обмежень: Додайте наступні обмеження:

      • $E3 \le D3$ (Використано борошна $\le$ Доступно борошна)

      • $E4 \le D4$ (Використано цукру $\le$ Доступно цукру)

      • $B2 \ge 0$ та $C2 \ge 0$ (Кількість не може бути від'ємною)

      • $B2$ та $C2$ мають бути цілими (Встановіть $B2:C2$ $\rightarrow$ $int$).

  7. Натисніть "Знайти рішення". Solver знайде оптимальні кількості $B2$ та $C2$, які максимізують $B6$.

3. Частина 2: Аналіз продажів (Пошук підсумка/Subtotals)

$\rightarrow$ Мета: Швидко отримати загальні суми продажів за регіонами та місяцями.

Дані для електронної таблиці (Вибірка):

ABCD
МісяцьРегіонТип печиваПродажі (грн)
СіченьСхідКласичне52000
СіченьЗахідШоколадне75000
ЛютийСхідКласичне58000
............
СіченьСхідШоколадне62000
ЛютийЗахідКласичне80000

(Примітка: Припустимо, що дані розміщені у діапазоні A10:D100)

Кроки виконання (Пошук підсумка):

  1. Створіть таблицю продажів із колонками: Місяць, Регіон, Тип печива, Продажі (грн).

  2. Сортування даних (ОБОВ'ЯЗКОВО):

    • Для коректної роботи Пошуку підсумка, дані повинні бути відсортовані за тими стовпцями, за якими ви хочете групувати.

    • Відсортуйте таблицю за двома рівнями: спочатку за "Регіон", потім за "Місяць". (Дані $\rightarrow$ Сортування $\rightarrow$ Додати рівень)

  3. Застосуйте "Пошук підсумка" (Дані $\rightarrow$ Структура $\rightarrow$ Проміжні підсумки або Subtotals):

    • Перший рівень підсумків (за Регіоном):

      • При кожній зміні у: Оберіть Регіон.

      • Операція: Оберіть Сума.

      • Додати підсумки до: Оберіть Продажі (грн).

      • Натисніть ОК.

    • Другий рівень підсумків (за Місяцем):

      • Повторно запустіть Проміжні підсумки.

      • При кожній зміні у: Оберіть Місяць.

      • Операція: Оберіть Сума.

      • Додати підсумки до: Оберіть Продажі (грн).

      • УВАГА: Зніміть прапорець "Замінити поточні підсумки" (щоб зберегти підсумки за регіонами).

      • Натисніть ОК.

  4. Аналіз результату: Тепер ваша таблиця матиме три рівні групування зліва:

    • Рівень 1: Загальний підсумок.

    • Рівень 2: Підсумки за Регіонами (наприклад, "Схід Підсумок", "Захід Підсумок").

    • Рівень 3: Підсумки за Місяцями (у межах кожного регіону).

4. Пояснення інструментів

Пошук рішення (Solver)

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

  • Використання в задачі: Дозволяє автоматично визначити найкращий план виробництва (скільки пачок кожного типу печива) для досягнення максимального прибутку, не перевищуючи ліміти на борошно та цукор.

Пошук підсумка (Subtotals)

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

  • Використання в задачі: Дозволяє швидко агрегувати дані — обчислити загальну суму продажів (або середнє, кількість) спочатку за Регіоном, а потім за Місяцем в межах кожного регіону, що спрощує аналіз даних.

Коментарі

Дописати коментар