|
藍字發(fā)送【目錄】
送你200篇獨家Excel精華教程 編按： 有些單位部分賬目不夠規(guī)范,，譬如報銷表，報銷內容中文字和金額數據是記錄在一起的,，怎么求和得到報銷金額呢？通常的做法是需要增加一列,，把數據單獨提取出來,，然后進行求和。當然也可以用公式一步到位直接求和。
數據求和原本很簡單,，可是如果數據錄入不規(guī)范,，譬如賬目中的文字和數據記錄在一起，那求和就復雜了,！請看下面的例子,。這是一個簡單的流水賬，費用內容文字和具體金額是記錄在一個單元格內的,，現在要計算總的消費金額,。 如何求和呢？常規(guī)操作,，是先建立輔助列把金額數據提取出來,，然后進行求和。
數據提取,，大家的第一反應可能是用Ctrl+E,。然而此處，用Ctrl+E會遇到問題,，因為有小數和負數存在,。下圖是用Ctrl+E的效果，存在錯誤： 當前數據少,，我們可以Ctrl+E后再手動修改個別錯誤數據,。如果數據有成百上千條呢？顯然我們需要另外的方法來處理,。 我們一起來分析這組數據的特點,。總結后有以下幾點：
數據和文本混合,，文本全部為漢字，沒有字母,。 每個單元格中只包含了一個數據,。 存在小數和負數。 在數據（含負號在內）前后沒有其他單字節(jié)字符,。 數據在字符串中所在位置不同,，也沒有規(guī)律。 數據的字符長度不固定,。
針對以上的幾個特點,，我們可以考慮使用MIDB函數、SEARCHB函數來提取數字,。=--MIDB(B2,SEARCHB("?",B2),2*LEN(B2)-LENB(B2))把數據從字符串中提取出來就是一個文本提取操作。當前的難點是每個單元格中數據開始提取的位置和長度不固定,。從前面的分析中,，我們得到一個可以利用的規(guī)律：在數據（含負號在內）前后沒有其他單字節(jié)字符。這條規(guī)律的價值在于,，單字節(jié)字符部分就是我們需要的數據（含負號）,。那只要能用函數自動獲取每個單元格中單字節(jié)字符的開始位置和長度，一切問題就解決了,。MIDB函數是根據指定字節(jié)數提取文本,。 它與我們常用的MID函數用法一致，唯一的區(qū)別就是：MID根據字符數來提取文本,，MIDB根據字節(jié)數來提取文本,。 SEARCHB函數用于解決從哪里開始提取文本。 SEARCHB函數是查找第一個字符串在第二個字符串中的起始位置,。它返回的起始位置用字節(jié)數表示,。 SEARCHB("?",B2)表示在B2單元格（第二個字符串）中查找第一個單字節(jié)（“？”）字符的字節(jié)位置,。其中“,？”在本處表示任意單字節(jié)字符。SEARCHB("?",B2)實際查找的就是數字6（它是B2單元格中第一個單字節(jié)字符）在B2中的位置,。在公式中選中SEARCHB("?",B2)按F9,，可以看到返回結果是7。
這部分函數公式幫我們解決了從哪里開始提取文本,。 2*LEN(B2)-LENB(B2)這部分函數公式解決了文本的提取長度,。 LEN(B2)統計的是B2單元格字符串的字符數；LENB(B2)統計的是B2單元格字符串的字節(jié)數,。兩倍字符數減去字節(jié)數就可以得到單元格中單字節(jié)字符的字節(jié)數,，對本例來說就是得到數據的字節(jié)數。
現在,，數字提取出來了,，最后再利用SUM函數求和即可。 這次我們來增加一些難度,。要求在一個單元格內直接用公式求出總費用。
在單元格D2中輸入公式,，三鍵回車（Shfit+Ctrl+Enter）即可。=SUM(--MIDB($B$2:$B$10,SEARCHB("?",$B$2:$B$10),2*LEN($B$2:$B$10)-LENB($B$2:$B$10)))要直接用SUM求和,，那就必須讓MIDB函數返回一組數而不是單個的值,。因此我們把上一個公式中的B2更換成了B2:B10，再套入到SUM函數中，輸入完畢按三鍵回車,。
|
