在 Excel 中常會踫到，當增加統計資料的內容後，而需要重新調整統計圖的問題。
通常只要在 Excel 的工作表中新增或刪除某些儲存格後，Excel會自動的調整公式中的儲存格位址。但是這種好心腸，有時候反而造成困擾。
例如，以下圖片中的例子，如果在第13列新增一列，則第13列會自動變為第14列，但是公式計算和統計圖都要以第13列開始計算，如此，便形成了問題。
【公式設計與解析】
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. 統計圖表
如果是圖表，該如何處理，才不需要重新調整統計圖？
統計圖原公式 =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列相同。）
接著，修改公式：
每日平均數列的公式：=SERIES(,,統計!$C$13:$C$32,1)
修改後公式：=SERIES(,,統計!每日平均,1)
同理，動態平均數列的公式：=SERIES(,,統計!動態平均,2)
如此，每當在第13列新增一列時，統計圖不需要重新調整。
【延伸閱讀】
參考：本部落格中其他關於 Excel OFFSET 函數的應用
參考：本部落格中其他關於 Excel COUNT 函數的應用
留言列表