利用EXCEL進行數(shù)據(jù)統(tǒng)計若干技巧

1、利用Excel進行數(shù)據(jù)統(tǒng)計的若干技巧一、認識常用的統(tǒng)計函數(shù):1、SUM：計算單元格區(qū)域內(nèi)所有數(shù)據(jù)的和。表達式為：=SUM（D2： F50）2、AVERAGE:計算單元格區(qū)域內(nèi)所有數(shù)據(jù)的算術平均數(shù)。表達式為：=AVERAGE（D2： F50）3、 COUNT:計算表中的數(shù)字參數(shù)和包含數(shù)字的單元格的個數(shù)。_表達式為：=COUNT（D2： F50）給定條件4、COUNTIF:計算單元格區(qū)域內(nèi)滿足給定條件單元格的個數(shù)。表達式為：=COUNTIF（D2： F50，5、MAX：返回一組數(shù)值中的最大值。表達式為：=MAX（D2： F50）6、MIN：返回一組數(shù)值中的最小值。起始位置表達式為：=MIN（D2：

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

3、為：=EXACT（D2,J2）12、VALUE:將代表數(shù)值的文本字符串轉換成數(shù)值。表達式為：=VALUE（F2）13、IF：判定一個條件是否滿足，如果滿足則返回一個值，如果不滿足則返回另一個值。指定條件不滿足條件的結果表達式為：=IF（F2=80,合格，不合格）第一條件第三條件滿足條件返回顧1，不 滿足條件返回0同時滿足多重條件的表達式：=IF(C2=90,IF (D2=84,IF(E2=38,1,0),0),0)注意：表達式中輸入的數(shù)據(jù)、符號必須在英文狀態(tài)下輸入。二、簡單數(shù)據(jù)的簡便統(tǒng)計：（一）數(shù)據(jù)整理。1、按單位整體集中數(shù)據(jù)（班為單位），統(tǒng)一編序號，刪除與統(tǒng)計 無關項目。把學校、班級列調(diào)整到

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

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

6、區(qū)域一選定條件區(qū)域一選定 篩選結果存放區(qū)域一【確定】（3）對篩選出來區(qū)域的數(shù)據(jù)進行統(tǒng)計。（三）利用數(shù)據(jù)透視表進行數(shù)據(jù)統(tǒng)計。（1）【數(shù)據(jù)】一【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】一根據(jù)“數(shù)據(jù)透視表和 數(shù)據(jù)透視圖導向”進行操作：（2）【鍵入數(shù)據(jù)源區(qū)域】一【下一步】一【現(xiàn)有工作表】，指定建立 統(tǒng)計區(qū)域位置一【布局】：學校”拖入“行”，“班級”拖入“列”， 各科成績拖入“數(shù)據(jù)”（雙擊科目可變更統(tǒng)計項，也可將各科成績多 次拖入，變更成不同的統(tǒng)計項）一【確定】一【完成】（3）數(shù)據(jù)透視表中數(shù)據(jù)的顯示：點擊學校項下拉箭頭，可顯示指定 學校數(shù)據(jù)。點擊班級項下拉箭頭，可顯示指定班級數(shù)據(jù)。（四）建立班級統(tǒng)計模板1、在原始成績表

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

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

9、各科。70-79 分人數(shù)：=COUNTIF（原始成績!C:C,=70）-B4-B5確定一拖 動填充柄至各科。60-69 分人數(shù)：=COUNTIF（原始成績!C:C,=60）-B4-B5-B6確定一 拖動填充柄至各科。60分以下人數(shù)：=COUNTIF（原始成績!C:C,=85”一【全 部替換】（即：把后括號替為A段分數(shù)線）4、選中“A段數(shù)”列所有數(shù)據(jù)，再次執(zhí)行替換操作：【編輯】一【替換】一【查找內(nèi)容】：counta 【替換為】：countif- 【全部替換】5、復制“A段數(shù)”列下數(shù)據(jù)到“B段數(shù)”列下：（拖動填充柄到目標列下 釋放）6、選中“B段數(shù)”列所有數(shù)據(jù)，執(zhí)行替換操作：【編輯】一【替換】一【

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

11、數(shù)線：【編輯】一【替換】一【查找內(nèi)容】：”=72”一【替換為】：”=69” 一【全部替換】4、照此類推，替換其它所有學科下的數(shù)據(jù)。（六）建立統(tǒng)計表1、插入新的空白工作表，明確統(tǒng)計方案：一、分數(shù)線：合格”按參考人數(shù)的98%劃線，優(yōu)秀”按參考 人數(shù)的50%劃線；優(yōu)生”人數(shù)中包括合格”人數(shù)。二、按考四科和三科兩條線統(tǒng)計到學科、班級、學校。學校按層次 進行分析。三、學科統(tǒng)計平均分”、“合格率”、“優(yōu)秀率”三項；學校統(tǒng) 計總分平均分”、“總分合格率”、均合格率”、均優(yōu)率”四項。均合格（優(yōu)）率”按語數(shù)外綜四科統(tǒng)計。四、相關公式：學科平均分比率=平均分/該科全縣最高平均分*100學科合格率=合格人數(shù)/參考人

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