[已解决]如何用Excel实现30层以上多重嵌套条件？

liangsir5 · 发表于 2010-4-21 11:32

我想在Excel里面实现根据A1单元格的省份名称，在D列自动生成各省份的代号（代码），但是超过29个条件后就提示出错，网上资料说可超过这个限制，下面有一个超过限制的举例：

38 个条件的示例

=CHOOSE(FIND("1",(A1<=1)*1 & (A1<=2)*1 & (A1<=3)*1 & (A1<=4)*1 & (A1<=5)*1 & (A1<=6)*1 & (A1<=7)*1 & (A1<=8)*1 & (A1<=9)*1 & (A1<=10)*1 & (A1<=11)*1 & (A1<=12)*1 & (A1<=13)*1 & (A1<=14)*1 & (A1<=15)*1 & (A1<=16)*1 & (A1<=17)*1 & (A1>17)*1),11,22,33,44,55,66,77,88,99,"AA","BB","CC","DD","EE","FF","GG","HH",CHOOSE(FIND("1",(A1<=18)*1 & (A1<=19)*1 & (A1<=20)*1 & (A1<=21)*1 & (A1<=22)*1 & (A1<=23)*1 & (A1<=24)*1 & (A1<=25)*1 & (A1<=26)*1 & (A1<=27)*1 & (A1<=28)*1 & (A1<=29)*1 & (A1<=30)*1 & (A1<=31)*1 & (A1<=32)*1 & (A1<=33)*1 & (A1<=34)*1 & (A1<=35)*1 & (A1<=36)*1 & (A1<=37)*1 & (A1>37)*1),1818,1919,2020,2121,2222,2323,2424,2525,2626,2727,2828,2929,3030,3131,3232, 3333,3434,3535,3636,3737,0))

我试了一下可以用，但是，我的条件是文本型不是数字型，我照上面改了以后，还是提示有错，请高人指点一下，看一下下面的代码错在哪里？附件有表单示例，公式如下：

=CHOOSE(FIND("1",(A1="上海")*1 & (A1="北京")*1 & (A1="广东")*1 & (A1="浙江")*1 & (A1="江苏")*1 & (A1="山东")*1 & (A1="山西")*1 & (A1="陕西")*1 & (A1="河北")*1 & (A1="河南")*1 & (A1="重庆")*1 & (A1="云南")*1 & (A1="西藏")*1 & (A1="江西")*1 & (A1="福建")*1 & (A1="广西")*1 & (A1="贵州")*1 & (A1="四川")*1),"01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18",CHOOSE(FIND("1",(A1="黑龙江")*1 & (A1="吉林")*1 & (A1="辽宁")*1 & (A1="天津")*1 & (A1="新疆")*1 & (A1="甘肃")*1 & (A1="青海")*1 & (A1="宁夏")*1 & (A1="内蒙")*1 & (A1="湖南")*1 & (A1="安徽")*1 & (A1="海南")*1 & (A1="香港")*1),"19","20","21","22","23","24","25","26","27","28","29","30","31","32"))

请问运行后，为什么不对？谢谢！

pxSRtOjd.rar (4.87 KB, 下载次数: 10)

liangsir5 · 发表于 2010-4-21 11:39

补充附件。

蝶·舞 · 发表于 2010-4-21 11:51

1、上传附件

2、当一个函数的参数超29之多，就应该考虑换一个函数或使用辅助列

3、附件须以压缩包形式上传

liangsir5 · 发表于 2010-4-21 12:49

有32个判定条件，32个结果，不一定用这个函数，有哪位大侠能做到吗？谢谢！

linskying · 发表于 2010-4-21 16:52

=VLOOKUP(A1,{"上海","01";"北京","02";"广东","03";"浙江","04";"江苏","05";"山东","06";"山西","07";"陕西","08";"河北","09";"河南","10";"重庆","11";"云南","12";"西藏","13";"江西","14";"福建","15";"广西","16";"贵州","17";"四川","18";"黑龙江","19";"吉林","20";"辽宁","21";"天津","22";"新疆","23";"甘肃","24";"青海","25";"宁夏","26";"内蒙","27";"湖南","28";"湖北","29";"安徽","30";"海南","31";"香港","32"},2,0)

可以用vlookup加数组数据

一剑惊心 · 发表于 2010-4-21 19:13

=text(FIND(LEFT(A1,2)," 上海北京广东浙江江苏山东山西陕西河北河南重庆云南西藏江西福建广西贵州四川黑龙吉林辽宁天津新疆甘肃青海宁夏内蒙湖南湖北安徽海南香港")/2,"00")

liangsir5 · 发表于 2010-4-21 22:01

牛人啊，谢谢！大侠，真是太厉害了！

爱学若渴 · 发表于 2010-5-13 09:12

头都昏了。

jiu128 · 发表于 2014-4-27 14:20

text函数竟然这么牛？谢谢分享了

		自动登录	找回密码
密码			注册

[已解决]如何用Excel实现30层以上多重嵌套条件？

附件－示例