日付が新しい数値を参照する。

質問

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

さて質問ですが、下に例を作成してみました。

現在は、B1に下のような式が入っています。
=IF(A1>0,Ａ1*108,0)
A1に数値を入れB1に答えが出るといった感じ、これが365日入力しています
　　　A　　|　B　　|
-------------------
　　　3　　|　318　|

式の中にある108という数値は「ふるい」の係数で、ふるいには一体型と分離型があり一体型の場合は108、分離型の場合は99と係数があります。
ふるいをして残った量（セルＡ１)に、一体型を使用した場合は108、分離型を使用した場合は99という風に計算しています。
そして、この係数は2週間に一度、校正をし厚生値が変わる度打ち変えています。

で、質問は
１）別のシートに校正値というシートを作り、そこに日付、一体型、分離型という見出しを作り校正する度に入力すればB1の数値が日付の新しいものを参照する。
　　　日付　　|　一体型｜分離型|
------------------------------
1｜2007/08/01　|　106|　　　　　｜
2｜2007/08/01　|　　　｜　　96　|

例えば、こういった感じで
　　A　　|　B　|　C　｜
------------------------
1| 分離型｜　3　|　318|

Ａ１は入力規則で選ぶようにして、Ｂ１に数値を入力するとＡ１を参照し、別シートの校正値から日付の新しい分離型を選び計算する。

２）B1の数値は校正値が新しいものを参照するが、一度計算されたものは校正値が新しくなっても書き換えられない。

こういうものは作成可能ですか？

わかりにくい質問文ですが、ご教授願います。

回答No.2

kimino_naha

一度計算されたものは校正値が新しくなっても書き換えられない。
がネックになります。再計算するものと再計算しないものが同居するのですから。

日付の新しいものを参照する、に関しては
MAX関数で日付の大きなものを探し
MATCH関数で大きな日付が表の何行目か探し
行と列をINDEX関数で値を取り出す
ということでいけると思います。校正値シート10行目までデータがあるとして
B列を求める場合
=INDEX(A1:C10,MATCH(MAX(A1:A10),A1:A10,0),2)
C列を求める場合
=INDEX(A1:C10,MATCH(MAX(A1:A10),A1:A10,0),3)
でいかがでしょうか。
同じ日付が複数あると求める結果と異なります、時刻も入力される等で対応してください。

一度計算されたものは校正値が新しくなっても書き換えられない。
のは計算式ではなく[値]ですので、コピー貼り付けで計算式を値にしてもらうしか浮かびません。
マクロで組むことは可能です。

回答No.1

pc_knight

「一度計算されたものは校正値が新しくなっても書き換えられない。」というのでしたらＶＢＡしかないと思います。
Ａ列とＢ列にデータが入力された直後に次の（１）～（３）の一連の処理をＶＢＡ（末尾に記載）に自動的にさせれば可能です。但し、データ入力シートの全セルを選択し、書式（Ｏ）→セル（Ｅ）→保護→ロック（Ｌ）のチェックを予め一度だけ外しておく必要があります。
☆ＶＢＡに行わせる一連の処理
（１）Ｃ列に計算結果値を代入する
（２）計算値をセットされたＣ列のセルに対し書式（Ｏ）→セル（Ｅ）→保護→ロック（Ｌ）を掛ける
（３） ツール（Ｔ）→保護（Ｐ）→シートの保護（Ｐ）
※ainouracho様、（１）にてＣ列に計算値をセットした際Ｄ列かどこかに、その時の係数値の書き込みの必要はありませんか。

☆ＶＢＡのコピペ
ＶＢＡを、シート名タグを右クリック→コードの表示（Ｖ）で表示されるコードエリアに貼り付けます。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim kata As String
ActiveSheet.Protect Contents:=False
clm = Target.Column
Select Case clm
Case 1 'Ｂ列、Ａ列の順に入力された場合
kata = Target
If Target.Offset(0, 1) <> "" Then
Select Case kata
Case "一体型"
Target.Offset(0, 2).Value = Worksheets("校正値").Range("B2") * Target.Offset(0, 1)
Case "分離型"
Target.Offset(0, 2).Value = Worksheets("校正値").Range("C3") * Target.Offset(0, 1)
End Select
Target.Offset(0, 2).Locked = True
ActiveSheet.Protect Contents:=True
End If
Case 2 'Ａ列、Ｂ列の順に入力された場合
kata = Target.Offset(0, -1)
Select Case kata
Case "一体型"
Target.Offset(0, 1).Value = Worksheets("校正値").Range("B2") * Target
Case "分離型"
Target.Offset(0, 1).Value = Worksheets("校正値").Range("C3") * Target
End Select
Target.Offset(0, 1).Locked = True
ActiveSheet.Protect Contents:=True
End Select
End Sub