如何使用VLOOKUP函数实现多值的横向排序与匹配？

背景说明 #

• 每月安排检查员到门店开展1-2次审计，建立门店评分体系
• 审计基础信息（日期、门店、检查员等）记录在首页工作表
• 另有scores工作表用于存储评分数据

核心需求 #

需在scores工作表中实现以下功能：

1. 使用VLOOKUP函数按门店横向展示指定月份（例如2024年1月）的多次审计分数
2. 同步展示对应审计的检查员姓名
3. 计算该门店在指定月份的审计平均分

实现思路示例 #

获取指定月份的审计分数 #

假设首页数据中，A列为审计日期，B列为门店名称，C列为检查员姓名，D列为评分，在scores工作表中：

• 针对某门店（如B2单元格为门店名称），获取匹配指定月份的第一次审计分数：

  =VLOOKUP(B2,INDEX(首页!$B:$D,MATCH(1,(YEAR(首页!$A:$A)=2024)*(MONTH(首页!$A:$A)=1)*(首页!$B:$B=B2),0),0),3,FALSE)
  

注：Excel旧版本需按Ctrl+Shift+Enter作为数组公式输入，Excel 365/Google Sheets直接回车即可

获取对应检查员姓名 #

调整VLOOKUP的列索引，即可同步获取检查员信息：

=VLOOKUP(B2,INDEX(首页!$B:$D,MATCH(1,(YEAR(首页!$A:$A)=2024)*(MONTH(首页!$A:$A)=1)*(首页!$B:$B=B2),0),0),2,FALSE)

计算平均分 #

将该门店多次审计的分数单元格引用后，使用AVERAGE函数计算：

=AVERAGE(E2:F2)

（假设E2、F2为该门店两次审计的分数存储单元格）

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