Excel基于AA至AE列非空值创建新行的公式实现咨询

原公式问题说明 #

你原有公式的核心逻辑存在两个错位：

• MATCH函数直接将A列值作为查找值在AA-AF列范围匹配，查找目标和范围不对应，导致行定位错误
• 仅统计了每行非空值总数，没有对应当前行下第N个非空值的定位逻辑，无法逐个提取AA-AE的非空值生成新行

方案1：Excel 365/2021 动态数组方案（无需下拉，自动溢出结果） #

假设你的原始数据范围为A3:Z100（固定列），需要拆分的多值列范围为AA3:AE100，在空白单元格输入以下公式即可直接生成所有结果：

=LET(
    固定列范围,A3:Z100,
    多值列范围,AA3:AE100,
    总行数,ROWS(固定列范围),
    行序号,SEQUENCE(总行数),
    每行非空数,BYROW(多值列范围,LAMBDA(r,COUNTA(r))),
    展开行号,TOCOL(IF(多值列范围<>"",行序号,NA()),2),
    拆分后值,TOCOL(FILTER(多值列范围,多值列范围<>""),2),
    HSTACK(INDEX(固定列范围,展开行号,SEQUENCE(COLUMNS(固定列范围))),拆分后值)
)

方案2：兼容旧版Excel的下拉方案 #

若使用无动态数组功能的旧版Excel，按以下步骤操作：

• 新增辅助列AF，AF3输入=COUNTA(AA3:AE3)，下拉到所有数据行，统计每行AA-AE的非空值数量
• 新增累计辅助列AG，AG3输入=SUM(AF$3:AF3)，下拉，记录到当前行为止累计需要生成的总行数
• 在结果区第一行（假设为AI3）输入行索引公式：=IF(ROW(A1)>MAX(AG:AG),"",MATCH(ROW(A1)-1,AG:AG,1)+1)，下拉直到出现空白
• 提取固定列信息：比如要获取原A列内容，在AJ3输入=IF(AI3="","",INDEX(A:A,AI3))，向右拉到所有需要的固定列
• 提取拆分后的多值列内容：在固定列最后一列右侧单元格输入=IF(AI3="","",INDEX(AA:AE,AI3,COUNTIF(AI$3:AI3,AI3)))，下拉即可

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