На этом шаге мы рассмотрим некоторые вопросы, связанные с созданием макросов, автоматизирующих процессы подбора параметров.
Режим увеличения объема продаж
К следующему элементу управления по изменению уровня объема продаж предъявлены похожие требования:
- изменение содержимого ячейки С9 с шагом 1% и 10%;
- начальное (минимальное) значение 0%;
- максимальное значение 250%;
- быстрое перемещение в начальное или конечное значение, а также в любую точку выбранного диапазона;
- автоматический подбор параметра (значения) в ячейке С14, показывающий на сколько процентов необходимо уменьшить уровень издержек, чтобы достичь такого же уровня прибыли при любом исходном соотношении продажи/затраты от нуля до 100%;
- формирование текста - в состоянии какого режима расчета находится созданная модель.
Отличие элемента управления изменения уровня объема реализации в том, что он помещает результат в ячейку I10 и ему назначен макрос УвеличениеПродаж (рисунок 1).
Рис.1. Подпрограмма УвеличениеПродаж, переключающая модель в режим увеличения объема продаж и подбора
требуемого уменьшения объема затрат для достижения того же результата
Перед записью макроса введите формулы в ячейки С6, С9 и С14:
=I5 =I10/100 =(C6-C16)/C6
Подпрограмма несколько отличается от предыдущей, но алгоритм ее записи и редактирования не отличается. Описание последовательности выполнения действий подпрограммой УвеличениеПродаж:
- проверить содержимое ячейки F19 и, если в нее введен текст Режим изменения уровня издержек, то выполнить подпрограмму ИсходноеСостояние, которая обновит все формулы таблицы (приведет в исходное состояние) и очистит содержимое ячейки F19 с введенным текстом. После этого переходит к выполнению следующей строки кода VBA;
- если же в ячейке F19 не содержится ничего или введены другие данные, то перейти к выполнению следующей строки, игнорируя выполнение подпрограммы УвеличениеПродаж;
- ввести в ячейку F19 текст Режим изменения уровня издержек;
- в ячейку С6 ввести ссылку на ячейку I6 (Заданный уровень затрат в выручке при исходном состоянии). Содержимое ячейки I6 изменяется при помощи следующего элемента управления и будет раскрыто далее;
- в ячейку С9 (Процент увеличения объема продаж) ввести формулу деления содержимого ячейки I10 на 100. Содержимое ячейки I10 изменяется с помощью элемента управления Полоса прокрутки;
- в ячейку С14 (Процент изменения уровня затрат) вводится формула, производящая вычитание значения затрат, полученное после изменения издержек (ячейка С16) из значения затрат в исходном состоянии (ячейка С6), после чего результат делится на сумму затрат исходного состояния (ячейка С6);
- значению (Value) в ячейке С16 присваивается прежнее значение этой же ячейки, которое находилось в ней до выполнения подпрограммы за минусом значения, возвращаемого формулой в ячейке С21, которая сравнивает полученную дополнительную прибыль от изменения уровня объема продаж. Обратите внимание: в ячейку С16 вводится не формула, а значение. Это объясняется тем, что в ячейку нельзя вводить формулу, которая бы ссылалась на саму себя. И далее, на ячейку С16 присутствует ряд ссылок в формулах других ячеек, вводимых с помощью кнопки ИсходноеСостояние. Поэтому, в конечном итоге, значение ячейки С16 обновляет значение, возвращаемое формулой в ячейке С20, в результате чего результат вычисления в ячейке С21 равен нулю, что говорит о правильном подборе параметра.
Таким образом, изменяя при помощи элемента управления значение увеличения объемов продаж на определенный процент по сравнению с исходными данными, в ячейке С14 будет виден уровень, на который необходимо уменьшить затраты, чтобы достичь такого же увеличения дополнительно полученной прибыли.
Режим изменения пропорций затраты/прибыль в исходных данных
Проанализируем работу подпрограмм, назначенных двух первым элементам Полоса прокрутки. Формулы, которыми они оперируют и последовательность их выполнения, полностью зависит от вводимых формул при первоначальном создании таблицы. При выборе другого алгоритма, эти подпрограммы манипулировали бы другими данными и другой последовательностью их изменения.
Преимущество подпрограммы УвеличениеПродаж перед УменьшениеЗатрат заключается в том, что при ее запуске она принимает во внимание не вводимые числовые значения соотношения затраты/прибыль, а установленное соотношение при помощи третьего элемента управления, регулирующего эти пропорции.
Созданные две подпрограммы имеют один общий недостаток: они требуют проведения перерасчета формул практически после каждой строки кода. И поэтому, если в Excel будет установлен ручной режим перерасчета формул, то модель не сможет произвести вычисления и подобрать требуемые параметры. Для перевода Excel в автоматический режим вычислений выполните команду Сервис | Параметры и в открывшемся диалоговом окне Параметры (рисунок 2) на вкладке Вычисления активизируйте переключатель Автоматически в области Вычисления.
Рис.2. Диалоговое окно Параметры, вкладка Вычисления
Методы управления вычислениями в Excel
В Excel существует три режима вычислений:
- в автоматическом режиме - происходит автоматический перерасчет всех зависимых формул при проведении любых изменений в исходной;
- в полуавтоматическом режиме - также вычисляются все формулы, кроме тех, которые находятся в табличных базах данных;
- в ручном режиме вычислений - перерасчет формул происходит при наступлении одного из событий; нажатии на клавишу F9, нажатии на кнопку Вычислить на вкладке Вычисления (рисунок 2) или из подпрограммы VBA при вызове метода Calculate.
После перевода Excel в ручной режим вычислений созданные подпрограммы не выполнят возложенную на них миссию. Такая ситуация может быть и на практике. Чтобы исключить ее, запишите макрос выполнения процедуры перевода Excel в автоматический режим перерасчета формул. Для этого, после начала записи макроса, выполните команду вызова диалогового окна Параметры и активизируйте переключатель Автоматически (рисунок 2). Макрос зафиксирует все необходимые параметры вкладки Вычисления. Отредактируйте код VBA для получения только одной строки (первая строка кода на рисунке 3), в которой:
- Application - объект (сам Excel);
- Calculation - свойство объекта Application, которое может принимать значения: xlAutomatic, xlSemiautomatic и xlManual, соответствующие перечисленным режимам вычислений.
Рис.3. Подпрограмма ИзменитьПропорцииЗатратыПрибыль, переключающая модель в режим изменения параметра
затраты/прибыль в исходных данных и поддерживающий при этом автоматический подбор параметров как при изменении объема продаж, так и при
изменении уровня затрат, в зависимости от выбранного режима
Полученную строку кода затем скопируете в следующий макрос.
Подпрограмма изменения пропорций затраты/прибыль в исходных данных
Подпрограмма ИзменитьПропорцииЗатратыПрибыль (рисунок 3) изменения уровня пропорций затраты/прибыль в исходных данных создается практически только методом копирования отдельных фрагментов текста кода VBA из прежде созданных в предыдущих шагах подпрограмм.
До перехода к конструктору Select Case подпрограмма переводит Excel в режим автоматического вычисления и присваивает ячейке С6 значение ячейки I5 (Уровень затрат в получаемой выручке), которой управляет третий элемент управления, изменяющий значения в этой ячейке в диапазоне от нуля до 1000 с шагом 10 или 100.
Если ранее был активизирован один из режимов, о чем говорит введенный в ячейку F19 текст, то подпрограмма, на основании конструктора Select Case, выбирает три режима:
- Режим изменения объема продаж, который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование значением изменения объема продаж и последующим подбором параметра уменьшения издержек. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень повышения уровня продаж и производит подбор параметра изменения объема издержек, для получения такого же уровня прибыли. Для этого после первого элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УвеличениеПродаж;
- Режим изменения уровня издержек, который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование изменение значением объема издержек и последующим подбором параметра изменения объема продаж. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень изменения объема затрат и производит подбор параметра изменения объема продаж, для получения такого же уровня прибыли. Для этого после второго элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УменьшениеЗатрат;
- Без включения режима - изменяется соотношение исходных параметров затраты/прибыль без всякого подбора параметров.
На следующем шаге мы рассмотрим приближение созданной модели к пользовательскому интерфейсу.
