2017年12月30日 星期六

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

(網友提問)如下圖,在 Excel 中的工作表有一個資料清單,如何根據指定的月份、起始項目、終止項目求得此區間的總和?
本例試著使用 OFFSET 函數和 SUMPRODUCT 函數來處理。
Excel-指定範圍計算總和(OFFSET,MATCH,SUMPRODUCT,ROW,COLUMN)

【公式設計與解析】
選取儲存格A1:A2,按 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)

沒有留言:

張貼留言

檢視其他文章

好康東東