如何跳过Sheet2计算，直接在Sheet3获取按ID分组的Top2求和值？

核心逻辑拆解 #

原流程的本质是：

• 给Sheet2的每条数据匹配Sheet1中对应lookup_id的S1+S2值
• 按ID分组求和，得到每个ID的总合计
• 从这些分组合计里提取Top2值

我们可以把这些逻辑直接嵌套成公式，跳过Sheet2的中间计算步骤。

方案1：适用于Excel 365/2021（支持动态数组） #

在Sheet3的A2单元格（用于放置Top1）输入以下公式，下拉到A3即可得到Top2：

=LARGE(
    BYROW(UNIQUE(Sheet2!$B$2:$B$10), LAMBDA(id,
        SUM(XLOOKUP(Sheet2!$C$2:$C$10*(Sheet2!$B$2:$B$10=id), Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10+Sheet1!$C$2:$C$10, 0))
    )),
    ROW(A1)
)

公式说明：

1. UNIQUE(Sheet2!$B$2:$B$10)：提取Sheet2中不重复的ID列表
2. BYROW(..., LAMBDA(id, ...))：遍历每个不重复ID，计算该ID对应的所有S1+S2总和
   • Sheet2!$C$2:$C$10*(Sheet2!$B$2:$B$10=id)：筛选出当前ID对应的所有lookup_id
   • XLOOKUP(..., Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10+Sheet1!$C$2:$C$10, 0)：匹配对应的S1+S2值，无匹配返回0
   • SUM(...)：对当前ID的所有S1+S2值求和
3. LARGE(..., ROW(A1))：从所有分组总和中提取第N大的值（下拉时ROW(A1)自动变为ROW(A2)，对应Top2）

方案2：适用于旧版Excel（无动态数组，需按Ctrl+Shift+Enter确认数组公式） #

在Sheet3的A2单元格输入以下公式，按Ctrl+Shift+Enter后下拉到A3：

=LARGE(
    IF(MATCH(Sheet2!$B$2:$B$10, Sheet2!$B$2:$B$10,0)=ROW(Sheet2!$B$2:$B$10)-1,
        SUMIF(Sheet2!$B$2:$B$10, Sheet2!$B$2:$B$10,
            VLOOKUP(Sheet2!$C$2:$C$10, Sheet1!$A$2:$C$10,2,0)+VLOOKUP(Sheet2!$C$2:$C$10, Sheet1!$A$2:$C$10,3,0)
        ),""),
    ROW(A1)
)

公式说明：

1. MATCH(Sheet2!$B$2:$B$10, Sheet2!$B$2:$B$10,0)=ROW(Sheet2!$B$2:$B$10)-1：判断当前行的ID是否为第一次出现，模拟原流程的去重逻辑
2. SUMIF(Sheet2!$B$2:$B$10, Sheet2!$B$2:$B$10, VLOOKUP(...) + VLOOKUP(...))：计算每个ID对应的所有S1+S2总和
3. IF(...)：只保留每个ID第一次出现时的总和，其余返回空值
4. LARGE(..., ROW(A1))：从有效总和中提取TopN值

注意事项 #

• 公式中的单元格范围（如Sheet2!$B$2:$B$10、Sheet1!$A$2:$C$10）请根据实际数据范围调整
• 若Sheet1中存在lookup_id无匹配的情况，公式会返回0，可根据需求修改XLOOKUP或VLOOKUP的默认值（比如改成""）

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