如何用VLOOKUP函数计算Google Sheets中重复排名的平均分数？

问题背景 #

现有两个Google Sheets表格：

Lookup Sheet（查找表） #

Target Sheet（目标表） #

期望结果要求：重复排名需对应查找表中连续排名范围的分数平均值，具体如下：

当前使用公式 =VLOOKUP(A2, 'Lookup Sheet'!$A$2:$B$8, 2, FALSE) 仅返回单一排名的分数，无法满足平均值需求。

解决方案 #

单纯VLOOKUP无法直接计算范围平均值，需结合AVERAGE、IFERROR、INDEX、FILTER等函数实现动态范围的平均值计算。在Target Sheet的B2单元格输入以下公式，下拉填充即可：

=AVERAGE(
  INDEX('Lookup Sheet'!$B$2:$B$8, MATCH(A2, 'Lookup Sheet'!$A$2:$A$8, 0)):
  INDEX('Lookup Sheet'!$B$2:$B$8, IFERROR(MATCH(MIN(FILTER($A$2:$A$8, $A$2:$A$8>A2)), 'Lookup Sheet'!$A$2:$A$8, 0)-1, ROWS('Lookup Sheet'!$A$2:$A$8)))
)

公式逻辑说明 #

1. 确定范围结束排名：
   • FILTER($A$2:$A$8, $A$2:$A$8>A2)：筛选目标表中大于当前排名的所有值
   • INDEX(..., 1)-1：取第一个大于当前排名的值并减1，得到范围的结束排名；如果没有更大的值（如排名7），则用查找表的最后一个排名
2. 获取分数区间：
   • 用INDEX+MATCH分别定位起始排名和结束排名对应的分数，形成连续的单元格区间
3. 计算平均值：用AVERAGE对该区间内的分数取平均值

注意事项 #

• 确保查找表中的排名连续且无重复，否则公式可能无法正确识别范围
• 公式中的$A$2:$A$8需替换为目标表排名列的实际数据范围；'Lookup Sheet'!$A$2:$B$8替换为查找表的实际数据范围
• 如果目标表会新增行，可将固定范围改为动态引用（如$A:$A），但需注意排除表头行

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