колірної статус
Часто виникає необхідність в звітах акцентувати статус будь-якого процесу за допомогою кольору. При цьому досить популярна метафора світлофора. Якщо все добре - зелений статус, щось насторожує - жовтий, все погано - червоний. Таким чином, той, хто дивиться ваші звіти, має можливість за кольором відразу звертатися до проблемних цифрам. Звіти стають більш наочними, інтуїтивно зрозумілими і економлять час переглядають.
Обмеження умовного форматування
В Excel є стандартний інструмент, який вирішує цю задачу, - умовне форматування за допомогою набору значків. Інструмент відмінний, але в деяких ситуаціях вам його може бути недостатньо. Я, наприклад, бачу таку проблему: дані значки досить дрібні і добре виглядають тільки в своєму оригінальному розмірі. Якщо вам буде потрібно значок побільше і / або цікавіше, то доведеться його робити самому за допомогою фігур.
Фігурами в MS Office можна намалювати все, що завгодно. Серйозно. Будь-який складний малюнок "збирається" з простих елементів. Це питання тільки часу і старань. У цій статті ми будемо управляти ось такими нескладними, але досить привабливими світлофорами, які легко робляться з фігур овал (коло - окремий випадок овалу / еліпса) і кільце.
Ми хочемо візуалізувати співвідношення фактичних і планових витрат по проектам за допомогою наших світлофорів. Ось так:
послідовність кроків
Для початку підготуємо дані, на основі яких будемо обчислювати статуси. На нашому прикладі це стовпці: Проект. Бюджет. Факт.
Підготуємо допоміжну таблицю, на основі якої будемо присвоювати значення статусів. У нашому випадку ця таблиця розташовується на аркуші Налаштування. оформлена у вигляді розумної таблиці з назвою Шкала. Статус G означає Green (зелений), Y - Yellow (жовтий), R - Red (червоний).
У осередок E3 листа Статуси введена формула
= ЕСЛІОШІБКА (ВВР ((D3-C3) / C3; Шкала; 2); "D").
Як бачите, ми знаходимо різницю між фактом і бюджетом і ділимо її на бюджет. Мінімальне значення цього співвідношення -1 (мінус одиниця) досягається при нульових фактичних витратах. Цей факт визначає порогове значення (-1 = -100%) для статусу G в таблиці Шкала. Поріг початку жовтого кольору ви визначаєте самі - у мене він 0%. Тобто зелений колір повинен бути у всього, що в діапазоні від -100% до 0%. Жовтий - від 0% до 15%. Червоний - 15% і вище. Для вибору значення з Шкали ідеально підходить формула ВВР в своїй діапазонної версії, яка іщёт діапазон, в який потрапляє значення ((D3-C3) / C3) в довіднику (Шкала), і повертає з довідника вміст комірки на перетині знайденої рядки і зазначеного стовпця (2). Якщо обчислення функції ВПР (VLOOKUP) закінчується помилкою (наприклад, коли Бюджет = 0), то формула ЕСЛІОШІБКА (IFERROR) її перехоплює і повертає в осередок значення D. що буде означати, що світлофор не горить (сірий). Формулу з E3 поширюємо на E4: E5.
Формат даних діапазону E3: E5 встановлюємо в ";;;", що запобігає появі значень осередків на екрані, щоб цифри не визирали з-за світлофорів, які ми помістимо над цими осередками.
Створюємо іменований діапазон rngTrafLight для осередків E3: E5.
Створюємо з фігур наші світлофори. Кола, колір яких ми будемо міняти, називаємо іменами figTL1 для E3. figTL2 для E4 і figTL3 для E5. Маємо в своєму розпорядженні фігури, там де вони повинні знаходитися.
Перевірте як все працює.
Спробуйте! Це не складно, але ефектно.