Условное форматирование в Excel
Условное форматирование в Excel автоматом изменяет наружный вид ячейки в зависимости от ее содержимого. К примеру, Вы сможете выделить красноватым цветом ячейки, в которых содержаться недопустимые значения. Этот урок мы посвятим условному форматированию – одному из самых увлекательных и нужных средств Excel.
Представьте, что у Вас есть лист Excel, содержащий тыщу строк с данными. Думаю, будет достаточно проблемно посреди всего этого размера инфы рассмотреть закономерности либо нужные данные. Подобно диаграммам и спарклайнам, условное форматирование помогает визуализировать информацию и упростить ее восприятие.
Главные сведения о условном форматировании
Условное форматирование в Excel дозволяет автоматом изменять формат ячеек в зависимости от содержащихся в их значений. Для этого нужно создавать правила условного форматирования. Правило может звучать последующим образом: “Если значение меньше $2000, цвет ячейки – красноватый.” Используя это правило Вы можете стремительно найти ячейки, содержащие значения меньше $2000.
Создание правила условного форматирования
В последующем примере на листе Excel содержатся данные по продажам за крайние 4 месяца. Допустим, мы желаем знать, кто из продавцов делает месячный план продаж, а кто нет. Для выполнения плана нужно реализовать на сумму наиболее $4000 в месяц. Сделаем правило условного форматирования, которое выделит нам все ячейки в таблице со значением выше $4000.
- Выделите ячейки, по которым требуется выполнить проверку. В нашем случае это спектр B2:E9.
- На вкладке Основная нажмите команду Условное форматирование. Покажется выпадающее меню.
- Изберите нужное правило условного форматирования. Мы желаем выделить ячейки, значение которых Больше $4000.
- Покажется диалоговое окно. Введите нужное значение. В нашем случае это 4000.
- Укажите стиль форматирования в раскрывающемся перечне. Мы выберем Зеленоватую заливку и зеленый текст. Потом нажмите OK.
- Условное форматирование будет использовано к выделенным ячейкам. Сейчас без особенного труда можно узреть, кто из продавцов выполнил месячный план в $4000.
Вы сможете использовать сходу несколько правил условного форматирования к одному и тому же спектру ячеек, что дозволяет наиболее гибко и наглядно представить подходящую информацию.
Удаление условного форматирования
- Нажмите команду Условное форматирование. Покажется выпадающее меню.
- Наведите указатель мыши на пункт Удалить правила и изберите, какие правила Вы желаете удалить. В нашем примере мы выберем Удалить правила со всего листа, чтоб удалить все условное форматирование на листе.
- Условное форматирование будет удалено.
Вы сможете избрать пункт Управление правилами, чтоб узреть все правила условного форматирования, сделанные на этом листе либо в выделенном спектре. Диспетчер правил условного форматирования дозволяет отредактировать либо удалить пользовательские правила. Это в особенности комфортно, если сотворено несколько правил на одном листе.
Предустановленные стили условного форматирования
Excel содержит набор предустановленных стилей, которыми можно пользоваться, чтоб стремительно применить условное форматирование к данным. Они сгруппированы в три группы:
- Гистограммы – это горизонтальные полосы, добавляемые в каждую ячейку в виде ступенчатой диаграммы.
- Цветовые шкалы изменяют цвет каждой ячейки, основываясь на их значениях. Любая цветовая шкала употребляет 2-ух либо трехцветный градиент. К примеру, в цветовой шкале “Красноватый-желтый-зеленый” наибольшие значения выделены красноватым, средние – желтоватым, а малые – зеленоватым.
- Наборы значков добавляют особые значки в каждую ячейку на базе их значений.
Внедрение предустановленных стилей
- Выделите ячейки для сотворения правила условного форматирования.
- Нажмите команду Условное форматирование. Покажется выпадающее меню.
- Наведите указатель мыши на подходящую категорию, а потом изберите предустановленный стиль.
- Условное форматирование будет использовано к выделенным ячейкам.
Как употреблять пользовательские правила условного форматирования для дат в Excel
Добавление условного форматирования в ячейку в Excel дозволяет использовать разные характеристики форматирования, такие как цвет, когда данные в данной нам ячейке соответствуют данным вами условиям.
Аннотации в данной нам статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Mac, Excel для Office 365 и Excel Online.
Как употреблять условное форматирование
Чтоб упростить условное форматирование, Excel поддерживает предустановленные характеристики, которые обхватывают нередко применяемые ситуации, такие как:
- Даты
- Дублирующиеся данные
- Значения выше либо ниже среднего значения в спектре ячеек
В случае дат предустановленные характеристики упрощают процесс проверки данных на даты, близкие к текущей дате, такие как вчера, завтра, крайняя неделька либо последующий месяц.
Но если вы желаете проверить даты, которые выходят за границы перечисленных характеристик, настройте условное форматирование, добавив свою формулу, используя одну либо несколько функций даты в Excel.
Проверьте даты просрочки 30, 60 и 90 дней
Настройте условное форматирование при помощи формул , задав новое правило, которому Excel будет следовать при оценке данных в ячейке.
В Excel применяется условное форматирование сверху вниз, как они показываются в диалоговом окне « Диспетчер правил условного форматирования ».
Даже если к неким ячейкам может применяться несколько правил, 1-ое правило, соответственное условию, применяется к ячейкам.
В данной нам демонстрации для сотворения результатов употребляется текущая дата, за 40 дней до текущей даты, за 70 дней до текущей даты и за 100 дней до текущей даты.
Проверьте даты 30 дней просроченных
На пустом листе Excel выделите ячейки от C1 до C4, чтоб выделить их. Это спектр, к которому будут применяться правила условного форматирования.
- Изберите « Домой» > « Условное форматирование» > « Новое правило», чтоб открыть диалоговое окно « Новое правило форматирования ».
- Изберите « Употреблять формулу», чтоб найти ячейки для форматирования .
- В текстовые поля Формат значения, где эта формула является настоящей , введите формулу:
Эта формула инспектирует, не прошли ли даты в ячейках C1-C4 наиболее 30 дней.
Добавить правило для дат наиболее 60 дней просрочки
Заместо того, чтоб повторять все шаги, описанные выше, чтоб добавить последующие два правила, используйте опцию Manage Rules, чтоб добавить доп правила сходу.
- Выделите ячейки C1-C4, если нужно.
- Изберите Домашняя страничка > Условное форматирование > Управление правилами, чтоб открыть диалоговое окно Диспетчер правил условного форматирования .
- Изберите Новое правило .
- Изберите « Употреблять формулу», чтоб найти ячейки для форматирования .
- В текстовые поля Формат значения, где эта формула является настоящей , введите формулу:
Эта формула инспектирует, не превосходят ли даты в ячейках C1-C4 наиболее 60 дней.
Добавить правило для дат наиболее 90 дней, просроченных
- Выделите ячейки C1-C4, если нужно.
- Изберите Домашняя страничка > Условное форматирование > Управление правилами, чтоб открыть диалоговое окно Диспетчер правил условного форматирования .
- Изберите Новое правило .
- Изберите « Употреблять формулу», чтоб найти ячейки для форматирования .
- В значениях формата, где эта формула верна , введите формулу:
- Эта формула инспектирует, не превосходят ли даты в ячейках С1 по С4 наиболее 90 дней.
- Изберите « Формат», чтоб открыть диалоговое окно « Формат ячеек ».
- Изберите вкладку « Заливка », чтоб узреть характеристики цвета заливки фона.
- Изберите цвет заливки фона.
- Два раза нажмите клавишу ОК, чтоб закрыть диалоговое окно и возвратиться в диалоговое окно диспетчера правил условного форматирования .
- Изберите ОК, чтоб закрыть это диалоговое окно и возвратиться к рабочему листу .
Цвет фона ячеек C1-C4 изменяется на крайний избранный цвет заливки.
Проверьте правила условного форматирования
Проверьте правила условного форматирования в ячейках С1 по С4, введя последующие даты:
- Введите текущую дату в ячейку C1. Ячейка поменяется на белоснежный фон по дефлоту с черным текстом, так как ни одно из правил условного форматирования не применяется.
- Введите последующую формулу в ячейку C2:
Эта формула описывает, какая дата наступает за 40 дней до текущей даты. Ячейка заполняется цветом, избранным для правила условного форматирования для дат, которые просрочены наиболее чем на 30 дней.
Эта формула описывает, какая дата наступает за 70 дней до текущей даты. Ячейка заполняется цветом, избранным для правила условного форматирования для дат, которые просрочены наиболее чем на 60 дней.
Эта формула описывает, какая дата происходит за 100 дней до текущей даты. Цвет ячейки поменяется на цвет, избранный вами для правила условного форматирования для дат, которые просрочены наиболее чем на 90 дней.
QlikView. Условное форматирование «Как в Эксель»
Задачка – создать условное форматирование таблицы «как в эксель». QlikView полностью для себя совладевает со обычными задачками раскраски по условию, но вот с выбором характеристик динамически, да к тому же как в эксель – появился вопросец «это как совершенно. ».
Результат: было несколько вариантов реализации, несколько версий, потрачено большое время на тестирование и отладку. Проводился рефакторинг уже готовой реализации. Представляю итоговый вариант, удовлетворяющий потребности заказчика, оттестированный и сверенный.
Многофункциональная задачка(постановка)
Имеется таблица, в которой измерения и выражения выбираются динамически, формулы расчета самих значений ординарны: суммы, средние, относительные проценты, абсолютные значения и тому схожее.
Требуется отдать возможность юзеру раскрасить таблицу в зависимости от избранных характеристик. Главные вводные — один столбец должен уметь раскрашиваться различными цветами, другими словами мульти-раскраска, по различным характеристикам. 2-ое ключевое – обязана быть возможность раскрасить поле в зависимости от другого «взаимосвязанные характеристики», к примеру, мы раскрашиваем «сумму покупок» в зависимости от значения «средняя стоимость товаров на рынке».
Дальше приведу основную часть реализации. Я не буду останавливаться на таковых моментах, как скрыть/закрыть меню, создание списков и тому схожее. Если покажутся вопросцы по деталям расскажу добавочно.
Итоговый итог, путь к которому был тернист:
Блок 1. Подготовка данных для многофункционального меню
Так же мне потребовалось сделать несколько 10-ов переменных:
vColorFormatString – собранная строчка
vColorFormatString1…10 – предусмотрены для разбора строчки из vColorFormatString на 10 характеристик для абсолютных значений, конкретно такое ограничение я сделал хочет, моё мировоззрение, что больше не надо, в целом это ограничение чисто идейное и потратив мало времени количество характеристик можно прирастить.
vColorFormatStringPerc1…10 – предусмотрены для разбора строчки из vColorFormatString на 10 характеристик для относительных значений(проценты)
vColorFormat1…10 — цвета
Блок 2. Разработка многофункционального меню1
- Показывает текущий перечень сделанных характеристик юзером
- Возможность удалить какой-нибудь параметр условного форматирования
- Вызывает меню выбора характеристик условного форматирования
1-ый — это отображение текущих сделанных характеристик.
Данная область это текстовые объекты, отображающие текущие сделанные характеристики на базе парсинга строчки. А вот сейчас 1-ый кирпичик этого всего действа:
Кошмар некий, правда?:)
Сейчас думаю почти всем сделалось понятно, что вся логика реализована на текстовой строке. Собрали строчку – разобрали строчку!
Строчка в сборе имеет вид
||RWA %|RWA %|RGB(255,0,0)|Выше среднего|num(vColorFormatValue1/100,’# ##0,00′)|Empty
||КОД %|КОД %|RGB(255,0,0)|Ниже среднего|num(vColorFormatValue1/100,’# ##0,00′)|Empty
||НОД %|НОД %|RGB(255,0,0)|Ниже среднего|num(vColorFormatValue1/100,’# ##0,00′)|Empty
||RORWA%|RORWA%|RGB(255,0,0)|Ниже среднего|round(num(124.63),0.0001)|Empty
||EL %|EL %|RGB(255,255,0)|Выше среднего|round(num(124.62),0.0001)|Empty
||EL %|EL %|RGB(0,176,80)|Ниже среднего|round(num(124.62),0.0001)|Empty
||ОД %|ОД %|RGB(0,112,192)|Ниже среднего|round(num(124.62),0.0001)|Empty
||
||ЧтоКрасим|ПоКакомуЗначению|Цвет |Условие|Значение1|Значение2
Индексы в части SubField(TextBetween(vColorFormatString,’||’,’||’,1),’|’,4) — выражение всякого параметра выделяется ‘||’ ‘||’, свойство выражения разбиты одиночным ‘|’ таковым образом индексы 1 и 4 молвят взять 1-ый блок характеристик и из него 4-ое значение.
Условное форматирование в Google Таблицах
. С условным форматированием в Excel мы уже незначительно знакомы, так как я касался данной нам темы в статье про поиск дублей в Эксель. Гугл Docs – обычно наиболее непростой и чуждый нам вычислитель, потому с условным форматированием в Sheets и у меня появились препядствия. В данной нам статье я научу вас выделять ячейки по условию, также переносить форматирование на остальные листы и таблицы.
Как создать условное форматирование в «Google Таблице»?
Так как мой веб-сайт спец, я приведу пример из SEO. Чемодан будет таковой: есть таблица запросов с позициями, и необходимо выделить запросы с позициями в первой 10-ке зеленоватым, во 2-ой 10-ке – желтоватым, за ее пределам (ниже) – красноватым. Вот запросы:
Далее – по шагам:
Шаг 1. Идем во вкладку «Формат» и избираем пункт «Условное форматирование».
Справа покажется таковой блок:
Шаг 2. В меню «Правила форматирования» избрать «Меньше либо равно».
В показавшемся окошечке поставить значение «10» и надавить «Готово»
Шаг 3. Кликаем на «Применить к спектру», выделяем спектр ячеек с позициями и жмем «ОК».
Лицезреем, что все ячейки, надлежащие условию, окрасились зеленоватым (поэтому что он стоял по дефлоту).
Шаг 4. В блоке «Правила условного форматирования» нажимаем «Добавить правило».
Устанавливаем 2-ое правило – «Меж» – и проставляем значения «11» и «20». Ниже, где блок «Формат» избираем жёлтый цвет. Кликаем на «Готово».
Единственная ячейка, соответственная условию, окрасилась жёлтым.
Шаг 5. В конце концов, настроим крайнее правило. Устанавливаем правило «Больше» (1), проставляем «20» (2), избираем красноватый цвет (3) и нажимаем «Готово».
Все, наша таблица отформатирована по условиям. Почти всем из SEO-шников не нравится таковой «цветник», а по мне так очень упрощает анализ. Постоянно так делаю.
Как перенести форматирование в Гугл Sheets на иной лист?
А если таковых таблиц много, на каждую придется поновой прописывать правила? Нет, это не непременно, ведь условное форматирование можно скопировать. Жмем на отформатированную ячейку и кликаем на инструмент «Копировать форматирование».
Дальше перебегайте на иной лист и просто выделяете спектр. Клеточки раскрасятся автоматом.
Как сейчас отключить?
Это проще обычного. Выделите спектр, перейдите во вкладку «Формат» и кликните на «Очистить форматирование»
Также можно пользоваться сочетанием жарких кнопок CTRL+, если так сподручнее.
Подытоживаем
Форматировать по условию в Гугл Sheets никак не труднее, чем в Excel – просто незначительно не по привычке. Рекомендую для вас употреблять этот инструмент, так как он достаточно много многофункциональный. При помощи его опять-таки можно находить дубли либо пустые клеточки в таблицах Гугл Docs.
А ты воспользовался условным форматированием в Гугл Sheets? Расскажи нам, для каких целей, в комментах!
