У більшості випадків умовне форматування використовується для зміни кольорів стільників або шрифтів у відповідь на дані, введені в комірку, наприклад, прострочену дату або надто високі витрати на бюджет, і, як правило, це робиться за допомогою існуючих умов Excel. Однак також можна створити власні правила умовного форматування, використовуючи формули Excel, щоб перевірити умови, зазначені користувачем.
Одна така формула, яка поєднує в собіMOD іROW Функції можуть бути використані для автоматичного відтворення чергових рядків даних, які можуть зробити дані зчитування великими робочими листами набагато більш керованими.
Затінення рядків / стовпчиків у форматі Excel
Ще однією перевагою використання формули для додавання рядків затінення є те, що затінення є динамічним, тобто він змінюється, коли кількість рядків змінюється. Якщо рядки вставляються або видаляються, затухання рядків налаштовується самі, щоб зберегти малюнок.
Альтернативні рядки не є єдиним варіантом за допомогою цієї формули. Змінюючи його трохи, як це обговорювалося нижче, формула може відтінкувати будь-який шаблон рядків; його можна навіть використовувати для відтінку стовпців замість рядків, якщо ви цього оберете.
Затінення робочих рядків у Excel
Перший крок полягає в тому, щоб виділити діапазон клітин, які слід затінювати, оскільки формула впливає тільки на ці обрані комірки. Інструкції до відтінку рядків з умовним форматуванням використовуватимуть таку формулу:
= MOD (ROW (), 2) = 0
- Відкрийте Excel Робочий аркуш - порожній робочий аркуш буде працювати для цього навчального посібника.
- Виділіть діапазон клітин у робочому аркуші.
- Натисніть наБудинок вкладка з стрічки.
- Натисніть на Умовне форматування значок
- Виберіть Нове правиломожливість відкрити Нове правило форматування коробка.
- В Стиль поле, виберіть Класичний варіант
- Натисніть на Форматуйте лише верхнє або нижнє значення поле, щоб потім змінити значення до Використовуйте формулу, щоб визначити whch cells для форматування.
- Введіть формулу, наведену вище, у вікно нижчеВикористовуйте формулу, щоб визначити, які комірки потрібно форматувати варіант
- Натисніть кнопку випадаюче вікно поруч з Формат з щоб вибрати колірну схему для чергування рядків.
- Натиснітьв порядку щоб закрити діалогове вікно та повернутися до робочого листа.
- Альтернативні рядки у вибраному діапазоні тепер повинні бути затінені кольором вибраного фону.
Інтерпретація формули MOD
Шаблон, який ми спроектували, залежить від MOD функція у формулі. Що MOD робить ділить номер рядка (визначається ROW функція) другим числом у дужках (2) і повертає залишок або модуль, як його іноді називають.
= MOD (ROW (), 2) = 0
На цьому етапі умовне форматування перебирає та порівнює модуль з числом після знаку рівності. Якщо є відповідність (або більш правильно, якщо умова є ІСТИНА), рядок затінений, якщо номери з обох боків знаку рівності не співпадають, то умова ПОМИЛКОВИЙ і не відбувається затінення для цього рядка.
Стан=0 у формулі визначає, що перший рядок у діапазоні не затьмарений, що відбувається, оскільки цей рядок часто містить заголовки, що мають власне форматування.
Вибір тіні колон, а не рядок
Формула, яка використовується для відтінення альтернативних рядків, може бути модифікована для того, щоб дозволити також затінення стовпців. Необхідною зміною є використання COLUMN функція замість ROW функція у формулі. При цьому формула буде виглядати так:
= MOD (COLUMN (), 2) = 0
Зміна шаблону затінення
Зміна шаблону затінення легко зробити, змінюючи один із двох чисел у формулі.
- Для того, щоб рядок затінення починався з першого рядка замість другого, в кінці формули змінити =0 до=1.
- Щоб кожний третій або четвертий рядок затінені замість чергових рядків, змініть значення 2 в формулі до 3 або 4.
Число всередині дужок називається дільником, оскільки це число, яке розділяє в MOD функція Якщо ви пам'ятаєте ще в математичному класі, розділеному нулем, це не дозволено, і це також не дозволено в Excel. Якщо ви намагаєтеся використати a 0 всередині дужок на місці 2, ви не отримаєте будь-який затінення зовсім у діапазоні.
Крім того, щоб дійсно змінити шаблон, змініть умовний оператор або порівняння (=), що використовується в формулі менш знаку (<) Змінюючи =0 до <2 (менше 2), наприклад, два рядки разом можуть бути затіненими. Зроби це <3, і затінення буде виконано групами з трьох рядків.
Єдиний застереження щодо використання менше, ніж оператора, полягає в тому, щоб переконатися, що число усередині дужок більше, ніж число наприкінці формули. Якщо ні, кожен рядок у діапазоні буде затіненим.
