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函數還可以用來對數組中的非重複數據進行統計,例如統計非重複數據的個數,返回非重複數據的列表等等。
-
巧用Word文檔的自動更正快速輸入文字
往往打字速度的提高和文字的出錯率成正比,尤其是在輸入一些英文單詞的時候,由於輸入的速度快,可能就會將英文單詞打錯。重複而又仔細地檢查輸入的材料是必不可少的,但這又會降低工作的'效率。對於上述的情況,word提供的“自動更正”功能就可以很好地解決。下面我們...
-
Word文檔裏輕鬆實現逆頁序打印
一、打印到文件想打印文件,沒有打印機,而有打印機的電腦又沒裝Word,怎麼辦?我們可以在Word的“文件”菜單中調出“打印”對話框再選中“打印到文件”選項,確定後選擇保存該文件的位置,再輸入文件名後即可生成一個後綴名為prn的.打印機文件,這樣就可以在有打印機的電...
-
關於PPT的設計思路介紹
關於PPT的設計思路介紹PPT是針對徐渭而做的。介紹了徐渭的個人簡介、人生經歷、他的趣聞小故事及他的作品特點。首先,讓同學們對徐渭所處的朝代有所瞭解,再來介紹徐渭的人生經歷。這樣更有助於學生理解社會環境對他所造成的影響,從而影響徐渭的創作,使學生更容易接...
-
Word文檔出現的下劃線不同的含義
Word軟件中有很多不同的下劃線,你知道它們都代表什麼意思嗎?1.紅色或綠色波形下劃線當自動檢查拼寫和語法時,Word用紅色波形下劃線表示可能的拼寫錯誤,用綠色波形下劃線表示可能的語法錯誤。右鍵單擊紅色波形下劃線上的文字,Word將給出更正建議。2.電子郵件標題的紅...