Question

EXCEL中用函数公式实现在多个数值范围取值（不用if函数）

表一

表二

如上图。表一为标准表。表二是需要填写档次列。有没有好的公式可以实现？（档次太多了，有100多个，不好用if)
谢谢！





Answer 1

=INDIRECT("b"&(SUMPRODUCT(IF(E2>$A$2:$A$8,1))+2))

 

$A$2:$A$8 这一段你可以自己设定长一点

追问

能否简单解释一下各函数的用处。谢谢

追答

额。。。按F1会都告诉你的，就是最后输完之后要SHIFT+CTRL+ENTER，才可显示出来，后面的直接下拉就可以了

追问

两个表在同一表格内可以实现，但我的表一需要单独放一张表。比较，表一在sheet1,表二在sheet2，我用=INDIRECT("Sheet1!B"&(SUMPRODUCT(IF(A2>Sheet1!$A$2:$B$12,1))+2))无法实现。盼赐教。谢谢！

追答

=INDIRECT("SHEET1!b"&(SUMPRODUCT(IF(A2>Sheet1!$A$2:$A$8,1))+2))
一模一样诶。。我可以啊，重新按一遍SHIFT+CTRL+ENTER，就是要注意一点，我没有设置当大于最大值的时候，这种时候会计算出来是0，你就知道下，如果是就是最大的，方法可以是套用一个IF，也可以直接在sheet1中的B9写上8，也是可以实现的

亲~别忘了给我分哟

Answer 2

=LOOKUP(1,0/(B1>{0,5,14.2,22,30,45,67,95,135,160}),$C$1:$C$10)
=INDEX($C:$C,MATCH(,0/NOT(B1>{5,14.2,22,30,45,67,95,135,160,9.9E+307}),))
=MAX(IF(B1>{0;5;14.2;22;30;45;67;95;135;160},$C$1:$C$10,""))还加一种：
=LOOKUP(1,0/FREQUENCY($B1,{5,14.2,22,30,45,67,95,135,160}),$C$1:$C$10)
以上四种，数组公式及一般公式都是一样结果。

Answer 3

=lookup(a2-0.000001,{0,4,8,12,16,20,24,28,30,32,34},{1,2.3,4,5,6,7,8,9,10,11})

追问

大括号中有没有不用手工输入的办法？我们有100多个分数段和100多个档次，并且有好几个标准表，每个得手工输入，太麻烦了

Answer 4

=LOOKUP(-A2,{-99999999,-34,-32,-30,-28,-24,-20,-16,-12,-8,-4},{11,10,9,8,7,6,5,4,3,2,1})
下拉

追问

大括号中有没有不用手工输入的办法？我们有100多个分数段和100多个档次，并且有好几个标准表，每个得手工输入，太麻烦了

追答

你可以把条件和结果放在1列或者1行中,来调用,替代大括号里的内容,参见附件

Answer 5

假设你的表二第一个要取的数值位置是A2
=lookup(a2,{0,4,8,12,16,20,24,28,30,32,34},{1,2,3,4,5,6,7,8,9,10,11})

追问

大括号中有没有不用手工输入的办法？我们有100多个分数段和100多个档次，并且有好几个标准表，每个得手工输入，太麻烦了

追答

使用函数我想没有太好的方法来实现了，这个真的不行