问题描述
我正在尝试在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...
有人知道如何获得此公式以重复此列(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公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!