Excelで予実管理・予実比較（予算実績管理・予算実績比較）。ーVLOOKUP関数の活用ー

「予実管理　Excel」「予実管理　エクセル」という検索キーワードで、当ブログにお越しいただいている方が多いです。
Excelで予実管理をするしくみをご紹介します。

会計ソフトに予算を入れられるかどうか

Excelで予実管理をする場合、会計ソフトに予算を入れられるかどうかによっても変わってきます。
価格の安いもの（弥生会計のスタンダード）、クラウド会計ソフトは、予算を入れることができません。

予算を入れられるならば、予実管理表をExcelにダウンロードし、こちらの推移表を加工するしくみと同じように、作ることができます。
【関連記事】会計ソフトの推移表データをExcelで楽に加工するしくみの作り方 | EX-IT
リンク

予実管理に必要なデータ

Excelで予実管理を作るなら、次のようなデータが必要です。

・実績
・実績累計
・予算
・予算累計

そして、前期比較も入れるのであれば、
・前期実績
・前期実績累計
も準備する必要があります。

毎回、これらの数字を準備していたら、大変です。
そこで、次のような工夫をします。

・推移表で準備する

すべての数値を推移表で準備します。
推移表なら、今期の数字は、すべて入っているからです。
必要に応じて、読み込む数字と変えれば、毎月使えます。

・累計は、Excel上で計算する

単月と累計の数字で比較するため、それぞれの数字が必要となります。
ただ、累計の数字は、Excel上で計算できるので、準備しなくても大丈夫です。

=SUM（予算!$b2:b2)
という数式を入れて、コピーすれば、実績（この場合は、「予算」というシート）から累計が簡単に計算できます。

・毎月準備するのは、実績の推移表のみ

予算、前期の数字は、期首の時点でもうそろっているはずです。
毎月準備する必要がありません。

実績の推移表のみを毎月準備すればいいことになります。
会計ソフトから推移表をダウンロードして貼り付けましょう。

・実績→毎月準備
・実績累計→計算
・予算→事前準備
・予算累計→計算
・前期実績→事前準備
・前期実績累計→計算
となり、このようなシート構成です。

6枚のシートから、数字を連動させます。

VLOOKUP関数で予実管理を作るしくみ

数字を連動するときに使うのは、VLOOKUP関数です。

VLOOKUP関数で、たとえば、2014年6月の売上高（実績）を連動させるなら、

・「売上高」（セルA2）を
・シート「実績」のA列からM列の範囲から、
・4番目の数値で、
・完全一致（数式ではFalse）

と入力します。
（ところどころに、$がついているのは、コピーを楽にするためです。）

ただ、このままでは、7月になったときに、数式内の「4」を「5」に変えなければいけません。
実績だけではなく、予算や、前期もすべてです。
手間がかかりますので、もう少し効率化します。

連動する月を変更するしくみ　VLOOKUP・INDEX・MATCH

セルA1に入っている、年月を変えれば、数字を読み取る場所を変えられるようにします。
VLOOKUP関数とINDEX、MATCH関数を組み合わせれば、2014/6（実際には、2014/6/1と入っていて、書式が「2014/6」です）を探し、一致した場所、つまり4を数式に組み込めます。

7月、8月・・と変更しても自動的に読み取る位置を変えることができるのです。

ただし、これは、月の表示が完全に一致している必要があります。

たとえば、freeeからダウンロードした推移表は、こうなっていますが、書式が違うだけで日付の数値は一緒なので、読み取ることは可能です。

ただし、サンプルとは数字の入っている位置が異なるため、VLOOKUP関数はちょっと変えなければいけません。

弥生会計だと、こんな形式です。

「6月度」というイケてない形式の、このしくみは使えません。
さらに、「上半期残高」が入っているため列がずれます。
エクスポートし、上半期残高を削除してからExcelのフォーマットに貼り付けるようにしましょう。
弥生会計からのエクスポートは、Excelへ切り出しボタンを使うよりも、【ファイル】→【エクスポート】（Alt→F→E）の方をおすすめします。
Excelへ切り出しだと、B/S、P/Lそれぞれで操作する必要があり、形式も使いにくいからです。

連動する月を変更するしくみ　VLOOKUP・MONTH・IF

弥生会計のようなパターンもありますので、私はINDEX・MATCHを使わない方法でやっています。

3月決算の場合、こういった数式です。
=VLOOKUP($A3,実績!$A:$M,IF(MONTH($A$1) <=3,MONTH($A$1)+10,MONTH($A$1)-2),FALSE)

IF(MONTH($A$1) <4,MONTH($A$1)+10,MONTH($A$1)-2)
で、2014年6月の場合、4列目を連動させるように指定しています。

MONTH関数で、月の値を読み込み、条件でわける必要があるのです。

4月→4→連動するのは実績の表の2列目なので、4-2
5月→5→連動するのは実績の表の3列目なので、5-2
6月→6→連動するのは実績の表の4列目なので、6-2・
・
・
という法則があります。

ただ、2015年1月、2月、3月は、この法則ではなく、
1月→1→連動するのは実績の表の11列目なので、1+10
2月→2→連動するのは実績の表の12列目なので、2+10
3月→3→連動するのは実績の表の13列目なので、3+10・
という法則です。

この法則を表現したのが、
=VLOOKUP($A3,実績!$A:$M,IF(MONTH($A$1) <=3,MONTH($A$1)+10,MONTH($A$1)-2),FALSE)
となります。

決算月により数式が変わりますので、法則を考えて、数式を修正しなければいけません。
それを避けるために、決算月を入力し、それを読み込む方法もオススメです。

条件付き書式で味付け

条件付き書式で味付けすることもできます。

【関連記事】グラフ機能を使わずに、Excelでかんたんにグラフを作る方法 | EX-IT
リンク

千円単位で表示

サンプル、及び今回の説明では、実数値の金額を千円単位で表示しています。
セルの書式設定（Ctrl＋1）で、【ユーザー定義】をえらび、次のように設定してみてください。

年月の入れ方

サンプルのセルA1の年月は、「2014-6」「2014/6」と日付の形式で入れなければいけません。
又は、セルの編集（F2）で月のみ編集してください。

サンプルはこちらからダウンロードできます。

EX-ITサンプル　予実管理PL.xlsx

---

---

---

---

【編集後記】

Apple Watch Sportを兼ねて自宅付近をラン。
昨日は暑くて、夕方に走っても汗だくでした。。。

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

青葉台付近をラン
サンワダイレクト　USB充電器