選單式成績查詢
每個學校都會用到成績查詢。常規的查詢查詢方式是輸入待查詢物件的相關資訊後讓系統提取成績資料。
這種方式有兩個缺點：
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.在單元格B1單元，下拉選單中產生法律系對應的兩個年級。從中選擇“二年級”，在C1單元格將會自動產生查詢結果：外語系三年級的成績84。見圖D所示；
3.單擊A1選擇“文學系”，則B1的下拉選單將產生四個年級；
4.如果在“成績表”中新增新的系別或者新增年級數，A1和B2的下拉選單將自動更新。
圖C 選擇系別
圖D 選擇年級後產生查詢結果
總 結：在利用名稱配套數甩有效性產生下拉選單時，都利用OFFSET函式的第四引數或者第五引數指定一個區域引用來達成。而為了讓公式適應資料的增減，通常 使用COUNTA函式來獲取行或者列中的非空單元格個數，做為OFFSET的引數。本例中展示了公式、名稱、資料有效性結合的多功能、自適應查詢系統。
自動生成查詢.rar