Решение задач оптимизации с помощью 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.