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

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

Каскадная диаграмма (Waterfall)

Эту диаграмму также называют водопадом (waterfall), факторной диаграммой. Ранее ее нужно было собирать с помощью формул и вспомогательных конструкций, а с Excel 2016 есть встроенный тип диаграммы.

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

Для построения каскадной диаграммы нужно подготовить данные: один столбец с подписями и один с данными (в нашем примере исходные данные в диапазоне A1:C5, а данные для диаграммы в E2:F6), в котором будет первоначальный показатель (в нашем случае план), далее влияние каждого показателя (мы вычитаем из факта план по каждой категории, чтобы вычислить, как она повлияла на общий результат, — например, по штуковинам вы недополучили 80 000) и, наконец, итоговый результат (общий факт).

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

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

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

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

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

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

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

Воронка (Funnel)

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

Дерево (Treemap)

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

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

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

Парето (Pareto)

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

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

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

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

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

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

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

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

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

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

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

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

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

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