哈嘍，大家好，今天咱們來學習OFFSET函數。

根據過往的經驗來看，這是咱們學習Excel函數過程中一個不大不小的坎，OFFSET算是比較高級的函數，參數比較多，變化也比較多；不過它在數據結構轉換、高級圖表製作等方方面用到的也比較多，所以還是要學一下。

別緊張，下面咱們玩個小遊戲。誰正式學習之前不先打半小時王者榮耀不是？

咱們這個遊戲的名字叫：劉皇叔的野望。劉備要從老家出發，跑到成都去，打下一塊地盤，準備一桶江山。

現在，假如你是劉備，你在B1單元格，你要走到成都（D5單元格）。

怎麼走？

正常來說，有兩條路。

一條是先向下走4個單元格，再向右走2個單元格。

一條是先向右走2個單元格，再向下走4個單元格。

好了，分析完了，你走吧。

如何使用函數來實現咱們的移動過程和目標呢？
OFFSET函數最是恰當不過了。
▎=OFFSET(B1,4,2)
OFFSET函數的第1參數是基點，也就是咱們出發點（老家），本例爲B1；第2參數爲縱向移動幾行，本例爲向下移動4行；第3參數爲橫向移動幾列，本例爲向右移動2列。

運行公式，返回結果成都。

叮咚~恭喜你，成功到達成都城，闖過遊戲第一關簡易級副本。
請問你是否選擇勇闖第一關普通級副本？
叮咚~你選擇了我沒選我沒選我說你選了你就選了第一關普通級副本，2秒後副本開啓。

副本的任務看起來確實很簡單嘛，請使用OFFSET函數將A列的數據轉置爲一行。

來，找諸葛亮星光兄弟一起分析下現狀。

咱們的基點，也就是老家，在A1單元格，公式寫在C1單元格，然後向右複製填充，公式每向右複製一個單元格，基點就要向下移動一行。因此公式如下：
▎=OFFSET($A$1,COLUMN(A1)-1,0)

COLUMN(A1)返回A1單元格的列號，也就是1。隨公式橫向複製填充後，依次返回B2/C1/D1……的列號，也就是2~3~4……，減去1之後，作爲OFFSET的第2參數。OFFSET第3參數爲0，表示不需要橫向移動。於是以A1爲基點，每次向下遞增偏移1行。
C1單元格公式爲：=OFFSET($A$1,0,0)，返回A1單元格
D1單元格公式爲：=OFFSET($A$1,1,0)，返回A2單元格
E1單元格公式爲：=OFFSET($A$1,2,0)，返回A3單元格
……
……
以此類推，即爲結果。

那麼反過來，又如何將C1:K1單元格區域的數據轉換爲A1:A9呢？
——別看我，儂自己想想。
叮咚~恭喜你，輕鬆闖過第一關普通級副本，獲得評分SSS。
請問你是否選擇勇闖第一關困難級副本？

叮咚~你選擇了我沒選我沒選我說你選了你就選了第一關困難級副本，副本馬上開啓。
請使用OFFSET函數將A列的數據轉換爲三行三列，如C1:E3區域所示。
然後……請再使用OFFSET函數將C1:E3區域三行三列的數據，轉換爲A列單列數據……

叮咚，很不幸，闖關失敗吶你，請下次再來吧。

誰玩遊戲還沒遇到過bug不是？不要灰心，解鎖答案可以參考文末示例文件。咱們的目標是經略西南~一桶江山，下面還請繼續勇闖第二關。
……
……
話說劉備佔領成都城後，地位漸穩，脾氣漸長，野心骨質增生。8842年的冬天，成都下了第一場雪，劉備趁老婆不在家，在趙雲的陪同下喫了一頓豐富的老壇酸菜牛肉方便麪，一時間辣氣幹雲，遂決定出師向南，佔領成都臨近的3座城市：江州、建寧和武陵。

——別發呆啊，該你上場了，請使用OFFSET函數完成從D5單元格成都城出發，佔領臨近3座城池的目標。

是否查看遊戲攻略✔

OFFSET函數基本語法如下：

=OFFSET(基點,移動的行數,移動的列數,[新引用的行數],[新引用的列數])

在第一關時，你已經懂得了第1、2、3參數的意義。它們可以將指定單元格或區域，按指定行/列數，移動到另一個單元格或區域。
這裏需要補充說明的是，移動的行列數可以是正數，也可以是負數。第2參數使用正數時，表示從基地向下偏移，使用負數時，表示向上偏移。第3參數使用正數時，表示向右偏移，使用負數時，表示向左偏移——這段話和你闖第2關沒啥關係。
第4和第5參數是可選的，如果省略這兩個參數，新引用的區域就是和基點一樣的大小。如果沒省略……就代表以基點位置開始，向四周擴張地盤。

其中第4參數代表地盤擴張後的行數，如爲正數，則向下擴張，如爲負數，則向上擴張；第5參數代表地盤擴張後的列數，如爲正數，則向右擴張，如爲負數，則向左擴張。
第2、3參數指定了基點移動的行/列數，是不包含基點自身的。而第4、5參數指定了地盤大小的行列數，是包含基點自身的。
因此第4和5參數不能爲零，爲零就說明你連老家都丟了，GAME OVER翹辮子了不是？

攻略結束

——看罷攻略，相比你對闖關已經胸有成竹了。

答案如下：

▎=OFFSET(D5,0,0,2,2)

OFFSET函數以D5單元格成都城爲基點，第2、3參數爲0，表示基點原地不動，然後向下擴張2行，向右擴充2列，也就是D5:E6區域。

公式運行後會返回錯誤值#VALUE!，這是多維引用結果落地的問題，咱們先不管它，以後有緣再聊。這裏可以先選中公式，按查看公式返回的結果是否正確。

OFFSET函數直接生成一個區域的用法，常用於動態圖表的數據源；而在函數處理和數據分析中，常作爲其它聚合統計或查找匹配類函數的參數。比如統計D5:E6區域數據的個數：

▎=COUNTA(OFFSET(D5,0,0,2,2))

更多技巧請繼續往下閱覽。

……

叮咚~恭喜你，成功闖過第二關簡易級副本，獲得評分SSS。
請問你是否選擇勇闖第二關普通級副本？
叮咚~你選擇了我沒選我沒選我說你選了你就選了第二關普通級副本，副本馬上開啓。

請使用OFFSET函數，直接從老家（B1單元格）出發，佔領成都、江都、建寧和武陵（D5:E6）區域。

▎=OFFSET(B1,4,2,2,2)

OFFSET函數以B1單元格爲基點，向下移動4行，然後向右移動2列，將基點移動到成都城，然後向下擴張2行，向右擴充2列，即爲D5:E6區域。

叮咚~恭喜你，成功闖過第二關普通級副本，獲得評分SS。
請問你是否繼續選擇勇闖第二關普通級副本？
叮咚~你再次選擇了我沒選我沒選我說你選了你就選了第二關普通級副本，副本馬上開啓。

請使用OFFSET函數，動態計算B列銷售額最近8條記錄的平均銷售值。動態是個什麼意思呢？就是當B列有新加的銷量時，公式必須自動顯示最新結果。

比如目前平均值區域是B7:B14；但當我在B15填入一個新的銷量，平均值計算區域就應該自動更新爲B8:B15……

公式如下：
▎=AVERAGE(OFFSET(B1,COUNTA(B:B)-1,0,-8))
COUNTA函數計算出B列非空單元格的個數，然後減去1，扣掉標題行。
OFFSET函數以B1單元格爲基點，以COUNTA函數的計算結果作爲向下移動的行數，也就是B列有多少個銷售記錄，就向下移動多少行。
此時基點移動到了B列最後的數值所在單元格，例如B14。第4參數指定了縱向擴張的行數：-8，意思是將基點向上擴充8行，於是得到單元格區域B7:B14。

如果B列的數值增加了，COUNT函數的計數結果就增加了，OFFSET函數的結果也就會自動更新。
最後使用AVERAGE函數計算出這個引用區域中的平均值。
……
……

適當遊戲益腦，沉迷遊戲傷身，今天和大家分享的內容就到這裏。身體要緊啊少年，一桶江山什麼的也別要了。

原載：知識星球

圖文作者：看見星光