前幾天有學員問了個問題,如圖所示,求合格率,要求是表格不能有任何的改動,只能用公式實現。
我的第一反應是:這個表中的文本幸好是非常有規律和規範的,用函數就可以輕鬆實現。當然,如果學員再學點VB的知識,那這個就非常容易實現了。
作為基礎課程,我們還是再熟悉一下函數,看看我們通過公式如何來解決這種有規律的文本問題。
分析
很明顯,計算合格率,我們必須將數字從文本中提取出來,我們可以看到出現的第一個數字是在“查”字之後,第一個“處”字之前,所以這第一組數據的提取可以這樣做:
第一:數字是從第3位開始的,我們提取的時候從第3位進行提取;
第二:“處”之前就是數字的最後一位了,所以我們只需要去掉處所在的位置,那麼留下的就是整個要提取的數字部分了;
第一組數據提取
通過上面的分析,我們提取的話,肯定會用到MID這個函數,也就是從什麼地方開始,到什麼地方結束,提取中間的內容。
我們來把公式拆解進行理解:
=MID(文本內容,開始位置,留下幾個文本)
文本內容:抽查500處,合格114處
開始位置:3
留下文本數量:3
很顯然,難點是這個留下的文本數量,因為是500的時候,你要留下3個文本,那一位數或兩位數的時候,你需要留下的就是一個或兩個文本了,這個數量就是變化的,所以我們要以不變應萬變。我們這裡只能通過“處”的位置來確定。
還記得上一節給大家講過的Find函數吧。我們通過Find函數看看“處”字在什麼位置就好了。所以公式是:
=Find(“處”,文本內容所在單元格),即如下圖所示:
把這結束位置減去這個起始位置,得到的不就是中間文本的長度麼?而且開始位置固定就是從3開始。所以公式的整合就是:
將公式套入進去應該是:
=MID(B3,3,FIND(“處”,B3)-3)
是不是非常容易就得出了我們要求合格率的分母。
第二組數據的提取
按照上面的邏輯,我們的開始位置可以是從“格”字開始,留取直到倒數第二位的文本長度就可以了。
所以根據上述的分析,我們找第二組數據的開始位置。
找到“格”字之後再往後增加一位就是了,所以公式是:
=FIND(“格”,B3)+1
結果是10,同樣根據前面的邏輯,我們看到最後的一個“處”字只佔據一個字符,那麼我們可以根據文本的總長度來進行計算。這裡LEN函數就是計算文本總長度的。
即:=LEN(B3)
文本總長度是:13
我們最終要留下的長度是3位,所以直接是13-10就可以了。如圖所示:
所以把所有的公式套入其中,就應該是:
=MID(B3,(FIND("格",B3)+1),LEN(B3)-(FIND("格",B3)+1)
這樣也就輕鬆了獲取了我們要的分子。
最終結果
上述提取了這兩組數據,計算合格率就非常簡單了。直接套入就好了:
=(MID(B3,(FIND("格",B3)+1),LEN(B3)-(FIND("格",B3)+1)))/(MID(B3,3,FIND("處",B3)-3))
如圖:
大功告成!
=========================
所以當你遇到一個問題需要解決的時候,尤其是需要公式進行套用或嵌套的時候,沒必要一把到位,先分解後再合併也是寫正確公式的一種做法,而且自己還能從中找到分析解決問題的方法。
如果你有其他更多的方法,歡迎留言一起交流學習。