Auditportal.ru

Аудит Портал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

Заливка

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

Замена

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

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

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

Фрукты

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

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

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

Правило

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

Красный

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

Кнопка ОК

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

Читайте так же:
Как легко ранжировать список без связей в Excel?

Закрашено

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

На цветном фоне

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

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

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

Больше 15

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

Пункт управление

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

Создание

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

Меньше 15

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

Применение

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

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

Завезено

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

Цена

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

Новое

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

Больше 90

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

Значения меньше 90

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

Залитые ячейки

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

Изменение

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

Итог

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

Читайте так же:
Как найти и заменить названия вкладок листов в Excel?

Все условия

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

Стили

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

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

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

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

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

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

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

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

Срезы

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

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

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

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

Читайте так же:
Как использовать флажок, чтобы скрыть или показать рабочий лист в Excel?

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

Вставка среза в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Где находится пункт условное форматирование в Excek

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

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

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

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

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

Условное форматирование в Excel - выделение диапазона данных

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

    Условное форматирование - правило меньше

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

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

    Форматирование ячеек в Excel меньше чем

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

    Пользовательский формат условного форматирования в Эксель

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

      Пользовательский формат ячеек

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

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

      условное форматирование отрицательных значений

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

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

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

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

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

      условное форматирование на основе значений между числами

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

      условное форматирование по собственному правилу

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

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

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

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

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

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

      Изменение правила ячейки по значению другой ячейки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Изменение правила ячейки по значению другой ячейки

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

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

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

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

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

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

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

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

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

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

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

      голоса
      Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector