Условное форматирование наборы значков в excel
Как изменить цвет значка условного форматирования в Excel?
При использовании значка условного форматирования, установленного в Excel, отображается значок трех цветов. Если относительное значение больше 67% всех значений, значок отображается в виде стрелки вверх с зеленым цветом, если значение больше 33%, но менее 67% всех значений, значок отображается в виде горизонтальной стрелки с желтым цветом, если значение меньше 33% всех значений, значок отображается в виде стрелки вниз с красным цветом. Вы когда-нибудь пытались изменить цвета этих значков, как показано на скриншотах ниже?
Изменить набор значков условного форматирования
На самом деле, нет способа изменить набор значков условного форматирования, но я могу представить способ вставки некоторых похожих значков с разными цветами по мере необходимости.
1. Выберите пустую ячейку рядом со списком данных, например, F2, введите 67%, затем в ячейках ниже введите 33% и 0%. Смотрите скриншот:
2. Затем введите = ПРОЦЕНТИЛЬ ($ C $ 1: $ C $ 8; F2) в G2, чтобы вычислить значение линии выше 67%. C1: C8 - это список значений, F2 - 67%., Затем перетащите дескриптор заполнения вниз, чтобы вычислить линию постоянного значения. Смотрите скриншот:
3. Затем в H2 щелкните Вставить > СимволИ в Символ диалоговое окно, выберите Wingdings 3 из шрифт раскрывающийся список, а затем вставьте тип стрелки вверх и щелкните Вставить, чтобы вставить его. Смотрите скриншоты:
4. Затем повторите вышеуказанный шаг, чтобы выбрать горизонтальную стрелку и стрелку вниз, чтобы вставить их в ячейку H3 и ячейку H4.
5. Затем в столбце B1 перед списком значений введите эту формулу. =IF(C1="","",IF(C1>=$G$2,$H$2,IF(C1>=$G$3,$H$3,$H$4))) , затем перетащите маркер заполнения вниз к используемым ячейкам. Смотрите скриншот:
Функции: C1 - это значение, которое вы используете, G2 - это строка значения 67%, H2 - стрелка, которую вы хотите показать, когда значения превышают 67%, G3 - это линия значения 33%, H3 - стрелка, отображаемая, когда значения превышают 33 %, но ниже 67%, G4 - значение ниже 33%, а H4 - стрелка, показываемая, когда значения ниже 33%.
6. Затем отформатируйте шрифт этих ячеек как Wingdings 3. Смотрите скриншот:
7. Продолжайте нажимать Главная > Условное форматирование > Новое правило чтобы отформатировать цвета стрелок по своему усмотрению. Смотрите скриншот:
8. в Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. in Выберите тип правила раздел и введите = $ C1> $ G $ 2 в Формат значений, где эта формула истинна. Нажмите Формат для открытия Формат ячеек диалога под шрифт на вкладке выберите цвет шрифта, который вы используете. Смотрите скриншот:
Функции: C1 - это первое значение, которое вы используете, G2 - это рассчитанное вами значение 67%.
9. Нажмите OK > OK, а стрелки вверх имеют красный цвет.
10. Повторите два вышеупомянутых шага, чтобы отформатировать горизонтальные стрелки / стрелки вниз с другими цветами.
Отформатируйте горизонтальные стрелки с помощью этой формулы = И ($ C1 $ G $ 3)
Отформатируйте стрелки вниз с помощью этой формулы = $ C1
11. Затем отформатируйте цвет шрифта стрелок на другой цвет, Цвет шрифта под Главная таб. Смотрите скриншот:
При помощи наборов значков в Excel 2010 очень просто визуализировать значения в ячейках. Каждый значок представляет собой диапазон значений.
Чтобы добавить набор значков, выполните следующие действия:
- Выделите диапазон ячеек.
- На вкладке Главная (Home) нажмите кнопку Условное форматирование >Наборы значков (Conditional Formatting > Icon Sets) и выберите подтип.Результат:
-
По умолчанию для трёх значков Excel вычисляет 67 и 33 процента от максимального значения в диапазоне:
67% = мин + 0,67 * (макс-мин) = 2 + 0,67 * (95-2) = 64,31
33%= мин + 0,33 * (макс-мин) = 2 + 0,33 * (95-2) = 32,69
Читайте дальше, чтобы узнать, как настроить набор значков.
Примечание: Чтобы открыть это диалоговое окно для новых правил, на 2-м шаге нашей инструкции кликните Другие правила (More Rules).
Результат:
Условное форматирование в Excel автоматически изменяет внешний вид ячейки в зависимости от ее содержимого. Например, Вы можете выделить красным цветом ячейки, в которых содержаться недопустимые значения. Этот урок мы посвятим условному форматированию – одному из самых интересных и полезных средств Excel.
Представьте, что у Вас есть лист Excel, содержащий тысячу строк с данными. Думаю, будет довольно затруднительно среди всего этого объема информации разглядеть закономерности или необходимые данные. Подобно диаграммам и спарклайнам, условное форматирование помогает визуализировать информацию и упростить ее восприятие.
Основные сведения об условном форматировании
Условное форматирование в Excel позволяет автоматически изменять формат ячеек в зависимости от содержащихся в них значений. Для этого необходимо создавать правила условного форматирования. Правило может звучать следующим образом: «Если значение меньше $2000, цвет ячейки – красный.» Используя это правило Вы сможете быстро определить ячейки, содержащие значения меньше $2000.
Создание правила условного форматирования
В следующем примере на листе Excel содержатся данные по продажам за последние 4 месяца. Допустим, мы хотим знать, кто из продавцов выполняет месячный план продаж, а кто нет. Для выполнения плана необходимо продать на сумму более $4000 в месяц. Создадим правило условного форматирования, которое выделит нам все ячейки в таблице со значением выше $4000.
- Выделите ячейки, по которым требуется выполнить проверку. В нашем случае это диапазон B2:E9.
- На вкладке Главная нажмите команду Условное форматирование. Появится выпадающее меню.
- Выберите необходимое правило условного форматирования. Мы хотим выделить ячейки, значение которых Больше $4000.
- Появится диалоговое окно. Введите необходимое значение. В нашем случае это 4000.
- Укажите стиль форматирования в раскрывающемся списке. Мы выберем Зеленую заливку и темно-зеленый текст. Затем нажмите OK.
- Условное форматирование будет применено к выделенным ячейкам. Теперь без особого труда можно увидеть, кто из продавцов выполнил месячный план в $4000.
Вы можете применять сразу несколько правил условного форматирования к одному и тому же диапазону ячеек, что позволяет более гибко и наглядно представить нужную информацию.
Удаление условного форматирования
- Нажмите команду Условное форматирование. Появится выпадающее меню.
- Наведите указатель мыши на пункт Удалить правила и выберите, какие правила Вы хотите удалить. В нашем примере мы выберем Удалить правила со всего листа, чтобы удалить все условное форматирование на листе.
- Условное форматирование будет удалено.
Вы можете выбрать пункт Управление правилами, чтобы увидеть все правила условного форматирования, созданные на этом листе или в выделенном диапазоне. Диспетчер правил условного форматирования позволяет отредактировать или удалить пользовательские правила. Это особенно удобно, если создано несколько правил на одном листе.
Предустановленные стили условного форматирования
Excel содержит набор предустановленных стилей, которыми можно воспользоваться, чтобы быстро применить условное форматирование к данным. Они сгруппированы в три категории:
Как использовать наборы значков для выделения значений при условном форматировании в Excel?
Для условного форматирования вы можете использовать наборы значков, чтобы отмечать значения, которые соответствуют вашим условиям. Наборы иконок более привлекательны, чтобы привлечь ваше внимание. В этой статье будет показано, как использовать наборы значков для выделения значений в условном форматировании в Excel в деталях.
Использование наборов значков для выделения значений при условном форматировании в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
1. Выберите диапазон ячеек, в который вы хотите добавить значок, задающий условное форматирование.
2. Нажмите Условное форматирование > Наборы иконок под Главная вкладку, затем выберите нужный набор значков. Смотрите скриншот:
Затем вы увидите, что наборы значков добавляются перед выбранными значениями.
Внимание: По умолчанию эти три значка рассчитываются Excel, как показано на скриншоте ниже. (Макс. и Мин. - это наибольшее и наименьшее число выбранного диапазона данных в следующих формулах.)
1). Значок отмечает значения> = 115.35. Формула 115.35 = мин + 0.67 * (макс - мин) = 45 + 0.67 * (150-45);
2). Значок отмечает значения, которые равны = 79.65. Формула 79.65 = мин + 0.33 * (макс-мин) = 45 + 0.33 * (150-45)
3). И Значок отмечает значения, которые
3. Теперь вам нужно изменить условия иконки под свои нужды. Выберите диапазон и нажмите Условное форматирование > Управление правилами.
4. в Диспетчер правил условного форматирования диалоговом окне выберите правило с наборами значков, которые вы хотите изменить, затем щелкните Редактировать Туле кнопка. Смотрите скриншот:
5. в Изменить правило форматирования диалогового окна, перейдите к Редактировать описание правила раздел. В этом разделе вы можете указать условия правила по своему усмотрению.
В этом случае я меняю тип правила на Число, и отображать только значок, чтобы отметить значения> = 100. Смотрите скриншот:
6. После нажатия на OK в Изменить правило форматирования диалоговое окно, он вернется к Диспетчер правил условного форматирования диалоговое окно. Пожалуйста, нажмите на OK чтобы завершить настройки.
Затем значок устанавливает условное форматирование с вашими собственными условиями правила.
Как сравнить соседние ячейки с наборами значков условного форматирования в Excel?
Когда вы сравниваете два столбца данных, вы хотите использовать наборы значков условного форматирования, дающие визуальное представление сравнения. Например, если данные в столбце B больше, чем в столбце A, появится значок стрелки вверх; если столбец B меньше столбца A, появится значок стрелки вниз; или если столбцы B и A равны, значок стрелки вправо будет отображаться, как показано на следующем снимке экрана. В этой статье мы расскажем, как быстро и легко решить эту задачу в Excel.
Сравните соседние ячейки столбца с набором значков условного форматирования в Excel
Если у вас есть данные из двух столбцов, чтобы сравнить соседние ячейки с помощью наборов значков условного форматирования, сделайте следующее:
1. Введите эту формулу: = ЕСЛИ (A2> B2; 0; ЕСЛИ (A2 в пустую ячейку рядом с вашими данными и перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить формулой, см. снимок экрана:
2. Затем выберите ячейки формулы и нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. В Новое правило форматирования диалоговом окне выполните следующие операции:
(1.) Щелкните Отформатируйте все ячейки на основе их значений вариант в Выберите тип правила список;
(2.) В Стиль формата выпадающий список, выберите Наборы иконок, А затем выберите Значок 3 стрелки из Иконка Стиль;
(3.) Проверить Показать только значок вариант;
(4.) Измените значения и тип, пожалуйста, измените Тип в Число отдельно и в первой строке измените значение на 2, во второй строке измените значение на 1.
4. После завершения настроек в диалоговом окне нажмите OK кнопку, вы получите желаемый результат, см. снимок экрана:
Сравните ячейки соседних строк с помощью значка условного форматирования, установленного в Excel
Если у вас есть список чисел, чтобы сравнить их строка за строкой, возможно, вам помогут следующие шаги.
1. Введите эту формулу: = ЗНАК ($ A3- $ A2) в соседнюю ячейку B3, а затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите использовать, см. снимок экрана:
2. Продолжайте выбирать ячейки формулы, а затем перейдите к Новый тип форматирования диалоговом окне выполните следующие операции:
(1.) Щелкните Отформатируйте все ячейки на основе их значений вариант в Выберите тип правила список;
(2.) В Стиль формата выпадающий список, выберите Наборы иконок, А затем выберите Значок 3 стрелки из Иконка Стиль;
(3.) Проверить Показать только значок вариант;
(4.) Измените значения и тип, пожалуйста, измените Тип в Число отдельно и в первой строке выберите > в раскрывающемся списке и измените значение на 0, во второй строке измените значение на 0 в том числе.
3. Затем нажмите OK кнопку, и соответствующий набор значков был вставлен в ячейки по мере необходимости, см. снимок экрана:
Читайте также: