Question

excel 嵌套层数超出当前文件格式的允许值

=IF(B2="24*27","1",IF(B2="38*38","2",IF(B2="27*48","3",IF(B2="38*53","4",IF(B2="50*50","5",IF(B2="38*65","6",IF(B2="38*76","7",IF(B2="38*76","8",IF(B2="25*76","9",IF(B2="25*105","10",IF(B2=""35*105","11",IF(B2=""76*76","12",IF(B2="22*38","13"))))))))))))
跪求 在线等

Answer 1

换种方式处理，先建个这样的数据清单（但你7和8 是一样的，得改下），再用公式：=vlookup(substitute(b2,"*","×",),d:e,2,0)，就行了,或参考公式：=(B2="24*27")*1+(B2="38*38")*2+(B2="27*48")*3+……，最好是vlookup

j

Answer 2

超过7层就不行了。
可以用下面的公式
=VLOOKUP(C2,{"24*27",1;"38*38",2;"27*48",3;"38*53",4;"50*50",5;"38*65",6;"38*76",7;"38*77",8;"25*76",9;"25*105",10;"35*105",11;"76*76",12;"22*38",13},2,0)
你原公式有误，出现了二次38*76
我给你的公式中，第二个，修改为了38*77

Answer 3

=MATCH(B2，{"24*27","38*38","27*48","38*53","50*50","38*65","38*76","38*76","25*76","25*105","35*105","76*76","22*38"},0)

Answer 4

IF 嵌套太多了。
=VLOOKUP(B2,{"24*27",1;"38*38",2;"27*48",3;"38*53",4;"50*50",5;"38*65",6;"38*76",7;"38*76",8;"25*76",9;"25*105",10;"35*105",11;"76*76",12;"22*38",13},2,)

另外为什么会有两个38*76 返回不同的值？

Answer 5

2003版最多只能嵌套7层，用2007版吧。