如何修改Excel公式：基于VLOOKUP取三列数据并排除零值计算平均值？

首先得指出你原来公式的问题：你用&把三个VLOOKUP的结果拼接成了文本字符串，这完全搞错了AVERAGEIF的使用逻辑——AVERAGEIF的第一个参数需要是单元格区域，不是拼接后的文本，所以这个公式根本没法正确计算三个值的平均值。

针对你的需求（对三个VLOOKUP返回值求平均，排除零值，同时忽略无匹配的行），分两种场景给你解决方案：

场景1：使用Office 365/2021及以上版本（支持动态数组+LAMBDA） #

这个版本可以直接用BYROW逐行处理，自动溢出结果到整列，公式更简洁：

=BYROW(B:B,LAMBDA(x,AVERAGE(IF(IFERROR(CHOOSE({1,2,3},VLOOKUP(x,$C:$F,2,0),VLOOKUP(x,$C:$F,3,0),VLOOKUP(x,$C:$F,4,0)),"")<>0,IFERROR(CHOOSE({1,2,3},VLOOKUP(x,$C:$F,2,0),VLOOKUP(x,$C:$F,3,0),VLOOKUP(x,$C:$F,4,0)),"")))))

公式各部分作用：

• CHOOSE({1,2,3},...)：把三个VLOOKUP的结果组合成一个横向数组，方便统一处理
• IFERROR(..., "")：把VLOOKUP返回的错误值（比如#N/A）转换成空文本，AVERAGE会自动忽略空值
• IF(<>0,...)：过滤掉数组中的零值，只保留非零值参与计算
• BYROW(..., LAMBDA(x,...))：针对B列的每一行数据，单独计算对应的平均值，结果自动溢出到整列

场景2：使用旧版Excel（无动态数组支持） #

旧版需要用数组公式，针对单个单元格（比如D2）输入后按Ctrl+Shift+Enter确认，再下拉填充：

=AVERAGE(IF(IFERROR({VLOOKUP(B2,$C:$F,2,0),VLOOKUP(B2,$C:$F,3,0),VLOOKUP(B2,$C:$F,4,0)},0)<>0,IFERROR({VLOOKUP(B2,$C:$F,2,0),VLOOKUP(B2,$C:$F,3,0),VLOOKUP(B2,$C:$F,4,0)},0)))

这里把VLOOKUP的错误值转成0，再通过IF(<>0,...)过滤掉零值（包括转成0的错误值），最后用AVERAGE计算剩余非零值的平均值。

额外说明 #

如果你只需要排除零值，不需要处理错误值（比如允许#N/A出现在结果中），可以去掉公式里的IFERROR部分，简化成：

• 新版：

  =BYROW(B:B,LAMBDA(x,AVERAGE(IF(CHOOSE({1,2,3},VLOOKUP(x,$C:$F,2,0),VLOOKUP(x,$C:$F,3,0),VLOOKUP(x,$C:$F,4,0))<>0,CHOOSE({1,2,3},VLOOKUP(x,$C:$F,2,0),VLOOKUP(x,$C:$F,3,0),VLOOKUP(x,$C:$F,4,0))))))
  

• 旧版数组公式：

  =AVERAGE(IF({VLOOKUP(B2,$C:$F,2,0),VLOOKUP(B2,$C:$F,3,0),VLOOKUP(B2,$C:$F,4,0)}<>0,{VLOOKUP(B2,$C:$F,2,0),VLOOKUP(B2,$C:$F,3,0),VLOOKUP(B2,$C:$F,4,0)}))
  

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