lookup,上下限问题

Hiro小超

按照实际A1控制结果进行评分，具体标准为：
A1   ≤2%，得15分。
A1（2%，3%]，得13分；
A1（3%，4%]，得10分；
A1（4%，5%]，得7分；
A1（5%，6%]，得5分；
A1（6%，7%]，得3分；
A1（7%，8%]，得1分。
A1 >8%，得0分。


=LOOKUP(A1,{0,0.02,0.03,0.04,0.05,0.06,0.07,0.08},{15,13,10,7,5,3,1,0})

当A1等于0.02时，应该是15分，公式得13分，怎么解决。

misterZ

=LOOKUP(A1,{0,0.021,0.031,0.041,0.051,0.061,0.071,0.081},{15,13,10,7,5,3,1,0})

1786024734

1. =LOOKUP(A1-9^-9,{0,0.02,0.03,0.04,0.05,0.06,0.07,0.08},{15,13,10,7,5,3,1,0})

复制代码

part98

=LOOKUP(A1,{0,2,3,4,5,6,7,8}%+1%%,{15,13,10,7,5,3,1,0})

象山海鲜

1. =LOOKUP(,0/FREQUENCY(A1,{2,3,4,5,6,7,8}%),{15,13,10,7,5,3,1,0})

复制代码

Hiro小超

1786024734 发表于 2024-7-25 14:57

老师，可以解释下A1-9^-9这里吗，多谢了

Hiro小超

misterZ 发表于 2024-7-25 14:57
=LOOKUP(A1,{0,0.021,0.031,0.041,0.051,0.061,0.071,0.081},{15,13,10,7,5,3,1,0})

这样容易有BUG哈

1786024734

Hiro小超 发表于 2024-7-25 15:03
老师，可以解释下A1-9^-9这里吗，多谢了

LOOKUP在第二参数中查找【小于】或【等于】第一参数的最大值，本例中【仅】查找【小于】而【不等于】第一参数的最大值，所以用-9^9来【修正查找范围】，避免出现本例中要解决的问题。