7.5 查詢函式：OFFSET

OFFSET 的查詢與前幾節不同，其輸出會是某個儲存格或某個範圍，其查詢的方式是以某個儲存格為基準點，向上下左右移動得到要查詢的範圍，說明如下：

以下先使用一些簡單的範例說明之：

1. 判斷以下公式會輸出的儲存格範圍

   公式	(1) =OFFSET(A1,2,1) (2) =OFFSET(A1,-1,0) (3) =OFFSET(H8,-4,-3) (4) =OFFSET(A1,2,1,3,2) (5) =OFFSET(H8,-4,-3,3,2)
   解說	(1) A1 往下移 2 列、往右移 1 欄，會輸出 B3 的值。 (2) A1 往上移 1 列，但已超出工作表範圍，會輸出 #REF!。 (3) H8 往上移 4 列、往左移 3 欄，會輸出 E4 的值。 (4) A1 往下移 2 列、往右移 1 欄後，輸出 3 列及 2 欄的內容，會輸出 B3:C5 的值。 (5) H8 往上移 4 列、往左移 3 欄後，輸出 3 列及 2 欄的內容，會輸出 E4:F6 的值。

了解 OFFSET 的運作方式後，接下來說明 OFFSET 經常使用的時機：

• 搭配 SUM / COUNTIF / SUMIF 等函式，加總 / 計算特定範圍
• 搭配 MATCH，用於欄 / 列轉置的範圍查詢

以下使用一個進階的範例說明上述兩者的用法：

2. 計算丁公司二月到四月的金額加總
   

   公式

   =SUM(OFFSET(A1,MATCH("丁",A:A,0)-1,MATCH("二月",A1:G1,0)-1,1, MATCH("四月",A1:G1,0)-MATCH("二月",A1:G1,0)+1))

   解說

   在此可以想像成要製作一個搜尋器，讓使用者輸入公司 ("丁")、起始月份 ("二月")、結束月份 ("四月")，因此接下來會使用 SUM + OFFSET + MATCH 完成這個需求，因為範例較為複雜，以下分成幾個步驟說明： 丁公司二月到四月的總金額：=SUM(OFFSET(A1,4,2,1,3)) 可以使用 OFFSET 決定要加總的範圍，在此以 A1 為參照，已知丁在第五列所以下移 4 列，二月到四月可以用 OFFSET 以 A1 為起始點右移 2 欄、選取 3 欄得到。 丁公司：4 ⭢ MATCH("丁",A:A,0)-1 偏移列數可以使用 MATCH 取代，尋找 "丁" 出現在第幾列，但偏移列數要 -1 因為參照的 A1 已經是第一列，也可以直接用 MATCH("丁",A2:A,0) 代替之。 二月：2 ⭢ MATCH("二月",A1:G1,0)-1 偏移欄數也可以使用 MATCH 取代，尋找 "二月" 出現在第幾列，但偏移列數要 -1 因為參照的 A1 已經是第一列，也可以用 MATCH("二月",B1:G1,0) 代替之。 二月到四月：3 ⭢ MATCH("四月",A1:G1,0)-MATCH("二月",A1:G1,0)+1 決定寬度可使用兩個 MATCH 分別計算 "四月"、"二月" 所在的欄數，兩者相減後要 +1 因為二月、四月都要算入，和 3/10~3/21 共有 21-10+1=12 天一樣的道理。

依據上述步驟完成後，能進一步使用資料驗證搭配此公式使用，製作成搜尋器，讓公式更靈活的運用，將會在第 7.8 節示範到。