Excel技术问询：如何按阈值计算allocations表J列平均值（非简单IF公式）

针对你的需求，这里提供几种避开简单IF+VLOOKUP嵌套的可行方案：

方案1：用LET函数拆分逻辑（适合Excel 365/2021+） #

LET能先定义变量，把阈值获取和条件判断拆分开，逻辑更清晰：
在J2单元格输入下面的公式，下拉填充即可：

=LET(
    avg_threshold, XLOOKUP("average", thresholds!A:A, thresholds!B:B),
    current_diff, I2,
    IF(AND(NOT(ISBLANK(current_diff)), current_diff <= avg_threshold), AVERAGE(D2, G2), "")
)

• 说明：先通过XLOOKUP精准拿到thresholds表中"average"对应的阈值，存为变量avg_threshold；再把当前行的评分差存为current_diff；最后做条件判断，符合要求就计算平均值，否则留空。

方案2：定义名称+拆分公式逻辑 #

把阈值的获取逻辑独立成自定义名称，公式只负责判断和计算：

1. 点击菜单栏【公式】→【定义名称】，名称设为AvgThreshold，引用位置填：=XLOOKUP("average", thresholds!$A:$A, thresholds!$B:$B)，点击确定。
2. 在J2单元格输入公式，下拉填充：

=IF(AND(NOT(ISBLANK(I2)), I2<=AvgThreshold), AVERAGE(D2,G2), "")

• 说明：自定义名称把阈值的获取逻辑和计算逻辑分离，公式结构更简洁，也避开了你提到的简单嵌套写法。

方案3：数组公式兼容旧版Excel #

如果用的是没有XLOOKUP/LET的旧版Excel，用INDEX+MATCH组合替代VLOOKUP，配合数组公式实现：
在J2单元格输入公式后，按Ctrl+Shift+Enter完成数组公式输入，再下拉填充：

=IF(AND(NOT(ISBLANK(I2)), I2<=INDEX(thresholds!$B:$B, MATCH("average", thresholds!$A:$A, 0))), AVERAGE(D2,G2), "")

• 说明：用INDEX+MATCH精准定位阈值，比VLOOKUP的逻辑更直观，同时条件判断的结构也不是你禁止的那种简单嵌套。

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