资源描述：
《excel数字分离法荟粹.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、数字分离法荟粹在工作中，常常因填表人书写不规范，造成汇总数据者无进行数据运算。或者通过某些软件导出的数据因产生了干扰符，造成后续运算难以进行。本文力图从以下五个方面者进行演示、总结，期望给各位同仁的工作带来便利。u去干扰符u数字在左边u数字在右边u数在字中间u同时提取多段数字一：去干扰符数据状态：数据中包含某个非数字字符，使数字以文本型式存在于单元格中，让公式无法运算。举例：1.人为产生的干扰符。例如“36.9℃”，因为数据中存在“℃”而导致求平均时公式无法运算。2.导出软件或者其它未知因素造成的干扰符。例如图一，其中汇总结果有误，因为单元格B2存
2、在干扰符。该符号看不见，也不是空格，但可以通过LEN函数计算单元格字符数来肯定它的存在。图一 因干扰符而影响汇总解决办法：对于人为录入的数据单位，可以利用公式替换该字符为空，再进行下一步运算。以图一数据为例，假设B2:C9区域每个数据都有单位“℃”，那么计算平均温度的数组公式如下：=AVERAGE(--LEFT(I2:I9,LEN(I2:I9)-1))公式含义：从每个单元格左边第一位开始提取字符串，长度为总长度减1，即忽略符号“℃”，然后将提取出来的字符串转换成数值，再求平均。而对于具有图一这种未知因素产生的无法识别的干扰符地汇总运算，可以使用以下
3、数组公式：=SUM(--SUBSTITUTE(B2:B9," ",""))公式含义：将干扰符替换成空白，再将字符串转换成数值并汇总。提示：公式中的“ ”符号不是空格，只能从该单元格复制到公式中，无法手工输入。二：数字在左边数据状态：数字在单元格数据的左边，右边是不定长度的单位，从而造成无法计算。举例：购买的物品数量中包括不定长度的单位，现需要取其金额，见图二所示。图二 采购表解决办法：利用公式提取单元格中左边的数据，再与单价相乘，从而获得金额。D2单元格数组公式如下：=LOOKUP(9.99E+307,--LEFT(B2,ROW(A$1:A$100
4、)))*C2公式含义：分别提取数量单元格的左边1位、2位、3位......直到100位，组成一个包含100个元素的内存数组。再用“--”将其转换成数值，最后通过LOOKUP函数从这个数组中提取最大值，即目标数量。该值乘以单价即金额。三：数字在右边数据状态：数字在单元格数据的右边，右边是不定长度的数据说明，从而造成无法计算。举例：单元格中除了成绩外还包含对成绩的汉字描述。见图三。图三 成绩表解决办法：将每个单元格的数据提取出来，然后再计算平均。数组公式如下：=SUM(LOOKUP(9.99E+307,--RIGHT(A2,ROW(A$1:A$100)
5、)))公式含义：分别提取数量单元格的右边1位、2位、3位......直到100位，组成一个包含100个元素的内存数组。再用“--”将其转换成数值，最后通过LOOKUP函数从这个数组中提取最大值。四：数字在中间数据状态：数字在单元格数据的中间，左右有不定长度的汉字或者字母，从而造成无法计算。举例：采购表的一个单元格中记录了品名、数据和单位，现需计算其实金额。见图四。图四 采购表解决办法：将单元格中的数字提取出来再乘以单价，从而获得金额。数组公式如下：=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())组合计算出单元格中数字的起始位置，然后从该位置开始提取1位、2位、3位......直到100位数据，并转换成数值，再用LOOKUP函数提取最大值，即为采购表中的数字部分，再乘以单价即得到最后结果。提示：如果单元格中有多段数字，本公式提取第一段数字。五：同时提取多段数字数据状态：在一个单元格中有多段数字，每个段数之间有不定长的汉字或者字母间隔。举例：单元格包含多段数。如“长1米宽2米高12米”，需要将所有数字全部提取出来，见图五所示。图五 多段
7、数字混合解决办法：要提取多段数字，则需要将字符串中每一个字符取出来，再用ISNNUMBER函数排除非数字部分，然后对剩下的数字部分通过转换合并成一个值。公式如下：=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函数取出每一个字符，再用ISNUMBER函数排数非数字，形成一个TRUE和FALSE组成的数组，将该数组与1到100的序列号相乘，就可以得到字
8、符串中每一个数字的所处位置。然后如每个位置提取一位数，即将所有数字全部提取数出来组成一个数组。最后为了将数组中的每个数字串
