На этом шаге мы рассмотрим алгоритм выполнения корректировки таблицы.
В связи с тем, что для последующего анализа поведения затрат объем реализации в диапазоне ячеек D8:G8 будет изменяться, зафиксируем его и цену ее реализации в области ячеек D1:G2 (рисунок 1).
Рис.1. Таблица для исследования поведения затрат с зафиксированными значениями исходных данных: объем и цена реализации
Для этого скопируйте диапазон ячеек D8:G9 и, выделив ячейку D1, произведите вставку.
Для последующего обновления исходных данных в ячейках D8:G8 напишите макрос ВводИсходныхДанных (рисунок 2).
Рис.2. Подпрограмма обновления исходных данных
Конечно, наиболее простой вариант заключался бы в копировании диапазона D1:G2 и последующей вставке в таблицу. Но если обновление исходных данных потребуется задать, находясь на другом рабочем листе, то потребуется написать строки кода VBA для перехода на этот рабочий лист и возврата в исходный. Это не совсем удобно. Исходя из этого, подпрограмма (рисунок 2) производит присвоение значений ячейкам с исходными данными, не переходя на рабочий лист РаспределениеЗатрат.
Поведение затрат отдельного продукта в составе общих затрат
Для анализа поведения различных затрат и определения точки безубыточности на выпускаемую продукцию в составе всей продукции выпускаемым заводом, произведите вставку нового рабочего листа ТаблицаЗавод (рисунок 3).
Рис.3. Рабочий лист ТаблицаЗавод
Анализ проведем на примере продукции линии производства стандартных бутылок.
Методология создания таблицы будет отличаться от создания подобной таблицы на листе Анализ. Таблица расположена в области В1:F50. В верхней части ее в диапазоне ячеек В1:F1 последовательно слева направо введите формулы ссылки на ячейки рабочего листа РаспределениеЗатрат:
=РаспределениеЗатрат!F8
=РаспределениеЗатрат!F12
=РаспределениеЗатрат!F16+РаспределениеЗатрат!F18
=РаспределениеЗатрат!F28
=РаспределениеЗатрат!F30
В ячейку А1 введите шаг, с которым будет изменяться объем реализации продукции. В ячейку F8 рабочего листа РаспределениеЗатрат введите ссылку на ячейку А2 рабочего листа ТаблицаЗавод:
=ТаблицаЗавод!A2
Подпрограмма ЦиклТаблица является отдельным элементом подпрограммы для создания таблицы (рисунок 4).
Рис.4. Подпрограмма ЦиклТаблица
Его задачей является:
- присвоение переменной Row номера определенной первой пустой строки;
- копирование диапазона ячеек B1:F1;
- вставка значений ячеек скопированного диапазона в первой пустой строке в ячейку столбца В;
- увеличение значения ячейки А2 на шаг, заданный в ячейке А1.
Подпрограмма ЦиклТаблица, в свою очередь, входит в состав другой подпрограммы - НачальныеУсловия (рисунок 5), задачей которой является ввод исходных данных на листе РаспределениеЗатрат, а также очистка содержимого области таблицы и ввод формул, с помощью которых будет создаваться таблица поиска точки безубыточности отдельного продукта составе всей продукции выпускаемым заводом.
Рис.5. Подпрограмма ввода начальных условий для создания таблицы
Подпрограмма формирования таблицы отличается от всех созданных ранее своей специфичностью (рисунок 6).
Рис.6. Подпрограмма ТочкаИзделиеЗавод
Прежде чем приступить к написанию подпрограммы, опишем функции которые она должна выполнять:
- создаваемая таблица необходима только для одного - получения графика поведения затрат и определения точки безубыточности графическим путем;
- по мере формирования таблицы, а, следственно, и графика, желательно наблюдать весь процесс формирования кривых этого графика;
- для наблюдения за процессом формирования графика, желательно, чтобы он находился в центре окна Excel, и на экране не должны присутствовать лишние элементы рабочего листа.
Опишем процедуры выполняемые макросом.
Первая строка кода VBA предназначена для перевода объекта Excel (Application) в полноэкранный режим и для ее записи выполните команду Вид | Во весь экран. Тогда свойству DisplayFullScreen (экран) присваивается значение True.
В связи с тем, что формулы в ячейках диапазона D1:F1 должны обновляться после каждого изменения значения объема реализации в ячейке А2, вторая строка кода VBA переводит Excel в автоматический режим вычислений.
Следующие две строки изменяют ширину столбцов ColumnWidth и высоту строк RowHeight до указанного в подпрограмме размера. Это необходимо для того, чтобы диаграмма (рисунок 7), расположенная справа от таблицы, занимала большее место.
Рис.7. График поведения затрат и точка безубыточности по одному изделию в составе завода
Скрыть эти столбцы (строки) нельзя, потому что данные, находящиеся в скрытых областях, не отображаются на диаграмме. Для записи этих строк кода VBA последовательно выполните следующие действия:
- выделите столбцы A:F и выполните команду Формат | Столбец | Ширина и в диалоговом окне Ширина столбца в поле Ширина столбца введите значение;
- выделите строки 14:50 и выполните команду Формат | Строка | Высота и в диалоговом окне Высота строки в поле Высота строки введите значение;
- после механической записи макроса отредактируйте код.
Скопируйте наименование подпрограмм НачальныеУсловия и ЦиклТаблица и вставьте в тело макроса, причем текст имени ЦиклТаблица - 48 раз. Код повторения выполнения подпрограммы ЦиклТаблица записывается довольно быстро? но не является корректным с позиции программирования, и это может быть как один из вариантов быстрой записи повторяющихся операций. Пока этот цикл будет выполняться, пользователь может наблюдать весь процесс формирования графиков на диаграмме. Своего рода мультфильм, показывающий процесс создания графика.
После создания графика следующая строка кода VBA присваивает свойству DisplayFullScreen объекта Excel значение False, и для записи этой строки выполните команду Вид | Во весь экран.
В завершение в подпрограмме задайте столбцам и строкам их прежние размеры. Для восстановления высоты строки выполните ту же последовательность действий что и ранее при их изменении, а ширина столбцов восстанавливается их выделением и двойным нажатием на правую клавишу мыши по краю границы наименования столбца. В таком случае свойству EntireColumn присваивается значение автоматического подбора ширины (AutoFit).
На следующем шаге мы продолжим изучение этого вопроса.
