Excel vba學(xué)生各班成績(jī)分析統(tǒng)計(jì)及對(duì)應(yīng)模板

Excelvba學(xué)生各班成績(jī)分析統(tǒng)計(jì)及對(duì)應(yīng)模板(修正版)訪問(wèn)—/file/id_84798498557394945.htm可下載相應(yīng)模板及vba代碼。各班成績(jī)分析統(tǒng)計(jì).xlavba代碼(修正版)適合的學(xué)校計(jì)算方式為：在單科成績(jī)按總分降序排列取前“N”(平均基數(shù))名的基礎(chǔ)上求單科平均及對(duì)全年級(jí)求年級(jí)平均，班名及年名在執(zhí)行一下“清()”后可自動(dòng)顯示出來(lái)，(注意：請(qǐng)修改一下暫坐生標(biāo)志，防止先刪了數(shù)據(jù))模塊2代碼：Sub清()清除“姓名”字段中含“N/A”的無(wú)效數(shù)據(jù)。清除“姓名”字段中含“a”的暫坐生。Dimi,jAsIntegeri=Range("A65536").End(xlUp).RowForj=1ToiIfIsError(Cells(j,3))ThenRows(j).ClearContentsElseIfInStr(Cells(j,3),"a")>0Then'(”a”為暫坐生標(biāo)志，模板中“A”為正確暫坐生標(biāo)志，可自改)Rows(j).ClearContentsEndIfNextEndSubSub統(tǒng)()'ActiveCell.Formula="=sum(a1:f1)/"&i'ActiveCell.FormulaR1C1="=SUM(R[-6]C:R[-1]C)/"&i&""'Application.Run"Book2.xls!Macro1"'清除不必要數(shù)據(jù)MsgBox"請(qǐng)先設(shè)好暫座標(biāo)志"Application.Run"清"'初始化班級(jí)個(gè)數(shù)平均基數(shù)Dimi,m,j,n,o,jm,zh,li,newRangeAsInteger'Dimi,m,j,n,o,newRangeAsIntegerDimtellMeAsStringOnErrorGoToVeryEndtellMe="請(qǐng)輸入一個(gè)平均基數(shù)"tellMe2="請(qǐng)輸入一個(gè)正確的最大班級(jí)個(gè)數(shù)"i=Application.InputBox(prompt:=tellMe,Title:="平均基數(shù)",Default:=50,Type:=1)m=Application.InputBox(prompt:=tellMe2,Title:="班級(jí)個(gè)數(shù)",Default:=8,Type:=1)Ifi=FalseThenExitSubIfm=FalseThenExitSubVeryEnd:'求各班各科平均分'科目Range("D2").Range("A1:I1").SelectSelection.CopyRange("Q2").SelectSelection.PasteSpecialPaste:=xlPasteValuesAndNumberFormats,Operation:=_xlNone,SkipBlanks:=False,Transpose:=FalseRange("z2")="政史"'取得政史列號(hào)Forjm=17To30IfCells(2,jm)="政治"Thenzh=jmElseIfCells(2,jm)="歷史"Thenli=jmEndIfNext'班級(jí)j=1'執(zhí)行的班級(jí)個(gè)數(shù)n=83'執(zhí)行的求平均行號(hào)定位o=3'執(zhí)行聚集行號(hào)定位Whilej<=mRange("d"&n&"").FormulaArray="=AVERAGE(LARGE(R[-80]C:R[-1]C,ROW(R1:R"&i&")))"Range("d"&n&"").SelectSelection.AutoFillDestination:=ActiveCell.Range("A1:I1"),Type:=_xlFillDefaultActiveCell.Range("A1:I1").SelectSelection.CopyRange("q"&o&"").SelectSelection.PasteSpecialPaste:=xlPasteValuesAndNumberFormats,Operation:=_xlNone,SkipBlanks:=False,Transpose:=FalseRange("z"&o&"").SelectOnErrorResumeNext'ActiveCell.Formula=Cells(o,zh)+Cells(o,li)'ActiveCell.FormulaR1C1="=Application.WorksheetFunction.Sum((Chr(Asc("a")+zh-1)&o,Chr(Asc("a")+li-1)&o)"'Chr(Asc("a")+li-1)&2&":"ActiveCell.FormulaR1C1=Application.WorksheetFunction.Sum(Cells(o,zh),Cells(o,li))'ActiveCell.FormulaR1C1="=SUM(RC[-3],RC[-4])"n=n+81j=j+1o=o+1Wend'求年平均分Range("q"&o&"").SelectActiveCell.FormulaR1C1="=SUM(R[-"&m&"]C:R[-1]C)/"&m&""Selection.AutoFillDestination:=ActiveCell.Range("A1:J1"),Type:=_xlFillDefaultActiveCell.Offset(0,-1).Range("A1").SelectActiveCell.FormulaR1C1="年平"'設(shè)置格式為“2”位小數(shù)(紅色)Range("Q3:Z12").SelectSelection.NumberFormatLocal="[紅色]0.00_;[紅色]-0.00"'清空多余列'DimjmAsIntegerForjm=17To30IfCells(2,jm)="總分"ThenColumns(jm).ClearContentsElseIfCells(2,jm)="年名"ThenColumns(jm).ClearContentsElseIfCells(2,jm)="班名"ThenColumns(jm).ClearContentsEndIfNext'清空無(wú)效數(shù)據(jù)ForEachcInRange(Range("A1"),ActiveCell.SpecialCells(xlLastCell))IfIsError(c)Thenc.ClearContentsEndIfNextcEndSubThisbook代碼：PrivateSubWorkbook_Open()Workbook_AddinInstall'AddNewCommandBarEndSubPrivateSubWorkbook_AddinInstall()DimCBAsCommandBarControlDimiAsIntegeri=1ForEachCBInApplication.CommandBars(1).ControlsIfCB.Caption="成績(jī)處理"Then'菜單已加入，則刪除Application.CommandBars("WorksheetMenuBar").Controls("成績(jī)處理「.Visible=TrueApplication.CommandBars("WorksheetMenuBar").Controls("成績(jī)處理").DeleteEndIfNextDimobjCmdBrPpAsCommandBarPopupSetobjCmdBrPp=Application.CommandBars.ActiveMenuBar.Controls.Add(Type:=msoControlPopup,Temporary:=False)OnErrorResumeNextobjCmdBrPp.Caption="成績(jī)處理"SetobjCmdBrPp=NothingWithApplication.CommandBars("Worksheetmenubar").Controls("成績(jī)處理").Controls.Add(Type:=msoControlButton,Before:=1).Caption="清".Controls(“清").OnAction="清"EndWithWithApplication.CommandBars("Worksheetmenubar").Controls("成績(jī)處理").Controls.Add(Type:=msoControlButton,Before:=1).Caption="統(tǒng)".Controls(”統(tǒng)").OnAction="統(tǒng)"EndWith'建立工具欄DimxBarAsCommandBarDimxButton1AsCommandBarButtonDimxButton2AsCommandBarButtonOnErrorResumeNextApplication.CommandBars("CustomBar").DeleteSetxBar=Application.CommandBars.Add(Name:="成績(jī)處理",Position:=msoBarTop,MenuBar:=False,Temporary:=False)SetxButton1=xBar.Controls.Add(Type:=msoControlButton)SetxButton2=xBar.Controls.Add(Type:=msoControlButton)WithxButton1.Caption="清".Style=msoButtonCaption.OnAction="清"EndWithWithxButton2.Caption="統(tǒng)".Style=msoButtonCaption.OnAction="統(tǒng)"EndWithWithApplication.CommandBars("成績(jī)處理").Visible=TrueEndWithSetxBar=NothingSetxButton1=NothingSetxButton2=Nothing'固定工具欄DimintleftAsInteger,introwAsIntegerintleft=Application.CommandBars("formatting").Widthintrow=Application.CommandBars("formatting").RowIndexApplication.CommandBars("成績(jī)處理").Left=intleftApplication.CommandBars("成績(jī)處理").RowIndex=introwEndSubPrivateSubWorkbook_BeforeClose(CancelAsBoolean)'文件關(guān)閉，就刪除菜單DimCBAsCommandBarControlOnErrorResumeNextForEachCBInApplication.CommandBars(1).ControlsIfCB.Caption="成績(jī)處理"ThenApplicatio