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

  • URLをコピーしました!

月別の推移表をExcelで作っておくと、VLOOKUP関数を使って自由に数字を抽出できます。
EX IT
※Excelで作成したデータを、スクリーンショットでKeynoteに貼り付けて作成

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

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

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

EX IT 2

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

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

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

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

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

累計の数字は、数式で年(又は年度)ごとに入れておきましょう。
EX IT 7

サンプルでは、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月の計
と累計を計算する式を入れることができるのです。
EX IT 5

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

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

EX IT 6

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

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

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

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

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

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

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

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

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

EX IT 11

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

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

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

EX IT 12

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

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

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

EX IT 14

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

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

こういった場合は、
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)を変えれば自動的にデータを読み取るしくみです。
EX IT 16

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

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

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

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

EX IT 18

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

決算ごとの比較もできる

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

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

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

EX IT 23

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

月別推移は私の場合、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円)。

IMG 6987

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

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

  • URLをコピーしました!