Всем привет! 👋
Сегодня рассмотрим прием позволяющий изменять фон диаграммы в зависимости от значения линии тренда.
📢 Файл с примером кода размещен в конце статьи 🔽
▶️ Линии тренда в Excel
- показывают направление и характер тренда данных на диаграмме. Они используются для визуального анализа данных и выявления возможных закономерностей или трендов, которые могут быть скрыты в данных.
Линия тренда может быть добавлена к различным типам диаграмм в Excel, включая графики рассеяния, графики с областями, графики с линиями и столбчатые графики. Она представляет собой линию, которая проходит через точки данных на диаграмме, наиболее точно отображая тренд этих данных.
Линии тренда могут быть линейными, полиномиальными, экспоненциальными или другими типами, в зависимости от выбранной математической модели для анализа данных.
Добавление линии тренда выполняется путем выбора диаграммы, затем открытия вкладки "Дизайн" или "Добавление элементов графика" и выбора опции "Линия тренда". Затем можно настроить тип линии тренда, его параметры и отображение на графике
В данном примере применен линейный тренд:
Задача: задать форматирование фона диаграммы в зависимости от направления линии тренда.
▶️ Наклон тренда
Функция НАКЛОН в Excel используется для вычисления наклона (коэффициента наклона) линии тренда между указанными данными. Формула =НАКЛОН(диапазон_значений_X; диапазон_значений_Y) принимает два аргумента: диапазон значений X и диапазон значений Y.
Наклон линии тренда показывает, каким образом значения Y изменяются в зависимости от значений X. Положительное значение наклона указывает на положительную корреляцию между X и Y, тогда как отрицательное значение наклона указывает на отрицательную корреляцию. Значение близкое к нулю может указывать на отсутствие или слабую корреляцию между переменными.
В данном случае, формула =НАКЛОН(C3:C13; B3:B13) вычисляет наклон линии тренда между значениями в диапазоне C3:C13 (значения Y) и значениями в диапазоне B3:B13 (значения X). Результатом функции будет числовое значение, представляющее коэффициент наклона линии тренда.
▶️ Макрос
Данный код представляет собой процедуру Worksheet_Calculate, которая выполняется каждый раз, когда происходит пересчет листа. Процедура изменяет цвет графика в зависимости от значения ячейки C15.
📝 Описание кода:
- В первой строке объявляются переменные myColor (тип Long) и myChart (тип String).
- С помощью Application.EnableEvents = False отключается перехват событий, чтобы избежать бесконечного цикла пересчета.
- С помощью условия If ActiveSheet.Name <> "Пример" Then Exit Sub проверяется, что код выполняется только на определенном листе с названием "Пример". Если это условие не выполняется, процедура завершается с помощью Exit Sub.
- Присваивается значение "Chart 1" переменной myChart, которая указывает на название графика.
- С помощью условия If Range("c15").Value > 0 Then и ElseIf Range("c15").Value < 0 Then определяется значение переменной myColor в зависимости от значения ячейки C15. Если значение положительное, то myColor будет равен RGB(135, 235, 145) (зеленый цвет), если отрицательное, то myColor будет равен RGB(250, 190, 145) (оранжевый цвет). В противном случае, когда значение равно 0, myColor будет равен RGB(255, 255, 255) (белый цвет).
- Активируется график с помощью ActiveSheet.ChartObjects(myChart).Activate.
- Задается цвет области графика с помощью блока With ActiveSheet.Shapes(myChart).Fill и свойства .ForeColor.RGB.
- Задается цвет области построения графика с помощью блока ActiveChart.PlotArea.Select и свойства Selection.Format.Fill.
- С помощью Application.EnableEvents = True включается перехват событий.
▶️ Результат
- нисходящий тренд
- восходящий тренд
