Построение точечного графика и поиск координат пересечения в Excel

Иван Корнев·12.04.2026·⏱4 мин

Чтобы построить график с точками и найти точку их пересечения в Excel, используйте тип диаграммы «Точечная» для визуализации и функции ЛИНЕЙН (или НАКЛОН/ОТРЕЗОК) для расчета уравнений прямых. Координаты пересечения $(x; y)$ вычисляются по формулам: $x = (b_2 - b_1) / (m_1 - m_2)$ и $y = m_1 \cdot x + b_1$, где $m$ — наклон, а $b$ — смещение линии. Этот метод дает математически точный результат даже если линии визуально пересекаются между сеткой графика.

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

Для корректного построения и расчетов данные должны быть структурированы в столбцах. Вам понадобятся два набора координат: один для первой линии (Серия 1) и второй для линии пересечения (Серия 2).

Рекомендуемая структура таблицы:

  • Столбец A: Значения X для первой линии.
  • Столбец B: Значения Y для первой линии.
  • Столбец C: Значения X для второй линии.
  • Столбец D: Значения Y для второй линии.

Убедитесь, что значения в столбцах X отсортированы по возрастанию. Это улучшит читаемость графика и упростит проверку данных перед расчетом пересечения.

Создание точечной диаграммы

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

  1. Выделите диапазон данных первой серии (столбцы A и B).
  2. Перейдите на вкладку Вставка → группа Диаграммы → выберите Точечная (иконка с разрозненными точками).
  3. Чтобы добавить вторую линию, кликните правой кнопкой мыши по области графика и выберите Выбрать данные.
  4. Нажмите кнопку Добавить.
  5. В поле «Значения X» укажите диапазон столбца C, в поле «Значения Y» — диапазон столбца D.
  6. Нажмите ОК. Теперь на одном поле отображаются оба набора точек.

Для наглядности можно изменить тип отображения серий: кликните правой кнопкой по точкам на графике → Изменить тип диаграммы для ряда → выберите «Точечная с прямыми отрезками».

Расчет точки пересечения формулами

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

Шаг 1. Получение коэффициентов уравнений

Уравнение прямой имеет вид $y = mx + b$, где $m$ — наклон, $b$ — точка пересечения с осью Y. В Excel есть несколько способов найти эти параметры:

Способ А: Функция ЛИНЕЙН (массив данных) Эта функция возвращает сразу несколько параметров.

  • Для первой линии введите в ячейку: =ЛИНЕЙН(B2:B10; A2:A10)
    • Результат: первая ячейка — наклон ($m_1$), вторая — отрезок ($b_1$).
  • Для второй линии: =ЛИНЕЙН(D2:D10; C2:C10)
    • Результат: $m_2$ и $b_2$.

Способ Б: Отдельные функции (проще для новичков)

  • Наклон ($m$): =НАКЛОН(известные_значения_Y; известные_значения_X)
  • Отрезок ($b$): =ОТРЕЗОК(известные_значения_Y; известные_значения_X)

Запишите полученные четыре значения в отдельные ячейки для удобства (например, $m_1, b_1, m_2, b_2$).

Шаг 2. Вычисление координат пересечения

Точка пересечения находится там, где $y_1 = y_2$. Приравниваем уравнения: $$m_1 \cdot x + b_1 = m_2 \cdot x + b_2$$

Отсюда формула для расчета координаты X:

=(b2 - b1) / (m1 - m2)

(Замените b1, m1 и т.д. на адреса ячеек с вашими коэффициентами)

Формула для расчета координаты Y (подставляем найденный X в любое из уравнений):

=m1 * [Ячейка_с_X] + b1

Если наклоны линий равны ($m_1 = m_2$), знаменатель дроби обратится в ноль. Это означает, что линии параллельны и точки пересечения не существует (либо они совпадают полностью).

Визуализация результата на графике

Чтобы отметить найденную точку непосредственно на диаграмме:

  1. Создайте новый маленький блок данных рядом с расчетами:
    • Столбец X: ссылка на ячейку с рассчитанным значением X пересечения.
    • Столбец Y: ссылка на ячейку с рассчитанным значением Y пересечения.
  2. Кликните правой кнопкой по графику → Выбрать данные → Добавить.
  3. Укажите эти две новые ячейки как значения X и Y для новой серии.
  4. Назовите серию «Пересечение».
  5. Настройте формат этой серии: сделайте маркер крупным, ярким (например, красный круг) и добавьте подпись данных, чтобы координаты отображались рядом с точкой.

Частые ошибки

  • Перепутаны аргументы в функциях. В функциях НАКЛОН, ОТРЕЗОК и ЛИНЕЙН первым аргументом всегда идут значения Y, а вторым — X. Ошибка в порядке приведет к неверному углу наклона.
  • Использование обычного линейного графика. Если ось X содержит числа (например, 1, 5, 100), обычный график воспримет их как категории и расположит на равном расстоянии, исказив геометрию пересечения. Всегда используйте «Точечную» диаграмму.
  • Нелинейные данные. Метод с ЛИНЕЙН предполагает, что ваши точки лежат на прямой. Если данные описывают параболу или синусоиду, линейная аппроксимация даст лишь приблизительный результат. Для кривых линий требуется полиномиальная регрессия или поиск минимума разницы между значениями Y.

FAQ

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

Что делать, если линии пересекаются вне диапазона моих данных? Формулы все равно сработают и выдадут координаты, но экстраполяция (прогноз за пределами известных данных) может быть недостоверной, если поведение линии за пределами измерений меняется.

Как найти пересечение, если одна линия вертикальна? Вертикальная линия имеет уравнение $x = const$. В этом случае координата X пересечения известна сразу (это константа вертикальной линии), а Y нужно подставить в уравнение второй линии: $y = m \cdot x_{const} + b$. Формула с делением на разность наклонов здесь не подойдет, так как наклон вертикальной линии бесконечен.