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

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

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

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

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

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

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

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

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

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

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

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

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