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

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

這份表格放在“原始數據”工作表中。現在我們需要方便地查詢各個班各學科的總分、平均分、及格率、優秀率四個項目。

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

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

其它均采用默認設置。 用同樣的方法設置B2單元格的數據有效性，只是其來源為“總分，平均分，及格率，優秀率”。

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

在P3:X13區域建立如圖2所示的表格。 圖2 點擊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列，這樣該班級的所有科目的全部數據就都有了。

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

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

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

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

圖4 點擊“下一步”按鈕，點擊“數據標志”選項卡，選中“值”復選項。 點擊“完成”按鈕，將制作好的圖表拖到工作表中合適的位置。

所得結果如圖5所示。 圖5 完成 最后，我們只需要在B1、B2單元格中選擇好要查詢的班級和查詢的項目，那么就會實時地在下方出現相關的數據圖表，用WPS的表格工具，一份學生成績分析表就完成了。

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

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

改進方法：假定按成績分成4個班，第一輪，把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班，依此類推。分析：從上面的分班過程來看，由于下一輪最好的名次分到了下一班級，這樣就保證了每個班在四輪中都能分到最好，最差和中間的名次，同樣以1班為例，可以分到1、8、11、14、17、24等名次，相鄰兩名之間差距，四次中只有一次為7，其余三次為3，太小的差距也消除了，也可以對比一下其它班級分到的名次情況，比按順序分班要好多了。

下面進行實施步驟：

第一步：輸入好學生的相關信息，并按成績排列好順序。如圖。

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

可以采用兩種方法：

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

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

然后選擇所有數據，執行“數據”→“篩選”→“自動篩選”

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

按所需整理，打印，任務完成。

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

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

效果預覽

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

圖1

單元格H4、H5同樣輸入，公式根據對應情況做相應改變。

在G單元格輸入“姓名、語文、數學 等”相關資料。這樣，將所有同學的各科成績一一對應起來。

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

圖2

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

圖3

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

圖4

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

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

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

4.如何用WPS表格來分班

第一步：輸入好學生的相關信息，并按成績排列好順序。如圖。

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

可以采用兩種方法：

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

2、在表格里增加一列”編號“，容納后在這一列里分別輸入數據：0、1、2、3……直到最后一個學生，然后在A2中輸入公式：=mod(int(e2/4)+mod(e2,4),4)+1，拖動填充柄，直到最后一名學生。同樣形成這樣的序列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，同樣達到要求，如圖。

第二步：然后選擇所有數據，執行“數據”→“篩選”→“自動篩選”

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

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