Как построить диаграмму рассеивания в excel

Как создать матрицу рассеяния в Excel (с примером)

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

В этом руководстве объясняется, как создать следующую матрицу диаграммы рассеяния в Excel:

Шаг 1: введите данные

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

Шаг 2: Создайте диаграммы рассеяния

Затем выделим диапазон ячеек A2:B9 , затем щелкните вкладку « Вставка », затем нажмите кнопку « Разброс » в группе « Диаграммы ».

Автоматически будет создана следующая диаграмма рассеяния очков и передач:

Далее выполните следующие шаги:

• Нажмите на значения на оси X и измените минимальную ось, привязанную к 80.
• Щелкните ось Y и измените минимальное значение оси на 20.
• Щелкните заголовок диаграммы и удалите его.
• Нажмите на линии сетки на диаграмме и удалите их.
• Наконец, измените размер диаграммы, чтобы сделать ее меньше.

Конечный результат должен выглядеть примерно так:

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

Наконец, повторите эти шаги для переменных передач и подборов и поместите диаграмму рассеяния в нижний правый угол:

Шаг 3: Пометьте диаграммы рассеяния

Наконец, введите имена переменных рядом с диаграммами рассеяния, чтобы было легко понять, какие диаграммы рассеяния представляют какие переменные:

Вот как интерпретировать сюжеты:

• Диаграмма рассеяния в верхнем левом углу представляет соотношение между очками и передачами.
• Диаграмма рассеяния в левом нижнем углу представляет соотношение между очками и подборами.
• Диаграмма рассеяния в правом нижнем углу представляет соотношение между передачами и подборами.

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

Дополнительные ресурсы

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

Диаграмма рассеяния в Excel и сферы ее применения

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

При работе с двумерными данными обычно рисуют диаграммы рассеяния. Другие названия – «диаграммы разброса», «точечные диаграммы». Подобные графики показывают значения двух переменных в виде точек. Если в двумерных данных содержатся какие-либо проблемы (выбросы), то их легко будет обнаружить с помощью соответствующей диаграммы разброса.

Что показывает диаграмма рассеяния

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

1. качеством продукта и влияющим фактором;
2. двумя разными характеристиками качества;
3. двумя обстоятельствами, влияющими на качество, и т.п.

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

Построение диаграммы рассеяния в Excel

Диаграмма разброса представляет наблюдаемое явление в пространстве двух измерений. Если одну величину рассматривать как «причину», влияющую на другую величину, то ей будет соответствовать ось Х (горизонтальная ось). Реагирующей на это влияние величине соответствует ось Y (вертикальная ось). Когда четко классифицировать переменные невозможно, распределение производится пользователем.

Построим диаграмму рассеяния для небольшой двумерной совокупности данных:

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

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

По умолчанию программа построила диаграмму разброса такого вида:

Изменим параметры горизонтальной и вертикальной оси, чтобы четыре пары показателей расположились более равномерно в области построения. Щелкнем сначала правой кнопкой мыши по вертикальной оси. Выберем «Формат оси»:

На вкладке «Параметры оси» установим минимальное значение 100 000, а максимальное – 200 000. Показатели объема продаж находятся в этих пределах:

Минимальное значение для горизонтальной оси Х – 100, т.к. ниже этого показателя данных в таблице нет.

3.5. Предварительный анализ многомерных данных. Диаграмма рассеивания

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

(3.11)

Коэффициент корреляции определяется через коэффициент ковариации по формуле:

(3.12)

Коэффициент корреляции обладает следующими свойствами:

1. Если между случайными признаками исуществует положительная линейная связь, то , если связь отрицательная, то (рис. 3.18 — а,б).

2. Коэффициент корреляции является безразмерной величиной.

3.

4. Чем ближе по модулю к 1, тем сильнее линейная связь между случайными признаками и.

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

Рис. 3.18. Варианты связи между случайными признаками и

Приведем пример расчета коэффициентов ковариации и корреляции в таблице EXCEL. Рассчитаем эти коэффициенты для двух пар признаков (—) и (—). Расчеты приведены на рис. 3.19 и рис. 3.20.

Рис. 3.19. Табличный расчет коэффициентов ковариации и корреляции

пар признаков (—) и (—).

Рис. 3.20. Результаты расчета коэффициентов ковариации и корреляции

пар признаков (—) и (—).

Расчет коэффициента ковариации между двумя признаками в EXCEL можно выполнить и с помощью функции КОВАР (рис. 3.20 -3.21).

Рис. 3.21. Диалоговое окно функции КОВАР

Обычно парные коэффициенты таблицы данных со многими признаками сводятся в матрицы, которые называются соответственно ковариационной и корреляционной матрицей. Расчет ковариационной и корреляционной матриц производится с помощью программ, включенных в настраиваемый пакет “Анализ данных” (рис. 3.22). Интерфейс программ Ковариация и Корреляция приведены на рис. 3.23 и 3.25. Результаты расчета ковариационной и корреляционной матриц по данным таблицы данных приведены на рис. 3.24 и 3.26.

Рис. 3.22. Список программ пакета “Анализ данных”

Рис. 3.23. Диалоговое окно программы Ковариация

Рис. 3.24. Результаты расчета ковариационной матрицы

Рис. 3.25. Диалоговое окно программы Корреляция

Рис. 3.26. Результаты расчета корреляционной матрицы

На этапе предварительного анализа очень полезно построить точечные графики совместного распределения пар признаков. В точечной диаграмме каждой паре значений двух признаков ставится в соответствие отдельная точка. Значения признаков определяют координаты точек на плоскости координат двух признаков. Такие графики называются диаграммами рассеивания. Диаграммы рассеивания дают почву для выработки статистических гипотез. В EXCEL диаграммы рассеивания строится с помощью диаграммы Точечная (Мастер диаграмм) (рис. 3.27). Рассмотрим примеры диаграмм рассеивания для некоторых пар признаков таблицы данных. Диаграмма рассеивания признаков иприведена на рис. 3.28. Признакииимеют равномерное распределения. Точки на диаграмме равномерно покрывают квадрат со сторонами равными единице.

Рис. 3.27. Мастер диаграмм (Точечная)

Рис. 3.28. Диаграмма рассеивания признаков и

Диаграмма рассеивания признаков иприведена на рис. 3.29. Признакииимеют стандартное нормальное распределения. Коэффициент корреляции этой пары признаков , то есть очень близок к нулю. Признаки линейно независимы. Диаграмма такой пары признаков имеет форму круга. Плотность точек понижается при удалении от центра круга с координатами (0;0).

Диаграмма рассеивания признаков иприведена на рис. 3.30. Признакимеет равномерное распределении на интервале (0,1). Признакимеет нормальное распределение с параметрами . Однако, эти признаки имеют достаточно высокий коэффициент корреляции />. Связь имеет отрицательный характер. Связь между признаками объясняется тем, что значения признака были получены путем преобразования значений признака.

Рис. 3.29. Диаграмма рассеивания признаков и

Рис. 3.30. Диаграмма рассеивания признаков и

Рассмотрим еще один важный вид диаграмм рассеивания. Это диаграммы рассеивания классифицированных данных (рис. 3.32). Примерами таких данных могут быть антропологические данные двух рас или данные двух выборок, полученных при разных условиях. Известны несколько форм представления классифицированных данных. Первая форма это расположение данных классов в одних и тех же столбцах. Классы данных располагаются один под другим. При этом в таблице данных должен быть столбец, содержащий номера классов. Второй способ – расположение классов в различных столбцах таблицы. Применяется реже., потому что количество наблюдений в классах должно совпадать. Рассмотрим таблицу данных, сформированную путем моделирования. Будем считать, что признаки ипредставляют собой выборку из первого класса, признакиивыборку из второго класса. При этом количество наблюдений в обоих классах одинаково и равно 100 (вторая форма представления данных). Для построения диаграммы рассеивания воспользуемся все той же точечной диаграммой ECSEL. Только теперь будем вводить два ряда данных (рис. 3.31).

Рис. 3.31 Определение параметров для классифицированных данных

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

Из рис. 3.32 можно видеть, что классы данных хорошо различимы. При этом второй класс гораздо более размытый.

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

Диаграмма рассеяния вполне может быть самым полезным творением в истории статистической графики. Создание диаграммы разброса Гальтоном привело к использованию корреляции и регрессии в области статистики.

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

Давайте рассмотрим пример в Excel, чтобы выделить шаги, используемые для создания диаграммы рассеяния.

Пример набора данных

Вставка точечной диаграммы

Чтобы вставить диаграмму рассеяния, необходимо выполнить несколько шагов. Сначала выберите данные, которые вы хотите построить. Затем выберите вкладку «Вставка» и щелкните параметр «Вставить точечную или пузырьковую диаграмму», отмеченный цифрой 3 на рисунке ниже. Наконец, выберите вариант диаграммы рассеивания.

Вставка точечной диаграммы

Отображение элементов диаграммы точечной диаграммы

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

Отображение параметров элементов диаграммы

Элементы, которые могут быть добавлены или удалены из диаграммы рассеяния:

• Топоры
• Названия осей
• Заголовок диаграммы
• Метки данных
• Полосы ошибок
• Линии сетки
• Легенда
• Линия тренда

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

Параметры элементов диаграммы

Показать оси

По умолчанию оси показаны на графике. У вас есть возможность удалить эту опцию, если хотите. Когда оси удалены по существу, вы удаляете измерения для осей X и Y.

Отображаемые оси

Показать заголовки осей

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

Отображаемые заголовки осей

Заголовок диаграммы

По умолчанию отображается заголовок диаграммы. Это можно удалить, сняв флажок рядом с «Заголовком диаграммы» или щелкнув правой кнопкой мыши заголовок диаграммы, а затем нажав «Удалить». Чтобы изменить заголовок, дважды щелкните его текстовое поле.

Отображается заголовок диаграммы

Отображение меток данных

Параметр меток данных доступен для отображения данных для каждой точки данных. По умолчанию при выборе этого параметра отображаются точки данных положения оси Y.

Отображаемые метки данных

Отображение полос ошибок

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

Отображаются полосы ошибок

Линии сетки

Эта опция позволяет вам добавлять или удалять линию на заднем плане диаграммы.

Отображение легенды

Добавление легенды позволяет идентифицировать точки графика и конструкции линий тренда на диаграмме.

Легенда отображается

Показать линию тренда

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

• Обратно связаны ли данные, когда линия тренда наклоняется вниз слева направо.
• имеют ли данные прямую связь, когда линия тренда наклонена вверх справа налево.
• Независимо от того, не связаны ли данные, когда линия тренда является плоской линией.

Отображаемая линия тренда

Форматирование линии тренда

Линия тренда предлагает дополнительные параметры. Несколько вариантов, которые могут помочь при анализе ваших данных, — это варианты отображения уравнения регрессии для данных и значения R-квадрата на диаграмме. Чтобы выполнить эту задачу, щелкните диаграмму, затем щелкните стрелку справа от параметра линии тренда.

Параметры линии тренда

Добавление уравнения наклона и R в квадрате

Чтобы добавить уравнение регрессии и значение R-квадрата, установите флажки, показанные на рисунке ниже.

Отображаются уравнение и R-квадрат

Чтобы узнать больше о построении графиков в Excel, я рекомендую следующую книгу. Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.