[Excel]VBAで組み合わせグラフを作る - FB post for mutter

[Excel]VBAで組み合わせグラフを作る

| コメント(0) | トラックバック(0)

ExcelのVBAによるグラフ作成です。少しずつパターンが増えて来ました。今回は、『集合グラフ』に挑戦してみます。

基本的な仕様は、

[Excel]VBAでインライングラフを作成する
[Excel]VBAでインライングラフを作成する2

と同じです。

Excelでグラフを作る場合、複数のデータ列を基にグラフを重ねて作成する場合も、基本的に同じ形式のグラフが作られます。
横棒グラフを指定すれば、全ての系列とも横棒グラフになる。と言う仕様です。

そんな中、今回の『組み合わせグラフ』は、データの系列毎に別のグラフを選択し、1枚のグラフの中に描いてしまう物です。
一般的には、縦棒グラフと折れ線グラフを組み合わせます。折れ線グラフの代わりに、面グラフを選択する事もある様ですが、読みやすさと言う意味では、選択から外れる場合がある様です。

このパターンで解決しなければならない事は、

  • データソースをどの様に与えれば良いのか。
  • 複数タイプのグラフを重ねる場合も、重ね合わせグラフの要領で作れるのか。
  • 系列毎のグラフの種類をどの様に指定するのか。
  • 系列軸が複数ある場合(第1系列軸, 第2系列)がある場合、どの様にデータを与えるのか。

あたりでしょうか。

ついでに、第2系列のグラフに対して、近似値曲線を重ねる事にもチャレンジして見ようと思います。
複数のグラフがある場合の、2場面以降のグラフへ近似値曲線作成の指示を出す方法も同時に学ぼうと思います。

例の様に、まずは『マクロの記録』を取り、出来上がり見本と成るようなグラフを書いてみて、そのソースをお手本にしながら、グラフの位置やサイズも制御するソースを書いて行きます。

完成版

まずは、Excelの画面をご覧ください。

完成版のExcel画面

例のごとくデータは適当に作った物です。意味はありません。
年度に対して、人数(第1系列)と金額(第2系列)があり、第1系列は棒グラフ, 第2系列は折れ線グラフにしてあります。
さらに、金額に対して近似曲線を重ねる様にしてあります。

グラフ部分

完成版のソース

完全版のソースを示し、続いて細かく追って行きます。
いろいろな処理を入れてあるので、少し長いですが、コメントを参考にして頂ければ分かり易いと思います。

Sub NewGraph3()
    ' 変数定義
    Dim i As Integer, table_br As Integer

    ' グラフの削除
    For i = ActiveSheet.ChartObjects.Count To 1 Step -1
         ActiveSheet.ChartObjects(i).Delete
    Next i

    ' 表のサイズを調べる
    Range("A2").CurrentRegion.Select
    table_br = Selection(Selection.Count).Row

    ' グラフエリア確保
    ActiveSheet.Shapes.AddChart.Select

    ' グラフは、G2~M17 の範囲に入れる
    ActiveChart.Parent.Top = ActiveSheet.Range("Sheet1!G2:M17").Top
    ActiveChart.Parent.Left = ActiveSheet.Range("Sheet1!G2:M17").Left
    ActiveChart.Parent.Height = ActiveSheet.Range("Sheet1!G2:M17").Height
    ActiveChart.Parent.Width = ActiveSheet.Range("Sheet1!G2:M17").Width

    ' データソース
    ActiveChart.SetSourceData _
      Source:=Range(Cells(1, 1), Cells(table_br, 3)), _
      PlotBy:=xlColumns

    ' グラフ1(人数) を集合縦棒グラフとして作成
    ' 人口は第2軸へメモリを用意する
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 2

    ' グラフ2(金額) を折れ線グラフとして
    ' 金額は第1軸へメモリを用意する
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1

    ' グラフ2に対して、近似値曲線を設定
    ActiveChart.FullSeriesCollection(2).Select
    ActiveChart.FullSeriesCollection(2).Trendlines.Add

    ' グラフ2の近似値曲線1を選択
    ActiveChart.FullSeriesCollection(2).Trendlines(1).Select

    ' 近似曲線の種類を、2次の多項式近似に設定
    Selection.Type = xlPolynomial
    Selection.Order = 2

    ' 先の、色(.ForeColor.RGB)
    '       透明度(.Transparency)
    '       形状(.DashStyle)
    '       太さ(.Weight)
    ' .Visible = msoTrue も無くても動く模様。
    Selection.Format.Line.Visible = msoTrue
    Selection.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    Selection.Format.Line.Transparency = 0
    Selection.Format.Line.DashStyle = msoLineSysDot
    Selection.Format.Line.Weight = 1.5

    ' 凡例をグラフの下移動
    ActiveChart.Legend.Select
    Selection.Position = xlBottom

    ' カーソルをA1へ移動
    Range("A1").Select

End Sub

変数定義~表のサイズを調べる

特に、触れる様なところもありません。この辺りの初期処理はスニペット化しても良い位です。

' 変数定義
Dim i As Integer, table_br As Integer

' グラフの削除
For i = ActiveSheet.ChartObjects.Count To 1 Step -1
     ActiveSheet.ChartObjects(i).Delete
Next i

' 表のサイズを調べる
Range("A2").CurrentRegion.Select
table_br = Selection(Selection.Count).Row

グラフエリア確保

論理的な、グラフエリアを確保します。これも、いつものパターンです。

' グラフエリア確保
ActiveSheet.Shapes.AddChart.Select

グラフの場所と大きさを指定

ここもパターン化されている処理です。
セルの範囲に配置するので、列, 行の幅を変更する事で大きさは柔軟に調整できます。

' グラフは、G2~M17 の範囲に入れる
ActiveChart.Parent.Top = ActiveSheet.Range("Sheet1!G2:M17").Top
ActiveChart.Parent.Left = ActiveSheet.Range("Sheet1!G2:M17").Left
ActiveChart.Parent.Height = ActiveSheet.Range("Sheet1!G2:M17").Height
ActiveChart.Parent.Width = ActiveSheet.Range("Sheet1!G2:M17").Width

グラフの場所と大きさを指定

データソースの場所を指定します。今回は、A列が横軸, B列が縦第1軸, B列が縦第2列です。データの長さは、テーブルのサイズから取得しているので、Cells()を使用してしますが、固定で良い場合にはいきなりRange()から書き始めても問題ありません。
PlotBy:=xlColumnsオプションは無くても大丈夫でしたが、明示的に付けました。

' データソース
ActiveChart.SetSourceData _
  Source:=Range(Cells(1, 1), Cells(table_br, 3)), _
  PlotBy:=xlColumns

第1軸の作成と、系列メモリも設定

第1軸は、縦棒グラフにしました。設定内容的には集合縦棒グラフを意味するxlColumnClusteredを指定するらしいです。
1番目のグラフを作るので、FullSeriesCollection(1)に対して指示を与えます。 今回は、学習の意味も込め、第1系列の縦軸メモリは第2軸側(右側)に設置する様にしました。
そのため、パラメータの数字があって居らず、気持ちが悪いと思いますが、ミスではなく意図した物です。

' グラフ1(人数) を集合縦棒グラフとして作成
' 人口は第2軸へメモリを用意する
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).AxisGroup = 2

組み合わせグラフを作成する場合、グラフ毎にデータソースの位置を明記する必要は無い様です。
と言うより、明記しようとするとエラーになりました。
単純に、データソースの読み込み順に第1グラフ, 第2グラフ, 第3グラフ… と、データが割当られる様です。

ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered

まず、グラフ1を用意します。集合縦棒グラフを設定しています。

ActiveChart.FullSeriesCollection(1).AxisGroup = 2

Axisから始まるキーワードは、縦軸のメモリ部分の指定を行う時に出てくるキーワードです。
AxisGroup = 2は第2軸(右側の軸)を選択するよ。と言う意味です。

第2軸の作成と、系列メモリも設定

次に、第2軸の作成です。作り方は、第1軸と同じです。

' グラフ2(金額) を折れ線グラフとして
' 金額は第1軸へメモリを用意する
ActiveChart.FullSeriesCollection(2).ChartType = xlLine
ActiveChart.FullSeriesCollection(2).AxisGroup = 1

第2軸は、折れ線グラフです。
縦軸の左側に、軸メモリを配置する指定をしています。

第2軸へ、近似曲線を設定と諸設定

第2軸の折れ線グラフに対して、近似曲線を重ねてみました。
近似曲線の作り方は、[Excel]VBAで近似曲線グラフを重ねるで詳しいく解説してあります。併せてご参考ください。

' グラフ2に対して、近似値曲線を設定
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).Trendlines.Add

' グラフ2の近似値曲線1を選択
ActiveChart.FullSeriesCollection(2).Trendlines(1).Select

' 近似曲線の種類を、2次の多項式近似に設定
Selection.Type = xlPolynomial
Selection.Order = 2

' 先の、色(.ForeColor.RGB)
'       透明度(.Transparency)
'       形状(.DashStyle)
'       太さ(.Weight)
' .Visible = msoTrue も無くても動く模様。
Selection.Format.Line.Visible = msoTrue
Selection.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
Selection.Format.Line.Transparency = 0
Selection.Format.Line.DashStyle = msoLineSysDot
Selection.Format.Line.Weight = 1.5

今回のポイントは、グラフ2に対して近似曲線1を配置している。と言う部分です。ですので、

' グラフ2の近似値曲線1を選択
ActiveChart.FullSeriesCollection(2).Trendlines(1).Select

の様な設定になっています。

凡例の位置を変える

初期状態では、凡例がグラフの右側に表示されていました。そのままでも良いのですが、グラフ領域が狭くなってしまうので、グラフの下へ移動させました。

' 凡例をグラフの下移動
ActiveChart.Legend.Select
Selection.Position = xlBottom

カーソルを戻す

最後にカーソルをA1へ移動させて置きました。

' カーソルをA1へ移動
Range("A1").Select

コードを良く見ていると、重ね合わせグラフのコードに似ているな。と感じられて来ました。『組み合わせグラフ』も一種の重ね合わせグラフの様な物でもあるので、コードに類似点があるのは当然の事なのだろうと思います。
最大の違いは、データソースの与え方と、データソース指定の自由度が低い。と言う事です。この辺りは、他のグラフのコードとは全く違う、非常に異質な物でした。

実際にグラフが必要になる場面では、『組み合わせグラフ』は説得力のある表現が出来るように感じられます。しかし、Excel 2010ではそこまで高度なグラフ設定はできない。と言う事なのだと思います。
実際に資料などを作成する場合には、その事に注意して作業を進める必要がありそうです。

当然と言えば、当然ではありますが。Excelの画面上で可能な表現以上の表現を、VBAマクロで行う事はできない様です。
逆に言えば、Excelの画面上でできる事なら、全てVBAマクロ化可能なのだ。と言う事なのでしょう。少しでも、プログラミングに興味がある方であれば、自動化や簡略化のためにVBAマクロやユーザー定義ワークシート関数を積極的に使う選択もあるな。と感じています。

トラックバック(0)

トラックバックURL:

コメントする

サムネイル

[Excel]VBAで組み合わせグラフを作る - FB post for mutter
RSS 0.91
RSS 0.92
RSS 1.0
RSS 2.0
ATOM

twitter

Creative Commons License
このブログはクリエイティブ・コモンズでライセンスされています。
Powered by Movable Type 6.3.3

このブログ記事について

このページは、いまい やすゆきが2017年2月 2日 23:41に書いたブログ記事です。

ひとつ前のブログ記事は「[Excel]VBAで近似曲線グラフを重ねる」です。

次のブログ記事は「[Excel]VBAでレーダーチャートを作る」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。