excel如何列向求和求最大值，附公式和VBA、自定义函数对比

原创2021-05-26 09:26·探索3000

如上图，如何求每列合计数中间的最大值？附数据样本

第一列

第二列

第三列

第四列

1

1

5

9

3

1

5

7

3

4

1

6

5

8

1

8

5

1

4

4

8

3

1

3

9

8

6

1

6

4

4

1

2

7

5

1

4

9

3

1

4

5

3

4

有群友给出了公式方案：

26

MAX(MMULT(P1:Z1+1,A5:D15))




41

MAX(SUBTOTAL(9,OFFSET(A:A,,{0,1,2,3},,)))



43

MAX(SUMIF(A:A,">0",OFFSET(A:A,,{0,1,2,3})))



26

MAX(MMULT(D1:N1+1,A5:D15))




45

MAX((MMULT(TRANSPOSE(ROW(A5:A15)^0),A5:D15)))


45

MAX((MMULT(TRANSPOSE(ROW(A5:A15)^0),A5:D15)))


52

MAX(SUM(A5:A15),SUM(B5:B15),SUM(C5:C15),SUM(D5:D15))

也有半自动的方式，先求单列和用sum，再横向拖动公式，求出每列和

最后再用max求最大值

网友的vba方案：

Sub test1() '通用的数组调用工作表函数Sum区域求和法

Dim i, j, k, arr, brr(), msum, mmax

arr = [a5: d15]

For i = 1 To UBound(arr, 2)

'msum = O

' For j = 1 To UBound(arr)

' msum = msum + arr(j, i)

' Next

msum = Application.Sum(Range(Cells(5, i), Cells(UBound(arr) + 4, i)))

If msum > mmax Then mmax = msum

Next

'Range("k13") = mmax

Range("g6") = mmax

End Sub

下面给出我的解决方案

方案一，sub方式

Sub test2()

For i = 1 To 4

msum = Application.WorksheetFunction.Sum(Range(Cells(5, i), Cells(15, i)))

If msum > mmax Then mmax = msum

Next

[G6] = mmax

End Sub

因为用函数可以内部直接算，逻辑上也更简明。只用了for if，不需要数组，sum内部计算也很快。

方案二：function自定义函数方式

Function zsummax(rng As Range) '在range中列向求和再求最大值

Dim i, msum, mmax

For i = rng.Column To rng.Columns.Count

msum = Application.WorksheetFunction.Sum(rng.Range(rng.Cells(rng.Row, i), rng.Cells(rng.Row + rng.Rows.Count, i)))

If msum > mmax Then mmax = msum

Next

zsummax = mmax

End Function

这个自定义函数更适合公式玩家使用。

当然写完了之后，能正确求取结果，很快我又发现其中的bug问题

聪明的你看出来了吗

举报
评论 0
    热门：