望著Offset()函數(shù)半天,，猜不出它是干啥的,。不過，也許是因為我的英文不好,，你已經(jīng)感應(yīng)到它的用處,，正在蠢蠢欲試。

Offset()函數(shù)屬于查找引用函數(shù),，其功能是以指定的引用為參照,，通過給定偏移數(shù)值生成一個新的單元格或單元格區(qū)域引用。其語法結(jié)構(gòu)為Offset(reference,rows,cols,[height],[width]):

reference即偏移的基點,，可以是單元格或者相連的單元格區(qū)域,。

rows指定上下偏移的行數(shù),，正數(shù)表示向下偏移,，負數(shù)表示向上偏移，缺省表示不偏移,。

cols指定左右偏移的列數(shù),，正數(shù)表示向右偏移，負數(shù)表示向左偏移，缺省表示不偏移,。

height高度,，即所要返回的新引用區(qū)域的行數(shù)。高度必須為正數(shù),，缺省則默認與基點的行數(shù)相同,。

width寬度，即所要返回的新引用區(qū)域的列數(shù),。寬度必須為正數(shù),，缺省則默認與基點的列數(shù)相同。

如果偏移超出工作表范圍,，則報錯“#REF!”,。

1、基點為單元格,，高度和寬度參數(shù)缺省

在單元格B8鍵入公式“=OFFSET(A1,3,5)”,，則指向單元格A1+3行->A4+5列->F4單元格，因為高度和寬度缺省,，默認高寬與基點A1相同,，因此返回的引用也是一個單元格，顯示引用單元格數(shù)據(jù)“1225”,。

2,、基點為單元格，并設(shè)置了高度和寬度

在單元格鍵入公式“=OFFSET(A1,3,1,3,5)”,，返回引用單元格區(qū)域B4:F6,，當(dāng)公式的高度參數(shù)大于1時，該公式必須按照數(shù)組公式的要求即Ctrl+Shift和Enter三鍵一起確認,，公式兩側(cè)自動加上數(shù)組公式標(biāo)記大括號（數(shù)組公式的介紹可參閱知道這些Excel數(shù)組概念和運算規(guī)則,，數(shù)組公式就豁然開朗了）。

如果選擇單個單元格三鍵確認該公式,，則只會顯示返回引用區(qū)域的首個單元格的數(shù)據(jù),；選擇與參數(shù)大小相同的單元格區(qū)域，鍵入公式,，然后三鍵同時確認,，則會顯示全部單元格區(qū)域的數(shù)據(jù)。所以工作表單個單元格只能存儲返回數(shù)組的一個數(shù)據(jù),，用Sum()函數(shù)求和上面多單元格數(shù)組的單個單元格,，只能返回該單元格存儲的數(shù)據(jù)，如果希望求和引用區(qū)域,，需要直接用Sum()函數(shù)和Offset()函數(shù)嵌套使用,，或者求和多單元格數(shù)組的全部單元格,。

3、基點為單元格區(qū)域,，并設(shè)置了高度和寬度

在單元格B12：F14鍵入多單元格數(shù)組公式“=OFFSET(A1:F6,3,1,3,5)”,，會發(fā)現(xiàn)結(jié)果與上一個例子一模一樣，由此可見,，如果定義了參數(shù)的高度和寬度,，基點選擇區(qū)域沒有太大意義，但是,，如果高度和寬度缺省,，則返回引用默認為基點區(qū)域的高寬。

Offset()函數(shù)的功能和幾個參數(shù)的使用方法介紹完畢?，F(xiàn)在來看看如何利用Offset()函數(shù)制作動態(tài)圖表,，就以之前在介紹數(shù)組的文章中出現(xiàn)過的動態(tài)餅圖為例。

在上圖下拉框中選擇不同的數(shù)字,，數(shù)據(jù)區(qū)域相應(yīng)產(chǎn)品涂成黃色突出顯示,，餅圖扇形數(shù)量及占比也隨之變換。

首先準(zhǔn)備好數(shù)據(jù)和表頭,，為了防止數(shù)字填寫錯誤,，數(shù)字可以設(shè)置“數(shù)據(jù)有效性”。

然后,，點擊“公式”選項卡,，點擊“名稱管理器”按鈕，新建一個叫做“銷量”的名稱,，引用位置鍵入“=OFFSET($B$1,1,,$F$1,1)”,，在這里用到了Offset()函數(shù)，返回B1下移一行即以單元格B2為起點,，動態(tài)高度等于單元格F1的區(qū)域,，兩個單元格都是絕對引用。建立這個名稱的目的是為了給餅圖的數(shù)據(jù)源賦值,。（關(guān)于名稱的介紹請參閱Excel中越用越香的“名稱”工具,。）

然后選擇數(shù)據(jù)區(qū)域，點擊“插入”選項卡,，選擇“二維餅圖”,。

右鍵點擊餅圖，在彈出的菜單中選擇“選擇數(shù)據(jù)”,。

從“選擇數(shù)據(jù)源”對話框可以看見餅圖只有一個數(shù)據(jù)系列,。

點擊圖列項（系列）的“編輯”按鈕，彈出“編輯數(shù)據(jù)系列”對話框,。餅圖扇形數(shù)量和大小是由系列值決定的,，所以要想實現(xiàn)圖表動態(tài)顯示，則首先要實現(xiàn)系列值動態(tài)取值,。但是,，系列值的取值框不能直接鍵入公式，所以我們在這里鍵入提前設(shè)置好的名稱“銷量”,，通過名稱中的公式實現(xiàn)系列值動態(tài)取值,。

再做些簡單的美化，包括刪除圖例,、添加數(shù)據(jù)標(biāo)簽,、更換餅圖的顏色等，動態(tài)餅圖大功告成,。

為了呈現(xiàn)更好的效果,，我們可以通過“條件格式”讓數(shù)據(jù)區(qū)域的顯示與動態(tài)餅圖同步。選中單元格A2:B16,，點擊“開始”選項卡,，點擊“條件格式”按鈕，選擇“新建規(guī)則”,。

在“新建格式規(guī)則”對話框選擇“使用公式確定要設(shè)置格式的單元格”,。在編輯規(guī)則說明中鍵入規(guī)則“=MATCH($A2,$A$2:$A$16,0)<=$F$1”，點擊“格式”按鈕將填充色設(shè)置為黃色,。

以上規(guī)則的思路是通過Match()函數(shù)計算該產(chǎn)品在數(shù)據(jù)區(qū)域的排序,，如果排序小于或者等于單元格F1的數(shù)字則說明該產(chǎn)品屬于餅圖顯示范圍，則涂成黃色,。

注意條件規(guī)則的書寫也要遵循絕對引用和相對引用的規(guī)則,，特別注意Match()函數(shù)第一個參數(shù)的引用為“$A2”,列標(biāo)為絕對引用，行號為相對引用,。

選擇數(shù)字“9”做個測試,。OK！

掌握好Offset()函數(shù)和“名稱”這兩個工具,，動態(tài)圖表手到擒來,。