如下圖,在 Excel 中的工作表有一個數據清單,如何根據指定的月份、起始項目、終止項目求得此區間的總和?
本例試著使用 OFFSET 函數和 SUMPRODUCT 函數來處理。
【公式設計與解析】
選取單元格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)
單元格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))
設定格式:單元格背景色彩為粉紅色、單元格前景色彩為紅色。