Работа с пузырьковыми диаграммами

Пузырьковая диаграмма (bubble chart) — это диаграмма рассеяния, в которой каждая точка данных определяется тремя параметрами, а не двумя, как обычно. В точечной (ху chart) диаграмме, являющейся более общей формой диаграммы рассеяния, позиция каждой точки определяется двумя параметрами — координатами х и у. То же самое относится и к пузырьковой диаграмме, но в ней каждая точка данных представляет собой пузырек, размер которого указывает третий параметр. На рис. 27.3 таблица в ячейках A1:D6 включает в себя три столбца данных — число конкурентов, продажи в миллионах денежных единиц и процент охвата рынка. Позиция каждого пузырька на диаграмме определяется значениями в столбцах В и С. Размеры пузырьков задают значения в столбце D.

Рис. 27.3. На этой пузырьковой диаграмме размер пузырьков определяется значениями в столбце

D, а позиции пузырьков — значениями в столбцах В и С

При выделении ряда на пузырьковой диаграмме Excel обводит исходные данные тремя прямоугольниками вместо обычных двух. Третий, зеленый прямоугольник охватывает диапазон ячеек с данными, отвечающими за диаметр (или площадь) пузырьков. Этот диапазон является также пятым аргументом формулы РЯД, а на вкладке Ряд диалогового окна Исходные данные появляется дополнительное поле Размеры (Sizes).

Если вы выделите ряд пузырьков, выберете команду Выделенный ряд в меню Формат и откроете уже знакомую вкладку Параметры окна Формат ряда данных, то увидите несколько дополнительных элементов форматирования (рис. 27.4). Переключатель Значениям соответствует (Size Represents) определяет, будут ли исходные значения

задавать площадь пузырьков или их диаметр (большой разницы между этими двумя вариантами нет). Флажок Отрицательные значения (Show Negative Bubbles) позволяет включить в диаграмму пузырьки для отрицательных значений. Такие значения по умолчанию графически не представляются (даже как обычные точки). При установке флажка Excel отобразит отрицательные значения как незаполненные пузырьки, чтобы их было легко отличить от положительных чисел. На вкладке Параметры вы также можете изменить масштабный коэффициент для размеров пузырьков.

Рис. 27.4. Дополнительные параметры форматирования ряда пузырьковой диаграммы

Большой недостаток пузырьковых диаграмм Excel, как и точечных, заключается в том, что программа не умеет создавать аккуратные подписи данных. Если вы выберете команду Параметры диаграммы (Chart Options) в меню Диаграмма (Chart), то на вкладке Подписи данных (Data Labels) появившегося диалогового окна найдете флажок, позволяющий отобразить значение размера пузырька в виде подписи. К сожалению, по умолчанию подпись размещается справа от пузырька и может оказаться в центре его соседа (рис. 27.5). Чтобы решить эту проблему, нужно сначала создать подписи, после этого выделить их, выбрать команду Выделенные подписи данных (Selected Data Labels) в меню Формат и затем на вкладке Выравнивание (Alignment) открывшегося окна выбрать другой вариант размещения подписей в поле Положение подписи (Label Position).

Скорее всего, подписи, которые вы хотели бы видеть на диаграмме (в нашем примере с рис. 27.5 это изделия в столбце А), вам не удастся получить, опираясь только на стандартные возможности форматирования. Вы можете ввести собственные подписи вручную, следуя процедуре, описанной в разделе «Редактирование подписей данных» главы 25. Вы также можете воспользоваться процедурой VBA, приведенной в разделе

«Генерация подписей для точечных диаграмм» той же главы, чтобы создать подписи данных из текстовых значений диапазона ячеек (в нашем случае диапазона А2:А6).

На диаграмме-примере желательно включить в подписи данных как названия продуктов (столбец А), так и долю владения рынком (столбец D). Лучший подход — свести вместе значения в столбцах А и D в новом диапазоне ячеек. Например, если вы введете формулу

=A2&","&TEKCT(D2,"0%") в ячейку Е2 и затем продублируете ее вниз до ячейки Е6, то получите столбец, содержащий название изделия, за которым будут следовать запятая и пробел, а потом аккуратно отформатированное значение доли рынка. Затем вы можете создать подписи данных на диаграмме, подав этот столбец на вход макросу Подписи_диаграмм, код которого приведен в главе 25 «Улучшение внешнего вида диаграмм».

Рис. 27.5. Когда подписи данных создаются по умолчанию, подпись пузырька может оказаться внутри соседнего пузырька

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

Работа с лепестковыми диаграммами

Лепестковая диаграмма (radar chart) — это диаграмма, в которой каждая точка данных получает отдельную копию единственной оси значений; эти копии распределяются радиально вокруг центральной точки. Поскольку каждая точка данных обладает собственной копией оси значений, диаграмме не нужна ось категорий. Рисунок 27.6 демонстрирует одно из этих замечательных творений.

Три ряда, Сила, Скорость и Выносливость, состоят из 15 точек данных в каждом. Следовательно, диаграмма содержит 15 копий оси значений. Только на одной из этих копий (с надписью Александров) имеются подписи делений шкалы. Во всех остальных отношениях все копии имеют одинаковые параметры форматирования и масштаба — и это основное требование к диаграммам такого типа. Ряды различаются по форме маркеров (ромбовидные для ряда Сила, квадратные для ряда Скорость и треугольные для ряда Выносливость) и по цвету линий. Линии и маркеры позволено удалять и форматировать точно так же, как и на графиках.

Рис. 27.6. Так как эта лепестковая диаграмма отображает по 15 точек для каждого из трех рядов данных, в ней используются 15 копий оси значений

Диаграмма на рис. 27.6 представляет следующие данные:

Если линии рядов отображаются, каждый ряд становится замкнутым многоугольником. Изучая расположение многоугольников, вы сможете понять смысл диаграммы. На рис. 27.6, например, все три многоугольника сходятся в общей точке Ломтяев. Это означает, что Ломтяев обладает рассматриваемыми физическими качествами в наивысшей степени. Судя по трем вогнутым внутрь линиям, пересекающим луч к точке Колосов, можно предположить, что Колосов имеет средние показатели, и т. д.

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

Рис. 27.7. Линии сетки придают диаграмме вид паутины и снижают ее наглядность

Чем меньше точек и рядов данных отображается на диаграммах любого типа, тем легче их понимать. В случае лепестковой диаграммы имеет смысл ограничить количество точек данных. Так, на диаграмме с тремя точками данных три копии оси значений расположены под прямым углом одна к другой и напоминают известную систему координат x-y-z. Многоугольники в этом случае сводятся к треугольникам и могут восприниматься как пересекающиеся плоскости. Такую диаграмму (рис. 27.8) проще интерпретировать, чем представленную на рис. 27.6.

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

лепестковая диаграмма практически бесполезна для отображения рядов данных,

диапазоны значений которых практически несопоставимы.

Рис. 27.8. Не следует усложнять диаграмму, особенно лепестковую

Создание диаграмм Ганта

Диаграммы Ганта рекомендуется создавать при помощи программы управления проектами, такой как Microsoft Project. Но при необходимости подобие такой диаграммы можно получить в Excel. Рисунок 27.9 демонстрирует примитивную диаграмму Ганта, созданную средствами Excel.

Здесь показана диаграмма типа Парящие бруски (Floating Bars). Этот тип вы найдете на вкладке Нестандартные (Custom Types) диалогового окна Тип диаграммы (Chart Type). На диаграмме представлены два ряда — начальная дата и длительность задачи. Начальная дата отображается как смещение от некоторой точки отсчета. Задача 1, например, отсчитывается прямо с этой точки, Задача 2 берет начало в 4-м дне после стартовой точки и т. д. Первый ряд сделан невидимым (то есть отформатирован без границы и без заливки). Ряд длительности отображен в виде горизонтальных прямоугольников, расположенных поверх ряда начальных дат. Таким образом, каждая точка в ряде длительности смещается в горизонтальном направлении на значение соответствующей точки в ряде начальных дат.

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

фона. Если зеленый цвет вам не нравится, можете отформатировать диаграмму как-нибудь иначе.

Рис. 27.9. На основе диаграммы типа Парящие бруски можно создать простую диаграмму Ганта

Вы вправе также изменить порядок отображения по оси категорий на обратный, чтобы первая задача располагалась вверху, а последняя — внизу (традиционная практика в диаграммах Ганта). Для этого выделите ось категорий, выберите команду Выделенная ось в меню Формат и затем на вкладке Шкала окна Формат оси установите флажок Обратный порядок категорий (Categories In Reverse Order).

Подписи делений оси значений — 0, 10, 20, 30 — показывают количество дней, прошедших после даты отсчета диаграммы. Наверное, вам было бы удобнее видеть здесь фактические даты, но в данном случае Excel уже сделал все, что мог. (Поскольку это ось значений, нельзя воспользоваться формулой РЯД или диалоговым окном Исходные данные для того, чтобы указать для подписей другой диапазон.) Если для вас действительно актуально отобразить даты, следует сначала удалить подписи делений (выделите ось значений, выберите команду Выделенная ось в меню Формат, щелкните на вкладке Вид окна Формат оси и установите флажок Нет (None) в группе Метки делений (Tick Mark Labels)), а затем создать текстовые поля для нужных дат. Увы, делается это только вручную.

При необходимости показать стрелки зависимостей и другие средства диаграммы Ганта вам потребуется серьезно поработать инструментами панели Рисование (Drawing). Но если вам это на самом деле нужно, не лучше ли воспользоваться программой управления проектами?

Специфическое форматирование

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

Чтобы добиться от диаграмм Excel максимального эффекта, приходится их упрощать —

уменьшать количество элементов, создаваемых по умолчанию. Например, если ваша цель

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

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

Подписи делений без осей

При удалении оси диаграммы, как вы, несомненно, замечали, подписи делений исчезают вместе с осью. А если вам нужны только подписи делений, без оси? Не удаляйте ось, а выделите ее, выберите команду Выделенная ось в меню Формат. Затем в появившемся диалоговом окне перейдите на вкладку Вид и установите флажок Невидимая (None) в группе Ось (Lines).

Подписи делений в области рисования

Часто просится такой способ снизить сложность диаграммы — удалить ось значений и расположить метки делений под линиями сетки в области рисования. К сожалению, в Excel это сделать довольно затруднительно. Вы можете достичь нужного результата, если переместите ось значений внутрь диаграммы (выделите ось категорий, выберите команду Выделенная ось в меню Формат, щелкните на вкладке Шкала (Scale) и введите число

2 в поле Пересечение с осью Y (значений) в категории номер (Value (Y) Axis Crosses At Category Number)), скроете ось значений (выделите ее, выберите команду Выделенная ось в меню Формат, щелкните на вкладке Вид и в группе Ось (Lines) установите флажок Невидимая), а затем увеличите размер шрифта подписей делений и выведете их в режиме нижнего индекса. (Если не сделать последнего, подписи делений будут расположены поверх линий сетки.) Результат всех этих манипуляций показан на рис. 27.10.

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

Рис. 27.10. Вы можете расположить подписи делений под линиями сетки, если сместите ось значений вправо и скроете ее, а затем отобразите подписи в виде нижнего индекса

Рис. 27.11. Лучшее, но более сложное решение — создать для подписей делений текстовые поля и расположить их вручную

Форматирование выделенных линий сетки или подписей делений

Вы можете выделить и отформатировать отдельные маркеры и подписи данных, но, когда дело касается линий сетки или подписей делений, Excel «пасует». Если требуется видеть только определенные линии сетки или метки делений, ничего не остается, кроме как имитировать подписи текстовыми полями или прибегнуть к панели инструментов Рисование (Drawing), чтобы самостоятельно начертить линии сетки. Панель инструментов Рисование будет полезна также и в том случае, если вы захотите выборочно увеличить толщину оси или линий сетки.

Ступенчатые подписи делений

На диаграмме распределения на лидеров и отстающих, изображенной на рис. 27.12, подписи делений находятся слева от положительных полосок и справа от отрицательных. Однако сделать это в Excel напрямую не удается!

Рис. 27.12. Диаграмма лидеров и отстающих на самом деле собрана из двух аккуратно выровненных диаграмм

Показанная картинка на самом деле составлена из двух диаграмм, аккуратно примкнутых друг к другу так, чтобы их оси категорий располагались на одной линии. На нижней диаграмме подписи делений расположены в позиции Вверху (выделите ось, выберите команду Выделенная ось в меню Формат, перейдите на вкладку Вид открывшегося окна и установите флажок Вверху (High) в группе Метки делений (Tick Mark Labels)). Такой вид выравнивания годится, когда обе диаграммы имеют одинаковое число точек.

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

Отображение собственной линии прогноза

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

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

В этом случае следует отобразить два ряда: один — для имеющихся данных, другой — для предсказываемых. Если вы проследите за тем, чтобы оба набора данных имели точку соприкосновения (на рис. 27.13 значение для сентября), два графика соединятся. Используйте другой тип линии для второго ряда, и у вас появится свой собственный оракул.

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

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.