动态复合图表

楼主芐雨	一个动态的复合图表，通过复选框来控制要求： 1.不同的系列设置不同的颜色 2.不同的系列设置不同的图表类型 3.占比，转化率等系列在次坐标轴，如全部都是占比的系列设置在主次标轴（详情设置可见下图）思路根据要求列出不同系列的设置----遍历CheckBoxes找出勾选的系列----根据系列的设置做出图表代码如下： Sub 动态图表_芐雨() Dim arr, brr, Dic As Object Dim n As Integer, j As Integer, k As Integer, x As Integer Application.ScreenUpdating = False Set Dic = CreateObject("scripting.dictionary") k = 1 With ActiveSheet arr = .Range("X3").CurrentRegion '把格式设置导入数组arr For n = 2 To UBound(arr) Dic(arr(n, 1)) = n '放出字典Dic,n为数组arr的位置 Next ReDim brr(1 To n - 1, 0) .Shapes("图表 17").Select For j = 1 To .CheckBoxes.Count '求出√选的项，记入数组brr If .CheckBoxes(j).Value = 1 Then x = x + 1: brr(x, 0) = .CheckBoxes(j).Text Next End With With ActiveChart For Z = .SeriesCollection.Count To 1 Step -1 .SeriesCollection(Z).Delete '删除所有系列 Next For i = 1 To x tt = Dic(brr(i, 0)) .SeriesCollection.NewSeries .SeriesCollection(i).Name = arr(tt, 1) '系列名 .SeriesCollection(i).Values = arr(tt, 2) '系列值 .SeriesCollection(i).XValues = arr(tt, 3) '水平轴值 If arr(tt, 4) = "折线" Then .SeriesCollection(i).ChartType = xlLine '图形类型: xlLine折线图 .SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(arr(tt, 6), arr(tt, 7), arr(tt, 8)) '设置线颜色 .SeriesCollection(i).Format.Line.Weight = arr(tt, 5) '设置线大小' Else .SeriesCollection(i).ChartType = xlColumnClustered '图形类型:xlColumnClustered 柱形 .SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(arr(tt, 6), arr(tt, 7), arr(tt, 8)) '设置填充颜色 End If If .SeriesCollection(i).Name = "PV" Then k = 2 If .SeriesCollection(i).Name = "UV" Then k = 2 If .SeriesCollection(i).Name = "进货数" Then k = 2 If .SeriesCollection(i).Name = "销售数" Then k = 2 If .SeriesCollection(i).Name = "进货货值" Then k = 2 If .SeriesCollection(i).Name = "销售金额" Then k = 2 If .SeriesCollection(i).Name = "购买人数" Then k = 2 If .SeriesCollection(i).Name = "平均客单价" Then k = 2 Next For Z = .SeriesCollection.Count To 1 Step -1 '根据k值设置为次坐标轴 If .SeriesCollection(Z).Name = "销量售" Then .SeriesCollection(Z).AxisGroup = k If .SeriesCollection(Z).Name = "销量占比" Then .SeriesCollection(Z).AxisGroup = k If .SeriesCollection(Z).Name = "销售额售" Then .SeriesCollection(Z).AxisGroup = k If .SeriesCollection(Z).Name = "平均转化率" Then .SeriesCollection(Z).AxisGroup = k If .SeriesCollection(Z).Name = "平均退货率" Then .SeriesCollection(Z).AxisGroup = k If k = 1 Then 'k=1时，放入主坐标轴 .Axes(xlValue).TickLabels.NumberFormatLocal = "0.00%" 'Y轴数字格式 Else .Axes(xlValue).TickLabels.NumberFormatLocal = "0" End If Next End With Application.ScreenUpdating = True End Sub 动态复合图表_芐雨.zip
2楼滴水穿石	这种动态图，如果不用VBA可以做出来吗？
3楼芐雨	可以做出接近的，但部分效果不能实现。（如：占比，转化率会自动设置为次坐标轴。）
4楼滴水穿石	好吧... 感觉VBA好难...
5楼老糊涂	学习图表VBA
6楼 13641096715	下载学习，感谢分享。
7楼 yeminqiang	果断收藏！留给后代学习。相信下一代更有智慧！
8楼 yeminqiang