excel數(shù)字分離法薈粹.doc

《excel數(shù)字分離法薈粹.doc》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。

1、數(shù)字分離法薈粹在工作中，常常因填表人書寫不規(guī)范，造成匯總數(shù)據(jù)者無進(jìn)行數(shù)據(jù)運算?；蛘咄ㄟ^某些軟件導(dǎo)出的數(shù)據(jù)因產(chǎn)生了干擾符，造成后續(xù)運算難以進(jìn)行。本文力圖從以下五個方面者進(jìn)行演示、總結(jié)，期望給各位同仁的工作帶來便利。u去干擾符u數(shù)字在左邊u數(shù)字在右邊u數(shù)在字中間u同時提取多段數(shù)字一：去干擾符數(shù)據(jù)狀態(tài)：數(shù)據(jù)中包含某個非數(shù)字字符，使數(shù)字以文本型式存在于單元格中，讓公式無法運算。舉例：1.人為產(chǎn)生的干擾符。例如“36.9℃”，因為數(shù)據(jù)中存在“℃”而導(dǎo)致求平均時公式無法運算。2.導(dǎo)出軟件或者其它未知因素造成的干擾符。例如圖一，其中匯總結(jié)果有誤，因為單元格B2存

2、在干擾符。該符號看不見，也不是空格，但可以通過LEN函數(shù)計算單元格字符數(shù)來肯定它的存在。圖一　因干擾符而影響匯總解決辦法：對于人為錄入的數(shù)據(jù)單位，可以利用公式替換該字符為空，再進(jìn)行下一步運算。以圖一數(shù)據(jù)為例，假設(shè)B2:C9區(qū)域每個數(shù)據(jù)都有單位“℃”，那么計算平均溫度的數(shù)組公式如下：=AVERAGE(--LEFT(I2:I9,LEN(I2:I9)-1))公式含義：從每個單元格左邊第一位開始提取字符串，長度為總長度減1，即忽略符號“℃”，然后將提取出來的字符串轉(zhuǎn)換成數(shù)值，再求平均。而對于具有圖一這種未知因素產(chǎn)生的無法識別的干擾符地匯總運算，可以使用以下

3、數(shù)組公式：=SUM(--SUBSTITUTE(B2:B9,"?",""))公式含義：將干擾符替換成空白，再將字符串轉(zhuǎn)換成數(shù)值并匯總。提示：公式中的“?”符號不是空格，只能從該單元格復(fù)制到公式中，無法手工輸入。二：數(shù)字在左邊數(shù)據(jù)狀態(tài)：數(shù)字在單元格數(shù)據(jù)的左邊，右邊是不定長度的單位，從而造成無法計算。舉例：購買的物品數(shù)量中包括不定長度的單位，現(xiàn)需要取其金額，見圖二所示。圖二　采購表解決辦法：利用公式提取單元格中左邊的數(shù)據(jù)，再與單價相乘，從而獲得金額。D2單元格數(shù)組公式如下：=LOOKUP(9.99E+307,--LEFT(B2,ROW(A$1:A$100

4、)))*C2公式含義：分別提取數(shù)量單元格的左邊1位、2位、3位......直到100位，組成一個包含100個元素的內(nèi)存數(shù)組。再用“--”將其轉(zhuǎn)換成數(shù)值，最后通過LOOKUP函數(shù)從這個數(shù)組中提取最大值，即目標(biāo)數(shù)量。該值乘以單價即金額。三：數(shù)字在右邊數(shù)據(jù)狀態(tài)：數(shù)字在單元格數(shù)據(jù)的右邊，右邊是不定長度的數(shù)據(jù)說明，從而造成無法計算。舉例：單元格中除了成績外還包含對成績的漢字描述。見圖三。圖三　成績表解決辦法：將每個單元格的數(shù)據(jù)提取出來，然后再計算平均。數(shù)組公式如下：=SUM(LOOKUP(9.99E+307,--RIGHT(A2,ROW(A$1:A$100)

5、)))公式含義：分別提取數(shù)量單元格的右邊1位、2位、3位......直到100位，組成一個包含100個元素的內(nèi)存數(shù)組。再用“--”將其轉(zhuǎn)換成數(shù)值，最后通過LOOKUP函數(shù)從這個數(shù)組中提取最大值。四：數(shù)字在中間數(shù)據(jù)狀態(tài)：數(shù)字在單元格數(shù)據(jù)的中間，左右有不定長度的漢字或者字母，從而造成無法計算。舉例：采購表的一個單元格中記錄了品名、數(shù)據(jù)和單位，現(xiàn)需計算其實金額。見圖四。圖四　采購表解決辦法：將單元格中的數(shù)字提取出來再乘以單價，從而獲得金額。數(shù)組公式如下：=LOOKUP(9E+307,--MID(A2,MIN(FIND({1;2;3;4;5;6;7;8;9

6、},A2&123456789)),ROW(A$1:A$100)))*B2公式含義：利用MIN(FIND())組合計算出單元格中數(shù)字的起始位置，然后從該位置開始提取1位、2位、3位......直到100位數(shù)據(jù)，并轉(zhuǎn)換成數(shù)值，再用LOOKUP函數(shù)提取最大值，即為采購表中的數(shù)字部分，再乘以單價即得到最后結(jié)果。提示：如果單元格中有多段數(shù)字，本公式提取第一段數(shù)字。五：同時提取多段數(shù)字?jǐn)?shù)據(jù)狀態(tài)：在一個單元格中有多段數(shù)字，每個段數(shù)之間有不定長的漢字或者字母間隔。舉例：單元格包含多段數(shù)。如“長1米寬2米高12米”，需要將所有數(shù)字全部提取出來，見圖五所示。圖五　多段

7、數(shù)字混合解決辦法：要提取多段數(shù)字，則需要將字符串中每一個字符取出來，再用ISNNUMBER函數(shù)排除非數(shù)字部分，然后對剩下的數(shù)字部分通過轉(zhuǎn)換合并成一個值。公式如下：=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(A$1:A$100),1))*ROW(A$1:A$100),ROW(A$1:A$100))+1,1)*10^ROW(A$1:A$100)/10)公式含義：首先用MID函數(shù)取出每一個字符，再用ISNUMBER函數(shù)排數(shù)非數(shù)字，形成一個TRUE和FALSE組成的數(shù)組，將該數(shù)組與1到100的序列號相乘，就可以得到字

8、符串中每一個數(shù)字的所處位置。然后如每個位置提取一位數(shù)，即將所有數(shù)字全部提取數(shù)出來組成一個數(shù)組。最后為了將數(shù)組中的每個數(shù)字串