Excel表提取数据及数量：按输入机号查询所需凸轮及对应数量的公式咨询

前置说明 #

我们先预设表结构如下，你可以根据自己的实际表结构调整公式中的单元格范围：

• 数据源表命名为数据源：A列存储机器编号，B列及之后的列存储对应机器配置的凸轮型号，同一行内重复出现的凸轮即为同产品需要的多个同型号凸轮
• 查询表：A1单元格为用户手动输入的机器编号，A列从第3行开始输出匹配的凸轮型号，B列从第3行开始输出对应总需求数量

方案1：Excel 365/2021及以上版本（动态数组公式，输入后自动溢出结果，无需手动下拉） #

在查询表A3单元格输入以下公式即可自动返回所有凸轮型号和对应数量：

=LET(
    match_row, MATCH(A1, 数据源!$A:$A, 0),
    cam_range, OFFSET(数据源!$B$1, match_row-1, 0, 1, 100),
    cam_list, FILTER(cam_range, cam_range<>""),
    unique_cam, UNIQUE(cam_list),
    qty_list, COUNTIF(cam_range, unique_cam),
    HSTACK(unique_cam, qty_list)
)

公式逻辑说明：

• MATCH定位输入的机器编号在数据源A列的对应行号
• OFFSET提取该行所有存储凸轮型号的单元格区域
• FILTER过滤掉区域内的空单元格
• UNIQUE对凸轮型号去重得到不重复的型号列表
• COUNTIF统计每个型号在原区域的出现次数，即为总需求数量
• HSTACK将型号和数量拼合后自动溢出输出结果

方案2：Excel 2019及更早版本（需手动下拉填充公式） #

第一步：提取不重复凸轮型号 #

在查询表A3单元格输入以下数组公式，输入完成后按Ctrl+Shift+Enter确认生效，之后下拉填充直到出现空白值：

=IFERROR(INDEX(
    OFFSET(数据源!$B$1, MATCH(A$1, 数据源!$A:$A, 0)-1, 0, 1, 100),
    SMALL(
        IF(
            MATCH(OFFSET(数据源!$B$1, MATCH(A$1, 数据源!$A:$A, 0)-1, 0, 1, 100), OFFSET(数据源!$B$1, MATCH(A$1, 数据源!$A:$A, 0)-1, 0, 1, 100), 0)
            =COLUMN($A:$CV),
            COLUMN($A:$CV),
            9999
        ),
        ROW(A1)
    )
), "")

第二步：统计对应数量 #

在查询表B3单元格输入以下公式，下拉填充和A列对齐即可：

=IF(A3="", "", COUNTIF(OFFSET(数据源!$B$1, MATCH(A$1, 数据源!$A:$A, 0)-1, 0, 1, 100), A3))

适配调整说明 #

• 如果同一机器编号对应数据源中多行产品，只需将方案1公式中的cam_range替换为TOCOL(FILTER(数据源!$B:$Z, 数据源!$A:$A=A1, ""), 1)即可实现多行合并统计
• 公式中的100为最大凸轮列数，可根据你的实际最大列数调整，不要超过实际数据范围即可

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