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


Если вы привыкли использовать более старые версии Excel, параметры условного форматирования в Excel 2007, 2010 и 2013 вас удивят. Так зачем же вам беспокоиться об использовании условного форматирования? Итак, вот несколько причин, по которым мне нравится использовать эту функцию Excel:

<р>1. Чтобы сделать ваши данные более привлекательными.

<р>2. Чтобы ваши таблицы было легче понять с первого взгляда.

<р>3. Определить определенные типы чисел, которые помогут в решении проблем.

<р>4. Чтобы помочь вам сделать выводы на основе ваших данных.

<р>5. Чтобы визуально показать пользователю, что такое «хорошо» или «плохо», используя зеленый и красный цвета.

Теперь вы можете использовать условное форматирование для форматирования каждой ячейки диапазона на основе ваших собственных критериев (и существует множество вариантов форматирования на выбор). Например, если у вас есть отчет о прибылях и вы хотите пометить всю прибыль, превышающую 200 долларов США, зеленым цветом, всю прибыль менее 200 долларов – желтым, а все убытки — красным, то вы можете использовать условное форматирование, чтобы быстро выполнить всю работу за вас. .

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

Условное форматирование позволяет быстро и легко форматировать значительные объемы данных, сохраняя при этом возможность различать разные типы данных. Вы можете создать правила для параметров форматирования, которые позволят Microsoft Excel выполнять автоматическое форматирование. На самом деле вам нужно выполнить всего три простых шага.

Шаг 1. Выберите ячейки, которые хотите отформатировать.

Шаг 2. Нажмите кнопку Условное форматирование в главном меню в разделе «Стили».

изображение_317.jpg

Шаг 3. Выберите правила. Вверху расположены Правила выделения ячеек и Правила верхнего/нижнего уровня , которые позволяют сравнивать значения. В этом примере мы ввели три правила. Во-первых, любая сумма, превышающая 200 долларов США, считалась зелёной.

Следует отметить, что только раздел «Правила выделения ячеек» также можно использовать для сравнения одного набора данных с другим набором данных. Все остальное будет просто использовать один набор данных, который вы выделили, и сравнивать значения друг с другом. Например, при использовании правила «Больше чем» я могу сравнивать значения от A1 до A20 с определенным числом или сравнивать A1 и A20 с B1 и B20.

изображение_318.jpg

изображение_319.jpg

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

изображение_320.jpg

Если вам не нравятся эти параметры форматирования, в Excel есть множество различных новых параметров условного форматирования, которые вы можете использовать. Например, вы можете вставлять значки, такие как цветные стрелки (Наборы значков ), гистограммы, как во втором примере (Гистограммы данных ), или даже диапазон автоматически выбранных цветов, например в последнем примере (Цветовые шкалы ). Эти три параметра сравнивают только значения из одного и того же набора данных. Если вы выберете от A1 до A20, эти значения будут сравниваться только друг с другом.

image_322.jpg

изображение_323.jpg

изображение_324.jpg

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

изображение_321.jpg

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

rules manager

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

Существует также флажок Stop If True , который я не буду здесь вдаваться в подробности, поскольку он довольно сложный. Однако вы можете прочитать этот пост от Майкрософт, в котором это объясняется очень подробно.

Новые параметры условного форматирования Excel 2010

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

Ранее я упоминал, что раздел Правила выделения ячеек позволяет сравнивать один набор данных с другим набором данных в той же электронной таблице. В версии 2010 теперь можно ссылаться на другой лист в той же книге. Если вы попытаетесь сделать это в Excel 2007, он позволит вам выбрать данные из другого листа, но выдаст сообщение об ошибке, когда вы попытаетесь нажать «ОК» в конце..

Теперь это можно сделать в Excel 2010, но это немного сложно, поэтому я объясню это шаг за шагом. Допустим, у меня есть два рабочих листа, и на каждом листе есть данные от B2 до B12, например, о прибыли. Если я хочу увидеть, какие значения от B2 до B12 на листе 1 превышают значения от B2 до B12 на листе 2, я бы сначала выбрал значения от B2 до B12 на листе 1, а затем нажмите Отлично, чем в разделе «Правила выделения ячеек».

cell reference

Теперь нажмите кнопку ссылки на ячейку, которую я показал выше. Поле изменится, и значок курсора превратится в белый крест. Теперь нажмите на лист 2 и выберите ТОЛЬКО  ячейку B2. НЕ  выбирайте весь диапазон от B2 до B12.

select cells range

Вы увидите, что поле теперь имеет значение =Sheet2!$B$2 . Нам нужно будет изменить это на =Sheet2!$B2 . По сути, просто избавьтесь от , который стоит перед цифрой 2. Это сохранит столбец фиксированным, но позволит номеру строки изменяться автоматически. По какой-то причине вы не сможете просто выбрать весь диапазон.

Снова нажмите кнопку ссылки на ячейку, а затем нажмите «ОК». Теперь значения на листе 1, превышающие значения на листе 2, будут отформатированы в соответствии с выбранными вами параметрами форматирования.

conditional formatting

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

format cells rule

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

Похожие сообщения:


19.11.2014