如下圖，在 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))
設定格式：單元格背景色彩為粉紅色、單元格前景色彩為紅色。
閱讀更多 老徐漫談 的文章
