专栏/如何用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
}