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

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

  • автор:

Обучение условному форматированию в 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. Больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

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

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

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

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

Диспетчер.

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

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

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

Диапазон дат.

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

Дата.

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

Перечень доступных условий.

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

Пример.

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

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

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

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

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

Образец.

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

Пример1.

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

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

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

Таблица.

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

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

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

Условия для завершенных проектов.

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

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

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

В Диспетчере.

Пример2.

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

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

Условное форматирование: инструмент Microsoft Excel для визуализации данных

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

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

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

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

  • Гистограммы;
  • Цифровые шкалы;
  • Значки.

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

Выбор гистограммы в Microsoft Excel

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

Гистограмма применена в Microsoft Excel

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

Использование цветовой шкалы в Microsoft Excel

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

Значки при условном форматировании в Microsoft Excel

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

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

Lumpics.ru

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

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

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

  • Больше;
  • Меньше;
  • Равно;
  • Между;
  • Дата;
  • Повторяющиеся значения.

Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».

Переход к правилу выделения ячеек в Microsoft Excel

Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.

Установка границы для выделения ячеек в Microsoft Excel

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

Выбор цвета выделения в Microsoft Excel

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

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

После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».

Сохранение результатов в Microsoft Excel

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

Ячейки выделены согласно правилу в Microsoft Excel

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

Другие варианты выделения в Microsoft Excel

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

Выделение текст содержит в Microsoft Excel

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

Выделение ячеек по дате в Microsoft Excel

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

Выделение повторяющихся значений в Microsoft Excel

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

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

  • Первые 10 элементов;
  • Первые 10%;
  • Последние 10 элементов;
  • Последние 10%;
  • Выше среднего;
  • Ниже среднего.

Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.

Установка правила отбора первых и последних ячеек в Microsoft Excel

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

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

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

Переход к созданию правила в Microsoft Excel

Открывается окно, где нужно выбрать один из шести типов правил:

  1. Форматировать все ячейки на основании их значений;
  2. Форматировать только ячейки, которые содержат;
  3. Форматировать только первые и последние значения;
  4. Форматировать только значения, которые находятся выше или ниже среднего;
  5. Форматировать только уникальные или повторяющиеся значения;
  6. Использовать формулу для определения форматируемых ячеек.

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

Опивание правила в Microsoft Excel

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

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

Переход к управлению правидлами в Microsoft Excel

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

Окно управления праилами в Microsoft Excel

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

Изменение порядка правил в Microsoft Excel

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

Оставить если истина в Microsoft Excel

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

Создание и изменение правила в Microsoft Excel

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Удаление правила в Microsoft Excel

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

Удаление правил вторым способом в Microsoft Excel

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

Условное форматирование в 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. Поэтому рекомендую незамедлительно внедрить в практику.

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

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

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

Условное форматирование в excel как это работает?

Всем радости на fast-wolker.ru! Сегодня мы познакомимся с такой функцией табличного редактора excel, как «условное форматирование». Что это такое и для чего оно нужно? Условное форматирование предназначено для анализа данных и наглядного представления готовых результатов.

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

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

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

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

Условное форматирование — если выполняется условие то нужные ячейки в таблице окрашиваются в нужный цвет

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

Выделяем столбец, который нам нужен, затем идем в пункт «Условное форматирование» и в открывшемся меню выбираем «правила выделения ячеек». Сбоку откроется еще вкладка. Где нам надо выбрать значение «больше» или «меньше». Поскольку нам надо меньшее, то выбираем это значение.

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

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

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

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

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

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

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

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

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

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

Выделение диапазонов несколькими цветами по условию

Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.

Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.

Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».

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

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

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

Как создать свое условие для форматирования столбцов?

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

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

Ниже увидим данные для изменения этого правила.

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

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

И в окнах рядом ставим между какими значениями делаем форматирование. Например, больше 45, но меньше 66.

Затем нажимаем на кнопку «форматировать» и в открывшемся окне выбираем графическое значение: цвет, шрифт и т.п.

После применения всех настроек жмем ОК. В результате получим подкрашенные значения в диапазоне от 45 до 66. Все остальные значения окажутся не подсвеченными.

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

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

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