ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 26938|回复: 62

[分享] 千变万化取数字

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-8-13 08:43 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:拆分和提取
本帖最后由 心电感应 于 2015-2-13 13:25 编辑

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

目录

规范类数据(一)分列篇-----------------2楼

规范类数据(一)函数篇-----------------4楼

(外一篇)非数值型数字-----------------7楼

规范类数据(二)加深篇-----------------9楼

规范数据(三)补充位数篇----------------12楼

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





评分

12

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-13 10:35 | 显示全部楼层

规范类数据(一)分列篇

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

在Excel单元格中,可以存储的类型大体上可以分为:汉字、数字、字母、符号。
我们都知道如果在单元格中输入数字时,是可以直接进行求和等操作的。
例1:
TM截图20140813085908.png
这个要求出所有品种的合计数字,很简单,对不对,只要一个sum求和就可以了。
  1. =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, 下载次数: 378)

TA的精华主题

TA的得分主题

发表于 2014-8-13 11:13 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-13 13:48 | 显示全部楼层

规范类数据(一)函数篇

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

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

  1. =FIND("/",A28)
复制代码



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

  1. =MID(A28,FIND("/",A28)+1,4)*1
复制代码



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

  1. =SUM(MID(A28:A32,FIND("/",A28:A32)+1,4)*1)
复制代码

-----------------------------------------------------
第二份数据
此类数据,其实在函数方法上,要比第一类数据简单一些,不需要进行判断关键字所在的位置,直接从第3个字符开始进行提取即可。
  1. =MID(D21,3,4)*1
复制代码


直接使用sum嵌套数组公式为:
  1. =SUM(MID(D21:D25,3,4)*1)
复制代码

第三份数据
第三份数据,因为数字的字符长度固定,而且处于字符的末端,提取时,可以使用right函数
  1. =RIGHT(G21:G25,2)*1
复制代码


直接使用sum嵌套数组公式为:
  1. =SUM(RIGHT(G21:G25,2)*1)
复制代码

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


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

  1. =SUM(RIGHT(A21:A25,LEN(A21:A25)*2-LENB(A21:A25)-1)*1)
复制代码

第二份数据与第三份数据:
  1. =SUM(RIGHT(D21:D25,LEN(D21:D25)*2-LENB(D21:D25))*1)
复制代码

------------------------------------------------------------------------------------------------------------
总结一下:
对于此类的非常有规律的数据,使用函数提取时,一般使用到的关键函数有:left,right,mid,find,len
千变万化取数字-规范数据-函数篇.rar (8.08 KB, 下载次数: 296)

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-8-13 14:04 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-8-13 14:21 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
apirl2008 发表于 2014-8-13 13:48
还是2楼的数据

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

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-13 15:09 | 显示全部楼层

(外一篇)非数值型数字

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

文本型数字

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

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

方法一:使用&""方法
  1. =A2&""
复制代码

此方法常见于提取不重复值的时候,会看到一大串的index之后&"",用此方法消除结果为0
方法二:text/mid/left/right函数法
  1. =TEXT(A2,"0;-0;0;@")
复制代码
  1. =MID(A2,1,LEN(A2))
复制代码

此方法一般见于文本与数字混搭时,进行其中部分的提取
方法三:分列法
在分列向导的中,一直单击“下一步”至第三步,勾选“文本”,单击完成
2.png
---------------------------------------------
文本型数字转数值

方法一:*1(乘1)、-0(减0)、--(减负)、/1(除1)
  1. =B2*1
复制代码
  1. =--B2
复制代码

方法二:VALUE函数法
  1. =VALUE(B2)
复制代码

方法三:分列法
在分列向导的中,一直单击“下一步”至第三步,勾选“常规”,单击完成
3.png

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

有时候,有些童鞋会说,看到的单元格中并没有绿三角,但求和还是出错,这是为啥?如下图
4.png
看起来,B1单元格中的数字与平常的数字是一样一样的,但是,通过求字符判断时就会发现,里面有不一样的东西,而且是看不到的,这就是Excel中所谓的不可见字符,如下图
5.png
现在可以使用一个清除不可见字符的函数,clean函数来清除掉这些讨厌的东西
  1. =SUM(CLEAN(B1:B3)*1)
复制代码

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

TA的精华主题

TA的得分主题

发表于 2014-8-13 15:27 | 显示全部楼层
学习了;;;;;;;;;;;;;;;;;;

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-8-13 16:47 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

规范数据(二)-加深篇

此篇还是归纳到规范的数据之中,虽然看起来,是没啥规律的,但仔细找找,还是很有规律的。
不多看,看图,看数据
1.png
(原贴链接:http://club.excelhome.net/thread-1144565-2-1.html
此份数据源,需要提取的是从左至右,第一段英文与第一段数字
这样的一份数据,咋看起真的没啥规律,但抽丝剥茧的仔细看看,还是有那点规律的。
首先,可以看到第4个字符是数字,而第5个字符起就会出来英文。
那就直接把这一段数据取出来单独看
  1. =mid(a2,4,5)
复制代码

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

使用F9对公式进行摸黑后可见如下数据
  1. ={TRUE;TRUE;TRUE;FALSE;FALSE}
复制代码

再看看,出现的第一个false的位置,就是字母所在的位置,那现在只要找出此位置即可完成提取
找位置使用的函数,除了find,还可以是match函数
  1. =MATCH(FALSE,MID(C2,ROW($A$1:$A$5),1)<="9",)
复制代码

现在已经得出了一组数字
3.png
再结合一下left函数,即可完成了提取工作
  1. =LEFT(A2,MATCH(FALSE,MID(C2,ROW($A$1:$A$5),1)<="9",)+2)
复制代码

此处+2其实是:+3-1,加上之前屏蔽掉的3个字符,再减去出现字母所占的一个字符
综合一下公式:
  1. =LEFT(A2,MATCH(FALSE,MID(MID(A2,4,5),ROW($A$1:$A$5),1)<="9",)+2)
复制代码

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


TA的精华主题

TA的得分主题

发表于 2014-8-13 16:48 | 显示全部楼层
又学了几招        收藏了
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 21:13 , Processed in 0.055122 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表