[Excel]VBAでインライングラフを作成する - FB post for mutter

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

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

年が明け、だいぶ時間が経ってしまった。様々な細かい作業に追われ、なかなか先に進みませんでした。
このブログの更新もできずに居ました。書きたい小ネタは溜まってきているので、スケジュールを調整しつつ。と思っています。

Excelでグラフを書く事が増えてきました。視覚的にデータを見せる事ができるので、説得力もあるし、ダラダラと数字を追うより、概要が理解しやすいメリットは大きいです。なによりも、多くの方に見せる時に、最良限度の時間で説明が完了してしまう簡潔さがあります。

先週の後半、Excelのグラフに格闘していました。Excelを操作してグラフを作る作業も、いろいろと大変な面もありますが、マクロでグラフを作成する作業は、思うのほか大変な道のりでした。

追加: 関連記事が幾つかあります。カテゴリExcelで検索してください。

もっと正確に書けば、グラフ専用のシートを作ったり、グラフの位置, サイズに考慮をしなければ、簡単な作業だったのですが(『簡単な作業。』は、単なる思い込みだった。と言うことが、作業の途中で気づいたのでしたが。)、データ(データソース)があるページにグラフを挿入する『インライングラフ』を作成する作業はなかなかのくせものでした。

分かっている人には、そう難しい事では無いのかも知れませんが、ExcelのVBAを真面目に触る様になってから、そう時間が経ってないオジサンのやることなので。

悩ましかったポイントを列記をすると、

  • グラフオブジェクトの階層構造がとても分かり辛い。
  • その影響で、プロパティーの指定が思う様に動作しない。エラーになる。と言う事が多発する。
  • グラフ関係の情報が、曖昧で知りたい情報がなかなか収集できない。

辺りに集約できるのでしょうか。

今回やりたかった事は、

  • 元となるデータの表がある。表の幅(表の項目)は一定だが、表の長さは異なる可能性が高い。
  • その表を集計し、集計表を作成。
  • 集計表を基に、インラインのグラフを4枚作成する。

この方法以外にもグラフオブジェクトを操作する方法は色々な記述がある模様です。ここに書く内容は、試行錯誤の上で『やっと理解できた』物であって、グラフオブジェクトの詳細が理解できている訳ではありません。それゆえ、間違いや冗長な部分も多分にあると思います。そのつもりで御覧ください。

では、実際のコード例を示しつつ、作業開始です。
なお、Microsoft Office 2010 Professional Edition を使用しているので、Excel 2007以降向けの書き方になります。Excel 2003までの古い環境ではこの通り書いても動作しません。(キット、エラーになります。)
また、Excel2013以降に新設された機能も使えないので触れていません。Excel2013以降でも、ここに書いてある通り指定すればチャント動作します。(する、らしいです。)

今回、僕が書いたスクリプトで使用した設定をザザッと追いかけてポイントを説明します。
グラフの作成は後半に出てきます。
長いので、必要なところだけ摘み食いしてください。多分問題ありません。

表のサイズを見つけ出す

今回の表は、A1~O44までありました。長さはつど違うので、『44』の部分は変動します。そこで、『44』部分を取り出します。

Range("D3").CurrentRegion.Select
table_br = Selection(Selection.Count).Row

A~C列(A~Cカラム)と、1~2行には、ヘッダーが書かれているのでここを基準にしてしまうと正しい表のサイズが検出できないので、データが本当に始まっている左上のセル D3 を基準します。

Range(基準のセル).CurrentRegion.Select
table_tr = Selection(1).Row
table_tc = Selection(1).Column
table_br = Selection(Selection.Count).Row
table_bc = Selection(Selection.Count).Column

で、表の先頭と最後の位置を取得できます。基準のセルを CR 形式で指定したければ、

Range("D3").CurrentRegion.Select
Range(Cells(3, 4)).CurrentRegion.Select

どちらでも目的通りの結果になります。

名前の定義

Dカラム~Oカラムまで、それぞれ決まった内容が記入されていて、3行目~表の最後の行までを、それぞれ集計したいので、それぞれに名前を付けて管理する事にしました。

Range(Cells(3, <カラム>), Cells(table_br, <カラム>)).Name = <名前>

Cells() に行とカラムの情報を渡し、A1形式に変換してから、Rangeに選択範囲を指定する処理を使用しました。選択範囲の指定方法は、その時都合が良い方法を選びます。 選択範囲に対して、Nameプロパティに適当名前を渡します。 集計表を作る時に、計算式や関数を定義しますが、表の内容を参照する時には、<名前>を呼び出す事でコードを読みやすくします。

フォントや色とか

ここはざっくり、

Range("A1:D10").Font.Name = "AR P丸ゴシック体M"
Range("A1:D10").Font.Size = 10
Range("A1:D10").Font.Bold = Falce
Range("A1:D10").Font.Color = RGB(赤,緑,青)
Range("A1:D10").Interior.Color = RGB(赤,緑,青)

みたいな感じに設定します。最後の行は背景色を変更する指定です。

集計表に罫線を引く

面倒な処理の第一弾。罫線です。罫線を引く考え方自体はシンプルなのですが、行数が多くなるので、面倒な部分です。面倒なだけで、簡単なのですが。
考え方は、

  • Excelで線を引く作業をそのまま再現する。
  • 罫線を弾きたい場所を最小限度に指定する。
  • 線は、スタイル, 太さ, 色の情報をそれぞれ指定する必要がある。

Excelの操作画面で罫線を引く作業と基本的には同じなのですが、Excelの画面の方が若干簡略化されています。

B2~D6に、なにも考えずに細い実線で格子状の罫線を引く例を示します。罫線の色は標準色とします。

Range("B2:D6").Borders.LineStyle = xlContinuous
Range("B2:D6").Borders.Weight = xlThin
Range("B2:D6").Borders.ColorIndex = xlAutomatic

なんだ。簡単じゃん。もし、1行目がヘッダーでその下の行だけ二重線にしたいなら、

' 全体
Range("B2:D6").Borders.LineStyle = xlContinuous
Range("B2:D6").Borders.Weight = xlThin
Range("B2:D6").Borders.ColorIndex = xlAutomatic

' ヘッダー部の二重線
Range("B3:D3").Borders(xlEdgeTop).LineStyle = xlNone
Range("B3:D3").Borders(xlEdgeTop).LineStyle = xlDouble
Range("B3:D3").Borders(xlEdgeTop).Weight = xlThick
Range("B3:D3").Borders(xlEdgeTop).ColorIndex = xlAutomatic
Range("B3:D3").Borders(xlEdgeTop).TintAndShade = 0

こんな感じです。一度引いた線を消してから、二重線を引き直しているのがポイントです。マクロの記録を取ると、

Range("B3:D3").Borders(xlEdgeTop).TintAndShade = 0

も必要らしいのですが、無くても動作するようです。意味も、パラメータの内容も不明ですが、念のため指定しますか。

集計表の表示形式

セルの結合とか、右寄せとか、小数点3桁まで表示とかのたぐいです。

Range(A1:D1).MergeCells = True
Range(A2).HorizontalAlignment = xlCenter
Range(A2).VerticalAlignment = xlCenter
Range(A2).NumberFormatLocal = "@"
Range(B2).HorizontalAlignment = xlRight
Range(B2).VerticalAlignment = xlCenter
Range(B2).NumberFormatLocal = "##0.000"

こんな感じです。スペルが長いだけで、簡単です。

関数などの埋め込み

集計表を作るために、関数や計算式を幾つか埋め込みました。使用した関数はExcelが持つ標準的なワークシート関数と、独自仕様のユーザー定義ワークシート関数、それに普通の計算式です。
単なる埋め込みなので、文字や数字を入れる方法と全く同じです。

例えば、A1 へ『line1_data』と名前をつけたセルの平均を入れる。と言う事がやりたい場合こんな感じです。

Range(A1).Value = "=AVERAGE(line1_data)"

ユーザー定義のワークシート関数でも、計算式でも文字でも数字(数値は、CStr()などで文字列化してから入れます。)でも、入れたセルを指定して、Valueプロパティに内容を入れるだけです。

カラム幅の変更

今回はカラム幅だけを変更しましたが、行幅の変更も同じ要領です。

Columns("C").ColumnWidth = 18
Rows(2).RowHeight = 20

値の代わりに、

Columns("C").Columns.AutoFit
Rows(2) Rows.AutoFit

AutoFitを指定すれば、自動設定になります。

グラフの作成

さて、やっと本題です。VBAでグラフ作成を行う流儀は複数あるようで、知識の少ない人が取り組むには、かなりの勉強とTre & Goが必要となります。少なくても、僕は、ほぼ1日グラフと格闘し、やっと思う様な(実は、まだ完璧ではない。)グラフを作れる様になりました。

これから説明する方法は、僕の試行錯誤の結果なので、必ずしも正解では無いかも知れません。が、僕と同様の仕様で良いなら、この方法で確実に動作します。

グラフ作成に必要な要素

Excel 2007以降では、

  1. まずグラフを入れる場所を確保する。
  2. そこにざっくりとグラフを書く。
  3. 細かい調整を行う。

の順序で仕上げるのが流儀の様です。この流儀を、絶対に守る必要があります。

グラフを入れる場所を確保する

ActiveSheet.Shapes.AddChart.Select

僕が辿り着いた書き方です。場所の確保には様々な書き方があるようで、書き方によっては以降に登場するプロパティ設定が旨く動作しない(エラーになる)と言う現象に悩まされる可能性があります。

ActiveSheet

を明示的に指定しています。これが無いとインラインにならない模様です。

Shapes

図形用のオブジェクトです。グラフは、図形オブジェクトの配下に位置するらしいので、まず図形用のオブジェクトを定義します。

AddChart

グラフを追加するよ。です。Excel 2013以降は AddChart2 が使えるらしいですが、手元に無いので試していません。

Select

そのグラフを選択するよ。この指定もキモです。

これで、グラフを入れる場所の確保が完了しました。
でも、まだ『どこに置くか』と言う物理的な場所指定は行っていません。『仮想的な場所を確保した。』と言う意味の様です。

データソースを指定する

どのデータからグラフを作成するか。の、データソースの指定です。

ActiveChart.SetSourceData Source:=Range("A1:E10"), PlotBy:=xlRows

こんな感じです。

ActiveChart

今操作しようとしている、グラフに対して。と言う意味の様です。

SetSourceData

データソースを指定しますよ。パラメータが2つあります。

Source:=Range("A1:E10")

データソースは、Range()で選択した範囲ですよ。
『:=』Pascalの様ですね。

PlotBy:=xlRows
PlotBy:=xlColumns

データソースを読み込む順序を指定します。省略すると、xlRowsです。

グラフの種類を指定する

作成したグラフの種類を指定します。この部分は説明するとキリが無いほど、色々な指定が可能です。
今回、僕は『集合縦棒』と『100%積み上げ横棒』を使用しました。

ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartType = xlBarStacked100

重ね合わせグラフを作成する場合などは、もっと細かい設定が必要になります。少し調べましたが、途中で諦めました(^^;)

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

ここが、この記事のメイン部分です。

Excelでグラフを作成してからプロパティを調べると、大きさは分かりますが、位置の指定が見当たりませんでした。見方が悪いだけかも知れませんが。
しかも、大きさの単位と、VBAで指定すべき単位が違うらしく、『単純に参考にする事はできないみたいだな。』と言う事が分かりました。

致し方無いので、セルの位置に併せる事を考えました。セルの位置なら、Range()で指定できるので比較的分かりやすいです。

結論から書くと、セルの位置に併せる事が可能です。
でも、ここで説明しているグラフの書き方をしないと、エラーが出てしまう様です。理由はわかりませんが、ここで説明している書き方に従うなら、Range() で指定した範囲にグラフが挿入されます。

ActiveChart.Parent.Top = ActiveSheet.Range("A12:E22").Top
ActiveChart.Parent.Left = ActiveSheet.Range("A12:E22").Left
ActiveChart.Parent.Height = ActiveSheet.Range("A12:E22").Height
ActiveChart.Parent.Width = ActiveSheet.Range("A12:E22").Width

データソースの真下に、1行開けて、10行分をグラフ領域として指定する。と言う設定です。
Parentの指定が、Shapes.AddChart.Selectの状態でないと旨く動作しない模様です。理由は分かりませんし、実はオブジェクト階層が間違っているだけ。なんてオチなのかも知れませんが。

この書き方がなかなか発見できなくて苦労しました。出来上がってみると、なんとも論理的だな。と感じるのですが。

グラフの間隔を調整する

Excel 2010で素直に『集合縦棒』を作成すると、グラフの間隔が絶妙に空いて見やすいのですが、その指定をしないとベッタリと連なったグラフが出来てしまいました。
Excel 2010で作成したグラフのプロパティーの値を参考に、設定を行います。

ActiveChart.ChartGroups(1).Overlap = -27
ActiveChart.ChartGroups(1).GapWidth = 219

Excelでみると単位は % ですが、VBAではそのまま指定すれば良い様です。

ChartGroups(1)

は、複数のグラフを重ね併せて描画する場合の、『1番目のグラフに対して。』と言う意味の様ですが、詳細は不明です。
取り敢えず、上記の様に記述するとExcel 2010の標準と同様になりました。

グラフタイトル

『集合縦棒』でグラフタイトルが表示されなかったので、指定しました。ついでにフォントやサイズも指定したのは良かったのですが、なぜかボールドに成ってしまったので、解除もしました。

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "<グラフのタイトル>"
ActiveChart.ChartTitle.Font.Name = "AR丸ゴシック体M"
ActiveChart.ChartTitle.Font.Size = 11
ActiveChart.ChartTitle.Font.Bold = Falce

凡例の位置変更

凡例が、グラフの右側に表示されました。グラフ下の方が都合が良かったので場所を変更しました。また、グラフと重ならない様な指定も入れました。

ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionBottom
ActiveChart.Legend.IncludeInLayout = True

その他の設定

グラフに対して細かい設定が行いたい場合には、この辺りに記述します。
データソースの指定が完了していれば、以降の順序は問わない様ですが、経験上グラフの位置とサイズを設定した後で指定した方がトラブルは少ない模様です。

これで完了 次のグラフも

ここまで書くと、見事グラフが書き出されました。めでたしめでたし。
初めにも書いた様に、今回グラフを4つ入れたかったので、残り3も同じ要領で作成します。

ここで疑問。『直ぐに、ActiveSheetを使って良いの?』 

結論から言えば、OKです。ActiveSheetから次のActiveSheetまでが前のグラフの指定、以降が次のグラフの指定です。 ですので、今までの知識を応用して別のグラフも作りました。設定項目は多いですが、サクサク作業が進みます。

印刷範囲とか、用紙のサイズとか、マージンとかの設定

印刷する事が前提なので、印刷範囲や、用紙, マージンの設定。フッターの設定なども行いました。

帳票タイトルの準備

フッターに帳票のタイトルを入れたかったので、その文字列を準備しました。
A列に日付(Date形式)が入っている場合、

st_date = Range("A3").Value
ed_date = Range("A" & Cstr(table_br)).Value
title_str = Format(Year(st_date), "0000") & "年" & _
            Format(Month(st_date), "00") & "月" & _
            Format(Day(st_date), "00") & "日 ~ " & _
            Format(Year(ed_date), "0000") & "年" & _
            Format(Month(ed_date), "00") & "月" & _
            Format(Day(ed_date), "00") & "日 ○○表"

こんな感じで良いと思います。

用紙のサイズと、向き

今回は、A4用紙を横向きで印刷するので、

ActiveSheet.PageSetup.PaperSize = xlPaperA4
ActiveSheet.PageSetup.Orientation = xlLandscape

としました。

ズーム設定

実はここもハマったポイントでした。データの量によっては1枚に収まらなく、Excelのご厚意(印刷範囲を明示した弊害による物で、常にこの様になる訳ではありません。)で縮小しA4 1枚におさめてくれるのですが、今回は複数枚になっても縮小したく無かったので、

ActiveSheet.PageSetup.Zoom = 100

を指定しました。

余白(マージン)の設定

基本的にExcelに設定した設定を丸写しにすれば良いのですが、Excelの方は単位がcmでVBAはポイントなので、単位変換を行う必要がありました。
ついでに、横方向はセンタリングする事にしました。

ActiveSheet.PageSetup.TopMargin = Application.CentimetersToPoints(1.4)
ActiveSheet.PageSetup.BottomMargin = Application.CentimetersToPoints(1.2)
ActiveSheet.PageSetup.LeftMargin = Application.CentimetersToPoints(0.4)
ActiveSheet.PageSetup.RightMargin = Application.CentimetersToPoints(0.4)
ActiveSheet.PageSetup.HeaderMargin = Application.CentimetersToPoints(0.7)
ActiveSheet.PageSetup.FooterMargin = Application.CentimetersToPoints(0.7)
ActiveSheet.PageSetup.CenterHorizontally = True

印刷範囲と印刷タイトル

Excelの画面上にはスクリプトを起動するためのボタンなど、余計な物も表示されているので、印刷範囲を明示的に指定しました。
また、印刷物は複数ページにまたがる可能性があるので、ヘッダ部分をタイトルとして指定しました。
印刷物自体のタイトルは、フッターに入れるので印刷されない様に設定を行いました。

ActiveSheet.PageSetup.PrintArea = "A1:AF" & CStr(table_br)
ActiveSheet.PageSetup.PrintTitleRows = "$1:$2"
ActiveSheet.PageSetup.PrintTitleColumns = ""

フッターの設定

フッターには、中央にデータソースの日付範囲入りのタイトルと、右側にはページを入れました。

ActiveSheet.PageSetup.CenterFooter = title_str
ActiveSheet.PageSetup.RightFooter = "&P" & "/" & "&N"

最後の始末

全ての処理が完了したので、カーソルを A1 に戻して起きます。

Range("A1").Select

なんとか、目的に印刷物をマクロで作成する事に成功しました。

どうしてマクロ化したかったかと言うと、作成するべき集計表が意外に複雑で、集計表も複数作成する必要があった。などなど、『毎回、集計表を作るのが面倒だな。』と言う動機からでした。
ついでに、グラフ作成もVBAでやっちゃおうかな?と思ったのが運の尽きでして、丸1日費やすほど複雑怪奇な世界に翻弄されてしまいました。

と言っても。インライン形式の単純なグラフならこの方法で、比較的簡単に作成できる事が分かった事は大収穫でした。

単純でないグラフとは、重ね合わせグラフや、データソースとして配列的な表が使えない場合です。(例えば、平日のデータと土日祝日のデータを別々のグラフとし、それを重ね合わせたい。などなど。)
なにが大変かは、今回敢えて触らなかったグラフグループを設定しないとならないためです。(設定しないと、意図したようには動かないみたいです。)グラフグループの設定が今ひとつ理解できず、手を付けませんでした。

手元にあるグラフには、まさしくそう言った怪しげなグラフが大量にあるので、いづれ自動化したいとは思うのですが、いつの事になるのでしょう。
設定の方法が分かったら、またポイントを紹介できればな。と思います。

トラックバック(6)

トラックバックURL:

昨日、『VBAでインライングラフを作成する』を投稿しました。その後で、バグと... 続きを読む

ExcelのVBAでグラフを作成する作業を続けています。懸案の1つだった、重... 続きを読む

ExcelのVBAでグラフを作成する作業を進めています。昨日、近似曲線を重ね... 続きを読む

ExcelのVBAによるグラフ作成です。少しずつパターンが増えて来ました。今... 続きを読む

ExcelのVBAによるグラフ作成です。今回は、少し変わり種です。『レーダー... 続きを読む

コメントする

サムネイル

[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年1月30日 17:22に書いたブログ記事です。

ひとつ前のブログ記事は「QTTabBarを入れて見ました」です。

次のブログ記事は「[Excel]VBAでインライングラフを作成する2」です。

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