从矩阵移除0值并按月份聚合非零案件编号的Excel公式问题

你的核心需求是对原公式生成的月度交叉表，按每一列（对应单个月份）单独提取非0的案件编号，同时保留月份与案件列表的对应关系，避免维度不匹配导致的错误。

正确公式实现 #

直接用BYCOL遍历每一列进行单独筛选，就能解决原FILTER的维度错误问题：

=LET(
    a, RetroTable[Case Number],
    b, RetroTable[Retro Units],
    c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])),
    data, a*ABS(b*c),
    BYCOL(data, LAMBDA(col, FILTER(col, col<>0)))
)

公式说明 #

1. data部分：保留你原有的逻辑，生成完整的2414行×27列交叉表数组，单元格为案件编号或0
2. BYCOL+FILTER：
   • BYCOL会逐个处理data中的每一列（对应一个月份）
   • 对每一列col，用FILTER(col, col<>0)筛选出所有非0值，自动生成该月份的案件编号列表，长度随非0值数量变化

补充：带月份标题的完整表格 #

如果需要把月份名称和对应案件列表放在同一行展示，可以用HSTACK组合标题与筛选结果：

=LET(
    months, Analysis!A2#,
    a, RetroTable[Case Number],
    b, RetroTable[Retro Units],
    c, CHOOSECOLS(RetroTable, XMATCH(months, RetroTable[#Headers])),
    data, a*ABS(b*c),
    filtered_lists, BYCOL(data, LAMBDA(col, FILTER(col, col<>0))),
    HSTACK(months, filtered_lists)
)

原公式错误原因 #

你之前直接对二维数组用FILTER，会把所有非0值拉成一维数组，丢失月份列的对应关系；同时因为各列非0值数量不同，Excel无法自动对齐多维数组的维度，从而触发#VALUE!错误。

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