本文介绍了每N行重复Excel公式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试在Excel中添加一个重复公式,该公式将对每个主题的另一列(分数)中的值范围求和,以获取每个主题的总分.

I am trying to add a repeating formula in Excel that will sum a range of values in another column (scores) for each subject to get a Total Score for each subject.

我添加了以下公式,该公式似乎可以正确总计值:

I have added the following formula which seems to total the values correctly:

SUM(OFFSET($ AO $ 2,(ROW()-424)* 424,0,424,1))

SUM(OFFSET($AO$2,(ROW()-424)*424,0,424,1))

您可以在这里看到它

You can see it here

这是我目前通过调整公式得出的结果...

Here is the result I currently have from adapting the formula...

图片2

有人知道如何获得此公式以重复此列(AQ)中的每425行吗?

Does anyone know how can I get this formula to repeat every 425 rows in this column (AQ)?

感谢任何提示.

推荐答案

您可以在AQ2单元格中使用此公式并复制下来:

You could use this formula in cell AQ2 and copy down:

=IF(MOD(ROW()-1,425),"",SUM(OFFSET($AO$2,(ROW()-424)*424,0,424,1)))

它将仅从第426行开始的每第425行执行计算.您可以使用0代替""并设置范围的格式以不显示零.

It will perform the calculation only in every 425th row, starting in row 426. You can use a 0 instead of the "" and format the range not to show zeros.

您的偏移量公式无法正确计算当前行之前的424个单元格的范围.这有点太复杂了.您可以从当前行开始,并在行偏移量中使用负数,如下所示:

Your Offset formula is not correctly calculating the range of the 424 cells preceding the current row. It is a bit too complicated. You can start in the current row and use a negative number in the row offset, like this:

=IF(MOD(ROW()-1,425),"",SUM(OFFSET(AO2,-424,0,424,1)))

因此,从当前行开始向上移动424行,然后使用具有424行的范围.这将合计当前行上方的424行.

So, from the current row, go 424 rows up, then use a range that has 424 rows. That will sum the 424 rows above the current row.

这是屏幕截图,其中公式适用于BE列而不是AQ.相关行之间的行已隐藏.

Edit 2: Here is a screenshot with the formula working on column BE instead of AQ.Rows between the pertinent rows have been hidden.

可以在此处访问示例文件:

The sample file can be accessed here:

https://1drv.ms/x/s!Avd3VBkllUf-7zeMXBcqxfyc9- d-

这篇关于每N行重复Excel公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-20 16:27
查看更多