Что такое условное форматирование электронных таблиц
Перейти к содержимому

Что такое условное форматирование электронных таблиц

  • автор:

Применение условного форматирования с помощью формулы в Excel для Mac

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

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

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

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

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

  1. Выделите ячейки от A2 до A7 (для этого щелкните и перетащите указатель мыши с ячейки A2 в ячейку A7).
  2. На вкладке Главная выберите Условное форматирование >Создать правило.
  3. В поле Стиль выберите Классический.
  4. Под полем Классический выберите элемент Форматировать только первые или последние значения и измените его на Использовать формулу для определения форматируемых ячеек.
  5. В следующем поле введите формулу: =A2>СЕГОДНЯ() Функция СЕГОДНЯ используется в формуле для определения значений дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.
  6. В поле Форматировать с помощью выберите пользовательский формат.
  7. В диалоговом окне Формат ячеек откройте вкладку Шрифт.
  8. В поле Цвет выберите значение Красный. В поле Начертание шрифта выберите Полужирный.
  9. Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.

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

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

  1. Выделите ячейки от C2 до C7.
  2. На вкладке Главная выберите Условное форматирование >Создать правило.
  3. В поле Стиль выберите Классический.
  4. Под полем Классический выберите элемент Форматировать только первые или последние значения и измените его на Использовать формулу для определения форматируемых ячеек.
  5. В следующем поле введите формулу: =C2=»Y» Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают Excel, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.
  6. В поле Форматировать с помощью выберите пользовательский формат.
  7. В верхней части окна откройте вкладку Шрифт.
  8. В поле Цвет выберите Белый. В поле Начертание шрифта выберите Полужирный.
  9. В верхней части окна откройте вкладку Заливка и для параметра Цвет фона выберите значение Зеленый.
  10. Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.

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

Попробуйте попрактиковаться

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

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

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).

Что такое условное форматирование электронных таблиц

Электронные таблицы EXCEL2007

Условное форматирование-оформление ячеек в зависимости от введенных данных.

Находится на Главной ленте — Панель инструментов — стиль.

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

При создании условного формата можно ссылаться на другие ячейки на листе, например =FY2006!A5, но внешние ссылки на другую книгу использовать нельзя.

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

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

-Задачи и их решение

меню «правила выделения ячеек» содержит правила на основе арифметического сравнения чисел и дат, содержания теста, повторений значений; Правила отбора первых и последних значений -выделение наим. и наиб. чисел, сравнение числе относительно среднего значения диапазона чисел. Цветовые шкалы — сравнение чисел относительно друг друга: Чем цвет ячейки темнее, тем больше число. Наборы знаков — распределение чисел по 3 компонентам: наименьшие, средние и наибольшие, а также отображение того, как изменяется значение чисел относительно предыдущего, т.е. рассматривает столбец или строку как какую-то последовательность чисел.

-контекстное меню условного форматирования

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

Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.

Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

Таблица без форматирования

Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.

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

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

Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.

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

При ее нажатии открывается меню.

Меню условного форматирования

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

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

Все сценарии разбиты на категории:

– Правило выделения ячеек

– Правило отбора первых и последних значений

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

Правила выделения ячеек в условном форматировании

Больше… Если значение ячейки, к которой применяется правило выделения, больше указанного значения, то в силу вступает заданный формат.

Больше.

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

Меньше… Форматируются ячейки, у которых значение меньше заданного порога.

Меньше.

Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.

Между.

Равно… если значение или текст в ячейке совпадает с условием.

Равно.

Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).

Текст содержит…

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

Дата.

Повторяющиеся значения… выделяются ячейки с одинаковым содержимым. Отличный способ найти дубликаты (повторы). В настройках можно выбрать и обратный вариант – выделить только уникальные значения.

Повторяющиеся значения…

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

Правила отбора первых и последних значений

Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).

Первые 10 элементов…

Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

Первые 10%.

Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.

Последние 10 элементов…

Последние 10%… Наименьшие 10% или другая доля от всех элементов.

Последние 10%.

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

Выше среднего…

Ниже среднего… Ниже средней арифметической.

Ниже среднего…

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

Гистограммы

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

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

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

Цветные шкалы

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

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

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

Набор значков

В ячейках Excel выглядит так.

Набор значков в условном форматировании

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

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

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

Диалоговое окно управление правилами

После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.

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

Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.

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

Удаление условного форматирования

Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.

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

Неправильный формат ячеек

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

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

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

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

Правила выделения ячеек

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

  1. Выделите группу ячеек, к которой хотите применить правило, разверните меню «Условное форматирование» и наведите курсор на «Правила выделения ячеек». Названия всех правил соответствуют их действию. Например, при выборе «Больше» правило затронет только те клетки, значение в которых будет больше указанного. Точно так же работают и остальные варианты. Выбор первого правила для условного форматирования в Microsoft Excel
  2. После выбора укажите само число, от которого и должно отталкиваться правило. Условия для первого правила для условного форматирования в Microsoft Excel
  3. Затем разверните список с вариантами подсветок и выберите подходящую. Если среди них нет подходящего цвета, всегда можно нажать на «Пользовательский формат» и выбрать другую заливку или цвет текста. Выбор типа оформления первого правила для условного форматирования в Microsoft Excel
  4. Как только изменения вступают в силу, вы сразу же видите результат условного форматирования. При этом правило имеет динамический режим работы, то есть при изменении значения в ячейке изменится и ее подсветка (если она подпадает под действие правила). Просмотр оформления первого правила для условного форматирования в Microsoft Excel
  5. Если в меню выбора типа правила указать вариант «Другие правила», откроется новое окно, в котором вы можете более детально разобраться с форматированием, выбрать подходящий тип и задать условие. При этом ниже сразу же отобразится образец, от которого стоит отталкиваться при настройке. Ручная настройка первого правила для условного форматирования в Microsoft Excel

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

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Правила отбора первых и последних значений

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

  1. Для этого в том же меню условного форматирования наведите курсор на второе правило и ознакомьтесь с его типами. В предыдущем абзаце я уже перечислил их все, поэтому вам остается выбрать только подходящее. Уточню, что под «Первые 10 элементов» подразумевается не их порядок в таблице, а сам результат, записанный в ячейке. Выбор второго правила для условного форматирования в Microsoft Excel
  2. Хоть правила и называются «Первые 10…», «Последние 10…», количество ячеек для форматирования вы выбираете самостоятельно, указывая подходящий номер в счетчике. После этого нужно развернуть список с вариантами оформления и выбрать среди них подходящий. Параметры второго правила для условного форматирования в Microsoft Excel
  3. Я настроил подсветку первых шести элементов, а это значит, что теперь в моем диапазоне будут подсвечены первые 6 самых больших чисел. Результат оформления второго правила для условного форматирования в Microsoft Excel
  4. Вариант «Выше среднего» или «Ниже среднего» не имеет дополнительных настроек, поскольку среднее число из диапазона определяется автоматически. Вам остается выбрать только стиль форматирования. Ручная настройка второго правила для условного форматирования в Microsoft Excel
  5. Как видно на следующем изображении, программа сама определила, какие значения выше среднего в диапазоне, и подсветила их выбранным мною цветом. Второй результат оформления для условного форматирования в Microsoft Excel

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

Гистограммы

Рассмотрим следующее правило под названием «Гистограммы». Оно имеет два разных типа, обеспечивающих градиентную или сплошную заливку. Гистограммы появятся на всех ячейках, но их размер напрямую будет зависеть от величины значения в диапазоне.

  1. Наведите курсор на правило «Гистограммы» и выберите подходящий тип оформления. По умолчанию предлагается 12 вариантов. Выбор третьего правила для условного форматирования в Microsoft Excel
  2. Никаких дополнительных настроек это правило не имеет, поэтому после применения вы сразу видите сформированные гистограммы – от минимального к максимальному значению диапазона. Результат применения третьего правила для условного форматирования в Microsoft Excel
  3. Если выбрать вариант «Другое правило», то вы самостоятельно сможете настроить, какие значения стоит задеть и какую заливку к ним применить. Настройки интуитивно понятны, поэтому не стесняйтесь и экспериментируйте с различными стилями оформления. Ручная настройка гистограммы для условного форматирования в Microsoft Excel

Цветовые шкалы

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

  1. Откройте список всех типов цветовых шкал и выберите среди них подходящий цветовой градиент. Выбор градиентного цвета для условного форматирования в Microsoft Excel
  2. После его применения вы сразу увидите результат. Я выбрал тип условного форматирования – от зеленого к красному цвету. Зеленым подсвечиваются минимальные значения, а красным – максимальные. Соответственно, чем выше число, тем краснее будет цвет, начиная от классического зеленого и переходя к салатовому, желтому и так далее. Результат применения градиента цвета для условного форматирования в Microsoft Excel
  3. На предложенных градиентах варианты оформления не заканчиваются. Вы можете открыть уже знакомое окно с другими правилами, самостоятельно выбрать шкалу и цвета для нее. Ручная настройка градиента для условного форматирования в Microsoft Excel

Наборы значков

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

  1. Откройте наборы значков и ознакомьтесь со всеми доступными. Присутствуют различные направляющие в виде стрелок, фигуры разных цветов, индикаторы и оценки. Выбор значков для условного форматирования в Microsoft Excel
  2. Настроек для этого правила по умолчанию нет, поэтому при выборе значки сразу же появляются в своих клетках. Я выбрал шкалу с несколькими делениями, которые заполняются в зависимости от величины значения в текущем диапазоне. Вы же можете протестировать абсолютно все наборы значков. Применение значков для условного форматирования в Microsoft Excel
  3. В окне настройки данного правила обязательно укажите тип, а затем выберите любой из типов значков и условия, при которых они будут появляться. Это позволит игнорировать некоторые значения, которые вы бы не хотели подсвечивать. Ручная настройка значков для условного форматирования в Microsoft Excel

Создание своего правила

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

  1. Разверните «Условное форматирование» и выберите «Создать правило». Переход к ручному созданию правила для условного форматирования в Microsoft Excel
  2. Выберите из списка тип правила под ваши условия. Для этого достаточно ознакомиться с его описанием, чтобы найти подходящее. Ручное создание правила для условного форматирования в Microsoft Excel
  3. Затем посмотрите на второй блок с настройками и настройте само условие форматирования. Например, можно указать значение между конкретными числами. Настройка условия для правила для условного форматирования в Microsoft Excel
  4. Для определенных типов форматирования поддерживаются настройки шкалы, градиентов и гистограмм точно в таком же виде, как это было рассмотрено выше. Ручная настройка градиента при создании правила для условного форматирования в Microsoft Excel
  5. Примените свое правило и посмотрите, как теперь отображаются выбранные ячейки. Измените значения в некоторых из них для проверки динамики. В любой момент можно вернуться к настройкам и поменять тип условного форматирования. Применение вручную созданного правила для условного форматирования в Microsoft Excel

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *