Как автоматизировать решатель Excel

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

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

Как мы это сделаем

Мы будем использовать средство записи макросов в Excel для обработки большей части фактического кода. Инструмент макроса записи позволит Excel преобразовать наши ручные шаги в код VBA.

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

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

Настройка регистратора макросов

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

Снимок экрана ниже предназначен для Mac, но программа Excel для ПК аналогична.

Если вы не установили надстройку решателя, вы можете установить ее через меню надстроек. На сайте поддержки Microsoft есть инструкции для всех платформ. Загрузите надстройку Solver в Excel.

Запись макроса

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

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

Дайте вашему макросу имя и, если хотите, заполните описание.

Как только вы нажмете ОК, регистратор запустится.

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

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

Очистите код

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

Вы можете редактировать код с помощью редактора Excel VBA. Чтобы получить доступ к записанному коду, нажмите кнопку «Макросы».

Перейдите к своему макросу в новом меню и нажмите «Изменить».

Теперь, когда у нас открыт редактор кода, мы можем приступить к настройке.

Удаление ненужного кода

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

Настройка ссылок решателя

Последний шаг перед тем, как мы двинемся дальше, перейдите в Инструменты> Ссылки и выберите Решатель. Если вы этого не сделаете, пакет не загрузится в макрос, и вы получите сообщение об ошибке.

Теперь вы можете изменить свои параметры, открыть меню макросов, выбрать макрос, нажать «Выполнить», и он обновит результаты.

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

Затем мы выделим части, которые нужно изменить, и завершим нашу автоматизацию.

Промыть и повторить - построение петли

Теперь, когда у нас есть работающий решатель. Нам нужно повторить это несколько раз. Давайте соберем пример и закрепим план.

Мы начали с бюджетом в 1000 долларов в первой модели, но мы хотим сделать то же самое с шагом 100 долларов до 2500 долларов.

Давайте нырнем! У нас есть список значений в столбце B и наш результат в столбце C. Если мы выпишем процесс, он будет выглядеть так:

1. Задайте для общих расходов значение B10.
2. Решите модель.
3. Поместите вывод в C10.
4. Выберите следующее значение B.
5. Установите вывод в строке C.
6. Повторите шаги 2-5 для всех значений в B.

Запуск цикла

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

В этом примере мы определим конечную точку по количеству строк. B10: B25 имеет 16 значений.

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

Сброс решателя

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

Мы можем сделать это через SolverReset .

Обновление ссылок на ячейки

Теперь мы решаем шестнадцать раз, но нам нужно обновить ссылки на ячейки. Хотя мы изначально ссылались на B10, нам нужно перемещаться на одну ячейку вниз при каждом запуске, чтобы обновить пределы. Мы можем сделать это с помощью функции смещения. Мы можем обновить «$ B $ 10» для ссылки на ячейку, а затем смещение на i строк (0 при первом запуске, 1 строка при втором и т. Д.). Каждый раз при запуске цикла i увеличивается на единицу.

Сбор вывода

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

Мы вызовем C10, смещаем его на соответствующее количество строк, а затем вставим значение нашего вывода.

Обход уведомлений решателя

Избавим себя от головной боли и ни на минуту не будем учиться на примере. Помните, когда вы запускали решающую программу, и всплывало меню с вопросом, хотите ли вы принять решение? Это будет появляться каждый раз, если вы не отключите его. Представьте, что если вы настроите цикл со 100 значениями, вам придется каждый раз нажимать кнопку «Принять»!

Продолжайте и добавьте следующие строки после кода решателя и добавьте (TRUE) в SolverSolve. Это будет имитировать принятие вами изменений.

Теперь у нас есть готовый код!

Готово и работает

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

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

Заключение

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

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

Исходная дата публикации: 30.07.2019