代码之家 › 专栏 › 技术社区 › OC2PS

有条件地计算图纸上的值

office365 excel

1

OC2PS · 技术社区 · 3 年前

我有一张床单 allocations 动态捕获数据:

小部件ID
分配给小部件的测试人员1的B电子邮件
测试人员1对D小部件的评级
分配给小部件的测试人员2的电子邮件
测试人员2对G小部件的评级
I评分差异
J评级平均值

A.	B	C	D	E	F	G	H	我	J
小工具_id	电子邮件1	电子邮件1谢谢	评级1	电子邮件2	发送电子邮件2	评级2	已发送操作	差别	平均的
990235	`[email protected]`	0	45	`[email protected]`	0	50	0	5.
990236	`[email protected]`	0	55	`[email protected]`	0	53	0	2.
990231	`[email protected]`	0	75	`[email protected]`	0		0
990197	`[email protected]`	0		`[email protected]`	0	25	0
990003	`[email protected]`	0	65	`[email protected]`	0	85	0	20

(C、F、H列是电子邮件发送的标志,因此目前不相关)

在同一个工作簿中,我还有一张表 thresholds 其具有关于评级中不同级别差异的动作阈值的信息:

A应该采取什么行动
B触发此操作的评级差异阈值

A.	B
行动	门槛
平均的	5.
讨论	10

上面的值意味着,如果两个测试人员之间的差异(即 分配 片材)小于(或等于)5,
如果他们的评分相差大于5但小于(或等于)10,则采取第二个动作(目前无关)),
如果评分相差超过10分,则采取第三次行动(目前无关紧要)。

我想发生什么

如果 分配 工作表列F的值在 average 阈值(即。 阈值 薄片单元格 B2 值:5),将J列计算为( AVERAGE (column D, column G) )。

样本输出: 在第2行和第3行中,列J计算为 =AVERAGE(D2,G2) 和 =AVERAGE(D3,G3) 分别地
在第4行和第5行中,没有计算列J,因为在每种情况下,列I都没有数值。
在第6行中,不计算列J,因为在这种情况下,列I(20)的值大于 平均的 阈值(5)。

A.	B	C	D	E	F	G	H	我	J
小工具_id	电子邮件1	电子邮件1谢谢	评级1	电子邮件2	发送电子邮件2	评级2	已发送操作	差别	平均的
990235	`[email protected]`	0	45	`[电子邮件保护]`	0	50	0	5.	47.5
990236	`[email protected]`	0	55	`[email protected]`	0	53	0	2.	54
990231	`[email protected]`	0	75	`[email protected]`	0		0
990197	`[email protected]`	0		`[email protected]`	0	25	0
990003	`[email protected]`	0	65	`[email protected]`	0	85	0	20

不是这个
由于各种原因(包括如果列I没有数值,或者如果列J不应该根据阈值计算,我希望列J为空),我不想使用下面这样的简单公式并一直复制它:
=IF(AND(I2>0,I2<=VLOOKUP(I2,thresholds!A2:B3,2)),AVERAGE(D2,G2),)

1 回复 | 直到 3 年前

1

2

David Leal 3 年前

您可以尝试以下操作。这是一个数组解决方案,因此不需要向下展开公式:

=LET(lk, "average", thAct, A10:A11, thValue, B10:B11,
 th, XLOOKUP(lk, thAct,thValue),r_1,D2:D6, r_2, G2:G6,
 MAP(r_1,r_2, LAMBDA(x,y,
  IF(AND(ISNUMBER(x), ISNUMBER(y), y-x <= th), AVERAGE(x,y),""))))

或者不定义名称:

=LET(th, XLOOKUP("average", A10:A11,B10:B11),
 MAP(D2:D6,G2:G6, LAMBDA(x,y,
  IF(AND(ISNUMBER(x), ISNUMBER(y), y-x <= th), AVERAGE(x,y),""))))

您可以参考相应的图纸,根据您的具体问题调整范围。我没有使用差别列,因为它可以动态计算。仅当所有条件 AND 满足调用(两个速率都是数字,并且速率差在定义的阈值内),否则返回一个空字符串。而不是使用 以及 可以将条件相乘,如下所示:

ISNUMBER(x) * ISNUMBER(y) * (y-x <= th)

以下是输出: