Заливка через строку excel

Заливка через строку excel

В Excel имеются так называемые "умные таблицы" в которых можно установить сделать чередующуюся заливку всего лишь выбрав соответствующую опцию. Однако применение таких таблиц не всегда возможно. В таких случаях можно вручную заливать строки/столбцы, но лучше воспользоваться условным форматированием.

Создание чередующейся заливки

Чтобы создать чередующуюся заливку строк как на рисунке выше необходимо:

  1. Выбрать диапазон с таблицей
  2. На вкладке Главная выбрать Условное форматирование ->Создать правило
  3. Откроется диалоговое окно Создание правила форматирования. Выберите тип правила Использовать формулу для определения форматируемых ячеек.
  4. Введите формулу =ОСТАТ(СТРОКА();2)=0 в поле Форматировать значения, для которых следующая формула является истинной:
  5. Нажмите кнопку Формат. и выберите нужный цвет заливки. После нажмите ОК, чтобы закрыть диалоговое окно Формат ячеек.
  6. Еще раз нажмите ОК, чтобы закрыть диалоговое окно Создание правила форматирования.

Как работает формула

Немного о формуле, которую мы применили. Функция СТРОКА возвращает номер строки, а функция ОСТАТ — остаток от деления (в нашем случае на 2). Таким образом, формула =ОСТАТ(СТРОКА();2)=0 возвращает ИСТИНА для каждой четной строки.

Чередующиеся столбцы

Аналогично можно заливать и столбцы. Для этого необходимо изменить в формуле функцию СТРОКА на СТОЛБЕЦ. Т.е. должно получиться следующее: =ОСТАТ(СТОЛБЕЦ();2)=0.

Примеры чередующихся заливок

Заливка через заданное количество строк

Не сложно догадаться, что если необходимо заливать строки не через одну, а например каждую 3, 5, 10, то нужно в нашей формуле менять делить =ОСТАТ(СТРОКА();10)=0 .

Заливка со сдвигом

Если необходимо "сдвинуть" заливку, например, заливать нечетные строки, то необходимо применить следующую формулу =ОСТАТ(СТРОКА()+1;2)=0 .

Заливка в шахматном порядке

Еще один вариант чередующей заливки — заливка в шахматном порядке. В этом случае необходимо заливать ячейки на пересечении одинаковых строк и столбцов. Для этого используем следующую формулу: = ОСТАТ(СТОЛБЕЦ();2)=ОСТАТ(СТРОКА();2) . Получим такую картинку:

Полосатая заливка "зеброй" — весьма популярный и удобный для просмотра вид оформления таблиц:

Читайте также:  Как в инстаграме поделиться фото вконтакте

В Excel есть несколько способов быстрого создания такого дизайна:

Способ 1. Форматировать как таблицу

Это способ подойдет только тем, у кого последние версии Excel — 2007 или новее. Выделите ваш диапазон с данными и нажмите кнопку Форматировать как таблицу (Format as Table) на вкладке Главная (Home) :

Откровенно говоря, этот способ гораздо глубже, чем кажется на первый взгляд, ибо здесь форматирование — только побочный эффект. О настоящих последствиях превращения диапазона в Таблицу можно почитать здесь.

Способ 2. Автоформат

В Excel 2003 и старше можно использовать встроенные шаблоны полосатой заливки. Для этого выделить все ячейки таблицы и выберите в меню Формат — Автоформат (Format — AutoFormat) . Там есть несколько готовых форматов с похожими полосками.

Способ 3. Универсальный. Условное форматирование.

Этим способом можно воспользоваться в любой версии Excel. Он основан на использовании функции условного форматирования для заливки четных и нечетных строк разным цветом.

Выделите ячейки таблицы (кроме "шапки"), затем:

  • в Excel 2003 и старше — откройте меню Формат — Условное форматирование(Format — Conditional Formatting) , выберите в раскрывающемся списке вариант Формула (Formula) вместо Значение (Value)
  • в Excel 2007 и новее — нажмите кнопку Условное форматирование (Conditional Formatting) на вкладке Главная (Home) и выберите Создать правило (New Rule) , затем в открывшемся окне задайте последний тип правила Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)

В появившееся поле впишите следующую формулу:

=ОСТАТ(СТРОКА(A2);2)=0 или в английской версии =MOD(ROW(A2);2)=0

Эта формула берет номер текущей строки (функция СТРОКА, а A2 — первая ячейка выделения), делит его на 2 и проверяет остаток от деления (функция ОСТАТ). Если он равен нулю, т.е. номер строки четный, то происходит форматирование ячейки (не забудьте нажать кнопку Формат и задать цвет шрифта или заливку). Если необходимо залить не каждую 2-ю, а, скажем, каждую 5-ю строку, то просто измените в этой формуле число 2 на число 5.

Читайте также:  Как освободить порты 80 и 443

Вы определенно встречались с таблицами Excel, в которых цвета строк чередуются. Например, у нечетных строк белый цвет, у четных — серый. Это легко сделать при помощи условного форматирования.

Чередующиеся цвета и тени придают таблице профессиональный вид и упрощают чтение данных. Это форматирование можно наложить вручную, однако, как можно представить (или вспомнить из собственного опыта), это очень долгая задача, требующая постоянного обновления по мере добавления и удаления данных из таблицы. Кроме того, она требует бесконечного терпения. К счастью, условное форматирование может сократить объем необходимого терпения и улучшить ваш профессиональный образ.

Мы будем предполагать, что данные занимают диапазон А1:Н100. Выделите этот диапазон ячеек, начиная с ячейки А1, гарантируя, таким образом, что А1 будет активной ячейкой выделения. Теперь выберите команду Главная → Условное форматирование (Home → Conditional Formatting). В раскрывающемся списке, где вы видите параметр Значение (Cell Value Is), выберите вариант Формула (Formula Is).

В поле Формула (Formula) введите следующую формулу (рис. 2.5): =MOD(ROW();2) , в русской версии Excel: =ОСТАТ(СТРОКА();2) .

Рис. 2.5. Диалоговое окно условного форматирования с формулой для форматирования каждой второй строки в диапазоне

Щелкните кнопку Формат (Format) и выберите форматирование, которое будет применяться к каждой второй строке. Щелкните на кнопке ОК, затем еще раз щелкните на кнопке ОК. Указанный формат будет применен к каждой второй строке в диапазоне А1:Н100. Кроме того, вы должны сэкономить еще немного терпения до конца рабочего дня.

Если форматирование нужно применить к столбцам, а не к строкам, то используйте эту формулу: =MOD(COLUMN();2) , в русском варианте Excel: =ОСТАТ(СТОЛБЕЦ();2) .

Хотя в этом методе указанное форматирование легко и просто применяется к каждой второй строке или столбцу, оно не является динамическим. Строки, не содержащие данных, все также будут содержать форматирование. Это выглядит немного неаккуратно и затрудняет чтение таблицы. Чтобы сделать форматирование каждой второй строки или столбца динамическим, нужно еще немного поработать с формулами.

Читайте также:  Как в экселе переместить строку вверх

Снова выделите диапазон А1:Н100, следя за тем, чтобы А1 была активной ячейкой. Выберите команду Главная → Условное форматирование (Home → Conditional Formatting) и в раскрывающемся списке Значение (Cell Value Is) выберите Формула (Formula Is). В поле Формула (Formula) введите следующую формулу: =AND(MOD(ROW();2);COUNTA($A1:$H1)) , в русской версии Excel: =И(ОСТАТ(СТРОКА();2);СЧЁТЗ($А1:$H1)) .

Это работает, так как формула, которую вы указываете для условного форматирования, возвращает результат ИСТИНА (TRUE) или ЛОЖЬ (FALSE). На языке формул Excel 0 имеет булевское значение ЛОЖЬ (FALSE), а число, большее нуля, — значение ИСТИНА (TRUE). Формула =MOD(ROW();2) вернет либо О (ЛОЖЬ), либо число, большее 0. Функция СТРОКА (ROW) — это переменная функция, которая всегда возвращает номер строки для ячейки, в которой находится. Функция ОСТАТ (MOD) возвращает остаток от деления одного числа на другое. В случае наших формул мы делим номер строки на 2, поэтому для всех четных строк будет возвращаться результат О, а для всех нечетных — число, большее 0.

Когда вы помещаете функцию СТРОКА (ROW) и функцию СЧЁТЗ (COUNTA) в функцию И (AND), то для того чтобы функция И (AND) вернула значение ИСТИНА (TRUE), и функция ОСТАТ (MOD), и функция СЧЁТЗ (COUNTA) должны вернуть значение ИСТИНА (TRUE). Функция СЧЁТЗ (COUNTA) подсчитывает все непустые ячейки.

Ссылка на основную публикацию
Если сразу после тренировки появляется ощущение подавленности
Бывает ли у вас после тренировки лёгкое неприятное послевкусие, как будто бы вы что-то не закончили? Или, наоборот, такое впечатление,...
Добавить аккаунт в инстаграм что это
Добавление нескольких аккаунтов и переключение между ними. Теперь вы можете добавить до 5 аккаунтов Instagram и быстро переключаться между ними...
Добавить кавычки в excel
Использование текста в формулах ​Смотрите также​​Guest​Например, нужно в​Нужно в D63​KuklP​:​ на кавычки ругаются.​: =ЕСЛИ(ПОДСТАВИТЬ(A2;"""";)="ООО Ромашка". ​ включить в список​ на...
Если телефон сел что говорит оператор
Что значит «абонент временно недоступен» Что значит «данный вид связи недоступен для абонента» Почему связь обрывается Сообщение о «временной недоступности»...
Adblock detector