数字分离荟粹.docVIP

数字分离法荟粹 在工作中，常常因填表人书写不规范，造成汇总数据者无进行数据运算。或者通过某些软件导出的数据因产生了干扰符，造成后续运算难以进行。本文力图从以下五个方面者进行演示、总结，期望给各位同仁的工作带来便利。 去干扰符 数字在左边 数字在右边 数在字中间 同时提取多段数字 一：去干扰符 数据状态：数据中包含某个非数字字符，使数字以文本型式存在于单元格中，让公式无法运算。 举例： 人为产生的干扰符。例如“36.9℃”，因为数据中存在“℃”而导致求平均时公式无法运算。 导出软件或者其它未知因素造成的干扰符。例如图一，其中汇总结果有误，因为单元格B2存在干扰符。该符号看不见，也不是空格，但可以通过LEN函数计算单元格字符数来肯定它的存在。 图一　因干扰符而影响汇总 解决办法： 对于人为录入的数据单位，可以利用公式替换该字符为空，再进行下一步运算。以图一数据为例，假设B2:C9区域每个数据都有单位“℃”，那么计算平均温度的数组公式如下： =AVERAGE(--LEFT(I2:I9,LEN(I2:I9)-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函数从这个数组中提取最大值。 四：数字在中间 数据状态：数字在单元格数据的中间，左右有不定长度的汉字或者字母，从而造成无法计算。 举例：采购表的一个单元格中记录了品名、数据和单位，现需计算其实金额。见图四。 图四　采购表 解决办法： 将单元格中的数字提取出来再乘以单价，从而获得金额。数组公式如下： =LOOKUP(9E+307,--MID(A2,MIN(FIND({1;2;3;4;5;6;7;8;9},A2123456789)),ROW(A$1:A$100)))*B2 公式含义：利用MIN(FIND())组合计算出单元格中数字的起始位置，然后从该位置开始提取1位、2位、3位......直到100位数据，并转换成数值，再用LOOKUP函数提取最大值，即为采购表中的数字部分，再乘以单价即得到最后结果。 提示：如果单元格中有多段数字，本公式提取第一段数字。 五：同时提取多段数字 数据状态：在一个单元格中有多段数字，每个段数之间有不定长的汉字或者字母间隔。 举例：单元格包含多段数数。如“长1米宽2米高12米”，需要将所有数字全部提取如来，见图五所示。 图五　多段数字混合 解决办法： 要提取多段数字，则需要将字符串中每一个字符取出来，再用ISNNUMBER函数排除非数字部分，然后对剩下的数字部分通过转换合并成一个值。公式如下： =SUM(MID(0A1,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的序列号相乘，就可以得到字符串中每一个数字的所处位置。然后如每个位置提