閱： 5785 | 回： 3
等級:初學(xué)者
-
積分:0
-
財(cái)富值:0.00
-
身份:普通用戶
在Excel中， 有一個身份獨(dú)特的小星星（*），千萬不要小看他，就是這個小小的精靈，總讓新手們覺得難以捉摸。
說它身份獨(dú)特，是因?yàn)樾翘枺?）除了表示運(yùn)算符乘號，還具有通配符的身份，用來表示任意多個字符。
與之對應(yīng)的還有半角的問號“？”，問號也是通配符的一種，表示的是單個字符。
既然身份特殊，就要有特殊的處理規(guī)則。如下圖所示，需要將單元格中的星號（*）批量替換為“待評估”。
如果按常規(guī)方法，所有數(shù)據(jù)都將被替換為“待評估”。
正確方法是：
在星號（*）之前加上波形符（~），起到轉(zhuǎn)義的作用，就是告訴Excel：我要查找的是文本字符*，不要按通配符進(jìn)行處理哦。在某些公式中，星號（*）則表示通配符。如公式：
=SUMIF(A:A,”HK*”,B:B)
就是表示如果A列中以字符“HK”開頭，則計(jì)算所對應(yīng)的B列之和。
常用的支持通配符的函數(shù)包括：SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS等等。
除了使用星號（*）作為通配符按模糊條件進(jìn)行匯總之外，星號（*）還有另一個特殊的用途：如下圖所示，需要標(biāo)注身份證號碼是否重復(fù)。
如果直接使用下面的公式判斷，將無法得到正確結(jié)果。
=IF(COUNTIF(B:B,B2)>1,"重復(fù)","")
圖中，女神和金蓮的身份證號碼完全一致，公式貌似沒有問題，但是再看金花的身份證號碼，問題就來了，后三位不同的身份證號碼也被識別為相同了。這是因?yàn)镃OUNTIF函數(shù)在處理文本型數(shù)字時，會自動按數(shù)值進(jìn)行處理，而Excel的最大精度只有15位，超過15位部分全部按0進(jìn)行處理，所以對于18位的身份證號碼出現(xiàn)了錯誤判斷。
可以在公式中加上一個星號（*），以實(shí)現(xiàn)正確判斷。
=IF(COUNTIF(B:B,B2&"*")>1,"重復(fù)","")
加上星號（*）的目的是使其強(qiáng)制識別為文本，相當(dāng)于告訴COUNTIF，我找的是以B2單元格內(nèi)容開頭的文本，這樣就可以區(qū)分女神和金花了。如果需要精確查找含有星號（*）的內(nèi)容，也需要進(jìn)行特殊的處理。如下圖所示，需要根據(jù)D2單元格中的指定產(chǎn)品型號查詢對應(yīng)的供貨商，E2單元格公式為：
=INDEX(B:B,MATCH(D2,A:A,))
結(jié)果明顯不對了，明明是如花，返回怎么是女神呢。由于D2單元格中包含星號（*），MATCH函數(shù)查找時就會默認(rèn)將星號按通配符處理，在B列中返回前兩個字符是“6S”、最后一個字符是“A”的位置。如果有多個符合條件的結(jié)果，MATCH函數(shù)只能返回第一個的位置，所以女神如花傻傻分不清了。
使用以下公式可以返回正確的結(jié)果：
=LOOKUP(1,0/(A2:A8=D2),B2:B8)
利用等式中不能使用通配符的特點(diǎn)，用A2:A8=D2，以完全匹配的方式返回邏輯值TRUE或是FALSE。再用0除以邏輯值，得到0或是錯誤值#DIV/0!組成的內(nèi)存數(shù)組。最后使用1作為查找值，以內(nèi)存數(shù)組中最后一個0進(jìn)行匹配，并返回B2:B8單元格對應(yīng)位置的內(nèi)容。
我的個性簽名
等級:學(xué)者
等級:初學(xué)者