wps数字分离法讲解荟粹

　　在工作中，常常因填表人书写不规范，造成汇总数据者无进行数据运算。或者通过某些软件导出的数据因产生了干扰符，造成后续运算难以进行。本文力图从以下五个方面者进行演示、总结，期望给各位同仁的工作带来便利。
　　去干扰符
　　数字在左边
　　数字在右边
　　数字在字中间
　　同时提取多段数字

　　一：去干扰符
　　数据状态：数据中包含某个非数字字符，使数字以文本型式存在于单元格中，让公式无法运算。
　　举例：
　　1.人为产生的干扰符。例如“36.9℃”，因为数据中存在“℃”而导致求平均时公式无法运算。
　　2.导出软件或者其它未知因素造成的干扰符。例如图一，其中汇总结果有误，因为单元格B2存在干扰符。该符号看不见，也不是空格，但可以通过LEN函数计算单元格字符数来肯定它的存在。

 

图一　因干扰符而影响汇总

　　解决办法：

　　对于人为录入的数据单位，可以利用公式替换该字符为空，再进行下一步运算。以图一数据为例，假设B2:C9区域每个数据都有单位“℃”，那么计算平均温度的数组公式如下：

=AVERAGE(--LEFT(B2:B9,LEN(B2:B9)-1))

　　公式含义：从每个单元格左边第一位开始提取字符串，长度为总长度减1，即忽略符号“℃”，然后将提取出来的字符串转换成数值，再求平均。

　　而对于具有图一这种未知因素产生的无法识别的干扰符地汇总运算，可以使用以下数组公式：=SUM(--SUBSTITUTE(B2:B9," ",""))

　　公式含义：将干扰符替换成空白，再将字符串转换成数值并汇总。

　　提示：公式中的“
”符号不是空格，只能从该单元格复制到公式中，无法手工输入。


　　二：数字在左边

　　数据状态：数字在单元格数据的左边，右边是不定长度的单位，从而造成无法计算。

　　举例：购买的物品数量中包括不定长度的单位，现需要取其金额，见图二所示。

 

图二　采购表

　　解决办法：

　　利用公式提取单元格中左边的数据，再与单价相乘，从而获得金额。D2单元格数组公式如下：

=LOOKUP(9.99E+307,--LEFT(B2,ROW(A$1:A$100)))*C2

　　公式含义：分别提取数量单元格的左边1位、2位、3位......直到100位，组成一个包含100个元素的内存数组。再用“--”将其转换成数值，最后通过LOOKUP函数从这个数组中提取最大值，即目标数量。该值乘以单价即金额。

　　三：数字在右边

　　数据状态：数字在单元格数据的右边，右边是不定长度的数据说明，从而造成无法计算。

　　举例：单元格中除了成绩外还包含对成绩的汉字描述。见图三。

 

图三

　　解决办法：

　　将每个单元格的数据提取出来，然后再计算平均。数组公式如下：

=SUM(LOOKUP(9.99E+307,--RIGHT(A2,ROW(A$1:A$100))))

　　公式含义：分别提取数量单元格的右边1位、2位、3位......直到100位，组成一个包含100个元素的内存数组。再用“--”将其转换成数值，最后通过LOOKUP函数从这个数组中提取最大值。

       四：数字在中间

　　数据状态：数字在单元格数据的中间，左右有不定长度的汉字或者字母，从而造成无法计算。

　　举例：采购表的一个单元格中记录了品名、数据和单位，现需计算其实金额。见图四。

 

图4

　　解决办法：

　　将单元格中的数字提取出来再乘以单价，从而获得金额。数组公式如下：

=LOOKUP(9E+307,--MID(A2,MIN(FIND({1;2;3;4;5;6;7;8;9},A2&123456789)),ROW(A$1:A$100)))*B2

　　公式含义：利用MIN(FIND())组合计算出单元格中数字的起始位置，然后从该位置开始提取1位、2位、3位......直到100位数据，并转换成数值，再用LOOKUP函数提取最大值，即为采购表中的数字部分，再乘以单价即得到最后结果。

　　提示：如果单元格中有多段数字，本公式提取第一段数字。



       五：同时提取多段数字

　　数据状态：在一个单元格中有多段数字，每个段数之间有不定长的汉字或者字母间隔。

　　举例：单元格包含多段数数。如“长1米宽2米高12米”，需要将所有数字全部提取如来，见图五所示。

 

图五　多段数字混合

　　解决办法：

　　要提取多段数字，则需要将字符串中每一个字符取出来，再用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的序列号相乘，就可以得到字符串中每一个数字的所处位置。然后如每个位置提取一位数，即将所有数字全部提取数出来组成一个数组。最后为了将数组中的每个数字串连在一起，所以将每个数字乘扩大10的0次方倍、10的1次方倍、10的2次方倍...当所有数据汇总后就得到了字符串的每一个数字。

　　提示：本方法仅仅演示从字符串中提取数字的技巧，在实际工作中可能会极少用到。

　　本文所用到的所有实例、公式都在附表中。读者可以下载验证。



        本文来自wps金山社区

[ 本帖最后由 拥护扑奔 于 2008-10-26 15:34 编辑 ]