您當前的位置:首頁 > 舞蹈

據說這是Excel最難的函式,但它在這些場景非常有用!

作者:由 資料化管理Martin 發表于 舞蹈時間:2019-08-18

一般來說,函式的學習普遍都比較容易,因為你只需要在百度上輸入Excel+函式名稱,就會出現大量的教程網頁,所以要學習函式簡直太多資料了。

但在我線下的資料分析培訓中,發現有一個函式,普遍人認為它是難以理解和掌握的。

據說這是Excel最難的函式,但它在這些場景非常有用!

沒錯,就是OFFSET,那麼它到底難在哪裡?

其實是因為它和常用函式不一樣的地方是:

它返回的結果,並不是一個值,往往是一個單元格區域

這次文章給大家介紹一下OFFSET的語法用法,以及它的一些應用的場景。

函式的基本語法

據說這是Excel最難的函式,但它在這些場景非常有用!

以上是函式的語法解釋,為了更便於理解,我們來一些使用示範,先來看看資料來源:

據說這是Excel最難的函式,但它在這些場景非常有用!

OFFSET函式使用示範:

1、基礎用法→返回一個值

=OFFSET(A1,1,1)

意思就是指,以A1為起點,先向下走一行,再向右走一列,也就是取值的結果就是B2單元格了,所以返回的結果是1,類似的你應該都能理解了:

據說這是Excel最難的函式,但它在這些場景非常有用!

2、進階用法→返回一個單元格區域(比較常用)

=OFFSET(A1,1,1,1,1)

意思是指以A1為起點,先向下走一行,再向右走一列,再取1行1列(其實就是取一個單元格),即取值的結果還是B2單元格了,所以返回的結果還是1

據說這是Excel最難的函式,但它在這些場景非常有用!

再來一個看看:

=OFFSET(A1,1,1,2,2)

意思是指以A1為起點,先向下走一行,再向右走一列,再取2行2列(其實就是取了一個單元格區域),即取值的結果是B2:C3這個位置,所以返回的結果是一個數組來的。在實際的學習測試中,可以選中2X2的單元格區域,輸入公式後再按下CTRL+SHIFT+ENTER,這樣就可以讓結果顯示出來。

據說這是Excel最難的函式,但它在這些場景非常有用!

經過以上2點介紹,如果你還跟隨實操練習的話,肯定是可以明白OFFSET的基本用法了。

但是學習單個函式不難,最難的是巢狀的應用,因為需要考慮場景與邏輯。而這方面的學習,就需要透過實操經驗去積累了,當然關注本公眾號或者加入數說會員也是一個很好的學習渠道~

應用場景簡介

1、解決VLOOKUP的查詢表Key列一定要在最左的問題

場景還不清楚?直接看圖片:

據說這是Excel最難的函式,但它在這些場景非常有用!

使用vlookup函式,在左圖的資料結構是可以的,但若月份在右側,vlookup就無法查詢了!

解決這個問題,網路教程中有些是介紹使用陣列函式重構新表,但我非常不建議使用,運算效率極低,陣列函式玩深了會容易走火入魔~

使用INDEX+MATCH也是一個很好的解決方案,今天講OFFSET,我們就介紹用OFFSET去實現:

據說這是Excel最難的函式,但它在這些場景非常有用!

雖然是有點複雜,但確實是可以實現,這個方法就當開拓思維吧~(實際應用中建議使用index+match)

2、結合定義名稱生成動態單元格區域

定義名稱是一個很好的功能,它可以把固定單元格定義成一個自定義的名字,也能把OFFSET返回的一個動態區域定義一個名字,然後再進行後續的引用。

所以用OFFSET函式,巢狀COUNTA函式作為取值行數的動態識別,再定義為名稱的話,就能獲取到一個動態的單元格區域了!

有了動態的單元格區域,那你做透視表,圖表,都可以實現自動擴充套件資料來源了!

據說這是Excel最難的函式,但它在這些場景非常有用!

3、製作綜合資料分析模板

你的日報、週報、月報等,梳理好報表邏輯後,整合動態圖表來進行呈現分析,將能大大提升你的報表效率!例如這種效果:

據說這是Excel最難的函式,但它在這些場景非常有用!

在製作該模板的過程,也有使用到OFFSET函式,去構造出動態的分析物件列,指標列,這樣做也可以省去做輔助列的操作,也能提升運算效率!

如果你還想學習更多的動態圖表高階應用,歡迎加入《數說》會員,學習以下的系列課程,帶你玩好動態圖表,提升報表的效率!

據說這是Excel最難的函式,但它在這些場景非常有用!

標簽: offset  單元格  函式  A1  動態