Условное форматирование значений в диапазоне в excel

Создаём правила условного форматирования в Excel

Выберите начальную ячейку в первой из тех строк, которые Вы планируете форматировать. Кликните Conditional Formatting (Условное Форматирование) на вкладке Home (Главная) и выберите Manage Rules (Управление Правилами).

В открывшемся диалоговом окне Conditional Formatting Rules Manager (Диспетчер правил условного форматирования) нажмите New Rule (Создать правило).

В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите последний вариант из списка – Use a formula to determine which cells to format (Использовать формулу для определения форматируемых ячеек). А сейчас – главный секрет! Ваша формула должна выдавать значение TRUE (ИСТИНА), чтобы правило сработало, и должна быть достаточно гибкой, чтобы Вы могли использовать эту же формулу для дальнейшей работы с Вашей таблицей.

Давайте проанализируем формулу, которую я сделал в своём примере:

=$G15 – это адрес ячейки.

G – это столбец, который управляет работой правила (столбец Really? в таблице). Заметили знак доллара перед G? Если не поставить этот символ и скопировать правило в следующую ячейку, то в правиле адрес ячейки сдвинется. Таким образом, правило будет искать значение Yes, в какой-то другой ячейке, например, H15 вместо G15. В нашем же случае надо зафиксировать в формуле ссылку на столбец ($G), при этом позволив изменяться строке (15), поскольку мы собираемся применить это правило для нескольких строк.

=”Yes” – это значение ячейки, которое мы ищем. В нашем случае условие проще не придумаешь, ячейка должна говорить Yes. Условия можно создавать любые, какие подскажет Вам Ваша фантазия!

Говоря человеческим языком, выражение, записанное в нашей формуле, принимает значение TRUE (ИСТИНА), если ячейка, расположенная на пересечении заданной строки и столбца G, содержит слово Yes.

Теперь давайте займёмся форматированием. Нажмите кнопку Format (Формат). В открывшемся окне Format Cells (Формат ячеек) полистайте вкладки и настройте все параметры так, как Вы желаете. Мы в своём примере просто изменим цвет фона ячеек.

Когда Вы настроили желаемый вид ячейки, нажмите ОК. То, как будет выглядеть отформатированная ячейка, можно увидеть в окошке Preview (Образец) диалогового окна New Formatting Rule (Создание правила форматирования).

Нажмите ОК снова, чтобы вернуться в диалоговое окно Conditional Formatting Rules Manager (Диспетчер правил условного форматирования), и нажмите Apply (Применить). Если выбранная ячейка изменила свой формат, значит Ваша формула верна. Если форматирование не изменилось, вернитесь на несколько шагов назад и проверьте настройки формулы.

Теперь, когда у нас есть работающая формула в одной ячейке, давайте применим её ко всей таблице. Как Вы заметили, форматирование изменилось только в той ячейке, с которой мы начали работу. Нажмите на иконку справа от поля Applies to (Применяется к), чтобы свернуть диалоговое окно, и, нажав левую кнопку мыши, протяните выделение на всю Вашу таблицу.

Когда сделаете это, нажмите иконку справа от поля с адресом, чтобы вернуться к диалоговому окну. Область, которую Вы выделили, должна остаться обозначенной пунктиром, а в поле Applies to (Применяется к) теперь содержится адрес не одной ячейки, а целого диапазона. Нажмите Apply (Применить).

Теперь формат каждой строки Вашей таблицы должен измениться в соответствии с созданным правилом.

Вот и всё! Теперь осталось таким же образом создать правило форматирования для строк, в которых содержится ячейка со значением No (ведь версии Ubuntu с кодовым именем Chipper Chameleon на самом деле никогда не существовало). Если же в Вашей таблице данные сложнее, чем в этом примере, то вероятно придётся создать большее количество правил. Пользуясь этим методом, Вы легко будете создавать сложные наглядные таблицы, информация в которых буквально бросается в глаза.

Формат данных в Excel (+видео)

Управление правилами

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

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

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

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

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

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

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

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Представить числовые данные в виде текста

Управление правилами

Кроме умения создавать правила, условным форматированием также необходимо корректно управлять. В особенности это принципиально, когда для 1-го спектра применяется несколько критерий. Но обо всем по порядку.

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

В самом верху окна можно избрать, какие правила следует выводить в перечне: из текущего спектра, с этого листа, из хоть какого другого листа открытой книжки.

1-ые три клавиши диспетчера должны быть понятны без доп пояснений, а вот на крайних 2-ух (стрелки ввысь и вниз) остановимся подробнее.

На изображение приведено 2 правила: значение равно трем и значение больше 2-ух. Представьте, что они использованы к ячейке со числом 3. Какое из их сработает? В этом случае оба, потому что меж ними нет конфликта в форматировании, одно отвечает за заливку, а 2-ое за границу. Но если б они оба отвечали за один и этот же стиль, то выполнилось правило, которое стоит выше, поэтому что имеет больший ценность.

Итак вот, стрелками окна можно поменять положение раздельно выделенного правила и, соответственно, его значимость.

Разглядим очередной вариант, когда требуется выполнить лишь одно условие. В конце всякого правила имеется флаг «Приостановить, если правда». Выставив его, Вы отменяете выполнение всех следующих правил для текущего спектра, при условии, что это оно производится. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше 2-ух» произведена не будет.

Гистограммы

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

• градиентная;
• сплошная.

Рассмотрим каждый из предложенных вариантов.

Градиентная заливка

1. Первым делом необходимо выделить нужные строки и столбцы. Затем кликнуть на иконку «Условное форматирование». После этого перейти в раздел «Гистограммы» и выбрать любую из предложенных заливок.

К значениям по умолчанию относятся:

• зеленая;
• красная;
• оранжевая;
• голубая;
• фиолетовая.

При наведении на каждый из вариантов вам будет доступен предварительный просмотр.

Сплошная заливка

Данный тип маркировки сильно не отличается от вышеописанного и находится он в том же разделе.

Цвета используются те же самые.

Другие правила

Здесь вы сможете настроить:

стиль;

минимальное и максимальное значение;

внешний вид столбца.

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

Простейшие варианты условного форматирования

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

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

• Гистограммы;
• Цифровые шкалы;
• Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

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

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

При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.

Подсветка дат и сроков.

Как это сделать в Excel 2003

ВКЛЮЧИТЕ СУБТИТРЫ! «Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон. Но принцип действий тот же самый. Покоряйте Excel и до новых встреч!

Комментарии:

1. Svetlana — 27.06.2015 21:28

наконец-то узнала, как это можно сделать!

Виктор — 14.04.2016 17:23

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

salam — 19.05.2016 16:24

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Федя — 16.11.2016 14:39

Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом

Оля — 03.05.2017 12:12

подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо

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

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

Выделяем столбец, вводим подходящую цифру и идем в пункт «Условное форматирование». Тут, как описано в прошлом разделе, избираем «правила выделения ячеек», где так же избираем больше либо меньше, зависимо от того, что нам необходимо. Но пусть будет значение вновь «меньше».

В открывшемся окне в строчку «форматировать ячейки, которые меньше…» нужно внести значение 50. Щелкаем мышью по данной для нас цифре и формула ячейки отобразится в данной для нас строке.

Сразу подкрасятся и ячейки, которые меньше 50. Избираем в строке рядом нужную визуализацию и нажимаем ОК. В итоге получаем готовое форматирование.

Условное форматирование с помощью формулы

Рассмотрим следующий пример.

Есть зарплатная ведомость и нужно выделить все оклады свыше 35000 рублей. В одной из предыдущих заметок мы уже решали подобную задачу и сумма в 35000 задавалась непосредственно в правиле, что очень неудобно, ведь может потребоваться постоянно изменять значения для анализа таблицы. В такой ситуации придется каждый раз корректировать правило.

Именно поэтому интересующее нас значение вынесем в отдельное поле.

Затем выделим диапазон с окладами (1) и создадим собственное правило условного форматирования (3). При задании условия (4) мышью укажем нужные ячейки в таблице (5). Затем зададим само форматирование (6), например, изменим фон на красный.

Такая формула работать не будет и, скорее всего, вы уже догадываетесь почему. По умолчанию в формулу подставляются абсолютные ссылки на ячейки и поэтому в итоге мы не увидим никакого форматирования, так как самая первая ячейка диапазона (С6) не удовлетворяет заданному условию, а из-за абсолютных ссылок в нем условие не будет изменяться для последующих ячеек.

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

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

Итак, скопируем формулу из правила условного форматирования и вставим ее в столбец рядом. Раскопируем формулу по диапазону.

Мы видим, что все значения одинаковы и являются ложью. Также пробежав по столбцу со значениями в строке формул увидим неизменную формулу с абсолютными ссылками. То есть весь столбец зависит от значения его первой ячейки (С6), в чем легко убедиться, изменив ее значение на удовлетворяющее условию.

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

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

Интересующие нас значения будут ИСТИНОЙ, а значит в случае с условным форматированием такая ячейка ему подвергнется.

Осталось лишь скопировать формулу и изменить правило условного форматирования (1). Сразу можно изменить и само условное форматирование, поменяв фон на менее едкий, а также сделав шрифт полужирным (2).

Все работает!

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

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

Усложним немного задачу.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Формула условного форматирования для игнорирования пустых ячеек

Как создать таблицу в Эксель: Форматирование ячеек с помощью команд вкладки Главная

Приоритет выполнения правил — это важно!

Решение

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Практика

Правила использования формул в условном форматировании

Виды условного форматирования

Форматировать все ячейки на основании их значений

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

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует наибольшему значению спектра правила. Т.е. ячейка, содержащая наибольшее значение будет залита стопроцентно, а ячейка со значением в 2 раза наименьшим наибольшему – наполовину. В случае отрицательного значения, столбец будет окрашен иным цветом и иметь другую направленность (это можно поменять).

Глава 20. Условное форматирование и формулы массива

Что такое формат данных в Excel?

Как задать несколько условий для изменения цвета строки

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

• Как надо из наименования, данный тип отформатирует хоть какое значение, к которому применяется определенная формула. В этом примере все данные Excel, которые касаются будущих дат, будут выделены красноватым цветом.
• Изберите область в таблице, для которой вы желаете применить форматирование, и сделайте новое правило с типом «Употреблять формулу для определения форматируемых ячеек».
• Введите формулу = B2> СЕГОДНЯ ().
• Нажмите клавишу «Формат …» и изберите красноватый цвет в новеньком окне.
• Подтвердите выбор два раза нажав «ОК».

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

Как создать таблицу в Эксель: Настройка столбцов и строк

Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)

Определить, есть ли слово в списке несколько сложнее, чем определить содержится ли в списке некий текст. Разница в этих подходах проявляется при наличии в списке слов, содержащих искомый текст, но не совпадающих с ним. Например, слово в списке Мотодрель содержит текст Дрель, но слово Дрель не содержится в этой ячейке.

Чтобы выделить ячейки в списке, которые содержат слово Дрель, нужно воспользоваться формулами в файле примера на Листе «1 критерий (слово)».

Понятно, что нужно выделить ячейки, в которых:

• точно содержится слово Дрель ИЛИ;
• текстовая строка начинается со слова Дрель ИЛИ;
• текстовая строка заканчивается словом Дрель ИЛИ;
• слово Дрель содержится в середине текстовой строки (критерий обрамлен пробелами).

В принципе можно записать одну большую формулу и не использовать дополнительные столбцы (C:F)

=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));ИЛИ(ЕСЛИОШИБКА(ПОИСК(» «&$A$6&» «;A9);0));ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&» «;ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));ИЛИ(ЕСЛИОШИБКА(» «&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0)))

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

Возможно применять несколько правил к одной ячейке.

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец). Аналогично задаем правила форматирования для незавершенных проектов. Аналогично задаем правила форматирования для незавершенных проектов

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Получаем результат:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Автоматическое заполнение ячеек датами

Пользовательский формат ячеек в Excel: для чего нужен и как его сделать

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

Для примера разберём, как создать формат телефонного номера.

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

Вызываем окно форматирования ячеекСкриншот: Excel / Skillbox Media

В появившемся окне выберем значение «Все форматы». Excel предложит дополнительные варианты. Можно выбрать один из предложенных и изменить его либо в поле «Тип» ввести полностью новый.

Нажимаем сюда, чтобы добавить пользовательский форматСкриншот: Excel / Skillbox Media

В поле «Тип» введём формат телефонного номера — например, в таком виде: +7(000)-000-00-00. В поле «Образец» можно проконтролировать, какой вид примет ячейка после форматирования.

Нажмём кнопку «ОК».

Добавляем пользовательский форматСкриншот: Excel / Skillbox Media

Готово — все выделенные ячейки получили пользовательский формат.