Решение задач оптимизации с помощью Exce l: практикум

Москва

Горячая линия – Телеком

2020

УДК 519.852:004.94 
ББК 32.973.26-018.2 
    Ч-45 

Р е ц е н з е н т ы:  
доктор техн. наук, профессор  Б. И. Шахтарин;  
доктор техн. наук  В. В. Сизых 

Чернышов Ю. Н., Казновская Л. Н., Козлитина О. М. 
Ч-45         Решение задач оптимизации с помощью Excel: практикум. 
Учебное пособие для вузов. – М.: Горячая линия – Телеком, 
2020. – 96 с.: ил. 

ISBN 978-5-9912-0739-3. 

Изложены методы решения экономических задач оптимизации 
с использованием средства «Поиск решения» MS Excel. Рассмотрены 
задачи 
линейного 
программирования, 
систем 
массового 

обслуживания, теории игр и другие. Пособие подготовлено на основе 
задач и заданий, предлагаемых для решения студентам кафедры 
Инновационного предпринимательства Мытищинского филиала 
МГТУ им. Н. Э. Баумана при проведении практических занятий по 
дисциплине «Методы оптимальных решений». 
Для студентов экономических специальностей. 
ББК 32.973.26-018.2 

Адрес издательства в Интернет WWW.TECHBOOK.RU 

Учебное издание 

Чернышов Юрий Николаевич, Казновская Лиана Насимовна, 
Козлитина Ольга Михайловна 

Решение задач оптимизации с помощью Excel: практикум 
Учебное пособие для вузов 

Тиражирование книги начато в 2018 г.  

Все права защищены.
Любая часть этого издания не может быть воспроизведена в какой бы то ни было форме 
и какими бы то ни было средствами без письменного разрешения правообладателя
© ООО «Научно-техническое издательство «Горячая линия – Телеком»
www.techbook.ru
©  Ю. Н. Чернышов, Л. Н. Казновская, О. М. Козлитина 

Введение

Необходимость применения персональных компьютеров в про-
цессе принятия управленческих решений в эпоху рыночной эконо-
мики особенно актуальна. Однако, к сожалению, не всем руководи-
телям производств и экономистам известно, что существует простое
и доступное непрофессиональным программистам средство решения
экономико-математических задач, связанных с оптимизацией затрат
и, в конечном счете, увеличением прибыли предприятия.
Это средство — табличный процессор Excel фирмы Microsoft.
Идея соединения процедур поиска решения с электронной таблицей
оказалась весьма удачной и позволяет представлять данные для рас-
четов и результаты в удобной и наглядной форме.
В книге рассматривается применение поиска решений для ре-
шения наиболее часто встречающихся на практике экономических
задач.
Принцип решения оптимизационных задач с помощью Excel
чрезвычайно прост. В рабочей таблице необходимо выделить ячей-
ки, которые будут являться переменными, подлежащими оптимиза-
ции; в другие ячейки вносятся коэффициенты и ограничения, опре-
деляемые экономическими условиями задачи; наконец, выделяется
ячейка, в которую записывается формула критерия задачи, подле-
жащего оптимизации. Формула критерия строится с использовани-
ем ячеек первых двух типов. Для упрощения формирования крите-
рия могут быть использованы промежуточные ячейки таблицы.
Вторым шагом является вызов панели поиска решения и запол-
нение в нем полей исходных данных. Указываются адрес целевой
ячейки (ячейка критерия); цель оптимизации (минимум, максимум
или конкретное значение); адреса ячеек, подлежащих оптимизации
(ячейки переменных); адреса ячеек, определяющих ограничения за-
дачи. При необходимости могут быть дополнительно указаны па-
раметры выполнения процедуры оптимизации (метод оптимизации,
тип модели и пр.).
После этого выполняется процесс оптимизации и, если зада-
ча поставлена корректно, на экране появляются результаты. При
этом пользователь может кроме результатов оптимизации посмот-
реть оценку качества оптимизации (например качество сходимости).

Введение

При необходимости пользователь может вернуть таблицу к ис-
ходному виду, поменять значения коэффициентов или ограничений
и повторить решение задачи.
На современных компьютерах про-
цесс оптимизации экономических задач средней степени сложности
редко занимает практически секунды. Поэтому у пользователя име-
ется возможность за короткое время провести серию машинных экс-
периментов с различными вариантами исходных данных и выбрать
наилучший.
Построенное описанным выше способом решение задачи можно
сохранить и в дальнейшем использовать как шаблон для решения
однотипных задач, меняя только исходные данные (коэффициенты
и ограничения).
В приложении для самостоятельного решения предложен ряд
типовых задач по оптимизации, собранные из различных книг по
математическим методам [1–3], а также из Интернета.
Кроме того, даны краткие биографические сведения о двух ла-
уреатах Нобелевской премии по экономике — Леониде Канторовиче
и Василии Леонтьеве. Именно они заложили основы постановки и
решения задач по оптимизации в экономике.

Теоретическое введение

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

X = {x : gi(x) ⩾ 0, i = 1, 2, ..., k},

где gi(x) — также скалярные функции; функцию F(x) называют
целевой функцией, или функцией цели; множество X — допусти-
мым множеством; решение x∗ задачи МП — оптимальной точкой
(вектором).
В зависимости от природы множества X задачи МП классифи-
цируются как:
• задачи дискретного программирования (или комбинаторной оп-
тимизации) — если X конечно или счётно;
• задачи целочисленного программирования — если X является
подмножеством множества целых чисел;
• задачи нелинейного программирования, если ограничения или
целевая функция содержат нелинейные функции и X является
подмножеством конечномерного векторного пространства.
Если же все ограничения gi(x) и целевая функция F(x) содер-
жат лишь линейные функции, то это — задача линейного программирования.

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

Г л а в а 1

• распределительные задачи;
• задачи на составление смесей;
• задачи производственного планирования;
• задачи рационального использования сырья и материалов и
другие.
Характерные черты задач ЛП следующие:
1) показатель оптимальности F(x) представляет собой линейную 
функцию от элементов решения x = (x1, x2, ..., xn);
2) ограничительные условия, налагаемые на возможные решения, 
имеют вид линейных равенств или неравенств.

1.1. Общая форма записи математической
модели задачи ЛП

Целевая функция (ЦФ)

F(x) = c1x1 + c2x2 + . . . + cnxn → max(min)
(1.1)

при ограничениях
⎧
⎪
⎪
⎪
⎪
⎪
⎪
⎨

⎪
⎪
⎪
⎪
⎪
⎪
⎩

a11x1 + a12x2 + ... + a1nxn ⩽ (⩾, =) b1;

a21x1 + a22x2 + ... + a2nxn ⩽ (⩾, =) b2;

.......................................

am1x1 + am2xm + ... + amnxn ⩽ (⩾, =) bm;

x1, x2, ..., xn ⩾ 0.

(1.2)

В сокращенной записи целевая функция (1.1) и ограничения
(1.2) могут быть представлены в виде

F(x) =

n
j=1
cjxj → max(min)

⎧
⎪
⎪
⎨

⎪
⎪
⎩

n
j=1
aijxj ⩽ (⩾, =) bi;

xj ⩾ 0,

где i = 1, 2, ..., m; j = 1, 2, ..., n.
При описании реальной ситуации с помощью линейной модели
следует проверять наличие в модели таких свойств, как пропорциональность 
и аддитивность.
Пропорциональность означает, что вклад каждой переменной 
в ЦФ и общий объем потребления соответствующих ресурсов
должен быть прямо пропорционален этой переменной (1.1). Однако, 
если, например, продавая j-й товар в общем случае по цене
100 рублей, фирма будет делать скидку при определенном уровне
закупки до уровня цены 95 рублей, то будет отсутствовать прямая

Теоретическое введение
7

пропорциональность между доходом фирмы и величиной переменной 
xj. То есть в разных ситуациях одна единица j-го товара будет
приносить разный доход.
Аддитивность означает, что ЦФ и ограничения должны представлять 
собой сумму вкладов от различных переменных. Приме-
ром нарушения аддитивности служит ситуация, когда увеличение
сбыта одного из конкурирующих видов продукции, производимых
одной фирмой, влияет на объем реализации другого.
Допустимое решение — это совокупность значений перемен-
ных (план) X = (x1, x2, ..., xn), удовлетворяющих ограничениям
(1.2).
Оптимальное решение — это план, при котором целевая
функция (1.1) принимает свое оптимальное (максимальное или ми-
нимальное) значение.

1.2. Методика построения математической
модели

Прежде чем построить математическую модель задачи, т. е. за-
писать ее с помощью математических символов, необходимо четко
разобраться с экономической ситуацией, описанной в условии. Для
этого необходимо с точки зрения экономики, а не математики, от-
ветить на следующие вопросы:
1) Что является искомыми величинами задачи?
2) Какова цель решения?
3) Какой параметр задачи служит критерием эффективности
(оптимальности) решения, например, прибыль, себестоимость, вре-
мя и т. п.?
4) Какое значение (максимальное или минимальное) должен
принимать этот параметр для достижения наилучших результатов?
5) Какие условия в отношении искомых величин и ресурсов за-
дачи должны быть выполнены? Эти условия устанавливают, как
должны соотноситься друг с другом различные параметры зада-
чи, например количество ресурса, затраченного при производстве, и
его запас на складе; количество выпускаемой продукции и емкость
склада, на котором она будет храниться; количество выпускаемой
продукции и рыночный спрос на эту продукцию и т. д.
Только после ответа на все эти экономические вопросы можно
приступать к записи этих ответов в математическом виде, т. е. к
записи математической модели. При этом придерживаются следу-
ющих правил:

Г л а в а 1

1. Искомые величины, которые являются переменными зада-
чи, как правило, обозначаются малыми латинскими буквами с ин-
дексами, например однотипные переменные удобно представлять в
виде x = (x1, x2, ..., xn).
2. Цель решения записывается в виде целевой функции, обо-
значаемой, например, F(x). Математическая формула ЦФ F(x) от-
ражает способ расчета значений параметра — критерия эффектив-
ности задачи.
3. Условия, налагаемые на переменные и ресурсы задачи, запи-
сываются в виде системы равенств или неравенств, т. е. ограничений. 
Левые и правые части ограничений отражают способ получения (
расчет или численные значения из условия задачи) значений
тех параметров задачи, на которые были наложены соответствующие 
условия.
4. Для исключения ошибок рекомендуется в процессе записи
математической модели указывать единицы измерения переменных
задачи, целевой функции и всех ограничений.

1.3. Средство поиска решений в Excel
Для пользователей, впервые работающих с поиском решения,
заметим, что это средство Excel по умолчанию не установлено. Чтобы 
его активировать, кликните по значку
в левом верхнем
углу окна Excel и перейдите по кнопке «Параметры Excel» в окно
управления надстройками, где кликните по кнопке «Перейти». Появится 
окно с надстройками (рис. 1.1). Поставьте галочку рядом с
поиском решения и кликните ОК.

Рис. 1.1

Теоретическое введение
9

На закладке «Данные» появится поле «Анализ» с кнопкой

. Если кликнуть по этой кнопке, появится окно
«Поиск решения» (рис. 1.2). Использование средства поиска решений 
будет подробно объянено в следующих разделах.

Рис. 1.2

Рис. 1.3

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

Г л а в а 1

реальную экономическую задачу с большим числом переменных, рекомендуется, 
как минимум, уменьшить относительную погрешность
с тем, чтобы сократить время решения. Тем более, что погрешность,
равная одной миллионной, не требуется для большинства реальных
задач.
В следующих разделах приведены примеры решения различных 
экономических задач с помощью Excel методом поиска решений. 
С точки зрения математической постановки все они относятся
в линейному программированию и сводятся к формулам (1.1) и (1.2).
Однако в каждом разделе предлагается удобный для конкретной задачи 
способ представления исходных данных и формул для расчета.
Дополнительно для знакомых с приложением Visual Basic for
Application (VBA) приводятся примеры автоматизации решения ряда 
задач, в частности раскроя сортиментов для оптимизации раскроя 
материалов. Кроме того, решение задачи балансных моделей
уже не требует манипуляции с клавишами при работе с матрицами,
достаточно нажатия на отдельную, созданную пользователем кнопку 
для вызова несложной программы на VBA.