表格中自適應(yīng)成績(jī)查詢系統(tǒng)

菜單式成績(jī)查詢
每個(gè)學(xué)校都會(huì)用到成績(jī)查詢。常規(guī)的查詢查詢方式是輸入待查詢對(duì)象的相關(guān)信息后讓系統(tǒng)提取成績(jī)資料。

這種方式有兩個(gè)缺點(diǎn)：

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

2.如果錄入了錯(cuò)別字、同音字，則無(wú)法查詢到正確的結(jié)果。

今天教大家一種新的查詢方式，不需要錄入任何字符就可以查詢?nèi)魏钨Y料。同時(shí)因?yàn)椴恍枰�錄入字符，也就避免了錯(cuò)誤的產(chǎn)生。

假設(shè)圖A是學(xué)校中所有班級(jí)的平均成績(jī)表，其中各系別的學(xué)期長(zhǎng)度不同，分別有2年、3年、4年，所以中間存在空白區(qū)。

圖A 平均成績(jī)表

在本例中，“成績(jī)表”中存放所有數(shù)據(jù)，需要在“查詢表”中顯示結(jié)果。設(shè)計(jì)查詢功能步驟如下：

1.進(jìn)入工作表“查詢表”，單擊A1單元格，選擇菜單工具欄中插入名稱定義，打開(kāi)定義名稱對(duì)話框；

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

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

然后單擊“添加”按鈕完成第一個(gè)名稱的定義過(guò)程。

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

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

然后單擊“添加”按鈕，并關(guān)閉窗口。

提示：定義名稱時(shí)當(dāng)前的活動(dòng)單元格位置很重要。在本例中需要選擇A1再定義名稱。

本例中兩個(gè)公式的含義如下：

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

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

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

這個(gè)公式的功能是根據(jù)A1的系別返回其對(duì)應(yīng)的年級(jí)。結(jié)果是一個(gè)包含多單元格引用的數(shù)組。如果A1是“文學(xué)系”，則本公式產(chǎn)生一個(gè)一至四年級(jí)的數(shù)組。如果A1是“法律系”，則本公式將產(chǎn)生一個(gè)一至二年級(jí)的數(shù)組。

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

為了實(shí)現(xiàn)下拉菜單選擇條件以查詢成績(jī)，需要將前一步所定義的名稱套用到數(shù)據(jù)有效性，產(chǎn)生下拉菜單序列，供用戶選擇。

1.選擇“查詢表”中的A1，單擊數(shù)據(jù)有效性，打開(kāi)“數(shù)據(jù)有效性”對(duì)話框；

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

圖B 設(shè)置數(shù)據(jù)有效性

3.選擇B1單元格，重復(fù)步驟1和2，對(duì)B1添加數(shù)據(jù)有效性，其來(lái)源的公式為“=年級(jí)”。

注意：“來(lái)源”框中的公式必須用半角的等號(hào)，否則無(wú)法得到正確結(jié)果。

本公式中，利用MATCH函數(shù)計(jì)算A1系別在“成績(jī)表”中A列中的排位，以及計(jì)算B1年級(jí)在“成績(jī)表”中1行中的排位，然后通過(guò)這兩個(gè)座標(biāo)返回區(qū)域A1:I100中的相應(yīng)單元格的值。

而公式中的“&""”可以將零值轉(zhuǎn)化為空白。如INDEX引用的區(qū)域是空白時(shí)，結(jié)果會(huì)為0，為了將此0值轉(zhuǎn)化為空白，則在公式后面添加“&""”。

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

2.在單元格B1單元，下拉列表中產(chǎn)生法律系對(duì)應(yīng)的兩個(gè)年級(jí)。從中選擇“二年級(jí)”，在C1單元格將會(huì)自動(dòng)產(chǎn)生查詢結(jié)果：外語(yǔ)系三年級(jí)的成績(jī)84。見(jiàn)圖D所示；

3.單擊A1選擇“文學(xué)系”，則B1的下拉列表將產(chǎn)生四個(gè)年級(jí)；

4.如果在“成績(jī)表”中添加新的系別或者添加年級(jí)數(shù)，A1和B2的下拉列表將自動(dòng)更新。

圖C 選擇系別

圖D 選擇年級(jí)后產(chǎn)生查詢結(jié)果

總 結(jié)：在利用名稱配套數(shù)甩有效性產(chǎn)生下拉列表時(shí)，都利用OFFSET函數(shù)的第四參數(shù)或者第五參數(shù)指定一個(gè)區(qū)域引用來(lái)達(dá)成。而為了讓公式適應(yīng)數(shù)據(jù)的增減，通常 使用COUNTA函數(shù)來(lái)獲取行或者列中的非空單元格個(gè)數(shù)，做為OFFSET的參數(shù)。本例中展示了公式、名稱、數(shù)據(jù)有效性結(jié)合的多功能、自適應(yīng)查詢系統(tǒng)。