Excel中如何提取数字?会这几招就够了

来源:优页文档 作者:优页文档

有读者留言希望能够整理一期内容,讲解从数字、汉字中提取汉字或者数字的方法,本文将对比较常见的几类提取情况进行讲解。使用公式提取单元格内的数字,要根据具体的数据结构找到某种规律从而设计出对应的公式。当然也有所谓的万能提取公式,不过非常复杂并且计算量大,因此只在文末简单说明。

第一类情况:数字在左侧

例一:数字在左侧,且数字位数固定。

图片

在这个例子中,数据非常有规律,数字都在左边的三位,要想将学号单独提取出来,只需要使用left函数即可:=LEFT(A2,3)

图片

公式也非常简单,第一个参数是要提取数字的单元格,第二个参数是要提取几位(从左边算起)。对于例一的这一类数据,使用的时候根据数字的长度修改第二参数即可。

例二:数字在左侧,数字位数不固定,但是有明显的分隔符号。

如果数字长度不是固定的三位,使用之前的方法就不行了,如下图。这时就需要找到数据源的规律,再利用规律进行操作。当前的数据统一存在一个"-"号,可以根据"-"号的位置来确定数字的长度,再用left提取。

图片

对于这种情况需要用到另一个函数来帮忙,那就是find函数。

利用公式=FIND("-",A2,1)可以确定"-"的位置。

图片

find函数有三个参数,第一个参数是要找什么内容,第二个参数是从哪里找,第三个参数是从第几个字开始找(从左边算起)。

=FIND("-",A2,1) 意思是在A2单元格找“-”,从开始位置找(左边第一位)。公式的结果是数字,代表“-”在单元格的位置(第几个字符),此时我们需要提取的数字长度就是find的结果减1,因此方法就有了:=LEFT(A2,FIND("-",A2,1)-1)

图片

这个方法的适用范围也比较广泛,只要可以发现比较明显的分隔符号(可以是符号,汉字,字母等等内容),都可以使用这个方法。

例三:数字在左边,位数不固定并且没有明显的分隔符号。

图片

如果数据是这种情况,那么前面的方法都不能用了,数字长度不确定,也没分隔符号,唯一的规律就是数据里只有数字和汉字(没有字母以及其他符号)。

在Excel中,字符的长度有两种,半角字符(数字,字母以及英文方式下输入的符号)长度为1,而全角字符(汉字和中文符号等)长度为2。可以通过一个实例来了解这一点:

图片

len函数是一个专门计算单元格内容长度的函数,不区分全角半角,其长度与我们平时理解的一致,相当于字符的“个数”。下面再来看看区分全角和半角的情况:

图片

lenb函数的作用于len函数一致,区别就是计算内容的长度时会区分半角全角,从结果可以看出明显的差异。其中文字按全角计算的长度是不区分全半角的2倍,而数字、字母在两种情况下则完全一致。

对于例三的情况,就需要使用len与lenb来确定数字的长度,再用left提取。

利用公式=LEN(A2)*2-LENB(A2)可以确定数字的长度,如下图所示。

图片

为什么len*2-lenb就会得到数字的长度呢?

根据前面两种情况下的对比结果,lenb统计长度,文字长度是len统计值的两倍,而数字长度与len统计值一致,所以公式len*2-lenb就可以计算出数字的长度了。

数字的长度统计出来以后,解决方法显而易见:=LEFT(A2,LEN(A2)*2-LENB(A2))

图片

例三这种情况同样比较典型,只要记住是len*2-lenb就可以了,如果确实记不住,那么遇到问题的时候可以单独算一算,先把数字的长度算出来,再组合left就行了。

例四:数字在左边,并且含有字母等半角字符。

图片

这种情况很复杂,唯一的规律就是数字在左边。来看看如何解决吧。

可以利用公式:=-LOOKUP(1,-LEFT(A2,ROW($1:$9)))。

图片

关于这个公式,要解释的话估计还得五千字,所以各位只需要记得套路即可,唯一有可能修改的就是最后面那个9,当最长的数字超过9位时,就要修改这个9,可以直接用99来完成。

最后,这个公式适用于任何数字在左边的情况 

第二类情况:数字在右边

数字在右边的情况与数字在左边非常类似,以下仅做简单说明:

数字位数固定的直接用right提取;

图片

数字位数不固定但有分隔符号的使用find函数配合mid函数 完成提取:=MID(A2,FIND("-",A2,1)+1,9)

图片

注:公式中最后一个参数值“9”需要根据数字的最大位数修改,譬如提取的数字最大位数是10,则需要写成10或者大于10的数字。)

数字位数不固定同时没有分隔符的还是可以使用len和lenb的组合:=RIGHT(A2,LEN(A2)*2-LENB(A2))

图片

什么规律都没有的继续让lookup发大招:=-LOOKUP(1,-RIGHT(A2,ROW($1:$9)))

图片


第三类情况:数字在中间

数字在中间这种非常特殊,比较规范的数据源中一般不会出现这样的情况,举一个例子吧:

图片

例如图中这种数据,如果数字前面或者后面的长度固定,可以先用函数将内容变成前面讨论过的情况再去处理:

图片

公式比较简单,不做解释了,至于后面一步怎么做,根据前面的学习你已经会了吧?

下面要说的这个方法就是一种比较通用的方法了,有人称其为万能提取公式:

=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

图片

数组公式,需要按三键“Ctrl+shift+回车”结束。 

小结

关于如何使用公式提取单元格内的数字,以上将绝大多数情况都进行了说明,当然不是全部,例如含有小数的情况,因为有了小数点,就更加特殊了。

最后的那个数组公式可以算是一个比较通用的公式了。需要强调的是:大部分的问题之所以变得非常麻烦,就是因为不规范的数据源导致的,因此养成好的习惯,一个单元格只存放一种属性的数据,例如把名称与数量、价格等信息分开存放,这样就会大大地提高统计效率。

当然站在提高公式运用能力的角度来说,数字提取这一类问题也是函数练习的好题目。希望大家能够结合以前学过的函数,思考出更多的公式来实现数字的提取。



资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 毕业论文答辩PPT
  • 毕业论文答辩PPT
  • 暨南大学汇报答辩通用模板PPT
  • 开题报告毕业论文答辩PPT
  • 临床医学硕士研究生毕业论文答辩PPT
  • 毕业论文答辩PPT
  • 202X毕业论文答辩PPT
  • 通用毕业答辩PPT
  • 汉语专业毕业答辩PPT
  • 毕业论文答辩PPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2024 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。