Оптимизация в excel

Решение задач оптимизации в Microsoft Excel 2003

Курс дистанционного обучения:
«Экономическая информатика»
Модуль 2 (2,5 кредита): Прикладное программное обеспечение офисного назначения

Тема 2.1. Обработка текстовой информации

Тема 2.2. Процессоры электронных таблиц

Тема 2.3. Программные средства презентаций и основы офисного программирования

Тема 2.4. Системы управления базами данных и экспертные системы

2.4.11. Учебная база данных с главной кнопочной формой «Training_students» — Скачать

2.2. Процессоры электронных таблиц

2.2.7. Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое променение находят различные средства Excel.

В этом разделе рассмотрим команды:

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

Подбор параметров

Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

• формула для расчета;
• пустая ячейка для искомого значения;
• другие величины, которые используются в формуле.

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

Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи.

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления)

Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.

2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле «Установить в ячейке» в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.

3. Введите в текстовое поле Значение число, соответствующее объему продаж — 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.

После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.

Команда Поиск решения

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

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

В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек.

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

1. Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.
2. Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке.
3. Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

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

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

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе:

1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения (Рис. 3.). Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».
3. Перейдите к полю «Изменяя ячейки» и введите переменные ячейки листа.
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель «Сохранить найденное решение» и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

Диспетчер сценариев «что – если»

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

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.

Создание сценария

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

Создание сценариев происходит следующим образом:

1. Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
2. Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
3. Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
4. Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
5. Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

Просмотр сценария

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

1. Выполните команду Сервис/Сценарии. Открывается окно диалога.
2. Выберите из списка сценарий для просмотра.
3. Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
4. Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчетов по сценарию

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

Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчета по сценарию происходит следующим образом:

Оптимизация смен рабочих с помощью Поиска решений в EXCEL

Решим задачу об оптимизации смен рабочих с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника «Методы оптимизации управления и принятия решений» авторы Зайцев М.Г. и Варюхин С.Е. (2008г.). Задача 2.43 «Проблема мастера».

Сборник задач «Методы оптимизации управления и принятия решений» довольно популярен в качестве задачника для Вузов, поэтому ссылки на него встречаются повсеместно в интернете. Приятно, что для каждой задачи имеется ответ в конце книги — можно сравнить получившееся решение с решением, которое найдено авторами с помощью Поиска решения (в году так 2000-м, я полагаю).

Условия задачи (оригинальные)

Мастер должен выбрать несколько фрезеровщиков из 10 (Р1,Р2..Р10) для изготовления 8 видов деталей (Д1,Д2. Д8) для партии продукции:

так, чтобы сократить общие затраты рабочего времени. Среднее количество деталей, которое каждый рабочий может обработать за смену, дано в таблице:

а. Определить оптимальное распределение рабочих по операциям, принимая во внимание, что рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2 соответственно.b. Каков самый короткий срок выполнения этого заказа?

Разбор условия задачи

Скажу честно, но условия задачи не кажутся мне четко сформулированными. Вот мои комментарии:

1) Рабочих называют сначала фрезеровщиками, затем просто рабочими. Зачем?

2) Используется понятие «операции», то есть подразумевается некая определенная последовательность действий. То есть можно подумать, что сначала делается операция Д1 (изготовление детали 1), затем Д2 и т.д. Чтобы выяснить это, обратимся к ответу:

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

3) В ответе целая и дробные части смены отделены точкой, но как известно, в России разделителем является запятая. Кроме того, очевидно, что дробная часть смены приведет к дробному количеству изготовленных деталей (это как то непонятно). Как следствие, в задаче планируется складывать 63,111 и 48,888 деталей, чтобы выполнить заказ. Что не очень логично.

4) В условии используется » среднее количество деталей, которое каждый рабочий может обработать за смену». Как будто, эту задачу планируется решать статистическими методами с использованием некой функции распределения.

5) Зачем сказано, что «рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2»? Просто поставьте ноль, никакого смысла в этом условии нет.

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

Решение

Если вы не знакомы с Поиском решения, то для начала прочитайте статью Поиск решения MS EXCEL. Знакомство .

Чтобы построить модель для Поиска решения (ПР) в EXCEL нужно определить 3 ее составляющих:

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

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

Теперь рассчитаем, сколько же деталей будет изготовлено при такой загрузке рабочих.

Оказывается, в решении опечатка. При данном ответе не будет выполнен заказ — основное условие задачи. Оказывается, деталь Д6 также изготавливает рабочий Р4. После добавления нужного количества смен, получим правильный ответ — теперь заказ выполнен.

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

Оказывается, количество смен у каждого рабочего составляет от 1 до 4 (ровно)! Почему? Потому что, это дополнительное ограничение, про которое ничего не сказано в условии задачи. Получим это решение. Для этого создадим модель.

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

Нажав кнопку Найти решение получим именно тот ответ, который дан в задачнике. Чтобы понять, откуда взялось ограничение про загрузку каждого рабочего от 1 до 4 смен, удалим эти ограничения и снова запустим Поиск решения. Вот ответ:

Как видим, общее количество смен всех рабочих уменьшилось (35,78 против 37,23). Так зачем авторам потребовались эти дополнительные ограничения? Дело в том, что без этих ограничений мы получим тривиальный ответ, который очевиден и без Поиска решения! Сравните загрузку рабочих с их производительностью:

Максимальная производительность выделена цветом и поразительно совпадает с найденным решением. На самом деле решение очевидно: чтобы сократить загрузку рабочих — возьми самых производительных (по типам деталей) и загрузи их! Этот факт приводит к мысли, что либо задача некорректно сформулирована, либо неправильно решена. Будем грешить на решение.

Перед тем как решить задачу другим способом, ответим на второй вопрос задачи: «Каков самый короткий срок выполнения этого заказа?» (см. файл примера, лист б )

Чтобы вычислить самый короткий срок выполнения заказа — просто уберем 2 доп. ограничения о количестве смен (от 1 до 4) в окне Поиска решения и изменим целевую функцию в красной ячейке J31 . Теперь будем МИНимизировать МАКСимальную длительность смен рабочих ( =МАКС(J21:J30) ).

При этом считаем, что все 10 рабочих работают независимо друг от друга (на разных станках), поэтому время выполнения заказа будет равно максимальному количеству смен у одного из рабочих. Полученный ответ 3,89 совпадает с ответом в задачнике (менее 4-х дней).

Альтернативное решение

Теперь попробуем переформулировать условие задачи.

Во-первых, предположим, что каждый рабочий за 8-ми часовую смену обязан сделать все 8 типов деталей (от Д1 до Д8). Т.е., в 1-й час каждый рабочий делает Д1, во 2-й час каждый рабочий делает Д2 и т.д. Производительность дана в таблице.

Во-вторых, заказ нужно сделать за 1 смену.

Вопрос: каких рабочих нужно взять, чтобы выполнить заказ минимальным количеством рабочих?

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

Если для выполнения заказа мы берем рабочего, то ему присваивается значение 1, если нет, то 0.

Поиск решения (по прежнему линейная задача) находит, что достаточно взять 7 рабочих, чтобы выполнить заказ. В этом случае, правда, заказ будет несколько перевыполнен по некоторым типам деталей.

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

Технология решения задач оптимизации в Excel

Постановка задачи оптимизации

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

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

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

2. Ввод исходных данных и формул, реализующих математическую модель в электронную таблицу.

3. Настройка параметров инструмента Поиск решения и его применение для решения задачи.

Оптимальный план выпуска продукции

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

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

Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого 16 ден. ед., шоколадного – 14 ден. ед.

Требуется определить в каком количестве мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным.

Шаг 1 – разработка математической модели

Введем обозначения: x1 – суточный объем производства сливочного мороженого, х2 — суточный объем производства шоколадного мороженого. Исходя из условия задачи целевая функция будет иметь вид

Оптимизация доставки

Постановка задачи

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

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

Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:

• Светло-желтая таблица (C4:G6) описывает стоимость доставки одной единицы товара от каждого склада до каждого магазина.
• Лиловые ячейки (C15:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
• Красные ячейки (J10:J13) отображают емкость каждого склада – предельное количество товара, которое склад вмещает.
• Желтые (C13:G13) и синие (H10:H13) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
• Общая стоимость доставки (J18) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки — для подсчёта здесь используется функция СУММПРОИЗВ(SUMPRODUCT).

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

Решение

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

Если на вкладке Данные вашего Excel такой команды нет – ничего страшного — значит надстройка просто еще не подключена. Для ее активации откройте Файл, далее выберите Параметры – Надстройки – Перейти (Options — Add-Ins — Go To) . В открывшемся окне поставьте галочку напротив нужной нам строки Поиск решения (Solver) .

В этом окне нужно задать следующие параметры:

• Оптимизировать целевую функцию(Set targetcell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (J18). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданному значению (например, вписаться ровно в выделенный бюджет).
• Изменяя ячейки переменных(Bychangingcells) – здесь укажем зеленые ячейки (C10:G12), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
• В соответствии с ограничениями(SubjecttotheConstraints) – список ограничений, которые надо учитывать при проведении оптимизации. Для добавления ограничений в список нужно нажать кнопку Добавить(Add) и ввести условие в появившееся окно. В нашем случае, это будет ограничение на спрос:

и ограничение на предельный объем складов:

Кроме очевидных ограничений, связанных с физическими факторами (вместимость складов и средств перевозки, ограничения бюджета и сроков и т.д.) иногда приходится добавлять ограничения «специально для Excel». Так, например, Excel легко может устроить вам «оптимизацию» стоимости доставки, предложив возить товары из магазинов обратно на склад — расходы при этом станут отрицательными, т.е. мы получим прибыль! 🙂

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

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

В выпадающем списке Выберите метод решения (Select a solving method) дополнительно требуется подобрать подходящий математический метод для решения на выбор из трех вариантов:

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

Наша задача явно относится к линейным: доставили 1 шт — затратили 40 р., доставили 2 шт — затратили 80 р. и т.д., так что симплекс-метод будет наилучшим выбором.

Теперь, когда данные для расчета введены, нажмем кнопку Найти решение (Solve) , чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время (особенно с эволюционным методом), но наша задача для Excel проблемы не составит – через пару мгновений мы получим следующие результаты:

Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.

Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий. По желанию пользователя Excel может построить три типа Отчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.

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

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

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

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

Решение задач на оптимизацию с помощью MS Excel

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

Модели всех задач на оптимизацию состоят из следующих элементов:

1. Переменные — неизвестные величины, которые нужно найти при решении задачи.

2. Целевая функция — величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.

3. Ограничения — условия, которым должны удовлетворять переменные.

Поиск решения такой модели рассмотрим на примере такого вопроса:

Спрос на журнал «Автомеханик» составляет 12 тысяч экземпляров, а на журнал «Инструмент» -не более 7,5 тысячи в месяц.
Определите оптимальное количество издаваемых журналов, которое обеспечит максимально выручку от продажи.

Давайте разберемся, что здесь является переменными, что целевой функцией, что ограничениями.

Найти нам необходимо оптимальное количество издаваемых журналов каждого вида. А издавать их можно в трех типографиях на разных условиях. Вот и получается, что нам необходимо определить размер тиража каждого журнала напечатанного в каждой типографии. Это и будут наши переменные.

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

Теперь ограничения. В условиях сказано, что каждая типография может выделить на наш тираж только определенное время. Длительность печати тысячи единиц тиража каждого журнала каждой типографией известна.

Таким образом, произведение объема тиража на длительность печати тысячи единиц для каждой типографии не может быть больше заданного количества времени.

Еще одно важное ограничение, о котором обычно забывают — переменные должны быть неотрицательными.

Попытаемся представить модель в Excel.

Переменные, то есть объем тиража, находятся в ячейках B10:C12. Целевая функция — в ячейке D13. Обратите внимание, целевая функция построена формулой, ссылаясь на ячейки с переменными и исходные данные (стоимость единицы тиража).

Также формулами подсчитывается фактическое время печати тиража в каждой из типографий (ячейки E3:E5).

Все готово, приступаем решению задачи с помощью надстройки.

Включается она через меню Сервис — Поиск решений. Если такого пункта меню нет, войдите в меню Сервис — Надстройки и отметьте галочкой соответствующую надстройку. Может понадобиться установочный комплект Office.

Перед Вами появится следующий диалог:

Здесь указываем адрес целевой ячейки, отмечаем, что ее нужно привести к максимальному значению, изменяя ячейки $B$10:$C$12. Диапазоны можно указывать мышью — станьте в нужное поле диалога и выделите на листе нужные ячейки. Адрес автоматически попадет в диалог.

Добавляем ограничения. После нажатия кнопки Добавить появляется диалог:

Вспоминаем. У нас фактическое время печати тиража в каждой типографии не может превышать заданного лимита.

Для Алмаз-Пресс ограничение будет таким E3 ≤ D3. В ячейке E3 должна быть формула суммы продолжительности печати тиража первого и вторго журналов в этой типографии, полученной перемножением тиража на норму времени.

Думаю, понятно, как ввести в диалог описанное ограничение.

Если нажать Ок, ограничение будет добавлено, а диалог закроется. Чтобы несколько раз не открывать диалог, сделана кнопка Добавить. Ограничение сохраняется, а диалог очищается для добавления следующего ограничения. Аналогично добавляем ограничения для оставшихся типографий.

Ограничения неотрицательности можно также задать с помощью этого диалога — для каждой ячейки с объемом тиража установить ограничение ≥0.

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

Здесь достаточно отметить галочку Неотрицательные значения.

Все модель готова к расчету:

Через пару секунд Вы будете иметь оптимальное решение.

Теперь выберите Сохранить решение и нажмите Ok.

Можете проверить решение, пробуя подставлять другие значения тиража, перераспределяя тираж между типографиями. Вряд ли Вам удастся улучшить результат.

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

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