EXCEL中OFFSET函數(shù)的實用技巧
EXCEL中有大量的函數(shù),熟悉函數(shù)的用法,可以設(shè)計出許多實用的公式,大幅提高辦公效率。本篇主要介紹下OFFSET函數(shù)的功能和一些實際用法。 OFFSET函數(shù)的基本用法 OFFSET函數(shù)是引用函數(shù),
EXCEL中有大量的函數(shù),熟悉函數(shù)的用法,可以設(shè)計出許多實用的公式,大幅提高辦公效率。本篇主要介紹下OFFSET函數(shù)的功能和一些實際用法。
OFFSET函數(shù)的基本用法
OFFSET函數(shù)是引用函數(shù),根據(jù)指定的偏移量返回引用,此函數(shù)有五個參數(shù),第一個參數(shù)是參照系,以此為基準(zhǔn)進行偏移,第二個參數(shù)是偏移行數(shù),第三個參數(shù)是偏移列數(shù),第四個參數(shù)是返回區(qū)域的行數(shù),第五個參數(shù)是返回區(qū)域的列數(shù)。
如公式:OFFSET(C1,5,2,1,1)
,就是以C1為參照,向下偏移5行,到第6行,向右偏移兩列,到E列,1行高1行寬,合起來就是E6單元格。
前三個參數(shù)必須有,不可省略,第四、第五個參數(shù)可以省略,省略后表示和參照系相同的行或列數(shù)。如:OFFSET(C1,5,2)
與上面的公式是相同的。
偏移的行列數(shù),可正可負,正表示向下向右偏移,負表示向上向左偏移,如:OFFSET(E10,-4,-2)
,即表示返回C6單元格的引用。
實際應(yīng)用示例
了解了函數(shù)的基本用法,就可以來看實際應(yīng)用。舉個例子,從列表中每隔三行提取出一個姓名出來:OFFSET($B$2,(ROW(1:1)-1)*4,0)
,向下填充時,行號ROW(1:1)
會依次增加,行號每增加1,偏移量增加4行,所以公式中用了*4來增加偏移量。
如果要同步提取出各科的分?jǐn)?shù),依次增加列的偏移量即可,公式為:OFFSET($B$2,(ROW(1:1)-1)*4,COLUMN(A:A)-1)
。
當(dāng)然這里列只是依次增加一列,并沒有跳躍式增加,向右填充時,可以改變參照系,而不增加列的偏移量,公式為:OFFSET(B$2,(ROW(1:1)-1)*4,0)
。
結(jié)合其他函數(shù)的使用
結(jié)合其他函數(shù),根據(jù)指定的條件返回交叉點的數(shù)據(jù),如根據(jù)學(xué)號和科目返回成績:OFFSET(A1,MATCH(H2,A2:A19,0),MATCH(I1,B1:E1,0))
,使用MATCH函數(shù)返回各條件在相應(yīng)的行、列中的次序,作為OFFSET函數(shù)的偏移量參數(shù),從而返回需要的結(jié)果。
如果是多人多科目,只要將引用的行列加上相應(yīng)的絕對引用符就可以了:OFFSET($A$1,MATCH($H2,$A$2:$A$19,0),MATCH(I$1,$B$1:$E$1,0))
。
其他實用技巧
求表中某科目中最后幾人的平均成績(是表中最后幾人,不是成績的后幾名):AVERAGE(OFFSET(C1,COUNTA(C:C)-I1,I1))
。可以直接用平均值函數(shù)驗證下:AVERAGE(C15:C19)
。不管增加或刪除記錄,始終是返回指定數(shù)量的均值。
前幾個人中,某科目90分及以上的人數(shù):COUNTIF(OFFSET(C1,,I1),"gt;90")
,COUNTIF函數(shù)的第一參數(shù)必須為單元格或區(qū)域,除了直接引用外,可以接受OFFSET函數(shù)返回的引用區(qū)域。
以上是OFFSET函數(shù)的一些常用實例,結(jié)合其他函數(shù),可以解決一些比較復(fù)雜的問題,但萬變不離其宗,搞清楚幾個參數(shù)的意義,就可以生成想要的引用區(qū)域。