贊助廠商

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

搜尋本部落格文章資料

(網友提問)如下圖,在 Excel 中的工作表有一個資料清單,如何根據指定的月份、起始項目、終止項目求得此區間的總和?

本例試著使用 OFFSET 函數和 SUMPRODUCT 函數來處理。

Excel-指定範圍計算總和(OFFSET,MATCH,SUMPRODUCT,ROW,COLUMN)

 

【公式設計與解析】

選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目。

選取儲存格B1:G1,按 Ctrl+F3 鍵,開啟「名稱管理員」,新增名稱:月份。

選取儲存格B2:G23,按 Ctrl+F3 鍵,開啟「名稱管理員」,新增名稱:數值。

 

1. 計算區間的總和(OFFSET)

儲存格J4:=SUM(OFFSET(A1,MATCH(J2,項目,0),MATCH(J1,月份,0),
MATCH(J3,項目,0)-MATCH(J2,項目,0)+1,1))

(1) MATCH(J2,項目,0)

找出儲存格J2在項目陣列中的位置,傳回一個數值。(本例傳回9)

(2) MATCH(J1,月份,0)

找出儲存格J1在月份陣列中的位置,傳回一個數值。(本例傳回4)

(3) MATCH(J3,項目,0)-MATCH(J2,項目,0)

找出儲存格J3在項目陣列中的位置,傳回一個數值。(本例傳回19)

再和第(1)式相減,傳回一個數值。(本例傳回10)

將第(1)式、第(2)式、第(3)式代入 OFFSET 函數中,求得符合要求的儲存格範圍。

最後以 SUM 函數求得總和。

 

2. 計算區間的總和(SUMPRODUCT)

Excel-指定範圍計算總和(OFFSET,MATCH,SUMPRODUCT,ROW,COLUMN)

儲存格J4:=SUMPRODUCT((COLUMN(數值)=MATCH(J1,月份,0)+1)*
(ROW(數值)>=MATCH(J2,項目,0)+1)*(ROW(數值)<=MATCH(J3,項目,0)+1)*數值)

(1) 條件一:(COLUMN(數值)=MATCH(J1,月份,0)+1)

判斷儲存格數值陣列每個儲存格的『欄號』是否等於儲存格J1在月份陣列中所在位置。傳回 TRUE/FALSE 陣列。

(2) 條件二:(ROW(數值)>=MATCH(J2,項目,0)+1)

判斷儲存格數值陣列每個儲存格的『列號』是否大於或等於儲存格J2在項目陣列中所在位置。傳回 TRUE/FALSE 陣列。

(3) 條件三:(ROW(數值)<=MATCH(J3,項目,0)+1)

判斷儲存格數值陣列每個儲存格的『列號』是否小於或等於儲存格J2在項目陣列中所在位置。傳回 TRUE/FALSE 陣列。

SUMPRODUCT 函數代入以上三個條件,其中『*』運算相當於執行邏輯 AND 運算,傳回 TRUE/FALSE 陣列。

 

3. 設定格式化的條件顯示指定區間

若指定起始項目和終止項目,如何在原始資料清單中標示這個範圍呢?

先選數值範圍,再新增格式化的條件:

規則類型:使用公來決定要格式化哪些儲存格

輸入規則:=(MATCH($J$1,月份,0)+1=COLUMN(B2))*(MATCH($J$2,項目,0)+1<=
ROW(B2))*(MATCH($J$3,項目,0)+1>=ROW(B2))

設定格式:儲存格背景色彩為粉紅色、儲存格前景色彩為紅色。

Excel-指定範圍計算總和(OFFSET,MATCH,SUMPRODUCT,ROW,COLUMN)

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

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 悄悄話

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼