像 C 列中的“負責人選項表”這一項目的選項，在實際工作中調(diào)整這個選項的數(shù)量的情況其實非常普遍。如輸入商品名稱等操作，輸入選項會因為商品的改動或下架有所調(diào)整。遇到這樣的情況，如果“負責人選項表”所指定的范圍是 C2：C4這種固定范圍的話，如果之后要在單元格 C5中追加新的負責人名字，那么就無法出現(xiàn)在單元格 A2的序列輸入選項列中。

輸入新的負責人，無法顯示在序列中

如此一來，想要把 C5也放入指定范圍中，我們需要再次設置【來源】指定的范圍。如果不需要經(jīng)常增減選項的話，這樣的操作也不會花太多時間，但若是需要頻繁修改【來源】的范圍，那么就麻煩了。如果序列輸入可以自動對應【來源】內(nèi)容做出調(diào)整，即使需要頻繁修改也不會覺得麻煩。

為此，請在“負責人選項表”名稱的引用位置里輸入如下公式：

=OFFSET(負責人!$C$1,1,0,COUNTA(負責人!$C:$C)-1,1)

引用位置中輸入=OFFSET(負責人!$C$1,1,0,COUNTA(負責人!$C:$C)-1,1)

這里使用的是 OFFSET 函數(shù)。這個函數(shù)非常重要，請務必掌握。這一函數(shù)的要點有兩個：

• 確定作為基準的單元格，將其理解為引用的單元格從這一位置“偏離”
• 以基準單元格偏離后為起點來指定單元格的范圍

OFFSET 函數(shù)的公式：

【公式】

=OFFSET(基準單元格,偏離行數(shù),偏離列數(shù))

OFFSET 函數(shù)的語法為：“第一參數(shù)指定的單元格（基準單元格）開始，第二參數(shù)指定向上或向下偏移幾行，第三參數(shù)指定從第二參數(shù)偏離后的位置向右或向左偏移幾行”。第二參數(shù)為正數(shù)則向下移動，為負數(shù)則向上移動。第三參數(shù)為正數(shù)則向右移動，為負數(shù)則是向左移動。

下面來看一下使用案例吧。下圖中的工作表是 A1：D3為范圍以性別和課程來分類的費用表。

A1：D3為范圍以性別和課程來分類的費用表

男性為1、女性為2，并用括號括起來。每項各自以單元格 A1為基準，男性的費用在單元格 A1的下一行，女性的費用在單元格 A1的下兩行。

關(guān)于課程，初級為1、中級為2、高級為3。也是以單元格 A1為基準，初級在 A1向右一列，中級在 A1向右第二列，高級在 A1向右第三列。

這時，在單元格 B5輸入代表性別的數(shù)值，在單元格 B6輸入代表課程的數(shù)值，單元格 B7中就會顯示相應的費用金額。想要建立這種結(jié)構(gòu)，需要在單元格 B7輸入以下函數(shù)公式：

=OFFSET(A1,B5,B6)

在單元格 B7中輸入=OFFSET(A1,B5,B6)

這個公式可以導出以單元格 A1為基準，A1～B5指定的數(shù)字向下、B6指定的數(shù)字向右偏移的單元格的值。

如圖所示，第二參數(shù)單元格 B5為1，第三參數(shù)指定的單元格 B6為2。如此一來，A1向下偏移一格、再向右偏移兩格……即指向 C2的值。這利用的是 OFFSET 函數(shù)的基礎邏輯：第一參數(shù)指定的單元格為基準，第二參數(shù)指定的數(shù)字向下，再從這一位置以第三參數(shù)指定的數(shù)字向右移動所指向的單元格。

第二參數(shù)指定的數(shù)字若為負數(shù)，則第一參數(shù)為基準向上移動，第三參數(shù)指定的數(shù)字若為負數(shù)，則第一參數(shù)為基準向左移動。

順帶一提，運用這一函數(shù)也可以解決“在 VLOOKUP 函數(shù)中，是否能獲取位于檢索列左側(cè)的數(shù)值嗎”這一問題（參考 P116）。

如何指定范圍

另外，通過 OFFSET 函數(shù)，還能以從基準單元格按第二參數(shù)數(shù)值向下、第三參數(shù)數(shù)值向右偏移的位置為起點，再次指定范圍。但是此時需再追加2個參數(shù)。

=OFFSET(基準單元格,偏移行數(shù),偏移列數(shù),高度,寬度)

在下表中，B 列為每天的銷售額。在單元格 D1中輸入想要知道從1號開始到第 N 天的累計銷售額的天數(shù)，單元格 G1就會自動顯示銷售額數(shù)據(jù)。

此表中，D1的數(shù)值為2，銷售額 G1中則顯示1日～2日兩天的累計銷售額。

在單元格 G1中，需要輸入以下函數(shù)公式：

=SUM(OFFSET(B1,1,0,D1,1))

一般要計算數(shù)值的和，都會用到 SUM 函數(shù)，SUM 函數(shù)可計算出括號內(nèi)指定的單元格范圍內(nèi)的和。SUM 函數(shù)括號內(nèi)的 OFFSET 函數(shù)就在指定的單元格范圍。

首先，我們只看 OFFSET 函數(shù)部分，確認它所指定的范圍。這是以單元格 B1為基準，向下移動1格、向右移動0格，也就是不向右移動。于是，偏移的目標單元格為 B2。

再以 B2為起點，指向第四參數(shù)指定的行數(shù)（此表中單元格 D1的值為2，即2行）和第五參數(shù)指定的1列的范圍（具體為 B2：B3）。

這里需要掌握的重要信息為：OFFSET 函數(shù)的第四參數(shù)指定的范圍的行數(shù)若發(fā)生變化，OFFSET 函數(shù)指定的范圍也會有所變化。

OFFSET 函數(shù)所指定的范圍，可利用“根據(jù)單元格 D1的值，縱向擴展”這一點靈活應對。

• D1值為3?B2：B4
• D1值為5?B2：B6

應用這個方法，即便是序列輸入模式，可以應對【來源】范圍中數(shù)據(jù)有所增加的情況，選項也會自動增加。

那么接下來，我們再來看一下剛剛以“負責人選項表”為引用范圍輸入的公式。

=OFFSET(負責人!$C$1,1,0,COUNTA(負責人!$C:$C)-1,1)

我們來分析一下這個公式。首先，以“負責人”工作表中單元格 C1為基準，向下移動1格、向右移動0格的目標單元格，即單元格 C2為起點的范圍。

想要指定這個范圍的行數(shù)，需要使用 COUNTA 函數(shù)。通過 COUNTA 函數(shù)，將整個 C 列的含數(shù)據(jù)的單元格的行數(shù)減去1。由于 C 列中含數(shù)據(jù)的單元格中含有第一行“負責人選項表”這一項目，因此需要減去這一行。

然后，用第五參數(shù)指定范圍的寬度為1。

第四參數(shù)的 COUNTA 函數(shù)一般所取的是整個 C 列中含數(shù)據(jù)的行數(shù)減1后得到的數(shù)字，所以當 C 列中追加負責人后，“負責人選項表”的范圍也應自動進行相應的擴大。

如此一來，單元格指定范圍的“負責人選項表”下的數(shù)值，與單元格 A2的菜單中的下拉選項的數(shù)值必須要一致。

“負責人選項表”與單元格 A2的菜單中的下拉選項的數(shù)值一致