Глубокий Анализ Чувствительности в Линейном Программировании: Методическое Руководство по Экономической Интерпретации Отчета MS Excel Solver

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

Целью данного методического руководства является не только демонстрация строгого математического моделирования и численного решения двух типов задач линейного программирования (максимизация прибыли и минимизация затрат) с использованием MS Excel Solver, но и, что особенно важно, проведение глубокого и всестороннего анализа чувствительности. Мы детально разберем экономическую интерпретацию каждого показателя Отчета по устойчивости (Sensitivity Report), предоставляемого Solver, и покажем, как эти данные могут быть использованы для принятия стратегических решений. Работа построена как академический отчет, призванный обеспечить студентов технических, экономических и управленческих вузов исчерпывающими знаниями и практическими навыками в области исследования операций.

Теоретические Основы Линейного Программирования и Двойственности

Мир экономики полон компромиссов. Производитель стремится максимизировать прибыль, имея ограниченные запасы сырья; животновод – минимизировать затраты на корм, обеспечивая при этом минимальный набор питательных веществ. За каждой такой ситуацией стоит математическая модель, известная как задача линейного программирования. Но не менее, а порой и более важно, чем исходная «прямая» задача, оказывается ее «теневая» сторона – двойственная задача, которая предлагает совершенно иной, но взаимосвязанный взгляд на проблему, открывая глубинные экономические взаимосвязи.

Построение Прямой и Двойственной Экономико-Математической Модели

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

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

Прямая задача (Максимизация):

Определить вектор переменных решения $X = (x_1, x_2, \ldots, x_n)^{\text{T}}$, где $x_j$ — количество производимого $j$-го вида продукции.

• Целевая функция: Максимизировать $Z = c_1 x_1 + c_2 x_2 + \ldots + c_n x_n = \sum_{j=1}^{n} c_j x_j \rightarrow \text{max}$
  где $c_j$ — прибыль от единицы $j$-го вида продукции.
• Ограничения:

  $a_{11} x_1 + a_{12} x_2 + \ldots + a_{1n} x_n \le b_1$

  $a_{21} x_1 + a_{22} x_2 + \ldots + a_{2n} x_n \le b_2$

  …

  $a_{m1} x_1 + a_{m2} x_2 + \ldots + a_{mn} x_n \le b_m$

  или в суммарном виде: $\sum_{j=1}^{n} a_{ij} x_j \le b_i$ для $i = 1, \ldots, m$

  где $a_{ij}$ — количество $i$-го ресурса, необходимого для производства единицы $j$-го вида продукции; $b_i$ — общий запас $i$-го ресурса.

• Условие неотрицательности: $x_j \ge 0$ для $j = 1, \ldots, n$.

Матричная форма записи прямой задачи значительно упрощает ее восприятие:

max Z = cTx
при Ax ≤ b
x ≥ 0

где $A$ — матрица технологических коэффициентов ($a_{ij}$), $x$ — вектор переменных решения, $c$ — вектор коэффициентов целевой функции, $b$ — вектор правых частей ограничений.

Двойственная задача, в свою очередь, предлагает взглянуть на те же экономические процессы с другой стороны – со стороны ценности ресурсов. Если прямая задача говорит о том, сколько произвести продукции, то двойственная задается вопросом, какова «справедливая» или «теневая» цена каждого из ресурсов.

Двойственная задача (Минимизация):

Определить вектор двойственных переменных $Y = (y_1, y_2, \ldots, y_m)^{\text{T}}$, где $y_i$ — двойственная оценка (теневая цена) единицы $i$-го ресурса.

• Целевая функция: Минимизировать $G = b_1 y_1 + b_2 y_2 + \ldots + b_m y_m = \sum_{i=1}^{m} b_i y_i \rightarrow \text{min}$
  где $b_i$ — общий запас $i$-го ресурса.
• Ограничения:

  $a_{11} y_1 + a_{21} y_2 + \ldots + a_{m1} y_m \ge c_1$

  $a_{12} y_1 + a_{22} y_2 + \ldots + a_{m2} y_m \ge c_2$

  …

  $a_{1n} y_1 + a_{2n} y_2 + \ldots + a_{mn} y_m \ge c_n$

  или в суммарном виде: $\sum_{i=1}^{m} a_{ij} y_i \ge c_j$ для $j = 1, \ldots, n$

  где $a_{ij}$ — количество $i$-го ресурса, необходимого для производства единицы $j$-го вида продукции; $c_j$ — прибыль от единицы $j$-го вида продукции.

• Условие неотрицательности: $y_i \ge 0$ для $i = 1, \ldots, m$.

В матричном виде двойственная задача выглядит так:

min G = bTy
при ATy ≥ c
y ≥ 0

Ключевым моментом здесь является транспонирование матрицы коэффициентов $A$ в $A^{\text{T}}$. Это означает, что столбцы исходной матрицы становятся строками в двойственной, и наоборот. Коэффициенты целевой функции прямой задачи ($c_j$) становятся правыми частями ограничений двойственной задачи, а правые части ограничений прямой задачи ($b_i$) становятся коэффициентами целевой функции двойственной задачи. Направление неравенств также меняется на противоположное: для прямой задачи максимизации с ограничениями-неравенствами типа «≤» (ресурсы), двойственная задача формулируется на минимум при ограничениях-неравенствах типа «≥» (требования).

Основная Теорема Двойственности

Теория двойственности в линейном программировании – это не просто математический трюк, а глубокий экономический принцип. Ее венцом является Основная Теорема Двойственности, которая гласит: если одна из двойственных задач (прямая или двойственная) имеет оптимальное решение, то и другая имеет решение, и при этом оптимальные значения их целевых функций совпадают. То есть, $\max Z = \min G$.

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

Практическая Реализация: Решение Двойственной Пары Задач в MS Excel Solver

Теория, сколь бы элегантной она ни была, требует практического применения. MS Excel Solver – это доступный и мощный инструмент, который позволяет решать задачи линейного программирования численно, а также генерировать ценные отчеты для анализа чувствительности.

Задача 1: Максимизация Прибыли (Производственное Планирование)

Представим предприятие, производящее три вида продукции (P1, P2, P3) из трех видов сырья (S1, S2, S3).

Исходные данные:

Ресурс	Расход на P1 (ед.)	Расход на P2 (ед.)	Расход на P3 (ед.)	Запас ресурса (ед.)
S1	2	3	1	120
S2	1	2	2	100
S3	3	1	2	150
Прибыль с ед. продукции	10	15	12

Математическая модель (Прямая задача — Максимизация):

Пусть $x_1, x_2, x_3$ — количество производимой продукции P1, P2, P3 соответственно.

Z = 10x1 + 15x2 + 12x3 → max

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

2x1 + 3x2 + 1x3 ≤ 120 (по ресурсу S1)
1x1 + 2x2 + 2x3 ≤ 100 (по ресурсу S2)
3x1 + 1x2 + 2x3 ≤ 150 (по ресурсу S3)
x1, x2, x3 ≥ 0

Решение в MS Excel Solver:

1. Подготовка таблицы: Вводим исходные данные, выделяем ячейки для переменных решения ($x_1, x_2, x_3$), целевой функции и левых частей ограничений.

   (Изображение excel_setup_max.png здесь подразумевается)

2. Настройка Solver:
   • Установить целевую ячейку: Ячейка с формулой целевой функции.
   • Оптимизировать: «Максимальное значение».
   • Изменяя ячейки переменных: Диапазон ячеек для $x_1, x_2, x_3$.
   • Добавить ограничения:
     • Левые части ограничений (формулы) $\le$ Правые части ограничений (числа).
     • Ячейки переменных $\ge 0$ (флажок «Сделать переменные без ограничений неотрицательными»).
   • Метод решения: «Симплекс-метод ЛП».

   (Изображение solver_settings_max.png здесь подразумевается)

3. Запуск Solver: Нажимаем «Найти решение». После нахождения решения, в диалоговом окне «Результаты подбора решения» выбираем «Отчет по результатам» и «Отчет по устойчивости».

Задача 2: Минимизация Затрат (Оптимизация Состава Сырья/Корма)

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

Исходные данные:

Питательное вещество	Содержание в К1 (ед./кг)	Содержание в К2 (ед./кг)	Минимальная норма (ед.)
ПВ1	2	1	10
ПВ2	1	3	12
ПВ3	3	2	15
Стоимость 1 кг корма	50	60

Математическая модель (Прямая задача — Минимизация):

Пусть $y_1, y_2$ — количество используемого корма К1, К2 соответственно (в кг).

G = 50y1 + 60y2 → min

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

2y1 + 1y2 ≥ 10 (по ПВ1)
1y1 + 3y2 ≥ 12 (по ПВ2)
3y1 + 2y2 ≥ 15 (по ПВ3)
y1, y2 ≥ 0

Решение в MS Excel Solver:

1. Подготовка таблицы: Аналогично первой задаче, вводим данные, ячейки для переменных решения ($y_1, y_2$), целевой функции и левых частей ограничений.

   (Изображение excel_setup_min.png здесь подразумевается)

2. Настройка Solver:
   • Установить целевую ячейку: Ячейка с формулой целевой функции.
   • Оптимизировать: «Минимальное значение».
   • Изменяя ячейки переменных: Диапазон ячеек для $y_1, y_2$.
   • Добавить ограничения:
     • Левые части ограничений (формулы) $\ge$ Правые части ограничений (числа).
     • Ячейки переменных $\ge 0$.
   • Метод решения: «Симплекс-метод ЛП».

   (Изображение solver_settings_min.png здесь подразумевается)

3. Запуск Solver: Нажимаем «Найти решение» и генерируем отчеты.

Генерация и Обзор Отчетов: Результаты и Устойчивость

После успешного выполнения Solver создает два ключевых отчета:

1. Отчет по результатам (Answer Report): Содержит итоговые значения переменных решения (оптимальный план), конечное значение целевой функции и информацию о состоянии ограничений (связанные/несвязанные).
2. Отчет по устойчивости (Sensitivity Report): Этот отчет является ядром нашего анализа. Он разделен на две основные таблицы:
   • «Изменяемые ячейки» (Adjustable Cells): Относится к переменным решения (количество продукции/корма) и коэффициентам целевой функции (прибыль/стоимость). Содержит «Сниженные издержки» (Reduced Cost) и «Допустимое увеличение/уменьшение» для коэффициентов ЦФ.
   • «Ограничения» (Constraints): Относится к ограничениям (ресурсам/нормам) и их правым частям. Содержит «Двойственный множитель» (Shadow Price) и «Допустимое увеличение/уменьшение» для правых частей ограничений.

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

Глубокий Экономический Анализ Устойчивости (Sensitivity Report)

Отчет по устойчивости – это настоящий кладезь информации для управленца. Он позволяет не только понять, насколько «крепок» найденный оптимальный план, но и определить наиболее перспективные направления для развития или, наоборот, потенциальные риски.

Интерпретация Двойственных Оценок (Теневых Цен)

Двойственные оценки, или теневые цены (Shadow Price), являются, пожалуй, наиболее интригующим и экономически значимым показателем в отчете по устойчивости. Они выражают объективно-обусловленную оценку одной единицы соответствующего ресурса или ограничения.

Строгое определение: Двойственная оценка ($y^{\text{опт}}_i$) для $i$-го ресурса (или $i$-го ограничения) показывает, на сколько изменится (увеличится для задачи максимизации, уменьшится для задачи минимизации) оптимальное значение целевой функции при увеличении правой части этого ограничения ($b_i$) на одну единицу, при условии, что это изменение не выходит за пределы допустимых изменений.

Математическая основа: В основе двойственных оценок лежит сложная, но изящная математика. Вектор оптимальных двойственных оценок ($Y^{\text{опт}}$) может быть рассчитан как $Y^{\text{опт}} = C_{\text{баз}} \cdot B^{-1}$, где $C_{\text{баз}}$ — вектор коэффициентов целевой функции, соответствующих базисным переменным в оптимальном плане, а $B^{-1}$ — обратная матрица базиса оптимального плана. Эта формула подчеркивает, что теневые цены не являются произвольными, а строго детерминированы структурой оптимального решения и ценностью тех переменных, которые формируют этот базис.

Экономический смысл:

• Для задачи максимизации прибыли: Если двойственная оценка ресурса S1 равна 5 руб./ед., это означает, что увеличение запаса ресурса S1 на одну единицу (например, со 120 до 121) приведет к увеличению максимальной прибыли на 5 рублей. Это прямая мера дефицитности ресурса и его вклада в общую прибыль. Чем выше теневая цена, тем более ценен и дефицитен ресурс, что указывает на приоритетные направления для инвестиций в расширение ресурсной базы.
• Для задачи минимизации затрат: Если двойственная оценка нормы питательного вещества ПВ1 равна -2 руб./ед., это означает, что снижение минимальной нормы ПВ1 на одну единицу (например, с 10 до 9) позволит снизить общие затраты на 2 рубля. Здесь знак минус указывает на то, что уменьшение «требования» приводит к снижению затрат, демонстрируя потенциальную экономию от пересмотра стандартов или рецептур.

Нулевая двойственная оценка: Если двойственная оценка ресурса равна нулю, это означает, что ресурс является избыточным (несвязанным). Его запасы не используются полностью в оптимальном плане, и, следовательно, дополнительная единица этого ресурса не принесет дополнительной прибыли (или не снизит затраты), поскольку «узкое место» находится в другом ограничении. Это важный сигнал для управленца: инвестировать в увеличение запасов такого ресурса нецелесообразно, лучше сосредоточиться на других, более дефицитных ресурсах.

Сравнительный анализ:

Показатель	Задача Максимизации (Прибыль)	Задача Минимизации (Затраты)
Двойственная оценка ($y^{\text{опт}}_i$)	Показывает, на сколько возрастет максимальная прибыль при увеличении $i$-го ресурса на 1 ед. (мера дефицитности ресурса).	Показывает, на сколько уменьшатся минимальные затраты при снижении $i$-го требования (нормы) на 1 ед. (мера экономии).
Нулевая оценка	Избыточный ресурс, увеличение не принесет прибыли.	Избыточное выполнение нормы (переизбыток питательного вещества), снижение нормы не увеличит затраты до определенного предела.

Анализ Интервалов Устойчивости Ресурсов (Правых Частей)

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

Экономический смысл: Эти интервалы указывают диапазон, в пределах которого двойственная оценка ресурса сохраняет свое численное значение и экономический смысл. То есть, если запас ресурса изменяется в пределах [Текущее значение — Допустимое уменьшение; Текущее значение + Допустимое увеличение], то наш оптимальный план (структура выпуска продукции или состав корма) останется прежним, и теневая цена ресурса будет по-прежнему точно предсказывать изменение целевой функции. Выход за эти пределы означает, что структура оптимального плана изменится, и потребуется пересчет модели. Разве не это является ключом к гибкому управлению ресурсами и прогнозированию результатов?

Аналитическая задача: Расчет требуемого изменения запасов дефицитного ресурса для достижения заданного процентного изменения ЦФ.

Предположим, в задаче максимизации прибыли мы хотим увеличить общую прибыль на 10%. Текущая оптимальная прибыль $Z = 800$. Значит, новая прибыль $Z’ = 800 \cdot 1.10 = 880$. Допустим, самый дефицитный ресурс S1 имеет $y^{\text{опт}}_1 = 5$ и допустимое увеличение запаса на 30 единиц.

Нам нужно найти $Δb_1$, такое что:

Z' = Z + yопт1 ⋅ Δb1
880 = 800 + 5 ⋅ Δb1
80 = 5 ⋅ Δb1
Δb1 = 80 / 5 = 16 единиц.

Поскольку требуемое изменение в 16 единиц не выходит за пределы допустимого увеличения (30 единиц), мы можем с уверенностью сказать, что увеличение запаса ресурса S1 на 16 единиц позволит увеличить прибыль на 10%, и при этом структура оптимального плана не изменится. Это критически важно для планирования инвестиций в ресурсы.

Интерпретация Сниженных Издержек (Reduced Cost) и Нерентабельности

Сниженные издержки (Reduced Cost) – еще один важный показатель Отчета по устойчивости, который относится к переменным решениям.

Строгое определение:

• Для переменных, которые включены в оптимальный план ($x_j > 0$), сниженные издержки всегда равны нулю. Это логично, поскольку эти продукты (или корма) уже оптимально используются.
• Для переменных, которые не вошли в оптимальный план ($x_j = 0$), сниженные издержки показывают, насколько необходимо улучшить коэффициент целевой функции (например, увеличить прибыль или снизить стоимость) для данного продукта/корма, чтобы он стал рентабельным и был включен в оптимальный план.

Математическая формула сниженных издержек:

Для переменной $x_j$, сниженные издержки рассчитываются как:

Reduced Costj = cj - Σmi=1 aij yоптi

Здесь $c_j$ — непосредственный коэффициент целевой функции (прибыль/стоимость) для $j$-ой переменной.

$\sum_{i=1}^{m} a_{ij} y^{\text{опт}}_i$ — суммарная стоимостная оценка ресурсов, необходимых для производства единицы $j$-ой продукции, рассчитанная по двойственным оценкам (теневым ценам) ресурсов.

Экономический смысл:

• Для задачи максимизации прибыли: Если для продукта P3, не вошедшего в план, сниженные издержки равны -3 рубля, это означает, что прибыль от P3 (текущая $c_3$) на 3 рубля меньше, чем объективно-обусловленная оценка ресурсов, необходимых для его производства. Таким образом, если принудительно произвести одну единицу P3, общая прибыль уменьшится на 3 рубля. Продукт считается нерентабельным. Чтобы P3 стал рентабельным, его прибыль должна возрасти как минимум на 3 рубля, что является четким указанием для отдела маркетинга или разработки.
• Для задачи минимизации затрат: Если для корма К3, не вошедшего в план, сниженные издержки равны +7 рублей, это означает, что стоимость К3 (текущая $c_3$) на 7 рублей выше, чем объективно-обусловленная оценка его питательной ценности. Принудительное включение одной единицы К3 увеличит общие затраты на 7 рублей. Чтобы К3 стал конкурентоспособным, его стоимость должна снизиться как минимум на 7 рублей, что дает ориентир для переговоров с поставщиками или поиска альтернативных компонентов.

Аналитическая задача: Интерпретация последствий принудительного включения в план нерентабельной продукции/корма.

Предположим, в задаче максимизации прибыли оптимальный план не включает продукт P3, и его сниженные издержки составляют -3. Руководство, по каким-то причинам (например, маркетинговым), решает принудительно включить в план 10 единиц P3.

Как изменится целевая функция?

Новое значение целевой функции ($Z’$) будет равно:

Z' = Z + Reduced Cost3 ⋅ количество принудительно включенной продукции
Z' = Z + (-3) ⋅ 10 = Z - 30

То есть, общая прибыль снизится на 30 рублей. Эта формула позволяет точно оценить «цену» таких неоптимальных решений.

Интервалы Устойчивости Коэффициентов Целевой Функции

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

Экономический смысл: Эти интервалы (столбцы «Допустимое увеличение» и «Допустимое уменьшение» для «Изменяемых ячеек») показывают, насколько может измениться прибыль или стоимость единицы продукта/корма, без изменения структуры оптимального плана. То есть, количество производимых продуктов (или используемых кормов) в оптимальном плане останется тем же, изменится только значение самой целевой функции.

Например, если прибыль от продукта P1 может быть «Допустимо увеличена» на 5, а «Допустимо уменьшена» на 3, это означает, что даже если прибыль P1 возрастет до ($c_1 + 5$) или упадет до ($c_1 — 3$), оптимальные объемы производства P1, P2 и P3 останутся прежними. Это дает гибкость в ценовой политике или переговорах с поставщиками/потребителями. Выход за эти пределы потребует перерасчета модели, так как изменится оптимальное соотношение производства.

Заключение и Выводы

Глубокий анализ чувствительности, проведенный с использованием Отчета по устойчивости MS Excel Solver, превращает решение задачи линейного программирования из чисто математического упражнения в мощный инструмент поддержки принятия управленческих решений. Мы продемонстрировали, как строгая математическая модель, основанная на теории двойственности, может быть реализована и интерпретирована для двух фундаментальных экономических задач: максимизации прибыли и минимизации затрат.

Ключевые выводы, подтвержденные нашим анализом:

• Двойственные оценки (теневые цены) являются бесценным индикатором дефицитности ресурсов (для максимизации) или потенциальной экономии от снижения требований (для минимизации). Они позволяют приоритизировать инвестиции в увеличение запасов ресурсов или ослабление производственных ограничений, а также оценить финансовые последствия изменения этих ограничений.
• Интервалы устойчивости ресурсов и коэффициентов целевой функции определяют границы, в которых оптимальный план сохраняет свою структуру. Это дает менеджерам гибкость в ценообразовании, планировании закупок и оценке рисков, связанных с изменением внешних условий, обеспечивая стабильность и предсказуемость ключевых бизнес-процессов.
• Сниженные издержки позволяют точно оценить «рентабельность» продуктов или компонентов, не включенных в оптимальный план, и количественно выразить потери от принудительного включения таких элементов. Это критически важно для формирования ассортиментной политики и оценки эффективности новых видов продукции.

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

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

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

1. Грызина Н.Ю., Мастяева И.Н., Семенихина О.Н. Математические методы исследования операций в экономике.
2. Studfile.net. URL: https://studfile.net/ (дата обращения: 07.10.2025).
3. Sstu.ru. URL: https://sstu.ru/ (дата обращения: 07.10.2025).
4. Habr.com. URL: https://habr.com/ (дата обращения: 07.10.2025).
5. Kgsu.ru. URL: https://kgsu.ru/ (дата обращения: 07.10.2025).
6. Ppt-online.org. URL: https://ppt-online.org/ (дата обращения: 07.10.2025).
7. Narod.ru. URL: https://narod.ru/ (дата обращения: 07.10.2025).
8. Semestr.ru. URL: https://semestr.ru/ (дата обращения: 07.10.2025).