В большинстве случаев условное форматирование используется для изменения цветов ячеек или шрифтов в ответ на данные, введенные в ячейку, такие как просроченная дата или слишком высокие расходы бюджета, и обычно это делается с использованием нынешних условий Excel. Тем не менее, также можно создавать собственные правила условного форматирования с использованием формул Excel для проверки условий, заданных пользователем.
Одна такая формула, которая объединяетMOD а такжеСТРОКА функции могут использоваться для автоматического затенения чередующихся строк данных, которые могут делать данные чтения в больших листах, гораздо более управляемыми.
Затенение строк / столбцов в Excel
Еще одно преимущество использования формулы для добавления затенения строк заключается в том, что затенение является динамическим, то есть оно изменяется, если число строк изменяется. Если строки вставляются или удаляются, то закрашивание строк настраивается для поддержания шаблона.
Альтернативные строки - это не единственная опция с этой формулой. Изменяя его немного, как обсуждается ниже, формула может затенять любой образец строк; его можно даже использовать для затенения столбцов вместо строк, если вы так решите.
Строки таблицы затенения в Excel
Первый шаг - выделить диапазон ячеек, которые будут заштрихованы, поскольку эта формула влияет только на выбранные ячейки. Инструкции для оттенков строк с условным форматированием будут использовать следующую формулу:
= MOD (СТРОКА (), 2) = 0
- Откройте превосходить рабочий лист - для этого урока будет работать пустой лист.
- основной момент диапазон ячеек на листе.
- Нажми наГлавная табуляция ленты.
- Нажми на Условное форматирование значок.
- Выбрать Новое правиловозможность открыть Новое правило форматирования коробка.
- в Стиль , выберите классический вариант.
- Нажми на Отформатируйте только верхние или нижние ранжированные значения , чтобы изменить значение на Используйте формулу для определения whch-ячеек для форматирования.
- Введите приведенную выше формулу в поле нижеИспользуйте формулу, чтобы определить, какие ячейки следует форматировать. вариант.
- Нажмите выпадающий список рядом с Формат с выбрать цветовую схему для чередующихся строк.
- НажмитеХорошо для закрытия диалогового окна и возврата на рабочий лист.
- Альтернативные строки в выбранном диапазоне теперь должны быть заштрихованы с выбранным цветом заливки фона.
Интерпретация формулы MOD
Образец, который мы создали, зависит от MOD функции в формуле. Какие MOD do делит номер строки (определяется СТРОКА функция) вторым номером внутри скобок (2) и возвращает остаток или модуль, как его иногда называют.
= MOD (СТРОКА (), 2) = 0
На этом этапе условное форматирование берет верх и сравнивает модуль с номером после знака равенства. Если есть совпадение (или, вернее, если условие ПРАВДА), строка затенена, если числа с обеих сторон знака равенства не совпадают, условие равно ЛОЖНЫЙ и для этой строки не происходит затенения.
Условие=0 в формуле определяется, что первая строка в диапазоне не затенена, что делается потому, что эта строка часто содержит заголовки, которые имеют собственное форматирование.
Выбор теневых столбцов вместо строк
Формула, используемая для затенения чередующихся строк, может быть изменена так, чтобы можно было также закрасить столбцы. Необходимое изменение - использовать КОЛОНКА вместо СТРОКА функции в формуле. При этом формула будет выглядеть так:
= MOD (колонка (), 2) = 0
Изменение шаблона затенения
Изменение шаблона затенения легко сделать, изменив одно из двух чисел в формуле.
- Чтобы затенение строк начиналось с первой строки, а не второй, в конце формулы, измените =0 в=1.
- Чтобы каждая третья или четвертая строка затенена вместо альтернативных строк, измените 2 в формуле 3 или же 4.
Число внутри скобок называется делителем, так как оно является числом, которое делит в MOD функция. Если вы помните, что в классе математики деление на ноль не разрешалось, и это тоже не допускается в Excel. Если вы попытаетесь использовать 0 внутри скобок вместо 2, вы не получите никакого затенения вообще в диапазоне.
Кроме того, чтобы действительно изменить шаблон, измените условный или оператор сравнения (=), используемых в формуле, до знака меньше (<). Путем изменения =0 в <2 (менее 2), например, два ряда вместе могут быть заштрихованы. Сделайте это <3, а затемнение будет выполняться группами из трех строк.
Единственное предостережение для использования оператора меньше, чем для оператора, чтобы убедиться, что число внутри скобок больше числа в конце формулы. Если нет, каждая строка в диапазоне будет затенена.
