我希望 Excel 根据信息表返回一个值

我希望 Excel 根据信息表返回一个值

尝试为一位年长的高尔夫球友自动计算一组得分。根据他们的得分在图表中的位置添加击球数。我正在尝试编写一个公式,在搜索 5 列后返回 5 个值中的一个,并返回该值所属列下的相应值。

请参阅此表以获得直观的

前任。

  • 球员 a 击球 95。在 71-130 值表中,95 位于第 1 列第 5 行。第 1 列中的值需要 -2 杆。

  • 球员 B 打出 94 杆。在图表中,94 位于第 4 行第 5 列,需要 +2 杆。

玩家 A 的公式应返回“-2”,而玩家 B 的公式应返回“2”。

这对于 Excel 来说是不是太难处理了?

我已经尝试了 Vlookup 和 Xlookup 以及 IFS,但它不喜欢数字不连续或引用单元格(因为图表确实发生了变化。最好使用单元格引用)。

答案1

在 INDEX 中使用 AGGREGATE:

=INDEX(A13:E13,AGGREGATE(15,7,COLUMN(A1:E12)/(A1:E12=G2),1))

这将返回包含数字的列到 INDEX。使用 AGGREGATE 中的数学运算,除了匹配的列外,每次返回都会出错。这7告诉公式忽略所有错误,因此只能返回一个值。

在此处输入图片描述

答案2

您只需使用以下分析公式:

=MOD(G2,5)-2

MOD 计算整数除法后的余数,即 75 mod 5 为零(5 完全除以 75),但 79 mod 5 返回 4(79 = 15*5 + 4)。因此 MOD(x, 5) 将始终返回 0 到 4 之间的数字。我们用 -2 偏移它,以便始终根据您的要求返回 -2 到 2 之间的数字。如果您的表“移动”了一位,例如 74 应该返回 -2,只需移动 MOD 之前的值即可,例如=MOD(G2+1,5)-2

MOD 公式从 75 开始有效,但在 72 到 74 之间出现不连续性,显然会失效。不连续性的原因是什么?

为了解决这个问题,我们添加了一个带有移位的 IF 语句,如上所示:

=IF(G2<75, MOD(G2-2,5)-2, MOD(G2,5)-2)

相关内容