Как изменить указанное значение или цвет ячейки при установленном флажке в Excel?

Как поменять цвет ячейки в Excel

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

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

Нарисовать один или несколько блоков в Excel несложно. Сначала выберите их и на вкладке «Главная» нажмите на стрелку рядом с банкой с краской, чтобы развернуть список. Выберите цвет и, если вам ничего не подходит, нажмите на «Другие цвета».

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

Можно изменить цвет блоков или вообще удалить их, если в таблице что-то уже окрашено. Вы можете либо изменить цвет, либо нажать на кнопку «Нет».

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

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

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

Откроется следующее окно. В верхней части выберите «Форматировать только ячейки, содержащие». Затем мы проверим и это. Чуть дальше укажите условия: у нас есть текст, содержащий определенные слова. В последнем поле можно либо нажать кнопку и указать ячейку, либо ввести текст.

Ссылаясь на ячейку ( =$B$4 ), условие меняется в зависимости от того, что набрано. Другими словами, если вместо яблока в ячейку B4 вписать смородину, то правило изменится, и блоки с одинаковым текстом будут окрашены. Таким образом, если ввести в поле именно яблоко, то это слово будет искаться, независимо от всего остального.

На следующем этапе нажмите кнопку «Формат».

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

Правило создается и сохраняется при нажатии кнопки «OK».

Соответственно, все блоки с указанным текстом были окрашены в красный цвет.

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

Теперь давайте разберемся с цифрами. Расположим числа в столбце D на фоне в соответствии с заданными нами условиями.

Выделите столбец, создайте правило и укажите его тип. Затем введите «Значение» «больше» «15». Вы можете ввести последнее число вручную или указать адрес ячейки, из которой будут взяты данные. Определитесь с набивкой и нажмите «OK».

В блоках, где введенные числа больше, чем выбранное число, имеется затенение.

Давайте введем дополнительные правила для выделенных ячеек — выберите «Управлять правилами. «.

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

Выберите все, как описано выше, измените только цвет и установите условие «меньше или равно» .

Нажмите «Применить» и «OK», когда все готово.

Значения 15 и ниже заштрихованы бледно-голубым цветом.

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

Я буду использовать последний столбец, чтобы проиллюстрировать, как изменить цвет ячейки в соответствии с заданным значением. Выберите их, а затем перейдите в раздел «Управление правилами».

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

В новом окне выберите нужные элементы. Все значения выше 90 будут заполнены темно-зеленым цветом. Поскольку в последнем поле я указал адрес ( =$F$15 ), если я изменю число 90 в ячейке, например, на 110, правило также изменится. Сохраните изменения, нажав кнопку «ОК» .

Я создам еще одно правило, но выделю все, что меньше или равно 90, светло-зеленым цветом. Не забудьте сохранить все, нажав на кнопки внизу справа.

Вот что получилось. По последней колонке можно быстро понять, какой продукт принес наибольшую прибыль.

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

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

Чтобы посмотреть, что вы добавили, выберите диапазон, и в окне «Управление правилами» будут показаны все добавленные вами правила. Используя кнопки в верхней части, вы можете добавлять, изменять или удалять их.

Вы также можете изменить цвет ячеек, выбрав один из предопределенных стилей. Кнопка расположена на вкладке Главная.

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

Фильтрация данных в Excel

• Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

В результате фильтрация не работает автоматически, если данные изменились после фильтрации. Поэтому необходимо повторить процесс, нажав кнопку Reapply в группе Sort and Filter на вкладке Data.

Отмена фильтрации

Чтобы прекратить фильтрацию диапазона данных, просто снова нажмите кнопку Фильтр.

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

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

Для этого выполните следующие действия:

Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

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

Форматирование срезов

1. Выберите ломтик.
2. Выберите группу Стили слайсера на ленте Параметры, которая содержит 14 стилей по умолчанию и возможность создания собственного пользовательского стиля.

1. Выберите кнопку с соответствующим стилем форматирования.

После выбора фрагмента нажмите клавишу Delete.

Расширенный фильтр

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

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

В этом уроке мы рассмотрим основы условного форматирования в программе Excel.

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

Основы условного форматирования в Excel

Наше условное форматирование может быть использовано для:

• Значение цвета
• Изменение шрифта
• Установка формата границы.

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

Где находится условное форматирование в Эксель?

Кнопка «Условное форматирование» находится на панели инструментов, на вкладке «Главная»:

Как сделать условное форматирование в Excel?

При применении условного форматирования система должна выполнить две корректировки:

• Ячейки, которые нужно отформатировать;
• При каких условиях будет назначено форматирование.

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

• Выберите область в таблице данных, для которой нужно применить цветовое выделение:

• Перейдем на вкладку «Главная» на панели инструментов и кликнем на пункт «Условное форматирование». В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выбора первого и последнего значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы иконок

  

  Кроме того, доступны такие условия:

  1. Значения больше или равны определенному значению;
  2. Выделить текст, содержащий определенные буквы или слова;
  3. Выделить цветом дубликаты;
  4. Выделить конкретные даты.
  • Во всплывающем окне в поле «Форматировать ячейки которые МЕНЬШЕ» укажем значение «0», так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

  

  • Для присвоения формата можно использовать предопределенные цветовые палитры или создать свою собственную. Для этого нажмите на элемент :

  

  • Во всплывающем окне формата укажите:
    • Цвет заливки
    • Шрифт
    • Границы ячейки.

    

    • По завершении настроек нажмите кнопку «ОК».

    Ниже приведен пример таблицы с условным форматированием для заданных нами параметров. Данные с отрицательными значениями выделены красным цветом:

    

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

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

    • Выделим диапазон данных. Кликнем на пункт «Условное форматирование» в панели инструментов. В выпадающем списке выберем пункт «Новое правило»:

    

    • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип «Форматировать только ячейки, которые содержат». После этого зададим условие выделять данные, значения которых больше «57», но меньше «59»:

    

    • Кликнем на кнопку «Формат» и зададим формат, как мы это делали в примере выше. Нажмите кнопку «ОК»:

    

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

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

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

    • Выделим первую ячейку для назначения правила. Кликнем на пункт «Условное форматирование» на панели инструментов. Выберем условие «Меньше».
    • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку «ОК».

    

    • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт «Условное форматирование». Выберем в выпадающем меню «Управление правилами» => кликнем на кнопку «Изменить правило»:

    

    • В поле слева всплывающего окна «очистим» ссылку от знака «$». Нажимаем кнопку «ОК», а затем кнопку «Применить».

    

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

    

    На скриншоте ниже данные, для которых курс обмена снизился по сравнению с предыдущим периодом, выделены цветом:

    

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

    Одна клетка может регулироваться несколькими правилами.

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

    Чтобы применить несколько условий к одной ячейке, сделайте следующее

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

    

    • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту «Условное форматирование» и перейдем в раздел «Управление правилами». Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку «Остановить, если истина». Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку «Применить» и «ОК»:

    

    Применив их, наша таблица с данными температуры «подсвечена» корректными цветами, в соответствии с нашими условиями.

    

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

    Чтобы отредактировать назначенное правило, выполните следующие действия:

    • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
    • Перейдите в пункт меню панели инструментов «Условное форматирование». Затем, в пункт «Управление правилами». Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку «Изменить правило»:

    

    • После внесения изменений нажмите кнопку «ОК».

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

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

    • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов «Формат по образцу».
    • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

    

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

    Выполните следующие действия, чтобы удалить формат:

    • Выделите ячейки;
    • Нажмите на пункт меню «Условное форматирование» на панели инструментов. Кликните по пункту «Удалить правила». В раскрывающемся меню выберите метод удаления:

    

    Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

    Спасибо, очень полезный сайт!
    Вопрос:
    Есть таблица с остатками на складе. Последний столбец «остаток» — это формула «приход» минус «выдали».
    Пытаюсь по вашей статье создать правило автоматической окраски строки со значением «0» в ячейке «остаток». Но при создании условного форматирования выдаёт ошибку — ячейка уже содержит формулу, а не просто число. К тому же окрашиваются только ячейки с нужным значением, а не вся строка. Есть способ решить такую проблему?

    голоса

    Рейтинг статьи