В условном форматировании Excel есть не только «обычные» правила -- числа больше N, текст содержит «…», но и графические объекты — гистограммы и значки, и цветовые шкалы. На мой взгляд, гистограммы — очень полезный инструмент, зачастую можно обойтись ими для визуализации, не прибегая к обычным диаграммам.
Расскажу про несколько хитростей и нюансов.
Расскажу про несколько хитростей и нюансов.
Гистограммы (Data Bars) — горизонтальные столбики, которые отображаются в ячейках с числовыми данными. Просто выделяем диапазон, идем в «Условное форматирование» и выбираем гистограммы по вкусу — сплошные или полупрозрачные. Уже при наведении курсора на один из 12 вариантов вы будете видеть своего рода «предпросмотр» по выделенным ячейкам:
Как работают гистограммы?
Для самого большого значения в диапазоне гистограмма будет занимать всю ячейку по ширине. Для остальных ширина будет зависеть от значения — это будет доля от максимального. То есть если в диапазоне самое большое число — 1000, то для него гистограмма будет во всю ширину столбца, а для числа 500 гистограмма будет ровно до середины ячейки.
Можно ли это изменить? В настройках правила форматирования. «Управление правилами» в Условном форматировании и там выбираем нужное правило в списке — «Изменить правило»:
Для самого большого значения в диапазоне гистограмма будет занимать всю ячейку по ширине. Для остальных ширина будет зависеть от значения — это будет доля от максимального. То есть если в диапазоне самое большое число — 1000, то для него гистограмма будет во всю ширину столбца, а для числа 500 гистограмма будет ровно до середины ячейки.
Можно ли это изменить? В настройках правила форматирования. «Управление правилами» в Условном форматировании и там выбираем нужное правило в списке — «Изменить правило»:
И там мы увидим вариант по умолчанию — минимум и максимум определяются автоматически:
А мы, допустим, по другому хотим! Например, чтобы минимальное значение было вообще без гистограммы. Открываем выпадающий список «Тип» и выбираем там вариант «Минимальное значение». Получается, что теперь гистограммы не от нуля до максимального в диапазоне, а от минимального до максимального:
Раньше 20 897 соответствовала гистограмма в 40% от ширины ячейки. Потому что 20 897 = 40% от максимума в диапазоне (52 049). А теперь 20 897, минимальное значение, является точкой отсчета, и для него гистограмма вообще не отображается.
Мы можем также задавать свою собственную планку — точное число. Например, решим, что 60 000 в месяц является для нас мечтой и идеалом и только для этого числа гистограмма имеет право быть во всю ширину столбца:
Числа, кстати, можно брать и их ячеек — можно будет менять мин и макс в этих ячейках, не трогая само правило:
Такой вариант - "Число" - можно использовать, чтобы сделать своего рода прогресс-бар с помощью гистограммы условного форматирования, отслеживать выполнение плана (или сколько прошло в периоде). Если у нас есть процентное значение (от 0 до 1, иначе говоря, от 0 до 100%), можно поставить максимум, равный единице:
В этом примере формула возвращает то, сколько прошло от текущего года. Функция ДОЛЯГОДА / YEARFRAC возвращает число лет между двумя датами. Если даты в одном году — то это и будет доля года.
Формулы в мин/макс
В списке вариантов есть не только число, но и формула. То есть минимальные (такое, что гистограммы нет) и максимальные (такое, что гистограмма будет занимать всю ячейку) значения могут вычисляться. Например, если вам не нравится, что для минимального значения в диапазоне гистограмма совсем исчезает (если задать это самое значение как минимум, иначе говоря — определить, что гистограмма должна исчезать для минимального значения в диапазоне), можно в качестве минимума взять число поменьше. Например, так:
=МИН (диапазон) * 0,9
То есть находим минимальное число в диапазоне и умножаем на что-то меньше единицы (коэффициент подберите по вкусу, чем ближе к единице — тем крохотнее будет гистограмма), и тогда даже для минимального значения небольшая гистограмма будет:
В списке вариантов есть не только число, но и формула. То есть минимальные (такое, что гистограммы нет) и максимальные (такое, что гистограмма будет занимать всю ячейку) значения могут вычисляться. Например, если вам не нравится, что для минимального значения в диапазоне гистограмма совсем исчезает (если задать это самое значение как минимум, иначе говоря — определить, что гистограмма должна исчезать для минимального значения в диапазоне), можно в качестве минимума взять число поменьше. Например, так:
=МИН (диапазон) * 0,9
То есть находим минимальное число в диапазоне и умножаем на что-то меньше единицы (коэффициент подберите по вкусу, чем ближе к единице — тем крохотнее будет гистограмма), и тогда даже для минимального значения небольшая гистограмма будет:
Не применяйте гистограммы к итогам / промежуточным итогам
Предположим, у нас в данных есть итоги по кварталам:
Предположим, у нас в данных есть итоги по кварталам:
Если выделить все числа и вставить гистограммы, будет вот так:
Конечно, данные по кварталам будут априори больше, чем по месяцам. Мы сравниваем целое и его части, что не очень правильно. В такой ситуации лучше зажать Ctrl и выделить (если не хочется вообще менять структуру отчета и куда-то убрать кварталы отдельно) только месяцы, а потом вставить гистограммы:
Или выделить только ячейки с кварталами — почему нет?
Гистограммы для отрицательных значений
Гистограммы можно применять и к отрицательным числам. По умолчанию к положительным будет применяться тот цвет, который вы выбрали при вставке гистограмм, а к отрицательным красным. То есть во многих случаях логично будет вставить зеленую:
Гистограммы можно применять и к отрицательным числам. По умолчанию к положительным будет применяться тот цвет, который вы выбрали при вставке гистограмм, а к отрицательным красным. То есть во многих случаях логично будет вставить зеленую:
Но в любом случае цвета можно менять. Для этого нужно зайти в параметры конкретного правила.
Здесь можно поменять положительный цвет, а для изменения отрицательного нужно нажать на соответствующую кнопку ("Отрицательные значения и ось..."), откроется отдельное окно:
Ну, а еще можно отключать отображение чисел и оставить только сами гистограммы. Для этого в том же окне «Изменение правила форматирования» включите флажок «Показывать только столбец», и получится такая картинка:
Формулы никуда не делись и продолжают вычисляться, то есть гистограммы будут изменяться вслед за числами в столбцах «План» и «Факт».
Гистограммы в нескольких столбцах
Допустим, у нас данные в нескольких столбцах, и мы вставили гистограммы, выделив сразу оба столбца:
В чем тут проблема? Когда мы применяем гистограммы в каком-то диапазоне, Excel сам определяет максимальное число — для него гистограмма будет занимать весь столбец по ширине. А остальные будут относительно этого максимального значения.
Но ширина столбика зависит и от ширины столбца на листе Excel! Столбец с Санкт-Петербургом намного шире и поэтому гистограммы в нем больше.
Рассмотрим на примере июня. Во всей таблице максимум это февраль в Москве, 19 813. Вся ячейка залита. Июнь в Москве — 85% от максимума, залита соответствующая часть от ячейки. А в Санкт-Петербурге в июне только 53% от максимума, чуть больше половины ячейки. Но эта ячейка в широком столбце! Поэтому гистограмма много больше, чем у сопоставимых чисел в Москве.
Поэтому единственно верный вариант в такой ситуации — делать столбцы одинаковыми. Выделяем все и меняем ширину, чтобы она была одинаковой у всех.
Но ширина столбика зависит и от ширины столбца на листе Excel! Столбец с Санкт-Петербургом намного шире и поэтому гистограммы в нем больше.
Рассмотрим на примере июня. Во всей таблице максимум это февраль в Москве, 19 813. Вся ячейка залита. Июнь в Москве — 85% от максимума, залита соответствующая часть от ячейки. А в Санкт-Петербурге в июне только 53% от максимума, чуть больше половины ячейки. Но эта ячейка в широком столбце! Поэтому гистограмма много больше, чем у сопоставимых чисел в Москве.
Поэтому единственно верный вариант в такой ситуации — делать столбцы одинаковыми. Выделяем все и меняем ширину, чтобы она была одинаковой у всех.
Гистограммы в сводных таблицах
В сводных тоже можно вставлять объекты условного форматирования, в том числе и гистограммы.
Не нужно выделять заранее все значения в нужном поле сводной — просто выделите одну ячейку и вставьте гистограмму нужного цвета. После чего справа появится кнопка (смарт-тэг) — там будет три варианта:
В сводных тоже можно вставлять объекты условного форматирования, в том числе и гистограммы.
Не нужно выделять заранее все значения в нужном поле сводной — просто выделите одну ячейку и вставьте гистограмму нужного цвета. После чего справа появится кнопка (смарт-тэг) — там будет три варианта:
По умолчанию — «к выделенным ячейкам». Если выбрать второй вариант, то гистограммы будут везде, включая общий итог, что не очень корректно, как мы обсуждали выше:
Конечно, если бы у нас была не сумма, а среднее, допустим, то можно и к итогу тоже применить гистограмму. Но не тут. Так что выберем третий вариант — это будут гистограммы только на уровне компаний, без общего итога:
Такой вариант пригодится и в случаях, когда у вас несколько уровней в области строк сводной таблицы:
Здесь на первом уровне клиент, а на втором канал продаж. Если применить ко всему полю (выбрав второй вариант в списке после вставки одной гистограммы либо выделив сразу все ячейки подряд), то опять получится, что мы сравниваем части целого с целым. Если мы хотим сравнивать только значения второго уровня, вставляем гистограмму в любую ячейки с таким значением:
