自己動手制作excel成績分析表(一勞永逸)轉(zhuǎn)

1、原文地址：自己動手制作excel成績分析表（一勞永逸）作者：李國磊效果圖如下： 表一：文科成績 表二：理科成績表三：文科成績統(tǒng)計分析表四：理科成績統(tǒng)計分析 同一個文件中有四個工作表：“文科”“理科”“文科統(tǒng)計”“理科統(tǒng)計”其中成績表中包含有：總分，年級名次（相同的分?jǐn)?shù)相同的名次），班級名次（在不用分班的情況下統(tǒng)計班級名次），單科年級名次，與上一次考試的對比（是進(jìn)不了還是退步了）統(tǒng)計表中包含有：每個班各個分?jǐn)?shù)段的人數(shù)，年級各個分?jǐn)?shù)段的人數(shù)，年級累計人數(shù)，每個班年級前多少名的人數(shù)對于我們老師來說，這樣的一張表足可以滿足我們分析成績的需要，如果每次考試都統(tǒng)計一次，工作量

2、就會增加很多，并且是無效的重復(fù)勞動，為此鄙人用了四個小時的時間制作了這樣的一張模板表，每次我們只需要把單科的成績復(fù)制進(jìn)來，就會自動求出總分，排出單科的名次，年級名次，班級名次以及與上一次的對比情況，還有各個分?jǐn)?shù)段的人數(shù)，可以為我們省去很多不必要的麻煩，先將制作方法說明如下，有興趣的朋友我們一起探討，設(shè)計出更好的模板。 1、求總分     這個大家都會，就不過多解釋了，直接用SUM函數(shù)就行了2、怎樣排出年級名次，要求同一個分?jǐn)?shù)有相同的名次，如效果圖  方法：在G2單元格中輸入 =RANK(F2,F:F)就可以了，RANK是排序

3、函數(shù)，參數(shù)的意義是按照F列的數(shù)據(jù)排序3、怎樣在不分班的情況下，在不改變年級名次的情況下，排出班級名次？如上圖方法：在H2單元格個輸入 =SUMPRODUCT($E$2:$E$1000=E2)*(F$2:$F$1000>F2)+14、怎樣統(tǒng)計各個班在每個分?jǐn)?shù)段的人數(shù)？效果如表三、表四方法：在B2中輸入 =SUMPRODUCT(文科!E$2:文科!E$1000=A2)*(文科!F$2:文科!F$1000>=600)          在B3中輸入 =SUMPRODUCT(文科!E$2:文科!E$1

4、000=A2)*(文科!F$2:文科!F$1000<<font color="#FF0000">600)*(文科!F$2:文科!F$1000>=590)直接往后面復(fù)制，改變紅色數(shù)字就可統(tǒng)計出各個分?jǐn)?shù)段的人數(shù)           理科統(tǒng)計表不用再逐個輸入了，直接復(fù)制文科統(tǒng)計表，然后“編輯替換”就行了        5、怎樣在統(tǒng)計表中出現(xiàn)文科（理科）成績表中的最高分，及對應(yīng)的名

5、字和班級？統(tǒng)計文科(理科)成績表中的最高分A2=MAX(INDIRECT("文科!"&CHAR(64+ROW(C5)&2):INDIRECT("文科!"&CHAR(64+ROW(C5)&1000) CHAR（  ）返回單元格中的字符ROW(     )   返回單元格所在的行“64+row（）”的含義是，65對應(yīng)A，66對應(yīng)B，比如上面表一中總分在D列，所以就是64+ROW(C5)調(diào)出姓名=LOOKUP(2,1/(文科!D$2:D$1000=MAX(文科!D$2:D$100

6、0=A2)*文科!D$2:D$1000),文科!B$2:B$1000)A2是剛返回的最高分D列是分?jǐn)?shù) B列是姓名 調(diào)出班級=LOOKUP(2,1/(文科!D$2:D$1000=MAX(文科!D$2:D$1000=A2)*文科!D$2:D$1000),文科!C$2:C$1000)C列是班級  6、怎樣統(tǒng)計各個班在年級前多少名的人數(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、單元格里面都好多零好難看又影響看