Изучим способы поиска точек пересечения двух графиков и графика с осью координат, а также варианты их отображения в 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.
Здравствуйте, помогите пожалуйста, мне нужно найти пересечение двух графиков, точки взяты произвольно, формул никаких нет. Я пробовала вставить вашу программу, но выдает ошибку
Анастасия, в данной статье разбирается случай, когда графики имеют одинаковые координаты по горизонтальной оси, и разные координаты по вертикальной.
В приведенном примере, в случае с красной линией, это условие не выполняется, вероятно по этой причине возникает ошибка.
Добрый день. Спасибо за отличное решение по графикам и код макроса, точку находит. Единственный вопрос — прописывается ли в коде вид маркера и его цвет, и формат числа? Потому что ничего не меняется, когда меняешь его руками в формате подписей данных.
Конкретно в текущем коде макроса вид и цвет маркера не прописан — не добавлял для упрощения понимания.
Но если нужно, то в конце макроса можно дополнительно прописать настройку маркера.