Diagrammy_v_Excel_Dzhon_Uokenbakh_2003

ПОЛИНОМИАЛЬНЫЙ ТРЕНД ВТОРОГО ПОРЯДКА

Этот тренд (называемый также квадратичным трендом) описывает данные, кривая графика которых напоминает латинскую букву U (или перевернутую U). Уравнение полиномиального тренда второго порядка имеет вид

у = (с2 * х^2) + (с1 * х^1) +Ь

Обратите внимание: в уравнении присутствуют два коэффициента с (по одному для каждой степени х).

На рис. 5.14 показана диаграмма с полиномиальным трендом второго порядка. Формулы, приведенные в ячейках E2:G2, вычисляют все коэффициенты полиноми­ ального тренда.

Е2

F2

G2

=ИНДЕКС{ЛИНЕЙН(В2:В11;А2:А11^{1;2});1)

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2});1;2)

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2});1;3)

|В|		А	в		1		С		D	Б			Р	-	^	р=1
1	1 X Фактические Y 1 Значения тренда									С2			C1		b	s
2 1		1,0		1 873			2 069,909			63.4621212		-718.9068182			2695.596004
3 j		2,0		1 546			1 535.042
4 1		3.0		1 359			1 127,100		yHcz^^)-ici^')*b							П
б 1		4,0		1 200				846,082
б \		5,0			547			691.988
7 \		6,0			468			664,818
6 \		7,0			512			764,573
9 \		8.0			983			991,252
101		9.0		1 569			1 344,855
111		10,0		1	804	1	1 825,382
И2|
из!				Полиномиальный тренд 2-го порядка
1141
| 1 5 |		2 500	1
161								у = 63.462x2-718.91Х +2695,6
мЛ		2 000	- Л	f		:	Г		R2 = 0.8479
вШ
													JH*"^
if20l		1 500	-
pitl
[221		1 000	-
|23]									1"--
шз		500	-							— ^ Х ^
\щ										i		1	1
Щл
Г27|
1281			1 0	2,0		3.0	4,0	5.0	6.0	7.0	8.0		9,0	1С.0
'291
\т																'•'. 1
[юл															i
ГзЛ	>	ш q5J2/05		13Х05„14/05_15/05^1б/05					1 7 / 0 5 ^ в ^ Н |							MCI
|м i

Рис, 5.7-^. Полиномиальный тренд второго порядка

Столбец С содержит значения тренда у для каждого значения х, вычисленные на основе коэффициентов Ь, с1 и с2. Например, ячейка С2 содержит формулу

=($Е$2*А2"2)+($F$2*A2^1)+$G$2

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

в полиномиальном тренде может быть от двух до шести коэффициентов. Полиноми­ альные тренды высоких порядков часто способны описывать данные, содержащие не­ сколько максимумов и минимумов. На рис. 5.15 показана диафамма с полиномиальным трендом третьего порядка. Уравнение полиномиального тренда третьего порядка по срав­ нению с трендом второго порядка содержит дополнительный коэффициент сЗ.

у = (сЗ * х^З) 4- (с2 * х^2) + (с1 * х"1) + b

160	Часть I. Основы диаграмм

				^ ж а з В '
	Фактические Y	Значения тренда
1,0	136Qi	1 400.782	-9.42929293	169.5251 •830.971731 2045.293
2.0	1045i	999.600
3,0	9291	827.145	УНс2Х^)ЧсгхЧЧс1х)*Ь
4.0	732	826.842
5.0	832	942.115
6.0	1175	1 116.388
	1375	1 293.085
8.0		1 415.630
9.0		1 427.448
10,0	1284	1 271.964

1600

1400

10001200 ]

800

600

400

Полиномиальный тренд 3-го порядка

: : ! : ! : :

^*'seisin:
•	у = -9.4293х^ + 169.53х^ - 830.97х + 2045.3Г
;	R^ = 0.9169	|-

200		;	:	;	:	:	:		;
О		:	:	:	:	:	:	:
1.0	2.0	3.0	4.0	5.0	6.0	7.0		8.0	9.0	10.0
i > Ц]/ OS„tr/ 05,13 /		QS_.H \05J	5 / 1 Щ б /		05.17 /	OS^ISTC j		4 j .		±}£

Puc. 5.15. Полиномиальный тренд третьего порядка

В ячейках Е2:Н2 приведены формулы, по которым вычисляются значения коэф­ фициентов полиномиального тренда третьего порядка.

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2;3});1)

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2;3});1;2)

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2;3});1;3)

=ИНДЕКС(ЛИНЕЙН(В2:В11;А2:А11^{1;2;3});1;4)

Формулы, вычисляющие значения коэффициентов полиномиальных трендов более высоких порядков, чем третий, аналогичны приведенным выше. Напри­ мер, полиномиальный тренд пятого порядка содержит шесть коэффициентов, первый коэффициент с вычисляется по формуле

=ИНДЕКС (ЛИНЕЙН (В2 : В 1 1 ; А2 : А Н " { 1 ; 2 ; 3 ; 4 ; 5 } ) ; 1; 1) .

Линейная фильтрация

Применение линейной фильтрации задается в диалоговом окне Линия тренда. Од­ нако, как уже упоминалось, результат линейной фильтрации (скользящее среднее) — это не тренд. Скользящее среднее представляет собой линию, изображающую ряд значений, полученных путем усреднения заданного количества точек данных, распо­ ложенных слева от текущей (результирующей) точки.

Добавление линии скользящего среднего

Для добавления линии скользящего среднего (результата линейной фильтра­ ции) выделите диаграмму, выберите команду Диаграмма'=>Добавить линию тренда и в диалоговом окне Линия тренда активизируйте вкладку Тип. Выделите элемент Линейная фильтрация и в поле Точки задайте период фильтрации. Период пред-

Глава 5. Линии тренда и полосы погрешностей

161

ставляет собой количество точек данных слева, по которым вычисляется скользя­ щее среднее. Во вкладке Параметры при желании можно задать другое имя сгла­ женной кривой, т.е. текст, выводимый в легенде диаграммы рядом с ключом ли­ нии скользящего среднего.

Линейная фильтрация применяется для сглаживания зашумленных данных. Разглядеть тенденцию изменения данных в скользящем среднем легче, чем в несглаженных данных. На рис. 5.16 показан график со скользящим средним, вычис­ ленным по семи периодам (т.е. усреднялись семь точек данных слева от текущей). Как видите, линия скользящего среднего более гладкая, чем линия исходных дан­ ных, и на ней хорошо видна тенденция к увеличению значений.

											Наблюдения
300	-
250	•										t	1		Л . Л
200	•				. "г 1 hU л\ hlXAP^
100150	.-	л U							л			АШШГУЧ
50	-^iAArчr^r
				\мvr^Y ^
	1		3	5	7	9	11	13	15	17	19 21	23 25 27 29 31 33	35	37 39 41 43 45 47 49
							[—•—Данные					7 линейный фильтр (Данные) 1

Рис. 5.16, Диаграмма с линией скользящего среднего

Обратите внимание: линия скользящего среднего начинается не с первой точ­ ки данных. В примере, показанном на рис. 5.16, она начинается с седьмой точки, поскольку период усреднения равен 7. Значение первой точки скользящего сред­ него равно среднему арифметическому первых семи значений исходных данных. Значение второй точки скользящего среднего — это среднее арифметическое ис­ ходных точек с номерами от 2 до 8 и т.д. Чем больше период усреднения, тем бо­ лее гладкой будет линия скользящего среднего, однако при увеличении интервала усреднения эта линия становится короче.

Создание ряда скользящего среднего

Пользователь может создать формулы, вычисляющие скользящее среднее ряда данных, а затем вывести результат как отдельный ряд графика. Например, пред­ положим, что данные хранятся в диапазоне А1:А50. Чтобы вычислить скользящее среднее с периодом 7, введите в ячейку В7 формулу СРЗНАЧ(А1:А7). Скопируйте эту формулу во все ячейки столбца вплоть до В50, сдвигая диапазон аргумента на 1 для каждой следующей ячейки. Добавьте в график диапазон В1:В50 как новый ряд данных. Результат этих действий идентичен добавлению в график линии скользящего среднего с помощью диалогового окна Линия тренда.

Описанный способ имеет два преимущества. Во-первых, так можно добавить линию скользящего тренда в диаграмму, тип которой не поддерживает тренды.

162	Часть I. Основы диаграмм

Во-вторых, этот способ обеспечивает лучший контроль над внешним видом линии скользящего среднего*.

На рис. 5.17 показан объемный фафик (этот тип не поддерживает тренды), выводящий дополнительный ряд данных с вычисленными значениями скользящего среднего.

ы1		А			В	С 1 o'"'"'i		E	T"" F" " ;' T	?	H ^!	1	•	J	-H
1	i	98,00
? '		170,23
3		108.32							Нз1блк>дения
4	i	96.26					.-^
1 Я 1		34.15
1 бj		13^5
1И	!	175.47 '			99.48	300,00 П					\'Д#\\'"/(\1
1	^!	73,12		'	95.93	250,00-	'''i^""A"A*iV'A\iJj^								-J
10	?	56.99 '			68.70	200.00-
1 91		30.97		'	76.03
1иТ1		178.75		^	80.49	150,00-									1
12 i		77,42 ^			86.67	100,00-
13	I	64,66 ^			93.91	50,00-
14		103.57		'	83,64	0 00-
15		205.26		'	102.52		4	7 10	13 16 19 22 25	28 31	34 37
	I					1						40 43	46	49
16		90.06 [			110.96
пт		48.15	^		Щто	[•Скользящее среднее DДанные |
18	^
		88.10	'		96.75
| _		163.85	^		Т09Л9
20		229.12	'		132.59										^il
211		79.57 ^			129.16						.
															JQ
)И 4 *• WyCOS^isjl^mji						>^05^17 / 0 5 J 8 / OSJS 7 Ьэ^го/ 05_2V /OSJ221 < 1 ^									^

/*«c. 5.77. В объемном графике вместо линии скользящего среднего выводится ряд данных, вычисленных как скользящее среднее

Полосы погрешностей

Ряд диаграммы может содержать полосы погрешностей, отображающие дополни­ тельную информацию о данных. Например, их можно использовать для изображения ошибки или неопределенности, связанной с каждой точкой данных.

На рис. 5.18 показан график с полосами погрешностей, изображающими диапазо­ ны ошибок измерения каждой точки данных. В этом примере полосы погрешностей выражены в процентах: значение плюс-минус 10% от значения.

Типы диаграмм, поддерживающие полосы погрешностей

Полосы погрешностей поддерживаются рядами следующих типов двухмерных диафамм:

•диаграммы с областями;

•линейчатые диаграммы;

•гистограммы;

"Несимметричные алгоритмы линейной фильтрации (к ним относится скользящее среднее, используемое в Excel) применяются в технике для фильтрации в реальном времени, т.е. когда последующие значения в каждый момент времени неизвестны. Когда они известны, применять несимметричный фильтр не имеет смысла. В математике разработаны более совершенные сим­ метричные фильтры, вычисляющие тренд во всех точках диапазона (и за его пределами) и обес­ печивающие более высокую точность, чем скользящее среднее. То, что в Excel встроен самый простой, несовершенный и неуместный в данной ситуации алгоритм линейной фильтрации, вызывает крайнее удивление. Впрочем, вы можете закодировать в формулах Excel и применять любой другой алгоритм, например симметричное скользящее среднее, скользящий полином, сплайновый фильтр, прогнозирующий фильтр и т.д. — Прим. ред.

Глава 5. Линии тренда и полосы погрешностей

163

•графики;

•точечные диаграммы;

•пузырьковые диаграммы.

Поскольку точечные и пузырьковые диафаммы имеют две оси значений, полосы по­ грешностей в них можно выводить как для оси X, так и для оси у (а также для обеих осей).

Наблюдения с интервалом 1 минута

± 10%

120

100

80

60-^

40

20 -{

—	I	— I	—	1	—	I	—	I — I — I —	I	—	I	— I	—	I	—	I — I	— I	—	1	—	I	— I	— I — I — I — I — I —
1	2	3	4	5	6	7	8	9 10 1112 13	14	15	16	17	18	19	20	2122 23	24	25	26	27	28	29	30

Рис. 5.18. График с полосами погрешностей, выраженных в процентах

Добавление полос погрешностей в ряд

Для добавления полос погрешностей выделите ряд данных диаграммы, активизи­ руйте диалоговое окно Формат ряда данных и откройте вкладку Y-погрешности (рис. 5.19). Если диаграмма точечная или пузырьковая, диалоговое окно содержит до­ полнительную вкладку Х-погрешности.

ni'iihj'iif-^rniiiim							Jlil
	Порядок ряда»			I			Параметры
Вид	I	Ось		E I I l l H ^ J ^ r a i Z j l			Подписи Авимых
[-Показать ппд/црл погре1шостей по Y
	Плюс		f*iHyc
-•беличина псгрешиости				h	±1
С ф»«<;сироеа«*«ое эначемие:
<^ атносительное значение;				|io	4%
<^ стандартное отклонение;				ll	^
Г* стандартная погрешность
С о<зльзовательасвя;			+ 1
			- Г	-	3	•

Рис. 5.19. Вкладка Y-погрешности диалогового ок­

на Формат ряда данных

164	Часть I. Основы диаграмм

Выберите тип выводимых полос погрешностей. Полосы могут выводиться над точ­ кой, под точкой или в обоих направлениях от точки данных. Полосы погрешностей по оси X имеют те же типы, но в горизонтальном направлении.

Установите один из переключателей, определяющих величину погрешности.

•фиксированное значение. Полосы погрешностей откладываются от каждой точки данных на заданную пользователем фиксированную величину. Все поло­

сы погрешностей имеют одинаковую высоту (или ширину для оси х).

•относительное значение. Полосы погрешностей откладываются от каждой точ­ ки данных на величину, определяемую в процентах от значения точки. Напри­ мер, если задать в поле ввода значение 5%, а значение точки равно 100, то по­ лоса погрешности будет выведена от 95 до 105. Таким образом, длина полосы погрешности зависит от значения точки.

•стандартное отклонение. Полосы погрешностей откладываются на величину стандартного отклонения (другое название — среднеквадратическое отклоне­ ние), равного корню от суммы квадратов отклонений, деленному на квадрат­ ный корень от объема выборки. Все полосы погрешностей имеют одинаковую высоту (или ширину для оси х).

•пользовательская. Полосы погрешностей определяются значениями, хранящи­ мися в заданном пользователем диапазоне. Обычно диапазон содержит формулы.

На рис. 5.20 показана диаграмма с полосами погрешностей, изображающими ста­ тистическую ошибку результатов опроса. При создании диаграммы был установлен переключатель фиксированное значение и задана величина погрешности, равная 0,035, т.е. ошибка постоянна и равна 3,5%. Обратите внимание: ошибка выражается не в процентах от значения, а в единицах значения, т.е. в процентах (это существенно разные единицы).

ш	в		^^шт^шм
			JUTi
Выпуск облигаций 4556-А
	Июнь	Октябрь
За	22%	40%
Против		51%
Воздержались	20%	9%

Выпуск облигаций 4556-А

За	Против	Воздержались
Погрешность выборки = 3.5%		In Июнь 0 Октябрь 1

и 4 » ИК 0О8\05_20 / 0S_21 / 05jg2 / 0S_23 / ОЗЗГI < I	^r^

Рис. 5.20. Диаграмма с полосами погрешностей, отображающими статистическую ошибку результатов опроса

Глава 5. Линии тренда и полосы погрешностей

165

На рис. 5.21 показана точечная диаграмма с полосой погрешностей по оси у, созданной при установленном переключателе стандартное отклонение. В отличие от других типов полос погрешностей полоса типа стандартное отклонение выво­ дится относительно среднего арифметического значения всех точек данных. В примере, показанном на рис. 5.21, среднее ста точек данных равно 11,67, а стан­ дартное отклонение — 4,69.' Поэтому полоса погрешностей выводится вокруг среднего плюс-минус 4,69. Благодаря полосе погрешности из диаграммы ясно видно, что большинство точек данных отличаются от среднего не более чем на величину стандартного отклонения.

| р |Д|Р1'»т1'ГП|11;^^^^^^^^^^ИИИИ1ИИНИНИИВ||||||||^^				||1МШи1и[]|||||||^^		' \	Ё	i Г~1	0	i
	А	В	С 1 '	" 6	:
1 Номер наблюдения Значение
2	1	4.22			Среднее:		11,67
3	2	7.74	Стандартное отклонение:				4,69
4	3	8.05
5	4	17.44
6	5	4.49				100 наблюдений
7	6	15.76
1 8	7	10.80
^	8	18.13				«
10	9	10,45
И 1	10	14,14	20,00 -			•
1 12	11	6.81
[ИЗ	12	17,15
14	13	7,19
1 15	14	5,29	15,00 -
16	15	15,09				• * ч** * * .
17	16	16,39
18	17	6.08	10,00 -
19	18	12,56
20	19	15,21
21	20	5.11			*	»*	*	• •
22	21	16,13	5,00 - .
				•	*	*	•	•
23	22	13.64
24	23	7,51
ш.	24	10.38	0,00 •'
ПсГ	25	18,06
27	26	5,97	0		20		40	60		80
28	27	14,17
|И АТи/озЗё/Сгр!05^21/05 Jj27os:23X05:24/p5>705ll26XbSJ4|										^

^У-у ;'/" .„'^,.|,Ц||.^д {

н ~~Л Гт11

""il

•

• * *

•

'•

'

100 | •ш

Рис, 5.21. Полоса погрешностей показывает величину стандартного отклонения

Поскольку значения х в этой диаграмме представляют собой последователь­ ность целых чисел, точки данных (и полосы погрешностей) равномерно рас­ пределены в горизонтальном направлении. В результате полосы погрешно­ стей образовали одну сплошную полосу.

На рис. 5.22 показана точечная диаграмма с полосами погрешностей как по оси у, так и по оси X, Оба набора полос погрешностей выводят для каждого значения соот­ ветствующие ошибки — плюс-минус 10%. Полосы погрешностей по осям х и у неза­ висимы друг от друга. Для них могут быть установлены разные параметры.

Форматирование и модификация полос погрешностей

Для изменения формата полос погрешностей дважды ш.елкните на любой из них. Появится диалоговое окно Формат планок погрешностей. Во вкладке Вид можно изме­ нить тип линии погрешности и настроить тип маркера.

166	Часть I. Основы диаграмм

Диалоговое окно Формат планок погрешностей содержит также вкладки X- погрешности и Y-погрешности, в которых можно изменить параметры полос погреш­ ностей, заданные при создании. В диаграммах, отличных от точечных и пузырьковых, вкладка Х-погрешности отсутствует.

р			в 1	С	^	D	T E	T	F	T	•^G	i	-н		!	r^ril
1 1		X	Y													—II
2		88.43	63.88				ХиУ±10%
3	1	24.81	45.97
4	1	76.57	70.97	100 -
6		48.11	7.74
6		35.39	34.91	9 0 -
1'?'		43,89	79,.01	8 0 -							. I .
18		2.44	35.84
9		13.63	41.59^	70-										I .
llio
				60 -								'.
[12				50 -										i
\\и
				40 -
(Ж				30 - 1
Щ
DZ				20 -
Ж
\W				10	-
щ				0	•
[72			1	с) 10		20	30	40	50	60	70	80		90	100	J
ш
|и ^ * wl<..Q5J.|/05jg./CJ5_ZiХР5Ji^2jm^IMJ:±'lPh.L...........~J-......v															1
р1

Puc. 5.22. Точечная диаграмма с полосами погрешностей по осям х и у

Пользовательские полосы погрешностей

Переключатель пользовательская применяется для создания полос погрешностей, величина которых задается в ячейках рабочего листа. В большинстве случаев сначала нужно создать формулы, вычисляющие погрешности на основе исходных данных. За­ тем ячейки с формулами должны быть определены как диапазон (диапазоны), исполь­ зуемый полосами погрешностей.

На рис. 5.23 показан график, отображающий объемы ежемесячных продаж. Поло­ сы погрешностей изображают изменение объемов продаж по сравнению с предыду­ щим годом. Если полоса погрешности расположена над точкой данных, то в этом ме­ сяце прошлого года объем продаж был выше, а если под точкой — ниже. Такое ис­ пользование полос погрешностей фактически является альтернативой выводу дополнительного ряда данных. В примере, показанном на рис. 5.23, вывод еще одного ряда сделал бы диаграмму загроможденной и менее разборчивой.

Столбец D содержит простую формулу, вычисляющую разность данных столбцов В и С. Диапазон D2:D13 используется в качестве диапазона "-!-", ассоциированного с переключателем пользовательская. Выбран режим вывода Плюс.

Глава 5. Линии тренда и полосы погрешностей

167

	ШШША	шшшшш	С	D	"" е" "Т '"""'|'""^^^р*ж^
	Месяц	Текущий год	Прошлый год	Разница
в1		в
2	Янв	94 640	57 894	-36 746
3	Фев	96 096	66 394	-29 702
4	Map	111 552	88 816	-22 736
6 \ Апр		84 448	79 840	-4 608
б	^Май	75 712	71093	-4 619
7 1Июн		61 152	75 520	14 368
8 1Июл		50 960	73 098	22138
В	\	59 696	70 930	11 234
	Авг
hoi Сен		99 008	85 904	-13 104
111Окт		108 832	97 552	-11 280
12	Ноя	122 112	100 464	-21 648
И З ! Дек		117 936	110 656	-7 280
114

	П5"	Продажи по месяцам
	ПВ

\\щ

\Ш

]Щ

|2б1

ПЛ

Ж

\"Щ

Зб1

з!

[щ

\Щ

|Э4[

\» 4 >

100 000-		11	1,4ч		„„ггт:'.
120 000	•				Ш-
80 000	-		LiV		/О
60 000	- "^		[	•-*-[ <..^J	/
40 000	-
20 000	-

	Янв Фев Map Апр Май Июн Июл Двг	Сен Окт Ноя Дек
	Вертикал ьные линии изображают объемы продаж & прошлом году		_шЗ
	*^K^J^XP§J^J.^^/i<^J^\^.'^J^?.X.^J1tJ,^liJ„„,.,..	„..1

Рис. 5.23. Полосы погрешностей изображают объемы продаж в эти же месяцы прошлого года

На рис. 5.24 показан еще один пример диаграммы с пользовательскими полосами погрешностей. Гистограмма изображает усредненное по неделям количество ежеднев­ ных запросов для каждой из шести недель. Полосы погрешностей изображают мини­ мальное и максимальное количество запросов на протяжении дня для данной недели. Например, среднее количество запросов за первую неделю равно 77,71 запроса в день. Максимальное количество запросов (116) поступило в понедельник, а минимальное (32) — в воскресенье.

Количество запросов по отдельным дням хранится в диапазоне B3:G9. Используе­ мые в диаграмме данные получены в результате дополнительных вычислений. Стро­ ка 13 содержит вычисленные средние значения, выводимые столбиками гистограммы. Формулы, расположенные в строках 14 и 15, вычисляют минимальные и максималь­ ные значения. Формулы, расположенные в строках 16 и 17, вычисляют величины, ис­ пользуемые в полосах погрешностей. Формулы строки 16 вычитают среднее из мак­ симальных значений, в результате получаются значения диапазона "+", ассоцииро­ ванного с переключателем пользовательская. Формулы строки 17 вычитают минимальные значения из среднего, что дает значения диапазона "-".

168	Часть I. Основы диаграмм

Ь{|41тя^.Ш11|

	Среднее количество запросов за день в службу поддержки
	Неделя-1 Нвделя-2		Неделя-3	Неделя4	Неделя-5 Неделя-в
Воскресенье	32	45!	42	18	331	54
Понедельник	116	132	126	109	132	119
Вторник	95	124	109		93	90
Среда	101	132	101	114		98
Четверг	88	103	132	136	99	112
Пятница	671	74	104	121	110	87
Суббота	451	55	63	29	34	55
			Данные диаграины
Среднее	[ЕВЖДЕШЖаСВИИ
			1321	136		119
Максимум	116	132			132
Минимум	32	45	42,	18	33	54
Плюс	38.2861	37.000	35.2861	48.143	47.714	31.143,
Минус	45.714	50.000	54.714	69.857	51.286	33.857

Среднее количество запросов задень

Неделя-1 Неделя-2 Неделя-3 Неделя-4 Неделя-5 Неделя-6

'>'"'ЩMMIШЖШJПШMZШЖ\^:^Jl:±,hL Л t\u.

Рис, 5.24. Гистограмма изображает количество запросов, усредненное за неделю; пользовательские полосы погрешностей показывают мини­ мальное и максимальное количество запросов

Соединение точки данных с линией тренда

Если в диаграмму добавлена линия тренда, то полосы погрешностей можно ис­ пользовать для обозначения величины отклонения фактического значения точки от значения тренда (рис. 5.25).

Чтобы создать такую диаграмму, сначала добавьте линию тренда. Затем создайте формулы, вычисляющие значения тренда по оси у. В примере, показанном на рис. 5.25, диапазон С2:С11 содержит формулы массива, введенные с помощью клавиш <Ctrl+Shift+Enter>.

=ТЕНДЕНЦИЯ(В2:В11;А2:А11)

Создайте формулы, вычисляющие разность между значениями тренда по оси у и фактическими значениями по этой же оси. Для вычисления разности в ячейку D2 за­ писана формула =С2-В2, в ячейку D3 — формула =сз-вз и т.д.

И наконец, добавьте в диаграмму полосы погрешностей по оси у с помощью вкладки Y-погрешности диалогового окна Формат ряда данных. В примере, пока­ занном на рис. 5.25, установлены тип полосы погрешностей Плюс и переключа­ тель пользовательская. В поле "+" задан диапазон D2:D11.

Глава 5. Линии тренда и полосы погрешностей

169