|

楼主 |
发表于 2014-8-14 14:42
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
规范数据(三)补充数字位数
本帖最后由 apirl2008 于 2014-8-14 16:55 编辑
看图例:
A列是一般的物体的体积,可有一天,老板说,这样看起来实在是太乱了,让给整理整理,把所有的数字的位数变成4位数不足4位以0前置补充,让数字看起来非常的整齐美观,如B列效果。
老实说,老板有时候的想法,还真的是。。。。。。。(李子小声嘀咕一下)
但不管怎么说,老板需要的,咱做员工的,就得给出来。
-------------------------------------------------------------
方法一:分列+函数法
直接对关键字"*"进行分列,形成三列数字,然后再使用text函数进行0前置补充,使用"&"符号进行文本连接。
如下效果:
- =TEXT(A12,"0000")&TEXT(B12,"!*0000")&TEXT(c12,"!*0000")
复制代码
此方法的好处就是,不需要动太多的脑筋,只需要掌握一个text函数的简单用法即可完成
坏处:
1、如果数据发生变化,还得再次分列,无法做到动态化;
2、占用了3列做为辅助列,无法突显“高手”的作为。
--------------------------------------------------------
方法二:找“*”位置法
嗯,基于上述的坏处,下面就介绍一下函数的嵌套如何处理此类数据
同样,分析数据之后,会发现,数字是分为三段,中间由*隔开,重点要做的事情就是如何精准的提取这三段数字。
前面有过说,与提取常的函数有mid/left/right,本案也是同样,但如何去确定Start_num参数所在的位置就是难点了。
笨办法,知道就那么两个"*",先用find找出第一个"*"所在位置,再使用find+SUBSTITUTE函数来找出第二个"*"所在位置。
第一个“*”所在位置:
第二个“*”所在位置:
- =FIND("*",SUBSTITUTE(A2,"*","@",1))
复制代码
第一段数字:
第二段数字:
- =MID(A2,FIND("*",A2)+1,FIND("*",SUBSTITUTE(A2,"*","@",1))-FIND("*",A2)-1)
复制代码
第三段数字:
- =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"*","@",1)))
复制代码- =LOOKUP(9^12,--RIGHT(A2,ROW($1:$9)))
复制代码
利用lookup函数进行数据查找最大数字,使公式更加简洁,具体公式可见13楼(人事难料)
利用0进行位置补充:
- =TEXT(E2,"0000")&TEXT(F2,"!*0000")&TEXT(G2,"!*0000")
复制代码
最终代入之后公式结果:
- =TEXT(LEFT(A2,FIND("*",A2)-1),"0000")&TEXT(MID(A2,FIND("*",A2)+1,FIND("*",SUBSTITUTE(A2,"*","@",1))-FIND("*",A2)-1),"!*0000")&TEXT(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"*","@",1))),"!*0000")
复制代码
效果图如下:
嗯方法虽好,就是太长了点
-----------------------------------------------------
有没有更短的一点的公式呢,有那是肯定的,但我个人觉得,公式长点没事,别写一个公式要花个几天时间,就得不偿失了。
下面介绍个思路,希望给后来者能留下个念想,在以后遇到此类情况时,可以想到此方法。(其实李子也是跟在前人的后面学了一点皮毛,希望对以后人有用)
废话不再说了,介绍方法是正路。
方法三:空格补充法
方法二说的是将一个一个的“*”的位置给找出来,那此方法说的是直接将“*”用空格给替换掉。
不知道有没有童鞋发现前面几贴中使用mid函数的时候,都是使用一个较大的数字来确定mid函数的Num_chars参数。
那么我们想象一下,在此三段数字中间,隔了很多个空格,而且这个空格的长度已经超过了原数据本身所占的字符长度。
- =SUBSTITUTE(A2,"*",REPT(" ",15))
复制代码
如下图所示
看,使用红线分隔的,是不是已经将数字完整的分成了三段了?
而这个位置也非常的好确定出来,最终效果需要的字符长度为:4*3+2=14个字符,增加了:15*2-2=28个字符,平均分为三段,(14+28)/3=14,平均点分别为:{1,14,28},那需要提取的字符长度为15,也就是空格的长度。
当数字被提成一段一段的之后,再进行组合起来让它变成一组数字
- =SUM(MID(C2,{1,14,28},15)*10^{8,4,0})
复制代码
再使用text进行分隔,当这些数字提取出来之后,再进行位数的填充分隔也不再是个难事了,与前面一样一样的
- =TEXT(G2,"0000!*0000!*0000")
复制代码
组合起来公式:
- =TEXT(SUM(MID(SUBSTITUTE(A2,"*",REPT(" ",15)),{1,14,28},15)*10^{8,4,0}),"0000!*0000!*0000")
复制代码
千变万化取数字-规范数据-补充数位篇.rar
(12.39 KB, 下载次数: 163)
|
|