Excel中基于其他列非空值动态填充对应行编号并自动更新的实现方法

嘿，你这个需求我经常碰到，用Excel的函数就能完美搞定，还能实现自动更新的效果。下面分两种场景给你详细说明，适配不同版本的Excel：

一、Excel 365/2021及以后版本：用动态数组函数快速实现（推荐） #

这个版本支持动态数组溢出，不用手动拖拽公式，而且内容变化时会自动更新，非常省心：

1. 填充F列（对应B列的非空行）：
   在F1单元格输入表头后，直接在F2单元格输入公式：

   =FILTER($A$2:$A$7, $B$2:$B$7<>"")
   

   按下回车后，公式会自动溢出填充所有符合条件的行编号——只要B列对应行有内容，就会显示A列的#号，空行则自动跳过。

2. 同理，填充G列（对应C列）和H列（对应D列）：

   • G2单元格公式：

     =FILTER($A$2:$A$7, $C$2:$C$7<>"")
     

   • H2单元格公式：

     =FILTER($A$2:$A$7, $D$2:$D$7<>"")
     

进阶优化：适配动态新增行 #

如果以后会往表格里新增行，建议先把数据转换成Excel表（选中所有数据，按Ctrl+T，勾选“我的表有标题”），然后用表的列名写公式，比如：

=FILTER(Table1[#], Table1[Size]<>"")

这样新增行时，公式会自动识别扩展的范围，不用手动修改公式。

二、旧版Excel（无动态数组支持）：用数组公式实现兼容 #

如果你的Excel版本不支持动态数组（比如2019及更早版本），可以用INDEX+SMALL+IF组合的数组公式：

1. 填充F列：
   在F2单元格输入以下公式，然后按下Ctrl+Shift+Enter（必须按这个组合键触发数组公式，公式会自动加上大括号{}）：

   =IFERROR(INDEX($A$2:$A$7, SMALL(IF($B$2:$B$7<>"", ROW($B$2:$B$7)-ROW($B$2)+1), ROWS($F$2:F2))), "")
   

   然后把F2单元格的公式向下拖拽填充到足够多的行（比如F2到F7），当B列对应行有内容时，会显示对应的#号，没有内容的位置会显示空白。

2. 同理修改G列和H列的公式：

   • G2单元格数组公式（替换成C列的范围）：

     =IFERROR(INDEX($A$2:$A$7, SMALL(IF($C$2:$C$7<>"", ROW($C$2:$C$7)-ROW($C$2)+1), ROWS($G$2:G2))), "")
     

   • H2单元格数组公式（替换成D列的范围）：

     =IFERROR(INDEX($A$2:$A$7, SMALL(IF($D$2:$D$7<>"", ROW($D$2:$D$7)-ROW($D$2)+1), ROWS($H$2:H2))), "")
     

公式说明 #

简单解释下这个数组公式的逻辑：

• IF($B$2:$B$7<>"", ROW($B$2:$B$7)-ROW($B$2)+1)：找出B列非空行的相对行号
• SMALL(..., ROWS($F$2:F2))：按顺序提取这些相对行号
• INDEX($A$2:$A$7, ...)：根据相对行号提取A列的#号
• IFERROR(..., "")：当没有更多符合条件的行时，显示空白

这样修改B-D列的内容后，只要Excel设置了自动计算（默认就是自动计算），F-H列就会自动更新啦。

备注：内容来源于stack exchange，提问作者Mate de Vita