适配Excel 365的Google Sheets概率求和公式转换需求

针对需求，以下是适配Excel 365（版本16.89）的公式，可实现从逗号分隔的动作ID列表中提取非零ID，匹配对应概率并计算平均值：

=LET(
    ids, TEXTSPLIT(U5, ", "),
    non_zero_ids, FILTER(ids, ids<>"0"),
    count_non_zero, COUNTA(non_zero_ids),
    sum_probs, SUM(XLOOKUP(non_zero_ids, Totals!B8:B807, Totals!E8:E807, 0)),
    IF(count_non_zero=0, "", sum_probs/count_non_zero)
)

公式拆解说明 #

• TEXTSPLIT(U5, ", ")：将U5中逗号分隔的动作ID字符串拆分为动态数组，每个ID为单独元素。
• FILTER(ids, ids<>"0")：过滤拆分后的数组，仅保留非零的动作ID，排除无效的0值。
• COUNTA(non_zero_ids)：统计有效非零ID的数量，作为后续除法的分母。
• XLOOKUP(non_zero_ids, Totals!B8:B807, Totals!E8:E807, 0)：批量匹配每个非零ID对应的概率值，若ID在Totals表中不存在，返回0避免错误。
• IF(count_non_zero=0, "", sum_probs/count_non_zero)：处理无有效ID的情况（分母为0），此时返回空值；否则计算概率总和与有效ID数量的商，得到平均值。

示例验证 #

以需求中的示例数据为例：

1. U5内容："14, 281, 286, 288, 753, 0, 0, 0, 0"
2. 拆分过滤后得到非零ID数组：{"14","281","286","288","753"}
3. 匹配概率总和：0.10+0.20+0.05+0.05+0.20=0.60
4. 有效ID数量：5
5. 最终结果：0.60/5=0.12，与预期一致

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