讀者想要在以下的 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)

如此,便可以在小計欄位自動顯示較深底色。

arrow
arrow
    全站熱搜

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