Diagrammy_v_Excel_Dzhon_Uokenbakh_2003
.pdfЧрезвычайно важна последовательность наложения объектов. Объект, закры вающий другие объекты, должен быть самым верхним в стеке. Чтобы изме нить последовательность объектов в стеке рабочего листа, выделите сгруппи рованный объект, щелкните на нем правой кнопкой мыши и в контекстном меню выберите команду Порядок^На передний план (или На задний план и
т.д.) Чтобы изменить место диаграммы в стеке, нужно выделить объект диа граммы (не саму диаграмму, а именно объект!) и выбрать команду Порядок<=>На передний (задний) план. Для выделения объекта нажмите клавишу <Ctrl> и щелкните на диаграмме (если щелкнуть, не нажимая клавишу <Ctrl>, то будет выделена диаграмма, а не объект).
Накладывая диаграммы, важно сохранить идентичность шкал значений. В приме ре, показанном на рис. 8.57, я установил шкалы значений обеих диаграмм вручную. Для этого понадобилось настраивать высоту графика. Если дать Excel возможность ус тановить шкалы автоматически, то они наверняка окажутся неправильными.
Наложение диаграмм — тонкая ручная работа, требующая экспериментирования. Учтите, что Excel имеет склонность автоматически настраивать размеры и положение элементов диаграмм, что наверняка испортит вашу работу. Поэтому отключайте авто матическую настройку везде, где только возможно, и обязательно проверяйте оконча тельный результат.
ОБЪЕДИНЕНИЕ ДВУХ ДИАГРАММ В ОДНОЙ РАМКЕ
На рис. 8.58 показано объединение объемной круговой диаграммы и объемной гистограммы. Общая рамка создает впечатление, будто это одна диаграмма. Однако это две отдельные диаграммы, а рамка принадлежит не им а ячейкам рабочего листа. У обеих диаграмм области построения и области диаграмм прозрачные. Кажущийся заголовок диаграмм фактически тоже им не принадлежит — это текст, введенный в
объединенные ячейки рабочего листа.
Объемы продаж по месяцам и регионам
^^1[^^^Н^^^^НВР\Рвгион
2 500 . |
10% ^ ^ шт шт |
2 000
1500
1000
500
Янв Фев Map Апр Май Июн Июл Аег Сен Окт Ноя Дек
Рис. 8.58. Благодаря общей рамке две отдельные диа граммы кажутся одним объектом
НАЛОЖЕНИЕ ОБЪЕМНЫХ ГИСТОГРАММ
Объемные гистограммы Excel подвержены существенному ограничению: они не позволяют выводить дополнительные ряды в глубину. Другими словами, это не ис тинно трехмерные гистограммы, а всего лишь обычные гистограммы с визуальным эффектом объема. Однако в Excel всегда существуют обходные пути. На рис. 8.59 по казаны три объемные гистограммы с накоплением (по одному для каждого года),
282 |
Часть П. Построение диаграмм |
объединенные так, чтобы создать третью ось в глубину. Диаграмма позволяет визу ально сравнить результаты по трем параметрам — регионам, месяцам и годам.
Результаты за 1 -й квартал 2000-2002
18 |
^ ^ ^ ^ ^ w |
|
|
|
16 |
|
|
|
|
1 p^fttttHl 1 |
|
|
|
|
14 |
|
|
|
|
|
|
|
|
|
12 |
|
|
|
|
10 |
|
|
|
|
в |
|
|
|
|
6 |
|
|
•^^^^^^^НР^^^ |
|
4 |
|
|
|
|
2 |
|
|
| | Н | ^ | Н р Р ^ 2001 |
|
0 |
Янв |
Фев |
И Н В И Р ^ 2000 |
1 |
|
Map |
|
Рис. 8.59. Тщательно наложив три объемные гистограммы, можно создать эффект истинно трехмерной диаграммы с третьей осью в глубину
Стены всех объемных гистограмм удалены. Оставлена единственная ось значений, принадлежащая передней гистограмме. Заголо вок, легенда, подписи осей и текстовая об ласть добавлены отдельно. Сочетая диафаммы таким способом, важно сохранить идентич ность шкал осей значений. Также учитывайте, что любая модификация объемной перспек тивы (например, вращение) должна выполняться для всех диафамм одновременно.
ИМИТАЦИЯ РАЗОРВАННОЙ ОСИ ЗНАЧЕНИЙ
На рис. 8.60 показана стандартная гистограмма с разорванной осью значений. Ис пользовать такую диаграмму удобно в том случае, когда несколько значений данных намного превосходят остальные. В приведенном примере значение данных за июль намного больше остальных значений. Если вывести такой ряд данных в обычной гистофамме, то все данные, кроме значения за июль, были бы трудноразличимы.
Бюро путешествий и экскурсий Доходы по месяцам
Яне Фев Map Апр Май Июн Июл Авг Сен Окт Ноя Дек
Рис. 8.60. Гистограмма, имитирующая разо рванную ось
Глава 8. Искусные приемы создания и использования диаграмм |
283 |
Гистограмма, приведенная на рис. 8.60, фактически состоит из двух диаграмм, показанных на рис. 8.61. Обе диаграммы выводят одни и те же данные, но шкалы оси значений у них разные. Главная диаграмма (слева) содержит заголовок и много свободного пространства над областью построения. В ее поле максималь ное значение введено число 1000. У второй диаграммы отключена рамка, установ лена прозрачная область диаграммы и введены минимальное и максимальное зна чения соответственно 4500 и 5000. Символ разрыва столбика создан с помощью автофигуры.
Бюро путешествий и экскурсий Доходы по месяцам
5 000 |
I |
4 900 |
|
4 800 |
|
4 700 |
|
4 600 |
|
4 500 |
Янв Фее Map Апр Май Июн Июл Авг Сен Окт Ноя Дек
Рис. 8.61. Диаграмма справа размещена поверх диаграммы, показанной слева
Создание такой диаграммы — ручной процесс, требующих многих уловок и неко торого мастерства.
В листе Лист2 рабочей книги Pic08_60_add.xls приведен еще один пример гистограммы с разорванной осью. Гистограмма выглядит несколько иначе. Максимальное значение переустановлено вручную. Автофигура, изображаю щая разрыв, установлена поверх столбика, а поле с текстом поясняет значе ние текущей категории.
Градиентные контурные диаграммы
Контурная диаграмма Excel (один из подтипов стандартной поверхностной диаграммы) изображает различные значения разными цветами. Она представляет собой как бы вид сверху на обычную поверхностную диаграмму. Количество цве тов устанавливается с помощью текстового поля цена основных делений, распо ложенного во вкладке Шкала диалогового окна форматирования оси значений. Чем меньше цена делений, тем больше цветов изображают значения ряда. По скольку используются произвольные цвета, различить значения может оказаться отнюдь не легко.
Мною разработана специальная надстройка Gradient Contour Chart, предназначен ная для преодоления некоторых ограничений Excel. На рис. 8.62 показаны стандарт ная ^сонтурная диаграмма (слева) и градиентная контурная диаграмма (справа), соз данная с помощью надстройки.
Надстройка Gradient Contour Chart предоставляет довольно много возможностей. Например, в ней можно задавать количество цветов (от 2 до 56), выбирать первый и последний цвета, выбирать один из двух наборов цветов радуги и т.д. Диалоговое ок но надстройки Gradient Contour Chart показано на рис. 8.63.
284 Часть П. Построение диаграмм
Контурная диаграяна Excel |
Градиентная контурная диагранаа |
Рис. 8.62. Диаграмма, расположенная справа, создана с помощью надстройки Gradient Contour Chart
mmmmmi
u«t«r«rtge; |
~2 |
f |
<k»!httixii»s - |
- '• |
|
|
'08_62'!$C$4;$AY$52 |
\ |
Г |
Rainbowifi3lue to red |
|
||
|
Г |
R^inbQW^rr«dtobtue |
|
|||
I Градиентная контурная диагр |
|
«S'Sfilertcoiori |
|
|
||
|
Cater lor яиййтш! vakiei |
|
||||
|
|
|
|
|||
' Op«on$ |
|
|
I H H H |
Change:J |
||
Г Output bjChartsheest |
|
Cater Ьж xfkw»stt vatue: |
|
|||
V togarftNwc spate |
|
|
т |
Ц ц |
dwxie:J |
|
|
|
|
||||
SidHnes: IBoth |
-31 |
|
yo. colors: |
1 56 |
d j |
|
^
Рис. 8.63. Диалоговое окно надстройки Gradi ent Contour Chart
Надстройка создает рисунок, который можно затем вставить в диаграмму или в лист данных. Вследствие этого сгенерированная диаграмма не яв ляется динамической. Если данные изменяются, то диаграмму нужно сге нерировать заново.
Визуальный вывод данных без диаграмм
Визуально изобразить количественную информацию без диаграмм можно с помо щью ячеек рабочего листа. В этом разделе рассматривается два способа создания "диаграмм без диаграмм". Однако диаграммы такого вида, конечно же, не предостав ляют такой степени точности, как "настоящие" диаграммы.
Визуальный вывод данных с помощью символов ASCII
На рис. 8.64 показана диаграмма из текстовых символов, образующих горизон тальные полоски. Столбцы Е и G содержат формулы, графически изображающие ме сячные колебания выполнения бюджета путем вывода символов шрифтом Wingdings. Количество символов определяется с помощью функции ЕСЛИ.
Глава 8. Искусные приемы создания и использования диаграмм |
285 |
1 ^ |
Chapter08_pic31-66.Kls |
|
|
|
9^^^^^^^^^^^^;1^Ш^-]!^\1 |
|||
|
1 A |
В |
С |
D |
|
|
\^^^^^^^^^: |
П| |
I ^ |
|
Выполнение |
Процент |
Недобор |
|
|
Щ\ |
|
Смета |
разности |
|
|
|||||
1 2 |
• |
300 |
З Ц |
3,7% |
|
Янв |
• •ЯМ |
|
|
^HB |
|
|
|||||
s |
Map |
300 |
305 |
1.7% |
|
Map |
• • |
|
Anp |
350 |
351 |
0,3% |
|
Апр |
|
''А 1 |
|
6 |
Май |
350 |
402 |
14,9% |
|
Май |
|
|
7 |
Июн |
350 |
409 |
16.9% |
|
Июн |
|
|
8 |
Июл |
500 |
421 |
-15,8% |
|
Июл |
|
|
9 |
Авг |
500 |
454 |
-9,2% |
|
Авг |
|
|
И 0 |
Сен |
500 |
474 |
-5.2% |
|
Сен |
|
|
M l |
Окт |
500 |
521 |
4.2% |
• • • • • |
Окт • • • • |
|
|
И 2 |
Ноя |
500 |
476 |
-4,8% |
Ноя |
|
|
|
13 Дек |
500 |
487 |
-2,6% |
••ш |
Дек |
|
|
|
14 |
|
|
|
|
- . ы _ |
|
- „^' )йВ\ |
|
pni »•ж:шLlг/oa::lJ:ж#~/^жxш^iz^ |
. х _ : ^ _ . - ^ |
|||||||
Рис. 8.64. Диаграмма составлена из текстовых символов, выведенных в ячейках
Используемые диаграммой данные находятся в столбцах А:С. Формулы столб ца D вычисляют процент разности между сметой и ее выполнением. Столбцы Е и G содержат формулы, использующие значения столбца D. Ниже приведены фор мулы, расположенные в ячейках Е2 и G2. Остальные формулы столбцов Е и G аналогичны им.
Е2: =ЕСЛИ(В2<0;ПОВТОР("П";-ОКРУГЛ(В2*100;0)); G2: =ЕСЛИ(D2>0;ПОВТОР("п";-ОКРУГЛ(D2*-100;О)) II II \
Принцип действия диаграммы основан на использовании функции ПОВТОР, кото рая выводит в ячейку символ (определенный в первом аргументе) столько раз, сколь ко задано во втором аргументе. В ячейке, выводящей полоску, установлен шрифт Wingdings, буква п в нем выводится как черный квадратик. В столбце Е текст выров нен по правому краю, а в столбце G — по левому.
В зависимости от числового диапазона данных может возникнуть необходимость в изменении масштаба "оси". Поэкспериментируйте, заменив в формулах значение 100. Кроме того, вы можете заменить символ п каким-либо другим, изменив таким обра зом внешний вид диаграммы.
Вывод с помощью средств условного форматирования
Средства условного форматирования Excel —• удобный инструмент, позволяющий устанавливать формат ячейки на основе ее содержимого. Многие пользователи не знают, что условное форматирование можно использовать также для установки фор матов ячеек на основе типов условий, а не только на основе их содержимого.
На рис. 8.65 показан рабочий лист, в котором условное форматирование использу ется для вывода грубой линейчатой диаграммы. Обратите внимание: столбцы специ ально сделаны узкими, чтобы ячейки были квадратными.
В рабочем листе определены две именованные формулы: Минимум (ячейка D1) и Максимум (ячейка D2). Эти ячейки управляют верхней и нижней границами шкалы "оси", расположенной в строке 4. Ячейка Е4 содержит ссылку на ячейку Минимум. Ниже приведена формула, расположенная в ячейке F4, Остальные формулы строки 4 аналогичны ей.
=((Максимум-Минимум)/ЧИСЛСТ0ЛБ(Р$1:Аа$1))*(СТОЛБЕЦ()-5)+Минимум
Формулы строки 4 генерируют длины интервалов от начала оси до текущей ячей ки. В них используется функция числостолв (вместо постоянного значения), воз вращающая общее число столбцов диаграммы. Благодаря этому пользователь может вставлять дополнительные столбцы без изменения формул.
286 |
Часть П. Построение диаграмм |
Глава 9
Сводные диаграммы
В этой главе...
• |
Что такое сводная таблица |
289 |
• |
Создание сводной таблицы |
293 |
• |
Что такое сводная диаграмма |
301 |
• |
Примеры сводных диаграмм |
307 |
Многие (включая автора) считают сводные таблицы одним из наиболее мощных аналитических средств Excel. Сводная таблица мгновенно преобразует огромное коли чество информации в небольшую аккуратную таблицу. Сводные таблицы появились еще в Excel 5. Начиная с Excel 2000, сводные таблицы расширены средствами по строения сводных диаграмм.
Если у вас установлена версия Excel 97 или более ранняя, то вы не сможете воспользоваться значительной частью материала этой главы.
Глава начинается с вводного обзора сводных таблиц, затем рассматриваются свод ные диаграммы.
Что такое сводная таблица
Это динамическая итоговая таблица, обобщающая результаты базы данных, т.е. других таблиц, которые могут быть расположены как в этом же рабочем листе, так и во внешних файлах.
Сводная таблица способна генерировать различные обобщения исходных данных, например распределение частот или итоговые суммы нескольких различных измере ний данных. Кроме того, с помощью сводной таблицы можно выводить промежуточные обобщающие данные с любым уровнем детализации. Важнейшее свойство сводных таблиц — их интерактивность. После создания сводной таблицы информацию можно переупорядочивать самыми разными способами. Можно даже создавать пользовательские группы обобщающих данных (например, объединять ито говые данные по северным и восточным регионам).
В пределах Excel термин сводная таблица (pivot table) уникален. Она названа так (pivot — стержень) потому, что заголовки строк и столбцов таблицы можно как бы вращать вокруг основной области данных, предоставляя обобщенные данные под раз личными углами зрения.
Один из недостатков использования сводных таблиц вместо обобщающих отчетов, ос нованных на формулах, состоит в том, что сводная таблица не обновляется автоматически при изменении исходных данных. Однако этот недостаток не представляет серьезной про блемы, поскольку для обновления сводной таблицы достаточно одного щелчка на кнопке Обновить данные, расположенной на панели инструментов Сводные таблицы.