學Excel必須要掌握的「星級」教程

在Excel中， 有一個身份獨特的小星號（*），千萬不要小看他，就是這個小東東，總讓新手們覺得難以捉摸。

說它身份獨特，是因為星號（*）除了表示運算符乘號，還具有通配符的身份，用來表示任意多個字元。

與之對應的還有半形的問號「？」，問號也是通配符的一種，表示的是單個字元。

既然身份特殊，就要有特殊的處理規則。

如下圖所示，需要將單元格中的星號（*）批量替換為「待評估」。

如果按常規方法，所有數據都將被替換為「待評估」。

這是因為在替換時，星號默認表示所有字元。所以只要是有字元的地方就被全部替換掉了。

正確方法是：

在星號（*）之前加上波形符（~），起到轉義的作用。

就是告訴Excel：我要查找的是文本字元*，不要按通配符進行處理哦。

在某些公式中，星號（*）則表示通配符。

如公式：

=SUMIF(A:A,」HK*」,B:B)

就是表示如果A列中以字元「HK」開頭，則計算所對應的B列之和。

常用的支持通配符的函數包括：

SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS等等。

除了使用星號（*）作為通配符按模糊條件進行匯總之外，星號（*）還有另一個特殊的用途。

如下圖所示，需要標註身份證號碼是否重複。

如果直接使用下面的公式判斷，將無法得到正確結果。

=IF(COUNTIF(B:B,B2)>1,"重複","")

圖中，女神和金蓮的身份證號碼完全一致，公式貌似沒有問題，但是再看金花的身份證號碼，問題就來了，後三位不同的身份證號碼也被識別為相同了。

這是因為COUNTIF函數在處理文本型數字時，會自動按數值進行處理，而Excel的最大精度只有15位，超過15位部分全部按0進行處理，所以對於18位的身份證號碼出現了錯誤判斷。

可以在公式中加上一個星號（*），以實現正確判斷。

=IF(COUNTIF(B:B,B2&"*")>1,"重複","")

加上星號（*）的目的是使其強制識別為文本，相當於告訴COUNTIF：「我找的是以B2單元格內容開頭的文本」，這樣就可以區分女神和金花了。

如果需要精確查找含有星號（*）的內容，也需要進行特殊的處理。

如下圖所示，需要根據D2單元格中的指定產品型號查詢對應的供貨商，E2單元格公式為：

=INDEX(B:B,MATCH(D2,A:A,))

結果明顯不對了，明明是如花，返回怎麼是女神呢。

由於D2單元格中包含星號（*），MATCH函數查找時就會默認將星號按通配符處理，在B列中返回前兩個字元是「6S」、最後一個字元是「A」的位置。

如果有多個符合條件的結果，MATCH函數只能返回第一個的位置，所以女神如花傻傻分不清了。

使用以下公式可以返回正確的結果：

=LOOKUP(1,0/(A2:A8=D2),B2:B8)

利用等式中不能使用通配符的特點，用A2:A8=D2，以完全匹配的方式返回邏輯值TRUE或是FALSE。

再用0除以邏輯值，得到0或是錯誤值#DIV/0!組成的內存數組。

最後使用1作為查找值，以內存數組中最後一個0進行匹配，並返回B2:B8單元格對應位置的內容。

最後留給大家一個小尾巴：如果在單元格中輸入下面的內容，想想結果會是什麼呢？

=4**5

打開Excel驗證一下，你猜對了嗎，為什麼會是這樣呢？

圖文製作：祝洪忠

Excel函數實戰100例 - 第9期

公開課時間：2017-6-3、6-4（10:00~11:30）

正式課時間：2017-6-10 — 6-25

講師：翟振福

課程詳情：http://t.excelhome.net/thread-45002-1-1.html

免費報名公開課：http://t.excelhome.net/thread-45003-1-1.html

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！