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

class="image">

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

Мы можем сделать любое значение итогом — такие значения отсчитываются от нуля на оси, а не от предыдущего значения, и у них другой цвет. В нашем случае итоги — это план и факт (можно делать и предыдущие итоги, если необходимо). Чтобы сделать точку данных итогов, щелкаем по ней правой кнопкой и в контекстном меню выбираем «Установить в качестве итога» (Set as Total).

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

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

Можно менять и цвет итоговых значений.

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

Эта диаграмма есть и в Google Таблицах.

Воронка (Funnel)

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

Дерево (Treemap)

Еще одна относительно новая диаграмма, которая позволяет показать структуру, но несколько иначе, чем стандартная круговая диаграмма, — это «Дерево» (Treemap). Она появилась в Excel 2016.

Здесь нельзя вставить в подписи доли и значения, как в круговой диаграмме, — только значения.

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

Парето (Pareto)

Помните про принцип Парето? «20% усилий дают 80% результата».

Если хотите визуально проанализировать данные и посмотреть, какой процент клиентов / филиалов / каналов продаж / продуктов и т. д. дает какой процент результата, можно использовать эту диаграмму, появившуюся в Excel 2019.

Можно построить эту диаграмму по данным из предыдущего примера.

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

СВОДНЫЕ ДИАГРАММЫ

Файл с примером: Сводные диаграммы.xlsx

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

Например, в следующем случае у нас несколько тысяч строк с данными об остатках.

Любая диаграмма, построенная непосредственно на основе этих данных, будет выглядеть абсурдно.

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

Если уже есть сводная таблица, можно вставить диаграмму (там же на ленте на вкладке «Вставка»).

Ну а самым быстрым способом будет опция «Рекомендуемые сводные таблицы» (она есть в Excel с 2013-й версии).

Если в данных есть что группировать, то Excel сам предложит в списке рекомендуемых сводные диаграммы.

После нажатия ОК вы получите сводную таблицу и диаграмму.

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

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

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

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

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

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

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

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

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

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

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

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

Если построить диаграмму