2015年12月28日 星期一

Excel-取固定數量小計(OFFSET,ROW,MOD)

讀者想要在以下的 Excel 工作表,每間隔 5 個數即計算和,其餘儲存格均顯示空白,該如何處理?若分組數不是 5 個,而是一個變數,公式該如何設計?(參考下圖)
Excel-取固定數量小計(OFFSET,ROW,MOD)

【公式設計與解析】
1. 分組數固定(本例為5)
儲存格B2:=IF(MOD(ROW(A2),5)=1,SUM(OFFSET(A2,0,0,-5,)),"")
複製儲存格B2:往下各列貼上。
MOD(ROW(A2),5)=2;MOD(ROW(A3),5)=2;MOD(ROW(A4),5)=4;
MOD(ROW(A5),5)=0;MOD(ROW(A6),5)=1;MOD(ROW(A7),5)=2;
...
OFFSET(A2,0,0,-5,):以某個儲存格為準,傳回向上5個儲存格範圍。(因為要5個一組)

2. 分組數不固定(在儲存格D2控制)
如果將分組數置於儲存格D2,則公式將上式中的 5 置換為儲存格D2即可。
儲存格B2:=IF(MOD(ROW(D2),$D$2)=1,SUM(OFFSET(A2,0,0,-$D$2,)),"")
複製儲存格B2:往下各列貼上。
在儲存格D2輸入分組數,即可依這個數量顯示小計。
Excel-取固定數量小計(OFFSET,ROW,MOD)

【加碼演出】
如果要依分組數,如何在小計欄位自動顯示較深的色彩格式?
先選取A欄和B欄中有資料的儲存格,設定格式化的條件如下:
規則類型:使用公式來決定要格式化哪些儲存格。
規則:=MOD(ROW(A2),$D$2)=1
Excel-取固定數量小計(OFFSET,ROW,MOD)
如此,便可以在小計欄位自動顯示較深底色。

沒有留言:

張貼留言

好康東東