在 Excel 中常會踫到,當增加統計資料的內容後,而需要重新調整統計圖的問題。

通常只要在 Excel 的工作表中新增或刪除某些儲存格後,Excel會自動的調整公式中的儲存格位址。但是這種好心腸,有時候反而造成困擾。

例如,以下圖片中的例子,如果在第13列新增一列,則第13列會自動變為第14列,但是公式計算和統計圖都要以第13列開始計算,如此,便形成了問題。

Excel-解決因增加統計資料而需要重新調整統計圖的問題(OFFSET,COUNT)

【公式設計與解析】

1. 公式運算

如果想要讓新增一列時不改變公式中的某些儲存格參照,則必須改變公式的寫法。

此時,會用到 INDIRECT 函數。例如:

原公式 儲存格B10:=(B13-B33)/(A13-A33)

調整為 儲存格B10:=(INDIRECT("B13")-B33)/(INDIRECT("A13")-A33)

如果在第13列之上新增一列,則產生公式變化:

原公式 儲存格B10:=(B14-B34)/(A14-A34)

調整為 儲存格B10:=(INDIRECT("B13")-B34)/(INDIRECT("A13")-A34)

 

2. 統計圖表

如果是圖表,該如何處理,才不需要重新調整統計圖?

Excel-解決因增加統計資料而需要重新調整統計圖的問題(OFFSET,COUNT)

統計圖原公式 =SERIES(,,工作表1!$D$13:$D$32,2)

新增一列公式 =SERIES(,,工作表1!$D$14:$D$33,2)

此時,若要改善這個問題,則要使用名稱定義。

假設工作表名稱為「統計」,定義名稱:

每日平均:=OFFSET(統計!$C$12,1,0,COUNT(統計!$C$12:$C$32),1)

動態平均:=OFFSET(統計!$D$12,1,0,COUNT(統計!$D$12:$D$32),1)

其中「COUNT(統計!$C$12:$C$32)」是要計算每日平均欄位中的資料數量。

其中「COUNT(統計!$D$12:$D$32)」是要計算動態平均欄位中的資料數量。

(這裡用了一個小技巧:第12列是沒有內容,而且被隱藏的。原因是新增一列時,才不會格式和第11列相同。)

Excel-解決因增加統計資料而需要重新調整統計圖的問題(OFFSET,COUNT)

接著,修改公式:

每日平均數列的公式:=SERIES(,,統計!$C$13:$C$32,1)

修改後公式:=SERIES(,,統計!每日平均,1)

同理,動態平均數列的公式:=SERIES(,,統計!動態平均,2)

Excel-解決因增加統計資料而需要重新調整統計圖的問題(OFFSET,COUNT)

如此,每當在第13列新增一列時,統計圖不需要重新調整。

 

【延伸閱讀】

參考:本部落格中其他關於 Excel OFFSET 函數的應用

參考:本部落格中其他關於 Excel COUNT 函數的應用

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

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

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