Точка пересечения графиков в Excel

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

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

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

Принцип расчета
Общий принцип поиска координат следующий: для каждых двух соседних пар точек на оси абсцисс (на рисунке x1 и x2 расположены по горизонтали) проверяется условие пересекаются ли линии, то есть выполняется ли условие y1 ≥ z1 и y2 ≤ z2, или наоборот y1 ≤ z1 и y2 ≥ z2 (на рисунке y1, y2, z1 и z2 расположены по вертикали).

Пересечение двух графиков

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

Таблица с данными
Построим на основе этих данных точечную диаграмму. Выделяем диапазон данных A1:K3 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Точечная с прямыми отрезками.
В итоге получаем точечную диаграмму с двумя линиями:

Точечная диаграмма
Как видим на диаграмме линии пересеклись в 5 местах. В общем случае подобных точек может быть сколь угодно много, поэтому вручную находить каждую из них представляется достаточно трудоемким процессом.
Чтобы упростить работу и автоматизировать расчет воспользуемся средствами Visual Basic.
Переходим в редактор VBA (в панели вкладок выбираем Разработчик -> Visual Basic или воспользуемся сочетанием клавиш Alt + F11), создаем модуль и записываем в него макрос (напротив каждой строчки даются пояснения к коду):

Переходим обратно в Excel, нажимаем на диаграмму (для активации) и запускаем макрос:

Добавление ряда
Осталось только добавить на свое усмотрение некоторые детали для улучшения визуализации и получаем итоговый вариант:

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

Пересечение графика с осями координат

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

Таблица с данными
Применяем к графику макрос и получаем:

Пересечение графика с осью

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Комментарии (11)

  1. Андрей

    Очень здорово, вопрос один. А если график 3 или более функций и можно ли как то значения автоматом в таблицу занести

    1. tutorexcel Автор поста

      В случае если на графике 3 линии, то в макросе нужно будет дополнительно прописать проверки на пересечение каждой пары линий (т.е. первой и второй, первой и третьей, второй и третьей).
      Координаты точек пересечений хранятся в массивах ArrayT1 (ось X) и ArrayT2 (ось Y), соответственно чтобы занести эти координаты в таблицу, мы должны приравнять значения ячеек таблицы к элементам массивов.

      1. Андрей Мухин

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

  2. Анастасия Кудряшова

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

    1. tutorexcel Автор поста

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

  3. Alex

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

    1. tutorexcel Автор поста

      Конкретно в текущем коде макроса вид и цвет маркера не прописан — не добавлял для упрощения понимания.
      Но если нужно, то в конце макроса можно дополнительно прописать настройку маркера.

  4. KamilGuseinov

    Добрый день!

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

    1. tutorexcel Автор поста

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

      1. KamilGuseinov

        Благодарю за предоставленную информацию
        За выходные ознакомился со справочной информацией visual basic, более менее понял предыдущую подсказку.
        Спасибо Вам 🙂

Добавить комментарий

Ваш e-mail не будет опубликован.