如何从Excelexcel函数公式大全中提取数值?

专栏/如何用Excel公式提取非空单元格的数值?最简单的方法在这!2021年09月14日 00:32--浏览 ·
--喜欢 ·
--评论昨天有个小伙伴私信我,如何用公式从某个数据区域中获取非空单元格的数值。他的问题大致是这样子的:下图中A1:A10是数据区域,从这个数据区域中用公式拿到非空单元格的数据,也就是C1:C7单元格区域显示的结果。该如何实现呢?解决该问题的思路并不难,我们要先把A1:A10单元格区域中非空单元格的行号找到并获取到,然后使用INDEX函数取出对应的值即可。具体操作步骤如下:选中E1:E10单元格 -- 在编辑栏中输入公式“=IF(A1:A10<>"",ROW(A1:A10))” -- 按“Ctrl+Shift+Enter”回车。公式的意思是:将A1:A10单元格区域的值与空值进行比较,如果不为空,则显示相应非空单元格数值所在的行号,如果为空,则显示为FALSE。选中F1:F10单元格 -- 在编辑栏中输入公式“=SMALL(E1:E10,ROW(A1:A10))” -- 按“Ctrl+Shift+Enter”回车。公式的意思是:ROW(A1:A10)公式得到一个行号的数组{1;2;3;4;5;6;7;8;9;10},用SMALL函数在E1:E10数据区域中按从小到大的顺序取出第1至第10小的值。选中G1:G10单元格 -- 在编辑栏中输入公式“=INDEX(A1:A10,F1:F10)” -- 按“Ctrl+Shift+Enter”回车。公式的意思是:INDEX函数分别取出A1:A10数据区域中第1、3、4、6、7、10行的数据。从上图可以看到,单元格为空的数据取出来的值为错误值,所以要把这些错误值给屏蔽。选中H1:H10单元格 -- 在编辑栏中输入公式“=IFERROR(G1:G10,"")” -- 按“Ctrl+Shift+Enter”回车。公式的意思是:如果G1:G10数据局区域中有错误值,则返回空值,否则返回公式的结果。选中C1:C6单元格 -- 在编辑栏中输入公式“=IFERROR(INDEX(A1:A10,SMALL(IF(A1:A10<>"",ROW(A1:A10)),ROW(A1:A10))),"")” -- 按“Ctrl+Shift+Enter”回车即可。喜欢我的文章,三连走一波,您的不断支持就是小编继续写出优质教程的动力!关注我,每天都可以让你学到一个实用的办公技巧,提升你的办公效率!目录------6}

0
分享至
用微信扫码二维码分享至好友和朋友圈点击蓝字【秋叶 Excel】发送【礼包】免费领办公神器、Office 模板和免商字体!本文作者:小爽本文编辑:竺兰大家好,我是搞 Excel 的小爽鸭~在日常生活中,我们在各种群里,接龙采购信息的时候,有可能会出现文本与数字混合的数据 ,如下图:所以,我们需要把数量和名称都提取出来,方便我们进行查看。今天,我来给大家介绍三种方法,跟随我的步伐一起看看吧。PS.本文使用的版本是 Office 2021,推荐使用 Office 2016 及以上版本。函数方法提取数字在 Excel 中,并没有可以直接提取数字的函数,不过我们可以先将数据拆成一个个字符,然后通过数字的特性,将文本变成错误值,从而达到提取数字的目的。① 第一步 : 拆=MID(A2,ROW($1:$10),1)PS : 由于公式是个数组公式,也就是除 Office 2021,Office 365 之外的版本,包括 WPS,需要先选区域,再用数组三键【Ctrl+Shift+Enter】进行运算,下面同理。利用 mid 函数,将字符串拆成一个个字符。= MID (字符串,起始位置,提取数量)row(1:10) 可以生成 1 到 10 的序列,作为 mid 函数的第二参数,依次提取 1 个字符。PS.10 主要是看最长的字符串长度,大于等于这个数即可。② 第二步 : 转=1*MID(A2,ROW($1:$10),1)=1*①由于 1*文本数字,返回数字;1*文本字符,返回错误值。所以我们利用这个规律,将数字和文本字符分开。③ 第三步:变=IFERROR(1*MID(A2,ROW($1:$10),1),"")=IFERROR(②,"")利用 iferror 函数将错误值返回为空。④ 第四步:合=CONCAT(IFERROR(1*MID(A2,ROW($1:$10),1),""))=CONCAT(③)利用 concat 函数,将所有数字进行合并。提取文本前面已经提取了数字,那么对文本,我们只要将拆后的字符串,把数字替换掉,再合并就可以直接搞定。① 第一步 : 拆=MID(A2,ROW($1:$10),1)② 第二步 : 得这一步目的主要是得到去除文本后,拆分的数字。=IFERROR(1*MID(A2,ROW($1:$10),1),"")③ 第三步 : 换=SUBSTITUTE(MID(A2,ROW($1:$10),1),IFERROR(1*MID(A2,ROW($1:$10),1),""),"")=SUBSTITUTE(①,②,"")文本拆分后的数据,利用 substitute 函数,将提取的数字(旧字符),替换为空(新字符),即是去掉数字。④ 第四步:合=CONCAT(SUBSTITUTE(MID(A2,ROW($1:$10),1),IFERROR(1*MID(A2,ROW($1:$10),1),""),""))=CONCAT(③)利用 concat 函数将数据进行合并。就这样,我们就已经把数字和名称都提取出来了。前面,我们的 10 是固定的,如果不想固定,可以使用 len 函数获取长度,然后进行拼接,利用 indirect 函数进行返回。=CONCAT(IFERROR(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1),""))还有就是如果我们已经提取了数字,其实直接用 substitute 函数也可以。PQ 法不同于 Excel 函数公式,PQ 中就有提取指定字符的 M 函数。思路跟上面的函数方法是一样的,我们可以先提取数字(Text.Select),然后将字符串中的数字替换为空(Text.Repalce),即可得到文本。首先,肯定是需要先将数据导入到 PQ 编辑器中。全选数据,在【数据】选项卡下,单击【来自表格/区域】,单击【确定】按钮。提取数字在【添加列】选项卡中,选择【自定义列】。新列名:数量自定义列公式:Text.Select([数据],{"0".."9"})单击【确定】按钮后,如下图,我们可以看到数字已经提取出来了。Select 的意思是挑选,顾名思义, Text.Select 函数就是从文本字符中挑选出指定的内容。=Text.Select (文本, {要提取的字符}) =Text.Select([数据],{"0".."9"}){"0".."9"} 表示从 0 到 9 的数值,所以该公式的意思是:从文本中,挑选包含从 0 到 9 的数值。Text.Select 在提取数字上,非常的方便,小伙伴 get 到了嘛~提取文本在【添加列】选项卡中,选择【自定义列】。新列名:名称自定义列公式:Text.Replace([数据],[数量],"")单击【确定】按钮后,如下图,我们可以看到数字已经提取出来了。Text.Replace 跟函数中的 substitute 函数类似,都是将文本中的旧字符替换为新字符。=Text.Replace(文本,旧字符,新字符)=Text.Replace([数据],[数量],"")所以这个公式大家应该也不难理解,当然也不是只有这一种做法。公式 plus前面写了一大堆,其实如果你经常使用,也可以把这个编写成一个自定义公式。如果你有安装公式 plus,只需要一个 P 就可以搞定!提取数字=P_Num(A2)提取文本=P_Cn(A2)So Easy~Excel 操作简单、应用广泛,如果你想系统学习,让你的工作能力有机会增强 10 倍!那就赶紧点击下方小程序,加入《3 天 Excel 集训营》吧!现在报名,只需 1 元!还送35 个常见函数使用说明……速戳下方小程序加入《3 天 Excel 集训营》!最后的话本文写的其实并不难,主要是让大家学习一个思路。对于函数做法,我们需要懂得:拆:mid+row,可以字符串拆成一个个字符转:1*或者--等,可以将文本数字转为数字,文本变成错误值。换:提取数值后,substitute 可以将数字替换掉,从而获得文本。对于 pq 做法,我们需要掌握两个 M 函数:Text.Select可以提取指定字符,超级实用。Text.Replace类似于 substitute 函数,可以将旧字符替换为新字符。好了,本文就到这里结束啦。以上内容包含广告特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.相关推荐热点推荐
2024-05-22 17:46:44
}

我要回帖

更多关于 满足三个条件的vlookup 的文章

更多推荐

版权声明:文章内容来源于网络,版权归原作者所有,如有侵权请点击这里与我们联系,我们将及时删除。

点击添加站长微信