【EXCEL】一つのセル内で変動する数値の「今までの最大値、その日付」を残しておくには？

質問

EXCELにて、毎日価格を更新しています。
例えば、
6月6日 商品A 500円
6月7日 商品A 600円
6月8日 商品A 400円
6月9日 商品A 900円　だとして、

6月6日の時点で
セルC15に500円と記入。

6月7日の時点で
セルC15に600円と記入。

6月8日に
セルC15に400円と記入。

6月9日に
セルC15に900円と記入。
といった感じで、同じセル内で価格が変動します。

例えば、
セルD15に最高値、セルE15に日付を残すとして、
6月6日の更新時は、D15は500、E15は2004/6/6
6月7日の更新後は、D15は600、E15は2004/6/7
6月8日の更新後も、D15は600、E15は2004/6/7のまま
6月9日の更新後は、D15は900、E15は2004/6/9
と一番高かった値段と日付を残しておきたいのです。

このように毎日変動しているセルの最高値とその日付を自動的に残しておくことは可能でしょうか？
可能な場合、どのようにすればよいのでしょうか？

よろしくお願いします。

回答No.5

chupark · ベストアンサー

#2です。
#3さんのナイスフォロー＆改良をふまえて補足説明分のフォローをしてみました。

下の例では15行目から45行目までを
同じような処理の対象としてみました。

For i = 15 to 45　というところが範囲をさします。
ここをお好きな範囲に設定してみてください。

--以下マクロ部分--

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Dim i

For i = 15 To 45
Set rg = Range("C" & i)
If Target.Address = rg.Address Then
If Range("C" & i).Value > Range("D" & i).Value Then
Range("D" & i).Value = Range("C" & i).Value
Range("E" & i).Value = Format(Now(), "yyyy/mm/dd")
End If
End If
Next i

End Sub

こんな感じでいかがでしょうか？

お礼

なるほど、変数iを作るのですね！
大変参考になりました！

これで希望通りの動作ができました。
今までの作業が格段と楽になりました。

chuparkさん、ありがとうございいました！
回答してくださった皆さんも、ありがとうございました！
大変助かりました（＾＾

回答No.4

imogasi

Ｄ１セルに=IF(C1>D1,C1,D1)が入れられない（循環参照に
なってしまう）のでマクロのお世話にならないといけないでしょう。
（１）＃１のご回答のように操作者がマクロを実行する
　　　またはボタンをクリックして実行する仕掛けにす　　　　る。
（２）＃２のご回答のように、Ｃ１セルの値が変ると
　　　直ちにマクロを実行する。
がありますが
（２）の修正版で
（３）ブックを閉じる時に自動的に、マクロを実行する
なんてのもどうでしょうか。当作業中は１段前の状態を
見て作業する方が良い場合の方が多いのではないでしょうか（例えば前日までの最高値はいくらだったかなど）
これは原理的に良し悪しではなく、表を使う仕事の性格に関係します。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh1 As Worksheet
Set sh1 = Worksheets("sheet1")
If sh1.Range("C2") > sh1.Range("D2") Then
sh1.Range("D2") = sh1.Range("C2")
sh1.Range("E2") = sh1.Range("a2")
End If
End Sub
Ｖａｌｕｅは省けるので省きました。Ｓｈ１はシートが複数あるのが常態だと思い、限定をつけました。
日付はＡ列にあるものとし、その日付をＥ２にセットしました。
マクロの作成方法はツール－マクロ－ＶＢＥで出てくる画面の、左のフレームのＶＢＡＰｒｏｊｅｃｔのThisWorkbookをクリックし、右側のフレームの上部の、▼のあるボックスの左のボックスからWorkbook、
、▼のあるボックスの右のボックスからBeforeCloseをクリックして選び、上記の中身部分を貼りつけます。

お礼

回答ありがとうございます。
そうです！循環参照がネックになってました。

こちらの事を考えてマクロを作って頂いてありがとうございます。
仕事の性格上、日に数回更新することもあります。
その為、No.3さんのモノを使うことにしました。

No.3さんの改良版を使ってみて
ほぼ思い通りの機能です。
No.3さんに対して更に補足説明していますので
ご回答いただけたら幸いです。

回答No.3

MSZ006

＃２さんの改良版です。

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim rg As Range

　Set rg = Range("C15")
　　If Target.Address = rg.Address Then
　　　If Range("C15").Value > Range("D15").Value Then
　　　　Range("D15").Value = Range("C15").Value
　　　　Range("E15").Value = Format(Now(), "yyyy/mm/dd")
　　　End If
　　End If
End Sub

上記マクロをシート名のところを右クリックして「ソースの表示」で表示されるソース記述部分にコピペするとセルを更新したときに自動実行されると思います。

補足

回答ありがとうございます。
コピペで実行してみたらできました！
ほぼ、思い通りの動作です。

補足ですが、
商品が複数あります。
商品Bの価格はC16
商品Cの価格はC17
といった感じで３０商品（３０行）ほどあります。
そこで、
Set～End Ifまで更にコピペすればいいのかな？と思い
２行分コピペししまた。

コピペ後、15の部分をそれぞれ行に合わせて16,17に変えました。
↓これです。

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim rg As Range

　Set rg = Range("C15")
　　If Target.Address = rg.Address Then
　　　If Range("C15").Value > Range("D15").Value Then
　　　　Range("D15").Value = Range("C15").Value
　　　　Range("E15").Value = Format(Now(), "yyyy/mm/dd")
　　　End If
　　End If
Set rg = Range("C16")
　　If Target.Address = rg.Address Then
　　　If Range("C16").Value > Range("D16").Value Then
　　　　Range("D16").Value = Range("C16").Value
　　　　Range("E16").Value = Format(Now(), "yyyy/mm/dd")
　　　End If
　　End If
Set rg = Range("C17")
　　If Target.Address = rg.Address Then
　　　If Range("C17").Value > Range("D17").Value Then
　　　　Range("D17").Value = Range("C17").Value
　　　　Range("E17").Value = Format(Now(), "yyyy/mm/dd")
　　　End If
　　End If
End Sub
実行してみたらウマく行ったのですが
３０行全部に使うとしたら、３０行分コピペするしか方法はないのでしょうか？

それしか方法がないのであれば上記のようにコピペして使わせて頂きます。

回答No.2

chupark

数式のみで解決させようとすると、
　再計算を起こしてしまうために過去最高値を残しておくのが難しいと思います。
　それだとどうしても手動の作業をはさむ事になりそうな気がします。

　そこで思ったのですが、マクロを使う方法でも"アリ"でしょうか？
　それならば簡単なマクロでできるかと思います。

sub Test1()

if Range("C15").value > Range("D15").value then
Range("D15").value = Range("C15").value
Range("E15").value = Format(Now(),"yyyy/mm/dd")
end if

end sub

E15に入れる日付はどっかのセルからもってくるんでしょうか？
上のマクロではマクロを実行した現在の日付を取得するようにしてみました。

難点なのは、セルを更新しただけでは実行されず、マクロを自分で実行するなりして動作させる必要があることです。

お礼

やはり数式ではムリなのですね。
マクロ"アリ！"です。
マクロは今まで使ったことないですが（＾＾；

日付は、更新した日に直接入力していました。

マクロを実行した日を自動取得はかなり助かります！
早速マクロ使ってみました。
No.3さんの改良版を使ってみて
ほぼ思い通りの機能です。
No.3さんに対して補足説明しますので
ご回答いただけたら幸いです。

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

回答No.1

noname#16926

その金額と日付は別表でリスト化されていますか？

セルＣ１５に入力すれば、ほかにリンクして一覧に転記されているという事なのですが・・・

それならばそのリストの中で、ＭＡＸの数式を使い金額の最高値を表示することが出来ます。
その金額と同じ行にある日付をＥ１５に表示するという計算式で、解決すると思います。

リストが無い（毎回Ｃ１５に入力して履歴が残らない）ということでしたら、申し訳ありませんが私では分かりません。

お礼

No.2さんの回答で解決できそうです。
ありがとうございました。