|
动态区域:我理解的动态区域分两种,一种是不断更新的区域,但最基本的起始位置不变,只是数据在不断更新.另一种是完全动态的,起始位置可以发生变化,区域的大小可以发生变化.
对于第一种区域而言,可以算是静态区域的变异,基本使用方法和静态没有区别.不过有时也有一点不同,例如我需要调用的数据是你更新到那,就调用到那,这是需要一个定义的区域.这时需要使用OFFSET函数了.
例: 数据有效性大家基本都用过了,里面提供可以输入选择项目[数据有效性-序列],正常的时候,可以通过选择一部分数据或单元格作为备选项,此时输入信息时就可以通过选择的方法来实现,但是如果备选序列的数据是一个持续更新, 在B2:B5中输入,缺陷A,缺陷B….缺陷D,选择单元格A1, 设置数据有效性,备选项就是缺陷的名称,由于缺陷名称可能持续更新,此时就可以设置一个动态的区域.使用定义名称可以实现. 插入 – 名称 – 定义,输入定义的名称比如 defectname,然后在下面输入公式: =offset(Sheet2!$B$1,1,0,counta(Sheet2!$B$2:$B$20),1)
Counta函数用于查找非空单元格的,就是数一下有多少备选项目. 选择A1, 数据 – 数据有效性 – 序列 – 输入刚刚定义的名称
此时就实现了有固定起始位置的动态区域的引用
第二种就是完成的动态区域引用了. 在这里介绍几种引用完全动态区域的方法 A:Indirect+Address函数, B:Index函数 C:Offset函数 方法A:address函数要用来可以根据提供的行数和列数显示相应的单元格,和Indirect一起使用可以用于动态引用. 例:需要对A1:E10范围内数据,根据不同需要进行动态求和,
=SUM(INDIRECT(ADDRESS(H5,2)):INDIRECT(ADDRESS(H6,5))) [大家不要纠结在认为这个例子还有更简单的解法,这里只是介绍INDIRECT+ADDRESS的使用方法而已]
方法B:使用INDEX函数实现单元格区域的显示, 输入函数: =SUM(INDEX(A1:E10,H5,2):INDEX(A1:E10,H6,5)),例如sum(B2:E4),INDEX函数在这里的作用是代替B2和E4,待用的方法就是让INDEX函数返回的结果就是B2,E4单元格.
方法C:使用OFFSET函数将这个区域调出来,, 输入函数: =SUM(OFFSET(A1,H5-1,1,H6-H5+1,4)),OFFSET的函数结果就是B2:E4区域的数据.
这里关于INDIRECT,INDEX,OFFSET使用方法没有介绍,大家可以到网上查资料,以后也会在博客中更新.
其实动态引用区域,在很多复杂一点的数据运算时会用到.这里只是给出几种我常用的方法.动态区域可以作为一个区域参数应用到大多数的函数中.但是就是COUNTIF函数不能使用动态区域(即内存数据),使用的时候需要注意下. 2011-10-20 初始更新 |
|
|
