Offset函數，很好很實用，學會輕鬆找高新工作

Offset函數在實際工作中有很多應用，我們經常用其他函數公式給Offset的參數賦值，生成動態引用，從而實現豐富多彩的功能。

比如Offset函數用於分組求和、間隔取值。

參數rows和cols分別代表從基準單元格開始偏移的行數和列數。rows，正數表示向下，負數表示向上；cols正數表示向右，負數表示向左。

參數 height和width，代表從基點單元格偏移完之後返回的區域的行高和列寬。

1、分組求和

如下圖所示，在A1:L6的區域內有每個月的銷售數據，我們需要將每行數據按照季度匯總，也就是每三個單元格作為一組進行求和。

我們在M2單元格中輸入以下公式：

=SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3))

然後向右、向下拖拉複製公式填充，這樣就完成了所有的求和了。

2、間隔取值

還是上面的例子，假如我們想要取一、四、七、十這幾個月的值該怎麼做呢？

如果是一次取一個單元格的值的話，只需要OFFSET就可以了，不需要SUM了。

在M1單元格輸入以下公式：

=OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,1)

分組求和、間隔取值，你只需要一個公式

3、動態確定求和的數據區域

以下是數據區域，需要在其他區域按照產品獲取某些月份的總和。

我們在P2單元格輸入以下公式：

=SUMIF($A:$A,$O2,OFFSET($A:$A,,MATCH(P$1,$B$1:$M$1,0)))

這個公式中的Offset的參數是由Match函數來確定的，以A列為基點，確定P1單元格的值在B1:M1中的第幾個位置，就是要移動幾列。比如Feb在第2個位置，就從A列移動2列到了C列。這樣再用sumif求和就可以了。當我們需要獲取不連續區域的內容時，就可以用類似的公式，通過match確定是從那一列取值。

4、動態顯示最近幾天的總數量

在以下數據區域中顯示了一段日期的銷量，我們需要動態地顯示最近3天的銷量。在E2單元格中輸入以下公式：

=SUM(OFFSET(B1,COUNT(B:B)-D2 1,,D2))

或者=SUM(OFFSET(B1,COUNT(B:B),,-D2))

第一個公式的Offset參數是，確定B列有多少個數字，再減掉需要顯示的天數 1。可以這麼理解，如果參數只是COUNT(B:B)，則到達了最後一個單元格B9，也就是顯示了最近1天的數據，如果要顯示最近兩天的數據，就需要在COUNT(B:B)的基礎上減1，也就等於-D2 1。

第二個公式中的行高使用了負值，這個實際上是可以的，也是常見的用法之一。

5、創建二級下拉菜單

以下是每個部門及對應的二級部門，二級部門個數還不一樣。

我們需要設定選擇不同的一級部門後，生成對應的二級部門的列表。

我們需要創建兩個名稱：

一級菜單=OFFSET(二級下拉菜單!$A$1,1,,COUNTA(二級下拉菜單!$A:$A)-1)

二級菜單=OFFSET(二級下拉菜單!$B$1,MATCH(二級下拉菜單!G2,一級菜單,0),,,COUNTA(OFFSET(二級下拉菜單!$B$1:$E$1,MATCH(二級下拉菜單!G2,一級菜單,0),)))

在【公式】的【名稱管理器】中點【新建】按鈕可以插入名稱。

一級菜單可以動態地確定A列的區域範圍。

二級菜單會根據選擇的一級菜單，動態的返回對應的二級菜單區域。

在G2單元格設置數據有效性，設置「序列」，來源=一級菜單。

同樣的方法，在H2設置數據有效性，來源=二級菜單。

6、創建動態數據透視表

定義名稱 ：

動態數據透視表區域=OFFSET(動態的數據透視表!$A$1,,,COUNTA(動態的數據透視表!$A:$A),COUNTA(動態的數據透視表!$1:$1))

這個是非常常見的用法，從A1單元格區域開始擴展，最終行高就是A列有多少個數據，列寬就是第一行有多少個數據，這裡要注意A列和第一行都不能有空值，否則確認的區域就不正確。當我們在數據區域中增加行後，只需要刷新數據透視表就可以將增加的數據包含進去。

我們在插入數據透視表時輸入定義好的名稱「動態數據透視表區域」就可以了，在定義名稱時可以自己命名一個簡單點的名字。

7、創建動態圖表

用Offset既然可以生成動態區域，那麼我們可以通過指定不同的參數，從而生成不同的區域引用，在圖表的數據區域中加以引用，就可以生成動態圖表了。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！