Со второй частью цикла можно ознакомиться здесь.
Вкратце в предыдущих частях мы формировали такой сложный корпоративный отчёт, как PnL:
Мы определились с единым форматом исходных данных. Формат получился простым и универсальным. Научились извлекать все необходимые данные из этого форматированного источника. Научились динамически формировать отчёт во всех необходимых разрезах.
Цель этого этапа
На данном этапе мы будем строить графики по отчёту PnL. Моя цель не поразить вас их красотой, а показать, как на основе сложного отчёта можно динамически, программно формировать диаграммы. Данный материал предназначен для людей, которые обладают базовыми знаниями VBA. Кроме того, это даже не совсем учебный материал, а именно демонстрация наиболее продвинутого подхода к решаемой задаче, поэтому в статье не будет подробных объяснений, но я остановлюсь на ключевых моментах. Кроме того, исходный код VBA снабжен комментариями.
Скачать пример
Отсюда. Должен сразу оговориться, что файл корректно отрисовывает метки данных только в Excel 2013. В более ранних версиях (2007/2010) с этим есть серьёзные проблемы, решать которые не входит в цель данной статьи. Подробнее об этой проблеме сказано в конце статьи.
Компоненты решения
Все диаграммы строятся на листе Charts. Всего там 4 равноценных диаграммы.
Управление тем, что отображается на диаграммах, осуществляется пользователем при помощи специальной формы. Форма открывается по кнопке Show form.
На любой диаграмме может быть построен любой график. График строится на основе комбинации из 4-х параметров, суть которых хорошо видна на форме и должна быть вам понятна по предыдущим статьям, поэтому я не буду подробно на этом останавливаться.
Графики формируются на основе данных листа PnL. Однако на листе REF есть 2 вспомогательные таблицы, которые помогают извлекать из отчёта PnL данные. Первая таблица (именованный диапазон rngChartColRef) описывает столбцы таблицы PnL, вторая (rngChartRowRef) - строки.
Ещё одна вспомогательная таблица (rngChartStatus) на листе REF предназначена для сохранения настроек диаграмм, сделанных при помощи формы.
При каждом изменении элемента управления пользовательского интерфейса на форме запускается перестроение текущей диаграммы. В зависимости от выбранных флажков на форме, макрос считывает соответствующие строки во вспомогательных таблицах rngChartColRef и rngChartRowRef и по ним извлекает данные с листа PnL. Кроме этого форматируется и сама диаграмма в части настройки правильного отображения подписей данных.
Код, который я написал, на 100% корректно работает только в Excel 2013. В Excel 2007/2010 возникает ошибка на строке "Selection.Format.TextFrame2.WordWrap = msoFalse" и если её закоментировать, то 2007-й вообще не отрисует никакие метки данных, а 2010-й будет переносить строки в метках и выглядеть это будет не очень хорошо. К сожалению, эта проблема не имеет простого решения и является недостатком указанных версий Excel.
Читайте также:
| 
