EXCEL折れ線グラフについて

質問

エクセルの折れ線グラフでの質問です。

Sheet１　支店ごとの目標売上数の表
A支店　　　１００個
B支店　　１５００個
C支店　　　４５０個

Sheet２～　支店ごとの毎日の売上の表と折れ線グラフ
例：Sheet２（A支店）
１日（月）　１００個
２日（火）　１３０個
３日（水）　　８０個
４日（木）　１２０個
　・
　・
　・　

Sheet２以降の折れ線グラフに、各支店の目標売上数を赤線で直線をひいて、その値を赤字で表したいです。

私が考える範囲ではそれぞれのシートにそれぞれの目標値をコピーし、３１日分同じ数値を入れて折れ線グラフに直線をたす方法しかわかりません。何か他にいい案はありませんか？
また、それぞれの目標数はワードアートで表示するしかないでしょうか？
わかりにくい文章になってしまいましたが、宜しくお願いします。

回答No.6

pauNed · ベストアンサー

（#4コメントへのレスです）

#5のマクロを実行して、サンプルシートはできましたか？

...できてなくても、とりあえず１から手作業の手順を文章で説明するとして
１）仮に１ヶ月の売上数データがC2:C32にあるとします。
２）それを元に折れ線グラフをつくります。
３）目標数をH1とI1セルに入れます。
　　（サンプルシートでは店名をキーにして数式参照しています。
　　　どこでも良いので、２つのセルに同じ値を設定すれば、x軸と平行線がひけます）
４）グラフ右クリック[元のデータ]-[系列]タブ。
５）[追加]ボタンをクリック。
　　系列２が追加され、「値」欄に ={1} とはいります。
６）その「値」欄にマウスカーソルをおいて、H1:I1セル選択するとアドレスがはいります。
７）[OK]クリック。グラフの１日と２日の間に線が追加されます。
８）これを選択して右クリック[データ系列の書式設定]。
９）[データラベル]タブで「○ 値を表示する」にチェックして[OK]。
10）表示された「ラベル」を選択。間をおいて、２日のポイントのラベルをクリック。
11）２日のポイントのラベルだけが選択されるので、[Delete]キーで削除。
　　１日のポイントのラベルについては右クリック[データラベルの書式設定]で自由に変更してください。
12）系列２を選択して右クリック[近似曲線の追加]。
13）[種類]タブの[追加対象の系列]で「系列２」を選択。
14）[オプション]タブの「予測」で前方補外を 29.5 後方補外を 0.5 にして[OK]。
15）できた近似曲線を選択、右クリック[近似曲線の書式設定]-[パターン]タブで書式を変更できます。
...という手順です。
うまくいかない場合、どこの手順でつまずいているか、追加コメントください。

参考（googleイメージ検索で、比較的近いものがあったので）
http://www.gokuu.jp/press/archives/000226.html

お礼

pauNedさん
親切なご説明ありがとうございます！！
とりあえず、近似曲線をクリアしました＾-＾ひとつ出来ることが増えました♪
マクロのほうは知識不足すぎて意味がわかるまでに
かなり時間がかかりそうです(>_<)ありがとうございました。

回答No.5

pauNed

#4です。ごめんなさい。ダミーデータが足りませんでしたorz
ついでに確認メッセージを入れてみました。

Sub sample2()
　　'原則１回のみの実行です。『m』『d』というシートを削除すれば再実行できます。
　　With Sheets.Add
　　　　.Name = "m"
　　　　With .Range("A1:B200")
　　　　　　.Columns(1).Formula = "=ROW()"
　　　　　　.Columns(2).Formula = "=INT(RAND()*40+50)"
　　　　　　.Value = .Value
　　　　　　.Select
　　　　End With
　　　　MsgBox "まず、ダミーデータ(m)作成です。（各店目標値）"
　　End With
　　With Sheets.Add
　　　　.Name = "d"
　　　　.Range("A1:C1").Value _
　　　　　　　　= [{"店名","日にち","売上"}]
　　　　.Range("E1:I1").Formula _
　　　　　　　　= [{"店名","=SUBTOTAL(1,A2:A6201)","目標","=VLOOKUP(F1,m!A1:B200,2,0)","=H1"}]
　　　　With .Range("A2:C6201")
　　　　　　.Columns(1).Formula = "=INT(ROW(A31)/31)"
　　　　　　.Columns(2).Formula = "=MOD(ROW(A31),31)+1"
　　　　　　.Columns(3).Formula = "=INT(RAND()*80+20)"
　　　　　　.Value = .Value
　　　　　　.Select
　　　　End With
　　　　MsgBox "ダミーデータ(d)作成終了です。（200店×31日）"
　　　　.Range("A1:A6201").AutoFilter Field:=1, Criteria1:=1
　　　　With .ChartObjects.Add(150, 30, 500, 300).Chart
　　　　　　.Parent.Placement = xlFreeFloating
　　　　　　.ChartType = xlLine
　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　.XValues = "=d!R2C2:R6201C2"
　　　　　　　　.Values = "=d!R2C3:R6201C3"
　　　　　　　　.Name = "=d!R1C3"
　　　　　　End With
　　　　End With
　　　　.Range("H1:I1").Select
　　　　DoEvents
　　　　MsgBox "ノーマルなグラフを作成しました。次で、H1:I1 を系列２として追加します。"
　　　　With .ChartObjects(1).Chart
　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　.Values = "=d!R1C8:R1C9"
　　　　　　　　DoEvents
　　　　　　　　MsgBox "次で、系列２に近似曲線を追加します。（前方補外29.5/後方0.5）"
　　　　　　　　.Trendlines.Add(Type:=xlLinear, _
　　　　　　　　　　　　　　　　Forward:=29.5, _
　　　　　　　　　　　　　　　　Backward:=0.5).Border.ColorIndex = 3
　　　　　　　　DoEvents
　　　　　　　　MsgBox "系列２のラベルを表示します。"
　　　　　　　　.ApplyDataLabels Type:=xlDataLabelsShowValue
　　　　　　　　DoEvents
　　　　　　　　MsgBox "２ラベルのうち、１個不要なのでクリアします。"
　　　　　　　　.Points(2).DataLabel.Delete
　　　　　　　　DoEvents
　　　　　　　　MsgBox "ラベルの書式を設定します。"
　　　　　　　　With .DataLabels
　　　　　　　　　　.Interior.ColorIndex = 15
　　　　　　　　　　With .Font
　　　　　　　　　　　　.FontStyle = "太字"
　　　　　　　　　　　　.Size = 10
　　　　　　　　　　　　.ColorIndex = 3
　　　　　　　　　　End With
　　　　　　　　End With
　　　　　　End With
　　　　　　.ChartArea.AutoScaleFont = False
　　　　　　DoEvents
　　　　　　MsgBox "系列２の凡例をクリアします。"
　　　　　　.Legend.LegendEntries(2).Delete
　　　　End With
　　End With
　　MsgBox "以上で終了です。A列のオートフィルタで店を選択できます。"
End Sub

回答No.4

pauNed

こんにちは。
>各支店の目標売上数を赤線で直線をひいて、その値を赤字で表したい
これは、目標値を２点設定し、系列を追加、近似曲線を利用すれば良いかと思います。
それよりも
>２００以上もあるシートを作成する
ほうがたいへんかと^ ^;

文章で説明するのがたいへんなので、サンプルシートを作るマクロを書いてみました。
マクロの内容は、
シートを２枚追加して、ダミーデータとグラフを作成する内容です。
既存データには影響を与えません。
データは１シートに集約し、オートフィルタで必要な店を表示する形式にしてみました。

【手順】
1)新規Bookから[Alt]キー+[F11]キー同時押し。VisualBasicEditor（VBE）が起動します。
2)そのVBE画面で[Alt]キー → [i]キー → [m]キー の順押し。
　標準モジュールが挿入され、マウスカーソルが無地シートのようなものにフォーカスします。
　（コードウィンドウといいます）
3)そこに以下をコピーペーストです。

Sub sample1()
　　'原則１回のみの実行です。『m』『d』というシートを削除すれば再実行できます。
　　With Sheets.Add
　　　　.Name = "m"
　　　　With .Range("A1:B31")
　　　　　　.Columns(1).Formula = "=ROW()"
　　　　　　.Columns(2).Formula = "=INT(RAND()*40+50)"
　　　　　　.Value = .Value
　　　　End With
　　End With
　　With Sheets.Add
　　　　.Name = "d"
　　　　.Range("A1:C1").Value _
　　　　　　　　= [{"店名","日にち","売上"}]
　　　　.Range("E1:I1").Formula _
　　　　　　　　= [{"店名","=SUBTOTAL(1,A2:A6201)","目標","=VLOOKUP(F1,m!A1:B31,2,0)","=H1"}]
　　　　With .Range("A2:C6201")
　　　　　　.Columns(1).Formula = "=INT(ROW(A31)/31)"
　　　　　　.Columns(2).Formula = "=MOD(ROW(A31),31)+1"
　　　　　　.Columns(3).Formula = "=INT(RAND()*80+20)"
　　　　　　.Value = .Value
　　　　End With
　　　　.Range("A1:A6201").AutoFilter Field:=1, Criteria1:=1
　　　　With .ChartObjects.Add(200, 30, 500, 300).Chart
　　　　　　.ChartType = xlLine
　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　.XValues = "=d!R2C2:R6201C2"
　　　　　　　　.Values = "=d!R2C3:R6201C3"
　　　　　　　　.Name = "=d!R1C3"
　　　　　　End With
　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　.Values = "=d!R1C8:R1C9"
　　　　　　　　.Name = "=d!R1C7"
　　　　　　　　.Trendlines.Add(Type:=xlLinear, _
　　　　　　　　　　　　　　　　Forward:=29.5, _
　　　　　　　　　　　　　　　　Backward:=0.5).Border.ColorIndex = 3
　　　　　　　　.ApplyDataLabels Type:=xlDataLabelsShowValue
　　　　　　　　.Points(2).DataLabel.Delete
　　　　　　　　With .DataLabels
　　　　　　　　　　.Interior.ColorIndex = 15
　　　　　　　　　　With .Font
　　　　　　　　　　　　.FontStyle = "太字"
　　　　　　　　　　　　.Size = 10
　　　　　　　　　　　　.ColorIndex = 3
　　　　　　　　　　End With
　　　　　　　　End With
　　　　　　End With
　　　　　　.ChartArea.AutoScaleFont = False
　　　　　　.Legend.LegendEntries(2).Delete
　　　　　　.Parent.Placement = xlFreeFloating
　　　　End With
　　End With
End Sub

4)上記コード内のどこでも良いので、マウスカーソルを置いて、[F5]キーで実行です。
（もしくは、Excel側[ツール]-[マクロ]-[マクロ]からでも実行できます）

お礼

pauNedさん、回答ありがとうございます。
せっかくご回答いただいたのに私の知識不足で残念ながらほとんど
理解できていません。ごめんなさい。まず、
>各支店の目標売上数を赤線で直線をひいて、その値を赤字で表したい
これは、目標値を２点設定し、系列を追加、近似曲線を利用すれば良いかと思います。

このやり方がよくわかっていません。目標値を２点設定するのはどうしたらいいのでしょうか？？近似曲線？？といった感じなのです。
ごめんなさい詳しく教えてください。

回答No.3

koko88okok

私の場合は、参考URLに見られるように、オートシェイプで目標線を引く方法を実施していますが、mipmip さんの『それぞれのシートにそれぞれの目標値をコピーし、３１日分同じ数値を入れて折れ線グラフに直線をたす方法』の方が優れていると思います。

目標値の線を選択して「データ系列の書式設定」で、「マーカー」を「なし」、「線の太さ」や「色」をご希望に合わせて設定し、表の目標データが気になるようなら非表示にするか、文字の色を白に設定されればよいと思います。

今後、使わせて頂こうと思います。

「体重推移グラフ作成方法」
http://plaza.rakuten.co.jp/nyuron/3001

お礼

皆さんご回答ありがとうございました。
imogasiさんのおっしゃるように、何が解決したいのかが明確でなかったので補足させていただきます。
質問にのせたのは例だったため、A・B・C支店となっていますが、
実際はそれが２００ヶ所以上あるんです。
上司に言われて１ヶ所ずつのグラフを作ることになったんですが
１ヶ所なら現在のやり方で良いものの、２００以上もあるシートを作成
するため出来れば一ヶ所一ヶ所目標値をコピーをしたり、ワードアートで
数値を入れていくことを避けたかったんです。
このような場合、何か良い方法はありますでしょうか？
※目標値は例のような切りのいい数字ばかりではなく６８４のような
ものもあります。そのため補助目盛をそれに合わせるという方法ができません。

回答No.2

imogasi

（１）目標値と同じ値を各期間に追加して、Ｘ軸に平行な直線の（形式的には折れ線）にする（おっしゃっているもの）
（２）Ｘ軸並行に、横に直線図形を目標値の高さに貼り付ける。
正確にやるのが難しい。
（３）補助目盛り線を目標値刻みにする。
目標値、目標値ｘ２、目標値ｘ３の線が出てしまうが、実績が
目標値ｘ２まで行かない、という通常状態なら、目標値の線１ポンが出る。
ただ主目盛が制約される。
（４）（２）をＶＢＡでやる方法について
ActiveChart.Lines.Add 10, 100, 500, 100
において
１０　　線の左始まり点を決定、
５００　右終わり点を決定、
１００　線の高さを決定
を適当に計算（３番目の数は目標値が規制）すれば、目標値に横線を引けそうだ。線の色を返るのも簡単。
あと、目標数や見出し「目標数」はテキストボックスでも貼り付ける。
ただ小生は、解決すべき点がわからないところがあり、例解は載せられませんが。
ご参考までに。

回答No.1

saezuri

折れ線を追加する方法の代用に下記のようにしてみてはどうでしょうか

グラフウィザード＞ユーザ設定に
2軸上の折れ線と縦棒

があれば目標値を縦棒に、実績を折れ線にして表を作成

棒グラフを右クリックしてデータ系列の書式設定を選択

オプションにある棒の間隔を0に、データラベルの値を表示にする
パターンの輪郭をなしにすればOK

背景色（プロットエリアの色）と縦棒の色の境目が目標値になります。
プロットエリアの色をA色として
縦棒をB色～A色の塗りつぶし効果（B色が上になるよう）にすると綺麗になると思います。

データラベルのフォントの色を変更すれば文字色も変えられます。

軸の最大、最小値は両方とも同じにするのがポイントです。

↑コレ以外の方法が見つかりませんでしたorz