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

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

Зачем нужна диаграмма рассеяния

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

Коротко о сути

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

Когда выбирать диаграмму рассеяния

Используйте диаграмму рассеяния, если нужно:

1. Сравнить две числовые переменные и посмотреть, есть ли между ними зависимость. Пример — бюджет на рекламу и количество проданных товаров.
2. Оценить наличие корреляции — положительной, отрицательной или её отсутствие.
3. Выявить тренды — линейные и нелинейные зависимости.
4. Найти выбросы, плотные кластеры и неоднородности в данных.
5. Работать с временно независимыми точками данных, когда порядок наблюдений не важен.
6. Добавить третью переменную через размер пузырьков на диаграмме типа bubble.

Важно

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

Типы диаграмм рассеяния в Excel

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

• Точечная — классический вариант, только маркеры.
• Точечная с плавными линиями и маркерами — маркеры соединены сглаженной линией.
• Точечная с плавными линиями — сглаженная линия без маркеров.
• Точечная с прямыми линиями и маркерами — маркеры и прямые отрезки между ними.
• Точечная с прямыми линиями — прямые линии без маркеров.
• Пузырьковая диаграмма — добавляет третью числовую переменную через размер пузырьков.
• 3-D пузырьковая диаграмма — для трёхмерного визуального представления.

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

Ниже приведена расширенная и практическая инструкция, которая учитывает подготовку данных, создание диаграммы и её оформление для презентации.

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

Предположим, у вас есть таблица с бюджетом на рекламу и количеством проданных единиц товара. Разместите независимую переменную в левой колонке, зависимую в правой. Например, столбец B — бюджет, столбец C — продажи.

Шаг 1. Подготовьте таблицу

• Убедитесь, что в столбцах только числовые значения и одна строка заголовков.
• Уберите пустые строки или объедините диапазоны, если данные разрознены.
• Если данные разные по масштабу — подумайте о логарифмической шкале для одной из осей.

Шаг 2. Выделите диапазон

• Выделите две колонки вместе с заголовками. Пример диапазона: B1:C13.

Шаг 3. Создайте диаграмму

• Перейдите на вкладку «Вставка» на ленте.
• В разделе «Диаграммы» найдите группу точечных диаграмм и выберите вариант «Точечная» — классический вариант с маркерами.

Шаг 4. Проверьте соответствие осей

• Ось X должна отображать независимую переменную, ось Y — зависимую.
• Если оси перепутаны, щёлкните по точечной серии правой кнопкой и выберите «Выбрать данные», затем поменяйте местами X и Y в настройках ряда.

Шаг 5. Отформатируйте маркеры и сетку

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

Шаг 6. Добавьте заголовки осей и подписи

• На вкладке «Работа с диаграммами» — «Макет» или «Конструктор» добавьте подписи осей и заголовок диаграммы.
• Подписи данных можно добавить через «Элементы диаграммы» и выбрать «Подписи данных» — затем «Дополнительные параметры», чтобы выбрать диапазон меток.

Шаг 7. Добавьте линию тренда и уравнение

• Щёлкните правой кнопкой на серии точек и выберите «Добавить ряд тренда» или через «Добавить элемент диаграммы» выберите «Линия тренда».
• В панели «Формат линии тренда» отметьте «Отобразить уравнение на диаграмме», если нужно показать математическую модель.

Шаг 8. Сохраните шаблон

• Когда получите нужный вид, щёлкните правой кнопкой по диаграмме и выберите «Сохранить как шаблон», чтобы переиспользовать оформление.

Настройка области построения и осей

Изменение диапазона осей помогает фокусироваться на интересующем участке данных.

Как уменьшить область построения

1. Дважды щёлкните по горизонтальной или вертикальной оси, чтобы открыть «Формат оси».
2. В разделе «Параметры оси» установите значения «Минимум» и «Максимум» вручную.
3. Подберите шаг сетки, чтобы деления были информативными.

Если нужно убрать линии сетки

1. Дважды щёлкните по одной из горизонтальных линий сетки и в панели «Формат основных линий сетки» выберите «Без линии».
2. Повторите для вертикальных линий.

Быстрые макеты и стили для презентаций

Excel включает преднастроенные макеты и стили, которые экономят время при подготовке диаграммы для отчёта.

Как применить предустановленный макет

1. Дважды щёлкните по пустой области диаграммы.
2. На ленте в разделе «Работа с диаграммами» найдите «Быстрый макет» в группе «Макеты диаграмм».
3. Наведите курсор на макеты, чтобы увидеть предварительный просмотр, и выберите подходящий.

Как изменить стиль диаграммы

1. Перейдите на вкладку «Конструктор» в разделе «Работа с диаграммами».
2. Выберите один из стилей в галерее — это оперативно изменит цвета, шрифты и оформление.

Подписи данных и диапазоны меток

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

Как добавить подписи данных с пользовательскими метками

1. Щёлкните по диаграмме и выберите «Элементы диаграммы», затем отметьте «Подписи данных» и кликните по стрелке для дополнительных опций.
2. Выберите «Дополнительные параметры», затем «Выбрать диапазон», чтобы указать ячейки с метками, например A2:A6.
3. Разместите подписи так, чтобы они не перекрывали точки — перетащите их вручную.

Линия тренда и уравнение

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

Как добавить линию тренда и отобразить уравнение

1. Кликните по любой точке диаграммы и в разделе «Добавить элемент диаграммы» выберите «Линия тренда».
2. Выберите подходящий тип линии: линейная, экспоненциальная, полиномиальная и т. п.
3. Дважды щёлкните по линии тренда и в панели «Формат линии тренда» отметьте «Отобразить уравнение на диаграмме».

Интерпретация корреляции на диаграмме

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

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

Когда диаграмма рассеяния не подходит

Контрпримеры и ограничения

• Категориальные данные. Если одна ось содержит категории, лучше использовать столбчатую или линейную диаграмму по категориям.
• Малое число точек. При 2–3 точках диаграмма не даёт статистической значимости.
• Сильное наложение значений. Когда тысячи точек накладываются, используйте прозрачность маркеров, «джиттер» или тепловую карту плотности.
• Нелинейные и сложные зависимости. Для сложных зависимостей рассмотрите регрессию более высокого порядка, не только линейную, или переходите к специализированному ПО.

Альтернативные подходы

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

• Пузырьковая диаграмма — если нужна третья числовая переменная.
• Диаграмма плотности или heatmap — для плотных наборов данных.
• Встроенный инструмент Регрессия в надстройке Анализ данных — для получения статистических параметров (коэффициенты, R-квадрат, p-значения).
• Power BI или Tableau — для интерактивной визуализации и фильтрации.
• Python с библиотеками matplotlib и seaborn — для кастомных графиков и репликабельного анализа.

Методология подготовки данных и проверки качества

Мини-методика для подготовки диаграммы рассеяния

1. Очистить данные от пропусков и некорректных значений.
2. Проверить шкалы и единицы измерения у обеих переменных.
3. Преобразовать переменные при необходимости (логарифм, стандартизация).
4. Сначала визуализировать необработанные точки для поиска выбросов.
5. При наличии выбросов задокументировать гипотезы и не удалять данные без причины.
6. Добавить линию тренда и оценить согласованность с гипотезой.

Критерии приёмки

• Ось X и ось Y подписаны и указан знак единиц измерения.
• Диаграмма читаема при печати в отчёте и на экране презентации.
• При необходимости отображено уравнение линии тренда и его тип подписан.
• Подписи данных не перекрывают ключевые точки.

Чек-листы по ролям

Для аналитика

• Проверил соответствие единиц измерения.
• Вычислил базовые статистики: средние, медиану, стандартное отклонение.
• Оценил влияние выбросов.

Для менеджера продукта

• Диаграмма подчёркивает бизнес-результат или гипотезу.
• Линия тренда и уравнение включены при необходимости принятия решения.

Для презентации

• Цвета и стиль согласованы с брендом.
• Шрифт и размеры меток подходят для экранной презентации.
• Слои легенды и подписи сведены к минимуму для ясности.

Полезные шаблоны и короткие советы

Рекомендуемые значения оформления

• Размер маркера: 6–8 pt для отчёта, 8–12 pt для слайдов.
• Прозрачность маркера: 20–40% при пересечении точек.
• Толщина линии тренда: 1,5–2 pt.
• Цвета: используйте контрастный цвет для линии тренда и приглушённые для маркеров.

Шаблон диапазона осей

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

Сопроводительный текст для отчёта

• Кратко опишите суть зависимости, укажите тип линии тренда и значение R-квадрат при наличии.

Дерево принятия решения

flowchart TD
  A[Нужна диаграмма] --> B{Две числовые переменные?}
  B -- Да --> C{Точки не зависят от времени?}
  C -- Да --> D[Точечная диаграмма 'scatter']
  C -- Нет --> E[Линейный график или точки с линией]
  B -- Нет --> F[Рассмотреть столбчатую диаграмму или таблицу]
  D --> G{Есть третья переменная?}
  G -- Да --> H[Пузырьковая диаграмма]
  G -- Нет --> I[Обычная точечная диаграмма]

Дополнительные рекомендации по безопасности и приватности

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

Проверочные тесты и критерии приёмки

Тесты на корректность диаграммы

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

Критерии приёмки

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

Заключение

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

Короткое резюме

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

Резюме

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