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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
2. Чтобы создать новое правило форматирования, нажимаем Главная >Условное форматирование >Создать правило (Home > Conditional Formatting > New rule).
3. В появившемся диалоговом окне Создание правила форматирования (New Formatting Rule) выбираем вариант Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых следующая формула является истинной (Format values where this formula is true), вводим такое выражение:

Вместо C2 Вы можете ввести ссылку на другую ячейку Вашей таблицы, значение которой нужно использовать для проверки условия, а вместо 4 можете указать любое нужное число. Разумеется, в зависимости от поставленной задачи, Вы можете использовать операторы сравнения меньше ( =$C2
=$C2=4

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

Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.Таким же образом на остальных вкладках диалогового окна Формат ячеек (Format Cells) настраиваются другие параметры форматирования, такие как цвет шрифта или границы ячеек.

В поле Образец (Preview) показан результат выполнения созданного правила условного форматирования:

Если всё получилось так, как было задумано, и выбранный цвет устраивает, то жмём ОК, чтобы увидеть созданное правило в действии.Теперь, если значение в столбце Qty. больше 4, то соответствующая строка таблицы целиком станет голубой.

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

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

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

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

1. На вкладке Главная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) >Управление правилами (Manage Rules)
2. В выпадающем списке Показать правила форматирования для (Show formatting rules for) выберите Этот лист (This worksheet). Если нужно изменить параметры только для правил на выделенном фрагменте, выберите вариант Текущий фрагмент (Current Selection).
3. Выберите правило форматирования, которое должно быть применено первым, и при помощи стрелок переместите его вверх списка. Должно получиться вот так:Нажмите ОК, и строки в указанном фрагменте тут же изменят цвет, в соответствии с формулами в обоих правилах.

Как изменить цвет строки на основании текстового значения одной из ячеек

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

• Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таких ячеек должна быть оранжевой;
• Если заказ доставлен (значение Delivered), то заливка таких ячеек должна быть зелёной;
• Если срок доставки заказа находится в прошлом (значение Past Due), то заливка таких ячеек должна быть красной.

И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.

С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

=$E2=»Delivered»
=$E2=»Past Due»

Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

=ПОИСК(«Due in»;$E2)>0
=SEARCH(«Due in»,$E2)>0

В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.

Подсказка: Если в формуле используется условие “>0“, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

=ПОИСК(«Due in»;$E2)=1
=SEARCH(«Due in»,$E2)=1

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

Итак, выполнив те же шаги, что и в первом примере, мы создали три правила форматирования, и наша таблица стала выглядеть вот так:

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

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

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

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

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (AND), ИЛИ (OR) и объединить таким образом нескольких условий в одном правиле.

Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

=ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»)

=ИЛИ($F2=»Due in 5 Days»;$F2=»Due in 7 Days»)
=OR($F2=»Due in 5 Days»,$F2=»Due in 7 Days»)

Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.), запишем формулу с функцией И (AND):

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

=ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»;$F2=»Due in 5 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»,$F2=»Due in 5 Days»)

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

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

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

Доброго времени суток уважаемый посетитель!

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

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

Активировать условное форматирование в Excel возможно на вкладке «Главная», в группе «Стили» нажав кнопку с выпадающим списком «Условное форматирование», а в списке вы можете увидеть все доступные возможности: Рассмотрим более детально предоставленные возможности, которые предоставляет условное форматирование в Excel:

• «Правила выделения ячеек» — дает доступ к дополнительному меню с параметрами форматирования ячеек, значение которых находятся в определенном диапазоне или соответствуют определенному критерию.
• «Правила отбора первых и последних значений» — открывает возможность форматировать ячейки на основании их нахождения, в первых или последних 10 позициях.
• «Гистограмма» — предоставляет палитру разноцветных гистограмм предназначенные для визуализации содержимого в ячейках.
• «Цветовые шкалы» — предоставляют вам 2 или 3-х цветные шкалы с цветом фона ячеек, которые закрашиваются в определённый цвет относительно других ячеек вашего диапазона.
• «Наборы значков» — в этом пункте предоставляются наборы значков от 3 до 5, а отображения значков зависит от того, какое значение имеет данная ячейка в диапазоне относительно других ячеек.
• «Создать правило» — открывает вам диалоговое окно, которое поможет вам создать личное пользовательское условное форматирование для определённых ячеек.
• «Удалить правила» — запускает дополнительное меню, которое позволит вам удалить созданные или существующие правила условного форматирования, как в ячейке, так и на листе по вашему выбору.
• «Управление правилами» — запускает диалоговое окно, которое разрешает задавать приоритет, удалять и редактировать определенные правила.

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

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

Рассмотрим вариант, когда нам нужно раскрасить таблицу по 3 цифровым критериям, возьмем диапазон от 1 до 100 и представим что нам надо определить всё относительно целого числа 50. Итак, у нас есть 3 критерия: во-первых, это все числа меньше 50, во-вторых, это все числа которые равны 50 и в-третьих, это все числа больше 50. Все 3 критерия мы будет помечать разными цветами.

Рассмотрим пошагово, как это осуществить:

1. Выделите диапазон, к которому будет применено условное форматирование по заданным критериям;
2. Создаем 1-е правило-критерий для выделенного диапазона. На вкладке «Главная» в группе «Стили» нажимаем кнопку «Условное форматирование», переходим на пункт «Правила выделения ячеек» и выбираем пункт «Равно». Указываем наш первый критерий в левом поле, целое число – 50, а в правом выбираем цвет заливки при выполнении условия «Желая заливка и темно-желтый текст».
3. Создаем 2-е правило-критерий для выделенного диапазона. Проделываем тот же самый путь, но в конце выбираем пункт «Больше». В левом текстовом окне указываем наше число 50, больше которого все значения закрасим в зелёный цвет, который указываем в правом поле.
4. Создаем 3-е правило-критерий для выделенного диапазона. Повторяем предыдущие процедуры и в конце выбираем пункт «Меньше» и в поле выбора цвета укажем красный.

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

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

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

Давайте разберем этот пример подробнее и пошагово:

1. Выделяем диапазон для применения нашего условного форматирования;
2. На вкладке «Главная» выбираем в группе «Стили», кнопку «Условное форматирование» и в выпадающем меню нажимаем пункт «Создать правило». В диалоговом окне «Создание правила форматирования», в окне выбора «Выберите тип правил:», нам нужна строка «Использовать формулу для определения форматируемых ячеек».
3. В нижнее поле «Изменить описание правила» вводим наше условие в виде формулы и указываем условное форматирование для ячеек которые соответствуют нашим критериям. В нашем случае нам нужна формула =ДЕНЬНЕД($A2;2)=7 и при совпадении условия указываем заливку ячейки красным цветом.

Заливка ячеек по текстовому критерию.

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

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

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

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

Был очень рад помочь! Если я смог вам помочь ставьте лайк и пишите комментарии!

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

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

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

Понятие о рассматриваемом инструменте

Его нужно использовать там, где нет доступа к «1С», но при этом необходимо отображать актуальную информацию за минуты или даже секунды. С этой целью можно использовать инструмент «Условное форматирование» табличного процессора компании Microsoft.

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

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

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

Возможности использования в разных версиях

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

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

Рассмотрим примеры при использовании Excel 2016. Более ранние версии, начиная с 2007, имеют незначительные отличия, с которыми пользователь может разобраться самостоятельно.

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

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

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

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

Подтверждаем сделанные изменения нажатием Enter или кликом по ОК.

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

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

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

Сравниваем разные базы данных

Условное форматирование ячеек в Excel может осуществляться по некоторым диапазонам. Например, в колонке А введены одни значения, а в колонке В — другие. Выделяем первый из них сверху вниз. Кликаем по знакомой кнопке, выбираем те же пункты. После появления диалогового окна, имеющего одноименное с выбранным условием название, в его левой части вводим относительную ссылку (=<адрес_ячейки>) из второго диапазона. Если просто щелкнуть на нужную ячейку данного диапазона, то появится ссылка со знаками американского доллара, их нужно будет убрать.

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

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

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

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

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

Можно использовать и другой вариант, о котором речь пойдет ниже.

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

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

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

Рассматриваемый процесс по отношению к дате

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

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

Применение инструмента к строке по значению, заданному в ячейке

В этом случае выделяется строка, а условием осуществления рассматриваемого форматирования является некоторое значение в определенной ячейке. Здесь чаще всего применяются логические функции, которые начинаются со слова «ЕСЛИ», ссылка на строку приводится абсолютная, а на ячейку — смешанная.

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

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

Допустим, из имеющегося диапазона нам необходимо вычленить те числа, которые относятся к нечетным. Для этого кликаем по одноименной с рассматриваемым инструментом кнопке на панели инструментов, выбираем «Создать правило», а там «Использовать формулу… форматируемых ячеек». В нижней части вводим следующую формулу: =ОСТАТ($A1:2)=1 — и задаем тот формат, который нас устраивает. Подтверждаем введенные изменения, в результате в таблице те числа, которые являются нечетными, в колонке А будут выделены заданным цветом.

Создание информационного сообщения

Выделяем определенный диапазон, например, в колонке А. В ячейке В1 вводим формулу (=ЕСЛИ(А1><определенное_число>;»сверхнорма»;ЕСЛИ<следующее_определенное_число>;»следует обратить внимание»)). Эта формула может включать множество значений в зависимости от того, сколько информационных сообщений необходимо.

Кликаем на Enter, в итоге в В1 появится одно из тех значений формулы, которое в ней было введено в кавычках. Протягиваем эту формулу до конца колонки в соответствии с количеством строк со значениями в выделенном диапазоне.

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

В заключение

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