如何遍历单元格区域查找非零值并返回对应行列关联数据？

适用场景 #

针对你通过FILTER函数得到的筛选区域，需要提取所有非零值，并拼接成「日期（F列）+ 类别（第2行）+ 非零值」的格式。

方案1：Excel 365/2021 动态数组版（推荐） #

利用动态数组函数封装逻辑，一次性返回所有结果，无需下拉填充：

=LET(
    FilteredData, FILTER(你的原数据区域, 你的筛选条件),  // 替换为实际FILTER公式
    DateColumn, F:F,  // 日期所在的F列，可缩小范围如F3:F100提升效率
    CategoryRow, 2:2,  // 类别所在第2行，可缩小范围如B2:Z2
    // 转换筛选区域的行/列号为原表格实际行/列
    OrigRows, SEQUENCE(ROWS(FilteredData)) + MIN(ROW(FilteredData)) - 1,
    OrigCols, SEQUENCE(COLUMNS(FilteredData)) + MIN(COLUMN(FilteredData)) - 1,
    // 扁平化筛选区域及对应行/列索引
    FlatData, TOCOL(FilteredData, 1),
    MatchRows, TOCOL(INDEX(OrigRows, SEQUENCE(ROWS(FilteredData)), SEQUENCE(COLUMNS(FilteredData))), 1),
    MatchCols, TOCOL(INDEX(OrigCols, SEQUENCE(ROWS(FilteredData)), SEQUENCE(COLUMNS(FilteredData))), 1),
    // 筛选非零值的位置
    NonZeroPositions, FILTER(SEQUENCE(ROWS(FlatData)), FlatData <> 0),
    // 拼接目标格式
    IFERROR(
        TEXT(INDEX(DateColumn, INDEX(MatchRows, NonZeroPositions)), "m/d") & " " & 
        INDEX(CategoryRow, INDEX(MatchCols, NonZeroPositions)) & " " & 
        INDEX(FlatData, NonZeroPositions),
        ""
    )
)

公式说明

• LET：定义中间变量，简化公式结构并减少重复计算
• OrigRows/OrigCols：将筛选区域的相对行/列转换为原表格的实际行/列号，确保能准确匹配F列日期和第2行类别
• TOCOL(...,1)：将二维筛选区域转为一维数组，同时忽略空值（无需忽略则改为TOCOL(...,0)）
• NonZeroPositions：定位一维数组中非零值的索引，后续用于提取对应数据
• 最后通过INDEX提取对应字段，用TEXT格式化日期后拼接成目标样式

方案2：旧版Excel 数组公式版 #

如果使用Excel 2019及更早版本，用数组公式逐个提取结果（需按Ctrl+Shift+Enter确认，然后下拉填充至出现空值）：

=IFERROR(
    TEXT(INDEX(F:F, SMALL(IF(FilteredData<>0, ROW(FilteredData)), ROW(A1))),"m/d") & " " & 
    INDEX(2:2, SMALL(IF(FilteredData<>0, COLUMN(FilteredData)), ROW(A1))) & " " & 
    INDEX(FilteredData, SMALL(IF(FilteredData<>0, ROW(FilteredData)-MIN(ROW(FilteredData))+1), ROW(A1)), SMALL(IF(FilteredData<>0, COLUMN(FilteredData)-MIN(COLUMN(FilteredData))+1), ROW(A1))),
    ""
)

使用注意

• 把公式中的FilteredData替换为你的FILTER函数结果（或直接嵌入FILTER公式）
• 首次输入后必须按Ctrl+Shift+Enter触发数组计算，否则无法生效

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