Анализ деятельности офиса

Программное построение диаграмм с доверительными интервалами и трендами

Как это все программируется? В свое время мы рассказали о 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, связанными с анализом "Что, если ...?".

Таблицы подстановок, Сценарии и Поиск решения

Таблица подстановок - одно из средств анализа данных. Вот первая задача, которая приводит к построению таблицы подстановок. Рассмотрим набор функций, зависящих от одного и того же параметра: F1(a), F2(a), …Fm(a). Пусть каждая из этих функций задается формулой Excel. Пусть также требуется проанализировать зависимость этих функций от значений параметра a. Обычно нас интересуют результаты для конечного набора значений параметра - a1, a2, …an. В этом случае все, что нужно для анализа, - это построить прямоугольную таблицу размерности n*m, элементами которой будут значения Fj(ai). Excel позволяет без особого труда построить такую таблицу. Таблицы подстановок упрощают решение этой задачи.

Чтобы вручную построить такую таблицу, надо записать в столбец значения ai, в строку, расположенную на одну ячейку выше и правее, записать формулы Fj(Ain). Все формулы должны ссылаться на одну и ту же ячейку Ain - ячейку ввода. Можно, конечно, значения параметра записать в строку, а формулы в столбец. Основное требование к расположению формул и значений параметра состоит в том, чтобы они определяли прямоугольную область таблицы и воспринимались как заголовки ее строк и столбцов. Проделав эту подготовительную работу, достаточно выделить прямоугольную область, занятую таблицей, включая заголовки, и выбрать в меню "Данные" пункт "Таблица подстановок". В появившемся окне нужно задать ссылку на ячейку ввода. Заметьте, если значения параметра располагаются в столбец, то ссылку на ячейку ввода нужно задавать в окне строк, а не в окне столбцов. По щелчку кнопки OK таблица значений Fj(ai) будет автоматически построена.

Другая задача, приводящая к таблице подстановок, состоит в том, что рассматривается только одна функция F(a,b), но теперь зависящая от двух параметров. Элементами таблицы являются значения этой функции F(ai, bj). В роли заголовков строк и столбцов выступают значения ai и bj. Для записи формулы осталось одно свободное место - ячейка в левом верхнем углу таблицы. Формула, записанная в нее, ссылается теперь на две ячейки ввода - ячейку ввода строки и ячейку ввода столбца. Этим нюансом в расположении заголовков отличаются подготовительные действия по созданию таблицы подстановок в первом и во втором случае. Остальные действия аналогичны. И здесь нужно быть аккуратным в выборе окон при задании ссылок. Мне, например, всегда хочется задавать ссылки в другом порядке.

Конечно, таблицы подстановки - не столь уж мощное средство анализа данных. Собственно говоря, никакого анализа данных они не выполняют. Это лишь часто используемое средство, облегчающее построение таблицы данных для ее визуального анализа. Таблицу анализирует сам пользователь, исходя из содержательных соображений. Обычно, только он может понять, какие значения параметров a и b наиболее подходят для его целей. Хотя, конечно, может существовать и формальный алгоритм выбора из таблицы наилучшего значения.

А теперь покажем, как менеджер офиса использует таблицу подстановки в своем анализе. Ранее он построил уравнение регрессии, в котором продажи зависят от уровня рекламы и количества конкурирующих книг. Менеджер уже использовал его для прогноза состояния продажи книги и даже построил соответствующие графики, демонстрирующие прогнозируемую зависимость продаж от уровня рекламы. Однако графики были построены для фиксированного числа конкурирующих книг. Менеджер не определил еще окончательно, каков будет уровень рекламы в точке прогноза, и тем более он не знает точного значения количества конкурентов. Поэтому он решает построить таблицу подстановки, чтобы оценить все реально возможные варианты. Вот что у него получилось:

увеличить изображение
Рис. 8.30. Таблица подстановки, используемая в анализе "Что, если ...?"

Эта таблица позволяет ему получить ответы на вопросы: каков прогноз на продажу книг на следующий месяц и что будет, если появится новый конкурент, и что будет, если повысить уровень рекламы?

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

Ну и несколько слов о том, как построить таблицу подстановки программно. Если подготовительная работа уже выполнена, - созданы заголовки таблицы и записана формула, вычисляющая функцию от двух параметров, то дальнейшее построение таблицы подстановки выполняется одним оператором. Достаточно выделить соответствующую прямоугольную область под таблицу и вызвать метод Table объекта Selection или Range. Метод имеет два параметра, задающих ячейки ввода.

Вот процедура, которая на другом месте строит таблицу подстановки, аналогичную той, которая показана на предыдущем рисунке 8.30:

Public Sub Buildtable()
	'Программное построение таблицы подстановки
	Dim myr As Range
	Set myr = Range("A1:H7")
	myr.Clear
	'Построение заголовков таблицы подстановки
	Range("E1") = "Уровень рекламы"
	Range("A5") = "Число конкурентов"
	Range("C2") = 0: Range("D2") = 1
	Range("C2: D2").AutoFill Destination:=Range("C2:H2"), Type:=xlFillDefault
	Range("B3") = 0: Range("B4") = 1
	Range("B3: B4").AutoFill Destination:=Range("B3:B7"), Type:=xlFillDefault
	Range("B2").Formula = "= 8.318*$B$13 + 16.66*$A$1-26.58*$A$2 +109.06"
	'Формирование таблицы
	Set myr = Range("B2:H7")
	myr.Table RowInput:=Range("A1"), ColumnInput:=Range("A2")

End Sub