Условное форматирование и гистограмма в ячейке Excel
Начиная с версии Excel 2007, мы имеем возможность использовать гистограммы для ячеек в условном форматировании. Рассмотрим детально ее практическое применение и настройки.
Пример гистограммы в условном форматировании
Для наглядного примера возьмем отчет в таблице по прибыльности магазинов за последние 2 года:
Чтобы в столбце D добавить правило основано на гистограмме выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Гистограммы»-«Градиентная заливка»-«Зеленая гистограмма».
В результате появилась диаграмма в ячейке Excel, которая визуально отображает содержание значения.
Чем больше значение содержит ячейка по отношению к другим ячейкам, тем длиннее ее диаграмма. Положительные значения отображаются зеленым цветом, а отрицательные – красным.
Полезный совет! Уже при наведении мышкой на любой цвет гистограммы диапазон предварительно оформляется соответственному типу. Если среди предложенных вариантов Вы не нашли удовлетворяющий Вашим потребностям выберите нижнюю опцию «Другие правила». Откроется окно с гибкими настройками оформления гистограммы для ячеек.
Так же, как и при других типах правил условного форматирования мы можем изменять и настраивать график в ячейке с помощью диспетчера правил. Для этого:
- Выделите диапазон D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».
- Появится окно «Диспетчер правил условного форматирования». В нем выделите текущее правило и нажмите на кнопку «Изменить».
- В появившемся окне «Изменение правила форматирования» из группы параметров «Внешний вид столбца» из выпадающего списка «Граница» выберите опцию «Нет границы». Так мы немного изменим вид шкал под свой вкус.
- Нажмите на кнопку «Отрицательные значения и ось».
- Теперь в новом окне «Настройка отрицательных значений оси» укажите на самую нижнею опцию «Нет (показать отрицательные и положительные значения в одном направлении)». И нажмите ОК на всех открытых окнах.
Обратите внимание! Во окне «Настройка отрицательных значений оси» по умолчанию ось с нулевым значением автоматически смещается в зависимости от размера наибольшего отрицательного значения. Там же есть опция «Середина ячейки» при выборе которой ось с нулевым значением всегда будет находится по середине ячейки в независимости от ее значений. При этом по умолчанию для положительных значений шкала будет увеличиваться в правую сторону, а для отрицательных – в левую. Хотя направление можно менять в предыдущем окне «Изменение правила форматирования» в правом нижнем углу, выпадающий список «Направление столбца:»
Об этом следует помнить если выбран один и тот же цвет для положительных и отрицательных значений. Как вы заметили настройках отрицательного значения, так же изменяется цвет шкалы для отрицательных чисел.
В окне «Изменение правила форматирования» для пользователя предоставляются широкие возможности для настройки различных параметров. Например, если отметить галочкой пункт «Показать только столбец», то содержимое ячеек будет скрыто, будет видно только шкалы гистограмм.
Ниже нам предоставляется гибкая настройка для минимальных и максимальных значений. Из выпадающих списков выбираете опции: число, процент. А под ними вводите свои значения. При выборе же опции «формула» мы получаем неограниченные возможности для настройки данного параметра.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Представление данных в виде гистограммы
Excel для Microsoft 365 Outlook для Microsoft 365 Excel 2021 Outlook 2021 Excel 2019 Outlook 2019 Excel 2016 Outlook 2016 Excel 2013 Outlook 2013 Excel 2010 Outlook 2010 Excel 2007 Outlook 2007 Еще. Меньше
Гистограммы полезны для представления изменений данных с течением времени и для наглядного сравнения различных величин. В гистограммах категории обычно располагаются по горизонтальной оси, а значения — по вертикальной.
Сведения о гистограммах и о том, когда их следует использовать, см. в статье Типы диаграмм в Office.
Чтобы создать гистограмму, сделайте следующее:
- Введите данные в электронную таблицу.
- Выделите данные.
- В зависимости от используемой версии Excel выполните одно из указанных ниже действий.
- Excel 2016: на вкладке Вставка щелкните значок Вставить гистограмму или линейчатую диаграмму, а затем выберите нужный тип гистограммы.
- Excel 2013: на вкладке Вставка щелкните значок Вставить гистограмму, а затем выберите нужный тип гистограммы.
- Excel 2010 и Excel 2007: на вкладке Вставка щелкните значок Гистограмма, а затем выберите нужный тип гистограммы.
- Excel 2016: на вкладке Вставка щелкните значок Вставить гистограмму или линейчатую диаграмму, а затем выберите нужный тип гистограммы.
Вы можете при необходимости отформатировать диаграмму. Ниже описаны некоторые варианты.
Примечание: Перед применением форматирования нужно щелкнуть диаграмму.
- Чтобы изменить макет диаграммы, щелкните Конструктор >Макет диаграммы и выберите макет.
- Чтобы изменить стиль диаграммы, щелкните Конструктор >Стили диаграмм и выберите стиль.
- Чтобы применить другой стиль фигуры, щелкните Формат >Стили фигур и выберите стиль.
Примечание: Стили фигур отличаются от стилей диаграмм. Стиль фигуры — это форматирование, применяемое к границе диаграммы, а стиль диаграммы — это форматирование, которое применяется ко всей диаграмме.
Примечание: Если вы хорошо знакомы с диаграммами, вы также можете щелкнуть правой кнопкой мыши определенную область на диаграмме и выбрать параметр форматирования.
Чтобы создать гистограмму, сделайте следующее:
- В окне сообщения выберите Вставка >Диаграмма.
- В диалоговом окне Вставка диаграммы щелкните Гистограмма, выберите нужный тип гистограммы и нажмите кнопку ОК. В разделенном окне откроется лист Excel с примером данных.
- Замените их собственными данными.
Примечание: Если на диаграмме не отображаются данные листа, перетащите вертикальные линии вниз до последней строки таблицы.
- Щелкните значок Измените данные Microsoft Excel на панели быстрого доступа.
Лист откроется в Excel. - Сохраните лист.
Совет: Чтобы повторно открыть лист, щелкните Конструктор > Изменить данные и выберите нужный вариант.
Вы можете при необходимости отформатировать диаграмму. Ниже описаны некоторые варианты.
Примечание: Перед применением форматирования нужно щелкнуть диаграмму.
- Чтобы изменить макет диаграммы, щелкните Конструктор >Макет диаграммы и выберите макет.
- Чтобы изменить стиль диаграммы, щелкните Конструктор >Стили диаграмм и выберите стиль.
- Чтобы применить другой стиль фигуры, щелкните Формат >Стили фигур и выберите стиль.
Примечание: Стили фигур отличаются от стилей диаграмм. Стиль фигуры — это форматирование, применяемое к границе диаграммы, а стиль диаграммы — это форматирование, которое применяется ко всей диаграмме.
Примечание: Если вы хорошо знакомы с диаграммами, вы также можете щелкнуть правой кнопкой мыши определенную область на диаграмме и выбрать параметр форматирования.
Вы знали?
Если у вас нет подписки Microsoft 365 или последней Office, вы можете попробовать:
Показ тенденций изменения данных с помощью спарклайнов
Спарклайны — это небольшие диаграммы в ячейках листа, визуально представляющие данные. С помощью спарклайнов можно показывать тенденции в рядах значений (например, сезонные повышения и спады или экономические циклы) и выделять максимальные и минимальные значения. Чтобы добиться максимального эффекта, спарклайны следует располагать рядом с соответствующими данными.
Добавление спарклайна
- Выделите пустую ячейку в конце строки данных.
- На вкладке Вставка выберите тип спарклайна, например График или Гистограмма.
- Выделите ячейки в строке и нажмите кнопку ОК.
- Другие строки данных? Перетащите его, чтобы добавить спарклайн для каждой строки.
Форматирование спарклайн-диаграммы
- Выделите спарклайн-диаграмму.
- Выберите Спарклайн и выберите один из вариантов.
- Нажмите кнопку График, Гистограмма или Выигрыш/проигрыш, чтобы изменить тип диаграммы.
- Установите флажок Маркеры, чтобы выделить отдельные значения в спарклайн-диаграмме.
- Выберите стиль спарклайна в группе Стиль.
- Нажмите кнопку Цвет спарклайна и выберите цвет.
- Нажмите кнопку Цвет спарклайна и выберите пункт Толщина, чтобы выбрать ширину спарклайна.
- Нажмите кнопку Цвет маркера, чтобы изменить цвет маркеров.
- Если данные содержат положительные и отрицательные значения, нажмите кнопку Ось для отображения оси.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Использование гистограмм, цветовых шкал и наборов значков для выделения данных
Важно: Часть содержимого этого раздела может быть неприменима к некоторым языкам.
Гистограммы, цветовые шкалы и наборы значков представляют собой виды условного форматирования, которое заключается в применении визуальных эффектов к данным. Эти условные форматы упрощают сравнение значений в диапазоне ячеек.
Форматирование ячеек с использованием гистограмм
Гистограммы позволяют выделить наибольшие и наименьшие числа, например самые популярные и самые непопулярные игрушки в отчете по новогодним продажам. Более длинная полоса означает большее значение, а более короткая — меньшее.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная щелкните Условное форматирование.
- Выберите пункт Гистограммы, а затем выберите градиентную или сплошную заливку.
Совет: При увеличении ширины столбца с гистограммой разница между значениями ячеек становится более заметной.
Форматирование ячеек с использованием цветовых шкал
Цветовые шкалы могут помочь в понимании распределения и разброса данных, например доходов от инвестиций, с учетом времени. Ячейки окрашиваются оттенками двух или трех цветов, которые соответствуют минимальному, среднему и максимальному пороговым значениям.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная щелкните Условное форматирование.
- Наведите указатель на элемент Цветовые шкалы и выберите нужную шкалу. Верхний цвет означает наибольшие значения, средний цвет (при его наличии) — средние значения, а нижний — наименьшие.
Форматирование ячеек с использованием наборов значков
Наборы значков используются для представления данных в категориях числом от трех до пяти, разделенных пороговыми значениями. Каждый значок соответствует диапазону значений, а каждая ячейка обозначается значком, представляющим этот диапазон. Например, в наборе из трех значков один значок используется для выделения всех значений, которые больше или равны 67 %, другой значок — для значений меньше 67 % и больше или равных 33 %, а третий — для значений меньше 33 %.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная щелкните Условное форматирование.
- Наведите указатель на элемент Наборы значков и выберите набор.
Совет: Наборы значков можно сочетать с другими элементами условного форматирования.
Форматирование ячеек с использованием гистограмм
Гистограммы позволяют выделить наибольшие и наименьшие числа, например самые популярные и самые непопулярные игрушки в отчете по новогодним продажам. Более длинная полоса означает большее значение, а более короткая — меньшее.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная в разделе Формат щелкните Условное форматирование.
- Выберите пункт Гистограммы, а затем выберите градиентную или сплошную заливку.
Совет: При увеличении ширины столбца с гистограммой разница между значениями ячеек становится более заметной.
Форматирование ячеек с использованием цветовых шкал
Цветовые шкалы могут помочь в понимании распределения и разброса данных, например доходов от инвестиций, с учетом времени. Ячейки окрашиваются оттенками двух или трех цветов, которые соответствуют минимальному, среднему и максимальному пороговым значениям.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная в разделе Формат щелкните Условное форматирование.
- Наведите указатель на элемент Цветовые шкалы и выберите нужную шкалу. Верхний цвет означает наибольшие значения, средний цвет (при его наличии) — средние значения, а нижний — наименьшие.
Форматирование ячеек с использованием наборов значков
Наборы значков используются для представления данных в категориях числом от трех до пяти, разделенных пороговыми значениями. Каждый значок соответствует диапазону значений, а каждая ячейка обозначается значком, представляющим этот диапазон. Например, в наборе из трех значков один значок используется для выделения всех значений, которые больше или равны 67 %, другой значок — для значений меньше 67 % и больше или равных 33 %, а третий — для значений меньше 33 %.
- Выделите диапазон ячеек, таблицу или целый лист, к которому нужно применить условное форматирование.
- На вкладке Главная в разделе Формат щелкните Условное форматирование.
- Наведите указатель на элемент Наборы значков и выберите набор.