-
1 # 精進Excel
-
2 # 雷哥office
今天雷哥給大家詳細講解下Excel中的offset函式。
1.Offset 函式語法
=OFFSET(reference, rows, cols, [height], [width])
微軟官方引數解釋：① Reference 必需。 要作為偏移基準的參照。② Rows 必需。 需要左上角單元格引用的向上或向下行數。 rows =5，可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數（這意味著在起始引用的下方）或負數（這意味著在起始引用的上方）。③ Cols 必需。 需要結果的左上角單元格引用的從左到右的列數。 使用 5 作為 cols 引數，可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。④ 高度 可選。 需要返回的引用的行高。 Height 必須為正數。⑤ 寬度 可選。 需要返回的引用的列寬。 Width 必須為正數。看了這些解釋，大家是不是還是一頭霧水呀？ 正所謂字不如表，表不如圖。大家請看下圖：
場景一：=offset( B3, 4, 3, 2, 3) 表示的含義見下圖，以B3單元格為基準，向下移動4行，向右移動3列，返回2行3列的區域的資料。
場景二：=offset( B3, -4, 3, 3, 2) 表示的含義見下圖，以B3單元格為基準，向上移動4行，向右移動3列，返回3行2列的區域的資料。
場景三：=offset( B3, -4, -3, 3, 2) 表示的含義見下圖，以B3單元格為基準，向上移動4行，向右移動3列，返回3行2列的區域的資料。
學完了這3個場景的案例，相信大家對offset函式一定有了較深入的瞭解。
下面我們再來學習下offset函式的具體應用場景~
2. Offset函式實戰場景案例1：求出 =offset(A1,3,2)的值
解析
=offset(A1,3,2)，發現引數四和五省略了，當引數四和五引數為1時，可以省略不寫。因此該函式的完整引數為 =offset(A1,3,2,1,1)，表達的含義為 以A1單元格為參考，向右移動3行，想下移動2列，返回1個單元格的內容。因此結果為23。案例2：求出引用區域的和
求出=SUM(OFFSET(A1,1,3,5,1))值
解析：
=SUM(OFFSET(A1,1,3,5,1))，表達從A1單元格開始，向下移動1個單元格，向右移動3個單元格，返回5行1列資料。因此 =SUM(OFFSET(A1,1,3,5,1)) 表達的就是銷售額總和，數值大小為892.案例3：行列轉置
如圖所示，多行資料需要轉化為1列資料，如何實現呢？
方法
=OFFSET($A$1,(ROW(A1)-1)/3,MOD(ROW(A1)-1,3))&""
總結本文給大家講解了Offset函式的用法和典型的案例場景，大家學會了嗎？
-
3 # 雷鳥365
MATCH函式教程
說明
在單元格中搜索指定項，然後返回該項在單元格區域中的相對位置。
示例
MATCH("Sunday",A2:A9,0)
語法
MATCH(lookup_value,lookup_array,[match_type])
lookup_value
要在 lookup_array 中匹配的值。
lookup_array
要搜尋的單元格區域。 如果所用的範圍的高度和寬度均大於1，MATCH將返回#N/A!。
match_type-[可選]
[可選 - 預設值為1] - 要採用的搜尋方式。 1為預設型別，此時MATCH會假設範圍已按升序排序，並返回小於等於搜尋鍵值的最大值。 0表示完全匹配，在範圍未排序的情況下需要使用此方式。 -1讓MATCH假設範圍是按降序排序的，並返回大於等於搜尋鍵值的最小值。
實戰
https://www.leiniao365.com/work/table/26134483
回覆列表
OFFSET函式確實是一個非常厲害的函式，他在下拉選單、動態圖表、動態引用等操作中，具有不可替代的作用，本文讓你從入門到精通完全掌握OFFSET函式。
001 OFFSET 函式初識OFFSET函式的功能為以指定的引用為參照系，透過給定偏移量得到新的引用，返回的引用可以為一個單元格或單元格區域，其語法為：OFFSET(reference, rows, cols, [height], [width])，翻譯過程中文就是：OFFSET（參照,偏移行,偏移列,行高,列寬）
▌案例
如圖A1:D7是一個數據區域，我們在這個資料區域中，以A1單元格為參照，返回一個新的單元格。
在A9單元格寫入公式=OFFSET(A1,4,3,1,1)含義是：以A1單元格為參照，向下偏移4行，向右偏移3列，返回一個1行、一列的單元格區域，即D5.如果函式的最後兩個引數為1，可以省略，預設為返回一行或一列或一個單元格；如果都不為1，則返回的是一個區域。
▌案例
公式=OFFSET(C2,5,3,4,3) 就是以C2為基點，向下偏移5行，向右偏移3列，新引用的行數是4行，新引用的列數是3列，最終得到對F7:H10單元格區域的引用，如圖所示：
OFFSET函式如果是引用一個區域，則需要以陣列公式的方式輸入，以公式=OFFSET(C2,5,3,4,3) 為例：
①輸入公式之前選中一個4行3列的區域
②輸入公式=OFFSET(C2,5,3,4,3)，然後按Ctrl、Shift、Enter輸入公式
當然，僅僅是引用區域，是沒有太大用處的，OFFSET函式的神奇之處在於，透過引用構造動態區域，從而完成複雜的資料彙總、高階動態圖表、多級下拉選單等！
002 OFFSET 應用1-動態求和區域▌案例
如圖所示，根據AB兩列的個月的銷售資料，求最近三個月的銷售額之和，要注意的是AB兩列的資料時會變動的，也就是說過一個就增加一個月的資料。
在D3單元格寫入公式
=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))
▌解讀
=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))
用滑鼠選中OFFSET函式部分，然後按F9，可以預覽結果，OFFSET函式返回的就是最近三個月的銷售額。
▌解讀
=SUM(OFFSET(A1,COUNTA(A:A)-3,1,3))
①COUNTA(A:A)
是求A列非空單元格的數量，用這個數量-3，就得出來了最近三個月資料的起始位置。
②OFFSET(A1,COUNTA(A:A)-3,1,3)
就是以A1單位格為參照，向下偏移COUNTA(A:A)-3行，向右偏移1列，然後行數為4行一列的區域，這個區域如圖所示：
對這個區域求和，的出來的結果正是最近三個月的銷售額之和
003 OFFSET 應用2-二級下拉選單▌案例
如圖所示，根據AB兩列的城市列表，在黃色區域設定二級下拉選單，即在黃色區域選擇不同的省份，城市下拉選單中出現的是對應省份的城市。
▌步驟
Step1：設定一級下拉選單
一級選單很簡單，多數同學應該都會了，我直接放上結果：
Step2：建立二級選單名稱
②在【新建名稱】對話方塊中，【名稱】處填寫“二級選單”
引用位置填寫如下公式：
=OFFSET($A$1,MATCH($D$3,$A$2:$A$12,0),1,COUNTIF($A$2:$A$12, D$3),1)
Step3：設定二級下拉選單
①將滑鼠定位於E3單元格