表格中自適應成績查詢系統

菜單式成績查詢
每個學校都會用到成績查詢。常規的查詢查詢方式是輸入待查詢對象的相關信息后讓系統提取成績資料。

這種方式有兩個缺點：

1.需要錄入文字，對于不會打字或者字符錄入速度慢者不方便；

2.如果錄入了錯別字、同音字，則無法查詢到正確的結果。

今天教大家一種新的查詢方式，不需要錄入任何字符就可以查詢任何資料。同時因為不需要錄入字符，也就避免了錯誤的產生。

假設圖A是學校中所有班級的平均成績表，其中各系別的學期長度不同，分別有2年、3年、4年，所以中間存在空白區。

圖A 平均成績表

在本例中，“成績表”中存放所有數據，需要在“查詢表”中顯示結果。設計查詢功能步驟如下：

1.進入工作表“查詢表”，單擊A1單元格，選擇菜單工具欄中插入名稱定義，打開定義名稱對話框；

2.在名稱處鍵入“系別”，在引用位置處鍵入：

“=OFFSET(成績表!A1,1,,COUNTA(成績表!A:A)-1)”

然后單擊“添加”按鈕完成第一個名稱的定義過程。

3.繼續在名稱框中鍵入“年級”，在引用位置處鍵入以下公式：

“=OFFSET(成績表!$A$1,,1,,COUNTA(INDIRECT("成績表!"&(MATCH(查詢表!$A$1,成績表!$A：A,0)&":"&MATCH(查詢表!$A$1,成績表!$A：A,0))))-1)”

然后單擊“添加”按鈕，并關閉窗口。

提示：定義名稱時當前的活動單元格位置很重要。在本例中需要選擇A1再定義名稱。

本例中兩個公式的含義如下：

1.“=OFFSET(成績表!A1,1,,COUNTA(成績表!A:A)-1)”

這個公 式是指以成績表中A1單元格為參照點，偏移1行、0列(Offset的第二參數和第三參數被忽略時默認值為1)，偏移的高度為COUNTA函數所返回的結 果減1，即A列中非空單元格個數減1。這個公式用于自適應系別的增減。當在工作表中添加新的系別如“演藝系”時，公式可以自動將之提取出來

2.“=OFFSET(成績表!A1,,1,,COUNTA(INDIRECT("成績表!"&(MATCH(查詢表!A1,成績表!A:A,0)&":"&MATCH(查詢表!A1,成績表!A:A,0))))-1)”

這個公式的功能是根據A1的系別返回其對應的年級。結果是一個包含多單元格引用的數組。如果A1是“文學系”，則本公式產生一個一至四年級的數組。如果A1是“法律系”，則本公式將產生一個一至二年級的數組。

本公式的運算較復雜�？梢苑侄卫斫�。其 中，MATCH函數用來計算“查詢表”中A1單元格的系別在“成績表”中A列中的排位，并將其結果返回給INDIRECT函數轉換為行引用。而 COUNTA函數則計算該引用行中非空單元格的個數，此個數控制著年級的數量，使用公式具有自適應的能力。整個公式將會以“成績表”中A1單元格為參照， 偏移0行、1列，產生一個高度為1（Offset的第四參數和第五參數忽略時默認值為1）、寬度為COUNTA函數返回值的區域引用。

為了實現下拉菜單選擇條件以查詢成績，需要將前一步所定義的名稱套用到數據有效性，產生下拉菜單序列，供用戶選擇。

1.選擇“查詢表”中的A1，單擊數據有效性，打開“數據有效性”對話框；

2.在“允許”下拉列表中選擇“序列”，在“來源”框中輸入公式“=系別”，見圖B所示。然后單擊“確定”按鈕返回工作表。

圖B 設置數據有效性

3.選擇B1單元格，重復步驟1和2，對B1添加數據有效性，其來源的公式為“=年級”。

注意：“來源”框中的公式必須用半角的等號，否則無法得到正確結果。

本公式中，利用MATCH函數計算A1系別在“成績表”中A列中的排位，以及計算B1年級在“成績表”中1行中的排位，然后通過這兩個座標返回區域A1:I100中的相應單元格的值。

而公式中的“&""”可以將零值轉化為空白。如INDEX引用的區域是空白時，結果會為0，為了將此0值轉化為空白，則在公式后面添加“&""”。

1.在單元格A1單擊，從下拉列表中選擇“法律系”，見圖C所示；

2.在單元格B1單元，下拉列表中產生法律系對應的兩個年級。從中選擇“二年級”，在C1單元格將會自動產生查詢結果：外語系三年級的成績84。見圖D所示；

3.單擊A1選擇“文學系”，則B1的下拉列表將產生四個年級；

4.如果在“成績表”中添加新的系別或者添加年級數，A1和B2的下拉列表將自動更新。

圖C 選擇系別

圖D 選擇年級后產生查詢結果

總 結：在利用名稱配套數甩有效性產生下拉列表時，都利用OFFSET函數的第四參數或者第五參數指定一個區域引用來達成。而為了讓公式適應數據的增減，通常 使用COUNTA函數來獲取行或者列中的非空單元格個數，做為OFFSET的參數。本例中展示了公式、名稱、數據有效性結合的多功能、自適應查詢系統。