Решение задач оптимизации с помощью Exce l: практикум
Покупка
Тематика:
Табличные редакторы
Издательство:
Горячая линия-Телеком
Год издания: 2020
Кол-во страниц: 96
Дополнительно
Вид издания:
Учебное пособие
Уровень образования:
ВО - Бакалавриат
ISBN: 978-5-9912-0739-3
Артикул: 698771.03.99
Изложены методы решения экономических задач оптимизации с использованием средства «Поиск решения» MS Excel. Рассмотрены задачи линейного программирования, систем массового обслуживания, теории игр и другие. Пособие подготовлено на основе задач и заданий, предлагаемых для решения студентам кафедры Инновационного предпринимательства Мытищинского филиала МГТУ им. Н. Э. Баумана при проведении практических занятий по дисциплине «Методы оптимальных решений». Для студентов экономических специальностей.
Тематика:
ББК:
УДК:
- 004: Информационные технологии. Вычислительная техника...
- 519: Комбинатор. анализ. Теория графов. Теория вер. и мат. стат. Вычисл. мат., числ. анализ. Мат. кибер..
ОКСО:
- ВО - Бакалавриат
- 38.03.01: Экономика
- 38.03.02: Менеджмент
ГРНТИ:
Скопировать запись
Фрагмент текстового слоя документа размещен для индексирующих роботов.
Для полноценной работы с документом, пожалуйста, перейдите в
ридер.
Москва Горячая линия – Телеком 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.