OFFSET函數的應用（上）

2011-07-19 17:24:39|分類： 職場軟體應用 |字型大小訂閱

　　首先，認識一下OFFSET函數。

　　從下圖說明來認識一下excel中OFFSET函數的用法。

　　在C7單元格，輸入公式：=SUM(OFFSET(C2,1,2,3,1))，得到結果為18。這個公式是什麼意思呢?就是計算C2單元格靠下1 行並靠右2 列的3 行 1 列的區域的和。

　　可以在公式編輯欄，選中OFFSET(C2,1,2,3,1) 部分，按F9鍵抹黑，得到運算結果為：{3;8;7}，此時公式變為：=SUM({3;8;7})。從上圖可以得知，就是利用OFFSET函數來得到一個新的區域，然後使用SUM函數求出這個新區域的和。

　　下面，介紹OFFSET函數的用法。

　　Offset函數主要應用在單元格區域的定位和統計方面，一般做數據透視表定義名稱都需要用到Offset函數。Offset函數屬於查找與引用類的函數。

　　OFFSET函數以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區域，並可以指定返回的行數或列數。

　　OFFSET函數的語法是：OFFSET(reference,rows,cols,height,width)，按照中文的說法即是：OFFSET(引用區域,行數,列數,[高度],[寬度])

　　其中的參數意義如下：

　　Reference：作為偏移量參照系的引用區域。Reference必須為對單元格或相連單元格區域的引用;否則，函數 OFFSET 返回錯誤值 #VALUE!。

　　Rows：相對於偏移量參照系的左上角單元格，上(下)偏移的行數。如果使用 5 作為參數 Rows，則說明目標引用區域的左上角單元格比 reference 低 5 行。行數可為正數(代表在起始引用的下方)或負數(代表在起始引用的上方)。

　　Cols：相對於偏移量參照系的左上角單元格，左(右)偏移的列數。如果使用 5 作為參數 Cols，則說明目標引用區域的左上角的單元格比 reference 靠右 5 列。列數可為正數(代表在起始引用的右邊)或負數(代表在起始引用的左邊)。

　　Height：高度，即所要返回的引用區域的行數。Height 必須為正數。

　　Width：寬度，即所要返回的引用區域的列數。Width 必須為正數。

　　學習使用OFFSET函數需要注意以下幾點：

　　第一，如果行數和列數偏移量超出工作表邊緣，函數 OFFSET 返回錯誤值 #REF!。

　　第二，如果省略 height 或 width，則假設其高度或寬度與 reference 相同。

　　第三，函數 OFFSET 實際上並不移動任何單元格或更改選定區域，它只是返回一個引用。

　　第四，函數 OFFSET 可用於任何需要將引用作為參數的函數。

　　下面，結合幾個實例學習OFFSET函數的應用。

　　OFFSET函數通常與其它函數來嵌套使用。下圖所示的區域為實例的源數據區域。

　　OFFSET函數應用第一題：如下圖所示，計算上圖B列姓名下面所有單元格的數量。

　　在F3單元格輸入函數：=OFFSET(B2,0,0,COUNTA(B:B)-1)，全選公式，按F9鍵，得到：={"張三";"王五";"李四";"聞一";"張二";"江八";"張六";"趙六";"錢子";"趙一";"孫六";"周末"}，我們可以數一下個數，得到12個。

　　提示：COUNTA 函數就是返回參數列表中非空值的單元格個數。

　　接下來，在F5單元格輸入公式：=COUNTA(OFFSET(B2,0,0,COUNTA(B:B)-1))，得多結果為12。

　　OFFSET函數應用第二題：如下圖所示，計算最後的平均成績。分三種情況：第一，求最後一位、最後三位、最後五位的平均成績。

　　單擊F8單元格，可以看到有一個向下的箭頭，單擊箭頭可以看到有1、3、5三個選項。

　　在F9單元格輸入公式：=AVERAGE(OFFSET(C1,COUNTA(C:C)-1,,-F8))，就可以得到F8單元格所顯示的平均成績。

　　公式分析：首先使用OFFSET函數來計算出行數的值，然後使用AVERAGE來除以OFFSET部分得到的值，就得到了最後幾個的平均成績。

　　公式中，其中COUNTA(C:C)部分是統計C列的非空單元格個數為13，從而可以得到COUNTA(C:C)-1的值為12，即從C1單元格向下偏移12行。

　　另外-F8的意思是：減去F8單元格中的值。

　　當改變F8單元格的值，就可以得到其餘行數的平均成績。

　　OFFSET函數應用第三題：如下圖所示，要求統計出班級=H3,成績>=I3的人數。

　　H3和I3單元格的值是條件。單擊這兩個單元格，右下角有一個向下的三角形，單擊此按鈕，可以顯示選擇其餘的條件值。比如上圖所示，就是統計出班級為3版，成績在>=70分的人數有幾人。

　　本題的統計結果，可以在I5單元格輸入公式：=COUNTIF(OFFSET(C1,MATCH(H3,A2:A13,0),,COUNTIF(A2:A13,H3)),">="&I3)，得到結果。

　　　　公式分析：OFFSET的第二參數使用MATCH來精確查找位置。偏移的行數就由MATCH(H3,A2:A13,0)來控制，高度由COUNTIF(A2:A13,H3)來控制。然後把OFFSET函數的值用COUNTIF來計算符合條件的有多少個。