'主程序
Sub main()
Dim i
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
With Sheets(i)
.Name = sheetRename(.Name)
.Rows(2).NumberFormat = "0.00%"
.Rows(2).Value = .Rows(2).Value
End With
Call deleteChart(i)
Call CreateChart(i)
Next i
End Sub
'删除图表
Sub deleteChart(sheetIndex)
With Sheets(sheetIndex).ChartObjects
If .Count Then .Delete
End With
End Sub
'创建并设置图表
Sub CreateChart(sheetIndex)
Dim sh As Worksheet
Dim co As ChartObject
Dim sc As Series
Dim j As Integer
Dim addr As String
Set sh = Sheets(sheetIndex)
addr = Application.Intersect(sh.UsedRange, sh.[b2].Resize(1, 15)).Address
addr = "=" & sh.Name & "!" & addr
Set co = sh.ChartObjects.Add(Left:=20, Top:=80, Width:=800, Height:=200)
With co.Chart
.ChartType = xlColumnClustered '图表类型是柱形图
.ApplyLayout (5) '功能区中显示的第5个版式
Set sc = .SeriesCollection.NewSeries '新增的数据系列
sc.Values = addr '设置该系列的值
'数据标签
sc.ApplyDataLabels Type:=xlDataLabelsShowLabel
For j = 1 To sc.Points.Count
sc.Points(j).DataLabel.Text = sh.Cells(3, j + 1) & sh.Cells(1, j + 1)
Next j
.SetElement (msoElementChartTitleNone) '取消图表标题
.Axes(xlValue).AxisTitle.Delete '删除纵坐标标题
End With
Set co = Nothing: Set sh = Nothing: Set sc = Nothing
End Sub
'将工作表名中的非法字符,替换成下划线
Function sheetRename(str) As String
str = VBA.Replace(str, ",", "_")
str = VBA.Replace(str, "(", "_")
str = VBA.Replace(str, ")", "_")
sheetRename = str
End Function