4/24 新潟市東区 新潟市園芸センター
Canon EOS Kiss X4 on ML／SIGMA 10-20mm F3.5 EX DC HSM
HDR JPG×9→Photomatix／TpzAdjust／Photoshop
Tags #桜

＜実績値と予測値の共存（Excel）＞

例えば資金繰り表などで、実績値と予測値を共存させたいときってありますよね？　先月までの数字は確定した値で、今月から期末までは予測値を入れたい（それも過去実績に応じた平均値で）、という場面です。これ、毎月手動で編集するのが面倒だし、再計算しないと予測値が実態とかけ離れていき、資料の価値が雲ってしまします。

言葉ではなかなか説明し難いので、例をあげます。下記のようなよくある表をイメージしてください。


ここで、B列〜D列は売上の実績値です。青くなっているE列以降は実績の平均値になります。これを自動的に計算させることを考えてみました。

B1〜M1 ※その月の1日の日付を入れ、セル表示書式で「m"月"」を設定
E2〜M2 =$O2
O1 =DATEDIF(B1,TODAY(),"M")  ※更にセル表示書式で「0"ヶ月平均"」を設定
O2 =SUM(INDIRECT("B"&ROW()&":"&CHAR(CODE("B")+O$1-1)&ROW()))/O$1

上記のように式を入力します。

一応解説すると、O1には現在日付と期首日付の差から経過月数を計算します。これにより、自動的に実績値が何ヶ月分入力されているかが分かります。

O2が肝ですが、O1で求めた月数分のセル範囲を求めます。INDIRECT関数は文字列からセル範囲を作る関数で、今までこんな関数があるのを知りませんでした。このセル範囲で合計を算出し平均値を求めています。（AVERAGE関数で求めてもいいと思います）

E2〜M2列は、O2に求めた実績分の平均値を単にコピーしています。売上確定分を足したり、固定入力したりしてもいいと思います。

なお、月替わり時には循環参照エラーになりますが、（当たり前ですが）実績値を入力すれば直ります。

売上実績だけの合計を求めたり、色々と応用が効きます。