千变万化取数字

ldda

赞一个，留下学习。

apirl2008

看图例：

A列是一般的物体的体积，可有一天，老板说，这样看起来实在是太乱了，让给整理整理，把所有的数字的位数变成4位数不足4位以0前置补充，让数字看起来非常的整齐美观，如B列效果。
老实说，老板有时候的想法，还真的是。。。。。。。（李子小声嘀咕一下）
但不管怎么说，老板需要的，咱做员工的，就得给出来。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法一：分列+函数法
直接对关键字"*"进行分列，形成三列数字，然后再使用text函数进行0前置补充，使用"&"符号进行文本连接。
如下效果：

1. =TEXT(A12,"0000")&TEXT(B12,"!*0000")&TEXT(c12,"!*0000")

复制代码

此方法的好处就是，不需要动太多的脑筋，只需要掌握一个text函数的简单用法即可完成
坏处：
1、如果数据发生变化，还得再次分列，无法做到动态化；
2、占用了3列做为辅助列，无法突显“高手”的作为。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法二：找“*”位置法
嗯，基于上述的坏处，下面就介绍一下函数的嵌套如何处理此类数据
同样，分析数据之后，会发现，数字是分为三段，中间由*隔开，重点要做的事情就是如何精准的提取这三段数字。
前面有过说，与提取常的函数有mid/left/right，本案也是同样，但如何去确定Start_num参数所在的位置就是难点了。
笨办法，知道就那么两个"*"，先用find找出第一个"*"所在位置，再使用find+SUBSTITUTE函数来找出第二个"*"所在位置。
第一个“*”所在位置：

1. =FIND("*",A2)

复制代码

第二个“*”所在位置：

1. =FIND("*",SUBSTITUTE(A2,"*","@",1))

复制代码

第一段数字：

1. =LEFT(A2,FIND("*",A2)-1)

复制代码

第二段数字：

1. =MID(A2,FIND("*",A2)+1,FIND("*",SUBSTITUTE(A2,"*","@",1))-FIND("*",A2)-1)

复制代码

第三段数字：

1. =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"*","@",1)))

复制代码

1. =LOOKUP(9^12,--RIGHT(A2,ROW($1:$9)))

复制代码

利用lookup函数进行数据查找最大数字，使公式更加简洁，具体公式可见13楼（人事难料）
利用0进行位置补充：

1. =TEXT(E2,"0000")&TEXT(F2,"!*0000")&TEXT(G2,"!*0000")

复制代码

最终代入之后公式结果：

1. =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参数。
那么我们想象一下，在此三段数字中间，隔了很多个空格，而且这个空格的长度已经超过了原数据本身所占的字符长度。

1. =SUBSTITUTE(A2,"*",REPT(" ",15))

复制代码

如下图所示

看，使用红线分隔的，是不是已经将数字完整的分成了三段了？
而这个位置也非常的好确定出来，最终效果需要的字符长度为：4*3+2＝14个字符，增加了：15*2-2＝28个字符，平均分为三段，（14+28）/3＝14，平均点分别为：{1，14，28}，那需要提取的字符长度为15，也就是空格的长度。

1. =MID(C2,{1,14,28},15)

复制代码

当数字被提成一段一段的之后，再进行组合起来让它变成一组数字

1. =SUM(MID(C2,{1,14,28},15)*10^{8,4,0})

复制代码

再使用text进行分隔,当这些数字提取出来之后，再进行位数的填充分隔也不再是个难事了，与前面一样一样的

1. =TEXT(G2,"0000!*0000!*0000")

复制代码

组合起来公式：

1. =TEXT(SUM(MID(SUBSTITUTE(A2,"*",REPT(" ",15)),{1,14,28},15)*10^{8,4,0}),"0000!*0000!*0000")

复制代码

千变万化取数字－规范数据－补充数位篇.rar (12.39 KB, 下载次数: 161)

2014-8-14 15:33 上传

点击文件名下载附件

人事难料

apirl2008 发表于 2014-8-14 14:42
看图例：

A列是一般的物体的体积，可有一天，老板说，这样看起来实在是太乱了，让给整理整理，把所有的数 ...

试验了一下发现第三段公式改成lookup可以缩短一些。

1. =TEXT(LOOKUP(9^12,--RIGHT(A2,ROW($1:$9))),"!*0000")

复制代码

apirl2008

明天开始写不规范数据了，心中忐忑啊，我得好好想想，举些乱七八糟的例子

gydzzh

收了，谢谢

的风格123123

收藏学习，苦学中。。。。。

weifankun

真是让我大开眼界啊，谢谢楼主！

吉丫

今天才看 不错 谢谢楼主 辛苦啦

mgblue

apirl2008 发表于 2014-8-14 17:22
明天开始写不规范数据了，心中忐忑啊，我得好好想想，举些乱七八糟的例子

楼主还没写好啊，大家都在等着看呢。

mgblue

mgblue 发表于 2014-9-2 14:10
楼主还没写好啊，大家都在等着看呢。

呵呵，不是催您，只是很期待！