Excel公式需求：查找行内首个空白单元格及计算数据起始与缺口年

数据集 #

1. 生成「数据起始年」列公式 #

Excel 365/2021（动态数组版本） #

假设「数据起始年」列第一个单元格为S2，输入：

=XLOOKUP(TRUE, B2:R2<>"", B$1:R$1, NA())

下拉填充即可，公式会自动匹配每行第一个非空单元格对应的年份。

旧版Excel（无动态数组） #

使用INDEX+MATCH组合，输入后按Ctrl+Shift+Enter作为数组公式执行：

=INDEX(B$1:R$1, MATCH(TRUE, B2:R2<>"", 0))

2. 生成「首个数据缺口年」列公式 #

核心逻辑：定位到数据起始年之后的范围，查找第一个空白单元格对应的年份，无缺口则返回NA。

Excel 365/2021（动态数组版本） #

假设「首个数据缺口年」列第一个单元格为T2，输入：

=LET(
    start_year, S2,
    start_col, MATCH(start_year, B$1:R$1, 0),
    target_data, OFFSET(B2, 0, start_col-1, 1, COLUMNS(B2:R2)-start_col+1),
    target_years, OFFSET(B$1, 0, start_col-1, 1, COLUMNS(B$1:R$1)-start_col+1),
    XLOOKUP(TRUE, target_data="", target_years, NA())
)

下拉填充即可，LET函数让逻辑更清晰，分步截取起始年后的数据范围再查找缺口。

旧版Excel（无动态数组） #

输入后按Ctrl+Shift+Enter作为数组公式执行：

=IFERROR(INDEX(B$1:R$1, MATCH(TRUE, OFFSET(B2,0,MATCH(S2,B$1:R$1,0)-1,1,COLUMNS(B2:R2)-MATCH(S2,B$1:R$1,0)+1)="",0)+MATCH(S2,B$1:R$1,0)-1), NA())

注意事项 #

• 公式中的单元格范围需根据实际表格调整，带$的部分为固定表头行，确保下拉时不偏移
• 若空白单元格是空格而非空值，需将公式中的""替换为TRIM(cell)=""

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