Piny系列-上下裝問題

piny

儲存格若含有「褲」，則為下裝，否則則為上裝

參考出處
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,)

laowuwyx

叹为观止！

duwei9981

强，拓宽思路！！收藏学习！！

gooning

收藏学习！！

wangjguo44

简直是判别大全，佩服，收藏！

一剑惊心

=IF(COUNTIF(A2,"*裤*"),"下装","上装")
个人感觉这个红色星号也可省略，一般情况下，不可能出现第一个字即为“裤”字的。

lwwcom

收藏
好好的学习一下

wshcw

很好的作品，加入精华。

a216516

强大的人呀！！！为什么我就会一个IF，唉。。。

zhc19620430

好贴，努力学习函数。