未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
EXCEL中如何使用VLOOKUP函数提取单元格字符串中的数值

2018-03-05

如何使用VLOOKUP提取单元格字符串中的数值

在用EXCEL进行数据处理时,有时会遇到下面的数据,单元格有数字、文字和字符,还要对数字进行计算,如果数据量少还可以逐渐复制出来处理,但如果有成千上万行数据需要处理,就要用其他办法了。

本文今天介绍了一种方法:如何用VLOOKUP来提取字符串中的数字。

在总价单元格边上增加两列辅助列,用来提取单价和数量:

然后在G2单元格中输入数组公式:

=VLOOKUP(9^9,MID(C2,MIN(IF(ISNUMBER(--MID(C2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2),按CTRL + SHIFT + ENTER结束输入。

这是一个多层函数嵌套的数组公式,使用了VLOOKUP、MID、IF、MIN、ISNUMBER、ROW等函数,下面从VLOOKUP的4个参数来分析一下这个公式的机理。

(1) 参数1:9^9,9^9 = 387420489,这是一个较大的数值,用来进行查找、数值比较等,当然也可以用其他比较大的数值来代替9^9。

(2) 参数2:MID(C2,MIN(IF(ISNUMBER(--MID(C2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},这个是查找区域。

其中使用了以下函数:

MID()进行文本提取;

MIN()表示最小值,从小到大排序;

ISNUMBWR()用来判断值是否为数值;

ROW()返回行号,$1:$99表示从第1行到第99行;

'- -'这代表恢复到正常格式,如果是日期,就变成日期格式,如果是数值,就变成数值格式。

参数2构造了一个2列、99行的表格,用数组表示出来如下图所示。

可以利用F9来逐个查看参数中的返回值,这样更有利于理解函数原理。

(3) 参数3:2,即查找第2列

(4) 参数4:省略,参数4如果省略,默认为模糊查找,返回一个最接近于9^9的值,其实也是就字符串中的数值(本例中的数值均小于9^9)。

将G2向下、向右进行拖拉填充,提取相应的数值,就可以方便计算出物品的总价了。

公式有点复杂,也可以收藏起来直接套用。

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:华夏虫  > Office
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
EXCEL中如何使用VLOOKUP函数提取单元格字符串中的数值?
编制完整的会计科目
详解vlookup函数中{1,0}的使用方法,看完后给同事讲讲,秒变大神
【Excel函数篇】你会提取单元格中的手机号吗?
Excel中row函数的使用教程步骤图(2)
EXCEL常用函数大全4[51自学网园地]
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!