昨天我們介紹了Excel函數家族低調的王者：Index函數。今天我們來聊聊函數家族的紅娘：
offset函數。為什麼此函數能夠被稱之為“紅娘”呢？這是由這個函數本身所幹的活所決定的：此函數就是給其他函數或者Excel的其他功能引路搭橋的。我們來看看微軟官方的說法吧：offset函數：以指定的引用為參照系，通過給定偏移量返回新的引用。
這個什麼鬼，完全看不懂嘛。下面我來試著圖解一下，請看下圖：
offset函數圖解
offset函數主要用於返回單元格或者單元格區域的引用。它共計有5個參數：
Reference：起始單元格或者單元格區域，又叫基點或者參考系；
Rows：上下偏移的行數，負數向上偏移（不常用），正數向下偏移；
Cols:左右偏移的列數，負數向左偏移（不常用），正數向右偏移；
Height:返回的區域高度，1為1個單元格高度，此參數可省略，省略時，offset返回的引用區域的高度將與Reference區域的高度保持一致；
Width:返回的區域寬度，1為1個單元格寬度，此參數可省略，省略時，offset返回的引用區域的寬度將與Reference區域的寬度保持一致；
例如如上圖所示，通過公式：
=offset(B3,3,2,3,4)
offset函數將返回D6:G8這樣一個區域。簡單瞭解了一下offset函數的基本參數，下面我們來看看offset函數在職場中的實際運用吧。
一、offset函數輕鬆提取業績統計
如下圖所示，我們如何快速地完成右側的表格呢？顯然，部分同學會想到使用vlookup函數（大眾情人的稱號不是白叫的）。因此他們寫出的公式估計是這個樣子的：
=VLOOKUP(E17,$A$17:$B$51,2,)
這個公式非常簡單，也很好理解。但是針對下面的情況就不一定好用了（高手用通配符當然也是分分鐘）：
情況稍微複雜一些
這時候我們可以使用offset函數，我們分析一下左側的表格，發現“年總計”總是出現在四個季度之後，也就是說，假如我們以B16為基點，我們要得到的B21,B26,B31……的值，因此offset第一個參數reference為B16；第二個參數Rows，行偏移為5的1倍，2倍，3倍……，因此我們rows參數可以使用row(1:1)*5；第三個參數列偏移，我們的基點在B16單元格，要返回的單元格也在B列，因此無需偏移，因此這個參數留出位置即可；因為我們要返回的單元格均為1個單元格，第4個和第5個參數直接省略即可。最終公式如下：
=OFFSET(B$16,ROW(1:1)*5,)
offset函數基礎應用
這只是offset函數的小試牛刀。或許很多朋友還是會疑惑，就這點本事還不值得我去學習他啊。是的，如果就那麼點本事的確沒有必要學習。然而，offset函數要遠比我們想象得到的強大的多。無論是當其他函數的紅娘（主營業務）還是和數據驗證配合一起創建動態二級下拉列表或者和名稱功能創建動態圖表，我們隨處都可見offset函數的身影。繼續看下面的實例。
二、offset函數甘當其他函數的“紅娘”--案例一
如下圖所示，我們需要快速地得到最高月平均工資，該如何做呢？
求最高月平均工資
很多朋友遇到此類問題，通常選擇添加輔助列解決，在H列新建一個輔助列：月平均工資，然後用average算出每個人的月平均工資，再用max函數得到最高的月平均工資，如下圖所示：
最簡單的輔助列
的確，用這種辦法確實挺簡單實用的。然而在實際工作中，某些固定好的表格是不允許調整表格結構的，遇到這種情況通過以上的方法就行不通了。這時候一定要想到offset函數，在很多情況下它是可以完全取代輔助列的。思路和使用輔助列的思路一樣，
我們首先要引用到每個人的所有月工資數據，然後算出每個人的月平均工資，最後再去求最大值。首先我們如何快速得到每個人的月工資數據呢？
我們知道月工資在B:G列，因此我們的基點為B56:G56；
每多偏移一行就能得到一個人的工資數據，行偏移為1時，得到張三的月工資數據（B57:G57）,行偏移為2時，得到李四的月工資數據（B58:G58）……，我們需要分別得到張三到重八8個人的數據，因此第二參數為：{1,2,3,4,5,,6,7,8}，我們也可以通過row(1:8)得到這樣一個參數；
第三個參數列偏移為0，第4個和第5個參數可以省略；
因此返回每個人月工資數據的公式為：=offset(B56:G56,row(1:8),)
然後如何得到每個人的月平均工資呢？
由於offset函數得到的將會是一個多維數組，顯然我們使用average函數沒法得到多個人的月平均工資。這裡我們可以使用subtotal函數來做。公式為：
=subtotal(1,offset(B56:G56,row(1:8),)) 。這個函數我不多作解釋，有興趣的朋友可以參閱我寫的subtotal函數的教程 。最後，我們可以利用max函數快速得到月平均工資最高值。最終公式如下：
{=MAX(SUBTOTAL(1,OFFSET(B56:G56,ROW(1:8),)))}
最終效果圖
三、offset函數甘當其他函數的“紅娘”--案例二
還是剛才的表格，不過咱們的需求變了一下：我們需要快速求出6個月中有兩個月以上工資不到1000的人數有多少，這個怎麼來解決呢？
求人數
我們先來理一下思路，跟上面一個案例一樣，我們要統計2個月以上工資不到1000的人數，首先我們得要引用到每個人的工資數據，這個跟案例一樣；第二我們要統計每個人六個月的工資中，有幾個月的工資是低於1000的，這個我們可以countif函數來解決；第三我們要判斷一下哪些人超過1個月的工資是低於1000的，最後我們來統計人數。下面是每一步的公式：
Step1：求每個人的工資數據
=offset(B56:G56,row(1:8),)
Step2：統計每個人月工資低於1000的月份數
=countif(offset(B56:G56,row(1:8),),"<1000")
Step3：判斷每個人低於1000的月份數是否超過1個月
=--(countif(offset(B56:G56,row(1:8),),"<1000")>1)
Step4：統計人數
{=sum(--(countif(offset(B56:G56,row(1:8),),"<1000")>1)))}
最終結果：
{=SUM(--(COUNTIF(OFFSET(B56:G56,ROW(1:8),),"<1000")>1))}
最終效果圖
關於offset函數 ，今天就先分享到這裡，更多內容請持續關注：傲看今朝。
閱讀更多 傲看今朝 的文章
