Робота з бульбашковими діаграмами

Бульбашкова діаграма (Bubble chart) – це діаграма розсіювання, в якій кожна точка даних визначається трьома параметрами, а не двома, як звичайно У точкової (Ху chart) діаграмі, що є більш загальною формою діаграми розсіювання, позиція кожної точки визначається двома параметрами – координатами х і у Те ж саме відноситься і до бульбашкової діаграмі, але в ній кожна точка даних являє собою пухирець, розмір якого вказує третій параметр На рис 273 таблиця в осередках A1: D6 включає в себе три стовпці даних – число конкурентів, продажу в мільйонах грошових одиниць і відсоток охоплення ринку Позиція кожного бульбашки на діаграмі визначається значеннями у стовпцях В і С Розміри бульбашок задають значення в стовпці D

Рис 273 На цій бульбашкової діаграмі розмір бульбашок визначається значеннями в стовпці

D, а позиції бульбашок – значеннями у стовпцях В і С

При виділенні низки на бульбашкової діаграмі Excel обводить вихідні дані трьома прямокутниками замість звичайних двох Третій, зелений прямокутник охоплює діапазон комірок з даними, що відповідають за діаметр (або площа) бульбашок Цей діапазон є також пятий аргументом формули РЯД, а на вкладці Ряд діалогового вікна Вихідні дані зявляється додаткове поле Розміри (Sizes)

Якщо ви виділите ряд бульбашок, виберете команду Виділений ряд в меню Формат і відкриєте вже знайому вкладку Параметри вікна Формат ряду даних, то побачите кілька додаткових елементів форматування (Рис 274) Перемикач значеннямвідповідає (Size Represents) визначає, чи будуть вихідні значення

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

Рис 274 Додаткові параметри форматування ряду бульбашкової діаграми

Великий недолік бульбашкових діаграм Excel, як і точкових, полягає в тому, що програма не вміє створювати акуратні підписи даних Якщо ви виберете команду Параметри діаграми (Chart Options) у меню Діаграма (Chart), то на вкладці Підписи даних (Data Labels) діалогового вікна, знайдете прапорець, що дозволяє відобразити значення розміру бульбашки у вигляді підпису На жаль, за замовчуванням підпис розміщується справа від бульбашки і може опинитися в центрі його сусіда (рис 275) Щоб вирішити цю проблему, потрібно спочатку створити підписи, після цього виділити їх, вибрати команду Виділені підписи даних (Selected Data Labels) у меню Формат і потім на вкладці Вирівнювання (Alignment) вікна вибрати інший варіант розміщення підписів на поле Положення підпису (Label Position)

Швидше за все, підписи, які ви хотіли б бачити на діаграмі (у нашому прикладі з рис 275 це вироби в стовпці А), вам не вдасться отримати, спираючись тільки на стандартні можливості форматування Ви можете ввести власні підписи вручну, дотримуючись процедури, описаної в розділі «Редагування підписів даних» глави 25 Ви також можете скористатися процедурою VBA, наведеною в розділі

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

На діаграмі-прикладі бажано включити в підписи даних як назви продуктів (стовпець А), так і частку володіння ринком (стовпець D) Кращий підхід – звести разом значення в стовпцях А і D в новому діапазоні осередків Наприклад, якщо ви введете формулу

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

Рис 275 Коли підписи даних створюються за замовчуванням, підпис бульбашки може опинитися всередині сусіднього бульбашки

Для відображення точок даних, залежних від трьох параметрів, також підходять пелюсткові діаграми з трьома осями Причому три параметри для пелюсткових діаграм далеко не межа, але гарні вони тільки тоді, коли значення всіх параметрів потрапляють приблизно в один і той же числовий діапазон На бульбашкової діаграмі координати х, у і розміри бульбашок можуть бути величинами різного порядку – як це фактично відбувається на рис 273

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

Пелюсткова діаграма (Radar chart) – це діаграма, в якій кожна точка даних отримує окрему копію єдиною осі значень ці копії розподіляються радіально навколо центральної точки Оскільки кожна точка даних володіє власною копією осі значень, діаграмі не потрібна вісь категорій Малюнок 276 демонструє одне з цих чудових творінь

Три ряди, Сила, Швидкість і Витривалість, складаються з 15 точок даних у кожному Отже, діаграма містить 15 копій осі значень Тільки на одній з цих копій (з написом Александров) маються підписи поділок шкали У всіх інших відносинах всі копії мають однакові параметри форматування і масштабу – і це основна вимога до діаграм такого типу Ряди розрізняються за формою маркерів (Ромбоподібні для ряду Сила, квадратні для ряду Швидкість і трикутні для ряду Витривалість) і за кольором ліній Лінії і маркери дозволено видаляти і форматувати точно так само, як і на графіках

Рис 276 Так як ця пелюсткова діаграма відображає по 15 точок для кожного з трьох рядів даних, в ній використовуються 15 копій осі значень

Діаграма на рис 276 представляє наступні дані:

Якщо лінії рядів відображаються, кожен ряд стає замкнутим багатокутником Вивчаючи розташування багатокутників, ви зможете зрозуміти сенс діаграми На рис 276, наприклад, всі три багатокутника сходяться в загальній точці Ломтяев Це означає, що Ломтяев володіє розглянутими фізичними якостями в найвищій мірі Судячи по трьох увігнутим всередину лініях, які перетинають промінь до точки Колосов, можна припустити, що Колосов має середні показники, і т д

При створенні пелюсткової діаграми Excel за замовчуванням відображає основні лінії сітки для осей значень Сітка допомагає визначити значення кожної точки даних, але при цьому багатокутники рядів стають менш помітними (рис 277) Якщо лінії негативно впливають на наочність діаграми, виберіть команду Параметри діаграми в меню Діаграма і потім на вкладці Лінії сітки (Gridlines) вікна зніміть відповідний прапорець

Рис 277 Лінії сітки надають діаграмі вид павутини і знижують її наочність

Чим менше точок і рядів даних відображається на діаграмах будь-якого типу, тим легше їх розуміти У разі пелюсткової діаграми має сенс обмежити кількість точок даних Так, на діаграмі з трьома крапками даних три копії осі значень розташовані під прямим кутом одна до іншої і нагадують відому систему координат xyz Багатокутники в цьому випадку зводяться до трикутниками і можуть сприйматися як пересічні площини Таку діаграму (рис 278) простіше інтерпретувати, ніж представлену на рис 276

У кожному разі пелюсткова діаграма хороша тільки для рядів даних, значення яких потрапляють у загальний числовий діапазон Оскільки тут вам недоступна допоміжна вісь значень з іншого шкалою, як в діаграмах деяких типів,

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

діапазони значень яких практично непорівнянні

Рис 278 Не слід ускладнювати діаграму, особливо пелюсткову

Створення діаграм Ганта

Діаграми Ганта рекомендується створювати за допомогою програми управління проектами, такий як Microsoft Project Але при необхідності подобу такої діаграми можна отримати в Excel Малюнок 279 демонструє примітивну діаграму Ганта, створену засобами Excel

Тут показана діаграма типу Ширяючі бруски (Floating Bars) Цей тип ви знайдете на вкладці Нестандартні (Custom Types) діалогового вікна Тип діаграми (Chart Type) На діаграмі представлені два ряду – початкова дата і тривалість завдання Початкова дата відображається як зсув від деякої точки відліку Завдання 1, наприклад, відраховується прямо з цієї точки, Завдання 2 бере початок в 4-му дні після стартової точки і т д Перший ряд зроблений невидимим (тобто відформатований без кордону та без заливки) Ряд тривалості відображений у вигляді горизонтальних прямокутників, розташованих поверх ряду початкових дат Таким чином, кожна точка в ряді тривалості зміщується в горизонтальному напрямку на значення відповідної точки в ряді початкових дат

Цього не видно на чорно-білому малюнку (рис 279), але в діаграмі типу Ширяючі бруски використовується зелена градієнтна заливка області діаграми Підписи поділок шкали на обох осях мають світлий колір, контрастний щодо такого темного

фону Якщо зелений колір вам не подобається, можете відформатувати діаграму небудь інакше

Рис 279 На основі діаграми типу Ширяючі бруски можна створити просту діаграму Ганта

Ви вправі також змінити порядок відображення по осі категорій на зворотний, щоб перша задача розташовувалася вгорі, а остання – внизу (традиційна практика в діаграмах Ганта) Для цього виділіть вісь категорій, виберіть команду Виділена вісь в меню Формат і потім на вкладці Шкала вікна Формат осі встановіть прапорець Зворотний порядок категорій (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) встановіть прапорець Невидима), а потім збільшите розмір шрифту підписів ділень і виведете їх в режимі нижнього індексу (Якщо не зробити останнього, підписи ділень будуть розташовані поверх ліній сітки) Результат усіх цих маніпуляцій показаний на рис 2710

Недолік тут у тому, що на типовій діаграмі підписи поділок зміщуються занадто далеко всередину області малювання Кращі результати дає видалення підписів ділень і імітація їх текстовими полями, розташувати які доведеться вручну Ця техніка застосована до діаграми, показаної на рис 2711

Рис 2710 Ви можете розташувати підписи поділок під лініями сітки, якщо змістив вісь значень вправо і приховаєте її, а потім відображуватимете підписи у вигляді нижнього індексу

Рис 2711 Найкраще, але більш складне рішення – створити для підписів ділень текстові поля і розташувати їх вручну

Форматування виділених ліній сітки або підписів ділень

Ви можете виділити і відформатувати окремі маркери і підписи даних, але, коли справа стосується ліній сітки або підписів ділень, Excel «пасує» Якщо потрібно бачити тільки певні лінії сітки або мітки поділок, нічого не залишається, окрім як імітувати підписи текстовими полями або вдатися до панелі інструментів Малювання (Drawing), щоб самостійно накреслити лінії сітки Панель інструментів Малювання буде корисна також і в тому випадку, якщо ви захочете вибірково збільшити товщину осі або ліній сітки

Ступінчасті підписи поділок

На діаграмі розподілу на лідерів і відстаючих, зображеної на рис 2712, підписи ділень знаходяться зліва від позитивних смужок і праворуч від негативних Проте зробити це в Excel безпосередньо не вдається

Рис 2712 Діаграма лідерів і відстаючих насправді зібрана з двох акуратно вирівняних діаграм

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

Потім ви можете задати для кожного ряду даних однаковий розмір перекриття і ширини зазору, щоб всі смужки виглядали так, як якби вони належали одній діаграмі Якщо у вас значно більше позитивних смужок, ніж негативних, або навпаки, то вам слід відобразити кілька додаткових значень-заглушок в ряду з меншою кількістю точок і потім зробити ці значення і їх підписи невидимими

Відображення власної лінії прогнозу

Алгоритм побудови лінії тренда в Excel заснований на застосуванні регресійного аналізу до існуючих даних і, звичайно, дозволяє продовжити лінію регресії як в минуле, так і в майбутнє час Припустимо, вам потрібен прогноз тільки на майбутнє

– причому на основі своїх власних припущень, незважаючи на математику

У цьому випадку слід відобразити два ряди: один – для наявних даних, інший – для пророкуються Якщо ви простежите за тим, щоб обидва набору даних мали точку дотику (на рис 2713 значення для вересня), два графіка зєднаються Використовуйте інший тип лінії для другого ряду, і у вас зявиться свій власний оракул

Рис 2713 Ви можете намалювати персональну лінію прогнозу, відобразивши два ряди з загальною точкою даних

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*