获取Google表格中最后X行的范围 [英] Get the range of the last X rows in Google Sheets
问题描述
说我有一个包含18行的电子表格,我可以对D列中的最后7个值的范围进行硬编码,并像这样对它们的值求平均值:
Say I have a spreadsheet with 18 rows, and I can hard code the range of the last 7 values in column D and average their values like so:
=AVERAGE(D12:D18)
那我怎么能在不进行硬编码的情况下做同样的事情,所以当我添加更多的行时它会起作用吗?
How then could I do the same without hard coding them, so it'll work as I add more rows?
推荐答案
如果要计算平均值或总和,则不需要脚本.您也可以使用数组过滤器来完成此操作.下面的公式计算A列中最后7行的平均值:
If you want to calculate the average or sum, there's no need for a script. You can accomplish this with an array filter as well. The following formula calculates the average over the last 7 rows in the A column:
=AVERAGE(FILTER(A:A;ARRAYFORMULA(ROW(A:A)>COUNT(A:A)-7+1)))
这假定数据从第1行开始.否则,您必须将公式中的后一个常数更改为数据开始的行号.
This assumes that the data starts at row 1. Otherwise you have to change the latter constant in the formula to the row number where the data starts.
这篇关于获取Google表格中最后X行的范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!