Линейное программирование в Excel Solver: Методология построения модели и экономическая интерпретация оптимизационных задач

Линейное программирование (ЛП), разработанное в середине XX века, до сих пор остается одним из наиболее применимых и эффективных инструментов в оперативном управлении и планировании. Согласно историческим данным, изобретенный Джорджем Данцигом в 1947 году симплекс-метод позволил Пентагону в годы холодной войны оптимизировать логистику и распределение ресурсов с такой точностью, которая была недостижима ранее. Сегодня этот же математический аппарат, воплощенный в современных IT-решениях, таких как надстройка «Поиск решения» (Solver) в MS Excel, дает возможность экономистам и менеджерам находить глобально оптимальные решения в задачах распределения капитала, логистики и производственного планирования, обеспечивая максимальную прибыль или минимизируя затраты.

Введение: Цели, Актуальность и Структура Работы

Актуальность методов оптимизации в современной экономике не вызывает сомнений. В условиях ограниченности ресурсов, высокой конкуренции и необходимости быстрого принятия решений, способность находить наиболее эффективный план действий становится ключевым конкурентным преимуществом, способным определить успех или неудачу предприятия. Математическое программирование, и в частности, линейное программирование (ЗЛП), служит мощным фундаментом для формализации управленческих задач.

Целью настоящей работы является комплексное освоение методологии ЗЛП, которое включает:

1. Изучение теоретических основ и форм представления ЗЛП.
2. Овладение навыками построения корректных экономико-математических моделей.
3. Получение практического опыта решения оптимизационных задач с помощью надстройки «Поиск решения» (Solver) в Microsoft Excel.
4. Проведение глубокого экономического анализа полученного оптимального плана, включая интерпретацию двойственных оценок и анализ чувствительности.

Структура курсовой работы соответствует поставленным целям: она начинается с фундаментального теоретического блока, переходит к методологии моделирования, затем — к пошаговому практическому решению в Excel и завершается детализированным экономическим анализом полученных результатов.

Теоретические основы задачи линейного программирования (ЗЛП)

Определение и основные компоненты математической модели ЗЛП

Линейное программирование (ЛП) является разделом математического программирования, который исследует задачи нахождения экстремума (максимума или минимума) линейной целевой функции при условии, что искомые переменные удовлетворяют системе линейных ограничений.

Математическая модель ЗЛП состоит из трех ключевых элементов:

1. Управляемые переменные ($x_j$): Это искомые величины (например, объемы производства, количество закупленного сырья), которые подлежат определению и влияют на результат. Важнейшее требование: все переменные должны быть неотрицательными ($x_j \ge 0$).
2. Целевая функция ($F(X)$): Линейная функция, которую необходимо оптимизировать (максимизировать прибыль или минимизировать затраты). Она имеет вид:

   F(X) = Σj=1n cj xj → extr

   где $c_j$ — это коэффициенты (например, прибыль или стоимость единицы продукции).

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

   Σj=1n aij xj ≤ bi или Σj=1n aij xj = bi или Σj=1n aij xj ≥ bi

   где $a_{ij}$ — нормы расхода ресурсов, а $b_i$ — запасы $i$-го ресурса.

Стандартная и Каноническая формы ЗЛП: Переход с помощью балансовых переменных

Для применения универсальных аналитических методов (таких как симплекс-метод) задача линейного программирования должна быть приведена к одной из двух стандартных форм.

1. Стандартная форма ЗЛП (С-ЗЛП):
В С-ЗЛП целевая функция подлежит максимизации (или минимизации), а все функциональные ограничения представлены в виде нестрогих неравенств (как правило, $\le$ для максимизации и $\ge$ для минимизации). Все переменные должны быть неотрицательными.

2. Каноническая форма ЗЛП (К-ЗЛП):
К-ЗЛП требует, чтобы все функциональные ограничения были представлены в виде строгих равенств, а все переменные были неотрицательными.

Переход от С-ЗЛП к К-ЗЛП:
Этот переход является фундаментальным для аналитических методов решения и осуществляется путем введения неотрицательных балансовых (дополнительных) переменных. Эти переменные имеют важный экономический смысл, отражая неиспользованный (избыточный) ресурс. Это ключевой момент для понимания, как именно математический аппарат учитывает реальные ограничения ресурсов.

• Для ограничения типа «Меньше или равно» ($\le$): Вводится дополнительная переменная $x_{n+i} \ge 0$, которая прибавляется к левой части. Она отражает неиспользованный остаток $i$-го ресурса.

  Σj=1n aij xj ≤ bi → Σj=1n aij xj + xn+i = bi

• Для ограничения типа «Больше или равно» ($\ge$): Вычитается дополнительная переменная $x_{n+i} \ge 0$, отражающая избыток, на который фактический расход превышает норму.

  Σj=1n aij xj ≥ bi → Σj=1n aij xj - x_{n+i} = b_i

Таким образом, общая запись К-ЗЛП для задачи максимизации с $m$ ограничениями и $n$ основными переменными выглядит так:

F(X) = Σj=1n cj xj → max

При ограничениях:

Σj=1n aij xj = bi, i = 1, &dots;, m

xj ≥ 0, j = 1, &dots;, n+m

Симплекс-метод как универсальный алгоритм решения ЗЛП

В отличие от графического метода, который применим только для задач с двумя переменными, симплекс-метод является универсальным алгоритмом для решения ЗЛП любой размерности. Он был разработан Джорджем Данцигом в 1947 году.

Сущность метода: Симплекс-метод — это итерационный процесс, который последовательно перебирает угловые точки многогранника допустимых решений. Каждая угловая точка соответствует допустимому базисному решению. Алгоритм работает следующим образом:

1. Начинается с исходного базисного решения.
2. На каждой итерации проверяется, можно ли улучшить значение целевой функции, перейдя в соседнюю угловую точку (путем замены одной базисной переменной на небазисную).
3. Если такое улучшение возможно, совершается переход.
4. Процесс прекращается, когда достигнута угловая точка, из которой невозможно совершить переход, который бы улучшил целевую функцию. Эта точка и является глобальным оптимальным решением.

Ключевое преимущество симплекс-метода состоит в том, что он гарантированно находит глобальный оптимум для линейных задач, поскольку многогранник допустимых решений является выпуклым, и локальные оптимумы в нем отсутствуют, кроме того, который является глобальным. Именно этот алгоритм лежит в основе работы специализированного метода «Симплекс-метод LP» в Excel Solver. Почему же отсутствие локальных оптимумов так важно для менеджера? Это означает, что любое найденное решение — это действительно наилучшее решение из всех возможных, а не просто «хорошее».

Построение экономико-математической модели на примере задачи распределения ресурсов

Этапы моделирования

Задача о распределении ресурсов — это классический пример ЗЛП в экономике, целью которого является нахождение оптимального плана производства, максимизирующего общую прибыль (или минимизирующего затраты) при заданных ограничениях на сырье, рабочее время, оборудование и т.д.

Процесс построения экономико-математической модели всегда проходит три четких этапа:

1. Определение управляемых переменных ($x_j$): Необходимо ясно сформулировать, какие именно величины мы ищем. В задаче о ресурсах $x_j$ — это объем производства $j$-го вида продукции. Обязательно фиксируется условие неотрицательности ($x_j \ge 0$).
2. Формулировка целевой функции ($F \to \max$): Целевая функция отражает экономический критерий оптимизации. Если речь идет о прибыли, то функция является суммой произведений прибыли от единицы $j$-го продукта ($c_j$) на количество этого продукта ($x_j$).

   F(X) = c1 x1 + c2 x2 + &dots; + cn xn → max

3. Описание системы ограничений: Системы неравенств, отражающие физические и экономические лимиты.

Формализация ограничений и экономический смысл коэффициентов $a_{ij}$

Система ограничений является сердцем модели, поскольку именно она определяет допустимую область решений. Каждое ограничение в задаче о ресурсах формулируется как неравенство типа «$\le$», поскольку суммарный расход ресурса не может превышать его наличный запас.

Σj=1n aij xj ≤ bi

Экономический смысл коэффициента $a_{ij}$ на практике:

Коэффициент $a_{ij}$ представляет собой норму расхода $i$-го ресурса на производство единицы $j$-го вида продукции. Это технологический или производственный норматив.

Компонент	Математическое обозначение	Экономический смысл
Переменная	$x_j$	Объем производства $j$-го продукта.
Коэффициент ЦФ	$c_j$	Прибыль (доход) от единицы $j$-го продукта.
Технологический коэффициент	$a_{ij}$	Расход $i$-го ресурса (например, кг стали, чел/час) на единицу $j$-го продукта.
Правая часть ограничения	$b_i$	Общий доступный запас $i$-го ресурса (лимит).

Например, если на изготовление одного стола ($x_1$) требуется 5 кг дерева ($a_{11}=5$), а всего на складе 1000 кг дерева ($b_1=1000$), то ограничение по дереву будет выглядеть так: 5x1 + 8x2 + &dots; ≤ 1000.

Практический алгоритм решения ЗЛП с использованием надстройки «Поиск решения» в MS Excel

Microsoft Excel, оснащенный надстройкой «Поиск решения» (Solver), представляет собой мощный и доступный инструмент для практического решения задач линейного программирования.

Активация надстройки и подготовка рабочего листа

1. Активация Solver:
Перед началом работы необходимо убедиться, что надстройка «Поиск решения» активирована. Если она отсутствует на вкладке «Данные», ее следует подключить:

• Перейти в меню «Файл» → «Параметры» → «Надстройки».
• В нижней части окна, в поле «Управление», выбрать «Надстройки Excel» и нажать «Перейти».
• Установить флажок напротив «Поиск решения» и нажать OK.

2. Подготовка рабочего листа:
Рабочий лист Excel должен быть структурирован таким образом, чтобы четко отражать математическую модель ЗЛП:

Название области	Содержание	Формула
Переменные решения	Пустые ячейки (диапазон $x_j$), в которые Solver запишет оптимальные значения.	—
Коэффициенты ЦФ	Ячейки, содержащие $c_j$ (прибыль от единицы).	—
Целевая функция	Ячейка, в которой будет рассчитано оптимальное значение $F_{max}$.	СУММПРОИЗВ(Коэф. ЦФ; Переменные решения)
Матрица A	Ячейки с нормами расхода ресурсов ($a_{ij}$).	—
Левые части ограничений	Ячейки, где рассчитывается фактический расход каждого ресурса.	СУММПРОИЗВ(Строка aij; Переменные решения)
Правые части ограничений	Ячейки с запасами ресурсов ($b_i$).	—

Использование функции СУММПРОИЗВ (SUMPRODUCT) критически важно, поскольку она позволяет компактно и корректно записать линейную функцию или левую часть ограничения, соответствующую Σ aij xj.

Настройка параметров «Поиска решения» и выбор метода

После подготовки таблицы запускается Solver (вкладка «Данные» → «Поиск решения»).

Параметр Solver	Действие и выбор	Обоснование
Оптимизировать целевую функцию	Указать адрес ячейки, содержащей формулу ЦФ.	Указывает, какой показатель оптимизируется.
До (Критерий)	Выбрать «Максимум» (Max) или «Минимум» (Min).	Соответствие цели задачи (прибыль/затраты).
Изменяя ячейки переменных	Указать диапазон ячеек $x_j$ (переменные решения).	Ячейки, которые Solver будет подбирать.
Ограничения	Последовательно добавить все ограничения, сопоставляя ячейки левых частей с правыми частями через нужный оператор ($\le, =, \ge$).	Фиксация допустимой области решений.
Параметры/Флажок	Установить флажок «Сделать переменные без ограничений неотрицательными».	Обеспечение выполнения условия $x_j \ge 0$.
Метод решения	Выбрать «Симплекс-метод LP» (Simplex LP).	Критически важный шаг. Этот метод специально разработан для линейных задач и гарантирует нахождение глобального оптимального решения.

Запуск расчета и получение аналитических отчетов

После корректной настройки всех параметров нажимается кнопка «Найти решение».

В диалоговом окне «Результаты поиска решения» необходимо:

1. Убедиться, что Solver нашел решение (статус: «Найдено решение…»).
2. Выбрать опцию «Сохранить найденное решение».
3. Обязательно отметить для создания:
   • Отчет «Результаты»: Содержит оптимальный план ($x_j^*$) и значение целевой функции ($F_{max}$).
   • Отчет «Устойчивость» (Анализ чувствительности): Содержит ключевую информацию для экономического анализа, включая двойственные оценки и интервалы устойчивости.

Экономический анализ оптимального плана и двойственных оценок

Теория двойственности и экономический смысл двойственных оценок (теневых цен)

Экономическая интерпретация результатов ЗЛП не ограничивается нахождением оптимального плана. Наиболее ценная информация для управленческих решений содержится в двойственных оценках, которые являются решением двойственной задачи линейного программирования (ДЗЛП). Двойственная задача (ДЗЛП) строится на основе исходной (прямой) задачи и позволяет оценить стоимость ресурсов, а переменные ДЗЛП ($y_i$) — это и есть двойственные оценки.

Первая теорема двойственности (Сильная двойственность) устанавливает фундаментальную связь: если прямая задача имеет оптимальное решение, то двойственная ей задача также имеет оптимальное решение, и их экстремальные значения совпадают: Fmax = Gmin.

Экономический смысл двойственных оценок ($y_i^*$):
Двойственная оценка $y_i^*$ (теневая цена $i$-го ресурса) показывает, на сколько денежных единиц увеличится оптимальное значение целевой функции ($F_{max}$) при увеличении запаса $i$-го ресурса ($b_i$) на одну единицу, при условии, что все остальные параметры остаются неизменными.

yi* = Δ Fmax / Δ bi

Это фактически предельный прирост прибыли, который может быть получен за счет дополнительной единицы ресурса. Разве не это является ключевой информацией для принятия решений о расширении производства или инвестировании в новые запасы?

Интерпретация двойственных оценок: дефицитные и недефицитные ресурсы

Интерпретация двойственных оценок является прямым руководством к действию для менеджера:

1. Дефицитный (ограничивающий) ресурс ($y_i^* > 0$):
   • Если двойственная оценка ресурса положительна, это означает, что данный ресурс используется полностью (в Отчете «Результаты» его запас равен расходу), и его нехватка ограничивает дальнейший рост прибыли.
   • Значение $y_i^*$ — это максимальная экономически целесообразная цена, которую предприятие может заплатить за приобретение дополнительной единицы этого ресурса, чтобы сохранить или увеличить прибыль. Если рыночная цена ресурса меньше $y_i^*$, его закупка выгодна, поскольку каждая дополнительная единица ресурса принесет больше прибыли, чем стоит.
2. Недефицитный (избыточный) ресурс ($y_i^* = 0$):
   • Нулевая двойственная оценка означает, что ресурс не используется полностью (в Отчете «Результаты» есть неиспользованный остаток).
   • Увеличение запаса такого ресурса не приведет к росту оптимальной прибыли, поскольку он уже находится в избытке.

Значение $y_i^*$	Статус ресурса	Управленческое решение
$y_i^* > 0$	Дефицитный, ограничивающий	Рассмотреть возможность покупки или аренды дополнительного ресурса (если цена ≤ $y_i^*$).
$y_i^* = 0$	Недефицитный, избыточный	Нет смысла увеличивать запас. Возможно, стоит перенаправить излишки в другие проекты или продать.

Анализ чувствительности (устойчивости) оптимального плана

Анализ чувствительности (отчет «Устойчивость») позволяет оценить, насколько стабилен найденный оптимальный план при изменении исходных данных. Это критически важно, так как в реальной жизни коэффициенты прибыли ($c_j$) и запасы ресурсов ($b_i$) подвержены колебаниям.

1. Устойчивость двойственных оценок (Интервалы для запасов $b_i$):

Отчет об устойчивости показывает «Допустимое увеличение» и «Допустимое уменьшение» для правых частей ограничений ($b_i$).

Эти интервалы определяют диапазон, в котором можно изменять запас ресурса $b_i$, не меняя при этом:

• Величину двойственной оценки ($y_i^*$).
• Структуру оптимального плана (то есть, набор производимых продуктов остается тем же, меняются только их объемы).

Если запас ресурса выходит за пределы этого интервала, то старая теневая цена перестает быть актуальной, и необходимо пересчитать задачу, поскольку изменится набор ограничивающих ресурсов.

2. Устойчивость оптимального плана (Интервалы для коэффициентов $c_j$):

Аналогичные интервалы рассчитываются для коэффициентов целевой функции ($c_j$, например, прибыль от единицы продукции).

Эти интервалы показывают, в каких пределах может меняться прибыль от $j$-го продукта, при этом сам оптимальный план ($x_j^*$) останется неизменным. Если фактическая прибыль от продукта $c_j$ выходит за пределы интервала, то данный продукт может стать более или менее выгодным, и Solver найдет новый оптимальный план с другим набором производимой продукции. Используя анализ чувствительности, менеджер может оценить риски и разработать более гибкую стратегию, основанную на устойчивости текущего оптимального решения.

Заключение

Настоящая курсовая работа позволила не только освоить теоретические основы задачи линейного программирования, но и интегрировать их с практическим инструментарием современного экономического анализа.

Достигнутые цели:

1. Были строго сформулированы математические модели ЗЛП в стандартной и канонической формах, а также показан процесс перехода между ними.
2. Разработана методология построения экономико-математической модели, на примере задачи распределения ресурсов, с четким описанием экономического смысла всех коэффициентов.
3. Освоен пошаговый алгоритм решения ЗЛП с помощью надстройки «Поиск решения» в MS Excel, с акцентом на критически важном выборе метода «Симплекс-метод LP» для гарантированного нахождения глобального оптимума.
4. Проведен глубокий экономический анализ результатов, основанный на теории двойственности. Была дана исчерпывающая интерпретация двойственных оценок ($y_i^*$) как теневых цен, позволяющих определить дефицитные ресурсы и принять обоснованные управленческие решения о целесообразности их дополнительной закупки.
5. Изучен анализ чувствительности, который позволяет оценить устойчивость оптимального плана к колебаниям запасов ресурсов и коэффициентов прибыли, обеспечивая основу для гибкого стратегического планирования.

Таким образом, работа подтвердила, что линейное программирование, реализованное через доступные IT-инструменты, является незаменимым средством для повышения эффективности управленческих решений в условиях ограниченных ресурсов.

Список использованной литературы

1. Багриновский К.А., Матюшок В.М. Экономико-математические методы и модели (микроэкономика): Учеб. пособие для вузов. Москва: Изд-во Российского университета дружбы народов, 2009. 183 с.
2. Гладких Б.А. В исследование операций. Линейное программирование: Учебное пособие. Томск: Изд-во НТЛ, 2009. URL: https://bsu.edu.az/files/books/gl_invest_oper_lp.pdf (дата обращения: 30.10.2025).
3. Исследование операций в экономике: Учеб. пособие для вузов / Под ред. Н.Ш. Кремера. Москва: Банки и биржи/ЮНИТИ, 2007. 407 с.
4. Карасев А.И., Кремер Н.Ш., Савельев Т.И. Математические методы и модели в планировании. Москва: Экономика, 2008. 240 с.
5. Новиков А. И. Экономико-математические методы и модели: учебник. Москва: Дашков и К0, 2021. URL: https://elib.gstu.by/xmlui/bitstream/handle/123456789/19447/978-5-394-04144-8.pdf (дата обращения: 30.10.2025).
6. Работа с надстройкой Solver (Поиск решения) [Электронный ресурс]. URL: http://www.ut.ee/~kalle/Excel/Solver.pdf (дата обращения: 30.10.2025).
7. Служба поддержки Майкрософт. Загрузка надстройки «Поиск решения» в Excel [Электронный ресурс]. URL: https://support.microsoft.com/ru-ru/office/%D0%B7%D0%B0%D0%B3%D1%80%D1%83%D0%B7%D0%BA%D0%B0-%D0%BD%D0%B0%D0%B4%D1%81%D1%82%D1%80%D0%BE%D0%B9%D0%BA%D0%B8-%D0%BF%D0%BE%D0%B8%D1%81%D0%BA-%D1%80%D0%B5%D1%88%D0%B5%D0%BD%D0%B8%D1%8F-%D0%B2-excel-36e65a0c-60d3-4558-a5cd-ec2900227187 (дата обращения: 30.10.2025).
8. Экономико-математические методы и модели [Электронный ресурс]. Электронная библиотека БГТУ. URL: https://elib.belstu.by/bitstream/2152/4996/1/270_ЭМММ.pdf (дата обращения: 30.10.2025).
9. Экономико-математические методы и модели [Электронный ресурс]. Университет Лобачевского. URL: http://www.unn.ru/pages/issues/uchpos/98-251.pdf (дата обращения: 30.10.2025).
10. ЭКОНОМИКО-МАТЕМАТИЧЕСКОЕ МОДЕЛИРОВАНИЕ [Электронный ресурс]. URL: http://booksite.ru/fulltext/1/001/004/182/files/page/11_1.htm (дата обращения: 30.10.2025).
11. ИССЛЕДОВАНИЕ ОПЕРАЦИЙ [Электронный ресурс]. Нижегородский государственный архитектурно-строительный университет. URL: https://www.nngasu.ru/files/izdat/el_izdaniya/2015/m_i_zhilina.pdf (дата обращения: 30.10.2025).
12. ЛИНЕЙНОЕ ПРОГРАММИРОВАНИЕ [Электронный ресурс]. Белорусский государственный университет. URL: http://elib.bsu.by/bitstream/123456789/108933/1/%d0%9b%d0%98%d0%9d%d0%95%d0%99%d0%9d%d0%9e%d0%95%20%d0%9f%d0%a0%d0%9e%d0%93%d0%a0%d0%90%d0%9c%d0%9c%d0%98%d0%a0%d0%9e%d0%92%d0%90%d0%9d%d0%98%d0%95.pdf (дата обращения: 30.10.2025).
13. Двойственные задачи линейного программирования [Электронный ресурс]. URL: https://ektu.kz/sites/default/files/lib/emim.pdf (дата обращения: 30.10.2025).