execl常用的微函數(shù)系列
Offset函數(shù)是一個(gè)常見(jiàn)的引用類(lèi)函數(shù)，作用與Indirect函數(shù)相似，與Indirect函數(shù)所不同的在于，Offset函數(shù)是基于目標(biāo)基準(zhǔn)區(qū)域的偏移引用。
Offset函數(shù)首先需要指定一個(gè)基準(zhǔn)區(qū)域，然后通過(guò)行偏移量和列偏移量來(lái)確定實(shí)際的引用位置。偏移量是一個(gè)矢量值，以正負(fù)符號(hào)來(lái)表示偏移方向，以數(shù)值大小來(lái)表示偏移距離。
Offset函數(shù)的第三參數(shù)和第四參數(shù)分別表示引用區(qū)域的高度和寬度，兩個(gè)參數(shù)均可省略，在省略的情況下默認(rèn)與基準(zhǔn)區(qū)域的維度尺寸相一致。高度和寬度的參數(shù)值也可以使用負(fù)數(shù)，表示負(fù)方向(向上/向左)上的維度擴(kuò)展。
一、Offset函數(shù)
Offset的常見(jiàn)應(yīng)用場(chǎng)景包括：
1，與Match函數(shù)相結(jié)合的查詢，可以突破Vlookup、Hlookup等函數(shù)的單一方向性的限制
2，可以動(dòng)態(tài)更新的區(qū)域引用(常用于生成下拉菜單的數(shù)據(jù)有效性序列)
3，通過(guò)數(shù)學(xué)構(gòu)造，部分引用某個(gè)區(qū)域，比如間隔引用等等(例如生成工資單)
4，三維引用
當(dāng)Offset函數(shù)的行列偏移或高度寬度參數(shù)使用數(shù)組作為參數(shù)值時(shí)，就會(huì)產(chǎn)生三維引用甚至更多維度的引用。例如=OFFSET(A1:B1,,,{1;2;3;4;5})公式產(chǎn)生如下圖所示的引用：
Indirect函數(shù)是比較常見(jiàn)的引用類(lèi)函數(shù)，與其功能相仿的函數(shù)包括Offset函數(shù)、Index函數(shù)等等。與Offset函數(shù)所不同的在于，Offset函數(shù)是基于目標(biāo)基準(zhǔn)位置的偏移引用，而Indirect函數(shù)則是通過(guò)構(gòu)造單元格地址的文本書(shū)寫(xiě)方式來(lái)對(duì)單元格區(qū)域直接進(jìn)行引用。
在Excel中單元格的引用方式包括A1樣式和R1C1樣式，因此Indirect函數(shù)在構(gòu)造引用字符串時(shí)，也可以使用上述兩種樣式的文本字符串，但需要在第二參數(shù)中使用不同的參數(shù)值進(jìn)行標(biāo)識(shí)區(qū)別。
A1樣式和R1C1樣式兩種不同引用方式的來(lái)源是由于單元格地址表示方式的不同：如果用字母來(lái)表示列標(biāo)，數(shù)字來(lái)表示行號(hào)，就是A1引用樣式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用樣式。
由于可以構(gòu)造文本形式的單元格引用地址，因此利用文本連接符&就可以構(gòu)造“常量”+“變量”、”靜態(tài)“+”動(dòng)態(tài)“相結(jié)合的單元格引用方式。
例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是靜態(tài)地址，其中的列標(biāo)A和行號(hào)2都是常量，而COUNTA(D:D)部分形成了第二個(gè)行號(hào)的動(dòng)態(tài)引用。整個(gè)公式的引用方式就等同于“A2:An”，其中的n是變量，由D列的非空單元格數(shù)量所決定。
基于這樣的特性，Indirect函數(shù)常見(jiàn)的用途包括：
1，引用地址事先未知，需要通過(guò)其他運(yùn)算得到部分引用元素的引用
2，可以動(dòng)態(tài)更新的區(qū)域引用(常用于生成下拉菜單的數(shù)據(jù)有效性序列)
3，表名稱有數(shù)字規(guī)律的多表引用(三維引用)
4，構(gòu)造生成指定的數(shù)字序列或數(shù)組
5，與Text函數(shù)相結(jié)合，通過(guò)一組數(shù)值生成相應(yīng)的一組R1C1式引用
Match函數(shù)是Excel中十分常用的匹配查詢類(lèi)函數(shù)，其作用和地位與Vlookup、Lookup等查詢類(lèi)函數(shù)相近。與Lookup類(lèi)函數(shù)有所區(qū)別的地方在于：Match函數(shù)返回的結(jié)果是匹配元素的所在位置，而非匹配元素本身。
此外，Match函數(shù)還支持包含“*”、“?”等通配符的模糊查詢，但不能區(qū)分英文字母的大小寫(xiě)。如果要在目標(biāo)數(shù)組中進(jìn)行區(qū)分大小寫(xiě)的查詢，可結(jié)合Exact函數(shù)來(lái)組合公式，例如要在數(shù)組{"EXCEL","book","excel","SHEET"}查詢"excel"的位置，可以使用公式：
=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)
返回結(jié)果為3，不會(huì)受數(shù)組中第一個(gè)元素"EXCEL"的干擾。
需要注意的是，Exact函數(shù)不支持通配符，因此不能通過(guò)上述方法同時(shí)進(jìn)行區(qū)分大小寫(xiě)和包含通配符的模糊查詢。
當(dāng)目標(biāo)數(shù)組中包含多個(gè)與查詢數(shù)據(jù)相匹配的元素時(shí)，Match函數(shù)只返回其中第一個(gè)匹配元素的所在位置，因此Match函數(shù)通常只用于唯一性的查詢。但利用這一特性，Match函數(shù)還可以用來(lái)對(duì)數(shù)組中的非重復(fù)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，例如統(tǒng)計(jì)非重復(fù)數(shù)據(jù)的個(gè)數(shù)，返回非重復(fù)數(shù)據(jù)的列表等等。
二、Indirect函數(shù)
Indirect函數(shù)是比較常見(jiàn)的引用類(lèi)函數(shù)，與其功能相仿的函數(shù)包括Offset函數(shù)、Index函數(shù)等等。與Offset函數(shù)所不同的在于，Offset函數(shù)是基于目標(biāo)基準(zhǔn)位置的偏移引用，而Indirect函數(shù)則是通過(guò)構(gòu)造單元格地址的`文本書(shū)寫(xiě)方式來(lái)對(duì)單元格區(qū)域直接進(jìn)行引用。
在Excel中單元格的引用方式包括A1樣式和R1C1樣式，因此Indirect函數(shù)在構(gòu)造引用字符串時(shí)，也可以使用上述兩種樣式的文本字符串，但需要在第二參數(shù)中使用不同的參數(shù)值進(jìn)行標(biāo)識(shí)區(qū)別。
A1樣式和R1C1樣式兩種不同引用方式的來(lái)源是由于單元格地址表示方式的不同：如果用字母來(lái)表示列標(biāo)，數(shù)字來(lái)表示行號(hào)，就是A1引用樣式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用樣式。
由于可以構(gòu)造文本形式的單元格引用地址，因此利用文本連接符&就可以構(gòu)造“常量”+“變量”、”靜態(tài)“+”動(dòng)態(tài)“相結(jié)合的單元格引用方式。
例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是靜態(tài)地址，其中的列標(biāo)A和行號(hào)2都是常量，而COUNTA(D:D)部分形成了第二個(gè)行號(hào)的動(dòng)態(tài)引用。整個(gè)公式的引用方式就等同于“A2:An”，其中的n是變量，由D列的非空單元格數(shù)量所決定。
基于這樣的特性，Indirect函數(shù)常見(jiàn)的用途包括：
1，引用地址事先未知，需要通過(guò)其他運(yùn)算得到部分引用元素的引用
2，可以動(dòng)態(tài)更新的區(qū)域引用(常用于生成下拉菜單的數(shù)據(jù)有效性序列)
3，表名稱有數(shù)字規(guī)律的多表引用(三維引用)
4，構(gòu)造生成指定的數(shù)字序列或數(shù)組
5，與Text函數(shù)相結(jié)合，通過(guò)一組數(shù)值生成相應(yīng)的一組R1C1式引用
三、Match函數(shù)
Match函數(shù)是Excel中十分常用的匹配查詢類(lèi)函數(shù)，其作用和地位與Vlookup、Lookup等查詢類(lèi)函數(shù)相近。與Lookup類(lèi)函數(shù)有所區(qū)別的地方在于：Match函數(shù)返回的結(jié)果是匹配元素的所在位置，而非匹配元素本身。
此外，Match函數(shù)還支持包含“*”、“?”等通配符的模糊查詢，但不能區(qū)分英文字母的大小寫(xiě)。如果要在目標(biāo)數(shù)組中進(jìn)行區(qū)分大小寫(xiě)的查詢，可結(jié)合Exact函數(shù)來(lái)組合公式，例如要在數(shù)組{"EXCEL","book","excel","SHEET"}查詢"excel"的位置，可以使用公式：
=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)
返回結(jié)果為3，不會(huì)受數(shù)組中第一個(gè)元素"EXCEL"的干擾。
需要注意的是，Exact函數(shù)不支持通配符，因此不能通過(guò)上述方法同時(shí)進(jìn)行區(qū)分大小寫(xiě)和包含通配符的模糊查詢。
當(dāng)目標(biāo)數(shù)組中包含多個(gè)與查詢數(shù)據(jù)相匹配的元素時(shí)，Match函數(shù)只返回其中第一個(gè)匹配元素的所在位置，因此Match函數(shù)通常只用于唯一性的查詢。但利用這一特性，Match函數(shù)還可以用來(lái)對(duì)數(shù)組中的非重復(fù)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，例如統(tǒng)計(jì)非重復(fù)數(shù)據(jù)的個(gè)數(shù)，返回非重復(fù)數(shù)據(jù)的列表等等。
【execl常用的微函數(shù)系列】相關(guān)文章：
2.execl如何把計(jì)算式轉(zhuǎn)換為運(yùn)算結(jié)果