388

圖文實例詳解OFFSET函數的使用方法

示範檔

範例檔

388.XLSX

結果檔

說起OFFSET函數,真的是像霧像雨又像風,從複雜的資料匯總、到樞紐分析表再到高級動態圖表,都離不開OFFSET函數的默默付出。

通過OFFSET函數,可以生成資料範圍的動態參照,這個參照再當作半成品,通過後續的處理加工,就可以為圖表和透視表提供動態的資料來源、為其他函數生成特定的參照了。

首先,咱們說說這個函數的作用:

OFFSET

查閱與參照

功能:傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。傳回的參照可以是單一儲存格或一個儲存格範圍。您可以指定要傳回的列數和欄數。

語法:OFFSET(Reference, Rows, Cols, [Height], [Width])

參數: Reference:為必要參數。這是用以計算位移的起始參照。Reference必須參照一個儲存格或相鄰的儲存格範圍,否則 OFFSET 會傳回 #VALUE! 的錯誤值。
Rows:為必要參數。這是要左上角儲存格往上或往下參照的列數。使用5做為Rows參數指出參照的左上角儲存格是Reference下方的第五列。Rows可以是正數(表示在起始參照下方)或負數(表示在起始參照上方)
Cols:為必要參數。這是要結果的左上角儲存格向左或向右參照的欄數。使用5作為Cols參數,指出參照位址的左上角儲存格是Reference右方的第五欄。Cols可以是正數(表示在起始參照右方)或負數(表示在起始參照左方)
[高度]:為選擇性參數。這是要傳回參照的列數高度。Height必須是正數。
[寬度]:為選擇性參數。這是要傳回參照的欄數寬度。Width
必須是正數。

注意: 如果RowsCols位移參照超出工作表的邊界,OFFSET會傳回#REF!的錯誤值。
如果省略HeightWidth,則假設高度或寬度與Reference相同。
OFFSET 不會實際移動任何儲存格,或變更選取範圍,它只會傳回參照。OFFSET可與任何必須有參照參數的函數搭配使用。例如:SUM(OFFSET(C2,1,2,3,1))會計算31欄範圍(也就是儲存格C2下方1列及右方2)
的總值。

如果使用下面這個公式:

=SUM(OFFSET(C3,4,2,4,3))

就是以C3為基點,向下偏移4列,向右偏移2欄,新參照的列數是4列,新參照的欄數是3欄,最終得到對E7:G10儲存格範圍的參照並計算其總和。

image

 

當然,僅僅得到參照是沒啥用處的,咱們的目的是把OFFSET函數得到的參照作為一個半成品,再通過其他方法進行再加工。

簡單認識了OFFSET函數,咱們再用一個動態圖表的製作,來說說OFFSET函數的實際應用。

OFFSET函數和動態圖表之間,屬於是「見不得人」的關係。

所謂動態圖表,就是能根據指定的條件,自動變化圖表資料來源,使圖表能夠按照我們指定的規則,動態顯示資料中重點關注的部分。

說「見不得人」,是指不能在圖表中直接使用OFFSET函數,而是要將OFFSET函數首先定義成名稱,然後在圖表中使用自訂名稱作為資料來源。

下面這個題目作為我說明的範例:

這個銷售流水記錄中,每天都要不斷的添加銷售資料。現在要製作一個圖表,僅展示最近7天的銷售狀況。

image

 

首先,點取「公式 > 己定義之名稱 > 定義名稱下拉方塊 >定義名稱」指令,開啟「新名稱」對話方塊。

點取「名稱」欄位,輸入「日期」字串。

點取「參照到」欄位,輸入「=OFFSET($A$1,COUNT($A:$A),0,-7)」字串,如下圖所示。

image

 

點取「確定」鈕。

點取「公式 > 己定義之名稱 > 定義名稱下拉方塊 >定義名稱」指令,開啟「新名稱」對話方塊。

點取「名稱」欄位,輸入「銷售額」字串。

點取「參照到」欄位,輸入「=OFFSET($B$1,COUNT($A:$A),0,-7)」字串,如下圖所示。

image

 

點取「公式 > 己定義之名稱 > 名稱管理員」圖示,可以看到建立了那些名稱,如下圖所示。

image

 

點取「關閉」鈕。

說說定義名稱日期這個公式的意思:

COUNT函數對A欄數值計數,結果作為OFFSET函數的列偏移參數。

OFFSET函數以A1為基點,向下偏移的列數是COUNT的結果,也就是A欄有多少個數值,就向下偏移多少列。

這時候就相當於到了A欄數值的最後一行,給定的偏移列數是0,新參照的行數是-7,得到從A欄數值的最後一列開始,向上7列這樣一個動態的範圍。

如果A欄的數值增加了,COUNT函數的計數結果就增加了,OFFSET函數的列偏移參數也就隨之變化。

就相當於一竿子捅到底,然後來個燒雞大窩脖兒,向上參照用7列,所以得到的始終是最後7列的參照。

接下來,按下圖步驟插入一個直條圖。

image

 

點取圖表一下,按滑鼠右鍵一下,點取「選取資料」指令,如下圖所示。

image

 

在「選取資料來源」對話方塊,點取圖例項目區「編輯」鈕,如下圖所示。

image

 

在「編輯數列」對話方塊,點取「數列名稱」欄位,輸入「=工作表2!$B$1」字串。

點取「數列值」欄位,輸入「=工作表2!銷售額」字串,如下圖所示

image

 

點取「確定」鈕,回到「選取資料來源」對話方塊。

點取水平座標軸標籤區「編輯」鈕,如下圖所示。

image

 

在「座標軸標籤」對話方塊,點取「座標軸標籤範圍」欄位,輸入「=工作表2!日期」字串,如下圖所示。

image

 

點取「確定」鈕,回到「選取資料來源」對話方塊。

image

 

點取「確定」鈕,回到工作表2

設置資料數列的系列值為:「=工作表2!銷售額

這裡的Sheet2,是資料來源工作表的名稱。銷售額,就是定義的名稱。

設置軸標籤的區域為:「=工作表2!日期

OK了,以後我們只要不斷的在資料來源表格中添加資料,這個圖表就始終顯示的是最近7天的資料變化了,如下圖所示。

image

 

最後別忘了,再對圖表進行簡單的美化,收工了——

今天只是講了OFFSET函數的基本用法,其實她還有很多更加複雜的引用方式,需要咱們一點點的學習領悟。

 

部落格相關範例

2020.12.19

Q5函數應用

2019.11.22

WEB-FILTERXML

2019.10.11

2234Excel跨工作表二層下拉式選單

2019.09.29

2219統計同列儲存格中各區間數值數量-COUNTOFFSETSUMPRODUCTCOUNTIFCOLUMN函數

2019.09.27

2238Excel 求不連續資料顯示方法

2019.09.21

2227Excel搜尋特定欄,若包含該值則複製整列資料-IFERRORIFMAXCOUNTIFOFFSETROWCOLUMNMATCH函數

2019.09.21

2208Excel函數用法-COUNTCOUNTIFIFIFERRORMATCHMIDVLOOKUPOFFSET

2019.09.20

2208Excel函數用法-COUNTCOUNTIFIFIFERRORMATCHMIDOFFSETRIGHTVLOOKUP

2019.07.13

2137Excel 自動判斷缺少部分插入12

2019.07.12

2137Excel 自動判斷缺少部分插入空白列

2019.07.12

2136如何複製台鐵網頁資料並貼在同一儲存格內

2019.01.10

OFFSET函數

2018.12.01

1065EXCEL 注塑機問題統計表製作-日期BY機台

2018.11.15

1045Excel 如何用不同工作天進行加總及平均-通用版

2018.08.02

856多條件篩選資料-使用IFERROROFFSETSMALLROW函數

2018.07.15

839增加記錄不需要重新計算-SumproductOffsetCount函數

2018.05.23

Q129VLOOKUP與合併儲存格(不同格數合併,5格內)

2018.04.30

Q4拖拉方塊應用

2017.09.30

346使用Excel函數計算最近7天平均銷售額的動態區域計算

2017.05.24

388圖文實例詳解OFFSET函數的使用方法

 


arrow
arrow

    錦子老師 發表在 痞客邦 留言(2) 人氣()