Наборы значков условного форматирования в Excel

Обновлено: 06.07.2024

Наборы значков в Excel — это наборы различных типов значков, фигур, индикаторов, направлений, которые используются для визуализации выбранных значений путем придания им различных значений. Доступ к наборам значков можно получить из раскрывающегося списка условного форматирования на ленте главного меню. Там у нас будут разные категории, и все значки в списке имеют какое-то назначение и применение. Мы можем использовать направления для взлетов и падений в данных; для любых указаний используйте фигуры и индикаторы и оценивайте что угодно, предпочитая рейтинги.

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Как использовать наборы значков в Excel?

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

Применение наборов значков индикаторов с помощью условного форматирования

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

Рассмотрите приведенный ниже пример, в котором показаны данные о покупках за ноябрь – 16 месяц.

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

Значок зеленого цвета: если значение больше равно 811.
Значок желтого цвета: если значение меньше 811 и больше равно 250 процентам.
Значок красного цвета: если значение меньше 250 процентов.

Теперь в этом примере мы увидим, как отобразить эти наборы значков в Excel, выполнив описанный ниже процесс.

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 121 оценка)

Сначала создайте новый столбец для отображения наборов значков, как показано ниже. Затем примените знак = (равно) к I2 и выберите ячейку G2, чтобы получить значение в ячейке I2 для применения условного форматирования.

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

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

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

Кроме того, мы видим различные правила форматирования. Выберите первое правило форматирования Форматировать все ячейки на основе их значений.
Мы можем увидеть стиль формата, стиль значка и значок, которые будут отображаться в диалоговом окне выше. Теперь примените условное форматирование, как показано ниже, чтобы отобразить наборы значков.
Значок зеленого цвета: если значение больше равно 811.
Значок желтого цвета: если значение меньше 811 и больше равно 250 процентам.
Значок красного цвета: если значение меньше 250 процентов.
Измените стиль значка на «Пометить» и «Применить значения», а также выберите «Тип как число», поскольку по умолчанию Excel использует значения в процентах. Мы выбрали тип в виде числа, потому что правило форматирования основано на значениях, а не на процентах, показанных на снимке экрана ниже.

Нажмите "ОК", и мы получим приведенный ниже вывод следующим образом.

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

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

В приведенном ниже результате мы видим значок флажка в столбце I, где мы применили условие для отображения в виде зеленого флажка: если значение больше или равно 811 и желтого флажка: если значение меньше 811. и больше или равно 250 и Red Flag: если значение меньше 250.

Применение наборов значков направления с помощью условного форматирования

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

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

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

Значок зеленого цвета: если значение больше 45 000.
Значок желтого цвета: если значение меньше 45 000 и больше 43 000.
Значок красного цвета: если значение меньше 0.

Теперь в этом примере мы увидим, как отобразить эти наборы значков, выполнив следующие шаги.

Сначала создайте новый столбец для отображения наборов значков, как показано ниже.
Затем примените знак = (равно) в столбце I2 и выберите ячейку G2, чтобы получить значение в ячейке I2 для применения условного форматирования.

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

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

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

Кроме того, мы видим различные правила форматирования. Затем выберите первое правило форматирования Форматировать все ячейки на основе их значений.
Мы можем увидеть стиль формата, стиль значка и значок, которые будут отображаться в диалоговом окне выше. Теперь примените условное форматирование, как показано ниже, чтобы отобразить наборы значков.
Значок зеленого цвета: если значение больше 45 000.
Значок желтого цвета: если значение меньше 45 000 и больше 43 000.
Значок красного цвета: если значение меньше 0.
Измените стиль значка на значок направления, примените значения и выберите числовой тип, потому что по умолчанию Excel использует значения в процентах. Мы выбрали тип в виде числа, потому что правило форматирования основано на значениях, а не на процентах, показанных на снимке экрана ниже.

Нажмите "ОК", и мы получим приведенный ниже вывод следующим образом.

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

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

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

В приведенном ниже результате мы видим значок флага в столбце I, где мы применили условие для отображения зеленым цветом: если значение больше или равно 45000 и желтым цветом: если значение меньше 45000 и больше или равно 43000 и Красный: если значение меньше 0.

Что нужно помнить

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

Цель

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

Готовы? Я тоже... приступим.

Подробнее

Рассчитать разницу
Применить набор значков
Настроить правило

Давайте рассмотрим их по одному.

Рассчитать разницу

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

Применить набор значков

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

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

Обновленный рабочий лист показан ниже для справки.

Теперь пришло время настроить правило условного форматирования по умолчанию.

Настроить правило

Для начала мы выбираем ячейки с условным форматированием (ячейки сравнения), а затем открываем диалоговое окно «Диспетчер правил условного форматирования», выбрав следующую команду на ленте:

Диалог должен выглядеть примерно так:

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

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

По умолчанию наборы значков из трех значков применяются на основе верхней, средней и нижней трети значений в диапазоне. Это можно увидеть, просмотрев нижнюю половину диалогового окна, где мы видим, что зеленый значок используется, когда значение больше или равно 67 процентам значений ячейки. Желтый значок используется для тех ячеек, где значение меньше 67 процентов и больше или равно 33 процентам. Красный значок используется для ячеек, в которых значение меньше 33 процентов. Мы можем изменить эти настройки по умолчанию.

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

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

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

< /p>

Как использовать наборы значков для выделения значений в условном форматировании в 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). И значок отмечает значения, которые являются правилами управления.

<р>4. В диалоговом окне «Диспетчер правил условного форматирования» выберите правило с наборами значков, которые вы хотите изменить, затем нажмите кнопку «Редактировать таблицу». Смотрите скриншот:

<р>5. В диалоговом окне «Редактировать правило форматирования» перейдите в раздел «Редактировать описание правила». В этом разделе вы можете указать условия правила по своему усмотрению.

В этом случае я изменяю тип правила на Число и отображаю значок только для обозначения значений >=100. Смотрите скриншот:

<р>6. После нажатия кнопки «ОК» в диалоговом окне «Редактировать правило форматирования» он вернется в диалоговое окно «Диспетчер правил условного форматирования». Нажмите кнопку OK, чтобы завершить настройку.

Затем значок устанавливает условное форматирование с вашими условиями правила.

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

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

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

Наборы значков условного форматирования Excel

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

Вы применяете наборы значков к своим данным, нажимая «Условное форматирование» > «Наборы значков», и значки сразу появляются внутри выбранных ячеек.

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

Здесь вы можете выбрать другие значки и назначить им разные значения. Я решил оставить те же значки, но назначить их по-другому:

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

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

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

Пример 1. Применение набора значков на основе других ячеек в строке (пустые и непустые)

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

Добавьте пустой столбец перед таблицей (столбец A).
Скопируйте следующую формулу в столбец A: =СЧИТАТЬПУСТОТЫ(B2:F2) , где F — последний столбец в таблице. Эта формула будет подсчитывать количество пустых ячеек в каждой строке.
Примените правило набора значков условного форматирования к столбцу А, нажав Дополнительные правила…, как описано выше.
В диалоговом окне Новое правило форматирования выполните следующие действия:
- Нажмите кнопку Обратный порядок значков, чтобы изменить порядок значков.
- Установите флажок Только набор значков.
- Для значка креста установите >=5 (где 5 – количество столбцов в таблице, исключая первый столбец "Значок").
- Для значка восклицательного знака установите >=1.
- Установите для параметра Тип значение "Число" для обоих значков.

И вот результат:

Пример 2. Добавление значков на основе значения другой ячейки

Допустим, у вас есть список заказов и вам нужно добавить зеленый флажок в столбец А, когда будет доставлен определенный товар, т.е. в соответствующей ячейке столбца D (Дата доставки) есть какое-либо значение. Если в столбце D нет значения, в столбце A следует поставить красный флажок.

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

И снова вы начинаете с добавления формулы в столбец A. На этот раз мы будем использовать функцию ЕСЛИ: =ЕСЛИ($D2<>"",3,1) . Формула говорит поставить 3 в столбце A, если в соответствующей ячейке столбца D есть какое-либо значение, в противном случае поставить 1. Естественно, вам нужно будет заменить D столбцом, на котором вы основываете свое форматирование.
Выделите все ячейки в столбце A, кроме заголовка столбца, и создайте правило набора значков условного форматирования, нажав Условное форматирование > Наборы значков > Дополнительные правила.
В диалоговом окне Новое правило форматирования выберите следующие параметры:
- Нажмите кнопку Обратный порядок значков, чтобы изменить порядок значков.
- Установите флажок Только набор значков.
- Для зеленого флажка установите >=3.
- Для желтого флажка установите >2. Как вы помните, нам не очень-то нужен желтый флаг где-либо, поэтому вы ставите условие, которое никогда не будет выполнено, т.е. значение больше 2 и меньше 3.
- Установите для параметра Тип значение "Число" для обоих значков.

И вот результат ваших усилий: значки зеленого и красного флажков добавляются в столбец A на основе значения в столбце D.

Гистограммы условного форматирования Excel

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

Вы применяете гистограммы в Excel уже знакомым вам способом — просто выберите ячейки, нажмите «Условное форматирование» > «Гистограммы» и выберите нужный тип гистограммы. Как только вы это сделаете, цветные полосы будут размещены внутри выбранных ячеек.

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

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

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

Цветовые шкалы условного форматирования Excel

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

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

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

Вот как вы используете наборы значков, цветовые шкалы и гистограммы Excel. Очень интуитивно понятно и удобно, не так ли? Если вам интересно узнать о других типах правил условного форматирования, вам могут пригодиться следующие руководства:

В следующей статье мы собираемся исследовать загадочные особенности формул условного форматирования Excel, следите за обновлениями!

144 комментария к статье "Условное форматирование Excel – наборы значков, гистограммы и цветовые шкалы"

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

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

Поэтому, насколько я понимаю, общепринятое мнение является правильным.

Я создал лист Excel, в котором есть список проектов с именами PM, в котором выделены даты отправки их отчетов о состоянии. Я хочу использовать наборы значков, чтобы выделить и показать даты, которые отражают раннюю отправку, запоздалую отправку и своевременную отправку.

Сроки выполнения указаны в таблице в строках над каждым месяцем. Срок сдачи апрельского отчета – 5 мая 2021 года. Если лица подают 4 мая, должен появиться зеленый значок, если отчет представлен вовремя, то есть 5 мая 2021 года, используется желтый значок, если отчет с опозданием, то есть 6 мая, должен появиться красный значок.

У меня проблемы с аргументацией и получением нужного значка.

Привет!
Вы пробовали способы, описанные в этом сообщении в блоге? Используйте Icon Set для ваших данных. Затем используйте «Управление правилами» — «Редактировать правило» и задайте нужные условия для каждого значка.

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

Мне нужно назначить встречи по временному интервалу. Они устанавливаются с интервалом в 15 минут. Время на высоте. Услуги предоставляются в течение 1 или 2 часов с 15-минутным перерывом между ними. Им нужно развернуть полосу на время (ячейки подряд), которое займет встреча. Я могу настроить цвета и текст с условным форматированием, но не могу заставить его перемещаться более чем по одной ячейке/столбцу подряд в течение времени, которое займет каждая встреча. Есть ли способ сделать это?

Я хочу, чтобы в ячейках появлялись значки, представляющие собой процентное значение, основанное на конкретной ячейке

в основном
Зарплата $1500
Бюджет (66%) = $990
Расходы $1300

Теперь, чтобы отслеживать мой бюджет, независимо от того, как изменится моя зарплата, я хотел бы иметь индикаторы/значки, когда мои расходы составляют 30 % (зеленый флажок), 50 % (желтый флажок) и 60 %. % (красный флажок) от моей зарплаты без необходимости индивидуально устанавливать каждую ячейку каждый месяц, когда зарплата меняется, так как она меняется каждый месяц. Возможно ли это?

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

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

привет, во-первых, спасибо за репетитора!

Как мне выполнить условие значка, если я хочу установить ячейку снова, ячейку, снова и снова?
например: скажем, валовая прибыль за месяц
c1: 20 000 (январь 20)
e1: 15 000 (декабрь 19)
g1: 22 000 (ноябрь 19)

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

Читайте также: