小伙伴們好啊,今天老祝要和大家分享一組很少有人注意,但是卻很重要的知識(shí)點(diǎn),逗號(hào)“,”和星號(hào)“*”。要想學(xué)好函數(shù)公式,這個(gè)必須要理解才可以哦。
在函數(shù)公式中,逗號(hào)的作用是對(duì)不同參數(shù)進(jìn)行間隔。但是在實(shí)際應(yīng)用中,一些新人朋友往往會(huì)被這個(gè)小逗逗搞得暈頭轉(zhuǎn)向。
首先來看看咱們熟知的IF函數(shù),如果A1輸入1,目測(cè)一下下面兩個(gè)公式,會(huì)返回什么結(jié)果呢?
=IF(A1>5,"大")
=IF(A1>5,"大",)
怎么樣,猜對(duì)了嗎?
前者返回的是邏輯值FALSE,后者返回的是數(shù)值0。
兩個(gè)公式唯一的差別是一個(gè)逗號(hào),第一個(gè)公式缺省參數(shù),是指參數(shù)的位置給剝奪了。
第二個(gè)公式是省略參數(shù)值,就是位置留著,不過沒有明確指出參數(shù)值是多少。
接下來再看一個(gè)排名函數(shù)RANK。
C2單元格的公式是
=RANK(B2,B$2:B$6)
RANK函數(shù)的第三參數(shù)如果為0或是省略,排位結(jié)果是按照降序排列的。
如果第三參數(shù)不為零,則排位結(jié)果是按照升序排列的。
一個(gè)逗號(hào)的差異,蒼老師從排名第一直接變成排名第5了。
MATCH函數(shù)參數(shù)中的最后一個(gè)逗號(hào)也是有講究的。如下圖所示,需要查詢芳菲在A列所處的位置。
圖中分別使用兩個(gè)公式,結(jié)果卻不相同。
=MATCH(D2,A:A)
=MATCH(D2,A:A,)
從圖中可以看出,前者返回的是一個(gè)錯(cuò)誤結(jié)果。
當(dāng)MATCH函數(shù)第三參數(shù)為0或是省略參數(shù)值時(shí),將按精確匹配的方式返回結(jié)果。
如果逗號(hào)也省略了,就是查找小于或等于查詢值的最大值,但是要求查詢參數(shù)中的值必須按升序排列。
受逗號(hào)影響的,還有VLOOKUP函數(shù),看下圖:
圖中分別使用了兩個(gè)公式,這里的逗號(hào)也會(huì)影響查詢的精度。
=VLOOKUP(D2,A:B,2)
=VLOOKUP(D2,A:B,2,)
當(dāng)VLOOKUP函數(shù)第四參數(shù)為0或是省略參數(shù)值時(shí),將按精確匹配的方式返回查詢結(jié)果。
如果逗號(hào)也省略了,就是在查詢區(qū)域中查詢小于或等于查詢值的最大值,并返回指定列的內(nèi)容,同樣要求查詢參數(shù)中的值必須按升序排列。
通過以上幾個(gè)例子可以看出,小小的逗號(hào)對(duì)公式結(jié)果的影響是不容忽視的,你還可以舉出幾個(gè)類似的例子嗎?
在Excel中, 還有一個(gè)身份獨(dú)特的符號(hào),就是小星號(hào)(*),千萬(wàn)不要小看他,就是這個(gè)小小的精靈,總讓新手們覺得難以捉摸。
說它身份獨(dú)特,是因?yàn)樾翘?hào)(*)除了表示運(yùn)算符乘號(hào),還具有通配符的身份,用來表示任意多個(gè)字符。
與之對(duì)應(yīng)的還有半角的問號(hào)“?”,問號(hào)也是通配符的一種,表示的是單個(gè)字符。
既然身份特殊,就要有特殊的處理規(guī)則。
如下圖所示,需要將單元格中的星號(hào)(*)批量替換為“待評(píng)估”。
如果按常規(guī)方法,所有數(shù)據(jù)都將被替換為“待評(píng)估”。
正確方法是:
在星號(hào)(*)之前加上波形符(~),起到轉(zhuǎn)義的作用,就是告訴Excel:我要查找的是文本字符*,不要按通配符進(jìn)行處理哦。
在某些公式中,星號(hào)(*)則表示通配符。
如公式:
=SUMIF(A:A,"HK*",B:B)
就是表示如果A列中以字符“HK”開頭,則計(jì)算所對(duì)應(yīng)的B列之和。
常用的支持通配符的函數(shù)包括:SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS等等。
除了使用星號(hào)(*)作為通配符按模糊條件進(jìn)行匯總之外,星號(hào)(*)還有另一個(gè)特殊的用途:如下圖所示,需要標(biāo)注身份證號(hào)碼是否重復(fù)。
如果直接使用下面的公式判斷,將無法得到正確結(jié)果。
=IF(COUNTIF(B:B,B2)>1,"重復(fù)","")
圖中,女神和金蓮的身份證號(hào)碼完全一致,公式貌似沒有問題,但是再看金花的身份證號(hào)碼,問題就來了,后三位不同的身份證號(hào)碼也被識(shí)別為相同了。
這是因?yàn)镃OUNTIF函數(shù)在處理文本型數(shù)字時(shí),會(huì)自動(dòng)按數(shù)值進(jìn)行處理,而Excel的最大精度只有15位,超過15位部分全部按0進(jìn)行處理,所以對(duì)于18位的身份證號(hào)碼出現(xiàn)了錯(cuò)誤判斷。
可以在公式中加上一個(gè)星號(hào)(*),以實(shí)現(xiàn)正確判斷。
=IF(COUNTIF(B:B,B2&"*")>1,"重復(fù)","")
加上星號(hào)(*)的目的是使其強(qiáng)制識(shí)別為文本,相當(dāng)于告訴COUNTIF,我找的是以B2單元格內(nèi)容開頭的文本,這樣就可以區(qū)分女神和金花了。
如果需要精確查找含有星號(hào)(*)的內(nèi)容,也需要進(jìn)行特殊的處理。
如下圖所示,需要根據(jù)D2單元格中的指定產(chǎn)品型號(hào)查詢對(duì)應(yīng)的供貨商,E2單元格公式為:
=INDEX(B:B,MATCH(D2,A:A,))
結(jié)果明顯不對(duì)了,明明是如花,返回怎么是女神呢。
由于D2單元格中包含星號(hào)(*),MATCH函數(shù)查找時(shí)就會(huì)默認(rèn)將星號(hào)按通配符處理,在B列中返回前兩個(gè)字符是“6S”、最后一個(gè)字符是“A”的位置。如果有多個(gè)符合條件的結(jié)果,MATCH函數(shù)只能返回第一個(gè)的位置,所以女神如花傻傻分不清了。
使用以下公式可以返回正確的結(jié)果:
=LOOKUP(1,0/(A2:A8=D2),B2:B8)
利用等式中不能使用通配符的特點(diǎn),用A2:A8=D2,以完全匹配的方式返回邏輯值TRUE或是FALSE。
再用0除以邏輯值,得到0或是錯(cuò)誤值#DIV/0!組成的內(nèi)存數(shù)組。最后使用1作為查找值,以內(nèi)存數(shù)組中最后一個(gè)0進(jìn)行匹配,并返回B2:B8單元格對(duì)應(yīng)位置的內(nèi)容。
最后留給大家一個(gè)小尾巴:如果在單元格中輸入下面的內(nèi)容,想想結(jié)果會(huì)是什么呢?
=4**5
打開Excel驗(yàn)證一下,你猜對(duì)了嗎,為什么會(huì)是這樣呢?