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

Аннотация. В данной статье идет речь о простом способе решения задач оптимизации. Рассмотрены возможности Microsoft Excel в решении такого рода задач, определенные команды программы, а также приведен наглядный пример.

Ключевые слова: Microsoft Excel, задачи оптимизации.

Сегодня существует достаточно много программ, помогающих школьникам, студентам и преподавателям решать прикладные задачи из различных сфер за короткий промежуток времени. Одной из первых программ, завоевавшей авторитет среди рядовых пользователей в решении такого рода задач является  Microsoft Excel.

В наши дни Microsoft Excel является самой популярной программой для решения различного рода задач. Возможности этой программы позволяют находить эффективные решения задач в различных сферах жизни (финансы, экономика, математика, логика и др.) за короткое время.

Одними из таких задач являются задачи оптимизации, которые имеют огромное прикладное значение. Такие задачи возникают в самых различных разделах экономики, с их помощью можно отыскать некоторое оптимальное решение (минимизирующего или максимизирующего целевую функцию: прибыль, затраты и т.п.) в условиях ограничений (ресурсы, время и т.п.). Например, на текстильных предприятиях – минимизация расходов при формировании состава сырья, а на швейных производствах – оптимизация раскроя или минимизация расходов на перевозку готового товара по данным маршрутам [1, с. 17]. В Excel для решения задач оптимизации используются определенные команды [1, с. 67].

1. Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, обеспечивающие нужный результат.
2. Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – команда, рассчитывающая оптимальную величину, учитывая при этом все переменные и ограничения.
3. Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – команда выполняет анализ нескольких вариантов исходных значений, создает и оценивает наборы сценариев.

Ниже приведен пример решения задачи оптимизации в Excel при помощи команды поиск решения [2, с. 224].

Условие задачи: на заводе производится несколько видов йогурта: «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Найдите, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные и нормы расхода сырья уже занесены в таблицу в Microsoft Excel.

Рисунок 1. Данные из условия задачи и нормы расхода сырья

Нам необходимо составить «рабочую» таблицу. За переменные вводим неизвестное нам количество изделий. В столбец «Прибыль» вносим формулы: =200*B11, =250*В12, =300*В13.

Понимаем, что расход сырья ограничен (это ограничения). Норму расхода умножаем на количество сырья и в ячейки вносим формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»).

В ответе нам необходимо выдать максимально возможную прибыль (ячейка С14).

Рисунок 2. Рабочая таблица по условию задачи

Активизируем команду поиск решения и вносим параметры.

Рисунок 3. Поиск решения задачи при помощи команды

В конечном итоге, команда выдает решение.

Рисунок 4. Ответ задачи

Ответ: для получения максимальной прибыли заводу необходимо сконцентрироваться на выпуске йогуртов «1» и «3».

Как мы видим, привычная для нас программа Microsoft Excel прекрасно походит для решения задач оптимизации, упрощая ручные расчеты.