Режим изменения пропорций затраты/прибыль в исходных данных

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

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

Созданные две подпрограммы имеют один общий недостаток — они требуют проведения перерасчета формул практически после каждой строки кода. И поэтому, если в Excel будет установлен ручной режим перерасчета формул, то модель не сможет произвести вычисления и подобрать требуемые параметры. Для перевода Excel в ручной режим вычислений выполните команду Сервис/Параметры и в открывшемся диалоговом окне Параметры (рис. 12.8.) на вкладке Вычисления активизируйте переключатель Вручную в области Вычисления.

Рис. 12.9. Диалоговое окно Параметры вкладка Вычисления

Методы управления вычислениями в Excel

В Excel существует три режима вычислений:

в автоматическом режиме — происходит автоматический перерасчет всех зависимых формул при проведении любых изменений в исходной;

в полуавтоматическом режиме — также вычисляются все формулы, кроме тех, которые находятся в табличных базах данных;

в ручном режиме вычислений — перерасчет формул происходит при наступлении одного из событий; нажатии на клавишу F9, нажатии на кнопку Вычислить на вкладке Вычисления (рис. 12.9.) или из подпрограммы VBA при вызове метода Calculate.

После перевода Excel в ручной режим вычислений созданные подпрограммы не выполнят возложенную на них миссию Такая ситуация может быть и на практике. Чтобы исключить ее, запишите макрос выполнения процедуры перевода Excel в автоматический режим перерасчета формул. Для этого, после начала записи макроса, выполните команду вызова диалогового окна Параметры и активизируйте переключатель Автоматически (рис. 12.9.). Макрос зафиксирует все необходимые параметры вкладки Вычисления. Отредактируйте код VBA для получения только одной строки (первая строка кода на рис. 12.10.), в которой:

• Application — объект (сам Excel);

• Calculation — свойство объекта Application, которое может принимать значения: xlAutomatic, xlSemiautomatic и xlManual, соответствующие перечисленным режимам вычислений

Полученную строку кода затем скопируете в следующий макрос.

Подпрограмма изменения пропорций затраты/прибыль в исходных данных

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

До перехода к конструктору Select Case, подпрограмма переводит Excel в режим автоматического вычисления и присваивает ячейке С6 значение ячейки I5 (уровень затрат в получаемой выручке), которой управляет третий элемент управления, изменяющий значения в этой ячейке в диапазоне от нуля до 1000 с шагом 10 или 100.

Если ранее был активизирован один из режимов, о чем говорит введенный в ячейку F19 текст, то подпрограмма на основании конструктора Select Case выбирает три режима:

Режим изменения объема продаж — который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование значением изменения объема продаж и последующим подбором параметра уменьшения издержек. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень повышения уровня продаж и производит подбор параметра изменения объема издержек, для получения такого же уровня прибыли. Для этого после первого элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УвеличениеПродаж;

Режим изменения уровня издержек — который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование изменение значением объема издержек и последующим подбором параметра изменения объема продаж. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень изменения объема затрат и производит подбор параметра изменения объема продаж, для получения такого же уровня прибыли. Для этого после второго элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УменьшениеЗатрат;

Без включения режима — изменяется соотношение исходных параметров затраты/прибыль без всякого подбора параметров.

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