Точечный график распределения джиттера для статистики в Excel

Распределенный точечный график в Excel является идеальным инструментом для анализа структуры данных, когда значения сосредоточены вокруг друг друга или когда их очень много но необходимо визуально определить самые популярные значения. В распределенном точечном графике с «диаграммой джиттера» используется метод управляемого рассеяния точек вдоль оси X, так что значения примера перекрываются как можно меньше. Например, если мы изучим вес случайно выбранных женщин и мужчин, то результаты 65 кг для женщин или 75 кг для мужчин будут повторяться достаточно часто, а аномалии будут повторятся наиболее редко. Давайте проверим: как создать такой график в Excel?

Распределенная точечная диаграмма джиттера

В нашем случае мы попытаемся представить почасовые ставки сотрудников компании-примера на диаграмме. Исходные данные собраны случайным образом на основе статистических показателей. Все они приведены в таблице, в которой количество строк достигает более чем 300:

Данные статистики.

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

Попытка создания 1.

Теперь пытаемся получить распределенный точечный график. Для этого выделяем диапазон A1:D317 и выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Вставить точечную» и мы получим график как показано ниже на рисунке:

Вторая попытка сделать анализ.

Результат неудачный. Добавим столбец (Индекс) между столбцами A(Пол) и B(Ставка $/ч.) для значений оси Х на графике. В столбце «Индекс» будет находится формула =ЕСЛИ(A2="Мужчина";1;3), которая присвоит каждому полу индексное число если мужчина, то 1, а если женщина – 3. И теперь выделим на этот раз диапазон и снова построим точечный график: «ВСТАВКА»-«Диаграммы»-«Вставить точечную».

Результат попытки 2.

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

Как сделать график распределения джиттера в Excel

Чтобы создать точечный график распределения джиттера добавьте к исходной таблице еще 2 вспомогательных столбца с названиями «XM» и «XW», в которых к значению на оси «x» из столбца «Индекс» мы добавим случайное число в виде функции =СЛЧИС(), возвращаемое значения которой всегда попадают в диапазон больших, равных 0 и меньших 1:

Формула для столбца XM:

=ЕСЛИ(A2="Мужчина";B2+СЛЧИС();НД())

Формула в столбце XW:

=ЕСЛИ(A2="Женщина";B2+СЛЧИС();НД())

Как показано ниже на рисунке:

2 вспомогательных столбца.

Чтобы создать диаграмму для мужского ряда, мы будем использовать столбец с именем XM, установив его как данные оси «x», а столбец «Мужчины» - как данные для оси «y». Выполните следующий ряд действий:

  1. Выделите столбец рабочего листа Excel – F и D, а затем выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Вставить точечную».
  2. Активируйте график щелкнув по нему левой кнопкой мышки и выберите инструмент из дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные».
  3. В появившемся диалоговом окне «Выбор источника данных» в левой секции «Элементы легенды (ряды)» нажмите на кнопку «Изменить» и заполните поля дополнительного окна «Изменения ряда» так как показано ниже на рисунке:
    4. Изменить Элементы легенды ряды.
  4. Теперь в окне «Выбор источника данных» в левой секции «Элементы легенды (ряды)» нажмите на кнопку «Добавить» и снова заполните поля для отображения на графике женских ставок как на рисунке:
    5. Выбор источника данных.

Скачать пример точечного графика распределения джиттера в Excel

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


en ru