返回首页
当前位置: 主页 > 精通Office > Excel教程 >

对以下excel文字和数字是在同一个单元格里头的。将里面的数字求

时间:2012-04-30 00:47来源:知行网www.zhixing123.cn 编辑:麦田守望者

假定以下的范围数据在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技巧
------分隔线----------------------------
推荐内容
猜你感兴趣