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

Зачастую экономисты в своей практике встречаются с вопросами оптимизации расходов.

Рассмотрим пример оптимизации транспортных расходов с помощью инструмента Excel «Поиск решений».

Пример 1

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

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

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

 Рис. 1. Расчет оптимальных перевозок

На рис. 1 расположены две таблицы: с исходными данными и расчетными данными. В ячейках D8:H11 расположены тарифы за 1 ездку в разрезе транспортных компаний до пунктов назначения, в ячейках D12:H12 — плановое количество ездок за период до пунктов назначения, в ячейках I8:I11 — удельный вес перевозок каждой транспортной компании в общем количестве планируемых перевозок за период. Эти ячейки для удобства не раскрашены. В ячейках J8:J12 и Н13 рассчитано число ездок по каждой транспортной компании и в целом за период. Формулы в этих ячейках выглядят следующим образом:

Ячейка Н13: =СУММ(D12:H12),

Ячейка J8: =I8*$H$13.

Данную формулу из ячейки J8 протаскиваем (копируем) в ячейки J9, J10, J11.

Ячейка J12: =СУММ(J8:J11).

Следующая таблица на листе посвящена расчету и называется «Расчет». Ячейки D19:Н22 предназначены для распределения количества ездок до пунктов назначения между транспортными компаниями. На рис. 1 в ячейках дано такое распределение, заполненное вручную. В ячейках D23:I27 рассчитаны суммы расходов на транспортные перевозки в разрезе транспортных компаний, оказывающих транспортные услуги, и пунктов назначений, а также итоги.

Приведем формулы, представленные в этих ячейках.

Значения в ячейках D24:Н27 получены перемножением количества ездок (ячейки D19:Н22) на тарифы (ячейки D8:Н12). В ячейку D24 запишем формулу:

=D19*D8.

Протащим (скопируем) формулу в ячейки D25:D27 и E24:Н27.

В ячейках D23:I23 формируются итоговые суммы транспортных услуг в разрезе пунктов назначения. Запишем в ячейку D23 формулу:

=СУММ(D24:D27).

Протащим (скопируем) эту формулу в ячейки Е23:I23.

В ячейках I24:I27 формируются итоговые суммы транспортных услуг в разрезе компаний, оказывающих эти услуги. Запишем в ячейку I24 формулу:

=СУММ(D24:H24).

Протащим (скопируем) ее в ячейки I25:I27.

Таким образом, стоимость транспортных расходов по компании в целом формируется в ячейке I23. В первоначальном расчете, представленном на рис. 1, данная сумма равна 35 790 руб.

Скопируем данный лист в эту же книгу. Далее необходимо приступить непосредственно к оптимизации. Задача — подобрать в ячейках D19:Н22 такие значения, чтобы в ячейке I23 была рассчитана минимальная сумма расходов на транспорт. Для этого воспользуемся инструментом «Поиск решений».

Для начала надо выбрать оптимизируемую ячейку (I23). Затем вызовем диалоговое окно «Поиск решений», представленное на рис. 2.

Это важно. Надстройку «Поиск решений» не всегда можно обнаружить в меню рабочего стола компьютера, так как она может быть не подключена. Для ее подключения необходимо выполнить ряд действий, которые аналогичны во всех версиях MS Office: «Сервис — Надстройки — Поиск решений (установить флажок)». Теперь данный инструмент можно будет найти на панели инструментов рабочего стола.

Рис. 2. Использование надстройки «Поиск решений»

В строке «Оптимизировать целевую функцию» будет стоять адрес оптимизируемой ячейки, в данном случае — $I$23. Выберем цель, поставив флажок «Минимум». В строке «Изменяя ячейки переменных» помещаются адреса ячеек, которые необходимо будет подобрать для достижения желаемого результата ($D$19:$Н$22).

В поле запишем ограничения в соответствии с ограничениями. Для этого воспользуемся кнопкой «Добавить», которая откроет окно «Добавить ограничения». Введем одно из ограничений:

$D$19:$H$22 = целое,

$D$12:$H$12 = $D$18:$H$18,

$J$8:$J$11 = $I$19:$I22.

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

Порядок ввода ограничений не имеет значения. Главное — не забыть ни одно из ограничений.

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

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

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

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

F(x) = a1 × x1 + а2 × x2 + … + аn × xn,

где a1, а2, …, аn — константы;

x1, x2, …, xn — переменные.

Данная модель является линейной.

Примером нелинейной модели является оптимизация перевозок с целью минимизации расходов, когда тарифы на перевозки распределены по интервалам:

• от 0 до 10 км — стоимость перевозки 200 руб.;
• от 11 до 20 км — стоимость перевозки 250 руб.;
• от 21 до 50 км — стоимость перевозки 500 руб. и т. д.

Вернемся к диалоговому окну «Параметры поиска решений». Далее нажимаем кнопку «Найти решение», в результате чего появится окно с результатом поиска решения. Так как нам необходимо сохранить найденный результат, то ставим флажок «Сохранить найденное решение», в результате чего на нашем листе сохранится найденное решение. Нажмем кнопку «ОК».

В ячейках $D$19:$Н$22 появляются подобранные системой значения, при которых в ячейке I23 формируется минимальное значение стоимости транспортных услуг — 35 000 руб.

В данном случае отклонения от подобранного нами вручную результата составляют лишь 2,2 %, или 790 руб., но это означает лишь то, что мы вручную удачно подобрали решение.

На рис. 3 представлены полученные при оптимизации данные.

Рис. 3. Результаты оптимизации

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

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

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

По этому случаю рассмотрим пример с другими исходными данными.

Пример 2

В ячейках J8:J11 запишем формулу, позволяющую округлить вычисляемые значения до целого числа, которая имеет вид:

Ячейка J8: =ОКРУГЛ(I8*$H$13;0).

Протащим (скопируем) эту формулу в ячейки J9:J11.

Как видим (рис. 4) плановое количество ездок в ячейке Н13 отличается от суммы в ячейках J8:J11, записанной в ячейке J12: значение в ячейке J12, полученное в результате суммирования округленных результатов расчетов числа ездок в ячейках J8:J11, не равно значению в ячейке Н13, полученному суммированием планового числа ездок до пунктов назначения. Это издержки примененной функции округления. Для того чтобы избежать данной ошибки, проделаем следующую процедуру. Для контроля и удобства вычислений введем проверочную ячейку J13. Формула в этой ячейке будет представлять собой разницу полученных значений в ячейках J12 и H13. Ячейка понадобится нам для коррекции вычислений.

Рис. 4. Пример с функцией округления расчетного числа ездок до целого числа

Используем для коррекции инструмент «Подбор параметра». Процедура подбора иллюстрируется на рис. 5.

Рис. 5. Использование инструмента «Подбор параметра»

Здесь необходимо применить следующую схему: подобрать в ячейке J13 значение равное 0, изменяя значение в ячейке I9 (доля в перевозках). Предварительно в ячейку I11 целесообразно ввести следующую формулу:

=100 % – I8 – I9 – I10.

Тогда при изменении значения в ячейке I9 в результате применения инструмента «Подбор параметра» автоматически изменится и значение в ячейке I11.

Так как в ячейках I8:I11 применено округление до целого значения, изменения в ячейках I9 и I11 на 0,25 % не обнаруживаются. Эти изменения будут видны, если мы добавим знаки после запятой.

Результат применения инструмента «Подбор параметра» приведен на рис. 6 (на с. …).

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

D19:H22 = целое,

D12:H12 = D18:H18,

J8:J11 = I19:I22.

Сумма транспортных расходов в ячейке I23 на рис. 6 рассчиталась равной 49 540 руб., а наша задача — минимизировать ее.

Рис. 6. Скорректированный вариант

На рис. 7 представлена демонстрация использования инструмента «Поиск решений» для оптимизации результата.

Рис. 7. Минимизация значения в ячейке I23 с помощью надстройки «Поиск решения»

На рис. 8 в ячейках D19:Н22 представлены данные, полученные в ходе оптимизации с помощью инструмента «Поиск решений». В результате минимизации в ячейке I23 получено значение 44 990 руб. Отклонения от достигнутого при первоначальном распределении результата составило 9 %, или 4550 руб.

Возможно, имеет право на существование такой вопрос: «Для чего нужна табличная часть со стоимостями перевозок в разрезе транспортных компаний и пунктов назначений?». Ведь можно было бы просто в итоговую ячейку I23 ввести формулу:

=СУММПРОИЗВ(D8:D11;D19:D22)+СУММПРОИЗВ(E8:E11;E19:E22)+СУММПРОИЗВ(F8:F11;F19:F22)+СУММПРОИЗВ(G8:G11;G19:G22)+СУММПРОИЗВ(H8:H11;H19:H22).

Следует помнить следующее:

• строк и столбцов может быть достаточно большое количество. Это значит, что написание самой формулы в ячейках будет слишком трудоемкой задачей;
• потеряется возможность анализа данных по компаниям и пунктам назначения. Значит, целесообразнее использовать вспомогательную таблицу (в нашем примере это ячейки B23:I27), которая содержала бы множество простых формул. Эти формулы записываются всего в два мгновения: запись в одной из ячеек и копирование или протаскивание в остальные ячейки. Такая таблица несет в себе полезную для анализа информацию о стоимости транспортных услуг в разрезе перевозчиков и пунктов назначения.

Рис. 8. Результат оптимизации примера с округлением

Заключение

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

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

О. С. Илюшина, ведущий экономист ЗАО «Спецнефтетранс»