Excelで最大値と最小値を記録したい

質問

Excelを使って仕事をしています。

データ通信で接続していて、たくさんのセルに温度・圧力・流量などのデータが刻々入ってきます。

このデータを１０分区切りで最小値、最大値を記録したのですがうまくゆきません。

現在は１０分区切りの瞬間の値だけを記録していますが、データの意味を最大限活用したくて最小値と最大値を記録したいのです。

どなたか知恵をお貸しください。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

こんな風なコードを参考にしてみたらいかがですか？

本来は、コマンドボタンひとつで、トグルでも可能ですが、タイマー・オンと、タイマー・オフを別々にしました。おそらく、データの通信で入ってくるときに、マクロが動いていることを考えると、TimeValue("00:00:10")　のような待機時間の設定が必要になります。

数式の部分は工夫してください。
-------------------------------------------------

'標準モジュール

Private i As Integer
Private timerFlg As Boolean
Private myTime As Date
Private rng As Range
Sub TimerOn()
　　'タイマーオン
　　If timerFlg = False Then
　　　　myTime = Now + TimeValue("00:00:30")
　　　　Application.OnTime myTime, "Main", TimeValue("00:00:10")
　　　　Beep　'音を出す
　　　　timerFlg = True
　　End If
End Sub
Sub TimerOFF()
　　'タイマーオフ
　　timerFlg = False
　　On Error Resume Next
　　Application.OnTime myTime, "Main", TimeValue("00:00:10"), False
　　If Err.Number = 0 Then
　　　　MsgBox Format$(myTime, "hh:mm") & " のタイマーはオフになりました。", 64
　　Else
　　　　MsgBox "タイマーの解除が失敗しました。", 48
　　End If
　　Err.Clear
　　On Error GoTo 0
　　i = 0
End Sub

Private Sub Main()
　Set rng = Range("A1:A10").Offset(i * 10)
　Range("B1").Offset(i, 1).FormulaLocal = "=Max(" & rng.Address & ")"
　Range("C1").Offset(i, 1).FormulaLocal = "=Min(" & rng.Address & ")"
　'数式を数値化させる
　' Range("B1:C1").Offset(i, 1).Value = Range("B1:C1").Offset(i, 1).Value
　Set rng = Nothing
　If timerFlg = True Then
　　myTime = Now + TimeValue("00:00:30")
　　Application.OnTime myTime, "Main", TimeValue("00:00:10")
　i = i + 1
　End If
End Sub

お礼

こんばんは
面白い考え方です。

参考になります、早速モジッてシステムにあうように組み直して実験してみたいと思います。

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

回答No.8

Wendy02

こんにちは。

>全く同じ　OnTime　を間違えて２回呼び出しました。

マクロを途中で止めたりすると、OnTime は、二重になります。だから、Off で一旦終わらせたほうが良いです。ただ、私の場合は、再帰ルーチンを使っていますから、ひとつの処理が終わると、次の処理が掛かるようになっていますから、仮に途中で止めても次の1回きりです。

Private timerFlg As Boolean
このブーリアン値を、True にすれば、タイマーのストッパーの働きをしますが、myTime にないものは、解除が効きません。

>３回連続で呼び出してみればどうなることやら。
>無駄なようなので実験はしていませんが・・・

こういうものを扱うマクロでは、一度は、経験します。分からなくなって、掲示板で質問する人もいます。

この種のものは、連続で設定させると、さっぱり先が見えないのです。リストとして出てくれば分かるのですが、そういう設定も面倒なのです。特に、今回は、10分ですから、そのまま、OnTime で数を重ねると相当先にまだ行きます。

だから、ひとつ済ませたら、次の設定するという、再帰ルーチンを使ったほうが安全だということと、時間設定は、モジュールレベルの変数として共有させないと、Offボタンの　OnTime のFalse で、抜け出せなくなります。

ただし、Excelを終わらせれば、設定は消えてしまいます。(残す方法も回答していますが）

似たような質問は多いのですが、今回は、比較的簡単な内容で、安堵しています。(10分間なら、うまくいかなかった、ということは、おそらくはないと思います。一番難しいのは、リアルタイムの処理です。）

回答No.7

Wendy02

#6の修正です。
もう、お分かりにはなっているとは思いますが、時間の単位が、めちゃくちゃになっているようです。

If timerFlg = True Then
　　myTime = Now + TimeValue("00:00:30")　'●
　　Application.OnTime myTime, "Main", TimeValue("00:00:10")
　i = i + 1
　End If

この myTime は、
myTime =Now + TimeValue("00:10:00")
で、10分になります。

これは、TimeValue("00:00:10")　待機時間で、データが入ってくるタイミングとOnTime がぶつかっても、10秒が占有されることはないとは思いますが、念のために、このぐらいの時間をとりました。

必要なら、ここも訂正してください。長く取ればよいわけでもありませんが、OnTime が外れるのも困りますからね。

補足

おはようございます

まだ気がつきませんでした。
コードはちらりと見たのですが、処理すべき他の仕事があって、今日の午後から取り組む予定でした。

システムでは時刻をいじくってタイミングなどを調整していますので、提供していただいたコードを読んでいけば気がついたかもしれません。
気がつかなくても　動かしならが　修正していきますので大丈夫です。

何しろ年寄りで（今６８歳）　頭が柔軟に動きにくくなってきていますので、苦労します。

最近　OnTime で不思議な現象を見ました。

全く同じ　OnTime　を間違えて２回呼び出しました。
しばらくして時刻が来て、同じ処理を立て続けに２回やってくれました。
何が起きたのかしばらく理由が判らなかったのですが、　３回連続で呼び出してみればどうなることやら。
無駄なようなので実験はしていませんが・・・

回答No.5

Wendy02

#2の回答者です。

こちらの補足要求には無視されたままで、私が書くのはとてもヘンなスタイルですが、

>このデータを１０分区切りで最小値、最大値を記録したのですがうまくゆきません。

データ入力に対するイベントは関係ないとすれば、とは関係なく、単に、10分ごとなら、OnTime を使って、そこに数式で、最大・最小を取れば良いのではありませんか？後は、どのようになっているのか、分かりませんが、一応のアドバイスはしておきます。

お礼

簡単なルーチンを作って実験してみました。
データ数を無視すればうまく動くようです。
ありがとうございました。

補足

こんにちは
回答ありがとうございます。

いまは１０分区切りで値を記録するのに　OnTimeメソッド　を使っています。

このメソッドでは単にルーチンを起動するだけです。　つまり

Call Application.OnTime(Sheets("制御データ").Cells(36,5),"main",,True)

としていて　制御データシートのセルに書き込んだ起動時刻を見てOnTimeが作動するようにしています。
起動時刻は　Mod関数で計算して次回の起動時刻を書き込んでいます。

とても有用なアドバイスありがとうございました。
もう一つのOnTimeルーチンを付け加えてそこで最大値・最小値を計算してみます。
なんだか先が見えてきました。

回答No.4

fumufumu_2006

別のブックのVBAで、要請があった時（何かボタンが押された時）目的のブックを読み取り専用で開き、集計して表示する。
という方法ではどうですか？

２つの問題があると思います。
1.データの取り込み途中でデータを参照したい。
2.データの10分間の期間内最大最小を表示したい。

1については、データの取り込みが終了してからではだめなんですよね？
データ読み込み中に参照するためには、（たぶん）読み取り専用で開けば可能じゃないかと思います。

2については、どの列に時刻があって、どの列がデータでその形式がわからないと、抽象的な説明になりますが、
>現在は１０分区切りの瞬間の値だけを記録していますが、
ということなら、期間内最大最小は、難しくはないと思います。
時間は無くてデータだけでも、10分ごとに読み取り専用で開けば、その時点までのデータが取得できます。
データ収集ブックの内容を変更するのは、その装置が他で開いていた場合を考慮している場合以外は、変更はしない方がいいと思います。

とりあえず、装置の稼働中に手動で読み取り専用で開いてみて、装置からの新規のデータの追加に支障がないかを確かめてください。
次に時刻とデータの列と、各々の形式と、どのように追加されるのかが具体的にわからないと、正解の回答は難しいと思います。

補足

こんにちは　いろいろご説明いただいてありがとうございます。

まず、１のデータの取り込み途中でのデータ参照はありません。

データの種類は９０種類ですが　全て　Single です。
その９０種のデータが勝手に変化しながら入ってきます。
時刻の信号もSingleで扱っています。
表示するときだけ見やすいように　yy/mm/dd-hh:mm:ss 様式にしています。

現在はそれを１行にずらりと並べて勝手に変化するにまかせておいて、時刻が来たときに１行送ります。
そこに行送りした時刻をとデータを送ったか送ってないかのフラグを一緒に記録しています。
データ要請のイベントがくるとフラグを見て送ってないデータをまとめて送り出します。
こうすると行送りの瞬間の値だけは確保できますが、期間中の最大値と最小値が記録できていないので、これを解決したかったのです。

＞時間は無くてデータだけでも、10分ごとに読み取り専用で開けば、その時点までのデータが取得できます。

これはどのような手段で解決できるものでしょうか。
期間の区切り時刻だけが必要なので最大値・最小値の発生時刻は問題にしていません。

よろしくお願いします。

回答No.3

imogasi

＞記録したのですがうまくゆきませんーー＞記録した「い」のですがうまくゆきません、の意味か。
記録はうまくいったが・・の意味か？
ーー
＃２でもおっしゃっていると思うが、イベントとしての介入度合はどうなっているのでしょうか。
毎レコードの到着の都度プログラム介入する仕組みはありますか。なければどうしようもない。質問のことを考えているということは
出来ると質問者は掴んでいるようにも見える。
ただし、いま良く見るとエクセルの話のようなので、セルのChangeイベントしかないと思うが、通信データではどうなるかな。
出来るなら、言わずもがなのことですが、最小値を保持する変数、最小値を保持する変数をもって、毎レコードに対し
最小値を保持する変数より小か？小なら置き換える。そしてExit。
でないなら
最大値を保持する変数より大か？大なら置き換える。
区切りの時刻がくれば両変数を送信。
＞っているのでＰＣはメンテナンス以外はさわれません
VBAのコードは入れたり触ったりできるのですか？

お礼

参考になるご意見ありがとうございました。
Changeイベントと　Wendy02さんからのご意見とをあわせて実験してみたらうまくいきました。

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

補足

こんにちは　詳しく説明していただいてありがとうございます。

まず　「記録した」→「記録したい」でした。

ＶＢＡは入れたり触ったりは自由にできます。

セルの数が　９０個あってそこに　外部から数値が入ってきます。
入ってくる数値は刻々変化しているのでＥｘｃｅｌまかせでピークホールドのような考え方で保持しておけないかと考えていました。

データを受信するたびにイベントは発生しますので、そのイベントの度にマクロ介入することも考えましたが、忙しすぎて処理が間に合わないのかなと思って実際にはテストしていませんでした。

これらの最大値、最小値、１０分区切りの現在値などから温度・圧力・流量の相関を取って、製品の品質とリアルタイム照合をするのが目的です。

イベントには全部時刻をヒモ付けすることは可能です。

少しマクロ介入を試してみたいと思います。
ありがとうございました。

回答No.2

Wendy02

こんばんは。

>よって、手動介入は不可能です。
マクロだとおっしゃりたいわけではありませんか？
ところが、一般のマクロでは、イベントが発生しませんので、うまくいきません。

今まで、同様の質問は数回、ここのカテゴリでも出ています。
POSの　1時間ごと、という人もいれば、3分という人もいます。ひどい例では、1分以内という人もいます。ただ、同様の質問はあるのですが、ただ、こちらが提示したものは、全部ダメ出しされてヘンだなって思い、そのデータが、OLEからだと分かったこともあります。

今回は、「データ通信で接続していて」とおっしゃっていただいていますが、おそらくは、DDE か、OLE でExcelに入ってくるのではないか、と思っています。(それは、こちらの想像だけです。）

>１０分ごとの区切りを記録して、EntireRow.Insert で改行して外部からの要請を待っています。
>いろいろ試して見たのですが循環参照みたいになって失敗の連続で困っていたところです。

今の段階では、まったく手がつけられないです。マクロを作っているならマクロで、具体的に、どのようにしているか、どのようにExcelに表示されるとか、そういう具体的な話をしていかないと、おそらくは、解決にまでは至らないような気がしています。

回答No.1

noname#79209

ご要望が、言葉だけの表現なので、読み違えているかも知れませんが。
１行目が表題だとして、10計測分の最大値を算出したいなら
A列のデータの最大値を求めたいなら、
=MAX(OFFSET($A2,0,0,10,1))
として、下へコピー。

ということでは、ないのかなぁ．．．

補足

早速のご回答ありがとうございます。
質問の方法が悪かったようですが、バッチ処理が出来なので困っていました。
リアルタイムで自動的に最大値と最小値を１０分ごとに記録して外部から要請があったときに送信したいのです。
装置は稼働時間中は連続的にデータを送ってくるので手動で介入している間にのデータが来ます。よって、手動介入は不可能です。

つまり、エクセルを装置のフロントエンドとして使用しているのですが、１０分ごとの区切りを記録して、EntireRow.Insert で改行して外部からの要請を待っています。
要請があったときにバッファとして溜め込んでいたデータを一気に送っていますがその中に最大値と最小値を含めたかったのです。

何しろフロントエンドで使っているのでＰＣはメンテナンス以外はさわれません。

いろいろ試して見たのですが循環参照みたいになって失敗の連続で困っていたところです。

よろしくおねがいします。