[Excel]VBAで近似曲線グラフを重ねる - FB post for mutter

[Excel]VBAで近似曲線グラフを重ねる

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

ExcelのVBAでグラフを作成する作業を進めています。昨日、近似曲線を重ねる例が出来たので、紹介します。

基本的な仕様は、

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

と同じです。今後、紹介するかも知れない事例も、全てこの仕様に併せて作成する予定です。
今後、グラフ専用ページを追加するタイプのグラフ作成を行う時にも、大いに参考になるはずです。それも考慮し、基本的な仕様は変更しない様にします。

今回のパターンで解決しなければならない課題は、

  • 近似曲線の元になるグラフをどの様に指定するのか。
  • 近似曲線をどの様に作るのか。

の2点だと思います。
今回は、少し色気をだし、近似曲線の体裁(見た目)を変更するコードも追加して見ました。

『マクロの記録』を使用して、Excelがどの様に近似曲線を定義しているのか確認し、それを基に基本仕様に併せてグラフを作成する手順で進めます。

完成版

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

完成版のExcel画面

A, B列のデータを基にグラフを作成する事にしました。データ自体は適当に打ち込んだ物です。なんの意図もありません。
ちなみに、年の列は、

=DATE(YEAR(A2)+1,1,1)

の様にして、縦方向へコピーを取ってあり、表示形式で、

yyyy"年"

と定義して置きました。金額の方も同様に『万円』を付けて表示するようしました。

#,##0"万円"

今回は、表の長さを調べて、データソースの範囲を決定するコードにしました。画像では13行までデータを打ち込んでありますが、これより少なくても、多くてもマクロの動作には支障はありません。

グラフ部分を拡大した画像です。『金額』に対する多項式(近似曲線)が描かれています。

完成版のExcel画面

少し見づらいかも知れませんが、赤い破線で近似曲線を描いて見ました。

完成版のソース

完全版のソースを示し、続いて細かく追って行きます。

Sub NewGraph2()
    ' 変数定義
    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

    ' 折れ線グラフをつくる
    ActiveChart.ChartType = xlLine

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

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

    ' 横軸を設定
    ActiveChart.FullSeriesCollection(1).XValues = _
      Range(Cells(2, 1), Cells(table_br, 1))

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

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

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

    ' (意味不明。無くても動く模様。)
    Application.CommandBars("Format Object").Visible = False

    ' 先の、色(.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

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

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

特に、解説するほとの内容でもありません。
A2 を基準にデータソースの長さを調べる様にしてあります。

' 変数定義
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

グラフの種類を選択

今回は、折れ線グラフを作成します。

' 折れ線グラフをつくる
ActiveChart.ChartType = xlLine

グラフの配置場所とサイズの指定

グラフを配置するエリアを指定します。
今回は、F2~L15に配置しました。

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

データソースの指定

データソースの場所を指定します。
データソースの長さを自動取得するコードにしたため、CR形式の値をCells()に渡し、それをRange()で領域指定する様にしてあります。
長くなったので、改行してありますが、一行に書いてしまっても問題はありません。
PlotBy:=xlColumns指定はなくても問題ない様ですが、データの読み込み順序を意識付けるために明示して置きました。

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

横軸の設定

横軸が、1, 2, 3… と成ってしまったので、B列を明示して置きました。

' 横軸を設定
ActiveChart.FullSeriesCollection(1).XValues = _
  Range(Cells(2, 1), Cells(table_br, 1))

近似曲線の対象を指定する

ここからが、今回のポイントです。
まず、近似曲線の基となるグラフはどれなのか。を設定します。この動作は、Excelの画面で近似曲線を追加する時と全く同じです。

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

今回は、グラフが1つしか表示されていないので、話しが単純です。
FullSeriesCollection()に 1 を渡して、1番目のグラフに対してだよ。
と指定します。

ActiveChart.FullSeriesCollection(1).Select

そのグラフに対して、近似曲線を追加するよ。 と言う指定です。

ActiveChart.FullSeriesCollection(1).Trendlines.Add

以降で説明がて出てきますが、この近似曲線の番号も 1 です。
グラフ1 の 近似曲線1 が追加された。と言う意味です。

追加した近似曲線を選択する

追加した近似曲線に対して、細かな設定を行うため、追加した近似曲線を選択します。
『近似曲線の対象を指定する』でも説明しましたが、今回追加した近似曲線は、グラフ1 の 近似曲線1 なので、

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

この様に選択する事になります。

近似曲線の形式を指定する

次に、追加する近似曲線の種類を指定します。今回は、2次の多項式を選びました。
もっと、高次を選択しても、別の曲線を選択しても良いと思います。

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

うぅ~ん

コードのコメントにも書いた様に、この行の意味は良く分りませんでした。

' (意味不明。無くても動く模様。)
Application.CommandBars("Format Object").Visible = False

マクロの記録を取ると、この行が書かれていました。また、近似曲線を解説している他のページでも、やはりこのコードが入っていました。

もしかすると、僕と同様に『意味は良く分からなかったけど、マクロ記録に入っていたから。』と言う理由なのかも知れませんが、それも推察でしかありませんので。
外して実行したところ、特に問題なく動作してしまったので、指定しなくても問題ない様な気がします。でも、『マクロ記録に入っていたから。』と言う不純な理由で残してあります。

このコードの意味をご存知の方居られれば、教えて下さい。

近似曲線の体裁を変更する

近似曲線の色とか太さとかを変更して見ました。
特に説明は必要ないと思いますが、

' 先の、色(.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

コメントにもある様に、この行も意味が良く分からない、不純な理由により残っているものです。

Selection.Format.Line.Visible = msoTrue

カーソルを戻す

最後に、カーソルをA1へ戻して置きます。
このコード、実は意味があって。この行がないとグラフ1が選択されたままに成ってしまいます。selectしてある部分を解除すれば良いのだとは思うのですが、実はコードがわからなかった。と言う理由で、カーソルを移動してお茶を濁しました。

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

実際にコードを書いて見ると、意外に簡単なコードになりました。基本的には、Excelでの動作通りにコードを書けば良いだけの事ですね。 一部、オブジェクトの指定方法に疑問がありました。今回は『マクロの記録』で作れたコードがほぼそのまま使えましたが、別の事例ではエラーが続発して『良く分からない。』状態になる物もありました。
そう言う意味では、近似曲線は分かりやすくて良かったです。

試行錯誤を繰り返しているので、徐々に『感』を掴んできた。とも言えるのですが。

トラックバック(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日 11:55に書いたブログ記事です。

ひとつ前のブログ記事は「[Excel]VBAで重ね合わせインライングラフを作成する」です。

次のブログ記事は「[Excel]VBAで組み合わせグラフを作る」です。

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