ExcelVLOOKUP関数で、月別推移から前年比較・年別推移を作る方法

月別の推移表をExcelで作っておくと、VLOOKUP関数を使って自由に数字を抽出できます。

※Excelで作成したデータを、スクリーンショットでKeynoteに貼り付けて作成

Excelで月別推移のデータを持っておくと便利

Excelで月別のデータを持っておくとその後加工できるので便利です。
サンプルでは、2012年1月から

2015年5月までのデータを入れています。

独立、起業時からすべてのデータを集めてみましょう。
過去の数字を見ると、今そして今後の方針を立てることができます。

苦労して、ときにはお金をかけて作った数字を確定申告（税務申告）だけで終わらせていてはもったいないです。

月別推移は、
・B/S（貸借対照表）
・P/L（損益計算書）の単月
・P/L（損益計算書）の累計
を作っておきましょう。

P/Lの数字には、単月（2016年5月）と累計（2016年1月〜5月。フリーランスまたは12月決算の会社の場合）という見方があるからです。

さらには移動年計という見方もあります。
【関連記事】売上が変動・不安定なら見ておきたい数字【移動年計】とExcelテンプレート | EX-IT
リンク

累計の数字は、数式で年（又は年度）ごとに入れておきましょう。

サンプルでは、2012年1月に、=SUM($C8:c8）という数式を入れて2012年12月までコピーしています。
2012年2月は、　=SUM($c8:d8）→1月から2月の計
2012年3月は、　=SUM($c8:e8）→1月から3月の計
2012年4月は、　=SUM($c8:f8）→1月から4月の計
・
・
・
2012年12月は、　=SUM($c8:n8）→1月から12月の計
と累計を計算する式を入れることができるのです。

c8の前に$がつける（F4キーを押します）と、コピーしても位置を固定できます（相対参照というものです）。

年（又は年度）の区切りで、数式を変えてコピーしましょう。
2013年1月は、=SUM($o8:o8）という数式が入っています。

VLOOKUP関数で任意のデータを取り出す方法

月別の数字から、任意のデータを取り出せるようにしておくとさらに便利です。

たとえば、2016年5月のデータを取り出し、その前年と取り出せば比較ができます。
去年と比べてどうだったか？がわかるのです。

グラフを作ってみると、ぱっとみてわかります。

この場合、毎回、データをコピーして貼り付けていては大変です。
VLOOKUP関数でデータを取り出します。
（INDEX関数、MATCH関数でやる方法もありますが、応用がきくVLOOKUP関数でやるのをおすすめします）

［月別推移］の2016年5月の［現金］は、72,220円です。
これを［前年比較］に表示させます。
［月別推移］と［前年比較］の［現金］が一致していれば取り出せるのがVLOOKUP関数です。

この場合、72,220円、つまり2016年5月の数字は、B列からBC列の範囲のうち、左から54列目にあります。

これをVLOOKUP関数で入力すると、72,220円を表示するというしくみです。
（※範囲をBからBC列とすると、次の月のときに範囲外になりますので、多めに指定しておくと便利です。この場合CZ列まで指定しています。）

セルB2［現金］を、［推移表］のB列からぴったり一致するものを見つけたら、その54列目を表示します。

「ぴったり」というのがFalseです。
「＄」がついているのは、コピーしても位置がずれないように固定するものと考えてください。

54という数値を変えると、別の月の数字を表示できます。

54が2016年5月ですので、53だと2016年4月

42だと2015年5月を表示します。

ただ、毎回、数式内の数字を変えるのはめんどくくさく、おすすめしません。
［2016年5月］のセルを変更すれば、VLOOKUP関数内の数字が変わるようにしましょう。

2016年5月→54なので、適当な場所のセルに［54］と入れて、それを読み込むようにする方法があります。

次の月になれば、［55］に変えれば、前期比較の数字も変わります。
1年前は、そのセルの数字から12をひいた数（2015年5月の場合は、54-12）にしておけば大丈夫です。

ただ、この場合、手間がかかります。

こういった場合は、
2016年5月→54
2016年4月→53
2016年3月→52
という組み合わせの法則を考えてみましょう。

54というのは、2012年1月（この推移表のはじまりの月）から2016年5月の月数＋2なのです。
月数は、DATEDIF関数でカウントできます。
ただし、この場合、2012年1月は、2012年1月1日、2016年5月は、2016年5月1日なので、52です。
そのためDATEDIFの結果に2を足しています。

セルN1にはじまりの年月を入れて（2012-1と入れて）、年月（セルC1）を変えれば自動的にデータを読み取るしくみです。

1年前（この場合2015年5月）も入力しなくていいように数式を入れました。

しくみは複雑ですが、毎回考えなくてもいいので、楽になります。
サンプルで動きを確認してみていただければと思います。

なお、累計部分のVLOOKUP関数は、範囲を累計部分に限定しています。
B列からCZ列だと、単月の数字を読み取ってしまうからです。
VLOOKUP関数では、たとえば［売上高］を探して、最初に見つかった数値（単月）を表示します。
累計は、単月の下にあるので、表示されません。

そのため、数式をちょっと変えて、B21からCZ32という範囲にしていますので、サンプルで確認してみていただければ。
（行を挿入するとこの範囲がずれますので気をつけましょう。ずれないようにする設定方法もあります）

すべてのデータが必要なければ、必要な分だけVLOOKUP関数で呼び出すこともできます。

決算ごとの比較もできる

上記のしくみを利用すると、5月の数値で、複数年を比較したり、

12月、つまり決算時（フリーランス、12月決算の場合）の数値で比較したりすることもできます。

決算での比較をするなら、推移表に予測の数値を入れておくのがおすすめです。

2016年の予測値、着地点を過去を比較して、このままでいいのか、もっとがんばらなければいけないのかなどがわかります。
こういった使い方をするのが本来の数字の役割です。

月別推移は私の場合、Excelで取引を入力して集計したものを使っていますが、
【関連記事】Excelの仕訳データから推移表を作るアイデア ーVLOOKUP・ピボットー | EX-IT
リンク

会計ソフトの月別推移表データをExcelで連動させることもできます。

【関連記事】会計ソフトの推移表データをExcelで楽に加工するしくみの作り方 | EX-IT
リンク

会計ソフトでも、時系列で比較できる機能があるものもありますが、独立以来、10年といった場合はやはりExcelで作るしかありません。
【関連記事】独立後9年弱の会計データをすべて1枚のExcelシートに入れている理由。管理しやすさ・使いやすさ・宝の山。 | EX-IT
リンク

サンプルはこちらです。
EX-ITサンプル　VLOOKUP前期比較.xlsx

 

■関連記事
【関連記事】去年の自分との戦い。Excelで数字を前年（前期）と比較しよう。 | EX-IT
リンク

---

---

---

---

【編集後記】
「野菜とスパイスとインド飯」という看板が目にとまり、ハブモアカレーというお店へ行ってみました。
2種類のカレー＆ターメリックライスと、選べる2種類の付け合わせのカレープレート（1,280円）のみのシンプルなメニューで、期待通りおいしかったです。
サイトを見ると、日々メニューがかわるようで、毎回楽しめます。

野菜も、赤軸ほうれん草、紅ぐるり大根、大納言小豆、青大豆、レッドムーン（じゃがいも）、ハワイアンオニオンなど珍しいものが多いです。

付け合わせは5種類。2人だと4種類なので、1種類プラスしました（＋150円）。

【昨日の1日1新】
※詳細は→「1日1新」

原宿　mizucafe
渋谷　ハブモアカレー