（網友提問）如下圖，在 Excel 中的工作表有一個資料清單，如何根據指定的月份、起始項目、終止項目求得此區間的總和？
本例試著使用 OFFSET 函數和 SUMPRODUCT 函數來處理。
【公式設計與解析】
選取儲存格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）
儲存格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))
設定格式：儲存格背景色彩為粉紅色、儲存格前景色彩為紅色。
留言列表