圖1

我們拿這份數據位例子。圖1所示為某份原始成績表，包括全年級八個班八個學科的成績。這份表格放在“原始數據”工作表中。我們的任務是能夠方便地查詢各個班各學科的總分、平均分、及格率、優秀率四個項目。為此，我們有如下工作要做：

一、基本表格準備

在Sheet2工作表標簽處右擊，將其重命名為“班級項目查詢”。在該工作表的A1單元格錄入“查詢班級”，在A2單元格錄入“查詢項目”。點擊B1單元格，再點擊菜單命令“數據→有效性”，打開“數據有效性”對話框。在“允許”下拉列表中點擊“序列”選項，在下方的“來源”輸入框中輸入“1,2,3,4,5,6,7,8”，要注意的是這里的數字和逗號均需在英文半角狀態下輸入。其它均采用默認設置好了，如圖2所示。

圖2

用同樣的方法設置B2單元格的數據有效性，只是其來源為“總分,平均分,及格率,優秀率”。經過這樣的設置之后，我們就可以輕松在B1、B2兩個單元格提供的下拉列表中選擇要查詢的班級和查詢的項目了，如圖3所示。

圖3

回到“原始數據”工作表。在P3:X13區域建立如圖4所示的表格。

圖4

點擊Q3單元格，錄入公式“=班級項目查詢!B1”。點擊Q4單元格，錄入公式“=班級項目查詢!B2”。至此，基本的表格準備工作就算是完成了。

二、數據準備

首先在“班級項目查詢”工作表的B1和B2單元格中分別選擇好查詢的班級和查詢項目。然后點擊“原始數據”工作表中Q5單元格，錄入公式“=SUMIF($B:$B,$Q$3,D:D)”，回車后就可以得到所查詢班級的語文科總分了。在Q6單元格錄入公式“=Q5/COUNTIF($B:$B,$Q$3)”，可以得到該班級語文科的平均分。在Q7單元格錄入公式“=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=60))/COUNTIF($B:$B,$Q$3)”，得到及格率，在Q8單元格錄入公式“=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=85))/COUNTIF($B:$B,$Q$3)”，得到優秀率。選中Q5:Q8單元格區域，拖動其填充句柄向右至X列，這樣該班級的所有科目的全部數據就都有了。我們可以選中全部數據，設置其格式為帶兩位小數的數字。