Ｅｘｃｅｌ與成績統計你問我答

　　1．問：如何方便地將Excel里的學生成績用電子郵件發給每一位學生呢?
　　答：如有一個班成績單如圖1所示，以此為例為大家說明需要進行的操作。
　　第一步，在工作表輸入每個考生的郵箱地址，輸入在H列，在I列輸入郵件內容的問候語，為使郵件格式正確，在I1單元格輸入文字時要使用“Alt+Enter”組合鍵實現段落分行。將文字格式設置好后，將I1單元格內容向下復制到相應數據區域(圖1)。
　　
　　第二步，編寫代碼。執行“工具→宏→VisulBasic編輯器”命令或按下Alt+F11組合鍵，在ViSUl Basic編輯器中選擇“插入→添加模塊”命令，在代碼窗口輸入以下代碼：
　　Sub發送郵件()
　　Dim X，iCount AS，nteger
　　On Error Resume Next
　　iCount=Sheets(”Sheetl”)．[A1]．Current RegiOn．ROWS．Count／／把Sheetl表中記錄行數賦予iCount
　　Set Y=CreateOBject(“OutIOOk．App，ication”)／／創建Outlook應用程序對象
　　FOr X=2 To iCount
　　Set Z=Y．Create，tem(OiMaiIITem)／／新建一個郵件對象
　　With Z／／設置郵件屬性
　　To=CeIIs(X，8)／／在單元格中取收件人地址
　　SuBJect：”沙洋監獄管理局中學高一期中考試成績”／／加注郵件主題
　　BOdy=CeIIs(X，1)&CeIIs(X，9)&” 語文”&CeIIs(X，2)-
　　&數學“&CeIIs(X，3)&”英語”&CeIIs(X，4)&”綜合”&CeIIs(X，5)一
　　&“總分”&　CeIIs(X，6)&”名次”&CeIIs(X，7)／／設置郵件內容
　　．Anachments．Add”D：＼圖片＼1022．jpg”
　　．AnachmentS．Add”D:\\xdh＼1．DOC"，／／添加2個郵件附件，文件名后綴不能省略
　　．Send／／自動發送由P件
　　End With
　　Set Z：NOthing／／撤銷郵件對象
　　Next
　　Set Y：NOthing／／撤銷肖Outl00k應用程序對象
　　MsgBOx iCount-1&”個學生成績發送成功! ”
　　End Sub
　　輸入完成后，關閉VBA窗口，返回到工作表編輯窗口。
　　第三步，發送郵件。執行“工具—宏—宏”命令，打開“宏”對話框，選擇剛才創建的宏，點擊“執行”按鈕，郵件就開始通過Outlook發送了。執行“發送郵件”宏時，隨即會彈出一個詢問對話框，單擊“是”按鈕，郵件才能正常發送。此時郵件并沒有真正發送出去，所有郵件均被保存在Outl look發件箱中。我們還需執行Outlook應用程序，打開發件箱，雙擊任意郵件可以看見將要發送的郵件內容和附件(圖2)。
　　
　　點擊工具欄上“發送／接收”按鈕右側的下拉箭頭，選擇“全部發送”即可。這樣所有的郵件就被發送出去了(以上代碼在EXCel 2003和Outl00k2003中測試正常)。
　　2．問：用標準分來衡量學生的成績，是現行考試中一種常見的方法。我們多用Excel統計學生的原始分，能否用它計算出標準分，方便快速地了解學生在考試中的排位情況呢?
　　答：標準分的計算原理是，對于每一個分數Xi，先算出這個分數以下的考生數占考生總數的百分比Pi，再查這個Pi對應于正態分布表中最接近的數值Zi，最后通過公式“標準分：Zi x 100+人數”算得標準分。
　　標準分總分的計算方法是：把每位考生各科的標準分相加求和，再算出這個和以下學生成績的學生數，除以總人數得到Pi值，再按照以上求各科標準分的方法，完成標準總分的轉換過程。
　　在Excel中計算標準分，要用到下面的函數：
　　●RANK函數(返回一個數字在數字列表中的排位)。
　　●NORMSDIST(返回標準正態累積分布函數，該分布的平均值為o，標準偏差為1)。
　　●NORMSINV(返回標準正態累積分布函數的反函數。該分布的平均值為o，標準偏差為1)。
　　以下面的例子來說明具體的操作方法。
　　如圖3，假設該年級學生共有500人。在H1單元格單擊，輸入公式“＝100 x NORMSINV(RANKD2， D$2：D$501，1)一1)／(COUNTA(D$2：D$501)+500”后回車，如果輸入無誤，可看到該單元格按照語文原始分轉化出來的語文標準分。 由于函數NORMSINV(0)返回的值是“#NUM!”(在以上公式中，就是最低分考生)，而標準分最低分是1O0分，因此，進行標準分轉換時，在顯示“#NUM!”的單元格上，必須手工把它改為1OO。
　　
　　設置H2單元格的“單元格格式”為“數值”、“小數點位數”是o，讓顯示出來的標準分都是整數。選擇H2單元格，橫向拖動填充句柄到K2單元格，這樣便完成了第一位考生各科標準分的轉換工作。用鼠標選擇H2—K2單元格，縱向拖動填充句柄到最后一位考生所在的行，我們便完成了所有考生單科成績轉換的工作。
　　在圖3中，L列“標準分之和”是每位考生各科成績轉換成標準分后求和，這是為計算標準分做準備的，完成L列設置后，在M2單元格中輸入公式：“＝100*NORMSINV(RANKL2，L$2：L$501，1)-1)／(COUNTAL$2：L$501)+500”后回車，再選擇這一單元格，縱向拖動填充句柄到最后一行的學生即可。
　　利用Exccl解讀標準分時，根據標準分轉換的原理，我們可以用NORMSINV函數表示標準分。
　　標準分=100*NORMSINV(Pi)+500
　　所以，NORMSINV(Pi＝(標準分-500)／100
　　所以，Pi=NORMS，NV—1((標準分—500)／100)
　　即：Pi：NORMSDIST((標準分-500)／100)
　　求出Pi后，我們便知道該考生在這次考試中的排位情況。
　　例如在某次考試中，有5000名考生，張三的總分是676分，這時我們根據以上分析的方法來確定這位考生在這次考試中的排位情況。則
　　Pi＝NORMSDIST((676—500)／100)=O．960796=96．0796％
　　可見，在這次考試中，在張三成績以下的考生人數的比例約是96．0796％，而高于或等于676分的學生的比例約是3．9204％，因為總人數為5000人，所以，與張二成績同分或以上的學生總人數約是196人(3．9204％x 5000)，而在張三成績之下的學生約有4 804人。這樣，考生張三在這次考試中所處的位置便一目了然