千变万化取数字

apirl2008 · 发表于 2014-8-13 08:43

本帖最后由心电感应于 2015-2-13 13:25 编辑

序

这几天逛论坛时，发现大部分的问题总出在文本与数字上，因此让我有写此贴的冲动。
刚有这么个念头的时候，是在昨天半夜时分，居然在大脑里一旦成型，就一发不可收拾，觉得不写出来，就难受。
虽然我不是一个很精通的人，不过，我还是希望把我所知道的分享出来。
如果当中有写的不当之处，还希望各位可以帮我指出，希望本贴做为一块抛砖引玉的砖还引出更好的玉。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

规范类数据（一）分列篇－－－－－－－－－－－－－－－－－2楼

规范类数据（一）函数篇－－－－－－－－－－－－－－－－－4楼

（外一篇）非数值型数字－－－－－－－－－－－－－－－－－7楼

规范类数据（二）加深篇－－－－－－－－－－－－－－－－－9楼

规范数据（三）补充位数篇－－－－－－－－－－－－－－－－12楼

不规则文本数字混合装－－－－－－－－－－－－－－－－－－22楼

apirl2008 · 发表于 2014-8-13 10:35

本帖最后由 apirl2008 于 2014-8-13 13:58 编辑

在Excel单元格中，可以存储的类型大体上可以分为：汉字、数字、字母、符号。
我们都知道如果在单元格中输入数字时，是可以直接进行求和等操作的。
例1：
TM截图20140813085908.png

这个要求出所有品种的合计数字，很简单，对不对，只要一个sum求和就可以了。

＝sum(B3:B7)

复制代码

----------------------------------------------------------------------------------------------------
可是，万一，总之，现在是这个样子了，那又应该怎么办呢？
例2：
TM截图20140813090543.png

现在再直接进行sum求和，那可是不行的。得先处理一下数据，让它可以被sum函数认可才行的。
分析数据：对例2的数据进行观察可以发现一个规律：
第一份数据：汉字与数字之间始终有一个"/"进行分隔着
第二份数据：汉字的字符数始终只有2个，数字的字符数不定
第三份数据：正好与第二份数据相反，汉字的字符数不定，数字的字符数固定着是2位
OK，那现在我们就可以针对这些规律进行操作了。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
方法1：分列法
步骤1：[数据]－[数据工具]组－单击[分列]，打开文本分列向导；
TM截图20140813095241.png

第一份数据
步骤2：勾选分隔符号－单击下一步；
步骤3：在分隔符号中，勾选[其他]并在文本框中输入"/"，单击下一步，直至完成。
TM截图20140813095355.png

第二份数据
步骤2：勾选固定宽度－单击下一步；
TM截图20140813095748.png

步骤3：在数据预览的文本框内，在汉字与数字之间单击鼠标，设置分列线，后单击下一步直至完成。
TM截图20140813095908.png

第三份数据
无法使用一次分列得到结果，本方法不适用。
完成效果图如下：
TM截图20140813100541.png

此时再使用sum对这些数字进行求和，将不再是个难事了。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

千变万化取数字－规范数据－分列法.rar (6.99 KB, 下载次数: 380)

yi860214 · 发表于 2014-8-13 11:13

谢谢分享。李子老师辛苦了。

apirl2008 · 发表于 2014-8-13 13:48

本帖最后由 apirl2008 于 2014-8-13 14:00 编辑

还是2楼的数据

－－－－－－－－－－－－－－－－－－－－－－－－－
方法二：函数法
函数法使用时，需要对Excel内置的大部分函数的用法使用熟练，在看到这些数据时，大脑里就要能想到可以使用什么样的函数来达到这样的效果。
同样的，使用函数法时，还是针对有规律的点进行的，这样做的好处就是不需要想太过复杂的嵌套。
第一份数据
针对第一份数据中的"/"，只要能找出"/"所在的位置，那数字提取也不再是个难事了。
这时候就可以用到FIND，SEARCH此类函数。

=FIND("/",A28)

复制代码

通过FIND函数，可以得到一个数字，这个数字指出了"/"所在的位置。（find函数使用方法在此不再累赘，不会使用的童鞋可以自行查看一下Excel自带的帮助文件）
明白"/"后面紧跟着的就是需要的数字，现在已经知道了"/"所在的位置了，那取数字，就需要mid函数上场了。

=MID(A28,FIND("/",A28)+1,4)*1

复制代码

因为mid函数取出来的数字是属于文本类型数字，所以进行*1处理将文本类型转为数字类型（其他文本类型转数字类型可以参考第5楼）
此时再进行求和，不再是难事了。
还可以直接使用一个数组嵌套，直接得出结果
将mid做为一个数组，嵌套在sum函数中

=SUM(MID(A28:A32,FIND("/",A28:A32)+1,4)*1)

复制代码

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
第二份数据
此类数据，其实在函数方法上，要比第一类数据简单一些，不需要进行判断关键字所在的位置，直接从第3个字符开始进行提取即可。

=MID(D21,3,4)*1

复制代码

直接使用sum嵌套数组公式为：

=SUM(MID(D21:D25,3,4)*1)

复制代码

第三份数据
第三份数据，因为数字的字符长度固定，而且处于字符的末端，提取时，可以使用right函数

=RIGHT(G21:G25,2)*1

复制代码

直接使用sum嵌套数组公式为：

=SUM(RIGHT(G21:G25,2)*1)

复制代码

----------------------------------------------------------------------------------------
方法二：
通过利用文本与数字之间的字节长度的不同，进行提取数字
在Excel中，每一个数字，汉字，字母，符号都是占有一定的字节长度的
一般来说（半角类）：数字、符号、字母占字节长度为：1，而汉字及全角类的占字节长度为：2，可以通过lenb函数判断出来
如下图

汉字与数字混合时，使用：len()*2-lenb()方法即可得出数字所占的字符长度
而且本案中的数字均在数据的右侧，格式均为：汉字+数字
那么就可以利用这样的特点，直接使用right函数结合字节长度的不同进行提取数字
第一份数据：

=SUM(RIGHT(A21:A25,LEN(A21:A25)*2-LENB(A21:A25)-1)*1)

复制代码

第二份数据与第三份数据：

=SUM(RIGHT(D21:D25,LEN(D21:D25)*2-LENB(D21:D25))*1)

复制代码

------------------------------------------------------------------------------------------------------------
总结一下：
对于此类的非常有规律的数据，使用函数提取时，一般使用到的关键函数有:left,right,mid,find,len

千变万化取数字－规范数据-函数篇.rar (8.08 KB, 下载次数: 297)

sw=andy · 发表于 2014-8-13 14:04

前排招租！！！！！！！！
多谢楼主分享。3Q

Bodhidharma · 发表于 2014-8-13 14:21

apirl2008 发表于 2014-8-13 13:48
还是2楼的数据

－－－－－－－－－－－－－－－－－－－－－－－－－

當數據亂七八糟(貶義)的時候，就會有亂七八糟(褒義)的解法了……：
【学习并记录020】字符汉字数字提取之奇形怪状奇思妙想不完全收录
不過該篇雖然豐富，不過實在太亂了，應該沒有多少人有閒情逸致一個一個欣賞(汗)

看起來樓主打算好好梳理這些技巧，功大莫焉啊~

apirl2008 · 发表于 2014-8-13 15:09

写在规范篇后的番外篇，本篇主要介绍一下所谓的非数值型数字与数值型数字的区别及两者的转化

文本型数字

所谓的文本型数字，说白的就是在有数字的单元格的左上角出现一个绿色的小三角，此类的数字为文本型数字，没有小三角的就是数值了，如下图：

文本型数字与数值，在目测时，打印出来时看起来都是一样的，但对于Excel来说，两者是两个不同的数据。
对于文本型数字与数值时，有些函数是不能得到结果的，比如说:sum、vlookup、lookup
那么就需要掌握两者之间的转化方法了
－－－－－－－－－－－－－－－－－－－－－－－－－－－
数值转化为文本

方法一：使用&""方法

=A2&""

复制代码

此方法常见于提取不重复值的时候，会看到一大串的index之后&""，用此方法消除结果为0
方法二：text/mid/left/right函数法

=TEXT(A2,"0;-0;0;@")

复制代码

=MID(A2,1,LEN(A2))

复制代码

此方法一般见于文本与数字混搭时，进行其中部分的提取
方法三：分列法
在分列向导的中，一直单击“下一步”至第三步，勾选“文本”，单击完成

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
文本型数字转数值

方法一：*1（乘1）、-0（减0）、--（减负）、/1（除1）

=B2*1

复制代码

=--B2

复制代码

方法二：VALUE函数法

=VALUE(B2)

复制代码

方法三：分列法
在分列向导的中，一直单击“下一步”至第三步，勾选“常规”，单击完成

在文本型数字与数值转化中，最常使用的为方法一与方法三
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
有特殊的数字

有时候，有些童鞋会说，看到的单元格中并没有绿三角，但求和还是出错，这是为啥？如下图

看起来，B1单元格中的数字与平常的数字是一样一样的，但是，通过求字符判断时就会发现，里面有不一样的东西，而且是看不到的，这就是Excel中所谓的不可见字符，如下图

现在可以使用一个清除不可见字符的函数，clean函数来清除掉这些讨厌的东西

=SUM(CLEAN(B1:B3)*1)

复制代码

---------------------------------------------------------------

千变万化取数字－规范数据－番外篇.rar (7.86 KB, 下载次数: 196)

lpy6666 · 发表于 2014-8-13 15:27

学习了；；；；；；；；；；；；；；；；；；

apirl2008 · 发表于 2014-8-13 16:47

此篇还是归纳到规范的数据之中，虽然看起来，是没啥规律的，但仔细找找，还是很有规律的。
不多看，看图，看数据

（原贴链接：http://club.excelhome.net/thread-1144565-2-1.html）
此份数据源，需要提取的是从左至右，第一段英文与第一段数字
这样的一份数据，咋看起真的没啥规律，但抽丝剥茧的仔细看看，还是有那点规律的。
首先，可以看到第4个字符是数字，而第5个字符起就会出来英文。
那就直接把这一段数据取出来单独看

=mid(a2,4,5)

复制代码

这样取出来的数据之后，就直接把第一段英文给全部屏蔽掉，下面只要判断出取出字符中，从左至右首个出来的英文所在的位置就可以了
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
其实在Excel中，所输入的字符都是可以进行大小比较的。
大小的顺序为：数字<文本型数字<字母<汉字
对于单个字符来说：
最小的数字：0
最大的数字：9
最小的英文：A
最大的英文：z
在一般情况之下，Excel是不分英文大小写的，但有些函数是要区分大小写的，比如说SUBSTITUTE函数，FIND函数等
通常使用最小的汉字：吖
通常使用最大的汉字：座
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
明白字符比较大小之后，那只要将mid()提取出来的字符再进一步分解出来，与最大的数字进行比较，使之形成一组由TRUE与FLASE组成的数组

=MID(C2,ROW($A$1:$A$5),1)<="9"

复制代码

使用F9对公式进行摸黑后可见如下数据

={TRUE;TRUE;TRUE;FALSE;FALSE}

复制代码

再看看，出现的第一个false的位置，就是字母所在的位置，那现在只要找出此位置即可完成提取
找位置使用的函数，除了find，还可以是match函数

=MATCH(FALSE,MID(C2,ROW($A$1:$A$5),1)<="9",)

复制代码

现在已经得出了一组数字

再结合一下left函数，即可完成了提取工作

=LEFT(A2,MATCH(FALSE,MID(C2,ROW($A$1:$A$5),1)<="9",)+2)

复制代码

此处+2其实是：+3-1，加上之前屏蔽掉的3个字符，再减去出现字母所占的一个字符
综合一下公式：

=LEFT(A2,MATCH(FALSE,MID(MID(A2,4,5),ROW($A$1:$A$5),1)<="9",)+2)

复制代码

千变万化取数字－规范数据－加深篇.rar (7.37 KB, 下载次数: 208)

三好小学生 · 发表于 2014-8-13 16:48

又学了几招收藏了

		自动登录	找回密码
密码			免费注册

[分享] 千变万化取数字

评分

规范类数据（一）分列篇

规范类数据（一）函数篇

评分

（外一篇）非数值型数字

规范数据（二）－加深篇

浏览过的版块