Невдало якось розпочався для мене рік 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

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

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

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

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

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

Суть питання полягала ось в чому: є файл Microsoft Excel, в якому містяться дані про співробітників компанії – ім’я, посада, хто кому підпорядкований, електронна пошта, телефон, та ін. На основі цих відомостей треба побудувати організаційну діаграму.

Звичайно, ми можемо скористатися можливостями SmartArt для побудови красивої діаграми, однак, в цьому випадку всі дані доведеться вводити заново (або копіювати з фапйлу Excel)! Хотілося б отримати діаграми, доклавши мінімум зусиль, можливо, навіть автоматично. Чи це можливо?

Звичайно ж, можливо! І допоможе нам в цьому Microsoft Visio.

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

Ось приклад “правильного” файлу (звичайно ж, сформованого методом “зі стелі”):

Переконались, що з вихідними даними все гаразд? Відкриваємо Microsoft Visio, і в групі шаблоній Business вибираємо Organization Chart Wizard:

Далі все просто – тиснемо на кнопку Create, запускається майстер, якому ми вказуємо на наш файл з відомостями про співробітників, і вказуємо, якою мовою вони в нас записані:

Далі треба вказати, в якому полі містяться і’я співробітника, та ім’я його менеджера:

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

Далі вказуємо, які поля додати до фігур діаграми у якості зв’язаних полів даних:

Якщо людей в нас праціє не дуже багато (діаграма вміститься на одному аркуші), натискаємо Next:

Далі, якщо ви все ж таки обрали кілька сторінок, переглядаємо (і за потреби підправляємо) інформацію, яка буде міститися на кожній з сторінок, і натискаємо Finish:

Все справу зроблено! Милуємось результатом:-)

Як бачите, ми створили цю діаграму повністю автоматично, не ввівши з клавіатури жодного символа, і не скориставшись буфером обміну. Можливо, нам все ж доведеться трошки її підредагувати і прикрасити, однак, це вже дрібниці. Чи не правда, чудовий спосіб економії часу?

І це ще не все – в арсеналі Visio ще чимало подібних прийомів еффективного використання даних, які місяться в файлах інших програм Microsoft Office. Можливо, скоро я про них розповім на сторінках цього блогу. Побачимось!:-)