加载中…excel单元格中的**万，变成数字的方法，并使最终结果为能够参与运算的数值型数字
(2018-11-20 09:23:51) | 分类： office软件使用技巧 |
Excel中“50万”变成“500000”的方法见下图
一、思路
第一步：用“if”、“countif”、“substitute”连用去掉单元格总数字后面的单位（如万、亿等）；
第二步：将去掉单位后的数字变成数值型数字，以便运算。具体方法是在第一步的公式中乘以或除以1、或10000或100000000等。
注释：SUBSTITUTE()是一个替换函数，将A1至A11里面的“元”去掉（这里是将“元”替换为空字符）。由于SUBSTITUTE()函数替换之后是文本形式，不能直接计算，因此这里采用连续的两个负号将其变成数字。比方说，16元在经过SUBSTITUTE()函数替换之后变成了文本16，由于SUM()函数不认识它，因此两个负号相当于连续两次对其取相反数，这样就变成可以由SUM()函数计算的数值了。下面的这个公式也行得通：=SUM(SUBSTITUTE(A1:A11,"元","")*1)&"元"，这里是将SUBSTITUTE()替换之后的文本乘以1，这样也变成了数值。还可以使用这个公式：=SUM(VALUE(SUBSTITUTE(A1:A11,"元","")))&"元"，VALUE()函数则直接将文本变成数值形式。如果你不希望最后的结果出现单位，那就更好办啦：=SUM(--SUBSTITUTE(A1:A11,"元",""))。若是这些数据正好属于一个矩形区域，比如A1：B10区域，那就把公式这样改：=SUM(VALUE(SUBSTITUTE(A1:B10,"元","")))&"元"。最后你会说，如果不使用SUBSTITUTE()函数可以吗？答案很简单：没问题！（把公式粘贴过去之后，别忘了同时按ctrl+shift+enter组合键进行确认哦！）=SUM(VALUE(LEFT(A1:A11,LEN(A1:A11)-1)))&"元"。再有什么问题的话，就给我发消息吧！
二、具体公式：
1、去掉e3单元格中的“亿”或“万”，并使最后得到的数字为能够参与运算的数值型数字，具体公式如下：=IF(COUNTIF(E3,"*亿"),SUBSTITUTE(E3,"亿","")/1,IF(COUNTIF(E3,"*万"),SUBSTITUTE(E3,"万","")/10000,""))
2、实例1
http://s9/mw690/0019ge8Azy7pl2IH7vi98&690
3、实例2：一列中既含万又含亿的去除方法
http://s3/mw690/0019ge8Azy7pU7UzUmC62&690
3、网络摘抄文本：Excel中substitute函数的使用方法
方法
-
1
SUBSTITUTE函数格式为：
SUBSTITUTE(text，old_text，new_text，instance_num)
text为需要替换其中字符的文本（或含有文本的的单元格的引用）
old_text为需要替换的旧文本
new_text为用于替换old_text的文本
instance_num用来指定以new_text替换第几次出现的old_text -
2
SUBSTITUTE函数使用方法如下：如图，如果希望对A1单元格内容中第2个字母进行替换，则可以使用公式来实现：
=SUBSTITUTE(A1,"e","o") -
3
对于文本字符串的替换也是如此，如果想将A2单元格中“开心”替换成“快乐”，则可以使用如下的公式：
=SUBSTITUTE(A2,"开心","快乐") -
4
替换数字串中的某几位：如图，如果想替换A3单元格内容中第2位数字“1”为“9”，则可以使用公式：
=SUBSTITUTE(A3,"1","9",2) -
5
此外，该函数还支持对单元格的引用，如图，如果想对B3单元格中的内容进行部分替换，可使用如下公式：
=SUBSTITUTE(B3,"1","9",1) -
6
在使用SUBSTITUTE函数的过程中，如果参数“instance_num”小于1则会显示“#VALUE！”的错误提示
喜欢
0
赠金笔