Diagrammy_v_Excel_Dzhon_Uokenbakh_2003

^ C h a p t e r O S - x is
В	НА		\ < I	^ к . I т1
Зака
зано складе производстве			Сравнение объема «ака$а с количеством изделий
A-6OO1 754	525	300	на складе и в производстве
B-IOOI	610

	А-360	A-SOO	В-100	С-900
	• Заказано	@На складе D B производстве |			.dU
и i > W|^08Ji 7^12Tb8J3"/08^H	jCmjS^j6jmj7\QB_J8J	ИГ.		.J '

Рис. 8.18. Гистограмма с накоплением выводится поверх обычной гистограммы

Создание ступенчатой диаграммы

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

Использование полос погрешностей рассматривается в главе 5, "Линии трен­ да и полосы погрешнослей".

Столбец С содержит простые формулы, вычисляющие разность между датами столбца А. Например, ячейка СЗ содержит формулу

=АЗ-А2

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

=:ВЗ-В2

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

250	Часть П. Построение диаграмм

	elthapterOe лЬ										•ШШШ1ШшшШi l i i l i i i i l l i ^ e						^^кЬ0'4-^ЫШ'-»/'/'''".«iaiisli
			/"^А		'-'		В	С	Г"	D^"' 1		е ^	"'Э"'"'""*Г""'"ё""'"""Т			н"'	\	1 i ^	1	т1
		1^				Бал<)нс														1
	pp-			01	янв		1 000						Дневной баланс
		я i		02 янв			1 000
		4 ,		03 янв			1 000
				04 янв			1 456	1 500 -
				05 янв			1 456										г\
		1	'	06 янв			1 654	1 000 -
		Н '		07 янв			1 433
		^	-	08 янв			1 433		500 -								1 V 4
		1«;		09 янв			1 433
		11		10 янв			1 103		0 1								1
		1?		11	янв		1 654
		13		12 янв			1654	-500 -									1
		14'		13 янв			1 800	-1	000 J
	1	If^f					1 800
	1	16		15 янв			1 800		1.1			8,1	15.1	22.1		29.1		5.2
		17^		16 янв			1 093
				17 янв			1 093						Дневном баланс
	1	19		18 ян^			1 093
	hu			19 янв			1 093
		2 1 '		20 янв			1 093	1 500 -												Л
	1	22		21 янв			875
		23		22 янв			566	1 000 -
		24		23 янв			566
		25'		24 янв			566		500	Як	^
		26		25 янв			566		0 -
		27'		26 янв			212
	128*"			27 янв			100	-500 -
	[29'			28 янв			-250
	Ы			29 янв			-450
		31		30 янв			-560				8.1		15 1	22 1		29 1		5.2
		3 2 ^		31	янв		1 250
		33	01 фев				1 250
	[И 4		П? AfiR				ЯЯ'^							Ы				1	,>JJ^|
			*• и1/оа-13 /08_14 / oals /os.ie/oe.j7 7osLte\o8.ЛЯ/
	Рис. 8.19. График и гистограмма; ни одна их этих диаграмм не является ис­
	тинно ступенчатой
г. л А • 1 " Т " ^							С	— 5 — :			I. X 1 1 9. 1 и 1 i i							J
1 Ц Н ^ Н Ё М ^ ^ Д ^ ^ ^ Щ														Дневной баланс
1 2 i 01.янв					1 иии				и
1 3 : 02.янв					1	150			150		1 500 -.... Г1 .
4	': 03.янв				1	150			0
1 5 1 04 янв					1 456				306									|Т
1 6 i 05 янв					1 654				198
7 \ 06.янв					1 654				0		1 000 -Х77
8	\ 07.янв				1 433				-221			/Ч
9	J 08.янв				1 433J				Oj		500 -							1	^
10 \ 09.янв					1433				0								К^
1 11 МО янв					1	103			-330
12) 11 янв					1 654				551		0 -
13| 12.ЯНВ					1 654				0								\
14; 13 янв					1 800				146		-500 -
1 15 \		14.янв			1800				0
1 16 i 15.янв					1 800				0!
17 i 16.янв					1 093				-707
1 18;		17 янв			1 093				о1		1 000	1	C.l	ID.I	/г	1	/y.l	62
19 1 18.янв					1 093				0		1
201 19.янв					1 093				0
|м	4 "У'Ш^ЛЛ					Jcejs"/U81"б7'бб_17			/0 8Je708j9 \ 0 e _ 2 0 /					Ш					1	±j

i

Рис. 8.20. Имитация ступенчатой диаграммы с помощью точечной диаграммы с полосами погрешностей

Глава 8. Искусные приемы создания и использования диаграмм

251

Сочетание графика и линейчатой диаграммы

Объединить график с гистограммой несложно (о сочетании графика с линейчатой диаграммой — немного ниже). Для этого можно или применить пользовательский тип в мастере диаграмм, или, создав стандартную гистограмму, заменить один из рядов графиком. На рис, 8.21 показана простая диаграмма, объединяющая гистограмму с графиком. Линия графика изображает запланированные объемы продаж, а столбики гистограммы ~ фактические объемы. Однако, если понадобится объединить график с линейчатой диаграммой, вы увидите, что Excel не позволяет сделать это.

Запланированные и фактические объемы продаж

300	-				Г ~ 1
250	-
200	•
150	-		...--	^	...
100	-	п	Piтц
50	-
		Янв	Фев Map	Апр Май Июн	Июл Авг Сен Окт Ноя Дек
				1 C~Z3 Продано	Запланировано |

Рис. 8.21. Создать смешанную диаграмму, объединяю­ щую график с гистограммой, несложно

Технически Excel не поддерживает объединение графика с линейчатой диаграм­ мой. Объясняется это тем, что ось категорий линейчатой диаграммы всегда направле­ на вертикально, а графика — горизонтально. Решение проблемы состоит в использо­ вании точечной диаграммы вместо графика, как показано на рис. 8.22.

щш в С) 0						Е	F	i 0	j И \			1 i	7 ^ ^ К
11	л	П р о д а но	50		План			Запланрированные и фактические объв мы продаж
2	Янв	60				1
1 3		64	70			2	Янв	, . . ..,		^	_ _^					г	0,5
\А'	Map	93	90			3			vl	:		:				•	1.5
									: >_	;		;
6	Апр	45	110			4	Фев									•	2.5
в	Май	102	130			5	Map		!Х	i		;
																• 3.5
7	Июн	143	150	1		6	Апр		':	; л,		!				•	4.5
8	Июл	205	200			7	Май	~
i ' ^ -	Авг	244	"250^			8				1	^s	'				•	5,5
to Сен		225	300			9	Июн					"У-'-^
																•	6.5
11 Окт		185	300			10	Июл		•			' ^''**'**<4«^
12 Ноя		264	300			11	Авг									•	7.5
13 Дек		325	300			12	Сен							1		•	8.5
Щ												1				•	9,5
							Окт
																•	10.5
Щ							Ноя							1
																•	11.5
Па"							Дек									J 1
								1	1	1		,	1	1	1		12,5
{Ж								С)						250	300	1-
									50	100		150	200			3£
Щ
									|г	1 Продано			План |

123

|И < • И|/Ов_15/О8_16/Ов._17 / 08_1в / 08J 9 / 0в_20 / 0в_21 \ 0 8 _ 2 2 / \<\

J J^

Рис. 8.22. Имитировать график в линейчатой диаграмме можно с помощью точечной диаграммы

252	Часть П. Построение диаграмм

Чтобы создать диаграмму, показанную на рис. 8.22, выполните ряд действий.

1.Создайте стандартную линейчатую диаграмму, выводящую диапазон А1:В13.

2.Выделите диапазон D1:E13 и выполните команду Правка^=>Копировать,

3.Активизируйте диаграмму и выполните команду ПравкаОСпециальная вставка. Установите переключатели новые ряды и Значения (Y) в столбцах. Установите флажки Имена рядов в первой строке и Категории (подписи оси X) в„ первом столбце. Обратите внимание: данные упорядочены не совсем обычно: значения X представляют запланированные объемы продаж, а значения у являются после­ довательностью целых чисел.

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

5.Измените тип нового ряда на тип точечной диаграммы с подтипом, в котором точки данных соединены линиями.

6.Настройте вспомогательную ось у. В поле минимальное значение введите число 0,5, а в поле максимальное значение — число 12,5. Этим вы задаете начало линии посередине полоски января, а конец линии — посередине по­ лоски декабря.

7.Если хотите, чтобы отсчет месяцев начинался с верха диаграммы, выделите ось категорий (она расположена слева) и активизируйте диалоговое окно Формат оси. Во вкладке Шкала установите флажки обратный порядок категорий и пере­ сечение с осью Y (значений) в максимальной категории.

8.Повторите действия, описанные в п. 7, для вспомогательной оси значений, ко­ торая расположена справа.

Разная ширина столбиков

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

Управлять шириной всех столбиков можно с помощью ширины зазора, однако в Excel нет средств установки разной ширины отдельных столбиков. Диаграмма, пока­ занная на рис. 8.23, фактически не использует данные диапазона A1:D7. Вместо этого диаграмма выводит шесть рядов по 100 точек каждый. Часть выводимых данных пока­ зана на рис. 8.24.

Ячейки столбца J содержат формулы, вычисляющие количество столбиков, вы­ водимых каждым из шести рядов. Количество столбиков вычисляется на основе значений столбца D (общий доход). Итак, диаграмма выводит 100 точек данных, размещенных в шести рядах. Формулы столбцов K:L вычисляют номера первых и последних непустых строк каждого ряда. Например, ряд А выводит 12 из 100 столбиков, остальные столбики ряда А отображают нули и не поднимаются над осью категорий. Принадлежащие ряду А точки данных расположены в строках 2—13. Ряд Б выводит 5 из 100 столбиков, его пять точек данных расположены в строках 14-18.

Формулы довольно сложны, однако пример, показанный на рис. 8.23, легко адап­ тировать к другим типам данных.

Глава 8. Искусные приемы создания и ис!пользования диаграмм

2 5 3

OfeCh^fereMb ^	ашшшайж!
	Е 1 F I G I Kfl
Изделие Продано изделий ДОУ.0Д нй одно изделие Общий доход
ibo90	$15 079
S9 64	$5 239
$15 00'	$11 760
$45.00,	$42 300
$ 1 4 ^	$20 399
"'?'6 00|	$24 864

Колнч. изделий	Oobs^fei продаж по типам изделий
1 800

Ширин.! cntonoit,! пропо^^йюналыт общему iScacady

М Ч > •! 1/ 08„! 9 / Oejf) /

08^21 /

0SJ2

Х08„23 /

И)

6

Рис. 8.23. При создании это

лстограммы использовались хитро-

умные трюки

I^'C:hapter08.Kls

:^,Ш:^^:ШШШШ^ШШ^^^^^^^^^^^^^^^^^^Ш

N Г о П

^'WT'-J''""""'^i''

'"'•{'

:-.'• Лп\щ

Г

\

1

J

1

к

!

С

' М '

p

^ПсГ"~"

R

s Tl

1

1

Ряд

Колич. столбцов j 1-я строк»! Гпосл^^дмля crpoKaj

,

A

Б

в

Г

Д

Е

2

i

А

' j i

:

I-":

265

3

;

Б

' S

«4

4ii

'

265

\А\

Е

•"" F

' ' '

Щ

j265~1

5

i

Г

351

2':'i

265

6

1

Д

7!

Г4!

BDI

265

7

^

Е

21 j

61 j

Kill

265

8

265

265

265

1.11

265

265

[13

265

[14

i

634

1 15

634

Пб^

634

pzl

1

634

Ц9^из'

[

634

784

[Ж]

784

|2Г;

784

[221

784

[231

784

[24"^

734

Г25]

784

:[2б1

• w/os^19 / oaj2o /

mjx

/ Pf3 ii

> 08 ^^^ /

|

784

t^\П\

|м <

<1

Рис. 8.24. Диаграмма,

показанная

на рис. 8.23, выводит данные столбцов N:S

254	Часть II. Построение диаграмм

Условные цвета

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

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

	QI ChapterQ8.Kis
	Данные		33 66 100
	-15		Демонстрация условных цветов
	-20	-20
	22		22
			45
	56		56
	84
	90
	54		54
	12		12

Янв Фев Map Апр Май |Июн Июл Авг Сен Окт Ноя

гШ.»т><^

ЖГ\

и i > W1^ 0 9 J ^ / bejZO / 08^21 / 08^22 / 08J23 \08_25 /	Ы	НИ

Рис. 8.25. Цвета столбиков зависят от значений

Фактически диаграмма выводит четыре ряда, причем столбики каждого ряда зали­ ты другим цветом. Диаграмма выводит столбцы C:F. Ячейки столбцов C:F содержат формулы, обращающиеся к значениям столбца В и диапазона C1:F1. Формулы столб­ цов С:Р вычисляют, что должна содержать ячейка — значение или пустую строку.

Например, формула ячейки С2 проверяет значение ячейки В2. Если оно меньше значения С1, то выводится значение из столбца В, Иначе формула возвращает пустую строку. Формула ячейки С2 имеет следующий вид:

= Е С Л И ( В 2 < = $ С $ 1 ; В 2 ; " " )

Формулы столбцов D:F немного сложнее, потому что они должны выяснить, нахо­ дится ли значение столбца В между двух значений. Например, формула ячейки D3 имеет вид

= Е С Л И ( И ( $ В З > С $ 1 ; $ В З < = В $ 1 ) ; $ В З ; " " )

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

например: =ЕСЛИ(В2< = $ С $ 1;В2;НД() ) .

Гистограмма, созданная на основе столбцов C:F, выводит четыре ряда. Ширина за­ зора и перекрытие рядов устанавливаются во вкладке Параметры диалогового окна Формат ряда данных. В диаграмме, показанной на рис. 8.25, установлена ширина за­ зора О, а перекрытие — 90.

Глава 8. Искусные приемы создания и использования диаграмм

255

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

Демографическая пирамидальная диаграмма

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

1	1 А		В		С	1	D		I... ]	F 1 0	1 н	1 i	i	J	1	к^	I	1 iril
	[Население в 2000 г. 281				421 906					Возрастной состав населения США (2000)
г
	Шоз|>аст	Мужчины Мужчины Женщины Женщины!								.85+							;
А jO-4		9 810		733	-3,5%	9 365 065			3,3%	80-84						•	Мужчины
5	j5-9	10	523	277	-3.7%	10 026		228	3,6%	75-79
																•	Женщины I
6 hO-14		10 520		197	-3.7%	10 007 875			3,6%	70-74
7	115-19	10 391		004	-3.7%	9 828 886			3,5%	65-69		д|||||||||||
8	120-24	9 687		814	-3,4%	9 276187			3.3%	60-64				. ^ ... . ^ ^[
9	125-29	9 798		760	-3,5%	9 582 576			3,4%	55-59		щшшшшМ		.',•"	<,."•' "'••УА
to 130-34		10 321		769	-3.7%	10188		619	3,6%	50-54	щшш^^
11 135-39		11	318	696	-4.0%	11	387	968	4,0%	45-49
12 140-44		11	129	102	-4,0%	11	312	761	4,0%	40-44				'«^^'' О -: U&'^'i'M:
13 .45-49		9 889		506	-3.5%	10 202 898			3,6%	35-39		Н н Ш п н
14 •50-54		8 607		724	-3.1%	8 977		824	3,2%	30-34		ШШЛ'''''^','^"''---^^у--'Аi
										25-29
15 '55-59		6 508		729	-2,3%	6 960		508	2.5%			ИИ—../.^:-^j-?.->:.. г?^.
16	60-64	5 136		627	-1,8%	5 668		820	2,0%	20-24		Ш И и и ''-'J'-'S ' -^^- 'Л
17	65-69	4 400		362	-1,6%	5133183			1,8%	15-19		••ШШ		'	' '""	' '
										10-14
18	70-74	3 902		912	-1.4%	4 954		529	1,8%	5-9		• И И			—^-^1^.
19	75-79	3 044		456	-1.1%	4 371		357	1,6%	0-4		щ1^Ш^Щ'"^^^ШШ(?^^Ш^
[Ж	80-84	1 834		897	-0.7%	3110		470	1.1*4
О Т 85 +		1	226	998	-0.4%	3 012		589^	1,1 %|	5 »,>	AOL	Oli.	П»л.		•Х»!		АО'	fiCi
[22
1« * • Hj/dОИЖ!1СшимЖШ1 ШТМЖУ ^?^'^/1Т1Г7711:^гггггг																	r r J .	^ П \

Рис. 8.26. Демографическая пирамидальная диаграмма

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

1. Создайте линейчатую диаграмму с накоплением, выводящую два ряда. Столбец А содержит категории, а столбцы С и Е — значения данных. Обратите внимание: проценты для мужчин выражены отрицательными числами.

2.Вьщелите ось значений (она расположена снизу), активизируйте диалоговое ок­ но Формат оси и откройте вкладку Число. Задайте следующий пользовательский числовой формат:

0 % ; 0 % ; 0 %

Этот формат устраняет знаки "минус".

3.Выделите ось категорий (она расположена посередине диаграммы), активизи­ руйте диалоговое окно Формат оси и откройте вкладку Вид. Удалите основные и промежуточные метки делений (переключатели нет). В группе Метки делений установите переключатель внизу. Этим вы задаете вывод оси по центру диа­ граммы, а надписи меток делений — слева.

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

256	Часть П. Построение диаграмм

Диаграммы Ганта

Горизонтальная линейчатая диаграмма Ганта часто используется в приложениях управления проектами. Хотя в Excel нет встроенного типа диаграмм Ганта, создать ее несложно, главное — правильно расположить данные.

На рис. 8.27 показана диаграмма Ганта, изображающая график выполнения проек­ та. Диаграмма выводит данные диапазона А1:С13. Горизонтальная ось представляет общее время выполнения проекта, а горизонтальные полоски — отдельные этапы проекта. На диаграмме хорошо видны продолжительность каждого этапа и перекры­ вание разных этапов.

^ Chapter08.Kis
Начал» этапа Продолж»гг»яьноаъ
Утверждение проекта		29 дек
Разработка анкеты		30 дек						11
Печать и распространение анкеты		13 янв|
Получение ответов		16 янв
Ввод данных		16 янв
Анализ данных		3 фев
Написание отчета		9фев
Распространение черновика отчета		23 фев
Консультация адвоката		24 фев
Завершение отчета		2 мар
Анализ отчета членами правления		9 мар
Заседание правления		17 мар
Утверждение проекта	J....I				1 График выполнения проекта
Ра:хравот1са анкеты \шк				^
Печать и распространите анкеты
Получение ответов	]-—			1			-	1
Ввод данных
Анализ данных
Написание отчета								•
Распространение черновика отчета
Консультация адвоката									•	•
Завершение отчета
Анализ отчета членами правления
Заседание правления							———			L
	29	5		12	19	26		2 9 16	23 2-L9 16
дек		янв	янв		якв	янв		фев фее фев фев мар мар мар
^Т"'и]^Щ]г|7Ж]£г7Ш^ТоСзТё^^										J	lljd
											Я

Рис. 8.27. Диаграмма Ганта

Столбец А содержит имена этапов, столбец В — даты начала этапов, а столбец С — продолжительность каждого этапа в днях. Чтобы создать диаграмму Ганта, выполните ряд действий.

1.С помощью мастера диаграмм создайте линейчатую диаграмму с накоплением, выводящую диапазон А2:С13.

2.На шаге 3 мастера удалите легенду и, щелкнув на кнопке Готово, создайте вне­ дренную диаграмму.

3.Настройте высоту диаграммы таким образом, чтобы были видны все подписи оси категорий. Можете также настроить размер шрифта подписей.

4.Для оси значений (это горизонтальная ось) активизируйте диалоговое окно

Формат оси. В поля минимальное значение и максимальное значение вкладки

Глава 8. Искусные приемы создания и использования диаграмм

257

_| *1А

Шкала введите даты первого и последнего дней выполнения проекта. Чтобы от­ метить недели, введите в поле цена основных делений число 7.

5.Для оси категорий (это вертикальная ось) активизируйте диалоговое окно Фор­ мат оси и откройте вкладку Шкала. Установите флажки обратный порядок кате­ горий и пересечение с осью Y (значений) в максимальной категории.

6.Выделите первый ряд данных, активизируйте диалоговое окно Формат ряда дан­ ных и откройте вкладку Вид. Установите переключатели Граница невидимая и Заливка прозрачная. Этим вы делаете первый ряд невидимым.

7.При желании выполните декоративное форматирование диаграммы.

	СОВЕТ	Если этапов проекта много, то будет довольно сложно заставить Excel вывес­
		ти все имена категорий . В т а к о м случае вы можете применить фиктивный ряд
		вдоль вертикальной	о с и , используя его подписи данных в качестве имен эта­
		пов. Использование	фиктивных рядов описано выше в главе.

Идентификация минимального и максимального значений ряда

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

iC|Chapter08 KIS					ь	1
1	Л	В		С
	Месяц	Продажи		Мах	Min
2	Янв	1 892		#н/д	#н/д
3	Фев	1 797		#н/д	#н/д
4	Map	1837		#н/д	#н/д
5	Апр	1 941		#н/д	#н/д
6	Май	1 435		#н/д	1435
7	Июн	2 768		#н/д	#н/д
8	Июл	2 220		#н/д	#н/д
9	Авг	3 123		3 123	#н/д	1
10	Сен	2	445	#н/д	#н/д
t1	Окт	2	493	#н/д	#н/д
И 2	Ноя	2	404	#н/д	#н/д
И З	Дек	2 550		#Н/Д	#н/д	1
14

Щ

			ШШ£М
е	F 1 0	1 Н 1 1 1 J \ к		щ
	Объемы продаж по месяцам
3 500	п
3 000	-	y W	3 123
2 500	-
2 000-
1 500 -		Ш 1 435
1 000 -
500	-
0	•
	Янв Фев Map Апр	Май Июн Июл Авг	Сен Окт Ноя Дек
	|—•—Продажи о Мах		D Min |

|н 4 ^ ИУ(0ejze/oe^Нм^Лл >8^Д6/0в^27\08_28 / и\

Рис. 8.28. Для выделения в графике минимального и максимального значенийi f ] ис­ пользуются два ряда точечной диаграммы

Чтобы создать диаграмму, показанную на рис. 8.28, выполните рад действий.

1.Введите в ячейку С2 формулу

=Е С Л И ( В 2 = М А К С ( $ В $ 2 : $ В $ 1 3 ) ; В 2 ; Н Д ( ))

2.Введите в ячейку D2 формулу

=Е С Л И ( В 2 = М И Н ( $ В $ 2 : $ В $ 1 3 ) ; В 2 ; Н Д ( ) )

3.Скопируйте диапазон C2:D2 вниз вплоть до строки 13 (заменяя в первом аргу­ менте В2 на вз, В4 и т.д.) Формулы возвращают или минимальное (максимальное) значение столбца В, или значение #н/д.

258	Часть П. Построение диаграмм

4.Вьщелите диапазон C1:D13 и выберите команду Правка'=>Копировать.

5.Выделите диаграмму и выберите команду Правкз'^^Специальная вставка. Установите переключатели новые ряды и Значения (Y) в столбцах. Установите флажок Имена ря­ дов в первой строке. Этим вы добавляете два новых ряда с именами Мах и Min.

6.Вьщелите ряд Мах, активизируйте диалоговое окно Формат ряда данных и от­ кройте вкладку Вид. Установите переключатель Линия отсутствует и замените маркер на большой пустой круг. Чтобы создать пустой круг, установите тип маркера как круг и в поле фон: установите Нет цвета. Увеличьте размер круга.

7.Повторите действия, описанные в п. 6, для серии Min, однако теперь задайте в качестве маркера большой пустой квадрат.

8.Добавьте в новые ряды подписи Мах и Min (значения #н/д не выводятся).

9.Выполните косметическое форматирование диаграммы.

Формулы, введенные в пп. 1 и 2, возвращают #н/д, если соответствующее значение в столбце В не является ни максимумом, ни минимумом. В графике и точечной диаграмме значение #н/д приводит к разрыву линии — это как раз то, что нужно в данной ситуации. В результате в ряде Min (или Мах) выводит­ ся только одна точка данных.

Затенение промежутка между линиями графика

в примере этого раздела рассматривается, как наложить тень на область между двумя линиями графика. На рис. 8.29 показан график с двумя рядами, область между двумя линиями заполнена определенным цветом.

тISHSESЯвН					С	шшщ
щ		Линия! Л11ния2 Область!				Область2|
			10.14	20.02	10.14	9.88У
Li_
			11.3	20.98	11,31	9.681
			13.35	19.08	13.35	573^
(6™рг			16.36	19.61	16.36	3.25
1 6			16.27	21,61	16.27	5.34
[ 7			17.37	22^4	17.37	4.67
1 3			16.45	21.96	16.45!	ST]
			15.85	24.41	15.85	8.56
1	10		17.98	25.39	17.98	7.41
1	11		19.6	28.58	19,6	8.98
1	12		21.05	30.1	21.05	9,05
13			22.38	31.11	22.38	8.73
1	14		22.19	31.71	22.19|	9.52
Пб			20,32	33,3	20.32	пщ
1	is		19.45	34.02	19.45	14.57
117			20.13	36.99	20.13	16.8б|
it 18 1			19.96	40.23	19.96	20.271
1	19		18.45	41.33	18.45	22.88
\\т			16.65	42.84	16.65	26.19
1	21		19.11	43.6	19.11	24.49

^Л.1..**К3^1^:^А^^

*~1

50 -]

45 -

40 -

35 -

30 •

25 -

20 -

15 •

10 -

5 -

0 - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

.1.^.J4L.^..-.:I..: :J	^,.L.L..
	•:^,.,.....L.-.:L„..I1.и:^..й

Рис. 8.29. График с заполнением области между двумя линиями

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

=В2-А2

Глава 8. Искусные приемы создания и использования диаграмм

259