excel 有条件地计算工作表上的值

我有一个工作表allocations动态捕获数据：

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

| 一个|B| C类|D级|E级|F型|G级|H型|我的|J型|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| widget_id|电子邮件1|电子邮件1感谢|评级1|电子邮件2|电子邮件2 thefed|评级2|行动|差异|平均值|
| 990235| jack@company.com| 0|四十五|mack@company.com| 0|五十|0| 5个||
| 990236| mack@company.com| 0|五十五|jack@company.com| 0|五十三|0| 2||
| 990231| jack@company.com| 0|七十五|iris@company.com| 0||0|||
| 990197| iris@company.com| 0||mack@company.com| 0|二十五|0|||
| 九九零零零三|mack@company.com| 0|六十五|iris@company.com| 0|八十五|0|二十个||
（C、F、H列是电子邮件发送标志，所以现在无关紧要）
在同一个工作簿中，我还有一个工作表thresholds，其中包含有关不同等级差异的行动阈值的信息：

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

| 一个|B|
| - -----|- -----|
| 作用|临界值|
| 平均值|5个|
| 讨论|十个|
上述值意味着，如果两个测试人员之间的差异（即，allocations页中的第I列）小于（或等于）5，
如果他们的评级相差大于5但小于（或等于）10，则采取第二个动作（现在不相关）），
并且如果评级相差超过10，则采取第三动作（目前无关）。

我想要的

如果allocations工作表列F值在average阈值内（即thresholds工作表单元B2值：5），将列J计算为（xlml 7 nlx）。
样本输出：在行2和3中，列J分别计算为=AVERAGE(D2,G2)和=AVERAGE(D3,G3)。
在行4和5中，不计算列J，因为在每种情况下，列I不具有数值。
在行6中，不计算列J，因为在这种情况下，列I（20）的值大于average阈值（5）。
| 一个|B| C类|D级|E级|F型|G级|H型|我的|J型|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| widget_id|电子邮件1|电子邮件1感谢|评级1|电子邮件2|电子邮件2 thefed|评级2|行动|差异|平均值|
| 990235| jack@company.com| 0|四十五|mack@company.com| 0|五十|0| 5个|四十七点五|
| 990236| mack@company.com| 0|五十五|jack@company.com| 0|五十三|0| 2|五十四个|
| 990231| jack@company.com| 0|七十五|iris@company.com| 0||0|||
| 990197| iris@company.com| 0||mack@company.com| 0|二十五|0|||
| 九九零零零三|mack@company.com| 0|六十五|iris@company.com| 0|八十五|0|二十个||

不是这个

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

您可以尝试以下操作。这是一个数组解决方案，所以你不需要向下扩展公式：

=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),""))))

您可以根据您的具体问题调整范围，请参阅相应的工作表。我没有使用difference列，因为它可以在运行中计算。如果AND调用中的所有条件都满足（两个速率都是数字并且速率差在定义的阈值内），则仅计算平均值，否则返回空字符串。不使用AND，可以按如下方式乘以条件：

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

下面是输出：

excel 有条件地计算工作表上的值

1条答案

相关问题

热门标签

最新问答