图7.85中的C列包含了数值和单位,现要求在E列提取其数字,忽略单位,是否有办法一次性提取成功?

Excel 能否提取单元格中位于左边的所有数字?-Excel22

解题步骤

提取左边的数字有多种方法,每种方法各有所长。使用基础操作可以完成,使用公式也可以完成,其中公式又分多种思路。本例讲述Lookup+Left组合的思路,具体操作步骤如下:

1.选择E2,然后输入以下公式:

Excel 能否提取单元格中位于左边的所有数字?-Excel22

2.双击E2的公式,使其自动填充到E11单元格,此时计算结果如图7.86所示。

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.86 提取每个单元格左边的数字

知识扩展

1.ROW函数用于计算单元格的行号,它的参数必须是单元格。

=ROW(G1)——结果为1,G1单元格的行号为1;

=ROW(C65536)——结果为65536;

=ROW(G1:D3)——当以普通公式的形式输入公式时,它的结果为1;当以数组公式的形式输入公式时,它的结果为{1,2,3},即选择纵向3个连续的单元格后输入公式,并按<Ctrl+Shift+Enter>键结束,若在单个单元格中输入公式只能得到G1的行号1。

由于Excel的计算精度是15位,因此本例采用ROW($1:$15)作为LEFT函数的参数提取1到15位字符,这是最通用的做法。

2.LEFT函数的功能是提取字符串左边若干位字符,其语法如下:

Excel 能否提取单元格中位于左边的所有数字?-Excel22

=LEFT("中华人民共和国",3)——计算结果为“中华人”,提取左边3位;

=LEFT(A1,ROW($1:$15)))——由于表达式“ROW($1:$15)”可以生成1到15的自然数序列,因此此公式可以提取C2单元格的左边1~15位字符。当选择纵向15个连续的单元格后输入公式,并按<Ctrl+Shift+Enter>组合键结束时可以产生15个运算结果,效果如图7.87所示。

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.87 逐一提取左边1~15位字符

3.LOOKUP函数功能相当强大,用法也相当复杂,要完整地介绍 LOOKUP函数的用法需要10页以上。本例只用到数组形式的模糊搜索,因此仅介绍此种情况下的LOOKUP用法。

数组形式、模糊搜索条件下的LOOKUP语法如下:

Excel 能否提取单元格中位于左边的所有数字?-Excel22

第一参数是搜索对象,可以是一个字符串、一个数字,也可以是一个单元格。第二参数是一个数组或一个区域(区域的值本质上就是一个数组),LOOKUP 会在第二参数中搜索第一参数的值,如果找不到第一参数的值,那么继续搜索比它小的最大值,找到后返回该值。

使用 lOOKUP 函数时,要求其第二参数的数据源升序排序,否则查找结果可能与预期不一致。

在图7.88中,公式“=LOOKUP(15,A1:A5)”的含义是在A1:A5中查找15,由于A1:A5中刚好有一个15,因此公式结果为15;图7.89中公式“=LOOKUP(40,A1:A5)”的含义是在A1:A5中查找40,由于找不到40,继续查找比40小的最大值,数据35符合条件,因此公式的返回值是35。

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.88 查找15并返回15

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.89 查找40,找不到时返回比它小的最大值

4.当使用LOOKUP函数执行模糊搜索(找不到目标值就继续查找比目标小的最大值)时,为了提升公式的通用性,通常会将LOOKUP的查找目标设置为最大值10的15次方(再大就没有必要了,超出了Excel的计算精度),然后在实际搜索过程中会搜索比它小的最大值。下面的例子可以展示LOOKUP搭配LEFT函数执行模糊搜索的过程。

图7.90中展示了LEFT函数从642578中逐一提取1位、2位、3位、4位、5位和6位字符时的运算过程和结果,运算结果包含6个值;图7.91则展示LOOKUP函数从这个数值中查找10^15的过程,由于找不到目标值,因此从数组中提取一个小于目标值的最大值642578作为最终结果。

Left函数的运算结果总是文本形式的,因此要把它们当作数值去参与运算时,需要在右边添加表达式“*1”,从而将文本型数字转换成数值。

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.90 LEFT函数逐一提取左边N位字符

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.91 使用LOOKUP从数组中搜索10^15

5.公式“=LOOKUP(10^15,LEFT(C2,ROW($1:$15))*1)”的运算过程比较复杂,可以通过图7.92来理解公式。

Excel 能否提取单元格中位于左边的所有数字?-Excel22

图7.92 图示LOOKUP+LEFT函数的运算过程