Excel如何動態擷取資料,同時繪出所需的圖表

Excel Offset 動態擷取資料

如果你在Excel中有了多筆的數值資料,但要繪出圖表時,需要依照某些條件來擷取資料顯示,而非單純的顯示「所有」的資料。那要如何做呢?這邊就來分享一下我自己的作法,提供給大家參考。

 

 

這篇會分享兩個部份:

  1. 如何動態擷取資料
  2. 如何繪製這個動態資料

 

如何動態擷取資料

更具體的說,如果我有了A商店的每日銷售額,但我在顯示圖表時,例如我只想要看到2020.10.01~2020.10.31的月完整資料,這樣的話,我要如何擷取這個數值區間呢?會使用到的EXCEL函數為OFFSET,來看看Microsoft上這個函數的說明。

 

OFFSET(reference, rows, cols, [height], [width])

 

OFFSET 函數語法具有下列引數:

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

 

以上是Microsoft官網的說明,我們再搭配以下的圖片來說明一下。Offset擷取資料的方式,主要就是透過:

  1. 先找到一個最初使的「參考點」(上面的Reference)
  2. 然後動態的給予Rows與Columns,去決定真正要擷取資料的「資料起始點」
  3. 最後再告訴函數,所要擷取的範圍(高度與寬度)是多大

Excel Offset 動態擷取資料

 

如果我們要擷取的資料是藍色 資料起始點起算到F17為止的資料範圍,那我們要如何表示呢?

公式就是「=OFFSET(B2, 9, 2, 6, 2)」

如果我們要擷取的資料是藍色 資料起始點起算到F17為止的資料範圍

公式就是「=OFFSET(B2, 9, 2, 6)」

 

透過以上的方式,我們就能夠利用函數OFFSET來定義出來。接著來看看怎麼將這個動態資料套用到圖表上。


 

 

如何繪製動態資料

回到目錄清單↑

在這個部份,會有兩個步驟:

  1. 將公式定義於一個代替的名稱A
  2. 在圖表中的資料,使用這個建立的名稱A

 

將公式定義於一個代替的名稱

# 新增一個公式名稱

# 於公式名稱中指定OFFSET函數

【分享一下】

在這個步驟中,我失敗了很多次,原因就在OFFSET公式中的「Reference」參考點。這個參考點,一開始我是使用其他函數動態計算出來的,這樣會不斷的出現公式的錯誤訊息。最後是將「Reference」參考點固定在某個位置,例如B2,然後透過動態指定Rows與Columns數值的方式來定義「資料起始點」。基本上,這樣也符合這個公式的設計使用方式。

所以統整一下上面這段,主要就是說:

  1. 第一個「Reference」參考點必須給定一個固定、明確的座標
  2. 之後再透過Rows與Columns的變化來達到描述「資料起始點」的目的

 

在圖表中使用上述建立的公式名稱資料

# 插入圖表,同時在圖表上「以滑鼠按右鍵」→「選取資料」

# 點選左邊的「座標軸」的「編輯」以及右邊「資料軸」的「編輯」分別定義動態範圍的資料。「座標軸」指的就是上面範例中的日期,因為你會指定一個時間區間,因此時間也只是一個限定的範圍(例如2020.09.01~2020.09.30),而不是所有資料的全部時間序列。「資料」則是所要顯示的資料,同樣也只會顯示你所想要顯示的時間區間。

# 點選上面圖片中的「編輯」後,就會進入這個視窗畫面。這裡就是指定上面步驟所定義「公式名稱」的位置,輸入完成後按確定,圖表就可以依照自己想要的範圍繪製了。

 

大功告成!

 


【Excel相關參考資料】

  • Excel中如何查詢數列中的最大數值,並找出相對應的代號
  • Excel如何動態擷取資料,同時繪出所需的圖表

 

 

 

※※※ 歡迎加入粉絲團 → sya的旅遊部落格 ※※※

 

 

發佈留言

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

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料