Условное форматирование в MS Excel с примерами
Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.
Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.
Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.
Согласитесь, так гораздо нагляднее. В реальности условия сложнее, а данные постоянно меняются. Поэтому эффект от применения условного форматирования – это многочасовая экономия времени ежедневно! Теперь для оценки запасов достаточно взглянуть на таблицу, а не анализировать каждую ячейку. Много желтого – пора действовать, много красного – ситуация критическая!
Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.
При ее нажатии открывается меню.
Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.
В нижней части еще три команды, с помощью которых происходит ручное создание, удаление и управление правилами условного форматирования. О них также поговорим.
Все сценарии разбиты на категории:
– Правило выделения ячеек
– Правило отбора первых и последних значений
Правила выделения ячеек применяют для ячеек, которые сравниваются с определенным значением. Возможны различные варианты, которые показаны на рисунке ниже.
Больше… Если значение ячейки, к которой применяется правило выделения, больше указанного значения, то в силу вступает заданный формат.
Пороговое значение указывается в левой части окна (сейчас там 80), готовый формат выбирается из выпадающего списка справа. Можно, конечно, и самому задать. Диалоговые окна для других условий похожи, поэтому ниже приводятся только те, которые могут вызвать затруднения.
Меньше… Форматируются ячейки, у которых значение меньше заданного порога.
Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.
Равно… если значение или текст в ячейке совпадает с условием.
Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).
Дата… Возможность форматировать периоды отстоящие от текущей даты, например, сегодня, вчера, последние 7 дней, следующий месяц и др. Условное форматирование даты полезно при контроле платежей, отгрузок и т.п.
Повторяющиеся значения… выделяются ячейки с одинаковым содержимым. Отличный способ найти дубликаты (повторы). В настройках можно выбрать и обратный вариант – выделить только уникальные значения.
Правила отбора первых и последних значений выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».
Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).
Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.
Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.
Последние 10%… Наименьшие 10% или другая доля от всех элементов.
Выше среднего… Форматируются все значения, которые больше средней арифметической.
Ниже среднего… Ниже средней арифметической.
Гистограммы позволяют в каждую ячейку с числом добавить столбец линейной гистограммы, размер которой определяется относительно максимального значения в выделенном диапазоне.
Помогает визуализировать небольшой набор данных без использования отдельных диаграмм. После применения выглядит примерно так.
Цветные шкалы также автоматически определяют максимальное и минимальное значение в диапазоне и форматирует каждую ячейку по цвету, который соответствует значению, изображая что-то вроде тепловой карты.
Например, наибольшее значения – это красное, наименьшее – зеленое, а остальные ячейки – это плавный переход от одного цвета к другому через промежуточный белый.
Набор значков – эффектный, но не очень гибкий способ визуализации. Каждой ячейке присваивается свой значок в соответствии с выбранным стилем.
В ячейках Excel выглядит так.
Все картинки выше были сделаны с помощью стилей по умолчанию. Чтобы внести изменения, нужно выделить диапазон и перейти в управление правилами.
Откроется диалоговое окно, где можно создать новое, изменить или удалить правило. Часто используют сразу несколько правил.
После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.
Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.
Если какое-то правило условного форматирования нужно удалить, то после выделения диапазона следует выбрать команду удаления.
Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.
Хочется только напомнить, что при использовании любого форматирования очень важно не переусердствовать. Всегда нужно помнить о главной цели: облегчение восприятия информации и привлечение внимания к наиболее важным местам. Например, формат ниже – это неправильно.
Старайтесь также, чтобы количество используемых цветов было не больше трех. Иначе внимание рассеивается и может стать еще хуже.
Условное форматирование в Excel: ничего сложного
В Excel вы можете использовать условное форматирование для реорганизации содержимого ячеек по своему усмотрению. Например, можно отобразить совершенно другой текст или изменить шрифт. Мощный набор инструментов включает множество опций, а также предопределенные правила. В этой статье мы расскажем о наиболее важных типах правил условного форматирования и рассмотрим их на примерах.
Условное форматирование может сделать работу в MS Excel 2010 значительно удобнее. Собственно, затем оно и нужно. О самых популярных его функциях мы рассказываем в данной заметке.
Отформатируйте все ячейки на основе их значений
Это правило Excel предоставляет пользователю графические параметры, например, для визуального оформления числовых значений. Оно облегчит анализ ваших данных.
Двух- и трехцветная шкала в Excel
Вы можете добавить наглядности вашим данным с помощью градиента. Например, покажите по шкале от одного до десяти, насколько высоко излучение в определенной области.
Гистограммы
Вы также можете использовать гистограммы при форматировании. Например, это нужно, чтобы сделать график ваших расходов за месяц.
Символы
Наборы символов можно использовать, например, для ввода силы сигнала сети в процентах и тому подобных задач.
«Форматировать только ячейки, которые содержат…»
Этот тип форматирования особенно полезен для выделения отдельных специальных значений. Например, все числа ниже 0 могут быть отформатированы красным цветом.
Представить числовые данные в виде текста
Если у вас есть предопределенные повторяющиеся текстовые данные, этот тип форматирования идеален для них. Он не только экономит ваше время, но и предотвращает опечатки.
Используйте формулу, чтобы определить ячейки для форматирования
В этой статье показано лишь несколько вариантов условного форматирования. Функция может использоваться чрезвычайно универсально, и немного набив руку вы можете добиться отличных результатов.
Условное форматирование строки в Excel
Условное форматирование всей или части строки на листе Excel в зависимости от содержимого одной или более ячеек. Примеры условного форматирования.
Условное форматирование всей строки
Пример условного форматирования всей строки на листе Excel в зависимости от содержимого одной ячейки в этой строке.
Условие примера
Решение примера
1. Правило условного форматирования будет применено ко всему рабочему листу Excel, если перед его созданием выделить весь лист. Быстро это можно сделать, нажав на треугольник в верхнем левом углу на пересечении наименований строк и столбцов.
2. Нажимаем кнопку «Условное форматирование» на ленте инструментов «Главная» и выбираем ссылку «Создать правило…»:
3. В окне «Создание правила форматирования» выбираем строку «Использовать формулу для определения форматируемых ячеек»:
5. После выбора заливки и возврата к форме «Создание правила форматирования» нажимаем кнопку «OK».
7. Нажимаем кнопку «Условное форматирование» на ленте инструментов «Главная» и выбираем ссылку «Управление правилами…»:
8. В открывшемся окне «Диспетчер правил условного форматирования» можно просмотреть и отредактировать созданные правила:
9. Вводим в ячейки столбца «C» наименования цветов и смотрим результаты условного форматирования всей строки:
Форматирование части строки в Excel
Пример условного форматирования части строки на листе Excel в зависимости от содержимого одной или двух ячеек в этой строке.
Условие примера
Решение примера
1. Выделяем первые 5 столбцов, чтобы задать диапазон, к которому будут применяться создаваемые правила условного форматирования:
5. Проверяем созданные правила в «Диспетчере правил условного форматирования». Видим, что диапазоны, к которым применяются правила, отобразились верно:
6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строки:
Обратите внимание на то, что в диапазоне, к которому применяются правила условного форматирования, нельзя перетаскивать ячейки. Это нарушит его абсолютный адрес в правилах, и работать опция будет не так, как задумано. Копирование, вставка и копирование перетягиванием ячеек не влияют на абсолютный адрес диапазона.
Если все-таки абсолютный адрес диапазона нарушен, исправить его можно непосредственно в «Диспетчере правил условного форматирования».
Скачать файл Excel с примерами. На первом листе реализовано условное форматирование всей строки, на втором – ее части.
2 комментария для “Условное форматирование строки в Excel”
Здравствуйте, Игорь!
Выделите весь диапазон будущей таблицы, начиная с третьей строки, и создайте три правила условного форматирования.
Формула для серого цвета: =И($G3<>«»;$H3<>«»;$O3=»»;$N3=»»)
Формула для желтого цвета: =И($G3<>«»;$H3<>«»;$O3<>«»;$N3=»»)
Формула для зеленого цвета: =И($G3<>«»;$H3<>«»;$O3<>«»;$N3<>«»)
Как посмотреть условное форматирование ячейки
Применить условное форматирование для каждой строки в Excel
Как мы знаем, условное форматирование создаст правило, определяющее, какие ячейки будут отформатированы. Иногда вам может потребоваться применить условное форматирование для каждой строки, как показано на скриншоте ниже. За исключением многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи. 
Метод A Изменение применимости к в диспетчере правил условного форматирования
Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.
1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4. 
3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги. 
Теперь к строке A2: B2 применяется условное форматирование.
4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами. 
5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK. 
Затем это правило условного форматирования будет применяться к каждой строке в новом диапазоне. 
Метод B Перетащите маркер автозаполнения
Если вы используете Excel 2013 или более поздние версии, вы можете использовать дескриптор автозаполнения, чтобы применить условное правило к соседним строкам.
Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2
Теперь вы хотите применить эти два правила к A3: B9.
1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.
4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения. 
Файл примера
Другие операции (статьи), связанные с форматированием Conditioanl
Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.
создать диаграмму с условным форматированием в Excel
Например, у вас есть таблица оценок для класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах. В этом руководстве представлен метод решения этой задачи.
Гистограмма с накоплением условного форматирования в Excel
В этом руководстве показано, как создать столбчатую диаграмму с условным форматированием, как показано на скриншоте ниже, шаг за шагом в Excel.
Условное форматирование строк или ячеек, если два столбца равны в Excel
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
Поиск и выделение результатов поиска в Excel
В Excel вы можете использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух различных способах облегчения поиска и выделения результатов поиска в Excel.