エクセルのグラフで推移を知る。。。。

質問

エクセルのグラフについて質問です。

任意のセル値の過去何ヶ月間（あるいは何日間）の値の推移を折れ線グラフで表示したいと思っています。（１月は１００、２月は５０、３月は１５０・・・というふうに）

違う別のセルに新しい値をどんどん入力していけば比較的簡単にできると思いますが、同じセルに値を上書きしていっても上書きされた値がグラフに反映されていくようにしたいです。

具体的にどのようにすればできるかご存知の方いらっしゃいますか？？？

回答No.3

noname#95859 · ベストアンサー

下記でもって、置き換えてください。
ポイント：
(1)myDate = Int(Now)　－－－これにより、時間を切り捨て、日にちだけにする。
(2).Axes(xlCategory).TickLabels.NumberFormatLocal = "yyyy mm/dd"　－－－これにより
グラフのX軸フォーマットを決める
(3) Do While rowpos <= position - 1 --- loop
これにより、入力された日にちがすでに存在する時には、上書きする。
新しい日であれば、新データとしてHistoryに追記する。
スクリプトの中に、変更、追加した箇所をコメントしてあります。
-----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myData As String
Dim myDate As Date
Dim position As Long
Dim FlagDuplicated As Integer
Dim rowpos As Integer

Application.EnableEvents = False
If Application.MoveAfterReturn = True And Target.Address = "$A$2" Then
Cells(2, 1).Select
End If

If Target.Address = "$A$2" Then
myData = Target.Value
myDate = Int(Now) 'changed

With Sheets("History")
position = .Cells(65536, 1).End(xlUp).Offset(1, 0).Row
If position = 2 Then .Cells(1, 1).Value = "Date": .Cells(1, 2).Value = "Data"
If IsNumeric(Target.Value) Then
FlagDuplicated = 0 'added
rowpos = 2 'added
Do While rowpos <= position - 1 'added
If .Cells(rowpos, 1).Value = myDate Then 'added
.Cells(rowpos, 1).Value = myDate 'added
.Cells(rowpos, 2).Value = myData 'added
FlagDuplicated = 1 'added
Exit Do 'added
End If 'added
rowpos = rowpos + 1 'added
Loop 'added
If FlagDuplicated = 0 Then 'added
.Cells(position, 1).Value = myDate
.Cells(position, 2).Value = myData
End If 'added
Else
.Cells(position - 1, 1).ClearContents
.Cells(position - 1, 2).ClearContents
End If
End With

Call MakeGraph

End If
Application.EnableEvents = True
End Sub

-----------------------------------
Sub MakeGraph()
Dim WS As Worksheet
Dim GR1 As ChartObject
Dim myGraph_found As Boolean
Dim E_rowpos As Integer
Dim hani As String
Dim S_Date As Date
Dim E_Date As Date

E_rowpos = Worksheets("History").Cells(65336, 1).End(xlUp).Row
hani = "A1:B" & E_rowpos
If E_rowpos > 2 Then
S_Date = Worksheets("History").Cells(2, 1).Value
E_Date = Worksheets("History").Cells(E_rowpos, 1).Value
hani = "A1:B" & E_rowpos
myGraph_found = False

Set WS = Worksheets("DataIn")
For Each GR1 In WS.ChartObjects
If GR1.Name = "myGraph" Then
myGraph_found = True
End If
Next

If myGraph_found = False Then
Set GR1 = WS.ChartObjects.Add(10, 50, 300, 200)
GR1.Name = "myGraph"
Else
Set GR1 = WS.ChartObjects("myGraph")
End If

With GR1.Chart
.SetSourceData Source:=Sheets("History").Range(hani), PlotBy:=xlColumns
.ChartType = xlXYScatterLines

.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinorUnitIsAuto = True
.Axes(xlValue).MajorUnitIsAuto = True
.Axes(xlCategory).MinorUnitIsAuto = True
.Axes(xlCategory).MajorUnitIsAuto = True
.Axes(xlCategory).TickLabels.NumberFormatLocal = "yyyy mm/dd" 'added
.HasTitle = True
.ChartTitle.Characters.Text = "Trial"
.HasLegend = False
End With
End If
End Sub
-----------------------------------------------

回答No.2

noname#95859

入力の履歴を別シートにイベントプロシージャ(Worksheet_Change)を使って書き込みます。
その上で、標準モジュールのグラフ作成ルーチンを呼び出します。

(1)準備：シート名「DataIn」と「History」をもつシートを用意してください。
そして、シート「DataIn」のセルA1に”Data"と入れてください。

(2)標準モジュールに下記を転記してください。
-------------------------------------
Sub MakeGraph()
Dim WS As Worksheet
Dim GR1 As ChartObject
Dim myObject As Object
Dim myGraph_found As Boolean
Dim E_rowpos As Integer
Dim hani As String
Dim S_Date As Date
Dim E_Date As Date

E_rowpos = Worksheets("History").Cells(65336, 1).End(xlUp).Row
hani = "A1:B" & E_rowpos
If E_rowpos > 2 Then
S_Date = Worksheets("History").Cells(2, 1).Value
E_Date = Worksheets("History").Cells(E_rowpos, 1).Value
hani = "A1:B" & E_rowpos
myGraph_found = False

Set WS = Worksheets("DataIn")
For Each GR1 In WS.ChartObjects
If GR1.Name = "myGraph" Then
myGraph_found = True
End If
Next

If myGraph_found = False Then
Set GR1 = WS.ChartObjects.Add(10, 50, 300, 200)
GR1.Name = "myGraph"
Else
Set GR1 = WS.ChartObjects("myGraph")
End If

With GR1.Chart
.SetSourceData Source:=Sheets("History").Range(hani), PlotBy:=xlColumns
.ChartType = xlXYScatterLines

.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinorUnitIsAuto = True
.Axes(xlValue).MajorUnitIsAuto = True
.Axes(xlCategory).MinorUnitIsAuto = True
.Axes(xlCategory).MajorUnitIsAuto = True

.HasTitle = True
.ChartTitle.Characters.Text = "Trial"
.HasLegend = False
End With
End If

End Sub

(3)VBAprojectにて、Sheet1(DataIn)をダブルクリックして出てくるVBAeditor画面に下記のスクリプトを転記してください。
-------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myData As String
Dim myDate As Date
Dim position As Long

Application.EnableEvents = False
If Application.MoveAfterReturn = True And Target.Address = "$A$2" Then
Cells(2, 1).Select
End If

If Target.Address = "$A$2" Then
myData = Target.Value
myDate = Now

With Sheets("History")
position = .Cells(65536, 1).End(xlUp).Offset(1, 0).Row
If position = 2 Then .Cells(1, 1).Value = "Date": .Cells(1, 2).Value = "Data"
If IsNumeric(Target.Value) Then
.Cells(position, 1).Value = myDate
.Cells(position, 2).Value = myData
Else
.Cells(position - 1, 1).ClearContents
.Cells(position - 1, 2).ClearContents
End If
End With

Call MakeGraph

End If
Application.EnableEvents = True
End Sub

-----------------------------------------
(4)使い方
シート「DataIn」のセルA2にデータを入れて、「Enter」を押してください。
2つ目のデータをA2にいれて、「Enter」を押してください。
すると、グラフが現れます。3つ目のデータを入れると、グラフが変化します。
もし、その値が違うということであれば、数値以外のキーをA2に入力、「Enter」を押してください。

(5)その他
デバッグはそれなりにしていますので、まずは問題なく動くと思います。
しかし、実際の仕事に使えるかと言うと、まだ、問題があります。
例えば、データが時間単位である場合、日単位である場合、はたまた月単位である場合、
X軸の表記は、それなりにコントロールされるべきです。さもないと、見づらいです。
自動でコントロールすることは、難しくないのですが、今回には、入っていません。

補足

Rich53さん、ありがとうございました。(^^♪
大変分かりやすい回答で、早速試しにやってみたところできました。

ひとつ質問があります。
入力した日時でグラフが更新されますが、同じ日にちであれば時間は関係なく、何回打ち直してもその日のグラフの値は変わらないようにしたいのですが、このモジュールのどこかを変えるとできますか？

回答No.1

mshr1962

まず単純なことですが、上書きの場合過去のデータがなくなるのでグラフは作れません。

おっしゃるようにする方法は、マクロを使って入力の履歴を表にして
その表を元にしたグラフを表示する方法だけです。
ところで、入力した値の見出しはどうするのでしょうか？入力した日付でいいのでしょうか？
その辺を補足されれば、よい回答がつくと思います。

お礼

早速ありがとうございました。

マクロで別シートに入力の履歴を作成してそのグラフを作成するのですね。

入力の履歴をその都度グラフに反映させる（入力した日付で見出し作成）ようなマクロを作成したいです。

マクロは全くの初心者なので、具体的な作成方法を教えていただければうれしいです。