Google Sheets公式动态范围引用实现及DIV/0报错解决方案

问题背景 #

我正在计算数值范围的平均值，该逻辑属于大型公式的一部分，仅当前环节遇到问题。期望范围的第一个单元格使用常规引用，如示例中的M列第1行，范围的第二个单元格引用通过函数生成，示例中为M列第10行，最初编写的公式如下：
=AVERAGE("M1"&":"&Address(10,13,4))
后续计划将上述公式中的"10"替换为其他函数返回值，这部分暂不存在问题。

遇到的报错 #

使用上述公式时返回DIV/0!错误，看起来AVERAGE函数接收到了范围参数但并非预期的正确范围，修改Address函数的参数为其他任意值也会出现同样报错。我并不局限于使用Address函数，若有其他函数可实现范围第二个引用的动态调整也可推荐。

解决方案 #

已验证可行方案 #

将AVERAGE括号内的文本用Indirect函数包裹后，即可被识别为正常的范围引用，修正后的公式示例如下：
=INDIRECT("M1"&":"&Address(RIGHT(A1,3),16,4))
原理说明：用&拼接出来的地址本质是文本字符串，AVERAGE不会自动将文本格式的地址识别为实际单元格范围，因此找不到可计算的数值返回错误。INDIRECT函数的作用就是把文本形式的单元格地址转换为实际的引用，让AVERAGE可以正常读取范围中的数值完成计算。

更优替代方案 #

如果不想使用易失性函数INDIRECT（数据量大时可能拖慢计算速度），可以用INDEX函数实现同样的动态范围效果，稳定性更高，也不会因为插入行/列导致引用偏移，公式示例如下：
=AVERAGE(M1:INDEX(M:M, N))
其中N替换为你原本要代入的动态行号返回值即可。

内容的提问来源于stack exchange，提问作者SugarCereal191126