一、前言
對(duì)于教師而言,經(jīng)常需要用Excel進(jìn)行學(xué)生成績(jī)統(tǒng)計(jì),會(huì)被一些常見問題難住。碰到的難題主要有:如何統(tǒng)計(jì)不同分?jǐn)?shù)段人數(shù)、如何在保持學(xué)號(hào)不變前提下進(jìn)行排名、如何將百分制轉(zhuǎn)換成不同分?jǐn)?shù)段與如何用紅色顯示不及格的分?jǐn)?shù)等,本文著重對(duì)這些問題的解決方法與技巧加以分析和討論。 本文假設(shè)讀者已對(duì)Excel的基本操作已經(jīng)有一定基礎(chǔ),已經(jīng)掌握如何進(jìn)行求和、求平均和如何使用自動(dòng)填充柄進(jìn)行復(fù)制公式等操作,本文對(duì)這些一般性操作不做詳細(xì)介紹,僅對(duì)一些難度較大的操作技巧進(jìn)行討論。 二、Excel統(tǒng)計(jì)學(xué)生成績(jī)時(shí)的四個(gè)難題 假設(shè)在統(tǒng)計(jì)學(xué)生成績(jī)時(shí),我們需要統(tǒng)計(jì)出如圖1所示的相關(guān)結(jié)果。 在 圖1 學(xué)生成績(jī)統(tǒng)計(jì)所需要的結(jié)果圖 這里,假設(shè)學(xué)號(hào)、姓名、成績(jī)等列及行15都已經(jīng)事先輸好,需要讓Excel統(tǒng)計(jì)其他的相關(guān)數(shù)據(jù)結(jié)果。這時(shí),成績(jī)統(tǒng)計(jì)中主要難解決的問題及它們?cè)趫D中的位置如下: 問題1:如何統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)?(圖中A16~E16) 問題2:如何在保持學(xué)號(hào)順序不變的前提下進(jìn)行學(xué)生成績(jī)名次排定?(圖中F2~F13) 問題3:如何將百分制轉(zhuǎn)換成不同的等級(jí)分?(圖中“等級(jí)1”與“等級(jí)2”列) 問題4:如何使不及格的分?jǐn)?shù)以紅色顯示?(圖中紅色顯示部分,即第12行)
三、解決統(tǒng)計(jì)學(xué)生成績(jī)時(shí)的四個(gè)難題的方法 下面,針對(duì)上面提出的四個(gè)難題分別討論解決的方法與技巧。 1、統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù) 統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)是非常常見的需求,其所需結(jié)果如圖1中A16~E16所示。這里,假設(shè)需要統(tǒng)計(jì)90~100、80~89、70~79、60~69及低于60分五個(gè)不同分?jǐn)?shù)段的人數(shù)。 通常,統(tǒng)計(jì)不同分?jǐn)?shù)段最好的方法是利用COUNTIF(X,Y)函數(shù)。其中有兩個(gè)參數(shù),第一個(gè)參數(shù)X為統(tǒng)計(jì)的范圍,一般最好用絕對(duì)引用;第二個(gè)參數(shù)Y為統(tǒng)計(jì)條件,要加引號(hào)。 對(duì)于小于60分的人數(shù)只要用一個(gè)COUNTIF( )函數(shù),如在E16單元格中輸入公式:=COUNTIF($C$2:$C$13,"<60")。 對(duì)于其他在兩個(gè)分?jǐn)?shù)之間的分?jǐn)?shù)段的人數(shù)統(tǒng)計(jì),需要用兩個(gè)COUNTIF( )函數(shù)相減。如在A16單元格中輸入公式:=COUNTIF($C$2:$C$13,"<=100")-COUNTIF($C$2:$C$13,"<90"),即用小于等于100的人數(shù)減去小于90的人數(shù)。 如果要統(tǒng)計(jì)80~89、70~79與60~69分?jǐn)?shù)段的人數(shù),只要利用自動(dòng)填充柄將該公式復(fù)制到右邊三個(gè)單元格,再把"<=100"與"<90"作相應(yīng)的修改,就可以得到正確的結(jié)果。 2.保持學(xué)號(hào)順序不變的前提下進(jìn)行成績(jī)排名 學(xué)生成績(jī)排定在學(xué)生成績(jī)統(tǒng)計(jì)中經(jīng)常用到。特別要強(qiáng)調(diào)的是,這里所談的方法不是一般的排序,因?yàn)槟菢訒?huì)使學(xué)生的學(xué)號(hào)順序發(fā)生變化。這里所需要的是在保持學(xué)號(hào)順序不變的情況下進(jìn)行學(xué)生成績(jī)名次排定的功能,其所需結(jié)果如圖1中F2~F13所示。 要進(jìn)行保持學(xué)號(hào)順序不變的情況下進(jìn)行學(xué)生成績(jī)名次的排定,最好使用RANK(X,Y,Z)函數(shù)。其中有三個(gè)參數(shù),第一個(gè)參數(shù)X為某個(gè)學(xué)生的成績(jī)所在單元格;第二個(gè)參數(shù)Y為整個(gè)班級(jí)成績(jī)所在的區(qū)域;第三個(gè)參數(shù)Z是可選的,表示統(tǒng)計(jì)方式,若省寫或?qū)?,則成績(jī)高的名次靠前,一般都使用這種方式,如果寫1,則成績(jī)高的名次靠后,這種情況一般較少用。 為了在保持學(xué)號(hào)順序不變的前提下進(jìn)行學(xué)生成績(jī)名次排定,可以在F2單元格中輸入公式:=RANK(C2,$C$2:$C$13,0),然后,利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。注意,這里$C$2:$C$13用的是絕對(duì)地址,是為了保證公式在復(fù)制時(shí)此處不變,因?yàn)樽鳛榈诙€(gè)參數(shù),這里都是指整個(gè)班級(jí)成績(jī)所在的區(qū)域,這個(gè)區(qū)域是相同的。 3、將百分制轉(zhuǎn)換成不同的等級(jí)分 將百分制轉(zhuǎn)換成不同的等級(jí)分有多種不同的劃分方法,其所需結(jié)果如圖1中“等級(jí)1”與“等級(jí)2”列所示。這里,“等級(jí)1”列是將百分制的分?jǐn)?shù)轉(zhuǎn)換成A(90~100)、B(80~89)、C(70~79)、D(60~69)與E(低于60)五個(gè)等級(jí);“等級(jí)2”列是將百分制的分?jǐn)?shù)轉(zhuǎn)換成優(yōu)(90~100)、良(75~89)、中(60~74)與不及格(低于60)四個(gè)等級(jí)。具體使用哪種等級(jí)劃分方法可根據(jù)實(shí)際情況自己確定。 在百分制轉(zhuǎn)換成不同的等級(jí)分時(shí),一般使用IF(X,Y,Z)函數(shù)。其中有三個(gè)參數(shù),第一個(gè)參數(shù)X為條件,不能加引號(hào);第二個(gè)參數(shù)為條件成立時(shí)的結(jié)果,如果是顯示某個(gè)值,則要加引號(hào);第三個(gè)參數(shù)為條件不成立時(shí)的結(jié)果,如果是顯示某個(gè)值,同樣要加引號(hào)。該函數(shù)可以嵌套,即在第二個(gè)或第三個(gè)參數(shù)處可以再寫一個(gè)IF函數(shù)。 為了得到“等級(jí)1”列所要的等級(jí)結(jié)果,可以在D2單元格中輸入公式:=IF(C2>=90,"A",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D","E")))),然后,利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。 為了得到“等級(jí)2”列所要的等級(jí)結(jié)果,可以在E2單元格中輸入公式:=IF(C2>=90,"優(yōu)",IF(C2>=75,"良",IF(C2>=60,"中","不及格"))),然后,利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。 4、使不及格的分?jǐn)?shù)以紅色顯示 統(tǒng)計(jì)學(xué)生成績(jī)時(shí)經(jīng)常需要將不及格的分?jǐn)?shù)用紅色顯示,其結(jié)果如圖1中紅色顯示部分(如第12行)。 使不及格的分?jǐn)?shù)以紅色顯示需要使用“格式”菜單中的“條件格式”命令。該命令會(huì)彈出一個(gè)對(duì)話框,其中要求確認(rèn)條件與相應(yīng)的格式。 對(duì)于“成績(jī)”列,可先選中C2:C13,然后使用“格式”菜單中的“條件格式”命令,在彈出的對(duì)話框中,左邊使用默認(rèn)的“單元格數(shù)值”,中間選“小于”,右邊填寫60,然后單擊右邊的“格式”按鈕,從中選擇紅色,最后單擊兩次“確定”按鈕。 對(duì)于“等級(jí)1”列,可先選中D2:D13,然后使用“格式”菜單中的“條件格式”命令,在彈出的對(duì)話框中,左邊使用默認(rèn)的“單元格數(shù)值”,中間選“等于”,右邊填寫E,然后單擊右邊的“格式”按鈕,從中選擇紅色,最后單擊兩次“確定”按鈕。“等級(jí)2”列類似。 對(duì)于其他的一些統(tǒng)計(jì)計(jì)算要求,如怎樣計(jì)算各分?jǐn)?shù)段的百分比、如何計(jì)算機(jī)優(yōu)良率與合格率等功能,應(yīng)該比較簡(jiǎn)單,本文此處不贅述。 |
|