какие виды условного форматирования можно применять для текстовых данных выберите все подходящие
Условное форматирование в excel как это работает?
Всем радости на fast-wolker.ru! Сегодня мы познакомимся с такой функцией табличного редактора excel, как «условное форматирование». Что это такое и для чего оно нужно? Условное форматирование предназначено для анализа данных и наглядного представления готовых результатов.
С помощью условного форматирования можно делать такие операции, как изменять шрифт, закрашивать значения различным цветом, а так же задавать формат границ. Применяется это форматирование можно, как на одну, так и на несколько ячеек, а так же строк и столбцов.
Условным такое форматирование называется потому, что формат ячейки задается при помощи условий. То есть, ячейки (или нужные области) меняют свой внешний вид если заданное в екселе условие выполнено (истина) или не выполнено (ложь).
Настройка осуществляется при помощи соответствующей кнопки, которая легко находится во вкладке «Главная». Нажав на нее, в открывшемся меню обнаружим различные варианты условного форматирования.
Когда вы применяете условное форматирование, вы задаете два основных параметра: какие ячейки будут форматироваться и те самые условия для форматирования. Ниже мы и выясним, как проводится такое условное форматирования и какой результат можно получить.
Условное форматирование — если выполняется условие то нужные ячейки в таблице окрашиваются в нужный цвет
Первое, что мы выясним, как в таблице подсветить цветом нужные данные. Например, у нас имеются определенные данные, среди которых нам необходимо выделить значения больше или меньше по отношению к исходному.
Выделяем столбец, который нам нужен, затем идем в пункт «Условное форматирование» и в открывшемся меню выбираем «правила выделения ячеек». Сбоку откроется еще вкладка. Где нам надо выбрать значение «больше» или «меньше». Поскольку нам надо меньшее, то выбираем это значение.
Появляется окно, в котором выбираем необходимое значение, меньше которого надо закрасить ячейки. По умолчанию стоит уже наиболее подходящее, но вы можете выбрать и любое другое, которое необходимо вам.
Одновременно с этим в таблицы все выбранные в этом диапазоне ячейки уже закрашены. Так что изменяя данные, вы теперь можете наглядно видеть готовый результат. В строке рядом, можно выбрать опцию закрашивания. Нажимаем на раскрывающееся меню и видим следующие «расцветки»:
Выбираем наиболее подходящий. Опять-таки, нажимая на каждый, видим готовый результат, так что можно по выбирать, что более приятно взгляду. Если вас не устраивают готовые шаблоны, вы можете нажать на самый последний пункт «пользовательский формат». Далее откроется окно, где вы сможете настроить свою визуализацию.
Когда вы закончите все настройки, нажимаете на кнопку ОК и изменения будут приняты. В результате получим следующую картинку:
В таблице подсвечены все необходимые нам данные и нам уже не нужно взглядом их искать по всему столбцу.
Применение условного форматирования по другому столбцу
Аналогичным образом можно произвести выборку по уже готовому значению, введенному заранее в столбец рядом.
Выделяем столбец, вводим нужную цифру и идем в пункт «Условное форматирование». Здесь, как описано в предыдущем разделе, выбираем «правила выделения ячеек», где так же выбираем больше или меньше, в зависимости от того, что нам нужно. Но пусть будет значение вновь «меньше».
В открывшемся окне в строку «форматировать ячейки, которые меньше…» необходимо внести значение 50. Щелкаем мышью по этой цифре и формула ячейки отобразится в этой строке.
Одновременно подкрасятся и ячейки, которые меньше 50. Выбираем в строке рядом необходимую визуализацию и жмем ОК. В результате получаем готовое форматирование.
Выделение диапазонов несколькими цветами по условию
Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.
Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.
Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».
В строке «форматировать ячейки, которые больше…» ставим значение 40. Цвет выбираем любой другой, например желтый. В результате применения параметров получим следующее.
У меня все значения были больше 40, поэтому желтым цветом закрасился весь столбец. А так, будут выделены те ячейки, которые несут заданный диапазон.
Аналогичным образом можно сделать форматирование по всем тем условиям, которые даны в соответствующем пункте. Однако, бывает ситуация, когда шаблонные условия не удовлетворяют и вам нужно свое особое форматирование. Что делать? Надо просто создать свое условие.
Как создать свое условие для форматирования столбцов?
Начинаем с того, что так же выделяем нужный столбец, для которого хотим создать условие или правило и идем в пункт «условное форматирование», где выбираем «создать правило».
Откроется окно с перечнем всех возможных вариантов. В верхнем окне выбираем тип правила, а в нижнем будут его настройки. Замечу, что для каждого типа правила свои настройки. Выбираем, например, «форматировать только ячейки, которые содержат…».
Ниже увидим данные для изменения этого правила.
Что здесь можно изменить. Первый пункт «значение ячейки». Раскрыв этот пункт увидим, что кроме значения ячеек, здесь есть вариант текст, дата и т.д. Выставляем то значение, которое у нас имеется в столбце. Нам необходимо форматировать данные в ячейках потому оставляем первое значение.
Во втором пункте определяем как будем отбирать данные: между, равно, больше или меньше и т.д. оставляем вариант между.
И в окнах рядом ставим между какими значениями делаем форматирование. Например, больше 45, но меньше 66.
Затем нажимаем на кнопку «форматировать» и в открывшемся окне выбираем графическое значение: цвет, шрифт и т.п.
После применения всех настроек жмем ОК. В результате получим подкрашенные значения в диапазоне от 45 до 66. Все остальные значения окажутся не подсвеченными.
Вот так на простых примерах можно создать условное форматирование по данным в таблице. Если поэкспериментировать с другими настройками, то вы получите полное представление о том, как работает эта функция. Успехов!
Как сделать условное форматирование в Excel? Инструкции с примерами.
В этой статье вы найдете множество быстрых способов как сделать условное форматирование строк, столбцов и отдельных ячеек в MS Excel 2016, 2013 и 2010. Мы рассмотрим, как можно применить различное оформление к данным, которые соответствуют определенным критериям. Это может помочь указать на наиболее важную информацию в ваших электронных таблицах.
Всем известно, что изменить фон ячейки легко. Это можно совершить, просто нажав кнопку «Цвет заливки». Но что, если вы хотите изменить оформление вашей таблицы при выполнении какого-то условия? Более того, что, если вам нужно, чтобы он изменялся автоматически при внесении изменений в таблицу? Условное форматирование для этого является действительно мощной и полезной функцией. Далее в этой статье вы найдете ответы на эти вопросы и прочтете несколько полезных советов, которые помогут выбрать правильный метод условного форматирования для каждой конкретной задачи.
В то же время изменение внешнего вида в связи с содержанием текущей либо какой-то иной ячейки либо от иных условий часто считается одной из самых сложных и непонятных функций, особенно для новичков. Если вас тоже пугает эта функция, не бойтесь! На самом деле, она очень удобна и проста в использовании, и вы убедитесь в этом всего за 5 минут после прочтения этого руководства. А теперь взгляните, сколько всего мы можем сделать!
Кроме того, если вы будете использовать форматирование по условию, то имейте в виду, что оно имеет более высокий приоритет по сравнению с обычным оформлением вручную, которое вы можете сделать через меню Главная – Формат.
Вы можете применить условное форматирование к одной или нескольким позициям, строкам, столбцам или всей таблице на основе их содержимого или при выполнении какого-то другого условия. Это делается путем создания правил (условий), в которых вы определяете, когда и как следует изменить вид выбранных клеток таблицы.
Где находится форматирование по условию в Excel?
Эта функция включает в себя стандартный набор заранее определенных правил и инструментов. Но главное, у пользователя есть возможность самому придумать и настроить необходимый алгоритм закраски и выделения, используя свои формулы.
Теперь, когда вы знаете, как активировать функцию условного форматирования в Excel, давайте продолжим и посмотрим, какие у вас есть варианты форматирования и как вы можете создавать свои собственные правила.
Как автоматически изменить цвет при помощи условного форматирования?
Чтобы по-настоящему использовать возможности условного формата в Excel, вы должны научиться создавать различные типы правил.
Правила условного форматирования определяют 2 ключевых момента:
Я покажу вам, как применить условное форматирование в Excel 2016, потому что это, кажется, самая популярная версия в наши дни. Однако оно практически не отличается от форматирования в версиях 2007, 2013 и 2010. Поэтому у вас не возникнет проблем с выделением цветом нужной информации независимо от того, какая версия установлена на вашем компьютере.
Задача: у вас есть таблица или диапазон данных, и вы хотите изменить фон ячеек на основе их содержания. Кроме того, вы хотите, чтобы он менялся динамически, отражая изменения данных.
Итак, вот что вы делаете шаг за шагом:
Перейдите на вкладку «Главная» и выберите 
(1) > «Правила выделения ячеек» (2) > «Меньше» (3). В более ранних версиях программы нужное нам меню располагается в группе «Стили».
Конечно, можно использовать любой другой тип правил, который больше подходит для ваших данных, например:
В диалоговом окне укажите, что числа должны быть меньше 100, также выберите вариант выделения.
В первом поле задается условие, а во втором указывают, каким образом отформатировать полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Но если хочется применить иные оттенки – сделать это можно, перейдя в «Пользовательский формат».
В результате клетки таблицы с количеством меньше 100 окрасились в красный цвет.
Приступаем к созданию второго правила. С этой же областью таблицы проделайте те же операции, только выберите на третьем шаге пункт «Больше».
В результате получим нужную нам раскраску.
Это самый простой вариант заливки ячеек.
С помощью использованных нами «Правил выделения ячеек»:
Тот же результат мы можем получить и чуть иначе. Если ни одно из готовых правил форматирования не отвечает вашим потребностям, вы можете создать новое с нуля. Для этого вновь перейдите на вкладку «Главная» и выберите (1 на рисунке) > «Создать правило» (2).
Затем выберите пункт «Форматировать только ячейки, которые содержат» (3). Чуть ниже укажите, что число должно быть меньше (4) цифры «100» (5).
И далее укажите, как это все должно выглядеть. Нажмите кнопку «Формат» (6).
Выберите «красный» на открывшейся вкладке «Заливка».
Повторите все то же самое еще раз, только измените условие: цифра должна быть больше или равна 100. И новый цвет условного форматирования, конечно же, выберите сейчас зеленый.
И, наконец, третий способ – самый сложный, но зато самый универсальный и с большими возможностями. Чуть ранее мы создали правила форматирования, указав определенные числа, дату либо текст. Однако в некоторых случаях имеет смысл основывать условие на значении определенной ячейки. Преимущество этого подхода состоит в том, что в зависимости от того, как значение этой ячейки изменится в будущем, ваше условное форматирование будет корректироваться автоматически и отражать изменение данных.
Затем выберите пункт «Использовать формулу для определения форматируемых ячеек» (3). Теперь нужно указать диапазон, в котором мы хотим что-то выделить. Для этого нажмите на пиктограмму со стрелкой вверх (4) и укажите мышкой начало диапазона – D2. Следите за тем, чтобы ссылка не была абсолютной (можно для этого использовать F4). И в конце просто допишите условие: “ 
Осталось только определить новые правила форматирования. Нажмите кнопку «Формат» (6).
Выберите красный на вкладке «Заливка».
Повторите создание условия еще раз, только выражение запишите D2>=100 и выберите зеленый.
Вы спросите: «А зачем все так сложно, если есть более простой вариант?» Дело в том, что использование формулы – более универсальный подход, который мы в дальнейшем будем еще неоднократно применять.
Итак, цель достигнута: фон выбранных ячеек изменяется от их наполнения.
Совет: вы можете использовать тот же метод не только для закраски, но и для изменения оформления шрифта. Для этого просто перейдите на вкладку «Шрифт» в диалоговом окне «Формат», которое мы обсуждали на шаге 6, и выберите предпочитаемый вариант оформления.
Условное форматирование Excel по значению ячейки.
Выделяем область для применения условного форматирования М2:М16 и затем выбираем пункт «Создать правило». В описании правила запишем выражение:
Обратите внимание, что здесь используются относительные ссылки, чтобы программа могла последовательно перебрать все ячейки указанной ей области, и при этом каждой ячейке из столбца М соответствовала ячейка из столбца В, расположенная в той же строке и относящаяся к тому же самому товару.
Отображение выделенных ячеек настройте так же, как мы это рассматривали ранее.
Как видно на рисунке, созданное нами правило условного форматирования работает правильно и выделяет декабрьские продажи тех товаров, которые выросли по сравнению с январём.
Использование абсолютных и относительных ссылок в правилах.
Для того, чтобы было проще изменять условия выделения определенных значений в таблице Эксель, запишем некоторые параметры отбора в специально отведённые для этого ячейки.
Задача: выделить в таблице заказы с количеством менее 50 и более 100 ед.
Наши ограничения записываем в D1 и D2. Далее создаем первое правило условного форматирования для диапазона E5:E24.
Абсолютная ссылка на D2 означает, что каждая из ячеек нашего диапазона сравнения должна сравниваться именно с D2. А относительная ссылка на первую ячейку нашей выделенной области E5 предписывает программе начать именно с этой позиции и последовательно двигаться вниз по столбцу, сравнивая количество с пороговым значением 100.
Как обычно, выбираем цвет заливки в случае выполнения условия.
Аналогичным образом для E5:E24 создаем второе правило
Аналогично второе условие мы меняем с E5 
В то же время ссылка на D2 так и остается абсолютной, поскольку условие записано именно в этой ячейке. В результате получаем «полосатую» таблицу, где цветом выделены уже целые строки. И вся хитрость заключается в грамотном использовании абсолютных ссылок в правилах.
Вывод. Давайте постараемся запомнить несложные принципы использования ссылок в правилах:
Как использовать в правилах ссылку на соседние листы?
В последних версиях начиная с 2010 года, в формулах условия вы можете спокойно использовать ссылки на данные с других листов. Делается это точно так же, как и в обычных формулах.
В более ранних версиях программы – 2007 и 2003, это ограничение можно легко обойти, использовав именованные диапазоны. Вы просто присваиваете определенные имена диапазонам на текущем или на соседних листах, а затем используете эти имена в функциях.
В частности, вместо
можно работать по формуле
Как вы понимаете, диапазон ‘Formatting (Лист2)’!$E$2:$E$21 получил имя «продажи» и теперь к нему можно обратиться из любого места вашей рабочей книги.
Приоритет выполнения правил — это важно!
При использовании условного форматирования в Excel вы не ограничены только одним правилом на ячейку. Вы можете применять столько правил, сколько требует логика вашего проекта. В том случае, если в вашей таблице используется несколько правил, то важно, в каком порядке они выполняются.
Если выбрать меню «Управление правилами» и указать там «Текущий лист», то вы увидите список имеющихся правил.
В этой таблице мы хотим выделить желтым цветом предстоящие в недалёком будущем отгрузки, а вот те из них, которые должны произойти сегодня или завтра, обозначить красным. Ведь к ним должно быть повышенное внимание и их нужно срочно выполнить.
Сначала создадим первое условие:
Как видим, сюда попадают все строки, в которых дата отгрузки больше текущей даты, записанной в ячейке C2.
Важно! Правила, расположенные выше в списке, имеют более высокий приоритет (1 и 2 на рисунке вверху). Новые правила всегда добавляются в начало списка и по этой причине имеют более высокий приоритет. Результат их работы не может быть изменен действием предшествующих правил, расположенных ниже.
Однако, порядок выполнения всегда можно изменить в этом же окне при помощи стрелок «Вверх» и «Вниз» (3).
Как редактировать условное форматирование?
Для того, чтобы изменить ранее созданное условие, нужно в первую очередь посмотреть, какие условия мы применяем к таблице и далее просто выбрать нужное правило. Последовательность действий та же, что мы рассмотрели чуть выше. Но на всякий случай еще раз повторю ее на скриншоте: нам нужен раздел «Управление правилами», затем указать, что рассматриваем текущий лист.
При нажатии иконки «Изменить. » мы попадаем в уже знакомое нам меню создания правила. Только все поля там уже заполнены текущими значениями. Остается только изменить то, что необходимо, и нажать «Ок».
А если забыл, где какие правила создавал?
В виду того, что этот способ имеет приоритет над обычным оформлением, вы можете получить внешний вид таблицы не совсем таким, как ожидали. Особенно, если забудете, где и какие правила создавали. Итак, как нам быстро найти в таблице все ячейки с условным форматированием?
Вы увидите все созданные вами правила, а также приоритет их выполнения. Напомним, что наивысший приоритет имеют правила, находящиеся в начале списка: чем выше, тем важнее. Также указаны области, к которым применяются созданные форматы. Думаю, здесь разобраться будет совершенно несложно.
Как можно скопировать условное форматирование?
Вот несколько способов для копирования правил.
Копировать формат по образцу
Можно скопировать так же, как и обычный формат.
На вкладке «Главная» в самом начале ленты расположена группа «Буфер обмена». В ней вы видите пиктограмму кисти – формат по образцу (в разных версия выглядит по-разному, но называется одинаково). Клик по ней копирует не только формат выделенных ячеек, но и условия для него, если таковые имеются. Следующим действием необходимо выделить те ячейки, в которые данное оформление необходимо перенести.
Имейте в виду, что описанный способ перенесет абсолютно все форматы, в том числе и установленные вручную.
Копирование через вставку.
Альтернативным вариантом дублировать формат является специальный способ вставки.
Скопируйте ячейки с нужным условным форматом любым привычным для вас способом. Выделите диапазон, на который требуется перенести формат (можете выделить и не смежные, зажав клавишу CTRL), а затем по щелчку правой кнопки мыши выберите пункт «Специальная вставка…». Тогда программа отобразит окно, где потребуется установить переключатель на точке «форматы», после чего нажать «OK».
Управление правилами.
Можно воспользоваться диспетчером правил.
Пройдите по следующему пути: -> «Управление правилами…».
Из раскрывающего списка «Показать правила. » выберите пункт «Этот лист». Вы сможете увидеть все правила, которые действуют на текущем листе.
В столбце списка правил «Применяется к» указаны диапазоны, на которые распространяется каждое правило. Допишите в это поле через точку с запятой нужные адреса ячеек, чтобы применить и к ним ранее созданные условия.
Данный способ более трудоемкий, чем предыдущие два. Но его прелесть в том, что он позволяет распространять только нужные правила. Это особенно полезно тогда, когда к копируемым ячейкам применяется несколько условий одновременно, а скопировать нужно только одно из них.
Как убрать условное форматирование?
Эта операция такая же несложная, как и создание правила. Выберите , и затем – «Удалить правила». Вам будет предложено либо удаление из выделенного диапазона данных, либо вовсе всех правил на листе. Но имейте в виду, что при этом вы удалите всё, что было ранее создано. А ведь, возможно, что-то вы хотели бы сохранить.
Поэтому существует и более тонкий инструмент, которым мы рекомендовали бы пользоваться и для редактирования, и для их удаления.
Используйте последний пункт выпадающего меню: «Управление правилами».
Здесь вы видите все правила на текущем листе, к каким диапазонам они относятся и что делают. Поэтому гораздо проще выбрать определенное правило и удалить его.
Либо изменить, если в этом есть необходимость.
Почему не работает?
Если вы не получаете ожидаемого результата, то в первую очередь следует убедиться, верно ли работает созданное вами правило условного форматирования. Для этого вы можете скопировать формулу из правила в любую пустую ячейку и посмотреть, какой результат будет получен. Если вы форматируете по условию целый столбец цифр, то выберите пустое место справа от вашей таблицы.
Если формула сложная, можно разбить ее на части и применить тот же метод отладки.
Надеемся, что вы нашли ответы на интересующие вас вопросы по условному форматированию в нашей инструкции.
Тем не менее, если всё же что-то не получается или не работает – пишите в комментариях ниже. Мы постараемся вам ответить либо даже сделаем отдельный материал, посвященный вашей проблеме.