首頁 » Excel » Excel 進階|Offset 函數應用

Excel 進階|Offset 函數應用

雖然身為一個不務正業的財務分析師,但偶爾還是要回歸一下本業。這篇就來跟大家分享幾個我吃飯的工具,前半段會介紹 offset 的用法,後半段是比較進階的應用,雖然比較複雜一點卻非常實用。

剛好前陣子在公司內分享了幾個函數的應用,打鐵趁熱,也把分享的內容寫成文章,希望能幫助大家多了解函數的應用,進而能夠提升工作效率。

這篇介紹的函數是 Offset,以下為官方說明:
Offset 傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。
(點擊可連結至官方說明頁)

如果你看完以上敘述,或是讀完官方說明,就知道要如何應用這個函數,這篇文章對你來說可能太淺了,或者你可以直接往下看進階應用的部分。如果你也跟我一樣,每個中文字都看得懂,但是完全不知道整句話在講什麼,歡迎你繼續看下去 XD,我會用自己平常工作遇到的狀況來舉例說明。

▶️ 本篇提供範例檔免費下載,別忘了到文章最後下載

Offset 基本介紹

OFFSET (reference, rows, cols)
OFFSET (參照儲存格, 向下位移, 向右位移)

這個函數的最重要的概念就是「位移」,只要選定了參照儲存格,就能夠以這個參照位置為起始位置,傳回指定位移後的資料。

下面的例子,是 1 月到 6 月的業績,每個月的業績可以再拆分到 A 或 B 產品。下面黑色框格的範例,就是我寫公式抓取的資料。

範例一要抓的數字是六月的業績 Jun Sales,對照公式內容,是以黃色格子 (A2) 為參照向下位移 0 列,向右位移 6 欄,所以抓到 40,000 這個數字。

範例二要抓的是二月 A 產品的業績 Feb A Sales,一樣以黃色格子 (A2) 為參照向下位移 1列,向右位移 2欄,所以抓到 5,000 這個數字。

offset 基本說明

當然我們也可以直接一格一格去對應,像是在範例一的結果寫=G2,但是在月份改變的時候就要去手動調整格子,需要抓的數字一多很容易會對應錯,但使用 offset 公式就只需要更改位移的數字就可以了,還有一個方便之處,如果這張表持續更新到 12 月,一樣是更改位移的數字,這個公式都還是可以繼續使用。

寫到這邊,實際在 Excel 中操作一次,大家應該都能夠大致理解 offset 的用法了,如果遇到任何問題,歡迎隨時提問。那接下來,我就要再介紹更進階的用法,也能夠搭配其他函數混用,讓應用更加靈活。

延伸閱讀:《Excel自學聖經:從完整入門到職場活用的技巧與實例大全》想要自學 Excel 嗎?帶你從八個面向完整學習相關應用

Offset 進階應用

這個部分的應用就稍微複雜一些,以下三個範例都有一個共通點,就是利用特定欄位來控制選擇的月份,只要將公式寫好,在 B10 這格填入月份就可以快速更新成我們要的結果,我知道這樣說很抽象,就直接來看範例說明吧!

offset 進階應用

Offset 應用

範例三 範例四 一樣是用 offset 抓取業績 sales 和 A 產品業績 A Sales的資料,可以從公式內容看到,跟前面的例子不同的是,在公式中「向右位移」這個參數,改成用 B10 (黃色格子) 來控制月份,只要填入月份,結果就會快速變成指定月份的營收。

範例三 = OFFSET(A2,0,B10)
範例四 = OFFSET(A2,1,B10)

Offset 與 Sum 的進階應用

範例五 延續前面用 B10 來控制月份的概念,範例五再結合了 sum 函數加總的概念,可以直接計算累積到 B10 指定月份的業績。

範例五 =SUM(B2:OFFSET(A2,0,B10))

SUM (加總起始儲存格 : 加總結束儲存格)
SUM (B2: G2)

要加總 1 到 6 月,就會用上面這個寫法,而我們這個進階範例,就是把冒號後面加總結束的儲存格,利用 offset 來運算,配合 B10 來控月份制,就可以快速的加總整年的業績。

講的白話一點,offset 其實就是數格子的概念,大家也可以想一想,能不能也套用到自己的工作上。Excel 函數的應用千變萬化,如果有任何有趣的發現也歡迎跟我分享。

結語&常用函數

這是第一次寫比較正式的 Excel 函數介紹,其實我覺得學習函數,重點並不是要學很多不同的函數,而是要去練習如何運用。

在遇到問題時,有能力去思考、尋找可以解決問題的函數,才是關鍵;至於沒學過的函數,其實也可以靠關鍵字搜尋,來找到一些線索,運氣好甚至可以找到別人解好的答案。我自己也是一路這樣學習過來的,與大家共勉之。

以下幫大家整理常用的函數,供大家參考:
Sum, Average, Subtotal
Min, Max
Vlookup, Hlookup
Offset
Match
Index
If, Iferror
Sumif, Sumifs
Countif, Countifs
Left, Right, Mid
Round

歡迎留言告訴我你有興趣了解的函數,有機會成為我下一篇文章的主題 😀

▶️ 免費下載本篇 Offset 範例檔請點此

如果需要 Excel 教學Excel檔案優化、Excel 客製化諮詢等服務,歡迎直接聯絡我 misac@misachen.com

在〈Excel 進階|Offset 函數應用〉中有 1 則留言

  1. 自動引用通知: Excel 函數|如何思考公式邏輯:Countif + Offset | 人生投資旅誌

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Scroll to Top