年度を表示するエクセルの関数(vlookup関数編)
前回に続いて、
年・月がわかっているときに、
年度を表示させる方法を考えます。
if関数だと大変な場合はどうする?
前回は、if関数を使った方法を紹介しましたが、
それでは、大変な場合があります。
例えば、決算期変更をしていて、
事業年度が次のようになっている会社を考えてみてください。
「2013年4月」 | から | 「2014年3月」 |
「2014年3月」 | から | 「2014年9月」 |
「2014年10月」 | から | 「2015年9月」 |
「2015年10月」 | から | 「2016年9月」 |
このような場合、
単純に「月」を見ただけでは、
事業年度を計算することができないので、
if関数を使った事業年度判定は
(次のように、できないことはないのですが)
なかなか大変です。
vlookoup関数で、if関数を代替する
今回の場合は、
vlookup関数を使うことで、計算式が非常に簡単になります。
- 1.シリアル値を作る
-
まず、下準備をします。
今、年月のデータが、A列とB列に入っています。
これをdate関数を使って、シリアル値に変換します。シリアル値に変換するには、
年月だけでなく日の情報も必要なのですが、
今回は、各年月の「1日」のシリアル値を取得します。 - 2.事業年度開始日と事業年度の対応表を作る
-
次に、
vloookup関数を使うために、
事業年度開始日と事業年度の対応表を作ります。ここでも
A列には「シリアル値」を入れているというところが、
ポイントです。見た目は日付に見えますが、
その実態は単なる「数値」です。 - 3.vlookup関数の近似値検索を使って事業年度を取得
-
あとは、
vlookup関数を使うことで、
事業年度を表示させることができます。シリアル値を使った近似値検索をしていますので、
意味合いとしては、
探そうとしている日付よりも過去の日付で、
一番近い日付の行を探す
という動きをすることになります。
「大小比較」が連続する場合はvlookup関数で置き換えられる
vlookup関数を使うと、
次のような「大小比較」を連続して行わないといけない場面では、
if関数を使うよりも、簡潔に表現することができます。
「1」から「9」 | → A |
「10」から「25」 | → B |
「26」から「60」 | → C |
今回やろうとしていることをまとめると、
次のようになります。
「2013年4月」 | から | 「2014年3月」 | → 2014年3月期 |
「2014年3月」 | から | 「2014年9月」 | → 2014年9月期 |
「2014年10月」 | から | 「2015年9月」 | → 2015年9月期 |
「2015年10月」 | から | 「2016年9月」 | → 2016年9月期 |
先ほどのものと、
形がとてもよく似ていますよね?
単純な数値の大小比較になっていないので、
ひとひねり必要なのですが、
こういう形のときは、
何らかの工夫をすれば、vlookup関数を使う形にできることが多いです。
シリアル値に変換することでvlookup関数が使える形になった!
今回は、
2013年4月1日のシリアル値が「41365」というように、
それぞれの年月の「1日」を「シリアル値」に変換してみると、
「41365」 | から | 「41698」 | → 2014年3月期 |
「41699」 | から | 「41912」 | → 2014年9月期 |
「41913」 | から | 「42277」 | → 2015年9月期 |
「42278」 | から | 「42643」 | → 2016年9月期 |
というふうになります。
これで、vlookup関数が使える形になったわけです。