Excel中如何基于Sheet2最近5条记录生成Sheet1的Form列？

前提说明 #

• Sheet1：A列为唯一人员列表（纵向排列），需在B列（命名为Form）生成对应人员的最近5条记录内容
• Sheet2：A列为纵向日期，第一行（B1、C1……）为横向排列的唯一人员姓名，对应列下方是该人员的历史记录

操作步骤 #

1. Sheet1的Form列公式输入
   在Sheet1的B2单元格输入以下公式，按Ctrl+Shift+Enter（旧版Excel需触发数组公式，新版Excel自动识别）后，下拉填充到所有人员行：

=TEXTJOIN("、", TRUE, INDEX(Sheet2!$B:$ZZ, LARGE(ROW(Sheet2!$A$2:$A$1000)*(Sheet2!$A$2:$A$1000<>""), ROW(INDIRECT("1:5"))), MATCH(A2, Sheet2!$B$1:$ZZ$1, 0)))

2. 公式参数说明

   • MATCH(A2, Sheet2!$B$1:$ZZ$1, 0)：定位当前人员在Sheet2表头中的对应列号
   • LARGE(ROW(Sheet2!$A$2:$A$1000)*(Sheet2!$A$2:$A$1000<>""), ROW(INDIRECT("1:5")))：筛选Sheet2中存在日期的有效行，取行号最大的5个（即最近5条记录的行）
   • INDEX(Sheet2!$B:$ZZ, 行号, 列号)：提取对应行、列的记录内容
   • TEXTJOIN("、", TRUE, ...)：将5条记录用顿号拼接成文本，自动忽略空值

3. 新增人员时的范围调整
   若Sheet2新增人员列（比如扩展到D列），直接修改公式中的表头范围Sheet2!$B$1:$ZZ$1和数据列范围Sheet2!$B:$ZZ即可，例如改为Sheet2!$B$1:$D$1和Sheet2!$B:$D。如果Sheet2的记录行数超过1000行，同步调整Sheet2!$A$2:$A$1000的行范围即可。

注意事项 #

• 确保Sheet2的日期列（A列）无空行，否则会影响最近记录的判断逻辑
• 若某人员历史记录不足5条，公式会自动取现有所有记录，不会填充空值

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