如何成為Excel大神—Offset函式

大家好，我是永不止步的老牛

今天我們來了解下Excel的Offset函式的實用用法

Offset函式的語法是：

Offset（reference， rows， cols， ［height］， ［width］），其中

Reference：表示要引用的

原點

，

是一個單元格，也可以是一個單元格區域

Rows：表示原點（Reference）的

行偏移數量

，

正數向下，負數向上，預設為0

Cols：表示原點（Reference）的列偏移數量，

正數向右，負數向左，預設為0

Height：表示返回

引用的高度

，

正數表示向下多少行，負數表示向上多少行，

預設為1，可以省略，不能為0

Width：表示返回

引用的寬度

，

正數表示向右多少列，負數表示向左多少列，

預設為1，可以省略，不能為0

什麼意思呢？

就是說你可以透過這個函式在指定一個原點(Reference)，然後往下偏移Rows行，再往右偏移Cols列，以這開始，返回Width列Height行的引用區域

我們用這張圖來理解一下

=OFFSET（B4，4，5，1，1）

這個公式表示從原點B4，往下偏移4行，往右偏移5列，然後返回1行1列

這裡有幾點要特別說明一下

如果

行數和列數偏移量超出工作表邊緣，函式 OFFSET 返回錯誤值

#REF！

假如把公式改成=OFFSET（B4，5000000，5，1 ，1），就會返回錯誤

Rows、Cols、Height和Width都有預設值

Rows和Cols的預設值是0，就是說不寫具體數值的話，就表示從原點沒有偏移

選中G15：H17，輸入公式OFFSET（B4， ， ，3，2），然後同時按Ctrl+Shift+Enter結束

Height和Width的預設值是1

，就是說不寫具體數值的話，返回行列偏移後的那個單元格

選中G15，輸入公式OFFSET（B4，4 ，5 ，，），返回的就是G8

Height和Width可以省略，則假設其高度或寬度與 reference 相同

省略和預設大家要分清楚，

預設是數字可以不寫，逗號要有，

省略是數字和逗號都不寫

Height和Width可以省略時，

其數值就和reference的一樣，

就是說如果reference是個單元格，那麼Height和Width就是1，

如果reference是B1：C6，

那麼Height就是6，Width就是2，

因為reference的高度是6，寬度是2

Height和Width可以是負數

這裡說一下，網上很多文章說這兩個引數不能是負數，是可以為負數的，負數代表方向相反，往下的變成往上，往右變成往左

下來我們用例項展示一下Offset函式的用法

1、 根據選擇的考試場次I14，計算該場次近3次考試外語平均成績，結果放在I15中

選中I14，輸入公式：

=AVERAGE（OFFSET（B4，3，MATCH（I14，C4：N4，0）-2，1，3））

表示以B4為原點，往下偏移3，往右偏移MATCH（I14，C4：N4，0）-2，返回1行3列

MATCH（I14，C4：N4，0）表示返回I14在C4：N4中的位置，近3次，就給它減2

我們前面說過，

Height和Width可以是負數

負數代表方向相反，往下的變成往上，往右變成往左

那麼我們可以在用MATCH（I14，C4：N4，0）找到場次的位置後，不減2，

而是用負號，告訴Offset往左邊數，公式變成：

=AVERAGE（OFFSET（B4，3，MATCH（I14，C4：N4，0），1，-3））

結果是一樣的

2、 根據選擇的科目B13，獲取十二次模考成績，結果放在C13：N13中

選中C13，輸入公式：

=OFFSET（B4，MATCH（$B$13，$B$5：$B$12，0），1，1，1）

MATCH（$B$13，$B$5：$B$12，0）表示返回B13在B5：B12中的位置，

就是找所選科目在第幾行，

以B4為原點，往下偏移MATCH返回的位置，往右偏移1，返回1行1列

我們在用得到的資料生成個折線圖

Offset和Match經常配合使用

3、 多行多列轉換成一列，

我們需要將C3：H6的人員轉換成一列，結果從K3開始放

以C3為原點，我們按每組4人算，

就是需要行偏移按0，1，2，3，迴圈，

列偏移是0，0，0，0，1，1，1，1，2，2，2，2…。。。

我們先寫行偏移公式，

以C3計算，先得出行號，再給減去3，然後除以4取餘，

就得到我們想要的結果

公式是=MOD（（ROW（C3）-3），4）

下來寫列偏移公式，

以C3計算，先得出行號，再給減去3，然後除以4取整，

就得到我們想要的結果

公式是=INT（（ROW（C3）-3）/4）

然後帶入到Offset函數里，最後的公式是

=OFFSET（C$3，MOD（（ROW（C3）-3），4），INT（（ROW（C3）-3）/4））

4、 生成成績條

我們需要根據學生成績表生成成績條，

要求每名學生的成績條都有表頭，

並用空行隔開，方便裁剪

因為要有空行分割，

每名學生的成績條就需要3行，

為了計算方便，我們從A9輸入公式

CHOOSE（MOD（ROW（），3）+1，A$1，OFFSET（A$1，ROW（3：3）/3，），）

Offset負責取每條學生的資料

Choose負責按表頭、學生成績、空行迴圈顯示

Ok，掌握了Offset函式的用法，你離大神又進了一步。

單選 | 文章對你有沒有幫助