execl常用的微函式系列

Offset函式是一個常見的引用類函式，作用與Indirect函式相似，與Indirect函式所不同的在於，Offset函式是基於目標基準區域的偏移引用。

Offset函式首先需要指定一個基準區域，然後通過行偏移量和列偏移量來確定實際的引用位置。偏移量是一個向量值，以正負符號來表示偏移方向，以數值大小來表示偏移距離。

Offset函式的第三引數和第四引數分別表示引用區域的高度和寬度，兩個引數均可省略，在省略的情況下預設與基準區域的維度尺寸相一致。高度和寬度的引數值也可以使用負數，表示負方向(向上/向左)上的維度擴充套件。

　　一、Offset函式

Offset的常見應用場景包括：

1，與Match函式相結合的查詢，可以突破Vlookup、Hlookup等函式的單一方向性的限制

2，可以動態更新的區域引用(常用於生成下拉選單的資料有效性序列)

3，通過數學構造，部分引用某個區域，比如間隔引用等等(例如生成工資單)

4，三維引用

當Offset函式的行列偏移或高度寬度引數使用陣列作為引數值時，就會產生三維引用甚至更多維度的引用。例如=OFFSET(A1:B1,,,{1;2;3;4;5})公式產生如下圖所示的引用：

Indirect函式是比較常見的引用類函式，與其功能相仿的函式包括Offset函式、Index函式等等。與Offset函式所不同的在於，Offset函式是基於目標基準位置的偏移引用，而Indirect函式則是通過構造單元格地址的文字書寫方式來對單元格區域直接進行引用。

在Excel中單元格的引用方式包括A1樣式和R1C1樣式，因此Indirect函式在構造引用字串時，也可以使用上述兩種樣式的文字字串，但需要在第二引數中使用不同的引數值進行標識區別。

A1樣式和R1C1樣式兩種不同引用方式的來源是由於單元格地址表示方式的不同：如果用字母來表示列標，數字來表示行號，就是A1引用樣式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用樣式。

由於可以構造文字形式的單元格引用地址，因此利用文字連線符&就可以構造“常量”+“變數”、”靜態“+”動態“相結合的單元格引用方式。

例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是靜態地址，其中的列標A和行號2都是常量，而COUNTA(D:D)部分形成了第二個行號的動態引用。整個公式的引用方式就等同於“A2:An”，其中的n是變數，由D列的非空單元格數量所決定。

基於這樣的特性，Indirect函式常見的用途包括：

1，引用地址事先未知，需要通過其他運算得到部分引用元素的引用

2，可以動態更新的區域引用(常用於生成下拉選單的資料有效性序列)

3，表名稱有數字規律的多表引用(三維引用)

4，構造生成指定的數字序列或陣列

5，與Text函式相結合，通過一組數值生成相應的一組R1C1式引用

Match函式是Excel中十分常用的匹配查詢類函式，其作用和地位與Vlookup、Lookup等查詢類函式相近。與Lookup類函式有所區別的地方在於：Match函式返回的結果是匹配元素的所在位置，而非匹配元素本身。

此外，Match函式還支援包含“*”、“?”等萬用字元的模糊查詢，但不能區分英文字母的大小寫。如果要在目標陣列中進行區分大小寫的查詢，可結合Exact函式來組合公式，例如要在陣列{"EXCEL","book","excel","SHEET"}查詢"excel"的位置，可以使用公式：

=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)

返回結果為3，不會受陣列中第一個元素"EXCEL"的干擾。

需要注意的是，Exact函式不支援萬用字元，因此不能通過上述方法同時進行區分大小寫和包含萬用字元的模糊查詢。

當目標陣列中包含多個與查詢資料相匹配的元素時，Match函式只返回其中第一個匹配元素的所在位置，因此Match函式通常只用於唯一性的查詢。但利用這一特性，Match函式還可以用來對陣列中的非重複資料進行統計，例如統計非重複資料的個數，返回非重複資料的列表等等。

　　二、Indirect函式

Indirect函式是比較常見的引用類函式，與其功能相仿的函式包括Offset函式、Index函式等等。與Offset函式所不同的在於，Offset函式是基於目標基準位置的偏移引用，而Indirect函式則是通過構造單元格地址的`文字書寫方式來對單元格區域直接進行引用。

在Excel中單元格的引用方式包括A1樣式和R1C1樣式，因此Indirect函式在構造引用字串時，也可以使用上述兩種樣式的文字字串，但需要在第二引數中使用不同的引數值進行標識區別。

A1樣式和R1C1樣式兩種不同引用方式的來源是由於單元格地址表示方式的不同：如果用字母來表示列標，數字來表示行號，就是A1引用樣式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用樣式。

由於可以構造文字形式的單元格引用地址，因此利用文字連線符&就可以構造“常量”+“變數”、”靜態“+”動態“相結合的單元格引用方式。

例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是靜態地址，其中的列標A和行號2都是常量，而COUNTA(D:D)部分形成了第二個行號的動態引用。整個公式的引用方式就等同於“A2:An”，其中的n是變數，由D列的非空單元格數量所決定。

基於這樣的特性，Indirect函式常見的用途包括：

1，引用地址事先未知，需要通過其他運算得到部分引用元素的引用

2，可以動態更新的區域引用(常用於生成下拉選單的資料有效性序列)

3，表名稱有數字規律的多表引用(三維引用)

4，構造生成指定的數字序列或陣列

5，與Text函式相結合，通過一組數值生成相應的一組R1C1式引用

　　三、Match函式

Match函式是Excel中十分常用的匹配查詢類函式，其作用和地位與Vlookup、Lookup等查詢類函式相近。與Lookup類函式有所區別的地方在於：Match函式返回的結果是匹配元素的所在位置，而非匹配元素本身。

此外，Match函式還支援包含“*”、“?”等萬用字元的模糊查詢，但不能區分英文字母的大小寫。如果要在目標陣列中進行區分大小寫的查詢，可結合Exact函式來組合公式，例如要在陣列{"EXCEL","book","excel","SHEET"}查詢"excel"的位置，可以使用公式：

=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)

返回結果為3，不會受陣列中第一個元素"EXCEL"的干擾。

需要注意的是，Exact函式不支援萬用字元，因此不能通過上述方法同時進行區分大小寫和包含萬用字元的模糊查詢。

當目標陣列中包含多個與查詢資料相匹配的元素時，Match函式只返回其中第一個匹配元素的所在位置，因此Match函式通常只用於唯一性的查詢。但利用這一特性，Match函式還可以用來對陣列中的非重複資料進行統計，例如統計非重複資料的個數，返回非重複資料的列表等等。