Умовне форматування у зведених таблицях Excel - Microsoft Excel для початківців
З виходом Excel 2007 компанія Microsoft додала деякі додаткові можливості умовного форматування, такі як гістограми та набори значків, які дозволяють наочно показати відносні величини в цих осередках.
Microsoft змінила порядок застосування умовного форматування до зведених таблиць. Тепер у Вас з'явилося більше можливостей та більшої гнучкості при використанні умовних форматів. У цій статті я покажу Вам, як застосовувати умовне форматування до зведених таблиць і як осідлати всю потужність нових можливостей.
Як працює умовне форматування
В Excel 2007 і 2010, коли умовне форматування застосовується до зведеної таблиці, воно застосовується переважно до структури зведеної таблиці, а не до самих осередків. Тому, коли Ви працюєте зі зведеною таблицею, припустимо, пересуваєте поля з місця на місце або відображаєте дані різними способами, форматування оновлюється за Вашими діями. Все це в поєднанні з новими форматами робить умовне форматування дуже зручним інструментом для використання разом зі зведеними таблицями.
Як застосувати умовне форматування до зведеної таблиці
Ми розглянемо умовне форматування з прикладу зведеної таблиці, у якій представлений графік прийому 4-х ветеринарів протягом року. У таблиці відображається кількість клієнтів з розбивкою по кварталах та за місцем прийому (ферма чи хірургія).
Щоб зробити дані більш наочними, вибираю значенняFarm (Ферма) іSurgery (Хірургія) першого ветеринара з прізвищемBrowning, тобто. осередки відB6 доE7. Виділивши цей діапазон, я заходжу на вкладкуHome (Головна), натискаюConditional Formatting >Data Bars (Умовне форматування > Гістограми) і вибираю, який використовувати колір. Ці дії форматують обраний діапазон таким чином, що в кожному осередку з'являється гістограма, яка показує відносну кількість клієнтів у кожному кварталі та для кожного місця прийому.
На наступному малюнку ми застосовуємо формат Data Bars (Гістограма) до першого діапазону даних:
Щоб застосувати це форматування до аналогічних даних для інших ветеринарів, потрібно виділити раніше відформатований діапазон, клацнути іконкуFormatting Options (Налаштування форматування), яка з'являється в правому нижньому куті діапазону, і вибрати третій із запропонованих варіантів (див. малюнок нижче).
Таким чином, правило буде застосовано до аналогічних даних решти всіх ветеринарів у зведеній таблиці, без необхідності застосовувати це правило до кожного діапазону окремо. Ці ж опції Ви зможете знайти, якщо вирішите створити нове правило форматування у діалоговому вікніNew Formatting Rule (Створення правила форматування).
На цьому малюнку показано, як застосувати однакове умовне форматування всіх даних одного типу в нашій зведеній таблиці:
Тепер завжди, коли в процесі роботи зі зведеною таблицею Ви приховуватимете або відображатимете дані, гістограми в комірках будуть змінюватися, щоб відобразити відносну величину значення в кожній видимій комірці порівняно з усіма іншими видимими комірками того ж формату.
Гістограми змінюються, коли змінюються дані у зведеній таблиці, довжина гістограми залежить від даних у всіх видимих осередках:
Ми можемо піти ще далі та порівняти підсумкові значення, використовуючи іншу умову для форматування. В даному випадку я хочу порівняти підсумкикожному ветеринару з підсумками інших ветеринарів, тому вибираю комірки відB5 доE5 - загальна кількість клієнтів (по кварталах) ветеринараBrowning. Створивши в цьому діапазоні гістограми іншого кольору, я можу порівнювати підсумкові дані про клієнтів, яких оглядав ветеринар Browning за ці чотири квартали.
Як і минулого разу з'являється іконкаFormatting Options (Параметри форматування), за допомогою якої ми зможемо застосувати це умовне форматування до підсумкових даних по клієнтам кожного ветеринара.
Тут Ви бачите гістограми іншого кольору в осередках з підсумковими значеннями, які можна порівняти один з одним:
Інші параметри
У деяких випадках має сенс розділити умовне форматування, як було зроблено в попередньому прикладі, застосовуючи його тільки до осередків, що містять дані одного рівня, щоб розділити підсумки та загальні підсумки (Grand Total). Але не завжди має сенс це робити.
Дані в наступній зведеній таблиці показують середню кількість клієнтів, а не сумарну кількість клієнтів, тому Ви можете застосувати однакове умовне форматування до всіх осередків таблиці.
Для цього виділимо діапазонB6:E6, перейдемоConditional Formatting >Data Bars (Умовне форматування > Гістограми) і виберемо зелений колір для гістограм. Далі у параметрах форматування виберемо другий варіант (див. рисунок нижче). Тепер умовне форматування охоплює як підсумкові, так і загальні підсумкові (Grand Total) значення, які, як і всі дані в зведеній таблиці, є середніми значеннями. Тому не буде порушенням порівняти їх однаково.
Тут усі осередки містять середні значення, тому допустимо застосувати одне правило умовногоформатування:
Переміщуємо дані
Повернемося до нашої вихідної зведеної таблиці та почнемо переміщувати дані. Зробивши це, Ви помітите, що форматування зберігається на потрібних місцях. Ми пересунули полеOffice (Місце прийому) в областьReport Filter (Фільтри) і перенесли полеQuarter (Квартал) в областьRow Labels (Рядки), і при цьому всі фіолетові гістограми залишилися на своїх місцях.
Навіть якщо структура таблиці змінена та поля пересунуті, умовне форматування зберігає своє положення:
Правила форматування з обмеженнями
Звичайно, бувають ситуації, коли не потрібно застосовувати умовне форматування до всіх діапазонів, а натомість потрібно порівняти дані у вужчому інтервалі. У нашому прикладі ми хочемо побачити, в якому кварталі кожен з наших ветеринарів показав найкращі результати, незалежно від місця прийому.
Ми створимо окреме правило умовного форматування для квартальних підсумків кожного ветеринара, тобто нам потрібно виділити та застосувати форматування до осередківD7:D10 (потім D12:D15, потім D17:D19 тощо). Потім використовуємо правилоConditional Formatting >Top 10 Items (Умовне форматування > Перші 10 елементів), встановлюємо умову для1 комірки, формат залишаємо запропонований за замовчуванням. Ви можете скопіювати це правило в сусідній діапазон, або створити для кожного індивідуально.
Щоб скопіювати форматування, виділіть один або кілька комірок із потрібним форматом і натиснітьCopy (Копіювати). Потім виділіть діапазон, який потрібно скопіювати форматування, і на вкладціHome (Головна) виберітьPaste Special >Formats (Спеціальна вставка > Формати).
В деякихвипадках може знадобитися порівняти дані всередині невеликої області, а не застосовувати правило умовного форматування на всю групу несуміжних діапазонів:
Умовне форматування в Excel у поєднанні з потужністю зведених таблиць дозволяє дуже тонко налаштувати форматування та точно визначити, які дані потрібно порівнювати. Ви можете порівняти схоже зі схожим як по всій зведеній таблиці, так і в рамках одного поля, яке Вас цікавить. Знання можливих варіантів та вміння користуватися ними допоможе Вам наочніше порівняти значення та отримати бажаний результат.