Exccelで、データを更新直前（1世代前）の数値データを記憶・取り出す方法

Exccelですが、セルのデータを更新しますが、その直前（1世代前）の数値データを記憶・取り出す方法がありますか、あればご教示ください。

具体的には、MAX関数で最大値を表示させていますが、データを更新しますと、当然に、MAX関数は、新しい最大値を表示します。
この時に、MAX関数以外のどこかのセルに、データ更新の直前（１世代前）の数値を格納して、表示させたいのですが、その方法がありますか？
よろしくお願いします。

質問者からの補足コメント

• たびたびありがとうございます。
  ご指示通りにやったつもりですが、下記画像のようになり、ご回答のような結果になりませんでした。
  「ひとつ前」B３は、B2、B4にリンクさせても、上記のようになりません。
  どこが間違っているのでしょうか？すみませんが、再度お願いします。

  
  No.8の回答に寄せられた補足コメントです。 補足日時：2017/03/20 12:24

• yokoyama様
  
  丁寧なご回答に恐縮に存じます。何がしたいか、Excelに書いてみました。
  この画像をご覧いただければ、何をしようとしているか、ご理解いただけると思いますが、A列に、毎月処理する連続番号を入れますが、新しい月の開始番号は、最終番号に１を加えて、A1から上書きして、その後必要な行数だけ番号付与するものです。
  
  画像の例では、前月の最終番号（最大値）が１４で、次月の開始番号は、１５となります。この月の必要な数は１０となっています。
  
  もちろん、前月の最終数字１４を覚えておいて、１５と手入力すれば問題ないのですが、記憶でなく、本例では、B１に表示できないか、と考えた次第です。
  
  A列に、その月の必要番号数を入れたなら、その後は、Vlookupなどを使って、他のデータを使って、自動処理となっています。
  
  なお、最初の行は、「制御領域」（印刷しない）で、見出しなどは2行目です。

  
  No.11の回答に寄せられた補足コメントです。 補足日時：2017/03/20 18:41

No.16 回答者： WindFaller 回答日時： 2017/03/21 12:12

こんにちは。

>補足日時：2017/03/20 18:41　から

なるほど、これでは、ぜんぜん、話が明後日になってしまっていますね。
このご質問の内容と補足からする、まったく別物です。冷静に岡目八目でみていると、ご質問者さんの要求するところは理解できました。

正直なところをいうと、これを自動的なマクロ（イベント型）にはするのは、辞めたほうがよいです。ご質問者さんは、そんなことは一言も言ってはいないような気もしますし、回答者側が、早合点をしたものだと思いますが。
VBAをたしなむものにとって、イベント型のその内容というのは、とても興味がある題材だからですが。

世代管理そのものは、私が触れたように、時間的な変化であって、決して、計算上(OnCalculate)ではありません。そんなマクロを必要としているとは思われません。

フォームのボタンにつけてください。
A列目のデータを消す時はに、そのコメントブロック('') を外してください。

'//標準モジュール（名称に決まりはありません）
Sub Button1_Click()
　Dim TargetCell As Range 'Maxを取るセル
　Dim RepositCell As Range '保存用のセル
　Set TargetCell = Range("C1") '----数式のあるセル・ご自分で設定してください。
　Set RepositCelll = Range("B1") '----定数・
　If TargetCell.HasFormula = False Then MsgBox "数式のあるセルを設定してください。", vbExclamation: Exit Sub
　
　If MsgBox("シートを更新します。よろしいですか？", vbOKCancel) = vbOK Then
　　Range("B1").Value = Range("C1").Value
　　''Range("A1", Cells(Rows.Count, 1).End(xlUp)).ClearContents '*
　　Range("A1").Value = Range("B1").Value + 1
　End If
End Sub

この回答へのお礼

ご丁寧なご回答ありがとうございます。
当初の質問の意図が、Macroでなく、簡単なExcelの関数の組合せでできる方法がないか、という趣旨でしたが、VBAでプログラミングしないと出来そうでありませんので、諦めます。
手作業でコピペすれば対応できますので、問題ありません。
多大なお時間をおかけしましたが、誠にありがとうございました。

お礼日時：2017/03/23 21:24

No.15 回答者： yokomaya 回答日時： 2017/03/20 20:55

私が何を言っているか、肝心なことが言えてないので追加します。

私が提供したVBAのコードは
①MAX式の書かれたセル番地
②MAX式の中で探すセル領域
③一世代前のセル番地
上記三つが完全に合致しないと正しく動作しません。

つまり本番環境と、テスト環境が仮にあるとしても
上記三つは変化禁止なわけです。

で、あなたの説明を読むと、結局本番か、テストか
はっきりしない。
一番肝心なそこがないのがイライラします。

本番であることを望みたいが
画面からは一切本番感がつたわりません・・・。

この回答へのお礼

何回もご丁寧なご回答ありがとうございます。
当初の質問の意図が、Macroでなく、簡単なExcelの関数の組合せでできる方法がないか、という趣旨でしたが、VBAでプログラミングしないと出来そうでありませんので、諦めます。
手作業でコピペすれば対応できますので、問題ありません。
真面目にお付き合いいただき、多大なお時間をおかけしましたが、誠にありがとうございました。

お礼日時：2017/03/23 21:25

No.14 回答者： yokomaya 回答日時： 2017/03/20 20:37

いやいや、超肝心なことがまだあった。

＞なお、B2=max(A:A) 、1世代前の数値は、B１に格納したと思います。
＞（A:Aの列の最大値を、B2に格納し、1世代前の数値は、B１に格納したと思います）

前回のB2はどうして消えたのかご説明願います。

No.13 回答者： yokomaya 回答日時： 2017/03/20 20:32

>この画像をご覧いただければ、何をしようとしているか、ご理解いただけると思いますが

この画像が実験なら判りますよ。
実務でこれって有り得ます？

まずA列が何を入力する列なのか。全く何の説明も
画面にないですね。

＞毎月処理する連続番号を入れますが、

って何の番号か全く判らないし
仮にそれでも行頭に番号って入れておくのが当たり前です。
だって、他の人に見てもらうとき説明すら困難じゃないですか。
事実、僕はこれで何をしたいのか、考える気力さえ萎えます。

C1の説明を下に書いてますけど
本来C1はC2に置いてC1に最大、等の
見出しを入れるのが普通です。

その当たり前がないのを、本当に実務でお使いでしたら
即刻、改善されることをお勧めしたいです。

＞画像の例では
これ、本当に混乱させてます。
例なんですか？
実務で使ってるものか、そうでないのか。
どっちでもとれる表現を、なぜぶち込んでくるんでしょう？

しかもMAX、（正直ちょっとめげてます。MAXには違いないけど）
連番ですよね。
A列の入力している一番下を拾ってくる
だけでよかったんですね。

Max(a1, a100)が間違いかどうか、何も
触れていないのはどういうおつもりでしょう？


＞なお、最初の行は、「制御領域」（印刷しない）で、見出しなどは2行目です。
何をおっしゃってるんですか？
この図のどこがそうなっているのか？
16が見出し？
これ理解できる方居ます？

た、助けて～。



ｐｓ　名前も誤記頂いてます。

No.12 回答者： WindFaller 回答日時： 2017/03/20 15:22

こんにちは。

私も考えてみましたが、Calculateイベントで、MAXの引数の変化するセルが、一回にひとつなら成功します。しかし、それが複数、同時に、言い換えれば、貼り付けした時には、イベントは、その想定される倍以上のイベントが走ってしまうので、作業前の値というのは、何十世代ま前のものになってしまいます。Calculateイベントにしろ、Changeイベントにしろ、1回の作業であっても、1回とは限らないのです。

もう少し、状況を説明していただければ助かるとは思いますが、イベントの発生する直前に保護し、イベント発生後は、封印する仕組みでないと、いけないはずです。言っていることはナンセンスですが、それを可能にするものは、タイムイベント、Excelでは、組み込みコマンドでは、OnTimeメソッドだけだったような気がするのです。非常に細かい周期で走らせます。OnTimeメソッドは、ワークシート計算中は待機状態です。（ただし、これは、複数のセルへの貼付けなどの場合です）

後は、画像のレイアウトが固定したものとして、また、数値をどのようにして入れるかによっても変わってきます。

今までの経過をみながら、私も見当させていただきます。

この回答へのお礼

WindFaller様

いろいろご検討いただきありがとうございます。
No.１１様へ補足コメントしましたので、ご覧いただければ、私の意図がご理解いただけると思います。

ご説明では、世代が何世代にもなってしまうということかと理解しましたが、イベント発生後は封印が必要など、あまり難しいようでしたら、諦めます。

お礼日時：2017/03/20 20:22

No.11 回答者： yokomaya 回答日時： 2017/03/20 14:43

もう一つ疑問があります。

このニーズ、つまり一世代前の数字がほしいというのが
こんなテスト環境のようなＡ１，Ａ100などという
状況で生まれるものでしょうか？

Ａ１に数字を入力するって普通の帳票やデータでは
考えられないですよね。
しかもそのＭＡＸがＢ１です。
項目名や何らかの補足が入る余地が全くない。

これは本番環境とは別にテスト環境を準備したことを
示しているのではないかという事を危惧しています。

もしそうなら本番環境を別名保存すれば
幾つでも本番と同じテスト環境は作れます。

セル番地が肝になる処理だけに、このセルでない
本番環境がもしあるなら、開示お願いします。

No.10 回答者： yokomaya 回答日時： 2017/03/20 13:38

前回のを消して新たに貼っていただくのは

下記になりますが。
No6の説明、こうは理解戴けなかったのですね。
まだまだ精進が足りずすみません。

ただ疑問があります。
なぜa1とa100の大きい方となっているのでしょう？
比較するセルをそんなに離す事情がおありかもしれませんが、
念のためA1からA100までの連続の中で最大であれば
A1:A100としなければなりません。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim befor
If Not Intersect(Target, Range("a1,a100")) Is Nothing Then
Application.EnableEvents = False
Application.Undo
befor = Range("B2").Value
Application.Undo
Range("B1") = befor
Application.EnableEvents = True
End If
End Sub

No.9 回答者： ママチャリ 回答日時： 2017/03/20 12:45

No8です。

「No.8の回答に寄せられた補足コメント」を読みました。
わたしとしては、数式での実装をご提案したつもりは無いのですが 、どうして数式になってしまったのでしょうか？
yokomayaさんの回答でVBAを使っていたので、VBAも理解されているものと判断していたのですが、VBAがわからないのであれば、わたしの回答は無視してください。

この回答へのお礼

No.9　ママチャリ様、
折角ご丁寧にご回答いただき、ありがとうございます。
残念ながら、VBAのロジックは凡そ理解できますが、このプログラムをVBAに装填・設定する方法がわかりません。
もしそのアドヴァイスをいただけると助かりますが、如何でしょうか？

お礼日時：2017/03/20 18:15

No.8 回答者： ママチャリ 回答日時： 2017/03/20 11:28

Worksheet_Calculateイベントプロシジャに下記のマクロを書いてみて下さい。

ちなみにシートレイアウトは添付画像を想定しています。
Worksheet_Calculateイベントプロシジャは、再計算が行われる都度、実行されます。その中で次の処理を行っています。

【仕様】
MAX関数の結果が「作業セル」と異なる場合、「作業セル」→「ひとつ前」に設定した後、「MAX」を「作業セル」に保存します。

【マクロ】
Private Sub Worksheet_Calculate()
If Range("B2") <> Range("B4") Then
Range("B3") = Range("B4")
Range("B4") = Range("B2")
End If
End Sub

No.7 回答者： 銀鱗 回答日時： 2017/03/19 22:55

自分なら、Ctrlキーを押しながらZキーを押して、新しい入力をキャンセルすることで以前の値を確認します。

必要ならその値をコピーしてどこかに「値として貼り付け」する。

でなければシートの作りで、入力方法を時系列的に行うようにし、全ての入力をシート上に残す。
そうすれば、いつの時点でMAX値がいくつかを言う事は一目瞭然にできる。
てか、一つ前の結果を表示したいなら入力の履歴を明確にしておくものですよ(´・ω・`)

・・・
例：
A1セルからA100セルまで順に入力をする。

A1セルからA2セルまでの値でMAX値は
　＝MAX(A1:A2)
この数式をB2セルに入力する。
A1セルからA3セルまでの値でMAX値は
　＝MAX(A1:A3)
この数式をB3セルに入力する。
…数式の入力が面倒なので、B1セルに
　＝IF(A1="","",MAX(A$1:A1))
と入力して、B100セルまで複製する。

A列に入力されたセルの数を数えて、INDEX関数で最新のMAX値と一つ前のMAX値を表示。
C1セルに
　=INDEX(B1:B100,COUNT(A1:A100))
C2セルに
　=INDEX(B1:B100,COUNT(A1:A100)-1)
これでC1セルに最新の、C2セルにその前の最大値を表示することができる。