Как сделать точечную диаграмму в excel

Точечная диаграмма со стрелками «было-стало»

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

• Изменение уровня доходов (X) и продолжительности жизни (Y) для разных стран за последние два года.
• Изменение количества клиентов (X) и среднего чека (Y) заказа ресторанов
• Соотношение стоимости компании (X) и количества сотрудников в ней (Y)
• .

Если что-то похожее встречается и в вашей практике, то имеет смысл разобраться, как такую красоту построить.

Про пузырьковые диаграммы (даже анимированные) я уже неоднократно писал. Точечная диаграмма (XY Scatter Chart) — это частный случай пузырьковой (Bubble Chart), но без третьего параметра — размера пузырьков. Т.е. каждая точка на графике описывается всего двумя параметрами: X и Y. Таким образом, построение начинается с подготовки исходных данных в виде двух таблиц:

Сначала построим то, что «было». Для этого выделим диапазон A3:C8 и выберем на вкладке Вставка (Insert) команду Рекомендуемые диаграммы (Recommended Charts) , а затем перейдем на вкладку Все диаграммы (All charts) и выберем тип Точечная (XY Scatter Chart) :

После нажатия на ОК получим заготовку нашей диаграммы.

Теперь добавим на неё данные из второй таблицы «Стало». Проще всего это сделать копированием. Для этого выделим диапазон E3:F8, скопируем его и, выделив диаграмму, выполним в неё специальную вставку через Главная — Вставить — Специальная вставка (Home — Paste — Paste Special) :

В появившемся окне выберем соответствующие опции вставки:

После нажатия на ОК на нашей диаграмме появится второй комплект точек («стало»):

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

Обратите внимание как она устроена:

• строки из исходных таблиц попарно чередуются, фиксируя начало и конец каждой стрелки
• каждая пара отделена от других пустой строкой, чтобы на выходе получились отдельные стрелки, а не одна большая
• если данные могут изменяться в будущем, то имеет смысл использовать не числа, а ссылки на исходные таблицы, т.е. в ячейке H4 ввести формулу =B4, в ячейку H5 ввести формулу =E4 и т.д.

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

После нажатия на ОК на диаграмме появятся новые точки начала и конца каждой стрелки (у меня они серого цвета), закрывающие уже построенные синие и оранжевые. Щелкнем по ним правой кнопкой мыши и выберем команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В открывшемся окне для исходных рядов «было» и «стало» оставим тип Точечная, а для ряда «стрелок» зададим Точечная с прямыми отрезками:

После нажатия на ОК наши точки «было» и «стало» будут соединены прямыми отрезками. Останется щелкнуть по ним правой кнопкой мыши и выбрать команду Формат ряда данных (Format Data Series) , а затем настроить параметры линий: толщину, тип стрелки и их размеры:

Для наглядности ещё неплохо бы добавить названия товаров. Для этого:

1. Щелкните по любой точке и выберите в контекстном меню Добавить подписи данных (Add Data Labels) — добавятся числовые подписи точек
2. Щелкните по подписям правой и выберите команду Формат подписей (Format Labels)
3. В открывшейся панели включите флажок Значения из ячеек (Values from cells) , нажмите кнопку Выбрать диапазон и выделите названия товаров (A4:A8).

Вот и всё — пользуйтесь 🙂

Ссылки по теме

• Что такое пузырьковая диаграмма, как её читать и построить в Excel
• Как сделать анимированную пузырьковую диаграмму
• Несколько способов построения диаграмм вида «План-Факт» в Excel

Как в Excel построить точечную диаграмму

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

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

Рис. 1. Точечная диаграмма

Диаграммы точечного типа достаточно просто создавать в программе Excel. Сначала выделите ячейки диапазона В2-С9 (рис. 2) и щелкните на кнопке X,Y (Scatter) (Точечная) вкладки Insert (Вставка). Затем щелкните на значке, который расположен в верхнем левом углу появившегося меню.

Рис. 2. Создание точечной диаграммы

Присвойте этой диаграмме, а также горизонтальной и вертикальной осям названия, как мы это делали в примере с диаграммой Парето.

Если вы хотите распечатать только точечную диаграмму, выберите ее щелчком мыши. Затем в меню кнопки Файл выберите команду Print → Quick Print (Печатать → Быстрая печать) (рис. 3). Для того чтобы убедиться в том, что на печатающее устройство будет отправлено именно то, что вам требуется, выберите в меню кнопки Файл команду Print → Print Preview (Предварительный просмотр). Как альтернативный вариант нажмите комбинацию клавиш Ctrl+P (что обозначает Print), предварительно выбрав нужную вам диаграмму щелчком мыши.

Рис. 3. Команды для печати данных и диаграмм расположены в подменю Print (Печать)

Делаем точечную диаграмму

Итак, мы нарисовали только столбиковую диаграмму с набором простых данных — просто одномерный набор цифр.

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

Данные

Как вы видели в главе «Типы данных», вы практически не ограничены в выборе структуры значений, содержащейся в вашем наборе данных. Для нашей точечной диаграммы, я собираюсь использовать массив массивов. Основной массив будет содержать по одному элементу для каждой «точки». Каждая из этих «точек» будет представлена другим массивом, который хранит два значения: один для х, второй для y.

var dataset = [ [5, 20], [480, 90], [250, 50], [100, 33], [330, 95], [410, 12], [475, 44], [25, 67], [85, 21], [220, 88] ];

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

Мы можем переписать наш набор данных по-другому, для большей наглядности:

var dataset = [ [ 5, 20 ], [ 480, 90 ], [ 250, 50 ], [ 100, 33 ], [ 330, 95 ], [ 410, 12 ], [ 475, 44 ], [ 25, 67 ], [ 85, 21 ], [ 220, 88 ] ];

Теперь мы видим, что каждой из 10 строк будет соответствовать одна точка в нашей визуализации. Например, в строке [5, 20], мы будем использовать 5 как координату х, а 20 — как у.

Точечная диаграмма

Давайте перенесем большую часть кода из реализации нашей столбиковой диаграммы, в том числе кусок кода, который создает SVG-элемент:

//Create SVG element var svg = d3.select("body") .append("svg") .attr("width", w) .attr("height", h);

Вместо создания прямоугольников, мы будем создавать по кругу для каждого значения в наборе данных:

svg.selectAll("circle") .data(dataset) .enter() .append("circle")

Также, теперь мы будем указывать и атрибуты, соответствующие кругу(cx, cy, r), а не прямоугольнику:

.attr("cx", function(d) < return d[0]; >) .attr("cy", function(d) < return d[1]; >) .attr("r", 5);

Заметьте, как мы получаем доступ к значениям данных, и используем их для атрибутов круга cx и cy. При использовании function(d), D3 автоматически представляет текущее значение в аргументе функции с именем d. Таким образом, текущее значение представляет маленький массив в нашем большом наборе данных.

Когда d является массивом значений(а не единичным значением, например, 3.14159), для доступа к его значениям необходимо использовать квадратные скобки. Следовательно, вместо того, чтобы писать return d, мы пишем return d[0] и return d[1], и, соответственно, возвращаем первое и второе значения массива.

Например, в нашей первой точке набора данных [5, 20], первое значение(индекс массива 0) равен 5, второе значение(индекс массива 1) равно 20. Таким образом:

d[0] returns 5 d[1] returns 20

Кстати, если вам когда-нибудь понадобится получить доступ к любому значению в большом наборе данных(без использования D3), вы можете сделать это также, с использованием квадратных скобок:

dataset[5] returns [410, 12]

Для быстрого доступа к значению вложенного массива сперва указывается индекс основного массива, потом указывается индекс массива, где хранится непосредственное значение:

dataset[5][1] returns 12

Не верите мне? Взгляните по-другому на точечную диаграму, откройте JavaScript-консоль, и введите сперва dataset[5], а потом dataset[5][1], и увидите, что случится.

Размер

Возможно, вы захотите, чтобы ваши круги имели разные размеры, чтобы, например, радиус круга зависел от y. Тогда можно попробовать такой код:

.attr("r", function(d) < return Math.sqrt(h - d[1]); >);

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

Метки

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

svg.selectAll("text") .data(dataset) .enter() .append("text")

Этот код ищет все text-элементы в SVG(которых пока нет), а потом добавляет новые текстовые элементы для каждого значения из набора данных. Используйте метод text() для задания текста для каждого элемента text:

.text(function(d) < return d[0] + "," + d[1]; >)

Немного сумбурно, но потерпите. И снова, используем function(d) для получения доступа к каждому значению набора данных. Далее, с использованием этой функции, мы используем d[0] и d[1] для получения значений массива, описывающего точку.

Символ «+», который в конкретном случае поставлен перед строкой «,», используется для объединения строк. Так эта строка кода делает: берет значения из d[0] и d[1], объединяет их в строку, в которой посередине будет находиться «,». В итоге, результат долен выглядеть примерно так: «5,20» или «25,67».

Дальше мы должны указать, где текст должен быть расположен. Это делается путем задания атрибутов текста x и y. Для этого мы будем использовать значения d[0] и d[1], их же мы используем для указания центра окружности:

.attr("x", function(d) < return d[0]; >) .attr("y", function(d) < return d[1]; >)

И в конце добавляем несколько стилей для шрифта:

.attr("font-family", "sans-serif") .attr("font-size", "11px") .attr("fill", "red");

Что дальше?

Я надеюсь, что некоторые основные концепции, заложенные в ядро D3, вам понятны: загрузка данных, создание новых элементов, использование данных для указания атрибутов созданных элементов.

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

Не беспокойтесь: D3 куда круче мастера диаграмм от Excel(не говоря уже о Clippy), но для того, чтобы генерировать блестящие интерактивные диаграммы, наш навык использования D3 должен быть на совершенно ином уровне. Для более гибкого использования наших данных мы поговорим о масштабировании в D3. Чтобы наша точечная диаграмма была более наглядна, мы научимся строить оси и указывать метки на них. Следующим шагом, который мы сделаем для создания по-настоящему интерактивых диаграмм, будет умение обновлять данные на лету.

Автором оригинального текста книги D3 Tutorials является Scott Murray
На русский язык перевел Ivanov Sergey. 2014 год

Точечная диаграмма в Excel с примером применения и настройки

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

Где используется точечная диаграмма

В точечной диаграмме одни значения выводятся вдоль горизонтальной оси Х, другие – вертикальной оси У. Эти числовые значения объединяются в точку. А точки выводятся через неравные промежутки или кластеры.

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

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

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

Как построить точечную диаграмму в Excel

Возьмем примерный набор данных, которые нужно нанести на точечную диаграмму:

1. Выделяем значения, которые нужно иллюстрировать с помощью точечной диаграммы. Переходим на вкладку «Вставка». В группе «Диаграммы» находим тип «Точечная».
2. Из предлагаемых вариантов выберем «Точечную с маркерами». Она подойдет для сравнения пар значений.
3. Получаем диаграмму такого вида:
4. Активизируем инструмент «Работа с диаграммами». Для этого нужно щелкнуть мышкой по области диаграммы. Поменяем стиль – сделаем точки красными. Добавим название (на вкладке «Макет») – «Количество заключенных менеджерами контрактов». Чтобы изменить размер шрифта, выделим название и щелкнем правой кнопкой мыши. В контекстном меню выберем поле «Шрифт».
5. Зададим названия осей. В одноименной группе на вкладке «Макет» выбираем название для горизонтальной оси («под осью»), вводим нужный текст. Для вертикальной оси – «Повернутое название».
6. Вводим название для оси и жмем ВВОД.

Точечная диаграмма отличается от других типов способом отображения показателей по горизонтальной оси. Средняя продолжительность разговора с клиентом из первого столбца показана в виде значений Х. Количество контрактов из второго столбца – в виде значений Y. То есть один набор числовых данных всегда формируется вдоль горизонтальной оси значений. А второй набор – вдоль вертикальной оси, тоже значений.

На пересечении координат – точка данных, которая объединяет два числовых значения. В нашем примере первая точка данных на рисунке представлена значением Y (16, ячейка В8) и значением Х (2, А8). Получилась точечная диаграмма связи показателей.

Подтипы точечной диаграммы

1. Мы построили точечную диаграмму с маркерами, которая позволяет сравнивать пары значений. Такой подтип рекомендуется применять, когда много данных (соединительная линия только затруднит восприятие) или нет нужды показывать связь между точками.
2. На точечной диаграмме со значениями, соединенными сглаживающими линиями, точки данных объединены гладкой кривой. Можно делать с маркерами или без. Без маркеров – когда много исходных данных.
3. В диаграмме с прямыми отрезками между точками – прямые соединительные линии. Пользователь может задать с маркерами или без.

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры