本文介绍了在Excel绘图中找到相交点的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个通过实验获得的值的散点图和一条线x = y,我希望找到趋势线和x = y之间的所有交点,是否有这样做的方法/功能?
I have a scatterplot of experimentally obtained values and a line x=y, I wish to locate all points of intersection between the trend line and x=y, is there a method/function for doing so?
没有方程可以很好地近似趋势线以进行代数求解.
There is no equation which approximates the trend line well enough to solve algebraically.
感谢您的帮助.
推荐答案
我认为删除唯一的答案是一个差劲的举动,尽管我觉得实际工作表中的解释会更好,但是从工作簿中编写代码还是可以的.
I feel deleting the only answer is a bit of a poor move, code from the workbook although although I feel the actual sheet explains it better.
代码来自这里:
http://andypope.info/charts/intersection.htm
'
' Algebra taken from various sources on the WWW
'
Option Explicit
Public Function IntersectComplex(x1 As Double, y1 As Double, x2 As Double, y2 As Double, LineCoordinates As Range, Axis As Boolean) As Variant
'
' Complex Intersect.
' Because the line segments are not uniformly spaced the (xy,y1)(x2,y2) could cross
' at any point along the other line
'
' Return
' If intersection
' requested coordinate
' else
' nothing
' endif
' Axis=True returns X value
' Axis=False returns Y value
'
Dim dblCrossX As Double
Dim dblCrossY As Double
Dim dblTestx1 As Double
Dim dblTesty1 As Double
Dim dblTestx2 As Double
Dim dblTesty2 As Double
Dim intSegment As Integer
With LineCoordinates
For intSegment = 1 To .Rows.Count - 1
dblTestx1 = .Cells(intSegment, 1)
dblTesty1 = .Cells(intSegment, 2)
dblTestx2 = .Cells(intSegment + 1, 1)
dblTesty2 = .Cells(intSegment + 1, 2)
If m_CalculateIntersection(x1, y1, x2, y2, dblTestx1, dblTesty1, dblTestx2, dblTesty2, dblCrossX, dblCrossY) Then
If Axis Then
IntersectComplex = dblCrossX
Else
IntersectComplex = dblCrossY
End If
Exit Function
End If
Next
' Special check for last pairing
intSegment = .Rows.Count
dblTestx1 = .Cells(intSegment, 1)
dblTesty1 = .Cells(intSegment, 2)
dblTestx2 = .Cells(intSegment, 1)
dblTesty2 = .Cells(intSegment, 2)
If m_CalculateIntersection(x1, y1, x2, y2, dblTestx1, dblTesty1, dblTestx2, dblTesty2, dblCrossX, dblCrossY) Then
If Axis Then
IntersectComplex = dblCrossX
Else
IntersectComplex = dblCrossY
End If
Exit Function
End If
End With
IntersectComplex = CVErr(xlErrNA) ' Null
End Function
Private Function m_CalculateIntersection(x1 As Double, y1 As Double, x2 As Double, y2 As Double, _
x3 As Double, y3 As Double, x4 As Double, y4 As Double, _
ByRef CrossX As Double, ByRef CrossY As Double) As Variant
'Call with x1,y1,x2,y2,x3,y3,x4,y4 and returns intersect,x,y
'
'Where:
' x1,y1,x2,y2,x3,y3,x4,y4 are the end points of two line segments
'Returns:
' intersect is true/false, and x,y is the interecting point if intersect is true
'
'Description:
'
'Equations for the lines are:
' Pa = P1 + Ua(P2 - P1)
' Pb = P3 + Ub(P4 - P3)
'
'Solving for the point where Pa = Pb gives the following equations for ua and ub
'
' Ua = ((x4 - x3) * (y1 - y3) - (y4 - y3 ) * (x1 - x3)) / ((y4 - y3) * (x2 - x1)
' - (x4 - x3) * (y2 - y1))
' Ub = ((x2 - x1) * (y1 - y3) - (y2 - y1 ) * (x1 - x3)) / ((y4 - y3) * (x2 - x1)
' - (x4 - x3) * (y2 - y1))
'
'Substituting either of these into the corresponding equation for the line gives
' the intersection point.
'For example the intersection point (x,y) is
' x = x1 + Ua(x2 - x1)
' y = y1 + Ua(y2 - y1)
'
'Notes:
' - The denominators are the same.
'
' - If the denominator above is 0 then the two lines are parallel.
'
' - If the denominator and numerator are 0 then the two lines are coincident.
'
' - The equations above apply to lines,
' if the intersection of line segments is
' required then it is only necessary to test if ua and ub lie between 0 and 1.
' Whichever one lies within that range then the corresponding line segment
' contains the intersection point. If both lie within the range of 0 to 1 then
' the intersection point is within both line segments.
'
Dim dblDenominator As Double
Dim dblUa As Double
Dim dblUb As Double
'Pre calc the denominator, if zero then
' both lines are parallel and there is no
' intersection
dblDenominator = ((y4 - y3) * (x2 - x1) - (x4 - x3) * (y2 - y1))
If dblDenominator <> 0 Then
'Solve for the simultaneous equations
dblUa = ((x4 - x3) * (y1 - y3) - (y4 - y3) * (x1 - x3)) / dblDenominator
dblUb = ((x2 - x1) * (y1 - y3) - (y2 - y1) * (x1 - x3)) / dblDenominator
Else
If (x1 = x3) And (y1 = y3) Then
CrossX = x1
CrossY = y1
m_CalculateIntersection = True
Else
m_CalculateIntersection = False
End If
Exit Function
End If
'Could the lines intersect?
If dblUa >= 0 And dblUa <= 1 And dblUb >= 0 And dblUb <= 1 Then
'Calculate the intersection point
CrossX = x1 + dblUa * (x2 - x1)
CrossY = y1 + dblUa * (y2 - y1)
'Yes, they do
m_CalculateIntersection = True
Else
'No, they do not
m_CalculateIntersection = False
End If
End Function
这篇关于在Excel绘图中找到相交点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!