列挿入に合わせて数式の参照セルを希望通りに自動変更する方法(2)
今回ご紹介するのは 「=A-B」のBの値を1週間ごとに自動変更する方法です。
株などは「今週月曜の値-前週金曜の値」、
「今週火曜の値-前週金曜の値」というふうに、
前週末との差を注視しするようです。
この場合、1週間ごとに前週金曜日の値を自動で変更しないと、
手で修正しなければなりません。
このわずらわしさを解消する数式です!
それでは先に数式をご紹介します。
「=OFFSET (AC3, 0, -1) – OFFSET (AC3 ,0 , CHOOSE (WEEKDAY (OFFSET (AC$1,
0, -1)) ,0, -2, -3, -4, -5, -6, 0 ) ) 」
※下の画像をクリックすると拡大します。
長い式なので、一旦「OFFSET(AC3,0,-1)-OFFSET(AC3,0,CHOOSE関数)」とします。
これを分解すると「=A-B」のAが「OFFSET(AC3,0,-1)」でAB3セルを参照しています。
Bが「OFFSET(AC3,0,CHOOSE関数)」でW3セルを参照しています。
A部分の「OFFSET(AC3,0,-1)」に関しては『列挿入に合わせて数式の参照セルを希望通りに自動変更する方法(1)』で説明しているので割愛しますね。
B部分の「OFFSET(AC3,0,CHOOSE関数)」ですが、
CHOOSE関数を使っている理由は、曜日に合わせて数値を自動変更するためです。
たとえば、月曜日なら「OFFSET(AC3,0,-2)」、
火曜日なら「OFFSET(AC3,0,-3)」、
水曜日なら「OFFSET(AC3,0,-4)」でいう具合です。
曜日にあわせて数値を変更することで正しい計算ができます。
CHOOSE関数は「CHOOSE(インデックス,数値1,数値2・・・)」という構造で、
インデックスに対応して数値1や数値2を返します。
インデックスに曜日を指定すれば、月曜日なら-2、火曜日なら-3、水曜日なら-4と
自動で数値を変更することが可能になります。
CHOOSE関数の記事はこちら
【エクセル関数】リストの中から指定した順番に位置する値を返すCHOOSE
数式にすると「=CHOOSE(曜日の関数,0,-2,-3,-4,-5,-6,0)」です。
0が2個ありますが、これは日曜日と土曜日の場合です。
今回の例が為替で土日不要のため、このように設定しました。
こうすると仮に土日があったとしても、
「正しく計算されませんよ。」と警告が出ます(この例の場合ですが)。
あとは「曜日の関数」のWEEKDAY関数を設定すればよいだけです。
WEEKDAY関数の構造は「=WEEKDAY(シリアル値,種類)」です。
WEEKDAY関数の記事はこちら
【エクセル関数】日付の曜日を調べるWEEKDAY
シリアル値に日付の入ったセル番地を指定します。
ここではセル番地が列挿入で毎日変更するので、
OFFSET関数を使って「OFFSET(AC$1,0,-1)」と指定しています。
これで常に「前週差」と入力されたセルの左隣セルを参照します。
種類は省略可です。
省略の場合、日曜日なら1、月曜日なら2、火曜日なら3・・・を返します。
結果、WEEKDAY関数を数式にすると、「WEEKDAY(OFFSET(AC$1,0,-1))」となります。
そして今まで説明した数式をつなげると、最初にご紹介した
「=OFFSET (AC3, 0, -1) – OFFSET (AC3 ,0 ,CHOOSE (WEEKDAY (OFFSET (AC$1, 0, -1)) ,0, -2, -3, -4, -5, -6, 0 ) ) 」になります。
説明を構造で表すとこんな感じになります。
※下の画像をクリックすると拡大します。
長い説明で読むのが大変だったと思います。
お役に立てれば幸いです。ご活用ください♪