excel小技巧,10秒提取3000行有效数据!快快来get吧
发布时间:2022-03-21 18:15:49
问题来自于一位群友的求助,大家请看图(源数据较多,为方便讲解,省略为下图):
数据源在A列,包含了很多项信息,现在需要从中提取出容值、封装和耐压三项数据,不难发现,需要提取的数据具有一定的规律性,分别是数据源的第二、三、四个逗号之后的数据。当我们遇到问题的时候,找到规律是解决问题的关键。现在规律找到了,因此解决方法也就有了。这里有三种方法,从最简单的快捷键操作到经典的吃遍天下的公式都有,以下分别进行介绍。
1.快速填充法(Ctrl+ E)
评价:★★★★★
优势:简单、易用。
劣势:还没有发现。
操作要点
(1)在B2单元格输入0402时,要先输入一个单引号,或者把单元格修改为文本格式再输入。
(2)只输入一个数据可能无法通过Ctrl+E得到正确结果,这时候连续输入两个数据就可以了。就本例而言,Ctrl+ E略微显得有些麻烦,因此再介绍一种用分列的处理方法。
2.分列法
评价:★★★★☆
优势:简单、易用。
劣势:数据量大了后工作量还是比较重。
操作要点:
(1)分列过程中使用逗号进行分隔;
(2)需要跳过不导入的列;
(3)对容值这列数据设置为文本格式;
(4)手工指定数据存放的目标区域。
相比第一个方法来说,使用分列就简单了许多,同时通过这个例子,大家也可以对分列这个强大的功能有了深入的了解。
使用分列虽然比较方便,但如果经常要处理这类数据的话,操作量也是蛮大的,最后我们再来分享一个公式的做法。
3. TRIM-MID-SUBSTITUTE-REPT组合公式法
使用公式:
=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(B1)*99,99))
右拉下拉即可得到所需的结果。
评价:★★★★☆
优势:快速,对付大量数据尤其实用,并且可以修改参数用于更复杂的字符提取。
劣势:公式不好记。
公式解析:
这个公式里用到了五个函数,其中有我们比较熟悉的MID和COLUMN,也有我们不太常用的TRIM、SUBSTITUTE和REPT函数。下面简单来解释一下这个公式的思路。
公式的核心部分是SUBSTITUTE($A2,",",REPT(" ",99)),这部分的作用是进行替换。
SUBSTITUTE函数格式为:
SUBSTITUTE(在哪里替换,替换什么,换成什么,换第几个)
例如:
公式=SUBSTITUTE($A2,",","-",3)的效果就是把A2单元格的第3个逗号换成-号。
当省略第四参数的时候,代表逗号全部替换,如图:
本例中是把A2中的逗号换成了REPT(" ",99),也就是99个空格。
REPT函数的格式为:
REPT(要重复的字符,重复次数)
例如:
REPT(“★”,5),就是将★重复五次。
至于公式中为什么要用99个空格,完全是一种套路,继续看完公式的其他部分或许就理解了。
使用SUBSTITUTE得到的数据还需要用MID函数来进行提取。MID函数大家应该比较熟悉了,基本格式为:MID(要提取的数据,从什么位置开始取,取几个字)。在本例中要提取的数据就是SUBSTITUTE(),而要提取的容值的位置原本是在第2个逗号之后,由于我们把逗号换成了99个空格,要提取的位置前面至少有两组空格也就是2*99个字符;相应封装的提取的位置是3*99,耐压的是4*99。采用公式右拉,所以这里用COLUMN(B1)*99作为提取位置。MID的最后一个参数是要取几个字符,为了保险起见,统一提取99个字。
也就是说,经过MID(SUBSTITUTE(),COLUMN(B1)*99,99)这部分公式运算后,得到的结果是我们实际需要的容值数据包含在前后空格中。为了便于大家理解,临时将空格换成-,可以直观地看出效果:
我们肯定不希望得到的结果中包含有大量无用的空格,因此在最外层套一个TRIM就可以去掉这些空格。TRIM函数只有一个参数,功能就是去掉字符串中多余的空格。
excel小技巧,10秒提取3000行有效数据!快快来get吧的下载地址:
猜你喜欢
- 当我们编辑数据文字量巨大的文档的时候,我们需要把握整个文档的脉络,对整篇文档进行修改。这就需要我们能够快速定位到需要修改的地方,也就是运用文
- 我们在excel当中,想要对比两个相似的表格数据,应该怎么做呢?下面小编就教大家设置Excel表格将两数据对比的方法吧,欢迎大家来到学习。e
- Excel折线图怎么做?使用折线图易于显示数据变化趋势以及变化幅度,可以直观地反映这种变化以及各组之间的差别。本期教程小编将为大家分享Exc
- 一、打开表格,选中你要制作中迷你图的数据源,之后单击“插入——迷你图——折线图”;二、在弹出来的对话框中,将位置范围的位置定义到你要显示迷你
- 用户不仅可以在Word 2007文档中为段落设置纯色底纹,还可以为段落设置图案底纹,使设置底纹的段落更美观。今天,小编就教大家在Word中进
- 下面给大家分享Excel表格中批量取消超链接的方法,一起来学习吧!本次演示版本使用Excel2003①单击菜单栏中的“工具”按钮,选择“宏”
- iOS 16 相册「重复项目」功能能将自动检测用户库中的任何重复照片和视频,并将它们整理到重复项目相册中以供检查,让用户可以选择合并重复集和
- 我们可以为文档插入合并域内容,从而将收件人列表中的相关字段信息自动插入到文档中,完成邮件合并操作,我们在使用邮件合并功能制作文档时,可以查看
- word、excel里给折线图加上坐标轴注释操作方法相同。(以下用word做详细操作步骤说明)方法如下:1、打开已插入拆线图的word;2、
- 在EXCEL中录入数据的时候经常需要用到合并单元格这个功能,合并单元格功能在Excel中是最基本的一个功能,如果还没学会改如何合并的朋友不妨
- 很多时候我们需要筛选出来excel表格中最大或者最小的数据,若要在excel2007中筛选出最大或最小的数据,可以通过下面的操作步骤实现。1
- 在Excel 2003中,如果某个工作簿中使用了自定义颜色设置,而其他工作簿需要使用这些自定义颜色时,可以在其他工作簿中直接调用,无需重新设
- excel表格的强大功能,给办公带来了很多便捷,但是有很多强大的功能,其实我们都还不会用,如在Excel单元格中如何用斜线分割填写,下面小编
- 简介:Word中的浮动工具栏是指,当你选中一段文字后,旁边会自动浮现一个工具栏。如图:这就是浮动工具栏问:浮动工具栏有什么作用?答:快速更改
- LEFT、RIGHT、MID这三个可以理解为按字数提取内容,LEFTB、RIGHTB、MIDB这三个可以理解为是按字节提取内容。他们参数都是
- Excel中的重复数据具体该如何去掉呢?接下来是小编为大家带来的excel2003去掉重复数据的方法,供大家参考。excel2003去掉重复
- Win10注册表被删了怎么办?注册表程序对我们电脑来说是非常重要的,有用户不小心删除了一些注册表,导致电脑运行某些程序的时候,出现了问题。那
- Word能够说是我们日常工作必不可少的办公软件之一,而我们在使用该软件的时候难免会遇到不熟悉的操作,例如有的用户不清楚如何为页眉添加双划线,
- 插图命令组图片使用图片命令,可在工作表中插入指定的图片。联机图片使用联机图片命令,可从互联网或其他地方插入图片。形状使用形状命令,可从 Ex
- Excel快速复制备份工作表。我们在处理Excel表格里的一些重要数据时,最好要先把数据备份一下,以免我们不小心误操作到什么,有备无患。下面