不規則な基準日と毎日のデータを自動で比較計算する方法

2015-02-12

毎日のデータを会社固有の不規則な基準日のデータと照らし合わせて
比較計算することがあるかと思います。
たとえば、直近の最高売上高とか、リニューアル前の売上高との比較です。

この場合、年月日が不規則なので、
日付の関数等で一定の期間を前提に計算式を作ることが出来ません。
しかし、HLOOKUP関数を使うと不規則な年月日でもスムーズに自動計算できます。

数式をご紹介します。
「=OFFSET(AC4,0,-1)-HLOOKUP("●",$3:$11,ROW()-2,0)」
ちなみに、これをセル番地で書き直すと「＝AB4－X4」です。
※画像をクリックすると拡大します。

上の表をご覧ください。
基準日とした日に●で印をつけています。
HLOOKUP関数で●をつけた日の数値を参照することで自動計算を可能にしています。

では、数式の詳しい説明をしますね。
ただし、前半のOFFSET（AC4,0,-1)に関しては既に『列挿入に合わせて数式の参照セルを希望通りに自動変更する方法（1）』で説明しているので割愛します。

HLOOKUP関数の構造は「=HLOOKUP（検索値，範囲，行番号，検索の型）」です。

この関数が行っていることは、
「検索値（●）が指定した範囲（$3:$11）の1行目のどこにあるかを検索し、
検索値（●）が見つかったら、検索値が所属する列と行番号（ROW()-2）がクロスするセルに入力されている値を返す。ただし、検索の型が０の場合は、検索値が完全に一致する場合のみ返す。」ということです。
※画像をクリックすると拡大します。

注意点は２つです。
一つ目は範囲を絶対参照で指定する点です。
絶対参照で指定しないと、数式をコピーしたときに範囲がずれる可能性があります。

二つ目は行番号をROW関数を使って指定する点です。
行番号を数値にすると、1行ごとに数値が違うので入力が大変です。
しかし、ROW関数を使えば、自動で行番号が計算されるので便利です。

このケースでは、ROW関数で返される行番号
（ワークシートを基準にした行番号）は「４」です。
しかし、HLOOKUP関数で使用する行番号
（HLOOKUP関数の指定範囲を基準にした行番号）は「２」なので、
ROW関数から２を引いて、HLOOKUP関数で使用する行番号を計算しています。
※画像をクリックすると拡大します。

今回は年月日が横並びだったのでHLOOKUP関数を使いましたが、
縦並びの場合はVLOOKUP関数を使えば同じことができます。

お役に立ちましたか？ご活用ください♪