贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

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

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (5)

發表留言
  • 悄悄話
  • 悄悄話
  • 婷婷
  • 你好,不曉得你的講義中有沒有教人可以利用下拉式選單,一次選擇相同屬性的資料,比如第一欄下拉式選單選擇:藍色,第二欄的二、三、四列會自動帶入:藍色上衣、藍色裙子、藍色褲子。請問有方法可以做出這樣的表單嗎?如果有的話,還請不吝賜教,謝謝!!
  • 請參考:http://isvincent.pixnet.net/blog/post/45727462

    vincent 於 2015/12/30 23:12 回覆

  • 悄悄話
  • 悄悄話

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼