自己動(dòng)手制作excel成績(jī)分析表(一勞永逸)轉(zhuǎn).doc

《自己動(dòng)手制作excel成績(jī)分析表(一勞永逸)轉(zhuǎn).doc》由會(huì)員分享，可在線閱讀，更多相關(guān)《自己動(dòng)手制作excel成績(jī)分析表(一勞永逸)轉(zhuǎn).doc（7頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

原文地址：自己動(dòng)手制作excel成績(jī)分析表（一勞永逸）作者：李國(guó)磊 效果圖如下：表一：文科成績(jī) 表二：理科成績(jī) 表三：文科成績(jī)統(tǒng)計(jì)分析 表四：理科成績(jī)統(tǒng)計(jì)分析 同一個(gè)文件中有四個(gè)工作表：“文科”“理科”“文科統(tǒng)計(jì)”“理科統(tǒng)計(jì)” 其中成績(jī)表中包含有：總分，年級(jí)名次（相同的分?jǐn)?shù)相同的名次），班級(jí)名次（在不用分班的情況下統(tǒng)計(jì)班級(jí)名次），單科年級(jí)名次，與上一次考試的對(duì)比（是進(jìn)不了還是退步了） 統(tǒng)計(jì)表中包含有：每個(gè)班各個(gè)分?jǐn)?shù)段的人數(shù)，年級(jí)各個(gè)分?jǐn)?shù)段的人數(shù)，年級(jí)累計(jì)人數(shù)，每個(gè)班年級(jí)前多少名的人數(shù) 對(duì)于我們老師來說，這樣的一張表足可以滿足我們分析成績(jī)的需要，如果每次考試都統(tǒng)計(jì)一次，工作量就會(huì)增加很多，并且是無效的重復(fù)勞動(dòng)，為此鄙人用了四個(gè)小時(shí)的時(shí)間制作了這樣的一張模板表，每次我們只需要把單科的成績(jī)復(fù)制進(jìn)來，就會(huì)自動(dòng)求出總分，排出單科的名次，年級(jí)名次，班級(jí)名次以及與上一次的對(duì)比情況，還有各個(gè)分?jǐn)?shù)段的人數(shù)，可以為我們省去很多不必要的麻煩，先將制作方法說明如下，有興趣的朋友我們一起探討，設(shè)計(jì)出更好的模板。 1、求總分 這個(gè)大家都會(huì)，就不過多解釋了，直接用SUM函數(shù)就行了 2、怎樣排出年級(jí)名次，要求同一個(gè)分?jǐn)?shù)有相同的名次，如效果圖 方法：在G2單元格中輸入 =RANK(F2,F:F)就可以了，RANK是排序函數(shù)，參數(shù)的意義是按照F列的數(shù)據(jù)排序 3、怎樣在不分班的情況下，在不改變年級(jí)名次的情況下，排出班級(jí)名次？如上圖 方法：在H2單元格個(gè)輸入 =SUMPRODUCT(($E$2:$E$1000=E2)*(F$2:$F$1000>F2))+1 4、怎樣統(tǒng)計(jì)各個(gè)班在每個(gè)分?jǐn)?shù)段的人數(shù)？效果如表三、表四 方法：在B2中輸入 =SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000>=600)) 在B3中輸入 =SUMPRODUCT((文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000<600)*(文科!F$2:文科!F$1000>=590)) 直接往后面復(fù)制，改變紅色數(shù)字就可統(tǒng)計(jì)出各個(gè)分?jǐn)?shù)段的人數(shù) 理科統(tǒng)計(jì)表不用再逐個(gè)輸入了，直接復(fù)制文科統(tǒng)計(jì)表，然后“編輯——替換”就行了 5、怎樣在統(tǒng)計(jì)表中出現(xiàn)文科（理科）成績(jī)表中的最高分，及對(duì)應(yīng)的名字和班級(jí)？ 統(tǒng)計(jì)文科(理科)成績(jī)表中的最高分 A2=MAX(INDIRECT("文科!"&CHAR(64+ROW(C5))&2):INDIRECT("文科!"&CHAR(64+ROW(C5))&1000)) CHAR（ ）返回單元格中的字符 ROW( ) 返回單元格所在的行 “64+row（）”的含義是，65對(duì)應(yīng)A，66對(duì)應(yīng)B，……比如上面表一中總分在D列，所以就是64+ROW(C5) 調(diào)出姓名 =LOOKUP(2,1/((文科!D$2:D$1000=MAX((文科!D$2:D$1000=A2)*文科!D$2:D$1000))),文科!B$2:B$1000) A2是剛返回的最高分 D列是分?jǐn)?shù) B列是姓名 調(diào)出班級(jí) =LOOKUP(2,1/((文科!D$2:D$1000=MAX((文科!D$2:D$1000=A2)*文科!D$2:D$1000))),文科!C$2:C$1000) C列是班級(jí) 6、怎樣統(tǒng)計(jì)各個(gè)班在年級(jí)前多少名的人數(shù)？ 方法：在B19中輸入 =SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=50)) 在C19中輸入 =SUMPRODUCT((文科!E$2:文科!E$1000=A19)*(文科!G$2:文科!G$1000<=100)) 后面的就不用說了吧 7、單元格里面都好多零好難看又影響看數(shù)據(jù)，怎樣隱藏這些0？ 方法：“工具 ——選項(xiàng)——視圖——零值前面的鉤去掉就行了”效果如下： 好了，一張適用的成績(jī)分析表就做好了，以后拿了直接用就OK了，凌晨一點(diǎn)了，睡覺去……