蒂姆有一个工作表,其中单元格B1包含公式= SUM(A1:A7)。

他想向下复制此公式并将范围增加7行,以便单元格B2将包含公式= SUM(A8:A14),单元格B3将包含= SUM(A15:A21),依此类推。问题是当他将其复制下来时,范围的每个“结束”仅增加1,应将其增加7以满足其需要。他想知道如何使Excel进行适当的递增。

您无法使用复制和粘贴使Excel进行适当的增量;它只是不会做。原因很简单-从公式的角度来看,有时候增加1是有意义的。由于Excel无法读懂您的想法(至少直到下一个版本:>为止),因此假定它只能以1递增。

解决方案是更改您的公式。使用几个工作表函数,您可以让Excel构造所需的求和范围。考虑以下提供所需总和的公式示例:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

如果将此公式放到单元格B1中,那么它将起作用,因为它会查看包含该公式的行的行号(由ROW函数返回)。由于它在第1行中,因此该公式通过Excel以这种方式求值:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

您最终在B1中得到的是所需的总和。 (INDIRECT函数使用字符串中的值,就好像它是真实范围一样,这就是您想要的。)将公式向下复制到列中时,随着行数的增加,公式在两端都提供适当的7增量。的范围。

您可以使用此技术的其他变体。唯一的区别是,变体使用不同的工作表功能来完成同一任务。例如,以下变体仍然使用ROW函数,但最终还是依靠OFFSET函数来计算所需范围:

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

使用OFFSET的较短方法如下:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

无论采用哪种方法,您都可以说出这个主意是想出一个公式,该公式使用公式所在的行来构造您真正想要的范围。到目前为止,每个示例都假设您从单元格B1开始。如果要在单元格B2中开始,则需要修改公式以说明要开始的任何行。为了让您对它的工作原理有所了解,如果您是从单元格B2开始的,则可以通过以下方式修改本技巧中介绍的三个公式:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

从其他位置开始,您将需要对选择使用的公式进行进一步的修改。

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

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