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

Обновлено: 21.11.2024

Узнайте, как построить точечную диаграмму, также известную как диаграмма x-y, на которой можно показать отношения между переменными.

Если вам нужно визуализировать взаимосвязь между двумя наборами количественных данных, Microsoft Excel позволяет вам создать диаграмму разброса по осям X-Y.

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

Точечная диаграмма Excel — когда ее следует использовать

В Microsoft Excel вы можете запутаться, является ли диаграмма X-Y точечной диаграммой или линейной диаграммой. Оба похожи, за исключением представления данных по горизонтальной оси (X).

Точечная диаграмма состоит из двух осей значений для визуализации количественных данных. Горизонтальная ось (X) представляет один набор числовых данных, а вертикальная ось (Y) — другой набор данных.

Но линейный график Excel визуализирует все данные категории по горизонтальной оси (X), а числовые значения по вертикальной оси (Y).

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

Чтобы проанализировать, есть ли корреляция между двумя наборами количественных значений. Внешний вид диаграммы X и Y будет очень похож на диагональное расположение.
Чтобы исследовать положительные или отрицательные тенденции в переменных.
Чтобы увеличить горизонтальную ось (X).
Чтобы визуализировать выбросы, кластеры, нелинейные тренды и линейные тренды в большом наборе данных.
Для сравнения большого количества независимых от времени точек данных.

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

Вот шаги для создания точечной диаграммы с использованием шаблона диаграммы X-Y в Microsoft Excel. Вы можете использовать следующие наборы данных в качестве примера для создания точечной диаграммы.

<р>1. Для начала отформатируйте наборы данных, чтобы поместить независимые переменные в левый столбец и зависимые переменные в правый столбец. В приведенных выше наборах данных рекламные бюджеты являются независимыми переменными, а проданные товары — зависимыми переменными.

<р>2. Вам нужно выбрать два столбца в Microsoft Excel с числовыми данными. Не забудьте также включить заголовки столбцов. В данном случае это диапазон B1:C13.

<р>3. Теперь щелкните вкладку «Вставка» на ленте, а затем выберите понравившийся шаблон точечной диаграммы в разделе «Диаграммы». В этом уроке это первая миниатюра классической точечной диаграммы.

<р>4. Классическая точечная диаграмма X-Y будет отображаться на листе Microsoft Excel. Это самая простая форма диаграммы рассеяния. Вы также можете настроить четкую и профессиональную визуализацию корреляции.

Другая оптимизация для визуализации диаграммы рассеяния

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

Типы точечных диаграмм

Диаграмма рассеяния X-Y является наиболее распространенным типом диаграммы рассеяния. Другие включают:

Разброс с плавными линиями и маркерами.
Разброс с плавными линиями.
Разброс с прямыми линиями и маркерами.
Разброс с прямыми линиями.
Разброс кружков по осям X-Y.
Трехмерный пузырьковый разброс по осям X-Y.

Настройка точечной диаграммы X-Y

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

Если вы хотите уменьшить площадь графика, выполните следующие действия:

Дважды щелкните горизонтальную (X) или вертикальную (Y) ось, чтобы открыть ось форматирования.
В меню "Параметры оси" установите минимальные и максимальные границы в соответствии с наборами данных.
Размер точечной диаграммы изменится соответствующим образом.

Если вы хотите удалить линии сетки, выполните следующие действия:

Дважды щелкните любую из горизонтальных линий сетки в области графика X-Y.
В боковом меню «Формат основных линий сетки» выберите «Без линии».
Теперь нажмите на любую из оставшихся вертикальных линий сетки и выберите "Без линии".
Сетка исчезнет с точечной диаграммы.

Microsoft Excel также предлагает профессиональные шаблоны точечных диаграмм. Вот как вы можете проверить это:

Дважды щелкните пустую область диаграммы.
На ленте найдите пункт "Быстрый макет" в разделе "Макеты диаграмм".
Нажмите «Быстрый макет», и вы увидите 11 предустановленных макетов для создания точечной диаграммы.
Наведите указатель на каждую из них, чтобы ознакомиться с функциями и выбрать ту, которая подходит для ваших наборов данных.

Добавьте профессиональный вид диаграмме рассеивания, выполнив следующие действия:

Нажмите на любое пустое место на диаграмме, чтобы открыть Инструменты диаграммы на ленте.
На вкладке "Дизайн" вы увидите 12 стилей для диаграмм X и Y.
Выберите любой, чтобы мгновенно превратить классический точечный график в стильный.

Добавить метки к точкам данных Excel на точечной диаграмме

Вы можете пометить точки данных на диаграмме X и Y в Microsoft Excel, выполнив следующие действия:

Нажмите на любое пустое место на диаграмме, а затем выберите элементы диаграммы (выглядит как значок плюса).
Затем выберите метки данных и нажмите черную стрелку, чтобы открыть дополнительные параметры.
Теперь нажмите "Дополнительные параметры", чтобы открыть параметры ярлыка.
Нажмите «Выбрать диапазон», чтобы определить более короткий диапазон из наборов данных.
Теперь для точек будут отображаться метки из столбцов A2:A6.
Для четкой визуализации метки перетащите ее в нужное место.

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

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

Чтобы добавить линию тренда, нажмите на любое пустое место на точечной диаграмме.
На ленте появится раздел «Макеты диаграмм».
Теперь нажмите "Добавить элемент диаграммы", чтобы открыть раскрывающееся меню.
В этом меню нажмите "Линия тренда", а затем выберите стиль линии тренда, который соответствует наборам данных.

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

Дважды щелкните линию тренда.
Откроется боковая панель «Формат линии тренда».
На этой боковой панели нажмите «Параметры линии тренда».
Теперь установите флажок "Отображать уравнение на диаграмме".

График рассеяния и корреляция переменных

Диаграмма рассеяния X и Y может визуализировать три типа корреляции между переменными в наборах данных для значимого представления данных. Эти соотношения следующие:

Отрицательная корреляция. При отрицательной корреляции значение одной переменной увеличивается, а значение другой уменьшается.
Положительная корреляция. Ярким примером положительной корреляции является то, что когда переменные по вертикальной (Y) оси увеличиваются, переменные по горизонтальной (X) оси также увеличиваются.
Нет корреляции. Корреляции не будет, если точки разбросаны по всей области точечной диаграммы.

Производите впечатление на аудиторию, создавая точечную диаграмму в Excel

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

Используйте точечную диаграмму (диаграмму XY), чтобы показать научные данные XY. Диаграммы рассеяния часто используются, чтобы выяснить, есть ли связь между переменными X и Y.

Только маркеры

Чтобы выяснить, существует ли связь между X (зарплатой человека) и Y (ценой его/ее автомобиля), выполните следующие действия.

<р>1. Выберите диапазон A1:B10.

<р>2. На вкладке "Вставка" в группе "Диаграммы" щелкните символ разброса.

<р>3. Нажмите Разброс.

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

Прямые линии

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

<р>1. Выберите диапазон A1:D22.

<р>2. На вкладке "Вставка" в группе "Диаграммы" щелкните символ разброса.

<р>3. Нажмите Разброс с прямыми линиями.

Примечание: см. также подтип Scatter with Smooth Lines.

Примечание: мы добавили заголовок горизонтальной и вертикальной осей. Горизонтальная ось точечной диаграммы — это ось значений, поэтому у вас есть больше вариантов масштабирования оси (так же, как вертикальная ось, которая всегда является осью значений).

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

Часто первым шагом в любом регрессионном анализе является создание диаграммы рассеяния, которая позволяет визуально исследовать связь между двумя наборами значений. В Excel для этого используется диаграмма XY (точечная).

Например, предположим, что вы хотите просмотреть или проанализировать эти значения. Диапазон рабочего листа A1:A11 показывает количество объявлений. Диапазон рабочего листа B1:B11 показывает полученные продажи. С помощью этих собранных данных вы можете изучить влияние рекламы на продажи или отсутствие эффекта.

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

Выберите диапазон рабочих листов A1:B11.

На вкладке "Вставка" нажмите кнопку команды диаграммы XY (точечная).

Выберите подтип диаграммы, который не содержит линий.

Excel отображает ваши данные в виде диаграммы XY (разброс).

Подтвердите организацию данных диаграммы.

Подтвердите, что Excel правильно упорядочил данные, взглянув на диаграмму.

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

Добавьте примечания к диаграмме, если это необходимо.

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

Добавьте линию тренда, нажав кнопку "Линия тренда" в меню "Добавить элемент диаграммы".

Чтобы отобразить меню "Добавить элемент диаграммы", перейдите на вкладку "Дизайн", а затем выберите команду "Добавить элемент диаграммы". Чтобы вкладка "Дизайн" отображалась, необходимо либо сначала выбрать встроенный объект диаграммы, либо отобразить лист диаграммы.

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

В Excel 2007 линию тренда можно добавить, щелкнув команду "Линия тренда" на вкладке "Макет диаграммы".

Добавьте уравнение регрессии к точечной диаграмме.

Чтобы отобразить уравнение для линии тренда, используемое на точечной диаграмме, выберите команду «Дополнительные параметры линии тренда» в меню «Линия тренда».

Затем установите флажки «Отображать уравнение на диаграмме» и «Отображать значение R-квадрата на диаграмме». Это говорит Excel добавить на диаграмму информацию о простом регрессионном анализе, необходимую для линии тренда. Обратите внимание, что вам может потребоваться прокрутить панель вниз, чтобы увидеть эти флажки.

В Excel 2007 и Excel 2010 нажмите кнопку "Линия тренда" на вкладке "Макет диаграммы" и выберите параметр "Дополнительные линии тренда", чтобы отобразить диалоговое окно "Формат линии тренда".

Используйте переключатели и текстовые поля на панели "Формат линии тренда", чтобы управлять расчетом линии тренда регрессионного анализа. Например, вы можете использовать флажок Set Intercept = и текстовое поле, чтобы заставить линию тренда пересекать ось X в определенной точке, например в нуле.

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

Данные регрессии едва видны, поэтому они были отформатированы, чтобы сделать их более разборчивыми.

Что вы видите, глядя на два столбца количественных данных в электронной таблице Excel? Всего два набора цифр. Хотите увидеть, как эти два набора связаны друг с другом? Точечная диаграмма идеально подходит для этого.

График рассеивания в Excel

Точечная диаграмма (также называемая XY-диаграммой или точечной диаграммой) – это двумерная диаграмма, показывающая взаимосвязь между двумя переменными.

В точечной диаграмме как горизонтальная, так и вертикальная оси являются осями значений, на которых отображаются числовые данные. Как правило, независимая переменная находится на оси x, а зависимая переменная — на оси y. На диаграмме отображаются значения на пересечении осей X и Y, объединенные в отдельные точки данных.

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

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

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

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

Для удобства использования независимая переменная должна находиться в левом столбце, так как этот столбец будет отложен по оси x. Зависимая переменная (та, на которую влияет независимая переменная) должна находиться в правом столбце и откладываться по оси Y.

Совет. Если ваш зависимый столбец стоит перед независимым столбцом и вы не можете изменить это на листе, вы можете поменять местами оси X и Y непосредственно на диаграмме.

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

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

Если исходные данные правильно организованы, создание точечной диаграммы в Excel выполняется в два простых шага:

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

Точечная диаграмма будет немедленно вставлена на ваш рабочий лист:

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

Типы точечных диаграмм

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

Разброс с плавными линиями и маркерами
Разброс с плавными линиями
Разброс с прямыми линиями и маркерами
Разброс прямыми линиями

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

Шаблоны графиков Excel XY также могут отображать каждую переменную отдельно, представляя одни и те же отношения по-разному. Для этого необходимо выделить 3 столбца с данными - крайний левый столбец с текстовыми значениями (метками) и два столбца с числами.

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

Чтобы просмотреть все доступные типы рассеяния в одном месте, выберите свои данные, щелкните значок рассеяния (X, Y) на ленте, а затем нажмите "Другие диаграммы рассеивания". Откроется Врезная диаграмма. диалоговое окно с выбранным типом XY (Scatter), и вы переключаетесь между различными шаблонами вверху, чтобы увидеть, какой из них обеспечивает наилучшее графическое представление ваших данных:

Трехмерный точечный график

В отличие от классической точечной диаграммы XY, трехмерная точечная диаграмма отображает точки данных по трем осям (x, y и z), чтобы показать взаимосвязь между тремя переменными. Поэтому его часто называют графиком XYZ.

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

График рассеяния и корреляция

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

Положительная корреляция: по мере увеличения переменной x увеличивается и переменная y. Примером сильной положительной корреляции является количество времени, которое учащиеся тратят на учебу, и их оценки.

Отрицательная корреляция: по мере увеличения переменной x значение переменной y уменьшается. Пропущенные уроки и оценки имеют отрицательную корреляцию: по мере увеличения количества пропусков экзаменационные баллы снижаются.

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

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

Как и в случае с другими типами диаграмм, почти каждый элемент точечной диаграммы в Excel можно настраивать. Вы можете легко изменить заголовок диаграммы, добавить заголовки осей, скрыть линии сетки, выбрать собственные цвета диаграммы и многое другое.

Ниже мы сосредоточимся на нескольких настройках, характерных для точечной диаграммы.

Настройка масштаба оси (уменьшение пробелов)

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

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

Щелкните правой кнопкой мыши ось X и выберите "Формат оси...".
На панели Ось формата задайте необходимые минимальные и максимальные границы.
Кроме того, вы можете изменить Основные единицы измерения, управляющие расстоянием между линиями сетки.

На снимке экрана ниже показаны мои настройки:

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

Добавить метки к точкам данных диаграммы рассеяния

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

Выберите график и нажмите кнопку Элементы диаграммы.
Отметьте поле Метки данных, нажмите маленькую черную стрелку рядом с ним, а затем нажмите Дополнительные параметры…
На панели Форматировать метки данных перейдите на вкладку Параметры метки (последняя) и настройте метки данных следующим образом:

Выберите поле «Значение из ячеек», а затем выберите диапазон, из которого вы хотите извлечь метки данных (в нашем случае B2:B6).
Если вы хотите отображать только имена, снимите флажки Значение X и/или Значение Y, чтобы удалить числовые значения из меток.
Укажите положение меток, над точками данных в нашем примере.

Вот оно! Все точки данных на нашей точечной диаграмме Excel теперь помечены по имени:

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

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

Добавить линию тренда и уравнение

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

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

Excel нарисует линию как можно ближе ко всем точкам данных, чтобы над линией было столько же точек, сколько и внизу.

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

Как переключить оси X и Y на точечной диаграмме

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

Если по какой-либо причине перестановка столбцов невозможна, вы можете поменять местами ряды данных X и Y непосредственно на диаграмме. Вот как:

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

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

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

Вас также может заинтересовать

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

Это не работает. Почему, черт возьми, Excel настолько нелогичен, что вы думаете, что если выбрать два столбца числовых данных и щелкнуть точечный график, получится простой точечный график, не так ли? Не в мире Microsoft, где вся идея заключается в том, что вам нужно «обучиться» использовать приложения Microsoft. Excel еще больше, чем раньше, — абсолютный мусор.

Читайте также: