Робимо підписи даних на діаграмах Excel інформативнішими

Доброго дня, друзі!

Невдало якось розпочався для мене рік 2013: роботи немає, дитина захворіла, настрою відповідно теж немає… Однак, я намагаюсь зберігати оптимізм – готуюсь до нових тренінгів, лікую доньку, тренуюсь (тренуюсь і знову тренуюсь), пишу статті, пишу в Twitter і блог.

І ось, сьогодні я згадав, що вже дуже давно не писав нічого про використання Microsoft Office, і вирішив поділись невеличким секретом створення красномовних діаграм у Excel.

Звичайно ж, всі ви знаєте, що в Excel елементарно увімкнути підписи даних на діаграмі – тоді, поруч, наприклад, зі стовпчиками на гістограмі з’являються відповідні числові значення. Але що робити, коли хочеться, щоб поруч зі стовпчиками даних були не просто числа, а щось складніше – наприклад, результати обчислень за певними формулами? Виявляється, зробити це досить просто і зараз я розповім вам як саме.

Завдання

Уявімо собі таку ситуацію.

У нас є числові дані про результати роботи співробітників нашої компанії за поточний і минулий роки (наприклад, сума продажів за угодами, підписаними кожним співробітником). Нам необхідно візуалізувати ці дані і дати їх порівняльну характеристику.

Знайоме завдання, чи не так?

Початкові дані можуть виглядати, наприклад, ось так:

Підготовчі дії

Далі ми робимо дуже прості дії.

У рядку, що слідує за рядком з сумою за 2011 рік, у комірці стовпчика, який відповідає кожному зі співробітників, пишемо просту формулу обчислення різниці між показниками за два роки.

Наприклад, для Миколайчука  – у комірці B17 ми пишемо таку формулу:

 =ABS(B16/B15-1)

і не забуваємо увімкнути процентний формат за допомогою відповідної кнопки у групі Number на вкладинці Home:

Далі ми “розтягуємо” формулу на інші комірки рядка (для інших співробітників), потягнувши за маркер у правому нижньому кутку комірки з формулою.

Результат цих маніпуляцій виглядає так:

У наступному рядку ми пишемо іншу формулу, яка порівнює відповідні значення для кожного співробітника за два роки і виводить у комірку, в яку її записано, слова “зростання на” або “зниження на” в залежності від результату. Наприклад, для того ж Миколайчука:

=IF(B15>B16;"зростання на ";"зниження на ")

Знову ж таки, поширюємо формулу на інші комірки рядка і отримуємо ось таку картинку:

Далі, у наступному рядку, ми пишемо формулу, яка “зклеює” результати обчислень за формулами у відповідних комірках двох попередніх рядків. Для того ж Миколайчука ми напишемо:

=B18&TEXT(Table4[[#Totals];[Миколайчук]];"0%")

Результат має бути таким:

Все, підготовчі роботи завершено!

Звичайно ж можна було записати формулу для отримання повідомлення про зміну показників у одну комірку. Ми “розмазали” її за трьома комірками – записали поетапно – лише для підвищення наочності і “читабельності”. Наступний крок – побудова діаграми.

Будуємо діаграму

Виділяємо за допомогою миші перші 16 рядків нашої таблиці (початкові дані) і натискаємо кнопку Recommended Charts у групі Charts на вкладинці Insert:

Обираємо потрібний нам тип діаграми (Clustered Bar):

Тепер треба залишити на діаграмі лише потрібні нам рядки початкових даних – показники за 2011 і 2012 роки.

Вибираємо мишею діаграму і натискаємо Select Data у групі Data на вкладинці Chart Tools > Design:

Просто знімаємо відмітки перед назвами усіх місяців у полі Horizontal (Category) Axis Labels, залишаючи лише дані за два роки:

Натискаємо Ok. Вже непогано, чи не так?

Трохи змінюємо вигляд діаграми – виділяємо діаграму мишею і натискаємо Change Chart Type у групі Type на вкладинці Chart Tools > Design:

Обираємо інший спосіб групування стовпчиків:

Вже краще:

Далі – справа техніки.

Додаємо підписи даних

Навпроти кожної пари стовпчиків малюємо текстове поле (кнопка Text Box у групі Text на вкладинці Insert):

Можемо також додати назву діаграми. Результат має бути таким:

Про всяк випадок нагадую, що після додавання першого текстового поля його можна “розмножити”, перетягуючи при затисненій клавіші CTRL.

Тепер нам треба вмістити у кожне текстове поле результати порівняння даних – вміст відповідних комірок рядка 19. Зробити це просто. Наприклад, для того ж Миколайчука ми натискаємо на відповідне йому текстове поле, потім клацаємо мишею на рядок формул і пишемо:

=Sheet1!$B$19

Повторюємо цю процедуру для всіх інших текстових полів, підбираємо шрифт, змінюємо заливку текстових полів за смаком і отримуємо картинку на кшталт такої:

Все, справу зроблено!

Чудово і інформативно, чи не так?

Так, це трохи довше ніж побудова простої типової діаграми, але результат того вартий. І в принципі, все це дуже просто – настільки, що навіть не прийде в голову багато кому з нас. Особисто я прочитав про подібні речі тут.

До зустрічі за тиждень!