在Excel表格輸入公式時，單元格區域的引用是必不可少的；直接框選單元格區域可以實現基本的操作需要，如果要實現動态的單元格區域引用就無能為力了。這篇文章為朋友們分享一下最強大的單元格區域引用函數→offset函數的基本概念和應用實例。Offset函數自身隻是一個單元格區域引用函數、并沒有什麼強大的功能。但是配合SUM、MATCH、COUNTIF等函數就可以完成很多很牛的操作。

一.Offset函數基本概念：

1.語法：Offset(reference, rows, cols, [height], [width])；

各參數中文含義：offset（起始區域，向下偏移行數，向右偏移列數，返回的行數，返回的列數）

2.各參數解釋：

（1）Reference：作為參照的單元格引用（不僅可以是單元格，也可以表示從一個區域開始進行偏移。）

（2）Rows：向上或向下偏移的行數（向下為正數，向上為負數；如果這個參數省略表示不向上下偏移，即省略時默認為0）

（3）Cols：向左或向右偏移的列數（向右為正數，向左為負數；如果這個參數省略表示不向左右偏移，即省略時默認為0）

（4）Height：高度，需要返回的引用的行高， Height 必須為正數。（如果這個參數省略表示引用的高度與第一個參數Reference的高度相同。）

（5）Width：需要返回的引用的列寬，Width 必須為正數。（如果這個參數省略表示引用的寬度與第一個參數Reference的寬度相同。）

3.實例演示：

（1）操作：選擇E1:F4單元格輸入公式=OFFSET(A1,3,1,4,2)同時按住鍵盤上的Ctrl Shift Enter确定公式。

（2）解析：以A1單元格為參考，向下移動三行、向右移動一列得到的一個4行2列的單元格區域的引用。

二.應用實例：

1.與SUM、MATCH函數配合使用實現動态求和。

（1）選擇E2:F2單元格通過數據驗證制作一個“月份”下拉列表；選擇G2單元格通過數據驗證制作一個“銷量”下拉列表。

（2）在H2單元格輸入公式：

=SUM(OFFSET(A1,MATCH(E2,A2:A10,0),MATCH(G2,B1:C1,0),MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0) 1))

（3）公式解析：

①A1單元格作為offset函數的參照單元格。

②MATCH(E2,A2:A10,0)查找E2單元格内容在A2:A10單元格區域的位置；例如，E2内容為2月時，返回的結果為2。

③MATCH(G2,B1:C1,0)查找G2單元格内容在B1:C1單元格區域的位置：例如，G2内容為實際銷量時，返回的結果為2。

④MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0) 1)中的MATCH(F2,A2:A10,0)查找F2單元格内容在A2:A10單元格區域的位置；例如，F2内容為5月時，返回的結果為5。計算結果5-2 1=4返回的是2月到5月的單元格區域的行數。

⑤offset函數的第五個參數省略，默認值與第一個參數A1單元格區域的列數相同、即第五個參數為1。

⑥最後使用SUM函數對OFFSET函數返回的單元格區域求和。

（4）注意事項：

因為OFFSET函數返回的是一個單元格區域、即返回結果為一個數組，所以一定要同時按住Ctrl Shift Enter三鍵确定公式。

（5）動态演示：

2.與COUNTIF、MATCH函數配合使用制作多級聯動下拉列表。

（1）制作一級下拉菜單：

選擇省份下的單元格區域→切換到數據選項卡→數據驗證→切換到設置選項界面→允許下選擇“序列”→來源下方的對話框輸入“遼甯省,吉林省,黑龍江省”→确定。

（2）制作二級聯動下拉菜單：

選擇市下的單元格區域→切換到數據選項卡→數據驗證→切換到設置選項界面→允許下選擇“序列”→來源下方的對話框輸入下方公式→确定。

=OFFSET($A$1,MATCH($D2,$A$2:$A$16,0),1,COUNTIF($A$2:$A$16,$D2))

（3）公式解析：

①A1單元格作為offset函數的參照單元格,注意這裡行和列的絕對引用。

②MATCH($D2,$A$2:$A$16,0)查找D2單元格的省份在A2:A16單元格第一次出現的位置。例如D2單元格内容為“黑龍江省”時，返回的結果為12。這裡注意D2單元的隻絕對引用列，不絕對引用行。

③因為市在省份的後一列，所以要向右偏移一列、即第三個參數為1。

④COUNTIF($A$2:$A$16,$D2)統計的是D2單元格的省份在A2:A16單元格區域出現的次數，也就是每個省份對應有幾個市、即offset函數第四個參數引用單元格區域的行數。

⑤offset函數的第五個參數省略，默認值與第一個參數A1單元格區域的列數相同、即第五個參數為1。

（4）注意事項：

①制作一級下拉菜單時，不同的的選項之間要用英文輸入法下的逗号隔開。

②制作二級下拉菜單時，注意公式中對單元格的引用方式、不要混淆絕對引用和相對引用。

（5）動态演示：

總結，通過對OFFSET函數的基本概念的講解和應用實例介紹相信你一定已經掌握這個函數的用法。如果有什麼不懂的地方歡迎在評論區留言讨論。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!