返回首頁
當前位置: 主頁 > 精通Office > Excel教程 >

對以下excel文字和數字是在同一個單元格里頭的。將里面的數字求

時間:2012-04-30 00:47來源:知行網www.aotfjk.live 編輯:麥田守望者

假定以下的范圍數據在A2:A18,而且所有數據的數字最后的單位都是1個"臺"字,可用以下數組公式求和:
=SUM(VALUE(MID(A1:A18,LEN(A1:A18)-(LEN(A1:A18)-(LENB(A1:A18)-LEN(A1:A18))),LEN(A1:A18)-(LENB(A1:A18)-LEN(A1:A18)))))&"臺"
注:輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{}"。
{=SUM(VALUE(MID(A1:A18,LEN(A1:A18)-(LEN(A1:A18)-(LENB(A1:A18)-LEN(A1:A18))),LEN(A1:A18)-(LENB(A1:A18)-LEN(A1:A18)))))&"臺" }

 

品名
手機32臺
電腦300臺
電冰箱254臺
手機33臺
電腦301臺
電冰箱255臺
手機34臺
電腦302臺
電冰箱256臺
手機35臺
電腦303臺
電冰箱257臺
手機36臺
電腦304臺
電冰箱258臺
手機37臺
電腦305臺
3302臺

45元
9元
14元
12元
25元
21元
10元
17元
18元
74元
在EXCEL中怎樣把以上的這些數加起來?

可以使用函數幫你直接完成。講解如下 :

將以上數據置入A2到A11單元格,
假定A2至A11是你需要計算的數據,則可以在任一單元格輸入以下函數(由于這是數組公式,因此輸完之后需要按ctrl+shift+enter組合鍵來確認):
=SUM(--SUBSTITUTE(A2:A11,"元",""))&"元"
同時按ctrl+shift+enter三個鍵之后會在編輯欄出現一對大括號,這是Excel自動生成的。不可以手動加上套括號。

這個函數不算難,簡要解釋一下:
SUBSTITUTE()是一個替換函數,將A2至A11里面的“元”去掉(這里是將“元”替換為空字符)。由于SUBSTITUTE()函數替換之后是文本形式,不能直接計算,因此這里采用連續的兩個負號將其變成數字。比方說,16元在經過SUBSTITUTE()函數替換之后變成了文本16,由于SUM()函數不認識它,因此兩個負號相當于連續兩次對其取相反數,這樣就變成可以由SUM()函數計算的數值了。下面的這個公式也行得通:
=SUM(SUBSTITUTE(A2:A11,"元","")*1)&"元"
這里是將SUBSTITUTE()替換之后的文本乘以1,這樣也變成了數值。還可以使用這個公式:
=SUM(VALUE(SUBSTITUTE(A2:A11,"元","")))&"元"
VALUE()函數則直接將文本變成數值形式。

如果你不希望最后的結果出現單位,那就更好辦啦:
=SUM(--SUBSTITUTE(A2:A11,"元",""))

若是這些數據正好屬于一個矩形區域,比如A2:B10區域,那就把公式這樣改:
=SUM(VALUE(SUBSTITUTE(A2:B10,"元","")))&"元"

最后你會說,如果不使用SUBSTITUTE()函數可以嗎?答案很簡單:沒問題!(把公式粘貼過去之后,別忘了同時按ctrl+shift+enter組合鍵進行確認哦!
=SUM(VALUE(LEFT(A2:A11,LEN(A2:A11)-1)))&"元"

 

金額 公式
45  
9  
14  
12  
25  
21  
10  
17  
18  
74  
245元 =SUM(--SUBSTITUTE(A2:A11,"",""))&""
245元 =SUM(SUBSTITUTE(A2:A11,"","")*1)&""
245元 =SUM(VALUE(SUBSTITUTE(A2:A11,"","")))&""
245元 =SUM(VALUE(LEFT(A1:A11,LEN(A1:A11)-1)))&""

177,046.68元
10,336.00元
4,000.00元
37,063.77元
500.00元
1,766.40元
8,895.31元
4,000.00元
2,030.00元
22,198.00元
7,178.14元
10,735.53元
72,197.00元
以上的數據按照帶單位求和(二)方法中的方法就不行了。

現在的數據格式應該為文本型(請不要復制以上的數據到EXCEL,請自行在EXCEL輸入數據進行測試),需將數據去掉"元"字,再自定義為帶單位"元"的數字型格式就可以用于計算了

1.去掉"元"字
用查找替換,將"元"替換為""

2.菜單:格式->單元格->自定義單元格格式為:#,##0.00"元"

------分隔線----------------------------
標簽(Tag):EXCEL Excel2007 Excel技巧 Excel2003 Excel2010 Excel教程 Excel實例教程 excel2010技巧
------分隔線----------------------------
推薦內容
猜你感興趣
湖南刘雪龙黑彩