2017年10月17日 星期二

Excel-新增一列資料時自動調整公式

網友提問:在 Excel 的工作表中,如何在不斷新增的儲存格範圍中找出最大值,並且當最後一個數就是最大值時,於最大值上標示紅色前景色彩,而且當不斷插入新的資料時有效。
如下圖,儲存格B2:B15裡的最大值是50(儲存格B15),所以儲存格B16標示為紅色。如何在新增資料時仍然適用原來公式?
Excel-新增一列資料時自動調整公式
由於 Excel 在你新增列或欄時,有自動調整公式的特性,如何克服這個問題?
在本例中,如果你使用:儲存格A16:=MAX(A2:A15),當你在15列和16列之間新增一列時,公式:=MAX(A2:A15)仍會維持不變。但這不是你要的。
改用以下公式:
儲存格A16=MAX(OFFSET(A2,0,0,ROW(A16)-2,1))
複製儲存格A16,貼至儲存格A16:G16。
OFFSET(A2,0,0,ROW(A16)-2,1)用以找出目前16列以上的儲存格範圍,再以 MAX 函數取儲存格範圍內的最大值。
公式自動變更如下:
Excel-新增一列資料時自動調整公式
儲存格A17:=MAX(OFFSET(A2,0,0,ROW(A17)-2,1))
使用此公式,便形成了一個動態的儲存格範圍。
接著,來設定當最後一個數就是最大值時,於最大值上標示紅色前景色彩。
選取儲存格A17:G17。在[編輯格式化的規則]對話框中設定:
類型:使用公式來決定要格式化哪些儲存格
規則:=A17=OFFSET(A17,-ROW(1:1),0)
格式:紅色前景色彩
在新增一列時,OFFSET(A17,-ROW(1:1),0)永遠對應到儲存格的上一個儲存格。
Excel-新增一列資料時自動調整公式

沒有留言:

張貼留言

檢視其他文章

好康東東