Как протянуть условное форматирование в excel на весь столбец

Копирование условного форматирования

Есть несколько методов как скопировать условия для формата ячеек. Рассмотрим их, начиная с самых простых и заканчивая более «сложными».

Копировать формат по образцу

Условное форматирование это всего лишь обычный формат, поэтому может быть скопирован так же.

На вкладке «Главная» в самом начале ленты расположена группа «Буфер обмена». В ней Вы найдете пиктограмму кисти – формат по образцу (в разных версия выглядит по-разному, но называется одинаково). Клик по ней копирует не только формат выделенных ячеек, но и условия для него, если таковые имеются. Следующим действием необходимо выделить те ячейки, в которые данное форматирование необходимо перенести.

Имейте в виду, что описанный способ перенесет абсолютно все форматы, которые будут иметься в его источнике.

Копирование через вставку

Другим вариантом дублировать формат является специальный способ вставки.

Скопируете ячейки с нужным форматов привычным для Вас способом. Выделите диапазон, на которые требуется перенести форматирование, а затем в группе буфера обмена вкладки «Главная» щелкните по стрелке, раскрывающей меню «Вставка». В нем выберите пункт «Форматирование (Ф)»:

Также можно выбрать пункт «Специальная вставка…». Тогда приложение Excel отобразит окно, где потребуется установить переключатель на радиокнопке «форматы», после нажать «OK».

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

Управление правилами условного форматирования

Перенести условия формата можно через диспетчер правил условного форматирования.

Пройдите по следующему пути: Вкладка «Главная» -> Группа «Стили» -> «Условное форматирование» -> «Управление правилами…». Последний пункт отобразит окно:

Из раскрывающего списка «Показать правила условного форматирования» выберите пункт «Этот лист». Тогда Вы сможете увидеть все правила, которые действуют на текущем листе.

В столбце списка правил «Применяется к» указаны диапазоны, на которые распространяется каждое правило. Добавьте в это поле нужные адреса ячеек, чтобы применить к ним условное форматирование.

Данный способ менее удобен, чем предыдущие два, но его отличие в том, что он позволяет распространять только нужные правила. Это полезно тогда, когда к копируемым ячейкам применяется несколько условий одновременно.

• Условное форматирование строки

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Как применить в условном форматировании несколько условий

Пользоваться условным форматированием очень просто! Вкратце можно описать этапами:

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

Для начала стоит ознакомиться с основными принципами использования условного форматирования, чтобы потом уметь быстро определять критерии для поиска и экспонирования определенных значений в ячейках (число, текст, дата и т.д.).

Как применить условное форматирование к столбцу

Для начала вспомним как пользоваться условным форматированием. Возьмем простой пример с одним условием. Допустим, мы имеем отчет о продажах по каждому торговому представителю фирмы за месяц:

В первую очередь отобразим цветом тех, кто выполнил месячный план > 25000. Для этого:

1. Выделим диапазон ячеек который содержит суммы продаж (C2:C10).
2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Больше».
3. В появившемся окне введите в поле значение 25000 и выберите из выпадающего списка «Зеленая заливка и темно-зеленый текст».
4. Нажмите ОК.

Примечание. Если нужно использовать данное условное форматирование с такими же критериями для других ячеек, то необязательно создавать его заново. Достаточно скопировать и вставить в необходимый диапазон ячеек с помощью «Специальной вставки» отметив опцию «форматы».

Как настроить пользовательский формат

При желании можно настроить свой формат отображения выделенных цветом значений. Для этого:

1. Перейдите курсором на любую ячейку диапазона с условным форматированием.
2. Выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
3. В появившемся окне нажмите на кнопку «Изменить правило».
4. В следующем окне нажмите на кнопку «Формат».
5. Теперь перейдите на закладку «Шрифт» и выберите в опции «Цвет:» синий для шрифта вместо зеленого.
6. На всех трех окнах нажмите «ОК».

В результате мы изменили формат отображения важных для нас данных на свой вкус.

Условное форматирование по нескольким условиям

Усложним задачу и добавим второе условие. Допустим, другим цветом нам нужно выделить плохие показатели, а именно все суммы меньше 21000. Чтобы добавить второе условие нет необходимости удалять первое и делать все сначала. Делаем так:

1. Снова выделите диапазон ячеек C2:C10 (несмотря на то, что он уже отформатирован в соответствии с условиями).
2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Меньше».
3. В появившемся окне введите в поле значение 21000 и выберите из выпадающего списка выберите «Светло-красная заливка и темно-красный текст».
4. Нажмите ОК.

Теперь у нас таблица экспонирует два типа показателей благодаря использованию двух условий. Чтобы посмотреть и при необходимости изменить эти условия, снова выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».

Несколько правил условного форматирования в одном столбце

Еще раз усложним задачу, добавив третье условие для экспонирования важных данных в таблице. Теперь выделим жирным шрифтом те суммы, которые находятся в пределах от 21800 и до 22000.

1. Снова выделите диапазон ячеек C2:C10.
2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Между».
3. В появившемся окне введите в первое поле значение 21800, во второе поле 22000. В выпадающем списке укажите на опцию «Пользовательский формат».
4. В окне «Формат ячеек» перейдите на закладку «Шрифт» и в разделе «Начертание» укажите «полужирный». После чего нажмите ОК на всех окнах.

Обратите внимание, что каждое новое условие не исключает предыдущие и распространяется на разный диапазон чисел.

Примечание. Критериями условного форматирования не обязательно должны быть только числа. Успешно можно его применять на текстовые значения ячеек Excel. Например, проверка с помощью оператора равенства, соответствует ли данный текст тексту, указанному в критериях. Или более сложные критерии с использованием формул Excel.

Ссылки в условиях условного форматирования

Во всех выше описанных примерах мы вносили критерии непосредственно в диалоговое окно правил условного форматирования. В некоторых случаях рационально все критерии предварительно записать в отдельные ячейки, а потом в окне присваивания условий соответственно ссылаться на них. Например:

1. Введите в ячейку D1 число 25000, а в D2 введите 21000. В ячейки D3 и E3 введите 21800 и 22000 соответственно.
2. Выделите диапазон ячеек C2:C10 и снова выберите опцию: «Главная»-«Условное форматирование»-«Управление правилами».
3. В окне «Диспетчер правил условного форматирования» откройте каждое из правил и поменяйте значения их критериев на соответствующие ссылки.

Внимание! Все ссылки должны быть абсолютными. Например, критерий со значением 25000 должен теперь содержать ссылку на =$D$1.

Примечание. Необязательно вручную вводить каждую ссылку с клавиатуры. Щелкните левой кнопкой мышки по соответствующему полю (чтобы придать ему фокус курсора клавиатуры), а потом щелкните по соответствующей ячейке. Excel вставит адрес ссылки автоматически.

Обратите внимание, что такой метод значительно эффективней, если критерии должны изменяться. Фактически вы теперь можете управлять ими без открытия диалоговых окон и лишней суеты. К тому же все изменения можно визуально анализировать в режиме реального времени. Это очень удобно!

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как протянуть условное форматирование в excel на весь столбец

Создал формулу для условного форматирования, заключается она в том, чтобы при значении указанной ячейки меньше 11 строка окрашивалась в розовый (как срочная), а при значении меньше 1 строка окрашивалась в серый (как не актуальная). Необходимо это условие протянуть на остальные строки ниже.

Проблема заключается в том, что при протягивании строки с заданным условным форматированием формула, которая содержится в форматировании не протягивается (т.е. не смещается её значение на G12, G13 и т.д.), а просто дублируется. Как заставить Эксель протянуть формулу, содержащуюся в условном форматировании?

Прикрепленные файлы

• правило выделения — копия.jpg (97.74 КБ)
• 1 сроки действия страховок и ТО.xlsx (13.05 КБ)

Изменено: ivan.k — 04.11.2020 14:13:01
Пользователь
Сообщений: 427 Регистрация: 24.01.2020
04.11.2020 14:32:48

В формуле снимите закрепления $G10$ должно стать G10. А там где «применяется как Вместо =$B$10:$G$10 надо указать диапазон например $B$10:$G$200

Пользователь
Сообщений: 3863 Регистрация: 31.10.2014
Excel 365 Бета-канал
04.11.2020 14:42:21

Цитата
DemonAMT написал: должно стать G10

всё же $ G10
Прикрепленные файлы

• 1 сроки действия страховок и ТО.xlsx (14.23 КБ)

Соблюдение правил форума не освобождает от модераторского произвола
Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
04.11.2020 14:47:23
я, обычно, отмечаю весь диапазон и пишу формулу условного форматирования для этого диапазона

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Пользователь
Сообщений: 40 Регистрация: 27.12.2018
04.11.2020 15:12:39

Цитата
DemonAMT написал: В формуле снимите закрепления

так ведь я и не ставил нигде закреплений. моя формула выгладит следующим образом: «G11<1"

Цитата
Ігор Гончаренко написал: я, обычно, отмечаю весь диапазон

А можно пример? а то я понимаю как поставить задачу экселю построчно, а как это сделать для области?
Пользователь
Сообщений: 3863 Регистрация: 31.10.2014
Excel 365 Бета-канал
04.11.2020 15:14:11
ivan.k, чем не устроил ответ из #3? там и приведен пример для области
Изменено: buchlotnik — 04.11.2020 15:14:41
Соблюдение правил форума не освобождает от модераторского произвола
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
04.11.2020 15:14:45

Цитата
ivan.k написал: как это сделать для области?

Выделить диапазон, записать правило для левой верхней ячейки диапазона.
Или:
Записать правило для верхней левой ячейки диапазона, указать диапазон УФ ( Применяется к. )

Естественно, с учетом протягиваний по горизонтали и вертикали (знаки $)

Пользователь
Сообщений: 40 Регистрация: 27.12.2018
06.11.2020 09:58:36

Ребята, большое спасибо всем кто откликнулся! ) Я только сейчас разобрался и понял о чем вы говорите )

Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
06.11.2020 11:26:37

и на примере:
1. перевел Excel в режим R1C1
2. выделил B:G
3. удалил все правила , которые там были написаны
4. написал =И(НЕ(Епусто(RC7));RC7<11)
5. выбрал в форматах заливка цветом
6. скопировал формулу
7. добавил правило
8. вставил формулу, удалил одну из двух единичек в конце
9. выбрал формат — заливка серым
10. поставил для последнего правила птичку остановить вычисления
11. Ок
ВСЕ!
условные форматы применены на ВСЕ строки диапазона B:G , правил — 2 шт.
пишите в ЛЮБОЙ строке, в ячейке G любое число, если оно окажется меньше 11 — сработает одно правило, а если меньше 1 — то другое
удвчи!

Прикрепленные файлы

• 1 сроки действия страховок и ТО.xlsx (13.28 КБ)

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Применение условного форматирования с помощью формулы в Excel для Mac

Условное форматирование позволяет быстро выделить на листе важные сведения. Но иногда встроенных правил форматирования недостаточно. Создав собственную формулу для правила условного форматирования, вы сможете выполнять действия, которые не под силу встроенным правилам.

Предположим, что вы следите за днями рождения пациентов своего стоматологического кабинета, а затем отмечаете тех, кто уже получил от вас поздравительную открытку.

С помощью условного форматирования, которое определяется двумя правилами с формулой, на этом листе отображаются необходимые вам сведения. Правило в столбце A форматирует предстоящие дни рождения, а правило в столбце C форматирует ячейки после ввода символа «Y», обозначающего отправленную поздравительную открытку.

Как создать первое правило

1. Выделите ячейки от A2 до A7 (для этого щелкните и перетащите указатель мыши с ячейки A2 в ячейку A7).
2. На вкладке Главная выберите Условное форматирование >Создать правило.
3. В поле Стиль выберите Классический.
4. Под полем Классический выберите элемент Форматировать только первые или последние значения и измените его на Использовать формулу для определения форматируемых ячеек.
5. В следующем поле введите формулу: =A2>СЕГОДНЯ() Функция СЕГОДНЯ используется в формуле для определения значений дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.
6. В поле Форматировать с помощью выберите пользовательский формат.
7. В диалоговом окне Формат ячеек откройте вкладку Шрифт.
8. В поле Цвет выберите значение Красный. В поле Начертание шрифта выберите Полужирный.
9. Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.

Теперь форматирование применено к столбцу A.

Как создать второе правило

1. Выделите ячейки от C2 до C7.
2. На вкладке Главная выберите Условное форматирование >Создать правило.
3. В поле Стиль выберите Классический.
4. Под полем Классический выберите элемент Форматировать только первые или последние значения и измените его на Использовать формулу для определения форматируемых ячеек.
5. В следующем поле введите формулу: =C2=»Y» Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают Excel, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.
6. В поле Форматировать с помощью выберите пользовательский формат.
7. В верхней части окна откройте вкладку Шрифт.
8. В поле Цвет выберите Белый. В поле Начертание шрифта выберите Полужирный.
9. В верхней части окна откройте вкладку Заливка и для параметра Цвет фона выберите значение Зеленый.
10. Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.

Теперь форматирование применено к столбцу C.

Попробуйте попрактиковаться

В приведенных выше примерах мы использовали простые формулы для условного форматирования. Поэкспериментируйте самостоятельно и попробуйте использовать другие известные вам формулы.

Вот еще один пример для тех, кто хочет узнать больше. В книге создайте таблицу данных со значениями, приведенными ниже. Начните с ячейки A1. Затем выделите ячейки D2:D11 и задайте новое правило условного форматирования с помощью следующей формулы:

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).