我正在试着写一个excel函数来对一长串数字进行分组。
例如:
<165 "reject" ( less than 165 = reject)
>=165 <167 "F" (greater than or equal to 165 and less than 167 = group F)
>=167 <169 "D" (greater than or equal to 167 and less than 169 = group D)
>=169 <171 "B" (greater than or equal to 169 and less than 171 = group B)
>=171 <173 "A" (greater than or equal to 171 and less than 173 = group A)
>=173 <175 "C" (greater than or equal to 173 and less than 175 = group C)
>=175 <177 "E" (greater than or equal to 175 and less than 177 = group E)
>=177 "reject" ( greater than or equal to 177 = reject)
如有任何建议,我们将不胜感激!
谢谢你
我已经尝试了下面的公式,但得到错误“函数中参数过多”
{=IF(C4<165,"reject165",IF(C4>=165,C4<167,"F",IF(C4>=167,C4<169,"D",IF(C4>=169,C4<171,"B",IF(C4>=165,C4<167,"A",IF(C4>=165,C4<167,"C",IF(C4>=165,C4<167,"E",IF(C4>=177,"reject177","null"))))))))}
2条答案
按热度按时间wtzytmuj1#
=中频(C4〈165,“拒绝165”,如果(AND(C4〉=165,C4〈167),“F”,如果(与(C4〉=167,C4〈169),“D”,如果(与(C4〉=169,C4〈171),“B”,如果(与(C4〉=171,C4〈173),“A”,如果(与(C4〉=173,C4〈175),“C”,如果(与(C4〉=175,C4〈177),“E”,如果(C4〉=177,“拒绝177”,“空”))))))
bqucvtff2#
您可以尝试使用不需要处理嵌套
IF
-condition的IFS
函数,如下所示:或者,您可以利用间隔的逻辑来构建序列并按如下方式查找字母:
因为区间不重叠,所以
FILTER
将只返回单个字母,如果值与任何区间都不匹配,则返回reject
。通过用
XLOOKUP
代替前面公式中的FILTER
,可以得到类似的解,如下所示: