如何编写跨数组用SUMIF的函数，实现Excel/Sheets多条件聚合计算？

需求梳理 #

需通过单个函数完成以下逻辑：

• 按employee筛选数据
• 对每个date分别求和measure_a与measure_b
• 筛选出sum_measure_a >= 30的日期分组
• 计算这些符合条件日期组的sum_measure_b平均值

Excel 365 实现方案 #

依托动态数组函数组合完成，假设数据区域为A2:D9（表头在A1:D1）：

单个员工计算（如AA） #

=LET(
    target_emp, "AA",
    emp_dates, UNIQUE(FILTER(B2:B9, A2:A9=target_emp)),
    daily_sum_a, SUMIFS(C2:C9, A2:A9=target_emp, B2:B9=emp_dates),
    daily_sum_b, SUMIFS(D2:D9, A2:A9=target_emp, B2:B9=emp_dates),
    valid_sum_b, FILTER(daily_sum_b, daily_sum_a>=30),
    IFERROR(AVERAGE(valid_sum_b), 0)
)

批量生成所有员工结果 #

一次性输出所有员工的计算结果，匹配示例表格格式：

=LET(
    all_emps, UNIQUE(A2:A9),
    calc_results, BYROW(all_emps, LAMBDA(emp,
        LET(
            emp_dates, UNIQUE(FILTER(B2:B9, A2:A9=emp)),
            daily_sum_a, SUMIFS(C2:C9, A2:A9=emp, B2:B9=emp_dates),
            daily_sum_b, SUMIFS(D2:D9, A2:A9=emp, B2:B9=emp_dates),
            valid_sum_b, FILTER(daily_sum_b, daily_sum_a>=30),
            IFERROR(AVERAGE(valid_sum_b), 0)
        )
    )),
    HSTACK(all_emps, calc_results)
)

Google Sheets 实现方案 #

可通过QUERY函数简化逻辑，或使用动态数组组合：

单个员工计算（如AA） #

=AVERAGE(FILTER(QUERY(A2:D9, "SELECT B, SUM(C), SUM(D) WHERE A='AA' GROUP BY B"), INDEX(QUERY(A2:D9, "SELECT B, SUM(C), SUM(D) WHERE A='AA' GROUP BY B"),,2)>=30,,3))

简洁批量输出方案 #

通过嵌套QUERY直接生成带表头的结果表格：

=QUERY(QUERY(A2:D9, "SELECT A, B, SUM(C), SUM(D) GROUP BY A, B HAVING SUM(C)>=30"), "SELECT A, AVG(Col4) GROUP BY A LABEL AVG(Col4) 'mean_measure_b'")

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