IF函數(shù)經(jīng)典案例:判斷一個單元格內(nèi)是否包含指定關(guān)鍵詞
作者:郅龍來源:部落窩教育發(fā)布時間:2021-10-18 10:45:59點擊:14793
編按:
Hello小伙伴們,IF函數(shù)是我們在工作中使用最頻繁的函數(shù)之一。當我們想知道某一個單元格中的數(shù)據(jù)是否包含指定的關(guān)鍵詞時,我們就可以用IF函數(shù)搭配IFERROR、SUM等函數(shù)進行判斷,這其中充分利用了數(shù)組公式的特性。阿碩老師沿襲了自己由淺入深的講解風格,為我們帶來一篇干貨滿滿的教程,大家一起來學習一下吧~
哈嘍,大家好,歡迎來到部落窩教育!我是阿碩。最近,有小伙伴問了這樣一個問題:有一組關(guān)鍵詞,我想找出包含這組關(guān)鍵詞中任意一個關(guān)鍵詞的單元格,該如何做?
案例數(shù)據(jù)如下圖所示。A列是員工的姓名;B列是員工出差所經(jīng)過的城市;C列是要查詢的城市,也就是本例中我們要查詢的關(guān)鍵詞。在本例中,我們以兩個關(guān)鍵詞為例,分別是“長春”和“唐山”,如果員工經(jīng)過長春或者唐山,那么我們就將該員工標注為“經(jīng)過”,否則標記為“未經(jīng)過”。
下面,就讓我們開始今天的學習吧!
首先,我們在D2中輸入 “=FIND(C2:C3,B2,1)”,輸入之后,我們按下Ctrl+Shift+Enter,完成數(shù)組公式的輸入,得到的結(jié)果如下圖所示。
FIND數(shù)組函數(shù)解析:
1.由上圖我們可以看到,D2單元格顯示的數(shù)據(jù)是“4”。但是,由于剛才我們是使用數(shù)組方式來輸入的函數(shù),所以此時我們要注意了,D2中實際存放的是一個數(shù)組。
下面我們來對D2中的內(nèi)容一探究竟。我們先單擊選中D2單元格,再去函數(shù)編輯欄內(nèi)選中我們剛才寫的FIND函數(shù),然后按下F9鍵,就可以查看D2中的內(nèi)容,如下圖所示。(注:按ESC鍵即可退出查看。)
由上圖我們可以看到,D2中實際的內(nèi)容是“{4;#VALUE!}”,實際上它是由兩個數(shù)據(jù)構(gòu)成的一個數(shù)組。那么,這兩個數(shù)據(jù),是怎么來的呢?
2.我們剛才寫下的FIND函數(shù)的第一參數(shù)是C2:C3,即為我們要查詢的兩個關(guān)鍵詞“長春”和“唐山”,所以此處D2中的內(nèi)容,就是FIND函數(shù)返回的“長春”和“唐山”在B2中的位置序號,這兩個位置序號構(gòu)成了一個數(shù)組。我們可以用“手工”判斷一下:“長春”在B2中出現(xiàn)在第4個字符處,所以構(gòu)成數(shù)組的第一個數(shù)據(jù)是“4”;“唐山”在B2中未出現(xiàn),所以FIND函數(shù)定位不到“唐山”的位置,于是返回了一個錯誤值“#VALUE!”,相應地,構(gòu)成數(shù)組的第二個數(shù)據(jù)是“#VALUE!”。
小結(jié):
FIND函數(shù)的第一參數(shù),是我們要查詢的一組關(guān)鍵詞,在本例中為C2:C3;
FIND函數(shù)的第二參數(shù),是要在其中定位關(guān)鍵詞的單元格,在本例中為B2(如果考慮到函數(shù)向下復制填充,則依次為B2至B7);
FIND函數(shù)的第三參數(shù)為1,即從首字符開始查詢。同時,要謹記,輸入函數(shù)之后,要按Ctrl+Shift+Enter構(gòu)造數(shù)組。
接下來,我們修改一下剛才所寫的FIND函數(shù)。由于在本例中,我們要查詢的關(guān)鍵詞始終是C2:C3中的數(shù)據(jù),所以為了防止向下復制填充公式的時候參數(shù)發(fā)生變化,我們需要對C2:C3做一下絕對引用。我們將D2中的函數(shù)公式修改為“=FIND($C$2:$C$3,B2,1)”,改好之后,按Ctrl+Shift+Enter構(gòu)造數(shù)組。然后,我們向下復制填充公式至D7,得到的結(jié)果如下圖所示。
這里,有一點需要大家要注意:由于我們要在其中查詢關(guān)鍵詞的單元格是變化的,在本例中依次為B2至B7,所以D2中FIND函數(shù)公式中的第二參數(shù),不需要加絕對引用,直接使用B2作為第二參數(shù)即可。
小伙伴們可以通過F9鍵來查看D2:D7中的內(nèi)容。為了便于大家理解,阿碩為大家做了整理,如下圖所示。
接下來,我們對“#VALUE!”進行一下修正。對于“#VALUE!”或“#N/A”等錯誤值,我們可以用IFERROR函數(shù)來修正。
在本例中,我們把“#VALUE”替換成0。我們將D2中的公式修改為“=IFERROR(FIND($C$2:$C$3,B2,1),0)”,然后按Ctrl+Shift+Enter構(gòu)造數(shù)組,然后再向下復制填充公式,得到的結(jié)果如下圖所示。
有的小伙伴可能會問,為什么要用0來修正?這是因為,在接下來的步驟中,我們將要進行求和運算,如果使用其他數(shù)據(jù)來修正的話,會影響求和結(jié)果,只有使用0來修正才不會影響求和的結(jié)果。
此時,如果我們再來查看D2:D7中的內(nèi)容,就會發(fā)現(xiàn),其中的數(shù)據(jù)內(nèi)容已經(jīng)發(fā)生了變化,如下圖所示。
好了,接下來,我們對D2:D7中的內(nèi)容來求和,我們將D2中的公式修改為“=SUM(IFERROR(FIND($C$2:$C$3,B2,1),0))”,然后按Ctrl+Shift+Enter構(gòu)造數(shù)組,然后再向下復制填充公式,得到的結(jié)果如下圖所示。
此時,D2至D7中的內(nèi)容,已經(jīng)是用IFERROR函數(shù)修正過之后的數(shù)據(jù)總和,也就是關(guān)鍵詞在B2至B7中相應的位置序號的和,由于我們剛剛做了求和運算,所以D2至D7中現(xiàn)在只保存一個數(shù)據(jù)了。
這里,也有一點需要注意,雖然D2至D7中每個單元格內(nèi)只保存一個數(shù)據(jù),但它們依然是數(shù)組,只不過每個單元格中數(shù)組存放的數(shù)據(jù)數(shù)量是1而已。
下面,讓我們來觀察一下數(shù)據(jù)中的規(guī)律:如果員工經(jīng)過長春和唐山這兩個城市之一,那么D2至D7中相應單元格中的數(shù)據(jù)一定是大于0的,如果這兩個城市均未經(jīng)過,則D2于D7相應單元格中的數(shù)據(jù)為0。找到這個規(guī)律后,距離勝利就不遠了,我們只需用IF函數(shù)來判斷一下就行啦!
我們將D2中函數(shù)公式修改為“=IF(SUM(IFERROR(FIND($C$2:$C$3,B2,1),0))>0,"經(jīng)過","未經(jīng)過")”,然后按Ctrl+Shift+Enter構(gòu)造數(shù)組,然后再向下復制填充公式,得到的結(jié)果如下圖所示。
好了,至此,查詢一組關(guān)鍵詞的方法,就講完啦!
小彩蛋
有的小伙伴可能會覺得C列擺在數(shù)據(jù)表中有點礙眼,想問問如果把C列去掉,能不能查詢一組關(guān)鍵詞。答案是能!下面我們來講一下。
我們可以單擊選中D2單元格,然后在函數(shù)欄中,選中“$C$2:$C$3”這一段(注意:只選這一段代碼即可,千萬別多選或者少選),然后按下F9鍵,如下圖所示。
此時,“$C$2:$C$3”變成了“{"長春";"唐山"}”,也就是我們要查詢的關(guān)鍵詞本身(之前我們那個方式是引用C2:C3中的內(nèi)容)。相應地,D2單元格中的公式變成了“=IF(SUM(IFERROR(FIND({"長春";"唐山"},B2,1),0))>0,"經(jīng)過","未經(jīng)過")”,然后我們按Ctrl+Shift+Enter構(gòu)建數(shù)組并向下復制填充公式,得到的結(jié)果如下圖所示。
這時,我們就可以將C列刪除了??梢钥吹?,將C列刪除后,得到的結(jié)果并不受影響,如下圖所示。
好了,各位小伙伴,我們今天的學習內(nèi)容就是這些,你學會了嗎?
最后,給大家留一個小作業(yè):請對經(jīng)過沈陽、哈爾濱、黑河這三個城市的員工進行標記。一定要試著做一下哦!
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:
相關(guān)推薦:
9條最實用的計算excel中關(guān)于日期的公式?。ńㄗh收藏)
版權(quán)申明:
本文作者阿碩;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!