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


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

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

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

Формулы в качестве критериев можно использовать практически любые, которые возвращают значения. Но для логических формул (возвращающие значения ИСТИННА или ЛОЖЬ) в Excel предусмотрен отдельный тип правил.

При использовании логических формул в качестве критериев следует:

  1. Выбрать инструмент: «Главная»-«Стили»-«Условное форматирование»-«Управление правилами».
  2. В появившемся окне «Диспетчер правил условного форматирования» нажать на кнопку «Создать правило».
  3. В списке опций «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматируемых ячеек».
  4. В поле ввода «Форматировать значения, для которых следующая формула является истинной» ввести логическую формула, а нажав на кнопку «Формат» указать стиль оформления ячеек.


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

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

  1. Нельзя ссылаться на данные в других листах или книгах. Но можно ссылаться на имена диапазонов (так же в других листах и книгах), что позволяет обойти данное ограничение.
  2. Существенное значение имеет тип ссылок в аргументах формул. Следует использовать абсолютные ссылки (например, =СУММ($A$1:$A$5) на ячейки вне диапазона условного форматирования. А если нужно ссылаться на несколько ячеек непосредственно внутри диапазона, тогда следует использовать смешанные типы ссылок (например, A$1).
  3. Если в критериях формула возвращает дату или время, то ее результат вычисления будет восприниматься как число. Ведь даты это те же целые числа (например, 01.01.1900 – это число 1 и т.д.). А время это дробные значения части от целых суток (например, 23:15 – это число 0,96875).

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

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

exceltable.com

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

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

Совет: Вы можете отсортировать ячейки, имеющие этот формат, по цвету - просто используйте контекстное меню.

Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения, отличного от ошибки.

Быстрое форматирование

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

  2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Цветовые шкалы.

  3. Выберите двухцветную шкалу.

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

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

Расширенное форматирование

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

  2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.

  3. Выполните одно из указанных ниже действий.

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

    • Для изменения условного форматирования выполните указанные ниже действия.

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

      2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.

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

  4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

    • к выделенным ячейкам, чтобы выбрать ячейки по выделению;

    • ко всем ячейкам, содержащим значения <метка значения>, чтобы выбрать ячейки с определенной меткой.

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

  5. В разделе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений (по умолчанию).

  6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала.

  7. В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.

    • Форматирование минимального и максимального значений.    Выберите минимальное значение и максимальное значение.

      В этом случае не вводите минимальное и максимальноезначения.

    • Форматирование числового значения, значения даты или времени.    Выберите элемент Число, а затем введите минимальное и максимальноезначения.

    • Форматирование процентного значения.    Введите минимальное и максимальноезначения.

      Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

    • Форматирование процентиля.    Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100.

      Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) с одним оттенком, а группу низких значений (например, нижнюю 20ю процентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

    • Форматирование результата формулы.    Выберите элемент Формула, а затем введите минимальное и максимальное значения.

      • Формула должна возвращать число, дату или время.

      • Начинайте ввод формулы со знака равенства (=).

      • Недопустимая формула не позволит применить форматирование.

      • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

        Примечания: 

        • Убедитесь, что минимальное значение меньше, чем максимальное.

        • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

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

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

support.office.com

Обучение условному форматированию в Excel с примерами

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.



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

Сравним значения диапазона А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. Откройте меню инструмента, нажмите «Управление правилами».

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

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

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

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

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

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

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

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

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

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

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

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

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

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

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

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

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

exceltable.com

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

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А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. Откройте меню инструмента, нажмите «Управление правилами».

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

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

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» — «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

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

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

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

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

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

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

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

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

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

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

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

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

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

Все очень просто. Трюки в эксель.. Условное форматирование 3

" data-medium-file="https://i0.wp.com/vseochenprosto.ru/wp-content/uploads/2017/03/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3Urok.png?fit=300%2C300&ssl=1" data-large-file="https://i0.wp.com/vseochenprosto.ru/wp-content/uploads/2017/03/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3Urok.png?fit=300%2C300&ssl=1" />Я снова рад приветствовать Вас уважаемые коллеги на занятиях по изучению огромных возможностей Exсel. Сегодня мы продолжим изучение функций условного форматирования и рассмотрим Условное форматирование при нескольких условиях. Создать правило. Условное форматирование строки по значению ячейки. Информационное окно. В имеющейся базе данных нам к примеру, необходимо выбрать числа, значение которых превышают три определенных порога (10, 20, 40). Для примера используем имеющийся у нас исходный диапазон чисел A1:A15, выделяем его, определяемся с условиями выделения информации (желтый цвет–числа > 10, зеленый цвет– числа > 20, красный цвет– числа > 40) и задаем правило форматирования. И так:

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

Рассмотрим несколько вариантов решения данной задачи.

1 вариант

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

Все очень просто. Условное форматирование 3

В выпавшем окне в левом поле задаем параметр, «10», а в правом, выбираем способ отображения данных в которых значение, больше установленного нами числа (желтый цвет)

Все очень просто. Условное форматирование 3.15

Нажимаем кнопку «ok» и повторяем эту процедуру для другого порога «20», только цвет выбираем (зеленый). И то же самое проделываем для третьего правила числа «40» и цвет естественно выбираем (красный)В результате получаем таблицу с выделенными данными по заданным нами условиям форматирования

Все очень просто. Условное форматирование 3.16

Попробуйте применить эту функцию в копиях своих наработок, кстати, параметры отображения (способы заливки, выбор цвета) Вы можете определять сами. Вот один из вариантов:

Все очень просто. Условное форматирование 3.1

И их очень много. Как это сделать? я обязательно расскажу в одном из следующих уроков.

2 вариант

Имея выделенный диапазон,заходим в меню «Условное форматирование» и выбираем «создать правило»

Все очень просто. Условное форматирование 3.3

В выпавшем окне, в разделе «выбор типа правил» выбираем «Использовать формулу для определения форматируемых ячеек».

Все очень просто. Условное форматирование 3.17

В разделе «измените описание правил» в строке формул ставим относительную или смешанную ссылку, как описано в предыдущем уроке (см. тут) или прописываем вручную, как показано в примере:

Все очень просто. Условное форматирование 3.18

Затем после относительной ссылки в формулу ставим знак «>» и цифру «10», чуть ниже нажимаем кнопку «формат», для выбора палитры отображения (в нашем случае «желтый цвет». Нажимаем «ok»

Все очень просто. Условное форматирование 3.19

Далее проделываем те же операции для значения «20» и выбираем естественно «зеленый» цвет И далее задаем правило для последнего значения, числа «40» и выбираем красный цвет. Вот и все

Все очень просто. Условное форматирование 3.2

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

Создать правило

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

Все очень просто. Условное форматирование 3.20

В нижней части «измените описание правила» мы можем выбрать стиль формата, значение (условие) и конечно палитру (цвет). Хочу сразу сказать, что содержание нижней части окна зависит от выбранного типа правил. Полистайте и по изучайте предлагаемые варианты. На изученных нами примерах или на каких-то своих наработках попробуйте применение стилей, поэкспериментируйте со значениями и вариантами отображения информации.

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

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

Все очень просто. Условное форматирование 3.4

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

Все очень просто. Условное форматирование 3.5

К примеру (на текущей недели). Жмем «ok» и получаем вот такой результат

Все очень просто. Условное форматирование 3.21

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

Все очень просто. Условное форматирование 3.22

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

Условием для данной функции УФ является выделение строки по определенному значению в ячейке. Для примера возьмем какую ни будь таблицу с данными (в нашем случае я буду использовать сведения по ремонтам оборудования)

Все очень просто. Условное форматирование 3.23

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

Все очень просто. Условное форматирование 3.26

В выпавшем меню выбираем тип правила (кликаем 1 раз ЛКМ по названию правила) – «использовать формулу для определения форматируемых ячеек» и в строке для формул забиваем логическую функцию, как показано на примере.

Все очень просто. Условное форматирование 3.24

Далее задаем формат отображения и жмем «ok» И обратите внимание, что ссылка на строку у нас в формуле – абсолютная, а ссылка на ячейку – смешанная. Затем по тому-же принципу задаем правило для не завершенных ремонтов и цвет выбираем «красный». И в «диспетчере правил условного форматирования» это выглядит так:

Все очень просто. Условное форматирование 3.27

А наша таблица будет иметь вот такой вид:

Все очень просто. Условное форматирование 3.25

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

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

Простой пример

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

Все очень просто. Условное форматирование 3.8

Нажимаем «ok» и результат должен быть таким:

Все очень просто. Условное форматирование 3.6

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

Информационное окно

Использовать будем все тот же диапазон чисел. К примеру, колонка с итоговыми цифрами (в нашем случае диапазон A1:A15) имеет постоянно меняющиеся данные в которых мы должны контролировать определенные параметры. Зададим сразу предельные значения: до 30 – это «норма», свыше 30 необходимо взять под «внимание», ну а свыше 40 – это «перебор» или «сверх нормы», а теперь в ячейке B1 прописываем вот такую формулу: =ЕСЛИ(A1>40;»сверхнормы»;ЕСЛИ(A1>30;»внимание»;ЕСЛИ(A1 Все очень просто. Условное форматирование 3.9

" data-medium-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=300%2C169&ssl=1" data-large-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=1024%2C576&ssl=1" /> Нажимаем «Enter» и в окне B1 должны в нашем случае увидеть «норма»,

Все очень просто. Условное форматирование 3.10

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

Все очень просто. Условное форматирование 3.11

Затем для каждого из предельных значений мы должны создать правило условного форматирования, порядок таков: Выделяем диапазоне B1:B15, в меню «условного форматирования» выбираем «создать правило».

Все очень просто. Условное форматирование 3.12

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

Все очень просто. Условное форматирование 3.14

Затем повторяем эту операцию для значения «внимание» и «сверх нормы» и в диспетчере задач мы должны получить вот такую картину:

Все очень просто. Условное форматирование 3.13

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

С уважением

Спасибо Вам, что поделились статьей в социальных сетях!

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

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

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

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

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

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

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

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

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

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

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

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

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:
  • Также, доступны следующие условия:

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

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

    • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Для редактирования присвоенного правила выполните следующие шаги:

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

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

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

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

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

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

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

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

    Рекомендуем к прочтению

    web-shpargalka.ru

    Применение условного форматирования с помощью формулы в 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" указывают, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.

    6. В поле Форматировать с помощью выберите пользовательский формат.

    7. В верхней части окна откройте вкладку Шрифт.

    8. В раскрывающемся списке Цвет выберите Белый. В списке раздела Начертание выберите Полужирный.

    9. В верхней части окна откройте вкладку Заливка и для параметра Цвет фона выберите значение Зеленый.

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

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

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

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

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

    =СЧЁТЕСЛИ($D$2:$D$11;D2)>1

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

    Имя

    Фамилия

    Телефон

    Город

    Юлия

    Ильина

    555-1213

    Москва

    Сергей

    Климов

    555-1214

    Электросталь

    Вадим

    Корепин

    555-1215

    Мурманск

    Андрей

    Гладких

    555-1216

    Дубна

    Станислав

    Песоцкий

    555-1217

    Коломна

    Ольга

    Костерина

    555-1218

    Мурманск

    Евгений

    Куликов

    555-1219

    Верея

    Николай

    Новиков

    555-1220

    Домодедово

    Светлана

    Омельченко

    555-1221

    Москва

    Инна

    Ожогина

    555-1222

    Электрогорск

    support.office.com

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

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

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

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

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

    1. Выделите целевой диапазон ячеек (в данном примере $D$2:$D$13) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Появится окно «Создание правила условного форматирования» как показано ниже на рисунке:
    2. На из списка в верхней части окна выберите опцию «Использовать формулу для определения форматируемых ячеек». Данная опция служит для преобразования форматов с помощью определенной формулы. Если в результате просмотра значений ячеек формулой, одно из них возвратит логическое значение ИСТИНА, тогда в данной ячейке будет применено условное форматирование.
    3. В поле ввода формул введите логическое выражение представленное на данном шаге. Обратите внимание, что данная формула просто сравнивает значения целевой ячейки D2 со значением искомой ячейкой по адресу $A$2. Подобно как в случае со стандартными формулами, нужно убедиться, чтобы была абсолютная ссылка на искомую ячейку, благодаря которой значение каждой ячейки в выделенном диапазоне будет сравниваться со значением искомой <$A$2:

      =D2<$A$2

      Обратите внимание, что в ссылке на первую целевую ячейку D2 нет знаков доллара ($) в выше приведенной формуле. Если не ввести адрес вручную, а только лишь кликнуть по ячейке D2, то Excel автоматически создаст абсолютную ссылку =$D$2. Важно, чтобы не было символов доллара в ссылках на ячейку, так правило форматирования сможет быть применено отдельно для каждой ячейки в диапазоне $D$2:$D$13.
    4. Щелкните на кнопку «Формат» и появится окно «Формат ячеек», в котором находятся все опции для форматирования шрифтов, границ и заливки ячеек. После указания необходимых опций форматирования подтвердите их нажатием на кнопку «ОК» на всех открытых окнах.
    

    В результате мы получили динамически изменяемый отчет по условию, указанному в формуле которое пользователь сам может изменить в ячейке $A$2:

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

    exceltable.com

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

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

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

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

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

  • Выделите необходимый диапазон ячеек (в данном примере это B2:B13) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Появится окно «Создание правила»:
  • В списке опций верхней части окна выберите опцию: «Использовать формулу для определения форматируемых ячеек». Данная опция служит для преобразования форматов с помощью определенной формулы. Если в результате обработки одного из значений формула будет возвращать логическое значение ИСТИНА, тогда в данной ячейке будет применено условное форматирование.
  • В поле для ввода формулы введите ниже приведенное логическое выражение. Нет необходимости в нем указывать ссылку на целый диапазон:

    =B2<4000

    Обратите внимание, что в этой формуле нет символа доллара ($) в ссылках на целевую ячейку (C3). Если не вводить ссылку вручную, а только кликнуть по ячейке C3, то Excel автоматически создаст абсолютную ссылку на ячейку. В данном случае важно, чтобы не указывать символ доллара в относительной ссылке на ячейку, чтобы правило форматирования могло быть применено для каждого значения текущей ячейки индивидуально.
  • Щелкните на кнопку «Формат». Появится окно «Формат ячеек», в котором находятся все опции форматирования шрифтов, границ и заливок ячеек. После выбора необходимой опции форматирования щелкните на кнопку «ОК», чтобы подтвердить изменения и вернутся к окну «Создание правила форматирования». Чтобы там также нажать на кнопку «ОК» и применить условное форматирование для выделенных ячеек диапазона B2:B13.
  • В результате автоматически отформатировались все значения по условию <4000.

    

    Изменение правил условий в условном форматировании Excel

    Если нужно редактировать правило условного форматирования, просто выделите любую ячейку на которую распространяется это правило динамического формата, а потом выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». Появится окно «Диспетчер правил условного форматирования». Выберите правило, которое необходимо изменить, после чего щелкните на кнопку «Изменить правило».

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

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

    exceltable.com

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

    Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

    Предыдущая глава                          Оглавление                               Следующая глава

    Несколько важных фактов об условном форматировании на основе формул:

    • Проверка условия возвращает значения ИСТИНА/ЛОЖЬ и применяется к диапазону ячеек.
    • Условие проверяется для каждой ячейки в диапазоне. Если условие выполнено, ячейка получает форматирование. Если условие не выполнено, ячейка не форматируется.
    • Для создания условия подходит любая формула, возвращающая значения ИСТИНА/ЛОЖЬ (если формула возвращает число, то любое число, кроме нуля приравнивается к значению ИСТИНА, а значение ноль – ЛОЖЬ).
    • Условное форматирование можно использовать, как с обычными формулами, так и с формулами массива.
    • Условное форматирование является волатильным (летучим): оно пересчитывается довольно часто, даже, когда вы напрямую не задействуете диапазон, в отношении которого применено условное форматирование (см. главу 13). Это замедляет работу.

    Скачать заметку в формате Word или pdf, примеры в формате Excel

    Чтобы создать условное форматирование с помощью формул:

    1. Выделите диапазон ячеек. Обратите внимание, активна левая верхняя ячейка диапазона (она светлее других).
    2. Пройдите по меню ГЛАВНАЯ –> Условное форматирование –> Создать правило, или нажав, и удерживая клавишу Alt, последовательно нажмите Я, Л, С. Откроется диалоговое окно Создание правила форматирования.
    3. Выберите опцию Использовать формулу для определения форматируемых ячеек.
    4. Поместите курсор в поле Форматировать значения, для которых следующая формула является истинной.
    5. Наберите формулу, которая будет применяться к активной ячейке выделенного диапазона. Примените абсолютные и относительные ссылки, как если бы вы копировали формулу на весь диапазон (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Помните, что условие, которое вы создаете, будет оцениваться отдельно для каждой ячейки, чтобы определить, должна ли эта ячейка получить форматирование.
    6. Нажмите кнопку Формат и выберите любую комбинацию форматирования на четырех вкладках: Число, Шрифт, Граница и Заливка.
    7. Нажмите несколько раз кнопку ОК, закрывая все диалоговые окна.

    Рассмотрим два примера условного форматирования на основе формул массива. Первый пример (рис. 20.1) показывает, как выделить строку, содержащую наименьшее значение для города, введенного в ячейку A11. Выделимте диапазон А3:В8 и откройте диалоговое окно Создание правила форматирования, как описано выше. (Если вы выполняете пример в приложенном файле Excel, у вас откроется окно Изменение правила форматирования, так как правило уже создано). Обратите внимание на смешанную ссылку на ячейку $В3 в формуле. Она позволяет каждой ячейки в диапазоне при поиске минимального значения для города, оставаться в столбце В. Обратите внимание, что формула массива возвращает одно и тоже значение для каждой ячейки в диапазоне. Но только в ячейках А5 и В5 это значение равно проверяемому. Если ячеек много волатильный характер вычислений может замедлить работу. Второй пример (рис. 20.2), обходит эту трудность, используя вспомогательную ячейку.

    Рис. 20.1. Формула массива в диалоговом окне Изменение правила форматирования; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

    Цель форматирования (рис. 20.2) – выделить строки с N лучшими результатами гонщика, указанного в ячейке А17. Число N указано в ячейке В17. Формулы массива, вычисляющие второе лучшее время для гонщика, приведены в ячейках D18 и D19 (используйте любую из них). Формула =И($A3=$A$17;$B3<=$D$18) проверяет два условия: равно ли имя гонщика выбранному критерию (в ячейке А17), и входит ли время в два наилучших результата (т.е., меньше или равно значению в D18). Вспомогательная ячейке (D18 или D19) предотвращают многократное повторение расчетов этого времени внутри формулы условного форматирования.

    Рис. 20.2. Вспомогательная ячейка избавляет от многократного расчета формулы массива

    baguzin.ru

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

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

    ​Смотрите также​ теперь другой вопрос,​ потом щелкните по​ распространяется на разный​ 21000. Чтобы добавить​ выполнил месячный план​ (например, 23:15 –​В появившемся окне «Диспетчер​ нескольких простых действий.​нажмите кнопку​ положительных​

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

    ​ последних значений​Сортировка​ применения условного форматирования.​Куликов​.​.​Условное форматирование позволяет быстро​

    ​ возможно ли мне​ соответствующей ячейке. Excel​ диапазон чисел.​ второе условие нет​ > 25000. Для​ это число 0,96875).​ правил условного форматирования»​Выделите ячейки, которые нужно​Условное форматирование​, нейтральных​ >​.​В этой статье мы​

    ​555-1219​

    1. ​Теперь форматирование применено к​Функция СЕГОДНЯ используется в​ выделить на листе​ человеку не сведующему​ вставит адрес ссылки​Примечание. Критериями условного форматирования​

    2. ​ необходимости удалять первое​​ этого:​​Иногда встроенные условия формата​​ нажать на кнопку​​ отформатировать.​​и выберите пункт​​и отрицательных​

    3. ​Первые 10 элементов​​В раскрывающемся списке​​ продемонстрируем несколько способов​​Верея​ столбцу C.​​ формуле для определения​

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

      ​ «Создать правило».​На вкладке​Цветовые шкалы​значков.​.​Сортировать по​ применения готовых правил​Николай​

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

    6. ​ под это дело​​Обратите внимание, что такой​​ быть только числа.​​ сначала. Делаем так:​​ содержит суммы продаж​​ всех потребностей пользователей.​​В списке опций «Выберите​​Главная​​. Затем выберите шкалу.​

    7. ​Чтобы узнать, как это​​Затем вы можете настроить​​выберите столбец​ условного форматирования. Существуют​Новиков​

      ​ ниже таблицу на​ столбце A, превышающих​

    ​ форматирования недостаточно. Создав​

    1. ​ макрос?​ метод значительно эффективней,​

    2. ​ Успешно можно его​Снова выделите диапазон ячеек​ (C2:C10).​​ Добавление собственной формулы​​ тип правила:» выберите​

      ​нажмите кнопку​ В данном случае​ сделать, выполните указанные​ количество элементов и​ФИО сотрудника​ тысячи возможных комбинаций,​555-1220​ лист в Excel​

    3. ​ значение сегодняшней даты​​ собственную формулу для​​wadya35​​ если критерии должны​​ применять на текстовые​​ C2:C10 (несмотря на​​Выберите инструмент: «Главная»-«Условное форматирование»-«Правила​​ в условное форматирование​​ опцию «Использовать формулу​

    4. ​Условное форматирование​​ мы использовали вариант​​ ниже действия.​​ цвет заполнения.​​, в раскрывающемся списке​

      ​ поэтому смело экспериментируйте,​Домодедово​

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

    ​ — обязательно вставьте​ (будущих дат). Ячейки,​ правила условного форматирования,​: Зачем?​ изменяться. Фактически вы​ значения ячеек Excel.​ то, что он​ выделения ячеек»-«Больше».​ обеспечивает дополнительную функциональность,​ для определения форматируемых​и выберите пункт​

    ​Цветовая шкала "Красный-желтый-зеленый"​

    ​Выделите диапазон, который хотите​Если вы применяете условное​Сортировка​ чтобы найти наилучший​Светлана​ ее в ячейку​ удовлетворяющие этому условию,​ вы сможете выполнять​Включите запись макроса​ теперь можете управлять​ Например, проверка с​ уже отформатирован в​В появившемся окне введите​

    ​ которая не доступна​

    ​ ячеек».​

    ​Создать правило​

    ​.​

    ​ отформатировать.​

    ​ форматирование с использованием​

    ​ — значение "Цвет ячейки",​

    ​ вариант. Но при​

    ​Омельченко​

    ​ A1. Затем выделите​

    ​ форматируются.​

    ​ действия, которые не​

    ​ на вкладке Разработчик​

    ​ ими без открытия​

    ​ помощью оператора равенства,​

    ​ соответствии с условиями).​

    ​ в поле значение​

    ​ в строенных функциях​

    ​В поле ввода «Форматировать​

    ​.​

    ​Если вы хотите применить​

    ​На вкладке​

    ​гистограмм​

    ​ в раскрывающемся списке​

    ​ этом не забывайте​

    ​555-1221​

    ​ ячейки D2: D11​

    ​Нажмите кнопку​

    ​ под силу встроенным​

    ​wadya35​

    ​ диалоговых окон и​

    ​ соответствует ли данный​

    ​Выберите инструмент: «Главная»-«Условное форматирование»-«Правила​

    ​ 25000 и выберите​

    ​ данного инструмента. Excel​

    ​ значения, для которых​

    ​Создайте правило и задайте​

    ​ форматирование к каждой​

    ​Главная​

    ​, Excel отображает в​

    ​Порядок​

    ​ о своей аудитории,​

    ​Москва​

    ​ и создайте правило​

    support.office.com>

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

    ​Формат​ правилам.​: А там запись​ лишней суеты. К​ текст тексту, указанному​ выделения ячеек»-«Меньше».​ из выпадающего списка​ предоставляет возможность использовать​ следующая формула является​ для него параметры​ второй строке данных,​нажмите кнопку​ каждой ячейке полосу,​ — цвет, а в​ чтобы слишком сложное​Инна​ условного форматирования, в​.​Предположим, что вы следите​ оказывается есть! А​ тому же все​ в критериях. Или​

    ​В появившемся окне введите​

    ​ «Зеленая заливка и​ много критериев или​

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

    my-excel.ru

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

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

    • поля данных;
    • цветовая палитра;
    • настройка шрифтов.

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

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

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

    Допустим столбец, содержит диапазон ячеек с числовыми значениями. Если определить их соответствующим условием форматирования, то все значения с числом более чем 100 будут отображаться красным цветом. Чтобы реализовать эту задачу данный инструмент Excel проанализирует в соответствии с условиями критериев значение каждой ячейки заданного диапазона. Результаты анализа дают положительный результат, например (A2>100=ИСТИНА), тогда будет присвоен предварительно заданный новый формат (красный цвет). В противоположном результате (A2>100=ЛОЖЬ), формат ячеек не изменяется.

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

    

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

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

    Допустим в ячейке A1 находиться числовое значение 50:

    Определим следующие условия для формата отображения значений в A1:

    1. Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
    2. Если число больше чем 30, то шрифт будет отображаться желтым цветом.
    3. Если число больше чем 40, то шрифт будет отображаться красным.

    Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

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

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

    Создать второе правило

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

    Все суммы в пределах 300$-600$ должны закрасить свои ячейки желтым фоном, а суммы меньше чем 500$ должны еще иметь и красный цвет шрифта.

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

    1. Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
    2. В появившемся окне нажмите на кнопку «Создать правило».
    3. В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
    4. В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.
    5. Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
    6. Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
    7. Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
    8. Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
    9. Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
    10. На всех окнах нажмите «ОК».

    Обратите внимание, в какой способ Excel применил форматирование. Суммы в ячейках C10, С13 и С15 соответствуют обоим условиям. Поэтому к ним применены оба стиля форматирования. А там где значение соответствует только одному из условий, они отображаются соответственным форматам.

    exceltable.com

    Как выделить наименьшее и наибольшее значение в Excel цветом

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

    Как выделить цветом максимальное значение в Excel

    Для примера возьмем таблицу расходов:

    Чтобы молниеносно выделить наибольшие и наименьшие расходы делаем следующее:

    1. Выделите диапазон с расходами B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
    2. Выберите: «Использовать формулу для определения форматируемых ячеек».
    3. В поле ввода введите формулу: =МАКС($B$2:$B$10)=B2
    4. Нажмите на кнопку «Формат», чтобы выделить максимальное значение в столбце Excel. Для этого в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите желаемый цвет заливки для ячеек. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

    Как выбрать минимальное значение в Excel

    Чтобы выделить цветом минимальное значение в Excel, повторите весь порядок действий, описанных выше. Только вместо функции МАКС, должна быть функция МИН. Данная функция позволяет выбрать наименьшее значение в таблице Excel. А вместо зеленого цвета заливки выберите красный цвет.

    К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

    Финальный эффект применения двух правил условного форматирования для выделения цветом наибольшего и наименьшего значения столбца B (Расходы):

    Принцип действий обоих правил одинаковый. Они отличаются только функциями =МАКС() и =МИН(). Более того аргументы функций идентичны. Первая функция =МАКС() ищет в несменяемом диапазоне с абсолютными ссылками $B$2:$B$10 максимальное значение. После функции стоит оператор сравнения с изменяемой текущей ячейкой которая имеет относительную ссылку начиная от B2. Правило условного форматирования читает данную формулу так: если число больше всех, то выделить цветом. Как только совпадает максимальное число (которое возвращает функция МАКС) со значением текущей ячейки, формула возвращает логическое значение ИСТИНА и сразу же применяется формат со соответствующим цветом заливки. Так же срабатывает правило для умного форматирования ячеек столбца и с функцией МИН.

    

    Выделение цветом трех наименьших значений подряд

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

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

    Чтобы добиться данного эффекта следует выполнить следующие действия:

    1. Выделите диапазон ячеек B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
    2. В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.
    3. Не снимая выделения с диапазона B2:B9 сразу же создаем второе правило условного форматирования. Снова выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
    4. В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;2)=B2 и нажмите на кнопку «Формат», чтобы задать оранжевый цвет заливки для ячейки. И нажмите ОК на всех окнах.
    5. Создайте третье правило. А в поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;3)=B2 и нажмите на кнопку «Формат», чтобы задать желтый цвет заливки для ячейки. ОК на всех окнах.
    6. К одному и тому же диапазону должно быть применено 3 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

    Три наименьшие значения автоматически выделились разными цветами.

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

    Аналогичным способом можно выделять цветом наибольшие значения. Просто изменить в формуле функцию НАИМЕНЬШИЙ на НАИБОЛЬШИЙ. Например: =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2

    Функция наименьший и наибольший в Excel

    Функция =НАИМЕНЬШИЙ() ищет в заданном (в первом аргументе) диапазоне очередное наименьшее значение. Очередь указана во втором аргументе функции. То есть если во втором аргументе мы указали число 3, значит функция возвращает третье по очереди наименьшее значение заданного диапазона $B$2:$B$9. Если результат вычисления функции равен текущей ячейке значит к ней присваивается соответственный формат (цвет заливки). Важно отметить что в формуле для просмотра диапазона мы используем абсолютные адреса ссылки, а после оператора сравнения =B2 – относительные, так как значение функции сравнивается с каждой ячейкой выделенного диапазона. А функция НАИБОЛЬШИЙ работает обратно пропорционально.

    Полезный совет! Если нет необходимости разбивать 3 наименьших значения на разные цвета, тогда необязательно создавать 3 правила условного форматирования для одного и того же диапазона. Достаточно просто немного изменить формулу добавив всего один символ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То есть – больше или равно.

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

    exceltable.com


    Смотрите также



    © 2010- GutenBlog.ru Карта сайта, XML.