1、利用Excel進行數據統計的若干技巧 一、認識常用的統計函數：統計區域1、SUM：計算單元格區域內所有數據的和。表達式為：=SUM（D2：F50）2、AVERAGE：計算單元格區域內所有數據的算術平均數。表達式為：=AVERAGE（D2：F50）3、COUNT：計算表中的數字參數和包含數字的單元格的個數。給定條件表達式為：=COUNT（D2：F50）4、COUNTIF：計算單元格區域內滿足給定條件單元格的個數。 表達式為：=COUNTIF（D2：F50，">=90"）5、MAX：返回一組數值中的最大值。表達式為：=MAX（D2：F50）6、MIN：返回一組數值中的最小

2、值。起始位置表達式為：=MIN（D2：F50）7、MIDB：自文字起始位置開始提取指定長度字符串。表達式為：=MIDB(B2,8,6)提取字符串長度指定字符指定第K個數8、LARGE：返回數據中第K個最大值。表達式為：=LARGE(F2:F34,11)9、SMAL：返回數據中第K個最小值。指定數字表達式為=SMALL(F2:F28,5)10、RANK：返回指定數字在列中的排位。指定數列表達式為：=RANK(I2,I:I)利用好統計公式中的定位符$ 例：RANK(I2,$I$2:$I$180)11、EXACT：比較兩個字符串是否完全相同（區分大小寫），如果完全相同，返回TREU，否則返回FALS

3、E。表達式為：=EXACT(D2,J2)12、VALUE：將代表數值的文本字符串轉換成數值。表達式為：=VALUE(F2)13、IF：判定一個條件是否滿足，如果滿足則返回一個值，如果不滿足則返回另一個值。不滿足條件的結果指定條件滿足條件返回顧1，不滿足條件返回0 表達式為：=IF(F2>=80,"合格","不合格")第一條件第三條件同時滿足多重條件的表達式：=IF(C2>=90,IF(D2>=84,IF(E2>=38,1,0),0),0)注意：表達式中輸入的數據、符號必須在英文狀態下輸入。 二、簡單數據的簡便統計：（一）數據整理。

4、 1、按單位整體集中數據（班為單位），統一編序號，刪除與統計無關項目。把學校、班級列調整到靠近數據區。（操作方法：剪切要移動列選中要移入的目標列點擊“【插入已剪切的單元格】”） 2、按升序或降序方式逐項清理各科數據，處理不合理數據（文本數據，超滿分數據）。注意：利用升降序工具進行操作時，數據必須至少有一方與數據區域相連。而按菜單【數據】【排序】操作則可避免此問題。 3、劃分分數線。按升序或降序方式：例：按A段人數（3500*40%=1400），選中語文科第1400行數據，將語文科降序排列，選中單元格即語文科A段分數線；其它學科照此類推。（二）利用分類匯總工具進行數據統計。1、平均分：【數據】【

5、分類匯總】【分類字段】：班級【匯總方式】：平均值【選定匯總項】：各科均選中【確定】【分級顯示】：選中2級。抄錄有關數據到統計總表。2、合格段人數：【移動或復制工作表】（選中【建立副本】）語文科成績降序排列刪除分數線下所有單元格數據。以班為單位排序【分類匯總】【分類字段】：班級【匯總方式】：計數【選定匯總項】：語文科【確定】【分層顯示】：選中2層。抄錄有關數據到統計總表。其它各科數據照此類推。3、數據高級篩選：（適用于均合格；有效上線人數）（1）數據前插入條件區域，建立條件項：（在相應欄目上方，與數據區域有隔斷行）語文 數學 總分>85 >78 >125（2）篩選數據：【數據】

6、【篩選】【高級篩選】點中【 “篩選結果復制到基它區域” 】 選定數據區域選定條件區域選定篩選結果存放區域【確定】（3）對篩選出來區域的數據進行統計。（三）利用數據透視表進行數據統計。（1）【數據】【數據透視表和數據透視圖】根據“數據透視表和數據透視圖導向”進行操作：（2）【鍵入數據源區域】【下一步】【現有工作表】，指定建立統計區域位置【布局】：“學校”拖入“行”，“班級”拖入“列”，各科成績拖入“數據”（雙擊科目可變更統計項，也可將各科成績多次拖入，變更成不同的統計項）【確定】【完成】（3）數據透視表中數據的顯示：點擊學校項下拉箭頭，可顯示指定學校數據。點擊班級項下拉箭頭，可顯示指定班級數據。

7、（四）建立班級統計模板1、在原始成績表輸入學生原始成績。（學生順序不能變，缺考空位）（1）鎖定單元格：【工具】【保護】【允許用戶編輯區域】【新建】【引用單元格】：選擇可編輯區（各科成績、總分、位次列）【工作表保護】【確定】（2）計算總分：選中總分下第一格輸入=SUM(C2:F2) 【確定】填充 計算位次：選中總分下第一格輸入=RANK(G2,G:G) 確定填充2、建班級檔案工作表（1）在班級檔案工作表中選中單元格A1，輸入=原始成績!A1 確定拖動拖動填充柄至B56，復制學生信息。（2）在班級檔案工作表中選中單元格C1，輸入=原始成績!G1 確定拖動拖動填充柄至D56，復制第一次總分、位次信息

8、。（3）在班級檔案工作表中選中C、D兩列，【復制】選中E、F兩列，【選擇性粘貼】【粘貼數值】重命名列標題，固定第一次總分、位次信息。以后每次獲得的信息均以此方式固定，即可形成學生成績檔案。3、建成績分析表（1）選中“成績分析”工作表，建立統計項：標題、科目、考試人數、各分數段、最高分、最低分、平均分（2）分別在各統計項后單元格內輸入統計公式：考試人數：=COUNT(原始成績!C:C) 確定拖動填充柄至各科。90-100分人數：=COUNTIF(原始成績!C:C,">=90")確定拖動填充柄至各科。80-89分人數：=COUNTIF(原始成績!C:C,">

9、;=80")-B4確定拖動填充柄至各科。70-79分人數：=COUNTIF(原始成績!C:C,">=70")-B4-B5確定拖動填充柄至各科。60-69分人數：=COUNTIF(原始成績!C:C,">=60")-B4-B5-B6確定拖動填充柄至各科。60分以下人數：=COUNTIF(原始成績!C:C,"<60")確定拖動填充柄至各科。最高分：=MAX(原始成績!C:C) 確定拖動填充柄至各科最低分：=MIN(原始成績!C:C) 確定拖動填充柄至各科平均分：=AVERAGE(原始成績!C:C) 確定拖動填充柄

10、至各科（3）使用圖表分析數據：【插入】【圖表】【圖表向導】【圖表類型】：餅型【下一步】【數據區域】單擊【折疊對話框】：選擇數據區域（只選擇與本學科有關的數據，不連續區域的選擇辦法是：選了第一區域后，按住CTEL控制鍵，選第二區域；依次類推）【下一步】【下一步】【作為其中對象插入】【確定】三、大面積數據的綜合統計（一）整理數據：1、列的排列依次為：考號、學校、班級、各科成績及總分2、學校、班數據集中。3、利用函數LARGE（返回數據中第K個最大值。）（1）確定分數線。復制各科標題為分數線標題排在數據區域之右側，選中分數線存放位置；分數段人數在公式編輯欄中輸入分式：例：學科數據區域 =LARGE(

11、F2:F34,10)（2）確定第一科分數線后，向右拖動填充柄，即可獲取各科分數線；在下行復復制公式，更改分數段人數，即可獲取另一層次分數線。（3）將分數線數據復制到新建空白統計表中：更改表名（選中表標簽，點右鍵，重命名：分數線）；復制數據表中分數線在分數線表中選中左上角單元格點鼠標右鍵【選擇性粘貼】【粘貼：數值】。（二）分類匯總1、點擊數據區域。【數據】【分類匯總】【分類字段】：學校【匯總方式】：計數【選定匯總項】：第一科【確定】。2、再次以班為字段分類匯總。【數據】【分類匯總】【分類字段】：班級【匯總方式】：計數【選定匯總項】：第一科【確定】。把【 “替換當前分類匯總” 】選項取消。3、篩選

12、：（1）選中表中任意單元格。【數據】【篩選】【自動篩選】點擊學校列【下拉箭頭】【自定義】【包含】內容內輸入“計數”（2）將篩選出的“學校計數項”復制到班級列。（選中復制區域，拖動填充柄到班級列釋放即可）（3）點擊學校列下拉箭頭【顯示全部數據】。（4）在“班級”列重復第（1）步操作：點擊班級列下拉箭頭【自定義】【包含】內容內輸入“計數”（三）建立統計區1、在數據區域正面（隔兩行）建立統計項目：（空） 學校 班級 參統人數 語文 數學 平均分 A段數 B段數 平均分 A段數 B段數2、替換引用區：選中第一科（即D列）所有篩選出的項目，執行替換操作：【編輯】【替換】【查找內容】：D【替換為】$D$【

13、全部替換】3、替換公式：選中第一科（即D列）所有篩選出的項目，執行替換操作：【編輯】【替換】【查找內容】：subtotal(3,【替換為】counta(【全部替換】4、將替換后的內容整體復制到統計區域：選中第一科（即D列）所有篩選出的項目，復制選中統計區“學校”項下單元格，點擊鼠標右鍵【選擇性粘貼】【粘貼公式】。5、刪除原始數據區內含公式的行：選中原始數據區內所有篩選出的行，全部刪除6、去除行標題中“計數”字符：選中統計區內所有包含“計數”字符的區域，執行替換操作：【編輯】【替換】【查找內容】：計數【替換為】 （保持空格）【全部替換】（四）分科統計（統計第一科數據）1、復制參考人數欄內數據到“

14、語文”科下各統計項下”平均分” 、“A段數”列下：選中參考人數欄內數據，拖動填充柄到目標列下釋放。2、選中“平均分”列所有數據，執行替換操作：【編輯】【替換】【查找內容】：counta【替換為】:average【全部替換】3、選中“A段數”列所有數據，執行替換操作：【編輯】【替換】【查找內容】：）【替換為】：,”>=85” 【全部替換 】 (即：把后括號替為A段分數線)4、選中“A段數”列所有數據，再次執行替換操作：【編輯】【替換】【查找內容】：counta【替換為】：countif【全部替換】 5、復制“A段數”列下數據到 “B段數”列下：(拖動填充柄到目標列下釋放)6、選中“B段數”

15、列所有數據，執行替換操作：【編輯】【替換】【查找內容】：”>=85” 【替換為】：”>=70” 【全部替換】(即：把A段分數線替為B段分數線) (五) 分科統計（統計其它各科數據）1、將第一科統計結果復制到其它各科統計欄目下。2、分科替換引用區：選中第二科下所有數據，執行替換操作：【編輯】【替換】【查找內容】：$D$【替換為】：$E$【全部替換】。(即：把引用的D列（語文科）的原始數據替為E列（數學科）的原始數據。3、分段替換分數線：（1）把語文A段分數線替為數學A段分數線：選中數學A段下所有數據，執行替換操作：【編輯】【替換】【查找內容】：”>=85” 【替換為】：”>

16、;=72” 【全部替換】 （2）把語文B段分數線替為數學B段分數線：【編輯】【替換】【查找內容】：”>=72” 【替換為】：”>=69” 【全部替換 】4、照此類推，替換其它所有學科下的數據。（六）建立統計表1、插入新的空白工作表，明確統計方案：一、分數線：“合格”按參考人數的98%劃線，“優秀”按參考人數的50%劃線；“優生”人數中包括“合格”人數。二、按考四科和三科兩條線統計到學科、班級、學校。學校按層次進行分析。三、學科統計“平均分”、“合格率”、“優秀率”三項；學校統計“總分平均分”、“總分合格率”、“均合格率”、“均優率”四項。“均合格（優）率”按語數外綜四科統計。四、相

17、關公式：學科平均分比率=平均分/該科全縣最高平均分*100學科合格率=合格人數/參考人數*100優秀率=優秀人數/參考人數*100總分平均分比率=總分平均/全縣學校最高平均分*100總分合格率=總分合格人數/參考人數*100均合格（優）率=四科均合格（優）人數/參考人數*100學科積分=平均分比率+合格率+優生率學校積分=總分平均分比率*25%+總分合格率*25%+均合格率*25%+均優秀率*25%2、復制數據到空白統計表中選中統計區內的所有數據：復制選中空白統計表左上角：【選擇性粘貼】【粘貼數字】3、在各段人數后分別插入空白列。4、在“語文A段”列后空白列自定義統計公式：選中該列數據區第一行單元格。在公式編輯欄輸入公式：=d3/$c3*100，【確定】雙擊填充柄復制公式到該列所有單元格。5、將“語文A段”的統計公式復制到其它各科各段人數列后：選中“語文A段”列的統計公式，復制。分別粘貼到其它空白列：【選擇性粘貼】【粘貼公式】6、計算各科積分：分別在各科后插入空白列。學科積分=平均分比率+合格率+優生率在公式編輯欄輸入公式： =d4/MAX(d:d)*100+f4+h4，【確定】7、排位在積分后插入空白列。在公式編輯欄輸入公式：=RANK(I2,I$4