在先前的文章《實用Excel技巧分享:7個提高表格檢視效率的方法》中,我們了解了7個提高表格檢視效率的小技巧。而今天我們來聊聊Excel提取數字,介紹幾類比較常見的提取情況進行講解,快來學習學習!
使用公式提取單元格內的數字,要根據具體的資料結構找到某種規律從而設計出對應的公式。當然也有所謂的萬能提取公式,不過非常複雜且計算量大,因此只在文末簡單說明。
第一類情況:數字在左側
#例一:數字在左側,且數字位數固定。
在這個例子中,資料非常有規律,數字都在左邊的三位,要想將學號單獨提取出來,只需要使用left函數即可: =LEFT(A2,3)
#公式也非常簡單,第一個參數是要提取數字的單元格,第二個參數是要提取幾位(從左邊算起)。對於例一的這一類數據,使用的時候會根據數字的長度修改第二參數即可。
例二:數字在左側,數字位數不固定,但有明顯的分隔符號。
如果數字長度不是固定的三位,使用之前的方法就不行了,如下圖。這時就需要找到資料來源的規律,再利用規律進行操作。目前的資料統一存在一個"-"號,可以根據"-"號的位置來決定數字的長度,再用left擷取。
對於這種情況需要用到另一個函數來幫忙,那就是find函數。
利用公式=FIND("-",A2,1)
可以確定"-"的位置。
find函數有三個參數,第一個參數是要找什麼內容,第二個參數是從哪裡找,第三個參數是從第幾個字開始找(從左邊算起)。
=FIND("-",A2,1)
意思是在A2單元格找“-”,從開始位置找(左邊第一位)。公式的結果是數字,代表「-」在儲存格的位置(第幾個字元),此時我們需要擷取的數字長度就是find的結果減1,因此方法就有了:=LEFT(A2 ,FIND("-",A2,1)-1)
#這個方法的適用範圍也比較廣泛,只要可以發現比較明顯的分隔符號(可以是符號,漢字,字母等等內容),都可以用這個方法。
例三:數字在左邊,位數不固定且沒有明顯的分隔符號。
如果資料是這種情況,那麼前面的方法都不能用了,數字長度不確定,也沒分隔符號,唯一的規律就是資料裡只有數字和漢字(沒有字母以及其他符號)。
在Excel中,字元的長度有兩種,半角字元(數字,字母以及英文方式下輸入的符號)長度為1,而全角字元(漢字和中文符號等)長度為2。可以透過一個實例來了解這一點:
len函數是一個專門計算單元格內容長度的函數,不區分全角半角,其長度與我們平時理解的一致,相當於字元的「個數」。下面再來看看區分全角和半角的情況:
lenb函數的作用於len函數一致,差異就是計算內容的長度時會區分半角全角,從結果可以看出明顯的差異。其中文字以全角計算的長度是不區分全半角的2倍,而數字、字母在兩種情況下則完全一致。
對於例三的情況,就需要使用len與lenb來決定數字的長度,再用left來擷取。
利用公式=LEN(A2)*2-LENB(A2)
可以決定數字的長度,如下圖所示。
為什麼len*2-lenb
就會得到數字的長度?
根據前面兩種情況下的比較結果,lenb統計長度,文字長度是len統計值的兩倍,而數字長度與len統計值一致,所以公式len*2-lenb就可以算出數字的長度了。
數字的長度統計出來以後,解決方法顯而易見:=LEFT(A2,LEN(A2)*2-LENB(A2))
例三這種情況同樣比較典型,只要記得是len*2-lenb就可以了,如果確實記不住,那麼遇到問題的時候可以單獨算一算,先把數字的長度算出來,再組合left就行了。
例四:數字在左邊,並且含有字母等半角字元。
這種情況很複雜,唯一的規律就是數字在左邊。來看看如何解決吧。
可以利用公式:=-LOOKUP(1,-LEFT(A2,ROW($1:$9)))
。
關於這個公式,要解釋的話估計還得五千字,所以各位只需要記得套路即可,唯一有可能修改的就是最後面那個9,當最長的數字超過9位時,就要修改這個9,可以直接用99來完成。
最後,這個公式適用於任何數字在左邊的情況。
第二類情況:數字在右邊
數字在右邊的情況與數字在左邊非常類似,以下僅做簡單說明:
數字位數固定的直接用right提取;
數字位數不固定但有分隔符號的使用find函數配合mid函數完成提取: =MID(A2,FIND("-",A2,1) 1,9)
(註:公式中最後一個參數值「9」需要根據數字的最大位數修改,譬如提取的數字最大位數是10,則需要寫成10或大於10的數字。)
數字位數不固定同時沒有分隔符號的還是可以使用len和lenb的組合:=RIGHT(A2,LEN(A2)*2-LENB(A2))
規則都沒有的繼續讓lookup發大招:=-LOOKUP(1,-RIGHT(A2,ROW($1:$9)))
第三類情況:數字在中間
數字在中間這種非常特殊,比較規範的資料來源中一般不會出現這樣的情況,舉個例子:
例如圖中這種數據,如果數字前面或後面的長度固定,可以先用函數將內容變成前面討論過的情況再去處理:
公式比較簡單,不做解釋了,至於後面一步怎麼做,根據前面的學習你已經會了吧?
下面要說的這個方法就是比較通用的方法了,有人稱之為萬能提取公式:
=-LOOKUP(0,-MID(A1,MIN (FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))
陣列公式,需依三鍵「Ctrl shift 回車」結束。
小結
關於如何使用公式提取單元格內的數字,以上將絕大多數情況都進行了說明,當然不是全部,例如含有小數的情況,因為有了小數點,就更加特殊了。
最後的那個陣列公式可以算是比較通用的公式了。需要強調的是:大部分的問題之所以變得非常麻煩,就是因為不規範的數據源導致的,因此養成好的習慣,一個單元格只存放一種屬性的數據,例如把名稱與數量、價格等資訊分開存放,這樣就會大大提高統計效率。
當然站在提升公式運用能力的角度來說,數字提取這一類問題也是函數練習的好題目。希望大家能夠結合以前學過的函數,思考出更多的公式來實現數字的提取。
相關學習推薦:excel教學
以上是實用Excel技巧分享:如何提取數字?的詳細內容。更多資訊請關注PHP中文網其他相關文章!