エクセルでグラフを最新の50個のデータにしたい

質問

シートのA列に日付、B列に特性A、C列に特性B・・と列ごとに上から順に空白なくデータが埋まっていく表があります。
このうちの2つの特性（列）をグラフにして「横軸が自動で伸びていく」グラフはできたのですが、ブック（製品）によってデータの数が数個～数百個まで大きく違っているので自動では目的と違ってしまう状況になってしまっています。
画面の大きさの関係でグラフの大きさが限られてしまっており、データが多くなると棒状になってしまいます。
また、同じようにデータの平均値や過去最少値、最大値もあまりデータ数が大きくなってしまっては意味がなくなってしまうことから、直近の３０個とか５０個のデータで傾向管理するように変更しました。

これに伴ってグラフも同様に最新の30個とか50個のデータで自動作成されるようにしたいのですが。（このデータの数は可変が好ましい）

対象要素は2つまで、各要素は主軸と第2軸で表示させたい。
指定のデータ個数（最低30個）に満たない場合は入力されたデータだけでグラフが作成される、ようにしたいのですが。

現在の方法はまず各要素に「名前の定義」をして、グラフの各線のSERIES(　)内を対応する名前に割り当てる、といったような方法で、当方にとってはかなり煩雑な方法です。
もしマクロの方が簡単なら、完全自動でなくてもデータ入力後のボタンクリックでグラフが更新される、はOKです。

色々勝手な要求を書きましたが何卒よろしくお願いいたします。

回答No.8

HohoPapa · ベストアンサー

>本チャンのブック
のデータレイアウトが未詳のままではありますが、
>データ範囲が41行目から79行目に固定されてしまうようです。
の問題は、以下にポストするコードで解決するはずです。
なお、1列目は、
データ開始行（17行目）以下、
有効行末まで空行なく埋まっている前提です。

>横軸の指定がグラフ要素になってしまう
これは多少予想していました。
横（項目）軸ラベルの範囲設定と
データ要素群の範囲設定の区別方法についての
私の理解が浅く手探りだからです。

ともあれ、
期待のグラフを右クリックし、
データの選択を選び、
表示される設定画面、凡例項目に余分な行があれば削除し
横（項目）軸ラベルは、編集を選び
明示的に範囲を指定してみてください。

その後、以下にコードを2種類ポストしますので
それぞれ試してみてください。
ポストしたコードはそれぞれデータ要素が２つある前提で
前者は、横（項目）軸ラベルを指定するコード
後者は、横（項目）軸ラベルを指定しないコードです。

Sub GraphSauceChange2()

　Const MaxRows = 10　 'データ範囲に指定する最大行数
　Const TgShNum = 2　　'対象シート番号
'　Const ColNum0 = 8　　'x軸要素名列
　Const ColNum1 = 3　　'1つ目データ格納列
　Const ColNum2 = 5　　'2つ目データ格納列
　Const SRowNum = 17　 'データ開始行番号
　Const KoumokuRow = 5 '項目名格納行番号

　Dim SRow As Long　　 'グラフ用データ開始行
　Dim ERow As Long　　 'グラフ用データ終了行
'　Dim tgRange0 As Range　'X軸ラベル
　Dim tgRange1 As Range　'データ群1つ目範囲
　Dim tgRange2 As Range　'データ群2つ目範囲
　Dim tgRangeA As Range　'上記合計範囲

　With ThisWorkbook.Sheets(TgShNum)

　　ERow = .Cells(.Rows.Count, 1).End(xlUp).Row
　　If ERow < MaxRows + SRowNum Then
　　　SRow = SRowNum
　　Else
　　　SRow = ERow - MaxRows + 1
　　End If

　　MsgBox (SRow & "-" & ERow)　　'デバック用コード

　'　Set tgRange0 = _
　　　Range(.Cells(SRow, ColNum0), .Cells(ERow, ColNum0))
　　Set tgRange1 = _
　　　Range(.Cells(SRow, ColNum1), .Cells(ERow, ColNum1))
　　Set tgRange2 = _
　　　Range(.Cells(SRow, ColNum2), .Cells(ERow, ColNum2))
　 ' Set tgRangeA = Union(tgRange0, tgRange1, tgRange2)　　　'結合
　　Set tgRangeA = Union(tgRange1, tgRange2)　　　 '結合
　　.ChartObjects(1).Chart.SetSourceData Source:=tgRangeA　 'セット

　　.ChartObjects(1).Chart.SeriesCollection(1).Name = _
　　　.Cells(KoumokuRow, ColNum1).Value
　　.ChartObjects(1).Chart.SeriesCollection(2).Name = _
　　　.Cells(KoumokuRow, ColNum2).Value

　End With

End Sub

Option Explicit

Sub GraphSauceChange2()

　Const MaxRows = 10　 'データ範囲に指定する最大行数
　Const TgShNum = 2　　'対象シート番号
　Const ColNum0 = 8　　'x軸要素名列
　Const ColNum1 = 3　　'1つ目データ格納列
　Const ColNum2 = 5　　'2つ目データ格納列
　Const SRowNum = 17　 'データ開始行番号
　Const KoumokuRow = 5 '項目名格納行番号

　Dim SRow As Long　　 'グラフ用データ開始行
　Dim ERow As Long　　 'グラフ用データ終了行
　Dim tgRange0 As Range　'X軸ラベル
　Dim tgRange1 As Range　'データ群1つ目範囲
　Dim tgRange2 As Range　'データ群2つ目範囲
　Dim tgRangeA As Range　'上記合計範囲

　With ThisWorkbook.Sheets(TgShNum)

　　ERow = .Cells(.Rows.Count, 1).End(xlUp).Row
　　If ERow < MaxRows + SRowNum Then
　　　SRow = SRowNum
　　Else
　　　SRow = ERow - MaxRows + 1
　　End If

　　MsgBox (SRow & "-" & ERow)　　'デバック用コード

　　Set tgRange0 = _
　　　Range(.Cells(SRow, ColNum0), .Cells(ERow, ColNum0))
　　Set tgRange1 = _
　　　Range(.Cells(SRow, ColNum1), .Cells(ERow, ColNum1))
　　Set tgRange2 = _
　　　Range(.Cells(SRow, ColNum2), .Cells(ERow, ColNum2))
　　Set tgRangeA = Union(tgRange0, tgRange1, tgRange2)　　　'結合
　　'Set tgRangeA = Union(tgRange1, tgRange2)　　　 '結合
　　.ChartObjects(1).Chart.SetSourceData Source:=tgRangeA　 'セット

　　.ChartObjects(1).Chart.SeriesCollection(1).Name = _
　　　.Cells(KoumokuRow, ColNum1).Value
　　.ChartObjects(1).Chart.SeriesCollection(2).Name = _
　　　.Cells(KoumokuRow, ColNum2).Value

　End With

End Sub

お礼

ものすごい大作を提示していただいて恐縮なのですが、当方には使えそうにない（全く動かず）ので再度具体的な表の構成を添付して別の質問にしましたのでいったん締め切らせていただきます。

お手数をおかけしました。

補足

毎度お世話になっております。

いつもと違って心苦しい報告です。
ご回答の＃１、＃２、＃３、を昨夕より試してみたのですが、#2、#3は全く動く気配なし。
エラーメッセージで目標の行数は正しく表示されます。（81-100等）
行数が足りないときは17-3と出るようです。
その後「エラー400」と出ます。
これ以上は当方にはお手上げ状態。

不思議なのは＃１で、再度試しましたが既報の通りやっぱりダミーシートでは行数が足りていれば期待通りに動くのですが、本チャンのシートだとなぜか動く気配なし。何度も同じコードをコピペして、上部の行数も合わせて確認したのですが違いが判らず。
とほほ・・・・

また、＃１はX軸に数字以外の列を指定すればちゃんとX軸に反映されますが、指定列が数字のみの場合はグラフ系列に取り込まれてしまって要素が3本のグラフができます。
X軸はデータ数になります。ただし、データ数が指定個数以下だと列の数字がX軸に反映されます。（当方には全く不可解ですが情報として）

これまでの経験からHohoPapaさんのご回答でこれほど思うようにいかないのは当方の表の問題だと思います。

そこでいったんこの質問を閉めて本チャンのダミーシートを添付して再度質問させていただくことにさせてください。
もしこれ以上のおんぶは負担が大きいようなら当然ですが無視して、次の機会?に体力温存でお願いします。

本当にいつも質問の不備と知識のなさでお手間をおかけし、重ね重ね申し訳ありません。

回答No.7

kon555

既に色々とマクロ側での対応と検討が進んでいるようなので蛇足でしょうが、まあ一応。

>>グラフの対象が常にB,C列に決まっていれば簡単ですが毎回違うので厄介です。
むしろこちら側をマクロ化してしまうのも一案ですね。
この場合、挙動がおかしい場合も検討は簡単なので、データの信頼性についても懸念は生じません。

データ解析、傾向判断を実務として行う場合、むしろ『データの妥当性チェックは日常的に必要』『ありえないようなデータが出た時こそ本番』だという事は(当然既にご存知でしょうが)留意しておいて下さい。
完全にマクロ化してしまう場合、それをどのように検討・検証するかも必要です。

私も以前これで痛い目を見ましたので(データ整理のやり直しで済んだだけマシでしたが)、余計なお世話とは思いますがお伝えしておきます。

お礼

ご回答ありがとうございます。
入力した値の異常は入力表の書式設定で、直近30個のデータの最小、最大、3シグマ外れ、5連続、等々赤字、青字、斜体等々の組み合わせでアラームが出るようにしてあります。
今回は個々データの異常検出ではなく、データの動きの「見える化」が目的です。

回答No.6

HohoPapa

>取り合えず改良版の試行結果をお待ちくださいませ
改良版も改めて確認すると、
恥ずかしながら、行範囲を求めるロジック
（とりわけ、範囲の開始行を求めるロジック）がいい加減です。
デタラメと呼ばれても仕方ないレベルです。<m(__)m>

そこで試行結果を待ち、それを受け、
>5行目が項目名です
を考慮して再ポストします。

補足

毎度毎度ですみません。
早急に試してみたいのですが、明日から孫のところへ行くので月曜日以降になってしまいます。
勝手なことばかりで恐縮です。
最初のご回答でも列の指定さえできれば使えそうな感じなので誠に申し訳ありませんが今少しご協力、というよりおんぶと抱っこをお願い致します。

最初の30個（4６行目）までは手動で範囲指定しておいて、46行目以降はデータ入力後にマクロボタンでグラフ範囲を更新していく、ことでも十分と思っています。
複数の人が入力する表ですがルール化すればワンクリックは実行できる状況です。

最初の質問時に、データは17行目から、データ1つから始まること、各列の1行目から16行目に項目名を含む統計データ等があり、列によっては文字が入った列もあること、空白セルもあること、等を説明しなかったために上に突き抜ける結果になってしまったこと、申し訳ありませんでした。

横軸のラベルは不要ですが、手間が変わらないなら、Ｂ列のＬＯＴ Ｎｏを横軸にしたいです。（単に30－50個のデータの推移を見える化したいだけですので）

やりたいことの説明（だけ）が先行してしまうのですみませんでした。

回答No.5

imogasi

#2です。
補足について、小生の意見。
小生のコードは、Sheet1にデータがあると仮定しており、行の下にデータを追加した場合、日付的に今までより、最近のもの＝「日付シリアル値が大」のデータが、行的に追加されていくと思いますので、, Criteria1:=16 のところを, Criteria1:=30 _　にすればよいように思った。日付シリアル値的にトップ３０を
抜き出して、Sheet2へセットされるーー＞このデータでグラフ化。
回答のVBAを、テストデータで、テストしていただいて後に、補足意見を書いてないようだ。VBAも質問者には、敷居が高いのかも。
　それであれば、＃２を無視していただいて結構。
この質問は、質問者のレベルを超えた課題だろうと思う。（ぴったりの、エクセル操作はないと思う。）

お礼

ご回答ありがとうございました。

回答No.4

HohoPapa

データ格納列番号もブックによって変動するようなので、
この番号も定数に格納してみました。

Sub GraphSauceChange1()

　Const MaxRows = 50　 'データ範囲に指定する最大行数
　Const TgShNum = 1　　'対象シート番号
　Const ColNum0 = 1　　'日付列？　1列目
　Const ColNum1 = 2　　'1つ目データ格納列
　Const ColNum2 = 4　　'2つ目データ格納列
　Const SRowNum = 2　　'データ開始行番号

　Dim SRow As Long　　 'グラフ用データ開始行
　Dim ERow As Long　　 'グラフ用データ終了行
　Dim tgRange0 As Range　'X軸日付範囲
　Dim tgRange1 As Range　'データ群1つ目範囲
　Dim tgRange2 As Range　'データ群2つ目範囲
　Dim tgRangeA As Range　'上記合計範囲

　With ThisWorkbook.Sheets(TgShNum)

　　ERow = .Cells(.Rows.Count, 1).End(xlUp).Row
　　If ERow < MaxRows Then
　　　SRow = SRowNum
　　Else
　　　SRow = ERow - MaxRows
　　End If

　　Set tgRange0 = _
　　　Range(.Cells(SRow, ColNum0), .Cells(ERow, ColNum0))
　　Set tgRange1 = _
　　　Range(.Cells(SRow, ColNum1), .Cells(ERow, ColNum1))
　　Set tgRange2 = _
　　　Range(.Cells(SRow, ColNum2), .Cells(ERow, ColNum2))
　　Set tgRangeA = Union(tgRange0, tgRange1, tgRange2)　　　'結合
　　.ChartObjects(1).Chart.SetSourceData Source:=tgRangeA　 'セット

　　.ChartObjects(1).Chart.SeriesCollection(1).Name = "あいう"
　　.ChartObjects(1).Chart.SeriesCollection(2).Name = "かきく"

　End With

End Sub

お礼

上の補足コメントで「完璧です」と思ったのですが、今日本チャンのブックで試してみたらなぜか全く予想外の結果になってしまいまし他。（ショック!!）
まず横軸の指定がグラフ要素になって、しかもデータ範囲が41行目から79行目に固定されてしまうようです。
データ数を20で試してみました。

なぜ自宅のダミーではうまく行ったのか全く不明ですが、HohoPapaさんが自ら
＞範囲行の設定に難あり、と言われているのでこちら（不具合）が正解かと思い急ぎ訂正報告をさせていただきます。

補足

すぐに動くわけないと思いながらもダミーシートで試してみました。

横軸を2列目に、開始行を17行から、データ数を20個にして、グラフ系列の列も変えてみました。
完璧です！！！（プロットが21個になるのはご愛敬で全く問題なし）

ただし、大きな問題点が1つ。
特性のうち重要な2つだけを指定してグラフに出来るのですが、この特性の桁数が全く違う場合がほとんどなので特性ごとに2軸表示が必須です。
例えば選択した特性が、長さと重さの場合は、0.1ｍｍと1000kgという感じです。
これがグラフ系列を２つだけに絞る最大の要因です。（軸が1つだと0.1は変動が見えなくなりますので）

また、若干の問題点は、
データ数が足りないと上に突き抜けて、その系列の上の平均や標準偏差、等々がグラフに取り込まれるのでぐちゃぐちゃになってしまうのでできれば改善したい問題点です。
ただし、最初のグラフ作成時に30個までを指定しておけば、30個目以降のデータを入力したらマクロボタンをクリックすることで対応可能なので大きな問題点ではありません。（これは大きく手間なら割愛でも十分）

図々しく贅沢を言うなら、
指定した列（ひな形では5行目の項目名）が自動で凡例に取り込まれればありがたいのですが。。。

回答No.3

HohoPapa

グラフのデータ範囲などをVBAでコントロールする説明が
http://officetanaka.net/excel/vba/tips/tips150.htm
に細かに説明されているものの
そもそもオプションがたくさんあるため難解です。

VBAでデータ範囲を制御したいということであれば、
添付画像と添付のコードを参考に挑戦してみてください。

Sub GraphSauceChange1()

　Const MaxRows = 50　 'データ範囲に指定する最大行数
　Const TgShNum = 1　　'対象シート番号

　Dim SRow As Long
　Dim ERow As Long
　Dim tgRange0 As Range　'X軸日付範囲
　Dim tgRange1 As Range　'データ群1つ目範囲
　Dim tgRange2 As Range　'データ群2つ目範囲
　Dim tgRangeA As Range　'上記合計範囲

　With ThisWorkbook.Sheets(TgShNum)

　　ERow = .Cells(.Rows.Count, 1).End(xlUp).Row
　　If ERow < MaxRows Then
　　　SRow = 2
　　Else
　　　SRow = ERow - MaxRows
　　End If

　　Set tgRange0 = Range(.Cells(SRow, 1), .Cells(ERow, 1))　'列1
　　Set tgRange1 = Range(.Cells(SRow, 2), .Cells(ERow, 2))　'列2
　　Set tgRange2 = Range(.Cells(SRow, 4), .Cells(ERow, 4))　'列4
　　Set tgRangeA = Union(tgRange0, tgRange1, tgRange2)　　　'結合
　　.ChartObjects(1).Chart.SetSourceData Source:=tgRangeA　 'セット

　　.ChartObjects(1).Chart.SeriesCollection(1).Name = "あいう"
　　.ChartObjects(1).Chart.SeriesCollection(2).Name = "かきく"

　End With

End Sub

補足

いつも大変お世話になっております。

昼休み明けからズ～とアレコレ試している間に改善版が出てしまいました。
今日はこれからは時間がないのでまずは＃3の試行結果のみ速報?させていただきます。

最初の疑問は、回答のグラフではどう見てもデータ数が50個ではない（11/16からなら9/28まで）なのでおかしいとは思いながら、知恵がないのでまずは言われた通りにコピペして少しアレコレいじったら見事データ数50個（正確には51個）のグラフができました。
そこでデータ数を４９個にしてみましたがやはり51個のまま。この個数はどうでもいいのですが、試しに40個にしてみたり、列の数字をいじってみたりしていたらアプリケーションエラーとなりました。
データ数を5０に戻してもエラーになってしまうようになった時点で一旦終了して、ここで列番号の指定の仕方を聞こうと思ったら改良版が入っていました。

ちなみにまたまた後追いの条件ですが、データが50個以上ある場合は問題なく増加分が下にずれるのですが、データが50個以下の場合は上に数字を含む種々の情報があるので突き抜けてしまって不具合が起こることに気づきました。

実際の使用には新製品は「ひな型」に取り込んで使用することにしているので、対象列の１７行目以降がデータ領域になります。
ついでに5行目が項目名です。（厚かましくてごめんなさい）
既存のファイルなら上に空白行を入れて17行目からに更新しても問題ないので、もし合わせて改良いただけたらと思いますが、取り合えず改良版の試行結果をお待ちくださいませ。
まずは試してみます。

明日以降に再度挑戦してみます。
一応できたのでゴールは近い、と感じていますが小生のことですので何が飛び出すか????

回答No.2

imogasi

エクセルのグラフの機能の中に、
データ数について
・データ内容による制限（間引き）
・外形的な（データ数など）制限
のメニュー（指定）はなかったと思います。
一旦つくてしまってからグラフの要素を少なくする方法もない。（ただしその行を非表示にする、は減らすのに有効のようだ）
だから自力でその条件にあったデータを作って、そちらを対象にするほかないと思います。
　ただし
（１）データの抽出で、数を少なくする
（２）データの（普通は行の非表示）非表示にして、グラフの見た目を少なくする
があります。
これらを手動やVBAでやって、その後グラフ化すればよい。
＞最新の30個
VBAで考えても、日付の点で、最新のX個というロジックは簡単ではないと思う。
・日付順にデータがあり
・１日１データで
なら考えやすいが。
「日付」データは、セルの値は、日付シリアル値という整数値と考えても良いものなのだが、ソートして上からX個取る方法もあるが、
下記のようなのも（Operator:=xlTop10Items）使えるかも。
Top10というが、１０個でなくても、下記では１６個現れた。
Sub test03()
Worksheets("Sheet2").AutoFilterMode = False
With Sheets("Sheet2").Range("A1")
.AutoFilter Field:=1 _
, Criteria1:=16 _
, Operator:=xlTop10Items
.CurrentRegion.Copy Sheets("Sheet3").Range("A1")
End With
End Sub

補足

早々のご回答ありがとうございます。
当方の実力ではご回答の内容が十分に理解しきれていない可能性もありますが、やりたいことは列の下方向に追加されていくデータの下から30個のグラフを自動もしくはマクロボタンで更新されるようにしたいのですが。。。
列は任意の2列まで指定できるようにしたいのです。

ご回答で思いついたのですが、別シートに元シートの下から30個（列は３－１０列）をコピーするような式かマクロができれば解決できるのではと思いつきましたが、この方法も分かりません。

回答No.1

kon555

要望的にはマクロ等で対応してもいいでしょうが、ひとまずグラフのテンプレート登録を活用してはどうでしょうか。
https://www.becoolusers.com/excel/chart-template1.html

まずこの手順で希望のグラフをテンプレート化し、任意データを選択しグラフ作成→テンプレート活用でかなり省力化できると思いますが。
マクロ等で自動化するのもいいのですが、多分データの分析や傾向判断に活用されているのですよね？　そうした業務の場合、使用者が認識していない挙動を挟むとデータ自体の信頼性が低下してしまいますので、あまりオススメしません。
(普通だと考えにくいような傾向が出たときに、『データがおかしいのか？』という疑いが発生してしまう)

また、この辺りのトピックスが活用できるかもしれません。
http://officetanaka.net/excel/function/tips/tips71.htm

ご参考までに

お礼

早々のご回答ありがとうございます。
目的はご指摘の通り、品質特性の傾向管理（確認)です。

ただ、質問に抜けていましたが、対象のファイル（製品数）が200-300以上あるので、テンプレートでは製品ごとに毎回手間暇がかかる（項目数も単位もマチマチ）なので汎用の方法を質問させてもらいました。

また、最後にご提案のあった方法は現在使用している方法です。
これはグラフの対象が常にB,C列に決まっていれば簡単ですが毎回違うので厄介です。