为一列中的连续条目找到最高的总计-EXCEL [英] finding the highest total for a consecutive group of entries in a column - EXCEL

问题描述

而不是找到最高的单个条目，我想要连续的n个条目组的最高总数，例如，在100个值的列中，我想查找找到总和最大的10个连续单元格

rather then finding the highest single entry, i want the highest total for a consecutive group of n entries e.g within a column of 100 values i want to look to find 10 consecutive cells whose sum is a maximum

我以10为例，希望能够轻松更改它.

I say 10 as an example i wish to be able to change that easily.

推荐答案

到目前为止，最干净的方法是使用用户定义函数(UDF).

By far the cleanest way to do this is with a User Defined Function (UDF).

这是一个可以完成的小例程.将此例程放在标准代码模块中:

Here is a small routine that will do it. Place this routine in a standard code module:

Function MaxN(n&, r As Range)
    Dim i&, j&, m#, t#, v
    v = r.Value2
    For i = 1 To UBound(v)
        If UBound(v) - i + 1 >= n Then
            t = 0
            For j = i To i + n - 1
                t = t + v(j, 1)
            Next
            If t > m Then m = t
        Else
            Exit For
        End If
    Next
    MaxN = m
End Function

然后在要计算最大值的工作表上，选择一个要返回计算值的单元格，然后输入以下公式:

Then on the worksheet where you want to calculate the max, pick a cell where you'd like to have the calculation return and enter this formula:

=MaxN(10,A1:A100)

就是这样.

使用此UDF，您可以轻松地将'10'更改为所需的值，并且可以通过更改公式轻松调整垂直范围的位置和大小.

With this UDF you can easily change the '10' to whatever you like and you can easily adjust the vertical range location and size by altering the formula.

这篇关于为一列中的连续条目找到最高的总计-EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！