關於 Excel中動態區域的引用,表示方法

  動態區域:我理解的動態區域分兩種,一種是不斷更新的區域,但最基本的起始位置不變,只是數據在不斷更新.另一種是完全動態的,起始位置可以發生變化,區域的大小可以發生變化.

對於第一種區域而言,可以算是靜態區域的變異,基本使用方法和靜態沒有區別.不過有時也有一點不同,例如我需要調用的數據是你更新到那,就調用到那,這是需要一個定義的區域.這時需要使用OFFSET函數了.

例: 數據有效性大家基本都用過了,裡面提供可以輸入選擇項目[數據有效性-序列],正常的時候,可以通過選擇一部分數據或單元格作為備選項,此時輸入信息時就可以通過選擇的方法來做到,但是如果備選序列的數據是一個持續更新,

在B2:B5中輸入,缺陷A,缺陷B….缺陷D,選擇單元格A1, 設置數據有效性,備選項就是缺陷的名稱,由於缺陷名稱可能持續更新,此時就可以設置一個動態的區域.使用定義名稱可以做到. 插入 – 名稱 – 定義,輸入定義的名稱比如 defectname,然後在下面輸入公式:

=offset(Sheet2!$B$1,1,0,counta(Sheet2!$B$2:$B$20),1)

關於 Excel中動態區域的引用,表示方法

Counta函數用於查找非空單元格的,就是數一下有多少備選項目.

選擇A1, 數據 – 數據有效性 – 序列 – 輸入剛剛定義的名稱

此時就做到了有固定起始位置的動態區域的引用

關於 Excel中動態區域的引用,表示方法

第二種就是完成的動態區域引用了.

在這裡介紹幾種引用完全動態區域的方法

A:Indirect+Address函數,

B:Index函數

C:Offset函數

方法A:address函數要用來可以根據提供的行數和列數顯示相應的單元格,和Indirect一起使用可以用於動態引用.

例:需要對A1:E10範圍內數據,根據不同需要進行動態求和,

關於 Excel中動態區域的引用,表示方法

=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 初始更新