excel函数公式中9E+307的用法详解

来源:互联网 时间:2016-01-03

如果EXCEL电子表格的数据比较复杂,所使用的行列比较多,那么,如何通过公式获取每一行或每一列中,有数据的单元格的最后一个单元格呢?

先看下表。

excel函数公式中9E+307的用法详解

比如第一行,有效数据的最后一个单元格是B2,而第五行,有效数据的最后一个单元格是F5。

再比如,A列的有效数据的最后一个单元格是A5,而C列,有效数据的最后一个单元格是C2。

诸如此类,在不规则数据的电子表格中,我们如何通过公式获取行列中的有效数据的最后一个单元格的值呢?

借助9E+307这个数,再加上LOOKUP函数,即可处理此类问题。

一、实例公式如下

A、获取第一行有效数据最后一个单元格

公式为:=LOOKUP(9E+307,A1:F1)

由于我们知道,最大使用行到F1,所以,上述的查找范围是从A1:F1;当然,您也可以修改得再大点,比如,A1:Z1,结果同样是正确的。

B、获取第二行有效数据最后一个单元格

公式为:=LOOKUP(9E+307,A2:Z2)

获取行的公式,大体类似,下面就不再介绍了。

C、获取第一列有效数据最后一个单元格

公式为:=LOOKUP(9E+307,A:A)

C、获取第四列有效数据最后一个单元格

公式为:=LOOKUP(9E+307,D:D)

获取其它列有效数据最后一个单元格公式都是类似的,不再介绍。

知识扩展:

为什么我们要借助9E+307这个数呢?到底有何用意?

9E+307用来表示EXCEL中最大的表中不可能出现的数,因而返回的是第一行列中最末一个单元格的数据。

这是使用科学记数法表示的9*10^307,是接近Excel允许键入的最大数值9.99999999999999E+307的一个数。

由于9E+307是个很大的数字,一般不会出现,其实只是一个代表性的数字。

运用技巧:

1、作为一个大的数值,9E+307常被用来作为查找、数值比较等,比如常用=LOOKUP(9E+307,B:B)查找B列最后一个数值;

2、严格来说,9E+307的使用是不规范的,比如如果A列刚好有数值9.9E+307,上面这个公式就不正确了,但实际的数据很少出现这种情况。此外,在公式中使用9E+307时,可能会被Excel自动更正为E9+307,而导致计算错误。

比如说,公式:=LOOKUP(9E+307,A:A),查找的是A列有效数据最后一个单元格

如果您把公式改为::=LOOKUP(100,A:A)

那么,由于从上图的表中,我们都看到了,A5单元格的123,比公式中的100还要大,所以,该公式=LOOKUP(100,A:A)返回的结果是3,而不是123,所以,尽管公式未提示错误,但实际上并非我们所需要的正确结果。所以,在使用的过程中,常常需要9E+307作为对比,用它来当作一个很少出错误或者不会出错误的参照对象。

相关阅读:
Top