錦子老師

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

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

首先，咱們說說這個函數的作用：

如果使用下面這個公式：

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

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

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

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

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

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

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

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

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

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

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

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

點取「確定」鈕。

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

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

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

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

點取「關閉」鈕。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

部落格相關範例