Программное построение диаграмм с доверительными интервалами и трендами
Как это все программируется? В свое время мы рассказали о Chart-объектах, их свойствах и методах, позволяющих программно строить диаграммы. Сейчас мы на примере покажем объекты, используемые при выводе доверительных интервалов и трендов. Например, эта процедура строит доверительный интервал:
Sub ДоверительныеИнтервалы() 'Построение доверительных интервалов на диаграммме Dim myChart As Chart Dim mySeries As Series Set myChart = ThisWorkbook.Worksheets("Лист5").ChartObjects(1).Chart Set mySeries = myChart.SeriesCollection(1) mySeries.Select mySeries.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, _ Type:=xlErrorBarTypeStDev, Amount:=1 mySeries.ErrorBars.Border.Color = RGB(255, 0, 0) myChart.ChartArea.Select End Sub
Как видите, я создаю объект Chart, выделяя соответствующий ChartObject объект, встроенный в рабочий лист. Затем создаю объект класса Series, задающий ряд данных. В нашем примере диаграмма включает только один ряд, так что коллекция SeriesCollection содержит единственный элемент.
Для элемента класса Series вызывается метод ErrorBar, который и строит доверительный интервал. Значение xlErrorBarIncludeBoth параметра Include указывает на необходимость построения двухсторонних границ интервала погрешностей, а тип ошибок задается параметром Type, параметр Amount задает количество единиц в интервале погрешностей. Чем шире интервал, тем выше вероятность попадания истинного значения в указанный интервал. Доверительный интервал выделяется заданным цветом.
Следующая процедура демонстрирует построение тренда:
Sub ЛинейныйТренд() 'Построение трендов на диаграммме Dim myChart As Chart Dim mySeries As Series Set myChart = ThisWorkbook.Worksheets("Лист5").ChartObjects(1).Chart Set mySeries = myChart.SeriesCollection(1) mySeries.Select mySeries.Trendlines.Add(Type:=xlLinear, Forward:=3, _ Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select mySeries.Trendlines.Add(Type:=xlPolynomial, Order:=3, Forward:=3, _ Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
myChart.ChartArea.Select End Sub
На одной диаграмме можно построить несколько трендов различного типа. Поэтому в состав объекта Series входит коллекция TrendLines, элементы которой создаются при вызове метода Add. Параметр Type задает один из 6 возможных типов тренда, параметры Forward и Backward задают интервалы прогнозируемых значений. Соответственно прогноз делается вперед и/или назад. Булев параметр DisplayEquation включает вывод уравнения регрессии в окне диаграммы.
Процедура строит на одной диаграмме два тренда - линейный и полиномиальный. Заметьте, для полиномиального тренда задается дополнительный параметр Orde, определяющий степень аппроксимирующего полинома.
Вот как выглядит лист с диаграммой, после того, как отработали рассмотренные нами процедуры:
увеличить изображение
Рис. 8.29. Программно построенные доверительные интервалы и тренды
Но вернемся к менеджеру "РР". Визуальный анализ данных показал, что, вряд ли, результаты продаж хорошо согласуются с моделью линейного или полиномиального тренда. Менеджер просмотрел все виды трендов: ни один из них не учитывал в полной мере характер поведения данных. Один из них при прогнозе дает слишком пессимистическую оценку, другой - излишне оптимистическую. Полином третьей степени неплохо описывает поведение данных, но только на интервале наблюдения. Использовать его для целей прогноза, очевидно, невозможно. Увы, такая ситуация типична. Модель, особенно полиномиальная, может хорошо описывать наблюдаемые значения, но не годиться для прогноза.
Дело здесь не в том, что используемые для прогноза функции плохи, дело в самой модели. Заметьте, на построенной диаграмме продажи строятся как функция от времени и никак не учитываются другие факторы, которые влияют на продажи. Я напомню, что менеджер при построении уравнения регрессии понимал это и строил это уравнение, как функцию, зависящую от трех факторов - времени, уровня рекламы, числа конкурирующих книг. Он понимал, что модель, используемая для прогноза, должна учитывать все факторы, оказывающие существенное влияние на прогнозируемую величину.
Приведенный здесь визуальный анализ продаж, как функции от времени, способен скорее убедить менеджера в том, что на уровень продаж влияет не только время, но и другие факторы. Оставим менеджера в размышлениях, а сами пока познакомимся еще с некоторыми стандартными средствами Office 2000, связанными с анализом "Что, если ...?".
myChart.ChartArea.Select End Sub
На одной диаграмме можно построить несколько трендов различного типа. Поэтому в состав объекта Series входит коллекция TrendLines, элементы которой создаются при вызове метода Add. Параметр Type задает один из 6 возможных типов тренда, параметры Forward и Backward задают интервалы прогнозируемых значений. Соответственно прогноз делается вперед и/или назад. Булев параметр DisplayEquation включает вывод уравнения регрессии в окне диаграммы.
Процедура строит на одной диаграмме два тренда - линейный и полиномиальный. Заметьте, для полиномиального тренда задается дополнительный параметр Orde, определяющий степень аппроксимирующего полинома.
Вот как выглядит лист с диаграммой, после того, как отработали рассмотренные нами процедуры:
увеличить изображение
Рис. 8.29. Программно построенные доверительные интервалы и тренды
Но вернемся к менеджеру "РР". Визуальный анализ данных показал, что, вряд ли, результаты продаж хорошо согласуются с моделью линейного или полиномиального тренда. Менеджер просмотрел все виды трендов: ни один из них не учитывал в полной мере характер поведения данных. Один из них при прогнозе дает слишком пессимистическую оценку, другой - излишне оптимистическую. Полином третьей степени неплохо описывает поведение данных, но только на интервале наблюдения. Использовать его для целей прогноза, очевидно, невозможно. Увы, такая ситуация типична. Модель, особенно полиномиальная, может хорошо описывать наблюдаемые значения, но не годиться для прогноза.
Дело здесь не в том, что используемые для прогноза функции плохи, дело в самой модели. Заметьте, на построенной диаграмме продажи строятся как функция от времени и никак не учитываются другие факторы, которые влияют на продажи. Я напомню, что менеджер при построении уравнения регрессии понимал это и строил это уравнение, как функцию, зависящую от трех факторов - времени, уровня рекламы, числа конкурирующих книг. Он понимал, что модель, используемая для прогноза, должна учитывать все факторы, оказывающие существенное влияние на прогнозируемую величину.
Приведенный здесь визуальный анализ продаж, как функции от времени, способен скорее убедить менеджера в том, что на уровень продаж влияет не только время, но и другие факторы. Оставим менеджера в размышлениях, а сами пока познакомимся еще с некоторыми стандартными средствами Office 2000, связанными с анализом "Что, если ...?".
