VBA去掉3个最大值和3个最小值的问题

p632264898

测区.rar (13.29 KB, 下载次数: 5)

2020-5-31 10:48 上传

点击文件名下载附件

Sub 回弹值计算()     Dim i As Long, a As Long, b As Long, c As Long, mxa As Long, _     mxa1 As Long, mxa2 As Long, min As Long, min1 As Long, min2 As Long, _     count As Long, sum As Long     For i = 2 To 2         count = 0         sum = 0         mxa = -1         min = 101         For a = 2 To 17             If Cells(i, a) > mxa Then                 mxa = Cells(i, a)             ElseIf Cells(i, a) > mxa1 And Cells(i, a) < mxa Then             mxa1 = Cells(i, a)             ElseIf Cells(i, a) > mxa2 And Cells(i, a) < mxa1 Then             mxa2 = Cells(i, a)             End If             count = count + 1             sum = sum + Cells(i, a)         Next a     Next i    MsgBox mxa2 End Sub 为什么用这种方法算出来的第三个最大值是不对，问题出在哪里

cui26896

Option Explicit

Sub 回弹值计算()
    Dim i As Long, a As Long, b As Long, c As Long, mxa As Long, _
    mxa1 As Long, mxa2 As Long, min As Long, min1 As Long, min2 As Long, _
    count As Long, sum As Long
    For i = 2 To 2
        count = 0
        sum = 0
        mxa = -1
        min = 101
        For a = 2 To 17
            If Cells(i, a) > mxa And Len(mxa) = 0 Then
                mxa = Cells(i, a)
            ElseIf Cells(i, a) > mxa And Len(mxa) Then
               mxa1 = mxa: mxa = Cells(i, a)
            ElseIf (Cells(i, a) > mxa1 And Cells(i, a) < mxa) And Len(mxa1) = 0 Then
              mxa1 = Cells(i, a)
            ElseIf (Cells(i, a) > mxa1 And Cells(i, a) < mxa) And Len(mxa1) Then
              mxa2 = mxa1: mxa1 = Cells(i, a)
            ElseIf Cells(i, a) > mxa2 And Cells(i, a) < mxa1 Then
              mxa2 = Cells(i, a)
            End If
            count = count + 1
            sum = sum + Cells(i, a)
        Next a
    Next i
   MsgBox mxa2
End Sub

p632264898

cui26896 发表于 2020-5-31 18:53
Option Explicit

Sub 回弹值计算()

Option Explicit

Sub 回弹值计算()
    Dim i As Long, a As Long, b As Long, c As Long, mxa As Long, _
    mxa1 As Long, mxa2 As Long, min As Long, min1 As Long, min2 As Long, _
    count As Long, sum As Long
    For i = 2 To 2
        count = 0
        sum = 0
        mxa = -1
        min = 101
        For a = 2 To 17
            If Cells(i, a) > mxa And Len(mxa) = 0 Then
                mxa = Cells(i, a)
            ElseIf Cells(i, a) > mxa And Len(mxa) Then
               mxa1 = mxa: mxa = Cells(i, a)
            ElseIf (Cells(i, a) > mxa1 And Cells(i, a) < mxa) And Len(mxa1) = 0 Then （len这个是什么意思可以解释一下么）
              mxa1 = Cells(i, a)
            ElseIf (Cells(i, a) > mxa1 And Cells(i, a) < mxa) And Len(mxa1) Then
              mxa2 = mxa1: mxa1 = Cells(i, a)（这个能解释一下么）
            ElseIf Cells(i, a) > mxa2 And Cells(i, a) < mxa1 Then
              mxa2 = Cells(i, a)
            End If
            count = count + 1
            sum = sum + Cells(i, a)
        Next a
    Next i
   MsgBox mxa2
End Sub

cui26896

如果该元素字长不为零。

p632264898

cui26896 发表于 2020-5-31 19:56
如果该元素字长不为零。

mxa2 = mxa1: mxa1 = Cells(i, a)（这个能解释一下么）
这个呢？
：代表什么意思

cui26896

本来要分两行写，这样就可以写在同一行

p632264898

cui26896 发表于 2020-5-31 20:03
本来要分两行写，这样就可以写在同一行

mxa2 = mxa1: mxa1 = Cells(i, a)
mxa2=mxa1  
mxa1 = Cells(i, a)
这两个怎么说呢？

cui26896

如果mxa1已经存在有数据了，并且 Cells(i, a)比mxa1大，但比mxa小，但现在mxa2还没有数据，则把原来的mxa1放到mxa2中，再把Cells(i, a)放到mxa1中。