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

Диаграмма рассеяния в EXCEL

Диаграмма рассеяния ( scatter plot ) используется для отображения возможной взаимосвязи между двумя переменными. Диаграмма рассеяния незаменима при проведении корреляционного и регрессионного анализа.

Возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х i ; Y i ). Для наглядности зададим различные типы зависимости между переменными: линейную, квадратичную и затухающую синусоидальную. Для этого сгенерируем соответствующие тренды и настроим случайный разброс переменной Y (по нормальному закону ).

Сначала рассмотрим линейный тренд Y = aX + b (см. Файл примера, лист Линейный ). Параметры тренда (прямой линии) a и b зададим в отдельной табличке, там же зададим параметры отвечающие за величину дисперсии переменной Y.

Величину постоянного разброса (отвечающую за гомоскедастичность модели) будем задавать в % от среднего значения Y. Иногда, дисперсия переменной Y не постоянна (имеется неоднородность наблюдений — гетероскедастичность ). Поэтому, при построении формул учтем и такую возможность.

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

Примечание : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .

Отображение информации о 3-х переменных на двухмерной диаграмме

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

Нам требуется построить двумерную диаграмму рассеяния (на плоскости), хотя у нас имеется 3 переменных: производительность, температура и режим .

Обратим внимание, что третья переменная Режим является категориальной (принимает только значения из ограниченного набора значений). В нашем случае переменная Режим принимает 2 значения: Режим №1 и Режим №2 (значения 1 и 2 присвоены номинально).

Пары значений ( производительность; температура ), относящиеся к Режиму №1 будем на диаграмме рассеяния выводить красным цветом, а относящиеся к Режиму №2 будем выводить синим ( файл примера лист 3-переменных ).

Такой же подход можно использовать для дискретных переменных , когда они принимают небольшое количество значений: 2-5.

Категоризованные диаграммы

Если третья переменная – непрерывная величина, то для отображения данных можно использовать так называемые категоризованные диаграммы (coplot = conditioning plot).

Теперь вместо категориальной переменной Режим у нас имеется непрерывная переменная Давление , которая принимает значения от 10 до 20. Предположим, что значение переменной Давление = 15, является неким пороговым и протекание процесса значительно отличается, если оно протекает при давлении от 10 до 15 и от 15 до 20. Используя этот факт строят 2 диаграммы:

• Пары значений ( производительность; температура ) при давлении от 10 до 15:
• Пары значений ( производительность; температура ) при давлении от 15 до 20.

Если пороговых значений 2, то понадобится 3 диаграммы и т.д. Эти диаграммы строятся аналогично диаграммам из предыдущего раздела.

Матрица диаграмм рассеивания

Для множественной регрессии, когда имеется 3 или более переменных, часто строят Матрицу диаграмм рассеивания (Matrix Scatter Plot, Scatter Plot Matrix — SPM).

Если имеется 3 переменных (x 1 , x 2 , y), то строятся 3 обычные диаграммы рассеяния отображающие парные взаимосвязи переменных: (x 1 , x 2 ); (x 1 , y); (x 2 , y).

Примечание : Чтобы найти количество диаграмм рассеяния в матрице, необходимо вычислить число сочетаний из n по 2, где n – число переменных. Например, для 4-х переменных число диаграмм равно ЧИСЛКОМБ(4;2) =6.

Иногда строят не только диаграмму (x 1 , x 2 ), но и (x 2 , x 1 ). В этом случае матрица будет содержать в 2 раза больше диаграмм рассеяния (см. файл примера лист Matrix ).

Примечание : Чтобы найти количество диаграмм рассеяния в такой (полной) матрице, необходимо вычислить число перестановок из n по 2, где n – число переменных. Например, для 4-х переменных число диаграмм равно ПЕРЕСТ(4;2) =12.

Работа с диаграммами и графиками в Excel

Примеры создания и настройки графиков или диаграмм для визуализации данных отчетов. Возможность создания инфо-графики стандартными средствами с примерами на картинках и описанием.

Создание и настройка диаграмм

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

Автоматическое создание графиков и диаграмм в Excel.
Примеры быстрого автоматизированного создания диаграмм и графиков для таблиц с данными. Особенности автоматического построения графиков и использование шаблонов по умолчанию.

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

Процентная диаграмма в Excel инструкция по созданию.
Как построить процентную диаграмму: круговую и столбчатую (гистограмму). Пошаговое руководство с картинками. Процентное соотношение на разных типах диаграмм.

Диаграмма рассеяния в Excel и сферы ее применения.
Как построить диаграмму рассеяния: пошаговая инструкция с картинками. Учимся использовать диаграмму разброса в целях анализа двумерных данных, делать по ней выводы.

Сравнительная диаграмма в Excel и примеры ее особенностей построения.
Как построить сравнительную диаграмму. Анализ диаграмм на примере сравнительных показателей экономического развития. Построение сравнительных лепестковых диаграмм.

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

Комбинированные диаграммы в Excel и способы их построения.
Как можно комбинировать разные типы диаграмм: способы создания смешанных диаграмм средствами офисной программы. Пошаговая инструкция построения с картинками.

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

Как использовать формулы в диаграммах Excel: примеры.
Примеры использования формул в диаграммах: аргументы функции РЯД, применение именованных диапазонов вместо ссылок на ряд данных при построении графиков. Условное форматирование.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Диаграмма разброса приобрела следующий вид:

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

1. Каждая точка дает представление об объеме продаж и контактах (как об одномерных совокупностях) и о взаимосвязи между этими параметрами.
2. Количество контактов (горизонтальная ось) распределилось в диапазоне 140-220. Типичное значение равно примерно 170.
3. Объемы продаж за анализируемый период (вертикальная ось) находятся в диапазоне примерно от 130 000 до 190 000. Типичное значение равняется приблизительно 150 000.
4. Взаимосвязь между числом контактов и объемом сбыта является положительной, т.к. точки выстроились слева направо снизу вверх. Следовательно, чем больше у менеджера было контактов с клиентами (точки правее), тем больше прибыли организации он дал (точки выше).

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