如何根据单元格内容将Excel指定列动态复制到另一工作表（非空）

背景 #

• S1工作表：含约100列、7500+行数据，首行为表头标识，A列是时间戳；新数据定期插入至第2行，每次新增1行。
• S2工作表现有配置：
  • S2!A2=AVERAGE(B:B)
  • S2!A3=STDEV.S(B:B)
  • S2!C1='Z-SCORE'
  • S2!Ci=(Bi-$A$2)*$A$3（i≥2）
  • S2!A1为用户输入的目标表头名称（如P_ID1）

核心需求 #

需实现公式满足：

1. 定位S1首行中与S2!A1内容匹配的表头
2. 将该表头对应列的有效数据提取至S2!B:B
3. 支持动态更新（S1新增数据时自动同步）
4. 排除S1中空单元格转换的0值，返回纯净动态数组

失败尝试记录 #

• 尝试1：S2!B1=S1!B:B
  问题：返回全列动态数组，但填充大量空值转0，导致平均值、标准差计算失效，且无法定位目标列。
• 尝试2：S2!B1=FILTER(S1!B:B, ISNUMBER(S1!B:B) + ISTEXT(S1!B:B))
  问题：能提取有效数据并动态更新，但不依赖S2!A1，无法切换目标分析列。
• 尝试3：逐行使用HLOOKUP

  S2!B1=HLOOKUP(S2!$A$1, S1!$A$1:$??, 1)
  S2!B2=HLOOKUP(S2!$A$1, S1!$A$1:$??, 2)
  ...
  

  问题：需预先固定范围，无法动态适配S1的行数变化，计算量大且构建繁琐。

解决方案公式 #

在S2!B1中输入以下公式：

=FILTER(INDEX(S1!$A:$ZZ, , MATCH(S2!$A$1, S1!$1:$1, 0)), INDEX(S1!$A:$ZZ, , MATCH(S2!$A$1, S1!$1:$1, 0))<>"")

公式说明 #

• MATCH(S2!$A$1, S1!$1:$1, 0)：精准匹配S1首行中与S2!A1一致的表头列号
• INDEX(S1!$A:$ZZ, , 列号)：提取该列的全部数据（$A:$ZZ可根据实际列数调整，确保覆盖所有表头）
• FILTER(..., 列数据<>"")：过滤掉列中的空单元格，避免空值转换为0影响后续统计计算
• 动态数组特性：S1新增数据时，公式会自动扩展更新结果，无需手动调整范围

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