Новые диаграммы в Excel 2016

Рубрика: 7. Полезняшки Excel

Microsoft очень давно не добавлял в Excel новые типы диаграмм, и вот, наконец в версии 2016 появилось целых шесть новых диаграмм! В предыдущих версиях некоторые из них были в принципе недоступны, а для построения других приходилось совершать шаманские действия. Сейчас же всё происходит в несколько щелчков мыши. Итак, представляю вашему вниманию (рис. 1):

  • Дерево
  • Солнечные лучи
  • Гистограмма (два типа)
  • Ящик с усами
  • Каскадная

%d1%80%d0%b8%d1%81-1-%d0%bd%d0%be%d0%b2%d1%8b%d0%b5-%d1%82%d0%b8%d0%bf%d1%8b-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc-%d0%b2%d1%8b%d0%b4%d0%b5%d0%bb%d0%b5%d0%bd%d1%8b

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 1. Новые типы диаграмм (выделены)

Не спрашивайте, почему «дерево», или почему MS дважды использует тип «гистограмма»… 🙂

Дерево

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

%d1%80%d0%b8%d1%81-2-%d0%b8%d0%b5%d1%80%d0%b0%d1%80%d1%85%d0%b8%d1%87%d0%b5%d1%81%d0%ba%d0%b0%d1%8f-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-treemap-chart-%d0%be%d0%bd%d0%b0-%d0%b6

Рис. 2. Иерархическая диаграмма (Treemap Chart), она же дерево в переводе MS на русский язык

Вот что пишет о такого рода диаграммах Найтон Яу в книге Искусство визуализации в бизнесе: «В 1990 году Бен Шнайдерман из Университета Мэриленда придумал тримап. Этот способ визуализации основан на областях, при котором размер каждого прямоугольника выражает собой количественный показатель. Внешние прямоугольники представляют родительские категории, а прямоугольники внутри родительских категорий — это своего рода подкатегории. Вы можете использовать тримап для визуализации прямых пропорций, но, чтобы опробовать технологию в полном объеме, ее лучше применять с иерархическими или, точнее, с древовидно структурированными данными». Вот почему MS назвал этот тип диаграмм деревом

Исходная область (в нашем примере А2:С20) не требует повторения заголовков первого уровня (можно указать Финансы только в ячейке А3, оставив ячейки А4 и А5 пустыми). Зато обязательно нужно отсортировать данные по столбцу А. Если строки, относящиеся к рубрике Финансы, расположить не подряд, Excel на диаграмме создаст несколько таких областей. К сожалению, этот тип диаграмм не совместим со сводными таблицами. Также в отличие от большинства других диаграмм не поддерживается связывание текста диаграммы с ячейками.

Солнечные лучи

Этот вид диаграмм также визуализирует иерархию элементов, поэтому в меню он сгруппирован вместе с деревом (рис. 3).

%d1%80%d0%b8%d1%81-3-%d0%b4%d0%b2%d0%b0-%d1%82%d0%b8%d0%bf%d0%b0-%d0%b8%d0%b5%d1%80%d0%b0%d1%80%d1%85%d0%b8%d1%87%d0%b5%d1%81%d0%ba%d0%b8%d1%85-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc

Рис. 3. Два типа иерархических диаграмм

В качестве примера я взял данные по населению РФ в разрезе федеральных округов и субъектов федерации (рис. 4).

%d1%80%d0%b8%d1%81-4-%d1%81%d0%b0%d0%bc%d1%8b%d0%b5-%d0%ba%d1%80%d1%83%d0%bf%d0%bd%d1%8b%d0%b5-%d0%bf%d0%be-%d0%bd%d0%b0%d1%81%d0%b5%d0%bb%d0%b5%d0%bd%d0%b8%d1%8e-%d1%81%d1%83%d0%b1%d1%8a%d0%b5

Рис. 4. Самые крупные (по населению) субъекты федерации

Если в таблице данные отсортированы по округам (в алфавитном порядке), а в каждом округе по населению отдельных субъектов (строка Другие всегда указана последней), то на диаграмме солнечные лучи (рис. 5) федеральные округа автоматически сортируются по совокупному населению, начиная с 12 часов, и по часовой стрелке. Строка Другие сортируется вместе с иными строками внутри округов. Я не нашел возможности, как не показывать строки Другие. Поэтому последовательно выделил соответствующие сектора и отформатировал Нет заливки.

%d1%80%d0%b8%d1%81-5-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d1%81%d0%be%d0%bb%d0%bd%d0%b5%d1%87%d0%bd%d1%8b%d0%b5-%d0%bb%d1%83%d1%87%d0%b8

Рис. 5. Диаграмма солнечные лучи; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Частотная гистограмма

В качестве примера я взял ежедневный курс доллара ЦБ РФ (рис. 6) выбрал только ряд В и выполнил команду Вставить –> Гистограмма –> Гистограмма (рис. 7)

%d1%80%d0%b8%d1%81-6-%d0%ba%d1%83%d1%80%d1%81-%d0%b4%d0%be%d0%bb%d0%bb%d0%b0%d1%80%d0%b0-%d1%86%d0%b1-%d1%80%d1%84

Рис. 6. Курс доллара ЦБ РФ

%d1%80%d0%b8%d1%81-7-%d0%b2%d1%81%d1%82%d0%b0%d0%b2%d0%b8%d1%82%d1%8c-%d0%b3%d0%b8%d1%81%d1%82%d0%be%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%83

Рис. 7. Вставить Гистограмму

В предыдущих версиях Excel, чтобы построить частотную гистограмму сначала требовалось обработать исходные данные, например, с помощью функции ЧАСТОТА или сводных таблиц (подробнее см. Функция массива ЧАСТОТА и Группировка данных сводной таблицы в Excel 2013, раздел Группировка числовых полей). Сейчас же данные могут быть расположены в произвольном порядке. Частотная диаграмма по умолчанию, выглядит не самым привлекательным образом (рис. 8).

%d1%80%d0%b8%d1%81-8-%d1%87%d0%b0%d1%81%d1%82%d0%be%d1%82%d0%bd%d0%b0%d1%8f-%d0%b3%d0%b8%d1%81%d1%82%d0%be%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d0%bf%d0%be-%d1%83%d0%bc%d0%be%d0%bb%d1%87%d0%b0%d0%bd

Рис. 8. Частотная гистограмма по умолчанию

Я выбрал иной стиль диаграммы, а также «поколдовал» с настройками диапазонов (рис. 9). Получилось более читабельно (рис. 10).

%d1%80%d0%b8%d1%81-9-%d0%bd%d0%b0%d1%81%d1%82%d1%80%d0%be%d0%b9%d0%ba%d0%b8-%d0%bf%d0%b0%d1%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80%d0%be%d0%b2-%d0%be%d1%81%d0%b8-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be

Рис. 9. Настройки параметров оси диапазонов (оси абсцисс)

%d1%80%d0%b8%d1%81-10-%d0%be%d1%82%d1%84%d0%be%d1%80%d0%bc%d0%b0%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd%d0%bd%d0%b0%d1%8f-%d1%87%d0%b0%d1%81%d1%82%d0%be%d1%82%d0%bd%d0%b0%d1%8f-%d0%b3%d0%b8%d1%81

Рис. 10. Отформатированная частотная гистограмма

Диаграмма Парето

Аналог частотной диаграммы. В диаграмме Парето, как правило, используются не числовые диапазоны, а категории. Например, причины возникновения дефектов (рис. 11). Категории могут располагаться в произвольном порядке. Допускается несколько строк на одну и ту же категорию. А вот переместить категорию Прочие в конец списка, мне не удалось. Диаграмма Парето является комбинированной: наряду с частотной диаграммой присутствует кумулятивная кривая накопленной доли категорий (в нашем примере – накопленная доля дефектов). Для этой кривой используется вторая ось ординат – справа. Так же, как и другие диаграммы, представленные ранее, не поддерживается связывание текста диаграммы с ячейками.

%d1%80%d0%b8%d1%81-11-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d0%bf%d0%b0%d1%80%d0%b5%d1%82%d0%be

Рис. 11. Диаграмма Парето

Ящик с усами

Используется для визуализации сводок статистических данных, характеризующих выборку. В предыдущих версиях приходилось исхитрятся и использовать для этих целей биржевые диаграммы, предварительно проводя анализ (подробнее см. Биржевая диаграмма, она же блочная, она же ящичная). Сейчас же исходные данные могут быть расположены в произвольном порядке, и Excel самостоятельно выдаст 6 сводок выборки: среднее арифметическое, медиану, первый и третий квартили, минимум и максимум, а также покажет отдельные выбросы (рис. 12; подробнее см. Диаграмма «ящик с усами» в Excel 2016).

%d1%80%d0%b8%d1%81-12-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d1%8f%d1%89%d0%b8%d0%ba-%d1%81-%d1%83%d1%81%d0%b0%d0%bc%d0%b8

Рис. 12. Диаграмма ящик с усами

Каскадная

Используется для отображения приращений (изменений) параметра. Наиболее любима финансовыми аналитиками для отображения кеш-флоу за период (рис. 13).

%d1%80%d0%b8%d1%81-13-%d0%ba%d0%b0%d1%81%d0%ba%d0%b0%d0%b4%d0%bd%d0%b0%d1%8f-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0

Рис. 13. Каскадная диаграмма

 


Прокомментировать