Excel多表数据提取求助：用INDEX+MATCH组合函数制作考勤报表

嘿，我来帮你搞定这个考勤报表的问题！根据你的需求，我整理了一套用INDEX+MATCH（配合其他辅助函数）来跨表提取数据、再按年月和语言等级分组统计的具体步骤：

一、先做基础准备：建立语言等级与考勤表的映射 #

因为不同语言等级的课程在不同日期（对应不同的考勤表），首先新建一个「映射表」工作表，把语言等级和对应的考勤表名对应起来，比如：

这样后续公式不用硬编码表名，修改起来更灵活。

二、用INDEX+MATCH跨表提取单个考勤状态 #

假设你的汇总表结构是：

• A列：年月（格式为mm/yyyy，比如10/2024）
• B列：语言等级（A1~C2）
• C列：提取的考勤状态（0/1）

在C2单元格输入以下公式（可以下拉填充）：

=INDEX(
    INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$C:$C"),
    MATCH(
        A2&"|"&B2,
        TEXT(INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$A:$A"), "mm/yyyy")&"|"&INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$B:$B"),
        0
    )
)

公式说明： #

1. VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)：根据当前语言等级，从映射表找到对应的考勤表名
2. INDIRECT(...)：用表名引用对应的工作表列（比如周一考勤!$C:$C就是周一表的考勤状态列）
3. TEXT(..., "mm/yyyy")：把考勤表里的日期转换成和汇总表一致的年月格式
4. A2&"|"&B2：用分隔符|拼接年月和语言等级，避免匹配时出现歧义
5. 最后用MATCH找到匹配的行，INDEX提取对应的考勤状态

三、直接统计指定分组的出勤次数（1的数量） #

如果不需要单独提取每个考勤状态，直接统计每个「年月+语言等级」分组下1的数量，可以用COUNTIFS函数更高效，在汇总表的D列输入：

=COUNTIFS(
    INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$A:$A"),
    ">="&DATE(RIGHT(A2,4), LEFT(A2,2), 1),
    INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$A:$A"),
    "<="&EOMONTH(DATE(RIGHT(A2,4), LEFT(A2,2), 1), 0),
    INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$B:$B"),
    B2,
    INDIRECT(VLOOKUP(B2, 映射表!$A:$B, 2, FALSE)&"!$C:$C"),
    1
)

公式说明： #

1. DATE(RIGHT(A2,4), LEFT(A2,2), 1)：把汇总表的mm/yyyy转换成当月第一天的日期
2. EOMONTH(..., 0)：获取当月最后一天的日期，用来筛选该月的所有记录
3. 用COUNTIFS同时筛选：指定年月的日期、对应语言等级、考勤状态为1的记录，直接返回数量

几个注意事项 #

• 确保所有考勤表的结构完全一致：A列是日期，B列是语言等级，C列是考勤状态（如果列位置不同，要修改公式里的列号）
• 映射表的语言等级要和考勤表里的完全匹配（包括大小写、空格，比如不能映射表是A1，考勤表里是A 1）
• 如果同一个语言等级在多个日期表有数据，可以把映射表改成一对多，再用数组公式或者SUMPRODUCT来汇总，不过根据你的描述应该是一对一的情况，上面的公式足够用

内容的提问来源于stack exchange，提问作者Marek Klučka