如何用Index+Match+Match返回数组并计算平均值（无VBA高效方案）

针对你的需求，以下是两种高效的公式实现，无需VBA、不修改原始数据，支持跨工作表引用：

1. Excel 365/2021 最优方案（动态数组，大数据高效） #

如果使用支持动态数组的Excel版本，推荐用FILTER+AVERAGE组合，比INDEX+MATCH+MATCH的数组方式更高效且容错性更强：

=AVERAGE(数据工作表!$B:$Z, (数据工作表!$A:$A="Concrete")*(数据工作表!$1:$1=DATE(2024,1,1))))

说明： #

• 把数据工作表替换成实际的目标工作表名称
• $A:$A是材料类别所在列（假设"Concrete"在该列）
• $1:$1是日期所在行（假设"1/1/2024"在该行）
• $B:$Z是数值数据区域
• FILTER会自动筛选出材料为Concrete且日期为1/1/2024的所有数值，直接传递给AVERAGE计算平均值

2. 基于INDEX+MATCH+MATCH的数组方案（兼容所有Excel版本） #

如果必须使用INDEX+MATCH+MATCH逻辑返回数组再计算平均，可使用以下公式：

=AVERAGE(INDEX(数据工作表!$A:$Z, MATCH("Concrete", 数据工作表!$A:$A, 0), MATCH(DATE(2024,1,1), 数据工作表!$1:$1, 0)+SEQUENCE(1, COUNTIF(数据工作表!$1:$1, DATE(2024,1,1)))-1))

说明： #

• MATCH("Concrete", ...)定位到Concrete所在的行号
• COUNTIF(...)统计日期"1/1/2024"在表头出现的次数
• SEQUENCE生成连续的列偏移量，配合MATCH返回的首个日期列号，定位所有目标列
• INDEX返回该行所有目标列的数值数组，最后由AVERAGE计算平均
• 若日期列不连续，此公式不适用，建议改用第一种方案

旧版Excel（无动态数组）的数组公式 #

如果使用Excel 2019及更早版本，需输入以下数组公式后按Ctrl+Shift+Enter确认：

=AVERAGE(IF((数据工作表!$A:$A="Concrete")*(数据工作表!$1:$1=DATE(2024,1,1)), 数据工作表!$A:$Z))

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