Шаг 306.
VBA в MSExcel. Модель управления затратами. Создание модели анализа ... при различных соотношениях исходных данных затраты/прибыль (окончание)

    На этом шаге мы рассмотрим некоторые вопросы, связанные с созданием макросов, автоматизирующих процессы подбора параметров.

Режим увеличения объема продаж

    К следующему элементу управления по изменению уровня объема продаж предъявлены похожие требования:

    Отличие элемента управления изменения уровня объема реализации в том, что он помещает результат в ячейку I10 и ему назначен макрос УвеличениеПродаж (рисунок 1).


Рис.1. Подпрограмма УвеличениеПродаж, переключающая модель в режим увеличения объема продаж и подбора требуемого уменьшения объема затрат для достижения того же результата

    Перед записью макроса введите формулы в ячейки С6, С9 и С14:

  =I5
  =I10/100
  =(C6-C16)/C6
и в ячейку F19 введите текст Режим изменения объема продаж.

    Подпрограмма несколько отличается от предыдущей, но алгоритм ее записи и редактирования не отличается. Описание последовательности выполнения действий подпрограммой УвеличениеПродаж:

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

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

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

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

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


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

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

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

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


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

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

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

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

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

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

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




Предыдущий шаг Содержание Следующий шаг