網友想要製作一個 Excel 的動態圖表(如下圖),圖表的內容是依指定的範圍來顯示內容。也就是要將一個靜態圖表轉換為動態圖表,該如何處理?

Excel-依指定範圍顯示圖表內容(建立動態圖表)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

 

【公式設計與解析】

先觀察:

如果你將數據製成一個統計圖,當你選取圖表中的數列時,會看到其對應的公式如下圖。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

本例中的公式:=SERIES(,'DATA '!$A$2:$B$8,'DATA '!$C$2:$C$8,1)

因此,如果要做一個動態圖表,則必須改變其中的參數,使其固定的位址改成動態位址。

所以,先建立二個動態範圍,利用 OFFSET 函數來建立名稱範圍。

view1:=OFFSET(DATA!$B$1,DATA!$F$1,0,DATA!$H$1-DATA!$F$1+1,1)

view2:=OFFSET(DATA!$C$1,DATA!$F$1,0,DATA!$H$1-DATA!$F$1+1,1)

此處要提醒,其中使用的位址必須要以完整的位址且要使用絶對參照方式。

例如:儲存格F1,要以 DATA!$F$1 表示,其中 DATA 是工作表名稱。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

接著,點選圖表中的數列,在公式中將參數改成:(活頁簿1 是目前的活頁簿檔案名稱)

=SERIES(,活頁簿1.xlsx!view1,活頁簿1.xlsx!view2,1)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

完成後,當你改變儲存格F1和儲存格H1的內容時,圖表也會隨之變動。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

 

【延伸學習】

如果想要將儲存A1:C23的內容依儲存格F1和儲存格H1而標示成紫色,該如何處理?

先選儲存格$A$2:$C$23,然後設定格式化的條件:

規則類型:使用公式來決定要格式化哪些儲存格

編輯規則:=(ROW(A2)>=$F$1+1)*(ROW(A2)<=$H$1+1)

格式設定:字型色彩設為「紫色」

Excel-依指定範圍顯示圖表內容(建立動態圖表)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

如果你覺得輸入數字太麻煩,你可以使用微調按鈕來輔助輸入數字。

在「開發人員」功能表中選取「表單控制項/微調按鈕」,並在工作表插入。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

設定控制項格式:(指定儲存格連結位址)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

如此,便可以使用微調按鈕來動態控制圖表顯示內容。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

【延伸閱讀】

Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel OFFSET
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()