凯文(Kevin)需要创建一个公式,该公式求和连续四个单元格。他知道他可以使用公式,例如= A6 + E6 + I6 + M6等,但是如果工作表中有很多列,这将变得很麻烦。

有几种方法可以解决此问题。一种方法是向工作表中添加一些其他信息,以指定应在总和中包括哪些单元格。例如,在示例中,您有兴趣对工作表第6行中的单元格求和。如果您可以在第5行中添加一些指标,则可以在公式中将这些指标用作“触发器”。例如,将数字1放在要包括在总和中的每个单元格上方(列A,E,I,M等)。然后,可以使用以下公式:

=SUMPRODUCT(A5:X5, A6:X6)

该公式基本上将第5行中的内容乘以第6行,然后将结果求和。由于要累加的列中只有1,因此这些都是最终总和中包括的所有内容。

如果您不想在工作表中添加指标行,则需要查看其他解决方案。您仍然可以在以下公式中使用SUMPRODUCT函数:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=1)*(6:6))

此公式依靠MOD函数返回除法的余数。在这种情况下,要除以一个单元格的列号除以值4。这将导致0、1、2或3的余数。行中的每四个单元格将具有相同的余数。因此,列A(也称为列1)的MOD值将为1(1除以4等于0,剩下1),列E,I,M等也将如此。

请注意,该公式比较MOD值是否为1。如果是,则比较返回True(1);如果不是,则返回False(0)。然后将其乘以第六行中的单元格。

最后,SUMPRODUCT对所有这些乘法求和,并得到所需的结果。

尽管此公式提供了第六行中每个第四个单元格的总和,但可以很容易地对其进行更改以提供每个第三个单元格,第五个单元格或所需间隔的总和。只需将MOD功能中的4更改为所需的时间间隔即可。

如果要在要求和的四个像元的每个“群”中选择一个不同的像元,那么您要做的就是更改MOD功能中要比较的值。在此示例中,只有四个群集中的每个群集中的第一个单元的MOD均为1(A,E,I,M等)。如果您想对以单元格C开头的每个第四个单元格求和,则将比较值从1更改为3。为什么?因为C是群集中的第三个单元,并且其MOD为3,所以此后的每个第四个单元(G,K,O等)也将如此。

通用规则唯一的“陷阱”是您是否希望对每个四单元群集中的第四单元求和。例如,您可能想对单元格D,H,L,P等求和。在这种情况下,使用的比较值将不会为4,因为在执行涉及除以4的MOD操作时,永远不会有4的余数。相反,比较值将为0,如下所示:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=0)*(6:6))

如果您更喜欢使用数组公式,则可以对上述公式使用稍短的变体:

=SUM(IF(MOD(COLUMN(6:6),4)=1,6:6))

请注意,应通过按Ctrl + Shift + Enter输入公式。

然后,它将出现在“公式”栏中,并在公式周围带有大括号(\ {})。相对于MOD除数和比较值的相同修改说明​​在这里适用,就像它们对SUMPRODUCT函数所做的一样。

这两种公式化方法(SUMPRODUCT和数组公式)

将整行中的第四个单元格求和。相反,如果您希望将得出总和的单元格限制为该行的一部分,则只需将6:6(两个实例)替换为适当的范围。因此,如果您只想对A6:Z6范围内的每个第四个单元格求和,则可以在公式中使用该范围。

如果您以这种方式进行大量求和,并且不仅将其应用于行中的范围,还应用于列中的范围,则可能需要考虑创建用户定义的函数来进行求和。下面的简单函数可以解决问题:

Function SumEveryFourth(MyRange As Range)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod 4) = 1 Then             SumEveryFourth = SumEveryFourth + MyRange.Cells(x).Value         End If     Next x End Function

该函数检查传递给它的范围,然后将范围内第一个单元格开始的每四个单元格相加。如果您希望它在范围内的每隔两个单元格求和,则如本技巧前面所述,在If语句中更改比较值。 (由于此函数使用Mod操作,并且其操作与MOD工作表功能相同,因此,在确定应对每个群集中的哪个单元格求和时,使用相同的比较值。)

用户定义的函数将在行中的单元格或列中的单元格上正常工作。您只需要确保将其传递给想要的范围即可,如此处所示:

=SumEveryFourth(C3:C57)

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

链接:/ excelribbon-ExcelTipsMacros [点击此处在新的浏览器标签中打开该特殊页面]。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(9203)适用于Office 365中的Microsoft Excel 2007、2010、2013、2016、2019和Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本:

链接:/ excel-Summing_Every_Fourth_Cell_in_a_Row [对一行中的第四个单元格求和]。