如何查詢偏移量
一:OFFSET函式是什麼?
以指定的引用為參照系,透過給定的偏移量得到新的引用。指定的引用可以是一個單元格或單元格區域,返回的引用也是一個單元格或單元格區域。
例如將A1單元格按指定的偏移量(3,2)進行偏移,那麼會返回單元格C4。
另外也可指定返回的單元格區域。例如上面例子A1單元格偏移後,指定的返回單元格區域為兩行兩列,那麼返回值為單元格區域C4:D5。
二:OFFSET的語法結構是什麼?
語法結構為:OFFSET(reference,rows,cols,[height],[width])
也就是:OFFSET(參照區域,偏移行數,偏移列數,【高度】,【寬度】)
其中:
Reference(參照區域)是指參照系的引用區域,必須為某一單元格或單元格區域
Rows(偏移行數)是指從參照區域左上角單元格,往下或往上偏移的行數,其中正數表示往下,負數表示往上。
Cols(偏移列數)是指參照區域左上角單元格,往左或往右偏移的列數,其中正數表示往右,負數表示往左。
Height(高度)是指從偏移後的單元格開始往下選取的行數,必須為正數。
Width(寬度)是指從偏移後的單元格開始往右選取的列數,必須為正數。
當高度和寬度省略時,返回的區域與參照區域範圍相同。
三:OFFSET函式怎麼用?
【例1】
如何編寫公式從A1單元格開始,進行偏移返回E6單元格內容。
思考:
從A1到E6行數往下增加5行,列數往右增加4列,偏移量為(5,4),偏移後選取返回單元格E6,也就是零行零列的內容,可以將行高和列寬省略。
操作:
公式表達為“OFFSET(A1,5,4)”。
【例2】
若返回E6:G8的單元格區域呢?並求和呢?
思考:
偏移量不變,返回區域的行數為3,列數為3,也就是高度為3,寬度為3;關於求和,只需要用SUM對返回區域進行引用。
操作:
公式表達為“SUM(OFFSET(A1,5,4,3,3))”
【例3】
根據F2中輸入的姓名,求算該同學的三科總成績。
思考:
以同學丁為例,丁位於A5單元格,三科成績位於B5:D5單元格區域,也就是返回的單元格區域為B5:D5。
以A1單元格為參照區域,成績區域的首個單元格B5位於A1單元格下方4行,右方1列,偏移量為(4,1)。
無論求算任何同學的成績偏移列均為1,只需根據姓名動態變化偏移列即可,這裡運用MACTH函式來完成。
MATCH函式用來返回查詢值位於查詢區域的相對位置。這裡是A5單元格在A列中的相對位置,也就是5。由於偏移函式為4,所以需做減1處理。
操作:
在G2單元格輸入公式
“=SUM(OFFSET(A1,MATCH(F2,A:A,0)-1,1,1,3))”,回車
小結:
OFFSET函式作為EXCEL中特有的偏移函式,往往需要結合其他函式進行組合,從而達到資料引用或統計分析的結果。
內容最後,記得
關注一下
,持續獲得更多內容。