Используйте условное форматирование, чтобы изменить цвет ячеек или шрифта для данных, которые вы хотите выделить. Быстро применяйте условное форматирование с помощью встроенных правил форматирования Excel. Или настройте форматирование, добавив формулу в правило условного форматирования.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Microsoft 365 и Excel для Mac.
Затенение строк и столбцов в Excel
Преимущество использования формулы для добавления затенения строк заключается в том, что затенение является динамическим, т. е. изменяется при изменении количества строк. Если строки вставляются или удаляются, затенение строк корректируется для сохранения шаблона.
Чередование рядов - не единственный вариант. Немного изменив правило условного форматирования, формула затеняет любой шаблон строк. Он также затеняет столбцы вместо строк.
Затенение строк рабочего листа в Excel
Первым шагом является выделение диапазона ячеек, которые необходимо заштриховать, поскольку формула влияет только на эти выбранные ячейки. В инструкциях по затенению строк с условным форматированием используется формула:
Чтобы применить условное форматирование с помощью формулы:
-
Откройте рабочий лист Excel. Чтобы следовать этому руководству, используйте пустой рабочий лист.
-
Выделить диапазон ячеек на листе.
Image - Выбрать Главная.
- Выбрать Условное форматирование.
- Выберите Новое правило, чтобы открыть диалоговое окно «Новое правило форматирования».
- Выберите Используйте формулу для определения форматируемых ячеек.
-
В поле Формат значений, где эта формула является истинным текстовым полем, введите формулу =MOD(ROW(), 2)=0.
Image - Выберите Формат, чтобы открыть диалоговое окно «Формат ячеек». За исключением Mac, где вы выбираете Format с.
-
Выберите вкладку Заполнить и выберите цвет для чередующихся строк. Когда закончите, выберите OK, чтобы вернуться в диалоговое окно «Новое правило форматирования».
Image - Выберите OK, чтобы закрыть диалоговое окно «Новое правило форматирования» и вернуться на рабочий лист.
-
К рабочему листу применяется правило условного форматирования, содержащее формулу.
Image - Альтернативные строки в выбранном диапазоне затеняются выбранным цветом заливки фона.
Интерпретация формулы MOD
Разработанный паттерн зависит от функции MOD в формуле. MOD делит номер строки (определяемый функцией ROW) на второе число в квадратных скобках (2) и возвращает остаток от модуля.
На этом этапе условное форматирование берет верх и сравнивает модуль с числом после знака равенства. Если есть совпадение (когда условие TRUE), строка затеняется. Если числа по обе стороны от знака равенства не совпадают, условие имеет значение ЛОЖЬ, и для этой строки не происходит затенения.
Условие=0 в формуле определяет, что первая строка в диапазоне не закрашена. Это сделано потому, что эта строка часто содержит заголовки, имеющие собственное форматирование.
Затенение столбцов вместо строк
Если вы хотите затенить чередующиеся столбцы, измените формулу, используемую для затенения чередующихся строк. Используйте в формуле функцию СТОЛБЦ вместо функции СТРОКА. Формула для затенения столбцов:
И результат выглядит так:
Изменить шаблон затенения
Чтобы изменить рисунок затенения, измените одно из двух чисел в формуле.
- Чтобы начать штриховку строки с первой строки вместо второй, в конце формулы измените=0 на =1.
- Чтобы закрасить каждую третью или четвертую строку вместо чередующихся, измените 2 в формуле на 3 или 4.
Число в квадратных скобках называется делителем, так как это число выполняет деление в функции ОСТАТ. В Excel также нельзя делить на ноль. Если вы введете 0 в скобках вместо 2, в диапазоне не появится затенение.
Кроме того, чтобы изменить шаблон, замените условный оператор или оператор сравнения (=), используемый в формуле, на знак «меньше» (<). Например, заменяя=0 на <2 (меньше 2), две строки вместе заштриховываются. Измените=0 на <3, и затенение будет выполнено группами по три ряда.
Единственное предостережение при использовании оператора «меньше» - убедиться, что число в скобках больше, чем число в конце формулы. В противном случае каждая строка в диапазоне будет затенена.
