Question

关于excel宏的问题

我要制作这样一个excel文件：
模拟产生本院级（至少10名同学）8门功课成绩表，具体要求：
（1）产生三个工作表
基础课成绩表（表一），技术基础课成绩表（表二），成绩汇总表（表三）
（2）表的内容为：
表一：姓名、性别、高数、英语、物理、政治
表二：姓名、性别、公共关系学、运筹学、C语言、微机系统及应用
表三：总分，总分排名
（3）表三由表一和表二经宏产生
2报表的格式
（1）字段名称用黑体，14号字，姓名、性别、分数数据用深黄色，不及格成绩用红色。
（2）表格按总分升序排列
（3）在表三种根据数据建立图表，图表类型为三维柱形图，按列产生；标题为“**班级成绩汇总表”
3创建宏，完成下列规定操作：
（1）根据表一和表二产生表三（各项成绩之和得到总分）
（2）根据总分排名次
我对这实在一窍不通，麻烦哪位高手帮帮忙，帮忙编写这个，不胜感激啊

Answer 1

Sub 成绩表()
'
' 成绩表 Macro
' 宏由 USER 录制，时间: 2008-12-31
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "姓名"
Range("B1").Select
ActiveCell.FormulaR1C1 = "性别"
Range("C1").Select
ActiveCell.FormulaR1C1 = "高数"
Range("D1").Select
ActiveCell.FormulaR1C1 = "英语"
Range("E1").Select
ActiveCell.FormulaR1C1 = "物理"
Range("F1").Select
ActiveCell.FormulaR1C1 = "政治"
Sheets("Sheet1").Select
Sheets.Add
ActiveCell.FormulaR1C1 = "姓名"
Range("B1").Select
ActiveCell.FormulaR1C1 = "性别"
Range("C1").Select
ActiveCell.FormulaR1C1 = "公共关系学"
Range("D1").Select
ActiveCell.FormulaR1C1 = "运筹学"
Range("E1").Select
ActiveCell.FormulaR1C1 = "c语言"
Range("F1").Select
ActiveCell.FormulaR1C1 = "微机及应用"
Range("F1").Select
ActiveCell.FormulaR1C1 = "微机系统及应用"
Columns("F:F").Select
Selection.ColumnWidth = 13.63
Columns("C:C").ColumnWidth = 10.25
ActiveWindow.SmallScroll Down:=0
Sheets("Sheet2").Select
Sheets.Add
ActiveCell.FormulaR1C1 = "总分"
Range("B1").Select
ActiveCell.FormulaR1C1 = "总分排名"
Sheets("Sheet1").Select
Range("A1:F1").Select
Application.WindowState = xlNormal
Windows("新建 Microsoft Excel 工作表 (2).xls").Activate
Sheets("Sheet3").Select
Windows("PERSONAL.XLS").Activate
With Selection.Font
.Name = "黑体"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "黑体"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("A:A").Select
Selection.Font.ColorIndex = 3
Selection.Font.ColorIndex = 3
Selection.Font.ColorIndex = 3
Selection.Font.ColorIndex = 3
Application.CommandBars("Font Color").Visible = True
Application.CommandBars("Font Color").Visible = False
With Selection.Font
.FontStyle = "常规"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 27
End With
Range("B:B,C:C,D:D,E:E,F:F").Select
Range("F1").Activate
With Selection.Font
.FontStyle = "常规"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 27
End With
Range("A1:F1").Select
Selection.Font.ColorIndex = 1
Range("A2").Select
Sheets("Sheet2").Select
Range("A:A,B:B,C:C,D:D,E:E,F1,F:F").Select
Range("F1").Activate
With Selection.Font
.Name = "黑体"
.FontStyle = "常规"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 27
End With
Range("A1:G1").Select
Selection.Font.ColorIndex = 1
Range("F2").Select
Columns("F:F").ColumnWidth = 16
Columns("C:C").ColumnWidth = 14.5
Columns("C:C").ColumnWidth = 13.5
Sheets("Sheet3").Select
Range("B:B,A:A").Select
Range("A1").Activate
With Selection.Font
.Name = "黑体"
.FontStyle = "常规"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 27
End With
Range("A1:B1").Select
Selection.Font.ColorIndex = 1
Columns("B:B").ColumnWidth = 10.5
Sheets("Sheet1").Select
Application.Left = 4.75
Application.Top = 247
Range("C2:F10").Select
ActiveWindow.SmallScroll Down:=-3
Range("F8").Select
ActiveWindow.SmallScroll Down:=-9
Range("C2:F10").Select
Application.WindowState = xlMinimized
Application.WindowState = xlMaximized
ActiveSheet.ChartObjects("图表 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("图表 3").IncrementLeft 4.5
ActiveSheet.Shapes("图表 3").IncrementTop 138.75
ActiveWindow.Visible = False
Windows("新建 Microsoft Excel 工作表.xls").Activate
Range("H8").Select
ActiveWindow.SmallScroll Down:=-21
Range("H4:H13").Select
Windows("新建 Microsoft Excel 工作表 (2).xls").Activate
Windows("PERSONAL.XLS").Activate
ActiveWindow.SmallScroll Down:=-15
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Windows("新建 Microsoft Excel 工作表 (2).xls").Activate
Application.WindowState = xlMinimized
Windows("PERSONAL.XLS").Activate
Windows("新建 Microsoft Excel 工作表.xls").Activate
Application.WindowState = xlMinimized
Windows("PERSONAL.XLS").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="60"
Selection.FormatConditions(1).Font.ColorIndex = 3
ActiveWindow.SmallScroll Down:=15
Sheets("Sheet3").Select
Range("A2:B10").Select
Charts.Add
ActiveChart.ChartType = xlCylinderColClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("A2:B10"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "**班级成绩汇总表**"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
Windows("PERSONAL.XLS").SmallScroll Down:=-9
ActiveWindow.Visible = False
Windows("PERSONAL.XLS").Activate
Range("A2:B10").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=-3
Range("A2:A10").Select
ActiveWindow.SmallScroll Down:=-12
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(Sheet3!RC[2]:RC[5])"
Selection.AutoFill Destination:=Range("A2:A10"), Type:=xlFillDefault
Range("A2:A10").Select
ActiveWindow.SmallScroll Down:=-12
Range("B2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("B2:B10"), Type:=xlFillSeries
Range("B2:B10").Select
ActiveWindow.SmallScroll Down:=-12
Range("A9").Select
ActiveWindow.SmallScroll Down:=0
Range("A10").Select
ActiveWindow.SmallScroll Down:=3
Selection.AutoFill Destination:=Range("A10:A11"), Type:=xlFillDefault
Range("A10:A11").Select
Range("B10").Select
Selection.AutoFill Destination:=Range("B10:B11"), Type:=xlFillSeries
Range("B10:B11").Select
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=-3
Range("C2:F10").Select
ActiveWindow.SmallScroll Down:=3
Selection.AutoFill Destination:=Range("C2:F11"), Type:=xlFillCopy
Range("C2:F11").Select
ActiveWindow.SmallScroll Down:=-12
Range("A2:B10").Select
Selection.AutoFill Destination:=Range("A2:B11"), Type:=xlFillCopy
Range("A2:B11").Select
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=6
Selection.AutoFill Destination:=Range("C2:F11"), Type:=xlFillDefault
Range("C2:F11").Select
ActiveWindow.SmallScroll Down:=-12
Range("A2:B10").Select
Selection.AutoFill Destination:=Range("A2:B11"), Type:=xlFillDefault
Range("A2:B11").Select
Sheets("Sheet2").Select
Range("B9").Select
ActiveWindow.SmallScroll Down:=-9
Range("A2:B10").Select
Selection.AutoFill Destination:=Range("A2:B11"), Type:=xlFillDefault
Range("A2:B11").Select
ActiveWindow.SmallScroll Down:=-12
Range("C2:F10").Select
ActiveWindow.SmallScroll Down:=3
Selection.AutoFill Destination:=Range("C2:F11"), Type:=xlFillDefault
Range("C2:F11").Select
Range("G11").Select
ActiveWindow.SmallScroll Down:=-12
Sheets("Sheet3").Select
ActiveWindow.SmallScroll Down:=-12
End Sub
将这个复制到宏编制器中，运行。
然后向表格里填数据就可以了。