儲存格若含有「褲」，則為下裝，否則則為上裝
參考出處
http://club.excelhome.net/viewthread.php?tid=656650
整理如下
32 =IF(COUNTIF(A2,"*裤*"),"下装","上装")
33 =TEXT(COUNTIF(A2,"*裤*"),"下装;;上装")
34 =IF(COUNT(FIND("裤",A2)),"下装","上装")
34 =IF(ISERR(FIND("裤",A2)),"上装","下装")
34 =MID("上装下装",3^COUNTIF(A2,"*裤*"),2)
35 =TEXT(COUNT(FIND("裤",A2)),"下装;;上装")
35 =TEXT(-ISERR(FIND("裤",A2)),"下装;上装")
36 =MID("上装下装",3^COUNT(FIND("裤",A2)),2)
36 =MID("下装上装",3^ISERR(FIND("裤",A2)),2)
37 =IF(ISNUMBER(FIND("裤",A2)),"下装","上装")
37 =IF(SUBSTITUTE(A2,"裤",)=A2,"上装","下装")
38 =TEXT(-ISNUMBER(FIND("裤",A2)),"上装;下装")
38 =TEXT(-ISNA(MATCH("*裤*",A2,)),"下装;上装")
39 =INDEX({"上装","下装"},COUNTIF(A2,"*裤*")+1)
39 =IF(FIND("裤",A2&"裤")>LEN(A2),"上装","下装")
39 =MID("上装下装",3^ISNUMBER(FIND("裤",A2)),2)
40 =LOOKUP(,-FIND({"","裤"},A2),{"上装","下装"})
41 =INDEX({"上装","下装"},COUNT(FIND("裤",A2))+1)
41 =MID("上下",COUNT(,MATCH("*裤*",A2,)),1)&"装"
41 =REPLACE("下装上装",3^COUNT(FIND("裤",A2)),2,)
41 =TEXT(-(SUBSTITUTE(A2,"裤",)<>A2),"上装;下装")
41 =INDEX({"下装","上装"},ISERR(FIND("裤",A2))+1)
42 =MID("上装下装",3^(SUBSTITUTE(A2,"裤",)<>A2),2)
43 =TEXT(-(LEN(A2)>=FIND("裤",A2&"裤")),"上装;下装")
44 =INDEX({"上装","下装"},ISNUMBER(FIND("裤",A2))+1)
46 =LOOKUP(COUNT(FIND("裤",A2)),{0,1},{"上装","下装"})
46 =VLOOKUP(COUNTIF(A2,"*裤*"),{1,"下装";0,"上装"},2,)
46 =HLOOKUP(COUNTIF(A2,"*裤*"),{1,0;"下装","上装"},2,)
47 =IF(LEN(SUBSTITUTE(A2,"裤",))<LEN(A2),"下装","上装")
48 =IF(SUM(N(MID(A2,COLUMN(1:1),1)="裤")),"下装","上装")
48 =TEXT(LEN(A2)-LEN(SUBSTITUTE(A2,"裤",)),"下装;;上装")
51 =IF(COUNT(1/(MID(A2,COLUMN(1:1),1)="裤")),"下装","上装")
其中，函數組合相同，則以字元數最少者為代表
並刪除以下可直接替換的解
FIND改SEARCH
MID改MIDB
LEN改LENB
IF改CHOOSE
FIND("裤",A2)改MATCH("*裤*",A2,) |
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-12-6 11:49
发表于 2010-12-6 11:54
发表于 2010-12-6 14:55
