Offset函數在教師監考查詢表中的應用

饒奇蘭

摘要：文章通過介紹Offset函數的語法規則，并結合具體的應用實例，對Offset函數在教師監考查詢表中的數據批量查詢功能進行詳細講解，通過使用Offset函數，可以幫助監考教師最快獲取自己的監考信息，同時能提高教務人員的工作效率，并能確保數據的可靠性。

關鍵詞：Offset函數;數據處理;批量查詢;教師監考信息管理

中圖分類號：G642? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2021）10-0257-02

1 引言

每一學期學校都將進行各種考試，比如：期中期末考試，時間間隔長，監考信息量大，每位監考教師的監考場次多，如果想要在電子版監考表中很快查找到自己的監考信息，需要通過Excel中常用的“查找”功能來進行，一方面耗時，另一方面也不能立馬查找到自己所有的監考信息，所以監考教師大多會選擇人工紙質版的表格查詢并記錄，不僅浪費時間和紙張，同時信息容易出錯，另外如果有特殊情況需要跟其他教師調換監考也需要查找對比大量數據，不僅效率低下，而且耗時耗力。

如果能夠進行批量查詢（又稱“數據匹配”），則可以解決問題。在Excel中，我們要實現通過查找監考教師姓名，找到對應的監考信息，因為需要提高制作查詢表的簡便快捷，所以我們采用Offset函數使用查找教師監考信息，這樣可以在最短的時間內查找到所有監考教師的監考信息，這樣既可以幫助監考教師快速查詢到自己和別人的所有監考信息，同時也提高了教務人員的工作效率，極大提高了數據的完整性與可靠性。

2 功能簡介

Offset函數：

offset函數是以指定的引用為參考系，通過上下左右偏移得到新的區域的引用。返回的引用可以是一個單元格也可以是一個區域。并且可以引用指定行列數的區域。如果行數和列數偏移量超出工作表邊緣，函數 OFFSET 返回錯誤值 #REF！。下面介紹她的語法和參數用法。

offset函數的語法表達式的語法形式為;

OFFSET（reference，rows，cols，height，width）

用通俗易懂的方式可以表示為：

offset（起始區域，向下偏移行數，向右偏移列數，返回的行數，返回的列數）

（1）Reference 作為偏移量參照系的引用區域。Reference 必須為對單元格或相連單元格區域的引用;否則，函數 OFFSET 返回錯誤值#VALUE！。（2）Rows相對于偏移量參照系的左上角單元格，上（下）偏移的行數，行數可為正數（代表在起始引用的下方）或負數（代表在起始引用的上方）。（3）Height高度，即所要返回的引用區域的行數。Height 可以為負，-x表示當前行向上的x行。（4）Width寬度，即所要返回的引用區域的列數。Width 可以為負，-x表示當前行向左的x列。

3 offset函數應用實例

為了能快捷查出每一個教師的監考情況，同時簡化數據實現過程，我們使用OFFSET 函數來幫助我們實現，現以監考表查詢內容為例，探討OFFSET函數的具體應用（因本文主要是為了說明函數的使用，故數據做了簡化處理）。

首先，在EXCEL Sheet1和Sheet2工作表中先分別創建監考表以及監考教師名單，比如三天的監考信息情況表，表中有時間，班級，科目，學號，份數，考場，監考教師，每天3場監考，以下為部分截圖，表的名稱為監考信息表，如圖1所示，現要查找監考教師的每天的監考信息，則需要找監考教師的姓名作為查詢對象，所以放到表的第一列中。

其次，以上兩個表格建好后，在Sheet3工作表中開始建立監考查詢表如圖3。圖3監考查詢表中D2單元格采用數據驗證，數據來源圖2中的教師監考名單中的數據。C6單元格中輸入公式，函數的參數設置情況如圖4所示，數據來源為圖1中的教師監考表。

圖3中C6單元格的公式為：

{=OFFSET（Sheet1！$A$1，SMALL（IF（$D$2=Sheet1！$A$2：$A$302，ROW（Sheet1！$A$2：$A$302），60000），ROW（Sheet1！A1））-1，COLUMN（Sheet1！A1））}

D2單元格值為劉賢敏，上圖為查找劉賢敏老師的監考信息，當C6單元格公式輸完后，使用向右和向下復制填充公式將返回表Sheet1中與D2單元格內容相匹配的單元格數值，即返回表Sheet1中第195行和第300行的劉賢敏老師的監考信息，監考查詢表將依次排列劉賢敏老師在整個考試期間的所有監考信息。現以圖3查找監考教師劉賢敏為例，對以上公式進行解析：

1）IF（$D$2=Sheet1！$A$2：$A$302，ROW（Sheet1！$A$2：$A$302），60000），這個表達式意思是：D2單元格值為劉賢敏，則將在Sheet1工作中查詢劉賢敏老師的監考信息，如果Sheet1工作表中A2：A302區域查找到劉賢敏老師的監考信息的那條記錄，就返回這條記錄的行號，反之則就返回60000這個數值，查詢結果為只有第195行和第300行是劉賢敏老師的監考信息，其他行不是，所以返回的結果為行號195、行號300和60000的數組，即{60000，60000，…，195，60000，…300，60000，…，60000}，因行數總共為302行，除去標題，所以返回的結果共301個數值組成的數組。

2）SMALL（IF（$D$2=Sheet1！$A$2：$A$302，ROW（Sheet1！$A$2：$A$302），60000），ROW（Sheet1！A1）， 這個表達式意思是SMALL函數將上述行號，和60000組成的數組進行由小到大的排序，所以此步驟表達式結果排序后結果為：{195，300，60000，…，60000}。

3）OFFSET（Sheet1！$A$1，SMALL（IF（$D$2=Sheet1！$A$2：$A$302，ROW（Sheet1！$A$2：$A$302），60000），ROW（Sheet1！A1））-1，COLUMN（Sheet1！A1）），這個表達式意思是，以A1為起始點 ，偏移數不包括A1所在的行，所以向下偏移行數為行號減一，向右偏移A1的列，因為是數組，所以上述按表達式需要按 ALT+CTRL+ENTER 返回區域所對應的第一個數值。圖3中查詢結果將顯示第195行和第300行的數據，所以第2表達式結果為以A1為起始點，向下偏移194行，再向右偏移1列，所以C6單元格返回的結果為： Sheet1工作表中第1列7月23日的日期;將此公式向右復制填充，圖4中D6單元格公式將把未鎖定的A1變為第二列B1，則結果返回的值將為Sheet1工作表中第195行的第2列班級信息，依此類推。將C6單元格公式向下復制填充，圖4中C7單元格將返回第300行的數據，即結果為以A1為起始點，向下偏移299行，再向右偏移1列，所以C7單元格返回的結果為： Sheet1工作表中第1列7月24日的日期;將C7單元格向右復制填充，圖4中D7單元格公式將把未鎖定的A2變為第二列B2，則結果返回的值將為Sheet1工作表中第300行第2列班級信息，以此類推。結果為60000的對應的是空單元格。

使用函數offset函數查詢到的結果是沒有邊框線的，為了能夠更直觀看到自己的監考信息，所以有數據時畫邊框線，無數據時不畫邊框線，這種效果可以通過條件格式來設置;當數據出現零時，如果不想讓零顯示，可以通過EXCEL選項中高級選項來設置，具體細節不再闡述。

4 結語

本文以教師監考查詢表為例，講解了監考查詢表的制作過程以及offset函數在監考查詢表中的具體應用，通過offset函數的運用，可以快速幫助監考教師在教師監考查詢表能快速查找到自己和別人的監考信息，同時能提高教務人員的工作效率，并能確保數據的可靠性。

參考文獻：

[1] 魯翠柳.OFFSET函數在“數據有效性”中的應用[J].電腦知識與技術，2010，6（30）：8515-8516.

[2] 周紅曉.Excel函數在數據有效性設置中的應用[J].電腦學習，2009（5）：32-34.

[3] 周洪林.EXCEL函數高級應用[J].福建電腦，2009，25（2）：179-180.

【通聯編輯：王力】