Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

по субъектам, то в ней будет слишком много элементов.

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

Таблица с примером: Группировка в диаграмме

https://mif.to/ymnBE

Интерактивные диаграммы

Файл с примерами: Интерактивные диаграммы.xlsx

Линия уровня

Чтобы добавить к диаграмме какую-то фиксированную линию (например, линию с планом), достаточно добавить столбец, в который нужно вставить одно и то же число, на уровне которого и должна проходить линия.

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

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

Чтобы избавиться от «пустого» пространства на диаграмме (у нас нет значений меньше 10 000, а ось начинается с нуля), можно изменить минимальное значение по вертикальной оси в формате этой самой оси (чтобы туда попасть, нужно выделить ось мышкой и нажать Ctrl + 1).

Выделение значений

На диаграммах можно изменять цвет заливки и другие параметры форматирования отдельных точек данных. За счет этого можно визуально выделить отдельные параметры/регионы/периоды.

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

После выделения точки данных можно нажать Ctrl + 1, чтобы открыть боковую панель форматирования «Формат точки данных» (Format Data Point), в ней можно настроить все параметры форматирования, например заливку.

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

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

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

Для этого нужно добавить столбец к исходным данным, в нем необходимо с помощью формул оставить только те значения, что мы будем выделять, и заменить те значения, что выделять не планируется, значением #Н/Д (#N/A).

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

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

И меняем ее тип на «Комбинированную» (Combo). Сделаем оба ряда данных одинакового типа — «Гистограмма» (Clustered Column). Таким образом, они будут накладываться друг на друга. Если во втором ряду нет значений, то будет отображаться первый (синего цвета в нашем примере). Если во втором ряду есть значения (а есть они, напомним, если значение соответствует условию) — они будут отображаться поверх первого ряда своим (оранжевым в нашем примере) цветом. И возникнет ощущение выделения.

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

Теперь при изменении уровня в ячейке E1 на диаграмме автоматически будут выделяться цветом значения выше этого уровня.

Выделение периода

Допустим, у нас есть данные по продажам в кафе Штруделя, и мы построили диаграммы (гистограмму) с продажами за несколько месяцев.

Часть этого периода — карантин. И мы хотим выделить этот период фоном определенного цвета.

Для этого нужен дополнительный столбец, в который мы вставим числа, примерно соответствующие верхней планке по оси в нашем графике (в нашем примере это 14 000).

После этого достаточно добавить новый столбец в диаграмму и изменить ее тип на комбинированный (Combo), чтобы сделать новый столбец диаграммой типа «С областями» (Area).

Высота оси автоматически изменится до 16 000, но ее можно всегда изменить обратно на 14 000 в формате оси, чтобы оранжевая область доходила до самого верха диаграммы.

Флажки (отключение рядов)

Если мы хотим включать и отключать определенные ряды данных на диаграмме, чтобы формировать набор для сравнения (например, выбирать, какие два или три региона из четырех сравнивать друг с другом на графике), можно воспользоваться элементами управления — флажками (Check Box).

Будем рассматривать задачу на примере данных по четырем регионам.

Нам необходимы четыре «переключателя» — флажка, нажимая на которые мы будем включать или выключать определенные регионы на диаграмме.

Флажки, как и другие элементы управления, вставляются на вкладке «Разработчик» (Developer). Если в вашем Excel она не отображается на ленте, ее нужно включить в параметрах Excel.

Флажки вставляются в группе «Элементы управления» — кнопка «Вставить» (Controls — Insert).

После вставки флажка его можно переименовать, чтобы надпись на нем соответствовала региону (щелчок правой кнопкой мыши по флажку — «Изменить текст», Edit Text).

И далее необходимо связать флажок с какой-нибудь ячейкой — это позволит отображать его текущее значение (снят/установлен) в виде логических значений ИСТИНА / ЛОЖЬ (TRUE / FALSE).