日々の為替レートの情報を外部サイトからExcelにインポートしました。
私は毎日、いくつかの数式で為替レートを使用しています。問題は、次の日になると為替レートが変わり、すべての数式が変更されてしまうことです。現在の日付に関連する行の数式のみを変更したいのです。昨日については、昨日の為替レートを維持したいと思います。
条件が満たされた場合に数式の更新を停止する方法はありますか? この場合の条件は、数式が関係する日付が TODAY() より小さい場合です。
答え1
はい、VBA や極端な手段を使わなくてもこの結果を達成できます。
しかし、あなたが考えているようなやり方ではありません。そのやり方は、あなたの考えが向かったところであり、特定のステップや条件に縛られているわけではないと私は確信しています。だから...
1 つの方法は、データが取得された日付を含むデータの列を追加するか、すでにある場合はそれを利用することです。それがあれば、2 つのことのいずれか、あるいはそれ以上のことを行うことができますが、ここでは 2 つのことに焦点を当てます。
1 つは、為替レートの値セルを使用するための簡単なIF()数式です。たとえば、日付列が列 A、計算列が列 B、セル $M$1 に毎日変化する為替レートがある場合は、次のようになります。
=IF(A1<TODAY(),B1,IF(A1>TODAY(),"",2000*$M$1))
列 A の日付では、作業数式は計算されます。しかし、後続の日付では計算されません。今日に一致する日付が列の下の方に行くほど、何も表示されていない新しいセル ( "") は正しく計算され、日付が進んでも再計算されないその上の他のセルと結合されます。
ただし、たとえば でセルに入力し、F2-EditEnter キーを押してそのセルの再計算を強制すると、そのセルは再計算されて失敗し、循環参照のために数式を計算できない場合に Excel が返す結果である「0」が表示されます。誤ってこれを実行した場合は、UNDO( Ctrl-Z) で元に戻すことができます。Enter キーを押す前に気付いた場合は、代わりに ESC キーを押すだけで保持されます。書式設定の変更によって問題が発生することはありません。
つまり、少し注意すれば、おそらく機能するでしょう。誰がそれを望むでしょうか? まあ、もっと良い方法を持たない人です。
2 番目のアプローチの方が優れています。列 1 に毎日の日付、列 2 に各日付の為替レートを含むテーブルを作成すれば、実行と管理が簡単になります。新しい為替レートを追加するたびに、テーブルは毎日大きくなります。または、列 1 に多くの将来の日付が既に入力されていて、新しい為替レートを毎日追加するだけで済むようにすることもできます。
次に、数式を使用して、列 1 の今日の日付 ( ) を選択して検索し、TODAY()列 2 の現在の為替レートを検索します。
簡単に移植でき、どこかに隠されたヘルパー ページに配置されます。そうすれば、数式に奇妙なトリックは必要ありません。検索のために日付列の日付を参照し、何年経ってもその日付の為替レートを常に見つけることができます。循環性に関する懸念はありません。
このテクニックはテーブルにも便利です。テーブルの利点の 1 つは、行が追加されるたびに数式が下に表示されることです。ただし、テーブルの弱点の 1 つは、行が追加されるたびに数式が下に表示されることです。
数式を変更する必要がある場合、現在の行では古い形式を維持しながら、データ行が追加されるにつれて新しい形式を下位で使用すると、問題が発生します。テーブルではそれができません。必要な数式をすべて変更しても、新しい数式では古い数式が引き続き表示されます。
はい、技術的には、この種の変更は可能ですが、説明されている方法はすべて説明どおりに機能するわけではありません。たとえば、4 行連続で変更すると、それが新しい数式になるはずです。私の経験では、6 行未満を変更してもそれが機能することは一度も見たことがなく、Excel が諦めて新しい数式を使用するまでに 10 行から 30 行の変更が必要になることもよくあります。これらはすべて実行できますが、実際には機能しません。別の方法は、範囲に変更してからテーブルに戻すことです。これにより、ほとんどの場合は軽微ではありますが問題が発生しますが、数式の最初の行がマスター数式として扱われ、新しい数式と同じになる可能性は低いため...
しかし、多くの数式は、テーブル外での動作方法の変更を許可するように記述できます。たとえば、為替レートです。テーブルには、何らかの値、おそらく名前付き範囲、または数式のチャンクへの参照があります。その名前付き範囲またはチャンクは、変化する為替レートを保持するデータ範囲 (またはテーブル) を参照できます。したがって、新しいレートは、それを使用するテーブル数式を変更せずに使用できます。テーブル数式を変更する必要はありません。覚えておく必要があります。
しかし、これはまた、データ、特にデータの変更やデータの追加を予測して設定することで、さまざまな部分の変更や使用が容易になる頻度を示しています。