手間をかけないExcel資料作成。会計ソフト推移表→決算予測・売上グラフ・経費グラフ・移動年計グラフ・予算実績比較表・目標達成度グラフ

Excelで資料をつくるときには、手間をかけないことが大事です。
とことん研究してみましょう。

※カフェにて　iPhone X

資料作成に時間をかける意味

資料をつくることは大事な仕事であるのですが、そこに手間をかけても意味がありません。
手間がかかると、時間がないのでつくらないということにもなりえます。

時間をかけてつくったほうが価値があるものもありますが、資料は決してそうではないでしょう。
独立前なら、資料をつくる時間＝どれだけ会社に残っているか、どれだけ分厚い資料をつくるかで評価されたかもしれません。
独立後は、その時間をアピールしても意味がなく、
「徹夜してつくりました！」
「3日かかりました！」
「大変なんですよ、この資料つくるの」
と言っても、「知らんがな」と他に依頼されてしまうでしょう。

そういう私も、独立直後は、資料に時間をかけていました。
それが価値と思っていたところもあり。
ただ、お客様の目の前で、PCをささっと操作することも多く、そうなると時間がかかっていないのはまるわかりです。
そこでかっこつけても、課金してもしかたないなと割り切りました。

会計ソフト推移表から資料（表・グラフ）をつくる流れ

たとえば、毎月の数字の資料です。

■売上のグラフ（3期で比較）

■売上の移動年計グラフ
売上の傾向を把握するものです。

■固定費の比較グラフ

■前期比較
主な数値を前期と比較、単月と累計。

■資産、負債の前期比較

■予算（目標）達成度のグラフ
折れ線が実績の売上と利益、棒が予算の売上と利益

■決算予測
この調子でいくと決算でどのくらいの数字になるか
前期実績との比較、税金の予測も。

これらを毎回つくっていては大変です。
ミスの可能性もあるでしょう。

そこで会計ソフトから、推移表データをエクスポートし、このファイルに貼り付けるだけで、資料ができあがるようにしています。
（決算予測だけ操作が必要です）

この手法です。

毎月やるのは、
・会計ソフトのデータをチェック
・会計ソフトから推移表をエクスポート
・そのデータをExcelで開き、該当部分をコピーしてファイルに貼り付け
・決算予測のデータをコピー
だけですみます。

それで前述の資料ができあがるのです。

「決算予測のデータ」をコピーというのは、こうやります。
事例の場合、2017年9月が最新の実績月で、それ以降は、予測です。

シート「会計ソフト推移表」に推移表データを最新の実績月（この場合、2017年9月）まで選択して貼り付けた後に、シート「決算予測」で、セルF2（2017年8月、前月の売上高）のを選択し、Ctrl＋Shift＋↓で、

このように選択し、そのままShift＋→で

こう選択したら、Ctrl＋Rでコピーできます。
こうすれば、2017年9月は、シート「会計ソフト推移表」からVLOOKUP関数を連動していますので、実績の数字が反映されるのです。
（マクロやロボットをつかって自動化することもできますが）

こちらの記事も参考にしていただければ。

脱Excelの前にExcelで手間をかけないしくみを

脱Excelという言葉をみるとイラッとします。
Excel好きなので。
Excelの可能性は無限大で工夫しがいがあるものです。
他のシステムにしたからといって、お金をかけたからといって必要な資料がかんたんにできるとは限りません。
私は、自分自身、お客様ごとに資料をわけています。
・資金繰りを見るべきケース
・粗利率を気にするケース
・種目別の売上を気にするケース
など、気にすべきところは、それぞれ違いからです。

個別に対応すると手間もかかるのですが、それをExcelでカバーしています。
個性のない標準的な資料を出してもしかたありません。

・標準的な資料を手間がかからない
・個別の資料は手間がかかる
を打ち破り、
・個別の資料を手間をかけずにつくる
を目指しましょう。

ま、この記事書くのにめちゃくちゃ時間かかってますが……

最後に、今回の事例ファイルを解説しておきます。
全体図はこのような流れです。
11枚のシートがあります。

①シート「会社情報｝から連動

シート「会社情報」に、期末の月を入れると、シート「3期比較」に連動し、月が表示されるというしくみです。
会社によって決算期が異なるので、それに対応し、期のデータもここでつくり、グラフで使っています。

②シート「会計ソフト推移表」からシート「3期比較」へ、実績の数値を連動

使っているのは、VLOOKUP関数です。
このシートでは、3期分のデータを準備しています。
3期比較グラフ、移動年計の計算に使うからです。
前期、前々期のデータはあらかじめ準備しておきましょう。
一度準備すれば、次の年からは、コピーですみます。

シート「会計ソフト推移表」に貼り付けるのは、最新月までのデータです。
列で指定して貼り付けましょう。
こうしているのには意味があります。
未経過月をブランクにし、シート「3期比較」ではあえてエラー（#N/A）にしています（IFとNA（）で）。
エラーにしておけば、棒グラフのデータ（9月以降）が空欄になり、

折れ線グラフでは表示されません。
（0だと線だけ表示されてしまいます）
こうなるところが

こうできるわけです。

さらに、最新月をINDEX関数とCOUNT関数で自動的に読み取り（このためにも先程のあえてエラー処理にしています）、
読み取った月を使って移動年計も計算できます。
（移動年計は最新月から過去2年の数字を使います）

③シート「会計ソフト推移表」からシート「決算予測」へVLOOKUP関数で連動

ここでもVLOOKUPです。
決算予測部分はそのときの状況により手入力します。
納税予測もここでやっておきましょう。
サンプルでは、ざっくり計算していますが、実際には、申告書の計算式で法人税、地方税を計算し、中間納税も考慮しています。

④シート「会計ソフト推移表」等からシート「予算実績比較」へ連動

シート「予算実績比較」では、実績、予算、前期、実績累計、予算累計、前期累計といった数字が必要です。

これをそれぞれのシートからVLOOKUP関数で連動します。

そこで問題になるのが、読み込む位置。
VLOOLUP関数ではどの位置から読み込むかを指定しなければいけません。
そしてその位置は毎月変わるので、読み込む位置をシート「3期比較」の最新月のデータを使います。
この数式は、決算月によって変わるので注意しましょう。
3月決算だと、8月（最近月）は、科目、4月、5月、6月、7月、8月と6列目です。
これが2月だと、科目、4月、5月、6月、7月、8月、9月、10月、11月、12月、1月、2月と、12列めになります。
これをIFとMONTH関数（月を取り出す）で表現したのが、この数式です。

なお、実績累計は、実績を計算し、予算・前期はあらかじめ準備して、予算累計・前期累計はSUMで計算しています。
こうすれば、毎月、実績だけを更新するだけで資料ができるわけです。

⑤シート「3期比較」からシート「資料」に連動
シート「資料」は、こういった1枚です。
今回の事例では1枚におさめましたが、複数枚になることもありますし、パワポ（orKeynote）を使うこともあります。

シート「資料」に集約して、これをお客様へお見せしたり、自分で使ったりしています。
シート「3期比較」からできる資料は次のようなものです。

■売上のグラフ

■売上の移動年計グラフ

■固定費の比較グラフ

⑥シート「決算予測」からシート「資料」に連動

シート「決算予測」から、VLOOKUP関数で主要なデータのみ連動しています。
細かい数字は必要なく、ざっくりわかればいいわけです。
■決算予測

⑦シート「予算実績比較」からシート「資料」に連動

シート「予算実績比較」から、VLOOKUP関数で主要なデータのみ連動しています。

■前期比較

■資産、負債の前期比較

■予算（目標）達成度のグラフ

その他

実際には、別途つくった資金繰り実績・予測グラフをつけることが多いです。
（サンプルでは画像で貼り付けています）

この方法でやっています。

これは推移表からつくることはできず、仕訳データをエクスポートしなければいけません。
結果的には、会計ソフトからエクスポートするのは
・推移表データ
・仕訳データ
です。

前期比較も今回の方法でやれば、計算できます。
エクスポートするデータも必要最低限にしているわけです。
（仕訳データからExcelで推移表をつくれるので、もう1つ減らそうと思えば減らせますが）

そして、異常値があれば、それについてのコメントも資料に入れておきます。

サンプルは、こちらです。
EX-ITサンプル　会計ソフト推移表から資料作成.xlsx

---

【編集後記】

昨日は、ランチで秋葉原近くのマーチエキュートへ。
以前行ったときから、お店も変わってよりオシャレになっていました。
帰りはラン。
途中に、スモークサーモン店でおにぎりを買いつつ。

【昨日の1日1新】
※詳細は→「1日1新」Mowi Salmon
フクモリ
カフェラウンジ千代楽

【昨日の娘日記】

新年度2日目。泣くことは減ったようですが、送りと迎えのときはギャン泣きでした。
送っていくときはベビーカーに乗るのですが、迎えにいくときはベビーカーに乗らず、だっこです。
まあ、1日離れているんでしかたないでしょうね。