Кнопки полей сводной диаграммы выводятся на печать. Если вы хотите это исключить (или просто хотите, чтобы они не отображались в Excel) — отключите их на вкладке «Анализ сводной диаграммы».
В Google Таблицах нет сводных диаграмм. Конечно, можно построить сводную, потом выделить ее ячейки и построить диаграмму, но источником данных для такой диаграммы станет выступать конкретный диапазон, связи со сводной таблицей не будет. Если вы поменяете ее структуру или добавятся новые элементы, диаграмма по-прежнему будет строиться на основе тех же ячеек (то есть в нее могут попасть не все значения, картинка будет искажена).
Зато в 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).
Для установки связи с ячейкой (Cell link) необходимо щелкнуть правой кнопкой по объекту и выбрать «Формат объекта» (Format Control).
Теперь при изменении флажка будет меняться и значение в ячейке J1.
Остается создать аналогичные флажки для каждого ряда данных (региона) в нашей таблице.
Теперь у нас есть четыре ячейки, в которых отражается выбор регионов.
Остается создать дополнительную таблицу, в которой будут не все данные (как в A1:E13), а только те, которые мы выбрали. Для этого можно, как мы делали в одном из предыдущих примеров, воспользоваться функциями ЕСЛИ / IF и НД / NA.
Теперь можно построить диаграмму по созданному диапазону A15:E27, в котором отображаются только выбранные данные.
При снятии и установке флажков на диаграмме теперь будут отображаться соответствующие ряды данных (регионы).
Значения ИСТИНА / ЛОЖЬ можно скрыть с помощью форматов или белого шрифта: они не нужны и не являются частью интерфейса, для пользователя здесь предназначены флажки, а значения в ячейках нужны для формул.
А сами флажки можно для большей наглядности передвинуть на диаграмму — к легенде. В таком случае текст на флажках можно удалить — достаточно будет подписей легенды.
Power Map (3D-карты)
Файл с примером: Строим модель данных.xlsx
Power Map — это надстройка, которую можно включить вместе с Power Pivot, если они доступны в вашей версии Excel. С ее помощью можно визуализировать географические данные на объемной карте Земли. Данными для такой визуализации выступает все, что загружено (и связано) в модели данных книги Excel (Power Pivot).
3D Maps — отдельное окно, в котором есть аналог ленты инструментов Excel, список обзоров (слева) и настройки диаграммы — тип и данные, которые используются (слева).
Если мы хотим визуализировать данные на карте, то нужно, чтобы хотя бы одно поле в модели данных (хотя бы один столбец в одной из таблиц) содержало данные, определяющие географическую привязку, например адрес, город, индекс, координаты.
Этот столбец нужно перенести в «Расположение» (Location).
Например, город.
Пока города просто отмечены на карте, нужно добавить количественный показатель, который будет определять высоту столбцов на гистограмме (или размер пузырьков, если будет выбрана пузырьковая диаграмма).
Если вы хотите смотреть на структуру (например, на продажи в разных типах магазинов: в нашем примере есть просто книжные магазины и есть магазины с кафе), добавьте соответствующее поле в область «Категория» (Category).
Обратите внимание, что ниже есть категория «Время» (Time). Если у вас есть поле с датами и/или временем, их можно добавить туда — и появится возможность воспроизводить изменение данных в динамике, наблюдая, как меняется отображаемый показатель.