Решение задачи оптимизации диеты в Excel

Это классическая задача о диете (минимизация стоимости смеси). Нам нужно составить 1 тонну смеси из четырех компонентов так, чтобы удовлетворить требования по питательным веществам при минимальной цене. 1. Математическая модель задачи Пусть: \( x_1 \) — доля люцерновой муки в смеси; \( x_2 \) — доля сухой барды в смеси; \( x_3 \) — доля рыбной муки в смеси; \( x_4 \) — доля соевого шрота в смеси. Целевая функция (минимизация стоимости): \[ Z = 70x_1 + 90x_2 + 150x_3 + 100x_4 \to \min \] Система ограничений: 1. По весу (ровно 1 тонна): \[ x_1 + x_2 + x_3 + x_4 = 1 \] 2. По белку (не менее 35%): \[ 17x_1 + 25x_2 + 60x_3 + 45x_4 \ge 35 \] 3. По жирам (не менее 1,5%): \[ 2x_1 + 5x_2 + 7x_3 + 0,5x_4 \ge 1,5 \] 4. По клетчатке (не более 8%): \[ 25x_1 + 3x_2 + 1x_3 + 6,5x_4 \le 8 \] 5. Условие неотрицательности: \[ x_i \ge 0 \] 2. Реализация в Excel Для решения в Excel выполните следующие шаги: Шаг 1. Создайте таблицу данных: — В ячейки B1:E1 введите названия компонентов. — В ячейки B2:E2 введите их стоимости (70, 90, 150, 100). — В ячейки B3:E5 введите содержание веществ (белок, жиры, клетчатка). Шаг 2. Выделите ячейки для переменных: — Пусть ячейки B7:E7 будут искомыми долями компонентов (изначально пустые). Шаг 3. Настройте расчетные формулы: — Стоимость (Целевая): \( =СУММПРОИЗВ(B2:E2; B7:E7) \) — Итого белка: \( =СУММПРОИЗВ(B3:E3; B7:E7) \) — Итого жиров: \( =СУММПРОИЗВ(B4:E4; B7:E7) \) — Итого клетчатки: \( =СУММПРОИЗВ(B5:E5; B7:E7) \) — Итого вес: \( =СУММ(B7:E7) \) Шаг 4. Поиск решения: — Оптимизировать целевую ячейку (стоимость) до минимума. — Изменяя ячейки переменных: B7:E7. — Ограничения: - Итого вес \( = 1 \) - Итого белка \( \ge 35 \) - Итого жиров \( \ge 1,5 \) - Итого клетчатки \( \le 8 \) - B7:E7 \( \ge 0 \) — Метод решения: Симплекс-метод. 3. Результат решения После запуска Поиска решения получаются следующие значения: \( x_1 \approx 0,095 \) (Люцерновая мука) \( x_2 \approx 0,452 \) (Сухая барда) \( x_3 \approx 0,452 \) (Рыбная мука) \( x_4 = 0 \) (Соевый шрот не используется) Минимальная стоимость 1 тонны смеси составит примерно 115,24 руб. Ответ: Оптимальный состав смеси: люцерновая мука — 9,5%, сухая барда — 45,2%, рыбная мука — 45,2%. Соевый шрот в состав не входит. Минимальная стоимость смеси — 115,24 руб. за тонну.