0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Эту удобную функцию можно найти на вкладке Главная в области Стили под одноименным значком:

Пиктограмма условного форматирования

Создать правило

Excel открывает следующее меню при нажатии соответствующей кнопки на ленте:

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

При выборе «Создать правило» появится окно:

Окно создания правил

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

Виды условного форматирования

Форматировать все ячейки на основании их значений

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

Гистограмма

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

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

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

Настройка гистограммы

  • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
  • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
  • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
  • Направление столбца – определяет способ направленности (слева направо либо наоборот);
  • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
    • Задайте цвет заливки столбца и его края или сделайте их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они разные)
    • Задайте положение оси или одинаковое направление для всех значений
    Цветовые шкалы

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

    Цветовая шкала

    В качестве примера рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от двухцветной.

    Параметры цветовой шкалы

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

    • Минимальное число — ноль, при этом более низкие значения будут иметь одинаковый цвет и насыщенность;
    • Промежуточное значение — единица и желтый цвет. Это означает, что переход шкалы от красного к желтому будет находиться в диапазоне от 0 до 1;
    • 4 — максимальное значение. Все, что выше этого, получает те же настройки. Переход от желтого к зеленому находится в диапазоне от 1 до 4.

    Пример цветовой шкалы

    Наборы значков (флажков)

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

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

    Форматировать только ячейки, которые содержат

    Условное форматирование отличается от первого тем, что перед форматированием необходимо соблюсти определенное правило.

    Форматирование по содержанию

    Ознакомьтесь с правилами, доступными в данном разделе:

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

    Форматировать только первые и последние значения

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

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

    Формула в условном форматировании

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

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

    Пример условия по формуле

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

    Воспользуемся двумя условиями со следующими формулами:

    • Если товара на складе нет, т.е. его количество равно 0, то позиция заказа выделяется красным цветом — =VPR(D3;A:B;2;FALSE)=0;
    • Если товар на складе есть, но его количество меньше количества, указанного в позиции заказа, то он выделяется желтым цветом — =A(VPR(D3;$A:$B;2;FALSE)0).

    Теперь настало время выбрать нужный диапазон и создать необходимые правила.

    Форматирование по формуле

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

    Остальные правила

    Ничего не было сказано о двух других типах правил, а именно

    • Форматирование на основе среднего значения — полное название «Форматировать только значения выше или ниже среднего»;
    • Форматирование единичных или повторяющихся значений.

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

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

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

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

    Диспетчер правил

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

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

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

    Теперь вы можете использовать стрелки в окне, чтобы изменить положение отдельного выделенного правила и, соответственно, его важность.

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

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

    Многие сталкивались с проблемой, когда внешний вид диаграммы казался простым для построения, но оказывалось на деле это не так. Гистограмма (или Линейчатая) с накоплением — казалось бы что может быть проще? Однако, подписать итоговые значения на раз-два удается далеко не каждому, т.к. стандартно в программе это пока (может, будет когда-нибудь) не предусмотрено. Здесь есть маленький секрет!

    column1.png

    В начальной таблице подсчитываются общие суммы, например:

    column2.png

    На основе данных таблицы строится следующая гистограмма:

    • Выделить всю таблицу с исходными данными.
    • На вкладке Вставка [Insert] в группе Диаграммы [Charts] выбрать Вставить гистограмму [Insert Column Chart] и тип Гистограмма с накоплением [Stacked Column]. undefined
    • На вкладке Конструктор [Design] в группе Данные [Data] воспользоваться командой Строка/Столбец [Switch Row/Column].

    Теперь необходимо настроить внешний вид диаманта:

    • Изменить тип диаграммы для ряда Итого – щелкнуть правой кнопкой мыши по ряду и выбрать Изменить тип диаграммы для ряда [Change Series Chart Type], сменить на тип диаграммы на График — График [Line – Line].
    • Выделить ряд Итого и задать цвет линии Нет контура [No Outline]
    • Добавить подписи данных – выделить диаграмму, нажать кнопку Элементы диаграммы [Chart Elements] и выбрать Подписи данных [Data Labels], затем В центре[Center].
    • Изменить расположение подписей данных для ряда Итого – щелкнуть правой кнопкой мыши по подписи данных и выбрать Формат подписей данных [Format Data Labels]. В Параметрах подписи [Label options] в группе Положение метки [Label Position] задать Сверху[Top].
    • Удалить из легенды Итого – выделить в легенде Итого и нажать Delete.
    Оставьте комментарий!

    На сообщение “Гистограмма с накоплением. Подписываем итоговое значение.” комментариев 17

    1. Люба :
      29.03.2015 (15:35)

    Если все делать так, как написано, это очень просто. Большое спасибо за то, что поделились!

    Не работает, пишет, что изменить тип диаграммы невозможно

    Norton, посмотрите внимательно, есть подозрение, что вы используете объемную гистограмму с накоплением, НО для начала следует выбрать плоскую гистограмму.

    Я тоже сначала хотела сделать более красивый объем, но потом увидела, что надо делать плоский. Спасибо!

    Как быстро и легко вы это делаете, но теперь я тоже это делаю, благодаря вам!

    Спасибо, у меня все получилось 🙂 🙂 🙂 🙂 🙂 🙂 🙂 .

    Простое и элегантное решение

    Спасибо, это пригодится мне в работе.

    Это прекрасное дополнение к существующим вариантам графиков.

    Мне нравятся четкие схемы

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

    Спасибо за помощь. Я люблю составлять диаграммы, особенно когда они простые.

    Можно построить сразу комбинированный график

    Где же Вы были раньше… Я бы столько не мучился 🙂

    Тамара, вы можете создать комбо только из версии 2013 года, но многие работают над версиями 2010 и 2007!

    Ух ты! Где вы были раньше? Закончатся ли мои страдания с подобными графиками? 🙂 🙂 🙂 🙂 🙂

    голоса
    Рейтинг статьи
    Читайте так же:
    Как вставить гистограмму в ячейку в Excel?
Ссылка на основную публикацию
Adblock
detector