Excel中除了常規的查詢匹配函數,還有一個80%的人基本沒有使用過的函數,那就是Offset函數,可能很多人沒有講過這個函數,更談不上如何去使用。今天我們就來用三個案例,來學習一下看看這個函數到底有多強大。
一、Offset函數基礎講解:
案例說明:如上圖黃色區域單元格B3,從當前行起向下4行,向右3列,引用4行,引用3列偏移後,從而形成了B3:D6單元格區域。
函數公式:=OFFSET(B3,4,3,4,3)
函數講解:
1、offset函數為偏移函數,它可以通過位置的偏移獲取一段單元格範圍區域;
2、總而言之offset函數偏移方向為,下—右—上—左。第一參數為起始位置;第二參數為向下偏移多少(正數為向下,負數為向上);第三參數為向右偏移多少(正數為向右,負數為向左);第四參數為引用多少行,第五參數為引用多少列。
二、Offset函數經典案例講解
案例1:通過數據偏移計算月份區間數據
案例說明:計算1-8月份總銷售額
函數公式:
=SUM(OFFSET(C2,0,0,MATCH(8,B2:B13,0)))
函數講解:
1、通過上述數據偏移我們可以得到一個數據區域;最後用sum函數進行求和計算;
2、offset函數偏移單元格從C2也就是1月銷售額開始;第二、三參數為0說明向下和向右位置不做偏移;
3、第三參match函數代表的是查詢出對應月份所在的位置,得到結果為8。在函數中也就代表引用8行數據。也就得到了C2:C9單元格區域。
案例2:計算後半年(7-12月)月平均銷售額
函數公式:
=AVERAGE(OFFSET(C1,COUNT(C:C),0,-6))
函數講解:
1、offset函數這裡從C1單元格進行開始偏移;
2、第二參數count(C:C)代表向下偏移多少位置,計算出對應C列函數字的單元格個數;
3、第三參數0代表向右不做偏移;
4、第四參數-6代表從最後一個單元格起,往上選擇6行,得到C8:C12單元格區域。最後用average函數進行計算平均值。
案例3:offset函數製作動態下拉菜單欄
案例說明:如上圖,當我們重新添加部門進去的時候,下拉菜單選項會自動進行更新。
函數公式:
=OFFSET(G$1,0,0,COUNTA(G:G))
函數講解:
1、offset函數這裡從G1單元格進行開始偏移;用$固定行,這樣往下拖動的時候就不會變化;
2、第四參選擇的範圍為counta函數計數文本單元格的個數得到選擇多少行;
【動態演示操作】
通過上面的案例,現在你知道在實際過程中如何運用offset函數了嗎?
喜歡的話轉發或掃描下方的二維碼關注一下唄!