Excel公式查找最大值/最小值

debugcn 发表于 Dev

用户名

我有两列，即X和Y列，它具有数据点，在绘制图表时（200多个数据点），它绘制了一个手绘正方形。我的职责是找到四个角的（X，y）点。为此，我需要找到X的最小值和Y最大值，以获取左上角点

我将需要确定最大X点和最大Y值，以找到左下角的点，依此类推。

但是，我不确定该怎么做，因为我可以找到X / Y的最大值/最小值，但是找不到四对角。有什么建议吗？

谢谢

geoB

如果边始终平行于坐标系，则这些点为：

A: min(x), min(y)
B: min(x), max(y)
C: max(x), min(y)
D: max(x), max(y)

如果两边不平行，则需要更加巧妙：

A: min(x), the value of y where x is minimum
B: the value of x where y is maximum,  max(y)
C: max(x), the value of y where x is maximum
D: the value of x where y is minimum,  min(y)

只有在最小或最大x处只有一个y且最小和最大x处只有一个x的情况下，这才起作用。该代码需要完成所有这些工作。

假设坐标在Sheet1中，x坐标在范围名称“ x”中，y坐标在范围名称“ y”中，以下代码应返回角坐标：

Option Explicit
Sub addresses()
Dim wks As Worksheet
Dim sXMinAddress As String, sXMaxAddress As String
Dim sXMinAddressY As String, sXMaxAddressY As String
Dim xMin As Double, xMax As Double
Dim xMinY As Double, xMaxY As Double
Dim sYMinAddress As String, sYMaxAddress As String
Dim sYMinAddressX As String, sYMaxAddressX As String
Dim yMin As Double, yMax As Double
Dim yMinX As Double, yMaxX As Double
Dim testAddress As String, i As Integer

Set wks = Worksheets("Sheet1")

sXMaxAddress = wks.Range("x").Cells(1, 1).Address
sXMinAddress = wks.Range("x").Cells(1, 1).Address
sXMaxAddressY = wks.Range(sXMaxAddress).Offset(0, 1).Address
sXMinAddressY = wks.Range(sXMinAddress).Offset(0, 1).Address
xMax = wks.Range(sXMaxAddress).Value
xMin = wks.Range(sXMinAddress).Value

sYMaxAddress = wks.Range("y").Cells(1, 2).Address
sYMinAddress = wks.Range("y").Cells(1, 2).Address
sYMaxAddressX = wks.Range(sYMaxAddress).Offset(0, -1).Address
sYMinAddressX = wks.Range(sYMinAddress).Offset(0, -1).Address
yMax = wks.Range(sYMaxAddress).Value
yMin = wks.Range(sYMinAddress).Value

For i = 2 To wks.Range("x").Count
    testAddress = wks.Range("x").Cells(i, 1).Address
    If wks.Range(testAddress).Value < xMin Then
        xMin = wks.Range(testAddress).Value
        sXMinAddress = testAddress
        sXMinAddressY = wks.Range(testAddress).Offset(0, 1).Address
        xMinY = wks.Range(sXMinAddressY).Value
    End If
    If wks.Range(testAddress).Value > xMax Then
        xMax = wks.Range(testAddress).Value
        sXMaxAddress = testAddress
        sXMaxAddressY = wks.Range(testAddress).Offset(0, 1).Address
        xMaxY = wks.Range(sXMaxAddressY).Value
    End If
Next i

For i = 2 To wks.Range("y").Count
    testAddress = wks.Range("y").Cells(i, 1).Address
    If wks.Range(testAddress).Value < yMin Then
        yMin = wks.Range(testAddress).Value
        sYMinAddress = testAddress
        sYMinAddressX = wks.Range(testAddress).Offset(0, -1).Address
        yMinX = wks.Range(sYMinAddressX).Value
    End If
    If wks.Range(testAddress).Value > yMax Then
        yMax = wks.Range(testAddress).Value
        sYMaxAddress = testAddress
        sYMaxAddressX = wks.Range(testAddress).Offset(0, -1).Address
        yMaxX = wks.Range(sYMaxAddressX).Value
    End If
Next i
Debug.Print "Vertices:"
Debug.Print "A: " & xMin & ", " & xMinY
Debug.Print "B: " & xMax & ", " & xMaxY
Debug.Print "C: " & yMaxX & ", " & yMax
Debug.Print "D: " & yMinX & ", " & yMin

Debug.Print "Sheet addresses"
Debug.Print "A: " & sXMinAddress & "," & sXMinAddressY
Debug.Print "B: " & sYMaxAddressX & "," & sYMaxAddress
Debug.Print "C: " & sXMaxAddress & "," & sXMaxAddressY
Debug.Print "D: " & sYMinAddressX & "," & sYMinAddress
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。