Excel VBA 实用技巧 ( 轻轻的进阶编 ) – 如何增加图表

~ 上傳‧分享‧網賺 ~

~ 免費上傳空間‧請即申請 Freak Share ~

~ 進入後請點選左上角 注冊 ! ~

~ 声明事项 ~

如各位想把以下本文章转贴 , 请记得要注明出处.

本文章之内容为本人之经验所写, 绝无抄袭成份, 特此声明.

由于图片制作需时, 所以更新会比较慢.

存放图片的 SHARE A PIC SERVER 可能读取速度较慢, 请耐心等待.

图表这家伙你没可能不知道是什么吧 ~ 既然我们用 EXCEL 去记录那么多的数据 , 你就没可能不需要把数据制作成一个图表 , 先看一下下面的程序代码 :

==================

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("B2:N5"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "图表标题"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X轴"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y轴"
End With

==================

首先 , 我在工作页 SHEET 2 的储存格 B2 到 N5 加入了一些数据 , 数据的内容不打紧 , 最重要的是数据表格的数据输入方式是给图表用的那种 … 嗯 … 再说明清楚一点吧 , 储存格 C2 到 N2 输入的是月份 ( 1 到 12 ) , 储存格 B3 到 B5 输入的是 “ 中文 ” “ 英文 ” 和 “ 数学 ” , 然后在储存格 C3 到 N5 胡乱的输入一堆数字 , 材料就是这些了 .

如果你懂得使用图表 , 当你按下那个增加图表的按钮时 , EXCEL 会自动把储存格 B2 到 N5 找出来作为图表的数据来源 , 而上面的程序代码也是一样 , 不过只是我们可以随便的修改 , 当然啰 ~ 能不能顺利执行就另作别论了 ~ 哈哈 ~

解释一下程序代码 ~ 第一句的 Charts.Add 不用多说就是增加图表了 , 不过 , 当程序代码在执行这一句的时候 , EXCEL 是会自动增加一个页面来存放这个新增的图表的 . ActiveChart.ChartType = xlColumnClustered 这句的意思就是图表的类型 , 有多少个呢 !? 嗯 … 嗯 … 给你看看吧 ~

ActiveChart.ChartType = xl3DArea
ActiveChart.ChartType = xl3DAreaStacked
ActiveChart.ChartType = xl3DAreaStacked100
ActiveChart.ChartType = xl3DBarClustered
ActiveChart.ChartType = xl3DBarStacked
ActiveChart.ChartType = xl3DBarStacked100
ActiveChart.ChartType = xl3DColumn
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.ChartType = xl3DColumnStacked
ActiveChart.ChartType = xl3DColumnStacked100
ActiveChart.ChartType = xl3DLine
ActiveChart.ChartType = xl3DPie
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.ChartType = xlArea
ActiveChart.ChartType = xlAreaStacked
ActiveChart.ChartType = xlAreaStacked100
ActiveChart.ChartType = xlBarClustered
ActiveChart.ChartType = xlBarOfPie
ActiveChart.ChartType = xlBarStacked
ActiveChart.ChartType = xlBarStacked100
ActiveChart.ChartType = xlBubble
ActiveChart.ChartType = xlBubble3DEffect
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartType = xlColumnStacked
ActiveChart.ChartType = xlColumnStacked100
ActiveChart.ChartType = xlConeBarClustered
ActiveChart.ChartType = xlConeBarStacked
ActiveChart.ChartType = xlConeBarStacked100
ActiveChart.ChartType = xlConeCol
ActiveChart.ChartType = xlConeColClustered
ActiveChart.ChartType = xlConeColStacked
ActiveChart.ChartType = xlConeColStacked100
ActiveChart.ChartType = xlCylinderBarClustered
ActiveChart.ChartType = xlCylinderBarStacked
ActiveChart.ChartType = xlCylinderBarStacked100
ActiveChart.ChartType = xlCylinderCol
ActiveChart.ChartType = xlCylinderColClustered
ActiveChart.ChartType = xlCylinderColStacked
ActiveChart.ChartType = xlCylinderColStacked100
ActiveChart.ChartType = xlDoughnut
ActiveChart.ChartType = xlDoughnutExploded
ActiveChart.ChartType = xlLine
ActiveChart.ChartType = xlLineMarkers
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.ChartType = xlLineMarkersStacked100
ActiveChart.ChartType = xlLineStacked
ActiveChart.ChartType = xlLineStacked100
ActiveChart.ChartType = xlPie
ActiveChart.ChartType = xlPieExploded
ActiveChart.ChartType = xlPieOfPie
ActiveChart.ChartType = xlPyramidBarClustered
ActiveChart.ChartType = xlPyramidBarStacked
ActiveChart.ChartType = xlPyramidBarStacked100
ActiveChart.ChartType = xlPyramidCol
ActiveChart.ChartType = xlPyramidColClustered
ActiveChart.ChartType = xlPyramidColStacked
ActiveChart.ChartType = xlPyramidColStacked100
ActiveChart.ChartType = xlRadar
ActiveChart.ChartType = xlRadarFilled
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.ChartType = xlStockHLC
ActiveChart.ChartType = xlStockOHLC
ActiveChart.ChartType = xlStockVHLC
ActiveChart.ChartType = xlStockVOHLC
ActiveChart.ChartType = xlSurface
ActiveChart.ChartType = xlSurfaceTopView
ActiveChart.ChartType = xlSurfaceTopViewWireframe
ActiveChart.ChartType = xlSurfaceWireframe
ActiveChart.ChartType = xlXYScatter
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

哈哈 ~ 怕了没 !? 这里面会有多少种是我们常用的呢 ? 我都忘了 ~ 哈哈 ~ 会是什么东西嘛 ~ 那就留给你慢慢玩啰 ~ 我这个懒人要去睡一下 ~ 哈哈 ~

好了 ~ 继续看程序代码吧 ~ ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("B2:N5"), 这句的意思就是图表数据来源的位置 , 而

PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "图表标题"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X轴"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y轴"
End With

这一堆就是图表的摆放位置 , 标题的设定 , X 轴和 Y 轴的名称设定 . 就是这样了 ~