Статья:

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

Журнал: Научный журнал «Студенческий форум» выпуск №28(121)

Рубрика: Экономика

Выходные данные
Сотникова Д.Г. Решение задач оптимизации в Microsoft Excel // Студенческий форум: электрон. научн. журн. 2020. № 28(121). URL: https://nauchforum.ru/journal/stud/121/76676 (дата обращения: 29.03.2024).
Журнал опубликован
Мне нравится
на печатьскачать .pdfподелиться

Решение задач оптимизации в 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 прекрасно походит для решения задач оптимизации, упрощая ручные расчеты.

 

Список литературы:
1. Гарнаев А.Ю. Использование MSExcelи VBA в экономике и финансах / А.Ю. Гарнаев // – СПб: БХВ – Санкт Петербург 2000 г. – 336 с.
2. Лавренов С.М. Excel. Сборник примеров и задач / С.М. Лавренов // – М: Финансы и статистика 2003г. – 335 с.