STUDY387

エクセルで仕事をもっと簡単にしよう

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

time 2015/02/10

列挿入に合わせて数式の参照セルを希望通りに自動変更する方法(2)
※ エクセル2013で説明しています。

今回ご紹介するのは 「=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 ) )

※下の画像をクリックすると拡大します。
excel-offset-oyo02-01

長い式なので、一旦「OFFSET(AC3,0,-1)-OFFSET(AC3,0,CHOOSE関数)」とします。
これを分解すると「=A-B」のAが「OFFSET(AC3,0,-1)」でAB3セルを参照しています。
Bが「OFFSET(AC3,0,CHOOSE関数)」でW3セルを参照しています。
excel-offset-oyo02-02

A部分の「OFFSET(AC3,0,-1)」に関しては『列挿入に合わせて数式の参照セルを希望通りに自動変更する方法(1)』で説明しているので割愛しますね。

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

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

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

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

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

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

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

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

種類は省略可です。
省略の場合、日曜日なら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 ) ) 」になります。

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

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

sponsored link



sponsored link

sponsored link

何はともあれOFFICEのことならマイクロソフト。意外とキャンペーンやっています。

コピー用紙あったかな?トナーもあったかな?事務用品はアクスルへGO!