IF函数公式简化

给心灵涂上色彩

=IF(V2<60,"60及以下",IF(V2<90,"60-99",IF(V2<120,"90-119",IF(V2<150,"120-149",IF(V2<180,"150-179",IF(V2<210,"180-209",IF(V2<240,"210-239",IF(V2<270,"240-269",IF(V2<300,"270-299",IF(V2<330,"300-329",IF(V2<360,"330-359",IF(V2<390,"360-389",IF(V2<420,"390-419",IF(V2<450,"420-449",IF(V2<480,"450-479",IF(V2<510,"480-509",IF(V2<540,"510-539",IF(V2<570,"540-569",IF(V2<600,"570-599",IF(V2<630,"600-629",IF(V2<660,"630-659",IF(V2<690,"660-689",IF(V2<720,"690-719",IF(V2<750,"720-749",IF(V2<780,"750-779",IF(V2<810,"780-809",IF(V2<840,"810-839",IF(V2<870,"840-869",IF(V2<900,"870-899",IF(V2<930,"900-929",IF(V2<960,"930-959",IF(V2<990,"960-989",IF(V2<1020,"990-1019",IF(V2<3000,"1000及以上"))))))))))))))))))))))))))))))))))
有大家看到这么长的公式估计也是醉了，所以在些请求大神简化下公式，拜谢。。。。

scyss

目测，用vlookup或者lookup构建

给心灵涂上色彩

scyss 发表于 2018-12-6 11:23
目测，用vlookup或者lookup构建

好的，谢谢大侠，只能用查找匹配了。。。。在此抱拳。。。。

李建军123

1. =IF(V2<60,"60及以下",IF(V2>1019,"1000及以上",V2-MOD(V2,30)&"-"&V2-MOD(V2,30)+29))

复制代码

泊蓝

1. =IF(V2<60,"60及以下",IF(V2>=1020,"1000及以上",QUOTIENT(V2,30)*30&"-"&(QUOTIENT(V2,30)+1)*30-1))

复制代码

micch

话说最后一个是不是写错了是，1000以上，还是1020以上？？？第二个是60-89吧？？？？

李建军123

=IF(V2<60,"60及以下",IF(V2<90,"60-99"这个也错了 应该是89吧

micch

1. =FLOOR(V2,30)&-FLOOR(V2,30)-29

复制代码

给你个公式吧，实在是受不了，两头的用if判断吧；

<60是60及以下？？<90是“60-99”？？？

给心灵涂上色彩

感谢各位大侠们，这么多方式，太强大了。。。