Изучим способы поиска точек пересечения двух графиков и графика с осью координат, а также варианты их отображения в 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), создаем модуль и записываем в него макрос (напротив каждой строчки даются пояснения к коду):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
Sub IntersectionGraph() X = ActiveChart.SeriesCollection(1).XValues 'Значение по оси X Y = ActiveChart.SeriesCollection(1).Values 'Значения 1-ой линии по оси Y Z = ActiveChart.SeriesCollection(2).Values '2-ой TotalCount = ActiveChart.SeriesCollection(1).Points.Count 'Подсчет количества точек по X Dim ArrayT1() As Double 'Определение массива для хранения координат по X Dim ArrayT2() As Double 'по Y ReDim ArrayT1(TotalCount) 'Определение размера массива ReDim ArrayT2(TotalCount) k = 0 ActiveChart.SeriesCollection(1).Select For i = 1 To TotalCount - 1 'Цикл по всем точкам X1 = X(i) X2 = X(i + 1) Y1 = Y(i) Y2 = Y(i + 1) Z1 = Z(i) Z2 = Z(i + 1) If (Y1 >= Z1 And Y2 <= Z2) Or (Y1 <= Z1 And Y2 >= Z2) Then 'Проверка условия 'Расчет ArrayT1(k) = ((X2 * Y1 - X1 * Y2) - (X2 * Z1 - X1 * Z2)) / ((Z2 - Z1) - (Y2 - Y1)) ArrayT2(k) = ((Y2 - Y1) * ArrayT1(k) + (X2 * Y1 - X1 * Y2)) / ((X2 - X1)) k = k + 1 End If Next i ReDim Preserve ArrayT1(k - 1) 'Изменение размера массива ReDim Preserve ArrayT2(k - 1) ActiveChart.SeriesCollection.NewSeries 'Добавления нового ряда на диаграмму ActiveChart.SeriesCollection(3).ChartType = xlXYScatter 'Определение типа ряда как точечный ActiveChart.SeriesCollection(3).Name = "Пересечение" 'Определение имени ряда ActiveChart.SeriesCollection(3).XValues = ArrayT1 'Приравнивание значений по X элементами массива ActiveChart.SeriesCollection(3).Values = ArrayT2 'по Y End Sub |
Переходим обратно в Excel, нажимаем на диаграмму (для активации) и запускаем макрос:
Осталось только добавить на свое усмотрение некоторые детали для улучшения визуализации и получаем итоговый вариант:
Для удобства и экономии времени, определение вида и цвета отображаемой точки, формата подписи данных и прочих настроек, можно также добавить в код макроса.
Пересечение графика с осями координат
Аналогичное решение можно применить и в случае поиска мест пересечения графика с осью абсцисс.
Для этого в качестве координат одной из линий установим нулевые значения:
Применяем к графику макрос и получаем:
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Очень здорово, вопрос один. А если график 3 или более функций и можно ли как то значения автоматом в таблицу занести
В случае если на графике 3 линии, то в макросе нужно будет дополнительно прописать проверки на пересечение каждой пары линий (т.е. первой и второй, первой и третьей, второй и третьей).
Координаты точек пересечений хранятся в массивах ArrayT1 (ось X) и ArrayT2 (ось Y), соответственно чтобы занести эти координаты в таблицу, мы должны приравнять значения ячеек таблицы к элементам массивов.
Спасибо за ответ, только если честно я конструктор, и незнаком с программированием, скажите возможно ли связаться с Вами для решения моей проблемы?
Да, напишите на info@tutorexcel.ru.
Скажите, пожалуйста, а можно сделать макрос подобный этому для 49-60 графиков. Для нахождения пересечения их с линией?
Да, формально нет ограничений на количество линий, если можно сделать для 2, то можно и для 60, но, естественно, из-за увеличения количества линий увеличится время работы.
Если интересно, то могу направить вам пример макроса для 3 линий, его можно будет доработать для нужно вам количества линий.
Здравствуйте, помогите пожалуйста, мне нужно найти пересечение двух графиков, точки взяты произвольно, формул никаких нет. Я пробовала вставить вашу программу, но выдает ошибку
Анастасия, в данной статье разбирается случай, когда графики имеют одинаковые координаты по горизонтальной оси, и разные координаты по вертикальной.
В приведенном примере, в случае с красной линией, это условие не выполняется, вероятно по этой причине возникает ошибка.
Добрый день. Спасибо за отличное решение по графикам и код макроса, точку находит. Единственный вопрос — прописывается ли в коде вид маркера и его цвет, и формат числа? Потому что ничего не меняется, когда меняешь его руками в формате подписей данных.
Конкретно в текущем коде макроса вид и цвет маркера не прописан — не добавлял для упрощения понимания.
Но если нужно, то в конце макроса можно дополнительно прописать настройку маркера.
Добрый день!
Подскажите, где возможно просмотреть коды макросов для графика с 3 функциями. Пытался самостоятельно вписать рассчет точки пересечения для 3-й функции, на основе ранее указанного комментария, но оказалось все не так уж и легко.
Буду Вам признателен
Направил пример на почту.
Если вкратце, то в коде макроса в начале указаны номера рядов, по которым ищутся пересечения.
Можно сделать эти номера параметрами, то можно прогнать этот макрос для любой пары линий.
Благодарю за предоставленную информацию
За выходные ознакомился со справочной информацией visual basic, более менее понял предыдущую подсказку.
Спасибо Вам 🙂
Здравствуйте, Иван! Спасибо за Ваш труд! Пожалуйста, отправьте мне тоже макрос для трех линий. Не могу понять где допускаю ошибку. Заранее Вас благодарю!
Отправил Вам на почту 🙂
Здравствуйте! Пожалуйста, отправьте мне тоже макрос для трех линий. Заранее Вас благодарю!
Отправил 🙂
Добрый день!
Объясните пожалуйста, как настроить отображение координат точки пересечения графиков?
Нужно щелкнуть правой кнопкой мыши по ряду с точками и в контекстном меню выбрать Добавить подпись данных.
И далее в формате подписей данных включить в подписи значение X и значение Y (как раз чтобы показывались координаты вида «x, y»).