Автоматизация диаграммы

Условное форматирование таблицы данных

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

Формула условного форматирования сравнивает ячейку периода текущего столбца с определенной фактической датой ($B$7). Выбор управляющего периода можно сделать через организацию списка (диалог «Проверка данных»).

Автоматизация диаграммы

Теперь усложним задачу. Качественная финансовая модель в Excel включает в себя не только табличные данные, но и графические формы отображения результата – графики, диаграммы, спарк-лайны. Раскрасить диаграмму по частям можно вручную через диалог «Формат точки данных», выбрав предварительно единичный элемент ряда (клик на ряд, затем двойной клик на точку, блок или линию):

Очевидно, что перекрашивать таким образом участки диаграммы при сдвиге периода совсем не хочется. Используя VBA, можно автоматизировать эту рутинную операцию. Пример содержится в файле diag1.xlsm (после загрузки необходимо разрешить макросы).

Процедура перерисовки области диаграммы:

view source

print?

01.Option Explicit

02.

03.Public Sub RepaintChart1()

04. RepaintChart_ CLng(ThisWorkbook.Names("LastActualPeriod").RefersToRange.Value), shtMain.ChartObjects(1).Chart.SeriesCollection(1)

05.End Sub

06.

07.Private Sub RepaintChart_(nDate As Long, oChartRow As Excel.Series)

08. Dim XValues As Variant

09. Dim nPoint As Long

10. Dim isPlan As Boolean

11.

12. XValues = oChartRow.XValues

13.

14. For nPoint = 1 To oChartRow.Points.Count

15. isPlan = (XValues(nPoint) > nDate)

16.

17. oChartRow.Points(nPoint).Format.Fill.ForeColor.ObjectThemeColor = _

18. IIf(isPlan, msoThemeColorAccent2, msoThemeColorAccent1)

19. oChartRow.Points(nPoint).Format.Line.ForeColor.ObjectThemeColor = _

20. IIf(isPlan, msoThemeColorAccent2, msoThemeColorAccent1)

21. Next

22.End Sub

В качестве параметров в процедуру передается управляющая дата, а также ссылка на ряд диаграммы (объект Series коллекции SeriesCollection). Для удобства дата задана в рабочей области таблицы через именованную ячейку. Макрос обновления диаграммы можно вызывать в любой момент времени (например, через Alt+F8). В примере вызов макроса осуществляется по событию изменения данных на листе 1:

view source

print?

01.Option Explicit

02.

03.Private Sub Worksheet_Change(ByVal Target As Range)

04. Dim oCell As Excel.Range

05.

06. For Each oCell In Target.Cells

07. If oCell.Address = Me.Parent.Names("LastActualPeriod").RefersToRange.Address Then

08. RepaintChart1

09. End If

10. Next

11.End Sub

Дата добавления: 2015-07-15