深夜,没有睡意。因为,再次发现VBA的魅力。
给出一组数据,如下:
可以绘制多少种Excel图表呢?
答案是以下70多种(Excel 实测):
这效果绝对炫酷,熬夜也值了。
以上Excel图表类型见下表,
VBA代码共享给有缘人:
Public Sub MonthlyCalc()Application.ScreenUpdating = False'定义用于循环的整型变量Dim Itemp As IntegerDim ChartTypeArray() As VariantDim ChartCount As IntegerFor Itemp = 1 To 12'计算商品(甲)的销售额合计Cells(4, Itemp + 2) = Cells(2, Itemp + 2) * Cells(3, Itemp + 2)'计算商品(乙)的销售额合计Cells(7, Itemp + 2) = Cells(5, Itemp + 2) * Cells(6, Itemp + 2)'计算商品(丙)的销售额合计Cells(10, Itemp + 2) = Cells(8, Itemp + 2) * Cells(9, Itemp + 2)'计算三种商品总的销售额合计Cells(11, Itemp + 2) = Cells(4, Itemp + 2) + Cells(7, Itemp + 2) _+ Cells(10, Itemp + 2)Next Itemp'ChartTypeArray = Array(xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100)'ChartTypeArray = Array(63, 64, 65, 66, 67)'88, 89, 90, 91 excluded'ChartTypeArray = Array(-4169, -4151, -4120, -4102, -4101, -4100, -4098, 1, 4, 5, 15, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87)'ChartTypeArray = Array(92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112)ChartTypeArray = Array(-4169, -4151, -4120, -4102, -4101, -4100, -4098, 1, 4, 5, 15, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, _67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, _102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112)ChartCount = 1Do While (ChartCount <= (UBound(ChartTypeArray) + 1))'添加图表Charts.Add'定义图表类型ActiveChart.ChartType = ChartTypeArray(ChartCount - 1)'图表数据源ActiveChart.SetSourceData Source:=Sheets("chap5_2").Range( _"A1:N1,A4:N4,A7:N7,A10:N10"), PlotBy:=xlRows'设置图表添加的位置ActiveChart.Location Where:=xlLocationAsObject, Name:="charts2"With ActiveChart'使图表带有“标题”.HasTitle = True'设置图表“标题”.ChartTitle.Characters.Text = ChartTypeArray(ChartCount - 1) & "——月销售情况对比"'使图表带有X坐标标题'.Axes(xlCategory, xlPrimary).HasTitle = True'设置图表X坐标标题'.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "月份"'使图表带有Y坐标标题' .Axes(xlValue, xlPrimary).HasTitle = True'使图表带有Y坐标标题' .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "合计(元)"End WithWith ActiveChart.Parent' .Left = 10 + 368 * (ChartCount - 1) '此处用以修改图表在工作表中的位置' .Top = 200.Left = 10 + Int(ChartCount / 15) * 356 '此处用以修改图表在工作表中的位置If (ChartCount Mod 15 <> 0) Then.Top = 222 * (ChartCount Mod 15)Else.Top = 222End IfEnd WithChartCount = ChartCount + 1LoopApplication.ScreenUpdating = TrueEnd Sub
如果觉得《Excel应用-使用VBA自动绘制所有适用类型的Excel图表(代码及效果图)》对你有帮助,请点赞、收藏,并留下你的观点哦!