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 軸的名稱設定 . 就是這樣了 ~