Excel 2021中用数组公式过滤二维数组：移除全零/空行与列

Excel 2021支持动态数组函数，完全可以实现过滤全零或全空行、列的需求，以下是具体的公式方案：

假设待处理的数据区域为A1:E6，使用以下公式即可一次性完成行和列的过滤：

=LET(
    data, A1:E6,
    col_valid, BYCOL(data, LAMBDA(col, SUMPRODUCT(--(col<>0)*(col<>""))>0)),
    cols_filtered, FILTER(data, col_valid),
    row_valid, BYROW(cols_filtered, LAMBDA(row, SUMPRODUCT(--(row<>0)*(row<>""))>0)),
    FILTER(cols_filtered, row_valid)
)

公式逻辑解析： #

• LET函数用于定义变量，简化公式结构，提升可读性
• col_valid：通过BYCOL遍历每一列，用SUMPRODUCT统计列内非零且非空的单元格数量，若数量大于0则标记为有效列
• cols_filtered：用FILTER函数先筛选出所有有效列，移除全零/空的列（比如你示例中的C列）
• row_valid：对筛选后的列，再通过BYROW遍历每一行，同样统计行内非零非空单元格数量，标记有效行
• 最后一次FILTER移除全零/空的行（比如你示例中的第5行），得到最终结果

使用注意： #

• 只需将公式中的data变量替换为你的实际数据区域即可
• 公式会同时排除全零或全空的行/列；若某行/列包含部分零或空，但存在非零非空单元格，会被保留
• 公式结果会自动溢出到相邻单元格，无需手动拖拽填充

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