小夥伴們好啊,今天和大家分享的是一組特殊數據的處理。
先看下面的數據,是各部門的獎勵補貼數據,同一個部門的數據都擠在一個單元格內,現在要計算每個部門的獎勵補貼總和,該如何處理呢?
接下來咱們說說幾種常用方法:
方法一:
復制Excel中B列的混合內容,開啟Word,右鍵貼上為文本。
然後按Ctrl+H鍵,調出替換對話方塊。
尋找內容輸入:
[!^1-^127]{1,}
替換為輸入加號「 + 」
然後選擇使用通配符,點選全部替換。
再將替換後的內容復制貼上到Excel即可:
方法二:
右鍵單擊工作表標簽→檢視程式碼
在VBE界面中點選【插入】→【模組】,然後輸入以下程式碼:
Function GetNum(S$)
Dim i&, SS
For i = 1 To Len(S)
SS = Val(Mid(S, i))
If SS <> 0 Then i = i + Len(SS): GetNum = GetNum + SS
Next i
End Function
關閉VBE視窗,在C2單元格輸入自訂函式,OK了:
=GetNum(B2)
使用該方法時,註意需要將檔保存為xlsm格式。
方法三:
如果你使用的是Excel 2021,可以使用以下公式處理:
=SUM(1*TEXTSPLIT(B2,TEXTSPLIT(B2,ROW($1:$10)-1,,1),,1))
TEXTSPLIT(B2,ROW($1:$10)-1,,1)部份,
使用
ROW($1:$10)-1的結果,也就是序號0~9作為間隔符號,對B2單元格的內容進行第一次拆分,第三參數使用1,表示忽略空白單元格。拆分結果為:
{"補貼:王亮","牛莉",",優秀人員標兵:李誌文","房新軍","王新","牛一萬"}
接下來再進行第二次拆分,第二拆分的間隔符號為第一次拆分得到結果,也就是除了數位之外的文本內容,拆分結果為:
{"5","10","300","300","300","300"}
將提取後的文本型數位乘以1變成數值格式,最後使用SUM函式求和。
方法四:
如果你使用的是最新版WPS表格,這個問題就簡單了。
C2單元格輸入以下公式,向下復制:
=SUM(1*REGEXP(B2,"\d+"))
REGEXP函式是WPS表格中特有的支持正規表式的函式,本例中,正規表式為「\d+」,表示要分別提取出連續的數位。
將提取後的文本型數位乘以1變成數值格式,最後使用SUM函式求和。
雖然咱們今天提供了四種解決不規範數據的處理方法,但是絕不能認為數據輸入可以隨心所欲。規範的數據是高效辦公的基礎,切記:
數據匯總時流的汗,就是錄入數據時腦子進的水。
圖文制作:祝洪忠