惱人的部分匹配查找

學習Excel技術,關注微信公眾號:

excelperfect


這天有空,小范照常開始了Excel的研習。俗話說,一天不練,手生腳慢;兩天不練,功夫減半;三天不練,成了門外漢。對於自己熱愛的Excel,小范從不馬虎。

 

他想研究前不久在chandoo.org網站上看到的一道題。小范習慣於瀏覽一些優秀的Excel站點,從中汲取營養,建議想要提高Excel技術水平的人養成這個習慣。

 

這道題是這樣的,從一列數據中提取出正確的內容,如下圖1所示。假設要剔除的單詞或字符總位於末尾,並且數據不是按字母順序排列的。

1

 

確實有點頭疼!如果只是查找“-”的位置,然後再提前該字符前的字符串,那很簡單:

=IF(FIND("-",B3),LEFT(B3,FIND("-",B3)-1),B3)

 

然而,該列數據中不只是有字符“-”,還會有“(”和“/”字符,並且這些字符的位置都會隨著字符串的變化而變化,每行數據中這些字符後面的單詞字符串長度也不同。

 

這涉及到一次查找多個不同字符的問題。

 

只有3個字符,使用條件判斷語句應該不會太長,經過嘗試後,小范寫下了公式:

=IFERROR(IFERROR(IFERROR(LEFT(B3,FIND("-",B3)-1),LEFT(B3,FIND("(",B3)-1)),LEFT(B3,FIND("/",B3)-1)),B3)

 

還不錯,只是判斷有點讓人眼花繚亂。但是,在這種情形下,與IF函數相比,IFERROR函數已經太好了。

 

還有其它的方法麼。

 

經過一番探索後,小范想到了利用數組。

 

先將每行數據暴力拆解,這通常使用MID/ROW函數組合來實現:

MID(B3,ROW($1:$100),1)

其中假設每行數據的字符不會超過100。這樣,就得到了由構成該行數據的單個字符組成的數組。

 

然後將其與字符“-”、“(”、“/”進行比較:

MID(B3,ROW($1:$100),1)={"-","(","/"}

得到一個由TRUE/FALSE值組成的數組,其中的TRUE值就是存在字符“-”、“(”、“/”中的一個。

 

在前面加上雙減號:

--(MID(B3,ROW($1:$100),1)={"-","(","/"})

將這個數組轉換成1/0組成的數組,再與{1;1;1}相乘:

MMULT(--(MID(B3,ROW($1:$100),1)={"-","(","/"}),{1;1;1})

求數組中各行的和,轉換成一個由0/1組成的單列數組。其中1的位置即字符“-”、“(”或“/”出現的位置。

 

然後使用MATCH函數查找1

MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={"-","(","/"}),{1;1;1}),0)

得到字符“-”、“(”或“/”出現的位置,然後使用LEFT函數提取出其左側的字符串。

 

完整的公式如下:

=IF(ISNUMBER(MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={"-","(","/"}),{1;1;1}),0)),LEFT(B3,MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={"-","(","/"}),{1;1;1}),0)-1),B3)

 

這是一個數組公式,輸入結束時要按Shift+Ctrl+Enter組合鍵。

 

終於解答出來了,小范長吁一口氣。

 

再看看別人的答案吧。學習他人編寫的公式,也是快速提升公式編寫能力的一個途徑。

 

於是,小范看到了這個公式:

=IF(COUNT(1/ISERR(SEARCH({"-","(","/"},B3)))=3,B3,D2)

 

牛!看來是我想多了。SEARCH/FIND函數就可以用來查找多個字符呀。這樣,公式就變簡單了。使用SEARCH/FIND函數在行數據中依次查找字符“-”、“(”和“/”,如果都沒有找到,就會返回3個錯誤值,表明獲取的數據就是該行數據本身,否則就取上一行已獲取的數據。

 

簡單!看來,提升無止境,還得繼續練習。

歡迎在下面留言,完善本文內容,讓更多的人學到更完美的知識。

歡迎到知識星球:完美Excel社群,進行技術交流和提問,獲取更多電子資料,並通過社群加入專門的微信討論群,更方便交流。

相關文章

地方民政部門依法取締的部分非法社會組織名單(第四批)

2021-06-08

為持續加大輿論宣傳力度,提高公眾防範意識,發揮社會監督作用,現向社會公佈第四批地方民政部門近年來依法取締的部分非法社會組織名單。社會公眾如果發現名單中的非法社會組織仍在開展活動的,可依法向公安機關報案。同時,提醒有關機構和社會公眾,在與社會組織開展合作或參與其活動時

地方民政部門依法取締的部分非法社會組織名單

2021-07-09

為持續加大輿論宣傳力度,提高公眾防範意識,發揮社會監督作用,現向社會公佈第六批地方民政部門近年來依法取締的部分非法社會組織名單。社會公眾如果發現名單中的非法社會組織仍在開展活動的,可依法向公安機關報案。同時,提醒有關機構和社會公眾,在與社會組織開展合作或參與其活動時

奶奶報警萬餘積蓄“被盜”保康民警耐心查找“破案”

2021-07-21

荊楚網(湖北日報網)訊(通訊員歐陽智慧、王浩然)15000元積蓄不翼而飛,家中門窗卻完好無損。7月21日,保康民警接到李某英奶奶報警,稱家中萬餘現金被盜。民警勘查現場後發現,原是虛驚一場,“被盜”現金安然無恙地躺在奶奶臥室衣櫃中。“警察同志,我的15000元現金不

鄭州鐵路:受大暴雨影響的部分旅客列車逐步恢復開行

2021-07-23

【鄭州鐵路:#受大暴雨影響的部分旅客列車逐步恢復開行#】記者23日從中國鐵路鄭州局集團有限公司瞭解到,該集團公司受大暴雨影響的部分旅客列車正在逐步恢復開行。當前,從西安方向去往商丘、徐州方向的直通高鐵列車、京廣高鐵部分直通高鐵列車可以通行。此外,該集團公司23日還將

喬丹體育捐出8月8日的部分銷售額,將用於……

2021-08-11

8月8日,在我國第13個全民健身日到來之際,喬丹體育官方宣佈,將8月8日當天的部分銷售額用於全面助力偏遠地區的體育、教育事業發展,用運動激發孩子們的興趣。據瞭解,喬丹體育秉持“取之社會,用之社會”的經營理念,通過“體育+教育+公益”深度融合發展的形式,努力提升社會責

犀利哦!浦北江濱西路一棵大榕樹的部分枝幹被風雨摧殘到斷裂

2021-08-14

今天中午約11點鐘左右,江濱西路靠近風雨橋附近路段的一棵大榕樹因持續的暴雨襲擊,使得密密麻麻的樹葉承受了雨水的重量後,頭重腳輕不堪重負多個樹幹活生生的被壓斷和撕裂,倒下的枝葉幾乎佔據了那一段馬路,已經影響到了交通的正常運行。正所謂“城門失火,殃及池魚”,倒塌的樹幹連

地方民政部門依法取締的部分非法社會組織名單(第七批)

2021-08-23

為持續加大輿論宣傳力度,提高公眾防範意識,發揮社會監督作用,現向社會公佈第七批地方民政部門近年來依法取締的部分非法社會組織名單。社會公眾如果發現名單中的非法社會組織仍在開展活動的,可依法向公安機關報案。同時,提醒有關機構和社會公眾,在與社會組織開展合作或參與其活動時

貴州省公安機關擬推薦參加全國第二批“楓橋式公安派出所”評選命名候選對象名單公示!銅仁兩個派出所上榜!

2021-08-28

公示根據全國公安機關“楓橋式公安派出所”評選命名工作部署,現將擬推薦參加全國第二批“楓橋式公安派出所”評選命名候選名單予以公示。貴陽市公安局白雲分局大山洞派出所清鎮市公安局麥格派出所遵義市公安局紅花崗分局老城派出所湄潭縣公安局興隆派出所六枝特區公安局落別派出所六盤水

山西:預計9月3日中南部有強降水天氣過程,臨汾、運城等地的部分地區有暴雨

2021-09-01

七月的風八月的雨九月的學校歡迎你經過一個假期的放鬆今天就要開學了此時天氣也是很給力氣溫適宜大部地區沒有雨水的打擾對學生返校不會造成影響不過受西風槽和低渦切變共同影響預計9月3日我省中南部有強降水天氣過程具體預報如下:今天下午到夜間:北中部晴天間多雲,南部多雲,運

女孩離家出走四天 小白樓派出所悉心查找

2021-09-02

8月27日10時許,小白樓派出所接到張女士報警求助,稱11歲的女兒幾日前離家出走了,希望民警幫忙找回女兒。經民警耐心詢問得知,8月23日張女士的女兒趁她睡覺之際離家出走,第二天女兒借用他人電話向張女士報了平安,而後又沒了消息。民警不由得產生疑問:四天前女兒離家出走,

中秋小記(附我讀過的部分書)

2021-09-20

中秋於我並沒有愉快的記憶,但一切都是可以改變的。從朋友家聚會回家洗漱完已經是凌晨一點,路上車依然很多,一樓人家的聚會還沒散,風輕輕地吹進車窗,吹著我,我看著前方。四十分鐘的回家路,竟然心生歡喜。月亮並沒有休息,它等著我,月光灑在床單上,那麼美,那麼安靜,那麼溫柔。皮

直鼻子咋給整歪了?

2021-09-23

愛美之心人皆有之,現在越來越多的人希望通過整形技術讓自己變得更美,這本無可厚非,但愛美一定要謹慎,因為整形失敗的案例也不在少數。近日,蚌山區人民法院審理一起因整形美容導致的醫療糾紛案件。經法官耐心調解,最終當事雙方達成調解協議,由整形醫院賠償原告醫療費、後續修復治療

城市通IC卡的部分業務可以網上辦理|哈熱電營業部暫停營業,交熱費可通過公眾號

2021-09-23

記者從交通集團城市通智能卡公司瞭解到,目前,城市通客戶服務中心及各分中心在嚴格落實疫情防控消殺措施下,窗口業務仍可正常辦理。不過智能卡公司提醒廣大用戶,充值、掛失等業務用戶可以在線上辦理,避免集中前往線下辦理。近年來城市通智能卡公司持續強化線上服務體系建設,目前通

因疫情滯留新疆伊犁的部分旅客7日起有序離開

2021-10-08

新華社烏魯木齊10月7日電(記者趙戈、周曄)10月7日17時42分,飛往鄭州的南航CZ570R航班從新疆伊寧機場起飛,這是7日起飛的第二架載有因疫情滯留伊犁哈薩克自治州的疆外旅客的飛機,預計當天還有兩架次航班準備離伊。記者從伊犁州黨委宣傳部獲悉,伊犁州疫情防控工作指

中央環保督察(十五):長春:完成治理的部分水體返黑返臭,河長製為何形同虛設?

2021-10-11

主城區僅兩處汙水口每天就有約4萬噸生活汙水直排,地標性溼地公園周邊臭氣熏天……“新華視點”記者近日跟隨中央第一生態環境保護督察組在吉林省發現,近年來,長春市下大力氣開展伊通河流域綜合整治,流域環境明顯改善。但部分區域汙水直排現象較為突出,已治理完成的部分水體返黑返臭

長春:完成治理的部分水體返黑返臭,河長製為何形同虛設?

2021-10-11

在柴戶張暗渠,大量淤泥漂浮在表面,水體渾濁,異味明顯(9月13日攝)。新華社發(中央第一生態環境保護督察組提供)南溪溼地後三家子溝水體重度黑臭(9月13日攝)。新華社發(中央第一生態環境保護督察組提供)在長春市眾恆路橋下,排汙口大量汙水直排進永春河(8月30日攝)。