1.如何用WPS表格打造方便查詢的成績分析表

圖1所示為某份原始成績表，包括全年級八個(gè)班八個(gè)學(xué)科的成績。

這份表格放在“原始數(shù)據(jù)”工作表中。現(xiàn)在我們需要方便地查詢各個(gè)班各學(xué)科的總分、平均分、及格率、優(yōu)秀率四個(gè)項(xiàng)目。

圖1 原始成績表 一、基本表格準(zhǔn)備 在Sheet2工作表標(biāo)簽處右擊，將其重命名為“班級項(xiàng)目查詢”。在該工作表的A1單元格錄入“查詢班級”，在A2單元格錄入“查詢項(xiàng)目”。

點(diǎn) 擊B1單元格，再點(diǎn)擊菜單命令“數(shù)據(jù)→有效性”，打開“數(shù)據(jù)有效性”對話框。在“允許”下拉列表中點(diǎn)擊“序列”選項(xiàng)，在下方的“來源”輸入框中輸入 “1,2,3,4,5,6,7,8”，要注意的是這里的數(shù)字和逗號均需在英文半角狀態(tài)下輸入。

其它均采用默認(rèn)設(shè)置。 用同樣的方法設(shè)置B2單元格的數(shù)據(jù)有效性，只是其來源為“總分，平均分，及格率，優(yōu)秀率”。

經(jīng)過這樣的設(shè)置之后，我們就可以輕松在B1、B2兩個(gè)單元格提供的下拉列表中選擇要查詢的班級和查詢的項(xiàng)目了。 回到“原始數(shù)據(jù)”工作表。

在P3:X13區(qū)域建立如圖2所示的表格。 圖2 點(diǎn)擊Q3單元格，錄入公式“=班級項(xiàng)目查詢！B1”。

點(diǎn)擊Q4單元格，錄入公式“=班級項(xiàng)目查詢！B2”。至此，基本的表格準(zhǔn)備工作就算是完成了。

二、數(shù)據(jù)準(zhǔn)備 首先在“班級項(xiàng)目查詢”工作表的B1和B2單元格中分別選擇好查詢的班級和查詢項(xiàng)目。然后點(diǎn)擊“原始數(shù)據(jù)”工作表中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) 得到優(yōu)秀率。選中Q5:Q8單元格區(qū)域，拖動(dòng)其填充句柄向右至X列，這樣該班級的所有科目的全部數(shù)據(jù)就都有了。

我們可以選中全部數(shù)據(jù)，設(shè)置其格式為帶兩位小數(shù)的數(shù)字。 至于第二個(gè)表格就簡單了。

點(diǎn)擊Q13單元格，錄入公式=VLOOKUP($Q$11,$P$5:$X$8,COLUMN()-15,FALSE） 然后拖動(dòng)其填充句柄至X13單元格，就可以得到所需要的結(jié)果了。同樣可以設(shè)置其數(shù)字格式，如圖3所示。

圖3 設(shè)置數(shù)字格式 三、建立圖表 數(shù)據(jù)有了，打造圖表就簡單了。回到“班級查詢項(xiàng)目”工作表，在某空白單元格錄入公式“="期末考試"&B1&"班"&B2&"圖表"”。

點(diǎn)擊菜單命令“插入→圖表”，打開“圖表類型”對話框。選中“簇狀柱形圖”類型，點(diǎn)擊“下一步”按鈕，點(diǎn)擊“系列”選項(xiàng)卡，在“名稱”右側(cè)的輸 入框中輸入“=原始數(shù)據(jù)！$Q$11”，在“值”右側(cè)的輸入框中輸入“=原始數(shù)據(jù)！$Q$13:$X$13”，在“分類X軸標(biāo)志”右側(cè)的輸入框中輸入“= 原始數(shù)據(jù)！$Q$12:$X$12”，如圖4所示。

圖4 點(diǎn)擊“下一步”按鈕，點(diǎn)擊“數(shù)據(jù)標(biāo)志”選項(xiàng)卡，選中“值”復(fù)選項(xiàng)。 點(diǎn)擊“完成”按鈕，將制作好的圖表拖到工作表中合適的位置。

所得結(jié)果如圖5所示。 圖5 完成 最后，我們只需要在B1、B2單元格中選擇好要查詢的班級和查詢的項(xiàng)目，那么就會(huì)實(shí)時(shí)地在下方出現(xiàn)相關(guān)的數(shù)據(jù)圖表，用WPS的表格工具，一份學(xué)生成績分析表就完成了。

2.如何用WPS表格來分班級

在新的一學(xué)期，分班是一項(xiàng)學(xué)校的常規(guī)工作。傳統(tǒng)方法按一定的順序進(jìn)行分班，就是假定按成績分成4個(gè)班，第一輪，把1、2、3、4名次分到1、2、3、4班；第二輪，把5、6、7、8名次分別分到4、3、2、1班，依此類推。這樣一來，1班和4班每兩輪有最好的和最差的學(xué)生。以1班為例，分到1、8、9、16、17、24等名次，相鄰兩名次之間的差距兩次中就有一次為7太大了，另外一次為1，太小了，這樣會(huì)造成班主任也有一小點(diǎn)意見。

改進(jìn)方法：假定按成績分成4個(gè)班，第一輪，把1、2、3、4名分別分到1、2、3、4班；第二輪，把5、6、7、8名分別分到2、3、4、1班；第三輪，把9、10、11、12名分別分到3、4、1、2班；第四輪，把13、14、15、16名分別公到4、1、2、3班，依此類推。分析：從上面的分班過程來看，由于下一輪最好的名次分到了下一班級，這樣就保證了每個(gè)班在四輪中都能分到最好，最差和中間的名次，同樣以1班為例，可以分到1、8、11、14、17、24等名次，相鄰兩名之間差距，四次中只有一次為7，其余三次為3，太小的差距也消除了，也可以對比一下其它班級分到的名次情況，比按順序分班要好多了。

下面進(jìn)行實(shí)施步驟：

第一步：輸入好學(xué)生的相關(guān)信息，并按成績排列好順序。如圖。

現(xiàn)在是，就是在班級中，從A2開始，要形成這樣的序列：1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3，如此反復(fù)直到最后的學(xué)生如圖。

可以采用兩種方法：

1、可以采用復(fù)制粘貼法，就是先輸入上述數(shù)據(jù)，即1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3然后粘貼到所有的數(shù)據(jù)，直到最后。這種方法，比較簡單，也容易理解。

2、采用函數(shù)法：增加一列：編號，分別在下面輸入數(shù)據(jù)：0、1、2、3……直到最后一個(gè)學(xué)生，然后在A2中輸入公式：=mod(int(e2/4)+mod(e2,4),4)+1，拖動(dòng)填充柄，直到最后一名學(xué)生。同樣形成這樣的序列1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3……直到最后，如圖。

然后選擇所有數(shù)據(jù)，執(zhí)行“數(shù)據(jù)”→“篩選”→“自動(dòng)篩選”

打開班級后面的倒三角符號，選擇1，就可以得到1班的學(xué)生名冊如圖，

按所需整理，打印，任務(wù)完成。

3.WPS如何幫助老師輕松查詢學(xué)生各科成績

上學(xué)的時(shí)候，經(jīng)常到了期末各位同學(xué)的家長跑到學(xué)校來找老師咨詢自己孩子的期末考試情況，老師們也為了應(yīng)付做了本厚厚的成績本，一頁一頁的翻查著，其實(shí)利用WPS表格可以相當(dāng)輕松的解決相關(guān)查詢問題，免去一天到晚翻本子的功夫。具體如下：

效果預(yù)覽

① 制作好相關(guān)的數(shù)據(jù)，在單元格H3中輸入公式：“=VLOOKUP(H2,B4:E21,2,0)” VLOOKUP表示：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。

圖1

單元格H4、H5同樣輸入，公式根據(jù)對應(yīng)情況做相應(yīng)改變。

在G單元格輸入“姓名、語文、數(shù)學(xué) 等”相關(guān)資料。這樣，將所有同學(xué)的各科成績一一對應(yīng)起來。

② 在“總分”一欄（單元格H6）輸入求和公式：“=SUM(H3:H5)”，求出各科總和。

圖2

③ 在“平均分”一欄（單元格H7）輸入求平均值公式：“=AVERAGE(H3:H5)”求出各科平均分。

圖3

為了方便，再將該單元格格式設(shè)置成“小數(shù)”保留兩位。

圖4

④ 在“評價(jià)”一欄（單元格H8）輸入判斷公式：“=IF(H7>=60，"及格"，"不及格")”判斷及格或不及格（一般情況下，以60分作為標(biāo)準(zhǔn)）

最后將表格稍加美化，即可。

以后只要在“小白”處輸入對應(yīng)名字 就能輕松查詢某學(xué)生的成績情況。

4.如何用WPS表格來分班

第一步：輸入好學(xué)生的相關(guān)信息，并按成績排列好順序。如圖。

在“班級”中，從A2開始，要形成這樣的序列：1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3，如此反復(fù)直到最后的學(xué)生如圖。

可以采用兩種方法：

1、可以采用復(fù)制粘貼法，就是先輸入上述數(shù)據(jù)，即1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3然后粘貼到所有的數(shù)據(jù)，直到最后。這種方法，比較簡單，也容易理解。

2、在表格里增加一列”編號“，容納后在這一列里分別輸入數(shù)據(jù)：0、1、2、3……直到最后一個(gè)學(xué)生，然后在A2中輸入公式：=mod(int(e2/4)+mod(e2,4),4)+1，拖動(dòng)填充柄，直到最后一名學(xué)生。同樣形成這樣的序列1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3……直到最后。也可以采用不增加“編號”列的方法，直接在A2輸入：=MOD(INT((ROW()-2)/4)+MOD((ROW()-2),4),4)+1，同樣達(dá)到要求，如圖。

第二步：然后選擇所有數(shù)據(jù)，執(zhí)行“數(shù)據(jù)”→“篩選”→“自動(dòng)篩選”

打開班級后面的倒三角符號，選擇1，就可以得到1班的學(xué)生名冊，如下圖所示，

第三步：班級替換成本校的班級。打印，任務(wù)完成。