Построение графиков зависимостей и матриц в Excel

Чтобы построить зависимость в Excel, используйте точечную диаграмму с добавлением линии тренда; для создания матрицы взаимосвязей примените инструмент «Анализ данных» (Корреляция) или функцию =КОРРЕЛ(). Эти методы позволяют визуально оценить влияние одной переменной на другую и выявить скрытые закономерности в таблицах без сложного программирования.

Понимание инструментов: от графика до матрицы

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

• График зависимости (Диаграмма рассеяния) показывает связь между двумя переменными (например, бюджет на рекламу и объем продаж). Добавление линии тренда и уравнения регрессии позволяет делать прогнозы.
• Матрица корреляций — это таблица, где на пересечении строк и столбцов отображается коэффициент связи (от -1 до 1) между множеством параметров. Она помогает быстро найти самые сильные взаимосвязи в большом массиве данных.

Построение графика зависимости: пошаговый алгоритм

Рассмотрим создание графика на примере влияния затрат на рекламу (тыс. руб.) на количество продаж (шт.).

Шаг 1. Подготовка и выбор данных

Внесите данные в смежные столбцы. Выделите весь диапазон, включая заголовки (например, A1:B6). Важно, чтобы независимая переменная (фактор) была в левом столбце, а зависимая (результат) — в правом.

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

1. Перейдите на вкладку Вставка.
2. В группе «Диаграммы» выберите значок Точечная ( Scatter ).
3. Выберите первый подтип «Точечная» (только маркеры). Не выбирайте графики с линиями сразу, так как они соединяют точки по порядку, а не показывают статистическую зависимость.

Шаг 3. Добавление линии тренда и уравнения

Для анализа сути зависимости необходимо добавить математическую модель:

1. Кликните правой кнопкой мыши по любой точке на графике.
2. Выберите Добавить линию тренда.
3. В появившейся панели справа выберите тип аппроксимации (обычно Линейная).
4. Внизу панели поставьте галочки:
   • показать уравнение на диаграмме;
   • поместить на диаграмму величину достоверности аппроксимации (R²).

Шаг 4. Форматирование для читаемости

Добавьте названия осей через кнопку «+» рядом с графиком (Элементы диаграммы > Названия осей). Подпишите их четко, указав единицы измерения (например, «Бюджет, тыс. руб.»).

Создание матрицы корреляций

Если переменных больше двух (например: Реклама, Цена, Сезонность, Продажи), строить отдельные графики неудобно. Здесь нужна матрица.

Метод 1: Использование надстройки «Пакет анализа» (Рекомендуемый)

Этот способ создает готовую таблицу коэффициентов автоматически.

1. Активация инструмента: Если во вкладке Данные нет кнопки «Анализ данных», включите надстройку:
   • Файл > Параметры > Надстройки.
   • Внизу в управлении выберите «Надстройки Excel» и нажмите Перейти.
   • Поставьте галочку Пакет анализа и нажмите ОК.
2. Запуск расчета:
   • Вкладка Данные > кнопка Анализ данных.
   • Выберите пункт Корреляция.
   • Укажите входной интервал (все столбцы с данными, включая заголовки).
   • Отметьте галочку Метки в первой строке.
   • Выберите место для вывода результата и нажмите ОК.

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

Метод 2: Формула КОРРЕЛ

Для разовой проверки связи двух конкретных столбцов используйте функцию: =КОРРЕЛ(массив1; массив2)

Пример: =КОРРЕЛ(A2:A100; C2:C100) покажет связь между первым и третьим столбцом данных.

Визуализация матрицы (Тепловая карта)

Чтобы матрица считывалась мгновенно:

1. Выделите полученную таблицу с коэффициентами.
2. Нажмите Главная > Условное форматирование > Цветовые шкалы.
3. Выберите шкалу «Красный – Белый – Зеленый» или аналогичную.
   • Ячейки с близкими к 1 значениями окрасятся в один цвет (сильная прямая связь).
   • Близкие к -1 — в другой (сильная обратная связь).
   • Около 0 останутся нейтральными.

Частые ошибки при построении

• Неверный выбор типа диаграммы. Использование обычного «Графика» (Line Chart) вместо «Точечной» (Scatter) приводит к тому, что ось X воспринимается как категории (текст), а не числа, искажая линию тренда.
• Текстовый формат чисел. Если данные импортированы из другой системы, они могут быть текстом. Функции вернут ошибку или ноль. Проверьте формат ячеек (должен быть «Числовой») и уберите лишние пробелы.
• Игнорирование выбросов. Одна ошибочная запись (например, продажа 10000 вместо 100) может сильно исказить линию тренда и коэффициент R². Перед построением отфильтруйте аномалии.
• Смешение данных в одном столбце. Для матрицы корреляции каждый параметр должен находиться в отдельном столбце, а строки должны соответствовать одним и тем же объектам наблюдения (например, данные по дням или магазинам).

FAQ

Как спрогнозировать значение на основе построенного графика? Используйте уравнение, полученное на графике (например, y = 5x + 10). Подставьте планируемое значение X в формулу в любой ячейке Excel. Либо используйте функцию =ПРОГНОЗ.ЛИНЕЙН(новое_значение_x; известные_значения_y; известные_значения_x).

Можно ли построить матрицу корреляции без надстройки «Пакет анализа»? Да, но вручную. Вам придется создать таблицу, где в заголовках строк и столбцов будут названия параметров, а в ячейках прописать формулу =КОРРЕЛ() для каждой пары столбцов исходных данных. Это трудоемко для большого количества переменных.

Что делать, если линия тренда не проходит через точки? Линия тренда (регрессии) редко проходит точно через все точки. Ее задача — минимизировать суммарное отклонение. Если точки разбросаны очень сильно (R² < 0,3), значит, линейная зависимость слабая или отсутствует, и прогноз по такой модели будет неточным. Попробуйте выбрать другой тип тренда (полиномиальный, экспоненциальный) в настройках линии.