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

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

這種方式有兩個缺點：

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添加數據有效性，其來源的公式為“=年級”。

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

三：設計成績公式下拉列表設計完畢後，需要利用一個單元格來顯示查詢結果。
1.選擇C1單元格，輸入以下公式：
=INDEX(成績表!A1:I100,MATCH(A1,成績表!A:A,0),MATCH(B1,成績表!1:1,0))&""

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

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

四：查詢

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

2.