列挿入に合わせて数式の参照セルを希望通りに自動変更する方法（２）

2015-02-102015-02-12

今回ご紹介するのは 「＝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)」に関しては『列挿入に合わせて数式の参照セルを希望通りに自動変更する方法（１）』で説明しているので割愛しますね。

B部分の「OFFSET(AC3,0,CHOOSE関数)」ですが、
CHOOSE関数を使っている理由は、曜日に合わせて数値を自動変更するためです。

たとえば、月曜日なら「OFFSET(AC3,0,-2)」、

火曜日なら「OFFSET(AC3,0,-3)」、

水曜日なら「OFFSET(AC3,0,-4)」でいう具合です。
曜日にあわせて数値を変更することで正しい計算ができます。

CHOOSE関数は「CHOOSE（インデックス，数値１,数値２・・・）」という構造で、
インデックスに対応して数値1や数値2を返します。
インデックスに曜日を指定すれば、月曜日なら-2、火曜日なら-3、水曜日なら-4と
自動で数値を変更することが可能になります。

数式にすると「=CHOOSE（曜日の関数,0,-2,-3,-4,-5,-6,0）」です。
0が2個ありますが、これは日曜日と土曜日の場合です。
今回の例が為替で土日不要のため、このように設定しました。
こうすると仮に土日があったとしても、
「正しく計算されませんよ。」と警告が出ます（この例の場合ですが）。

あとは「曜日の関数」のWEEKDAY関数を設定すればよいだけです。
WEEKDAY関数の構造は「＝WEEKDAY（シリアル値，種類）」です。

シリアル値に日付の入ったセル番地を指定します。
ここではセル番地が列挿入で毎日変更するので、
OFFSET関数を使って「OFFSET(AC$1,0,-1)」と指定しています。
これで常に「前週差」と入力されたセルの左隣セルを参照します。

種類は省略可です。
省略の場合、日曜日なら１、月曜日なら２、火曜日なら３・・・を返します。

結果、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 ) ) 」になります。

説明を構造で表すとこんな感じになります。
※下の画像をクリックすると拡大します。

長い説明で読むのが大変だったと思います。
お役に立てれば幸いです。ご活用ください♪