excel - 如何在excel中获得大约54级嵌套

Question

我喜欢添加 54 个 IF 嵌套，我尝试添加 & 选项，方法是在每 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",)))))))

Answer 1

使用 MATCH()

=MATCH(E9,{0,231,321,411,501,...})

替换 ...与您的其余阈值。

正如@SolarMike 所说，您还可以将阈值放在一个范围内。然后引用该范围而不是“硬编码”数组。因此，如果您将阈值放在 A1:A55 中，则公式为:

=MATCH(E9,A1:A55)