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

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

В следующем примере отсортировали данные в таблице — это привело к упорядочиванию и на диаграмме.

Обратите внимание, что областям сводных таблиц строго соответствуют области сводной таблицы:

— ось X (горизонтальная в случае графиков, гистограмм) — это область строк сводной таблицы;

— ось Y (вертикальная) — это область значений сводной таблицы;

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

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

Диаграмма будет выглядеть так:

Придется поменять столбцы и строки местами: либо в настройках полей сводной таблицы, либо на вкладке «Конструктор» (при выделенной диаграмме) — там есть опция «Строка/столбец» (Switch Row / Column), как и у обычных диаграмм.

Кнопки полей сводной диаграммы выводятся на печать. Если вы хотите это исключить (или просто хотите, чтобы они не отображались в Excel) — отключите их на вкладке «Анализ сводной диаграммы».

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

Зато в Google Таблицах можно сгруппировать по полю в настройках обычной диаграммы. Допустим, у нас данные по регионам и округам.

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

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

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

^{https://mif.to/ymnBE}

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

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

Линия уровня

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

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

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

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

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

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

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

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