Excelピボットテーブルで経費明細を去年（前期）と比較

去年（前期）と比べてどうだったか。
2年分の会計データがあれば、その明細をExcelピボットテーブルで比較できます。

 

 

 

 

 

 

※Keynoteで作成したスクリーンショット

「お金の使い方をトレーニングする」という考え方

仕事で使った経費は、データにしなければいけません。

・自分でひたすら入力する
・お金を払って依頼する
・ソフトを使う
など、それなりに対価を払ってデータにしているはずです。

それが「税務署のため」「税金のため」だけだとその対価を取り返せません。
自分のために使ってこそ、データの意味があるのです。

蓄積されたデータを見れば、自分のお金の使い方がわかりますので、反省することもでき、お金の使い方のトレーニングにもなります。

お金は、そうそう稼げないですし、そうそう貯まらないですし、ましてや増えることもありません。
鍵は、使い方です。
その使い方から目を背けず、しっかりを見ておきましょう。

Excelピボットテーブルで、経費を比較

経費を比較するというと、まずはこういった概略をみます。

去年（前期）と比べてどうだったか？
使いすぎていないか、もっと使っていいかなどが判断できます。

ただ、これだけでは自分のお金の使い方はわかりません。
もっと細かく見る必要があります。

・消耗品費が、87,278円多かったのはなぜ？
・広告宣伝費が少なくすんだのはなぜ？
・会議費ってこんなに使ってたっけ？
などといったことを知るのはこまかく見るしかありません。

同じ、ITコストや広告宣伝費でも、こういった明細を見れば、去年何を使って、今年何に使ったかがわかります。

こういった表を作れるのがピボットテーブルです。
自分の場合でも、お客様の場合でもこの表を作っています。

お金の使い方のチェックもできますし、データ入力が正しいかどうかのチェックもできるものです。

Excelピボットテーブル比較表のつくり方

Excelピボットテーブル比較表は、次のような手順でつくります。

①データの準備

まず、2年分の会計データを同じシートに準備しましょう。
会計ソフトからそれぞれExcelに出すことになります。

年ごとにわけてもいけませんし、空白行を入れてはいけません。

ピボットテーブルでうまく集計できなくなります。

※サンプルデータ。数値、内容は適当です

さらに、1行目にタイトルをつけます。
必要のない列があっても削除せずに、適当に入れておけば大丈夫です。
（「1」など。空白だとエラーが出ます）

②「年」のデータをつける

ピボットテーブルで集計するためにデータへ「年」のデータを入れます。
YEAR関数を使うと楽です。
セルA2に日付が入っている場合（2015/1/1）、=YEAR(A2)で、2015と表示することができます。

「期」を入れたい場合は、こちらの記事を参考に、VLOOKUP関数を使いましょう。

③データをテーブルにする

データをテーブルにします。
テーブルにしなくてもピボットテーブルにはできますが、後の処理を考えるとテーブルにしておいたほうがオススメです。
データ上のいずれかにカーソルを置き、Ctrl＋T→Enter（＋は同時押し、→は1つずつ）で、テーブルにしましょう。

④ピボットテーブルにする

テーブル上にカーソルを置き、［挿入］→［ピボットテーブル］でピボットテーブルをつくります。

⑤ピボットテーブルを組み合わせる

右側で項目を組み合わせれば（チェックボックスにチェック、マウスでドラッグ）、ピボットテーブルを集計できます。

［金額」にチェックを入れてみましょう。
元データの［金額］が集計されます。

［借方］をチェックすると、［借方］、つまり科目別の経費の金額が集計できます。

フィルタをクリックすると、科目の一覧が出てくるので、必要な科目のみにチェックを入れましょう。
［すべてを選択］をクリックすると、チェックがいったん外れます。

整理するとこうなります。
ついでに、B列を選択してCtrl＋Shift＋1を押し、カンマをつけておきましょう。

この表に［年」を追加します。
［年］をチェックすると、別のところに入る可能性が高いので、マウスで［列］へドラッグするほうが確実です。

これで、年、科目別の集計ができ、［総計］はいらないので、右クリックして［総計の削除］で、消します。

さらに［摘要］をドラッグすると、経費の詳細を比較できる表の完成です。

下にスクロールさせても、年がわかるように、ウィンドウ枠を固定（Alt→W→F→Enter）しておきましょう。

打ち合わせの内容を見てみるのもおもしろいです。

数式を入れれば、増減値もわかります。

気をつけたいのは［摘要］、つまり経費の明細です。
これが1文字でも違うと、別のデータとして表示されてしまいます。
比較をきちんとしたいなら、毎回同じ摘要で入れましょう。

厳密に合わせなくても、去年はこれを使ったから多かった、今年はこれがなかったから少なかったということがわかれば大丈夫です。

この場合は借方（左側）の科目のみ集計していますので、経費が貸方（右側）にある場合（例　現金　消耗品費 1080）は、正しく表示されません。
少額であれば気にしないか、会計ソフトにマイナスで入れておけば解決できます（例　消耗品費　現金　-1080)

⑥デザインの変更

ピボットテーブルは、通常こういったフォーマットです。

私はこちらのほうが好みなので、

ピボットテーブルツール（ピボットテーブルにカーソルを置くと表示）→［デザイン］→［レポートのレイアウト］の［表形式で表示］で変更しています。

色は、［デザイン］→［ピボットテーブルスタイル］で変更できるので、好みの色に変更しましょう。
小計を目立たせると見やすいです。

同じ方法で売上の分析もできます。

データがあったらピボットテーブルで集計してみる、数字を見るために集計する、数字を見るためにデータをつくると考えましょう。

---

【編集後記】

昨日は午後に個別コンサルティング。
freeeを使っている方で、数字をチェックし、申告まで完了。
マイナンバーカードも無事読み込めました。

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

Anker　USBハブ