（Excel for Mac 2011で確認しています。書き方が古い場合があります。）
この記事は「理系が使ってもそれほど怒られないグラフをExcelで」の第4弾です。
文中のマクロはすべて「散布図が選択状態であること」を前提に書いています。
詳しい使いかたは最初の記事をご覧ください。
今回は「近似曲線を追加する機能」に特化したマクロを書いてみます。散布図が既に作成され、グラフの体裁もある程度整っている状態を想定してます。
グラフの体裁を整えるマクロはこちら→第1弾 第2弾 第3弾
1 近似曲線をマクロで追加すると嬉しいこと
式とR^2値の表示を任意の傾きに回転できる＼(^^)／
私の環境だと、なぜか手動では水平か垂直しか選べないのです。最新版でもそうなんでしょうか。
1次の近似直線が複数あるときなどは、直線に沿ってラベルを傾けたいです。
2 近似曲線の作成
まず作成します。Add（追加）ですので、すでに近似曲線がある場合は2本、3本…と増えていきます。
With ActiveChart
.SeriesCollection(1).Trendlines.Add
End With
近似曲線は自動で(1)(2)(3)...とナンバリングされます。SeriesCollection(1).Trendlines(2)は、系列1に対する2本目の近似曲線という意味になります。
以降、With ActiveChart と End With を省略しますが、前後に書いてある前提です。
次に、近似の種類を指定します。1次(xlLinear)、n次(xlPolynomial)、指数(xlExponential)、べき乗(xlPower)、対数(xlLogarithmic)、移動平均(xlMovingAvg)が選べます。
.SeriesCollection(1).Trendlines(1).Type = xlLinear
近似の種類によって使えるオプションが異なります。
' 以下は必要なときにコメントアウトを外してください。
'.SeriesCollection(1).Trendlines(1).Intercept = 0 ' 切片 線形近似で使用できます
'.SeriesCollection(1).Trendlines(1).Order = 3 ' 次数 n次線形に必須です
'.SeriesCollection(1).Trendlines(1).Period = 3 ' 区間数 移動平均に必須です
理論上ありえないデータ点が含まれている場合は、近似曲線は作成されないようです。
近似曲線の長さが物足りないときは、前後に伸ばすことができます。
.SeriesCollection(1).Trendlines(1).Forward2 = 1.5 ' 線を前方に延長します
.SeriesCollection(1).Trendlines(1).Backward2 = 1.5 ' 線を後方に延長します
横軸で換算して何単位ぶん伸ばすかを、数値で指定できます。
3 ラベルの作成
近似曲線のラベルには線の式またはR^2値、またはその両方を表示できます。
.SeriesCollection(1).Trendlines(1).DisplayRSquared = True ' R^2の表示
.SeriesCollection(1).Trendlines(1).DisplayEquation = True ' 式の表示
ラベルはこんな感じでいろいろ変えられます。
.SeriesCollection(1).Trendlines(1).DataLabel.Select
With Selection
.Top = 175
.Left = 265
.Format.TextFrame2.TextRange.Font.Size = 15
.Format.TextFrame2.TextRange.Font.Name = "Century Schoolbook"
.Orientation = 40 ' 文字の傾き degree
End With
いったん選択状態にしてから操作しているのは、私の環境ではそうしないと上手く動かなかったので。.SeriesCollection(1).Trendlines(1).DataLabel.Top = 175とかでも動きそうな気がしたんですが。
Orientationについては水平や垂直を指定するための定数が用意されているみたいなのですが、適当な整数を入れたらその角度に傾けてくれました。
しかしExcelのこういう、パーツの種類によって操作方法が微妙に違う感じは何とかならないんでしょうかね。
ところで、式とR^2値を両方非表示のときに、書式を指定しようとするとエラーが出てマクロが止まります。
止まってしまったら、
「マクロを修正する」「マクロの一時停止を解除する」のふたつを行なってください。
不要な行は「'」を行頭につけるとコメントアウトできます。
4 凡例
グラフに凡例がある場合、このように「近似曲線の凡例」が現れます。
凡例に表示される言葉は以下で変えられます。
.SeriesCollection(1).Trendlines(1).Name = "MA"
しかし、消したいということのほうが多い気がします。上図の例だと、凡例の3行目を消すということで、以下のようになります。
.Legend.LegendEntries(3).Delete
場合によっては、手動で削除したほうが早いかもしれません。うまくクリックして凡例の一部だけを選択し、削除することが可能です。
また、近似曲線を追加すると凡例のボックスに文字が入りきらなくなり、レイアウトが崩れることがあります。
そんなときは、凡例のボックスのサイズを調節するとなおります。
.Legend.Width = 80
.Legend.Height = 90
5 マクロの利便性を上げる
このシリーズはグラフをできるだけ簡単に美しくすることを優先するので、プログラミング的なことは極力避けています。
しかし気になる人は、Addを実行するたびに近似曲線が増えてしまう問題、ラベルが無いときに書式設定できない問題、いらない凡例が登場する問題、を自動で回避するように、ひと工夫しても良いかもしれません。
以下はすごく大雑把な例です。このマクロは、予期しない結果を招く可能性がゼロではないので、良いプログラミングとは言えませんが、まあだいたい、そこそこ、ほぼほぼ使えるかとおもいます。
Sub Lines()
' 散布図を作成し、選択した状態で実行してください。
' 動作確認環境：Excel for Mac 2011 散布図＞散布図（オプションなし）
With ActiveChart
With .SeriesCollection(1) ' カッコ内には近似曲線を作成する系列番号を入れてください
If .Trendlines.Count = 0 Then
.Trendlines.Add
End If
.Trendlines(1).Name = "MA" ' 凡例がある場合、このテキストが凡例に表示されます。
' 近似曲線の種類 1次(xlLinear)、n次(xlPolynomial)、移動平均(xlMovingAvg)、
' ab ^ x(xlExponential)、c ln x(xlLogarithmic)、ax ^ b(xlPower)を選べます
.Trendlines(1).Type = xlLinear
.Trendlines(1).DisplayRSquared = True ' R^2の表示
.Trendlines(1).DisplayEquation = False ' 式の表示
' 以下は必要なときにコメントアウトを外してください。
' .Trendlines(1).Intercept = 0 ' 切片 線形近似で使用できます
' .Trendlines(1).Order = 2 ' 次数 n次線形に必須です
' .Trendlines(1).Period =3 ' 区間数 移動平均に必須です
'
' .Trendlines(1).Forward2 = 1.5 ' 線を前方に延長します
' .Trendlines(1).Backward2 = 1.5 ' 線を後方に延長します
On Error GoTo Point01 ' ラベルがないときはスキップします
.Trendlines(1).DataLabel.Select
With Selection
.Top = 320 ' ラベルの位置
.Left = 280
.Format.TextFrame2.TextRange.Font.Size = 15 ' ラベルのフォントサイズ
.Format.TextFrame2.TextRange.Font.Name = "Century Schoolbook" ' ラベルのフォント
.Orientation = 5 ' ラベルの傾き degree
End With
End With
Point01:
If .HasLegend = True Then
If .Legend.LegendEntries.Count > .SeriesCollection.Count Then
.Legend.LegendEntries(.SeriesCollection.Count + 1).Delete ' 近似曲線の凡例を消します
End If
End If
' 凡例のレイアウトが崩れるときはこちらをご利用ください
' .Legend.Width = 40
' .Legend.Height = 60
End With
End Sub
ラベルの位置など変えたいときは、数字を書き換えて再度実行してください。
実行前
実行後
6 今後の展望
・第二縦軸の利用
・エラーバーを追加
・プロットの値を表示
・関数曲線を追加
めげそう。