表親們好啊，今天老祝和大家分享關於OFFSET函數的知識。
說起OFFSET函數，真的是像霧像雨又像風，從複雜的數據彙總、到數據透視表再到高級動態圖表，都離不開OFFSET函數的默默付出。
通過OFFSET函數，可以生成數據區域的動態引用，這個引用再作為半成品，通過後續的處理加工，就可以為圖表和透視表提供動態的數據源、為其他函數生成特定的引用了。
首先，咱們說說這個函數的作用，微軟的幫助文件是這樣描述的：
以指定的引用為參照系，通過給定偏移量返回新的引用。
瞧瞧，這麼簡單的幾句話，讓人家怎麼能猜透你的心思嘛。
這個函數有5個參數：
第一個參數是基點
第二個參數是要偏移幾行，正數向下，負數向上。
第三個參數是要偏移幾列，正數向右，負數向左。
第四個參數是新引用幾行。
第五個參數是新引用幾列。
如果不使用第四個和第五個參數，新引用的區域就是和基點一樣的大小。
如果使用下面這個公式：
=OFFSET(C3,4,2,4,3)
就是以C3為基點，向下偏移4行，向右偏移2列，新引用的行數是4行，新引用的列數是3列，最終得到對E7:G10單元格區域的引用。
當然，僅僅得到引用是沒啥用處的，咱們的目的是把OFFSET函數得到的引用作為一個半成品，再通過其他方法進行再加工。
這麼說還是有點抽象啊，再來一個形象一點的：
OFFSET函數就像是一個敵人的小分隊，從據點（C3）出動，順著大路向南走4裡（C7）
拐彎兒再向東2裡，這時候就到馬家河子（E7）了
敵人隊長說了，我要以馬家河子（E7）這個地方開始，再佔領一片地盤。
有多大呢？向南4裡，向東3裡。結果就是E7:G10單元格區域了。
簡單認識了OFFSET函數，咱們再看看這個函數的一些典型應用。
1）行列轉置
如下圖，要將A2:D7單元格中多行多列的姓名，轉換到一列中。
F2單元格公式為：
=OFFSET($A$2,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))&""
OFFSET函數的基點為A2。
向下偏移的行數為(ROW(A1)-1)/4，這部分公式下拉時，可以得到從0開始，按0.25遞增的序號，即0，0.25，0.5，0.75，1，1.25，1.5……OFFSET函數對帶有小數的參數自動向下取整，向下偏移的行數依次為0 0 0 0 1 1 1 1 2 2 2 2……也就是公式每下拉四行，就從數據源中向下偏移一行。
向右偏移的列數為MOD(ROW(A1)-1,4)，這部分公式下拉時，可以得到0 1 2 3 0 1 2 3……的循環序列序列，也就是公式每下拉一個行，就從數據源向右偏移一列，下拉到第五行時，偏移的列數又會從0開始。
偏移行數和偏移列數二者結合，最終形成1 2 3 4 2 2 3 4 3 2 3 4 ……這樣的偏移方式。
2）計算指定區間的銷售額
如下圖所示，要計算從1月份到指定月份的累計銷售額。
F4單元格公式為：
=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))
MATCH(F2,A2:A13,0)部分，先使用MATCH函數計算出F2單元格中的月份在A2:A13中的位置，結果為9.
OFFSET函數以B2單元格為基點，向下偏移0行，向右偏移0列，以MATCH函數的計算結果作為新引用的行數，最終得到B2:B10單元格區域的引用，再使用SUM函數計算這個區域中的總和，得到從1月份到指定月份的銷售總額。
3）計算最近7天的平均銷量
如下圖所示，A列和B列是銷售流水記錄，要計算出最近7天的平均銷量。
F4單元格公式為：
=AVERAGE(OFFSET(B1,COUNT(B:B),0,-7))
先使用COUNT函數，統計出B列的數值個數。
OFFSET函數以B1為基點，以COUNT的結果作為向下偏移的行數，也就是B列有多少個數值，就向下偏移多少行。
這時候就相當於到了B列數值的最後一行，給定的偏移列數是0，新引用的行數是-7，得到從B列數值的最後一行開始，再向上7行這樣一個動態的區域。
如果B列的數值增加了，COUNT函數的計數結果就增加了，OFFSET函數的行偏移參數也就隨之變化。
就相當於一竿子捅到底，然後來個燒雞大窩脖兒，向上引用7行，所以得到的始終是最後7行的引用。
最後使用AVERAGE函數計算出這個引用區域中的平均值。
4）計算篩選後的商品總價
如下圖，是各食堂的採購記錄，需要計算篩選後的商品總價。
G1單元格公式為：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:9),0))*C2:C10*D2:D10)
要計算篩選後的內容，首先需要判斷單元格是不是處於顯示狀態。
先來看OFFSET(A1,ROW(1:9),0)部分，OFFSET函數以A1單元格為基點，向下偏移的行數是ROW(1:9)的計算結果，表示依次向下偏移1~9行，最終得到9個引用區域，每個單元格區域由一個單元格構成。
這裡涉及到多維引用的知識點了，小夥伴們如果犯迷糊，可以先收藏一下。
接下來使用SUBTOTAL函數對OFFSET函數得到的多個引用區域進行處理，第一參數使用3，表示使用COUNTA函數的計算規則，即依次統計A2~A9這九個單元格區域中的不為空的單元格個數。
如果單元格處於顯示狀態，則對這個單元格的統計結果為1，否則統計結果為0。
這部分公式得到類似下面的效果：
{0;0;0;0;0;1;1;1;1}
再用SUBTOTAL函數的結果乘以C列的單價和D列的數量，如果單元格處於顯示狀態，則相當於1*數量*單價，否則相當於0*數量*單價。
最後使用SUMPRODUCT函數對乘積進行求和，這樣就得到篩選後的商品總價了。
除了以上常規的用法，OFFSET函數還經常用於動態圖表的製作，這些內容咱們以後繼續分享。
好了，今天的內容就是這些吧，後半部分對於很多小夥伴來說可能有點難了，不過不用擔心，咱們可以先收藏，然後慢慢消化，積少成多循序漸進。
祝各位小夥伴一天好心情！
圖文製作：祝洪忠
閱讀更多 Excel之家 的文章
