排名次用到了RANK函數(shù),它的語法為:
RANK(number,ref,order)
其中number為需要找到排位的數(shù)字。
Ref為包含一組數(shù)字的數(shù)組或引用。Ref 中的非數(shù)值型參數(shù)將被忽略。
Order為一數(shù)字,指明排位的方式。
·如果 order 為 0 或省略,Microsoft Excel 將 ref 當(dāng)作按降序排列的數(shù)據(jù)清單進(jìn)行排位。
·如果 order 不為零,Microsoft Excel 將 ref 當(dāng)作按升序排列的數(shù)據(jù)清單進(jìn)行排位。
最后,單擊L1單元格,然后在“工具”菜單中選“排序”->“升序”,即可按照名次順序顯示各學(xué)生成績。
另外,我們還希望把不及格的學(xué)科突出顯示,最好用紅色顯示。于是拖拉選擇C2:E78(即所有學(xué)生語、數(shù)、外三科成績),然后執(zhí)行"格式"菜單下"條件格式"命令,彈出"條件格式對話框"。我們把條件設(shè)為小于72分的用紅色顯示(因?yàn)檫@三科每科總分為120分),點(diǎn)擊"格式"按鈕,把顏色設(shè)為紅色。再按"確定"按鈕。然后用同樣的方法把理、化、政、歷四科小于60分的也用紅色顯示(因?yàn)檫@四科每科總分為100分)。
下面我們來統(tǒng)計(jì)各科的分?jǐn)?shù)段以及及格率、優(yōu)生率、綜合指數(shù)等。
下面我們來統(tǒng)計(jì)各科的分?jǐn)?shù)段以及及格率、優(yōu)生率、綜合指數(shù)等。
?。?)60分以下人數(shù):在C78單元格處輸入公式"=COUNTIF(C2:C77,"〈60")",拖動填充柄向右填充至I78單元格處;
?。?)60分~69分人數(shù):在C79單元格處輸入公式"=COUNTIF(C2:C77,"〉=60")-COUNTIF(C2:C77,"〉=70")",拖動填充柄向右填充;
?。?)70分~79分人數(shù):在C80單元格處輸入公式"=COUNTIF(C2:C77,"〉=70")-COUNTIF(C2:C77,"〉=80")",拖動填充柄向右填充;
?。?)80分~89分人數(shù):在C81單元格處輸入公式"=COUNTIF(C2:C77,"〉=80")-COUNTIF(C2:C77,"〉=90")",拖動填充柄向右填充;
?。?)90分以上人數(shù):在C82單元格處輸入公式"=COUNTIF(C2:C77,"〉=90")",拖動填充柄向右填充;
?。?)平均分:在C83單元格處輸入公式"=AVERAGE(C2:C77)",拖動填充柄向右填充至I83;
?。?)最高分:在C84單元格處輸入公式"=MAX(C2:C77)",拖動填充柄向右填充至I84;
?。?)低分率:是指各科40分以下人數(shù)與總?cè)藬?shù)的比值。在C85單元格處輸入公式"=COUNTIF(C2:C77,"〈=40")/COUNT(C2:C77)*100",拖動填充柄向右填充至I85;
?。?)及格率:語、數(shù)、外三科及格分為72分,所以在C86單元格處輸入公式"=(COUNTIF(C2:C77,"〉=72")/COUNT(C2:C77))*100",并拖動填充柄向右填充至E86;而理、化、政、歷等四科及格分60分,所以在F86單元格處輸入公式"=(COUNTIF(F2:F77,"〉=60")/COUNT(F2:F77))*100",并拖動填充柄向右填充至I86;
?。?0)優(yōu)生率:語、數(shù)、外三科96分以上為優(yōu)生,所以在C87單元格處輸入公式"=(COUNTIF(C2:C77,"〉=96")/COUNT(C2:C77))*100",拖動填充柄向右填充至E87;理、化、政、歷等四科80分以上為優(yōu)生,所以在F87單元格處輸入公式"=(COUNTIF(F2:F77,"〉=80")/COUNT(F2:F77))*100",拖動填充柄向右填充至I87處;如圖3所示。
?。?1)綜合指數(shù):我們學(xué)校的綜合指數(shù)的計(jì)算公式為z=[(1+優(yōu)生率-低分率)/2+及格率+平均分/該科總分]/3。所以在C88單元格處輸入公式"=((1+C87/100-C85/100)/2+C86/100+C83/120)/3",拖動填充柄向右填充至E88;在F88單元格處輸入公式"=((1+F87/100-F85/100)/2+F86/100+F83/100)/3",拖動填充柄向右填充至I88。如圖3所示。
對了,為了讓別人對各科的分?jǐn)?shù)段有一個(gè)較直觀的認(rèn)識,可以考慮采用圖表。單擊“插入”菜單中“圖表”命令,彈出“圖表向?qū)?#8221;對話框,在“圖表類型”列表框中選擇一種圖型,如“餅圖”,單擊“下一步”,單擊“數(shù)據(jù)區(qū)域”文本框右邊的壓縮列表框,拖拉選擇B78:C82,再次點(diǎn)擊該壓縮列表框;單擊“下一步”,輸入圖表標(biāo)題,如“高一(1)班語文成績分析圖”;單擊“下一步”,再單擊“完成”。如圖4所示。其它各科同樣處理,但在拖拉選擇數(shù)據(jù)區(qū)域時(shí),因?yàn)槭遣贿B續(xù)的區(qū)域,所以要按住“Ctrl”鍵。好!一切OK!
且慢!為了以后的考試中不再重復(fù)上述繁瑣的工作,最好把上述工作表另存為一個(gè)模板。于是我把上述工作表復(fù)制一份到另一工作簿中,然后刪掉所有學(xué)生的單科成績(即表中C2:I77部分),執(zhí)行"文件"菜單中的"另存為"命令,在"保存類型"下拉列表框中選"模板(*.xlt)",把它保存為一個(gè)模板文件,這下可以一勞永逸了。
本篇文章來源于 麥客教程網(wǎng)(學(xué)吧頻道) 原文鏈接:http://x8.maicoo.com/tech/excel/4782.html