Застосування умовного форматування у зведеній таблиці може бути трохи складним.
З огляду на те, що зведені таблиці настільки динамічні і дані в бекенді можуть часто змінюватися, вам потрібно знати правильний спосіб використання умовного форматування у зведеній таблиці в Excel.
Неправильний спосіб застосування умовного форматування до зведеної таблиці
Давайте спочатку розглянемо звичайний спосіб застосування умовного форматування у зведеній таблиці.
Припустимо, у вас є зведена таблиця, як показано нижче:
У наведеному вище наборі даних дата вказана у рядках, а дані про продажі зберігаються у стовпцях.
Ось звичайний спосіб застосування умовного форматування до будь -якого набору даних:
- Виберіть дані (у цьому випадку ми застосовуємо умовне форматування до B5: D14).
- Перейдіть додому -> Умовне форматування -> Правила зверху/знизу -> Вище середнього.
- Вкажіть формат (я використовую “Зелене заповнення з темним зеленим текстом”).
- Натисніть OK.
Буде застосовано умовне форматування, як показано нижче:
Усі точки даних, які перевищують середнє значення всього набору даних, були виділені.
Проблема цього методу полягає в тому, що він застосував умовний формат до фіксованого діапазону клітинок (B5: D14). Якщо ви додасте дані в бекенд і оновите цю зведену таблицю, умовне форматування не буде застосовано до неї.
Наприклад, я повертаюся до набору даних і додаю дані ще для однієї дати (11 січня 2015 р.). Ось що я отримую, коли оновлюю зведену таблицю.
Як ви можете бачити на зображенні вище, дані за 11 січня 2015 року не виділяються (хоча це слід робити, оскільки значення для Магазину 1 та Магазину 3 вище середнього).
Причина, як я вже згадував вище, полягає в тому, що умовне форматування було застосовано до фіксованого діапазону (B5: D14), і воно не поширюється на нові дані у зведеній таблиці.
Правильний спосіб застосування умовного форматування до зведеної таблиці
Ось два способи переконатися, що умовне форматування працює, навіть якщо в бекенді є нові дані.
Спосіб 1 - Використання піктограми форматування зведеної таблиці
У цьому методі використовується значок Параметри форматування зведеної таблиці, який з’являється, щойно ви застосуєте умовне форматування до зведеної таблиці.
Нижче наведено кроки для цього.
- Виберіть дані, до яких потрібно застосувати умовне форматування.
- Перейдіть додому -> Умовне форматування -> Правила зверху/знизу -> Вище середнього.
- Вкажіть формат (я використовую “Зелена заливка з зеленим текстом Dard”).
- Натисніть ОК.
- Коли ви виконуєте наведені вище кроки, він застосовує умовне форматування до набору даних. У нижньому правому куті набору даних ви побачите значок Параметри форматування:
- Натисніть на значок. У спадному меню буде показано три варіанти:
- Вибрані комірки (які будуть вибрані за замовчуванням).
- Усі клітинки, що показують значення "Суми доходів".
- Усі клітинки, що показують значення "Суми доходів" для "Дата" та "Зберігання".
- Виберіть третій варіант - усі клітинки, які показують значення “Суми доходів” для “Дата” та “Зберігання”.
Тепер, коли ви додаєте будь -які дані у внутрішній частині та оновлюєте зведену таблицю, додаткові дані автоматично охоплюються умовним форматуванням.
Розуміння трьох варіантів:
- Вибрані клітини: Це параметр за замовчуванням, коли умовне форматування застосовується лише до вибраних комірок.
- Усі клітинки, що показують значення "Суми доходів": У цьому варіанті він враховує всі клітинки, які відображають суму значень доходів (або будь -які дані, які є у розділі значень зведеної таблиці).
- Проблема з цим варіантом полягає в тому, що він також охоплюватиме загальні значення та застосовуватиме до нього умовне форматування.
- Усі клітинки, що показують значення "Суми доходів" для "Дата" та "Зберігання": Це найкращий варіант в даному випадку. Він застосовує умовне форматування до всіх значень (за винятком загальних підсумків) для поєднання дати та зберігання. Навіть якщо ви додасте більше даних у внутрішній частині, ця опція подбає про це.
Примітка:
- Значок Параметри форматування стає видимим відразу після застосування умовного форматування до набору даних. Якщо зникає, якщо ви робите щось інше (редагуєте клітинку або змінюєте шрифт/вирівнювання тощо).
- Умовне форматування зникає, якщо ви змінюєте поля рядка/стовпця. Наприклад, якщо видалити поле «Дата» та застосувати його знову, умовне форматування буде втрачено.
Спосіб 2 - Використання диспетчера правил умовного форматування
Крім використання піктограми Параметри форматування, ви також можете скористатися діалоговим вікном «Менеджер правил умовного форматування» для застосування умовного форматування у зведеній таблиці.
Цей метод корисний, коли ви вже застосували умовне форматування і хочете змінити правила.
Ось як це зробити:
- Виберіть дані, до яких потрібно застосувати умовне форматування.
- Перейдіть додому -> Умовне форматування -> Правила зверху/знизу -> Вище середнього.
- Вкажіть формат (я використовую “Зелене заповнення з темним зеленим текстом”).
- Натисніть ОК. Це застосує умовне форматування до вибраних комірок.
- Перейдіть додому -> Умовне форматування -> Керувати правилами.
- У Менеджері правил умовного форматування виберіть правило, яке потрібно змінити, і натисніть кнопку Редагувати правило.
- У діалоговому вікні Редагувати правило ви побачите ті самі три варіанти:
- Вибрані клітини.
- Усі клітинки, що показують значення "Суми доходів".
- Усі клітинки, що показують значення "Суми доходів" для "Дата" та "Зберігання".
- Виберіть третій варіант і натисніть OK.
Це застосує умовне форматування до всіх клітинок для полів "Дата" та "Зберегти". Навіть якщо ви зміните дані бекенда (додасте більше даних про магазин або дату), умовне форматування буде функціональним.
Примітка: Умовне форматування зникає, якщо ви змінюєте поля рядка/стовпця. Наприклад, якщо видалити поле «Дата» та застосувати його знову, умовне форматування буде втрачено.
