如何在excel中实现大约54层的嵌套？
我喜欢添加54个IF嵌套，我尝试添加& option，在每8个之后将它们拆分，因为我已经这样做了，它只允许额外的8个嵌套，而其他的嵌套是不起作用的。我有没有办法绕过这个问题，让它正常工作？
=IF(E9<231,"1",IF(E9<321,"2",IF(E9<411,"3",IF(E9<501,"4",IF(E9<591,"5",IF(E9<681,"6",IF(E9<771,"7",IF(E9<861,"8",)))))))))IF(E9<951,"9",IF(E9<1041,"10",IF(E9<1131,"11",IF(E9<1221,"12",IF(E9<1311,"13",IF(E9<1401,"14",IF(E9<1491,"15",IF(E9<1581,"16",))))))))IF(E9<1581,"16",IF(E9<1671,"17",IF(E9<1761,"18",IF(E9<1851,"19",IF(E9<1941,"20",IF(E9<2031,"21",IF(E9<2121,"22",)))))))IF(E9<2211,"23",IF(E9<2301,"24",IF(E9<2391,"25",IF(E9<2481,"26",IF(E9<2571,"27",IF(E9<2661,"28",IF(E9<2751,"29",IF(E9<2841,"30",)))))))) IF(E9<2931,"31", IF(E9<3021,"32", IF(E9<3111,"33",IF(E9<3201,"34", IF(E9<3291,"35", IF(E9<3381,"36", IF(E9<3471,"37", IF(E9<3561,"38",)))))))) IF(E9<3651,"39", IF(E9<3741,"40", IF(E9<3831,"41", IF(E9<3921,"42", IF(E9<4011,"43", IF(E9<4101,"44", IF(E9<4191,"45", IF(E9<4281,"46",)))))))) IF(E9<4371,"47", IF(E9<4461,"48", IF(E9<4551,"49", IF(E9<4641,"50", IF(E9<4731,"51", IF(E9<4821,"52", IF(E9<4911,"53", IF(E9<5001,"54",)))))))
回答 2
Stack Overflow用户
发布于 2018-08-05 02:15:05
嵌套函数有时有一些模式，可以用一些数学方法来利用。
=INT(MAX(E9-51,90)/90)您没有提供E列中的值大于5000的规定，但这可以很容易地添加。
Stack Overflow用户
发布于 2018-08-05 00:53:06
使用MATCH()
=MATCH(E9,{0,231,321,411,501,...})将...替换为您的其余阈值。
正如@SolarMike所述，您还可以将阈值设置在一个范围内。然后引用该范围，而不是“硬编码”数组。因此，如果您将阈值设置为A1:A55，则公式为：
=MATCH(E9,A1:A55)https://stackoverflow.com/questions/51687709
复制相似问题