自社の数字に興味を持とう!経費の内訳を、Excelピボットテーブルでかんたんに集計する方法

事業の数字を見て、「○○費 ××円」とあったら、経営者(個人事業主)はその中身をおおまかにでも把握しておかなければいけません。
詳しく見たいときは、会計データをExcelで集計してみましょう。

スクリーンショット 2014 07 16 9 00 59

自社の数字に興味を持とう

税理士に依頼するにしろ、経理担当者に依頼するにしろ、自社の数字に興味を持ちましょう。
「数字が苦手」「わからないから見たくない」というのでは、いつまでたっても数字を使いこなせません。
専門的な知識は必要なく、さらっと眺めるだけで大丈夫です。

その眺めたときに、「あれ?」と思うものがないかを確認しましょう。

「○○費」に何が入っているか?多すぎないか?

たとえば、法人だと、こういった数字を見て、「支払手数料33万ってなんだっけ・・・?」と感じたら、その明細を確認するのです。
スクリーンショット 2014 07 16 7 53 16

個人事業だと、こういった数字で、「広告宣伝費ってこんなに使ってるかなぁ。。」と感じたら、同様に中身を見てみます。
スクリーンショット 2014 07 16 7 53 48

「支払手数料」「広告宣伝費」といった項目を勘定科目といい、便宜上、事業の取引を集計しています。
何が集計されているか、自分の感覚とあっているかを経営者(個人事業主)が確認することは欠かせません。

何か行動しなければ会計には記録されません。
記録され集計されているということは、何かを決定し行動したからなのです。
決定・行動した本人が把握していなければ意味がありません。

会計データからExcelピボットテーブルで明細表をかんたんに作る方法

もちろん、その明細を税理士や経理担当者に聞いてもいいのですが、自分でも集計することができます。
むしろ、自分で集計した方が、Excelスキルも数字スキルも上がるのでおすすめです。
(会計ソフトから仕訳データをExcelに変換する必要があります)


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


例として弥生会計で解説します。

①会計ソフトで仕訳データを開く

会計ソフトで仕訳データ(試算表や推移表ではありません)を開きます。ソフトによっては、仕訳帳、仕訳日記帳という名称です。

スクリーンショット 2014 07 16 7 52 42

②仕訳データをExcelに変換

弥生会計だと、ファイル→エクスポート(Alt、F、E)で、ファイル名に「.CSV」をつけて保存します。設定は「弥生インポート形式」です。
(ファイル名は任意で決めて大丈夫です)
スクリーンショット 2014 07 16 8 10 15

Excelで開くとこういったファイルができます。
スクリーンショット 2014 07 16 8 14 55

※他のソフトでは、データ切り出し、CSVエクスポート、Excel変換、CSV変換といったメニューがあります。

※[Excelへの書き出し]というメニューもありますが、こちらでやるとExcelで加工しづらい形式になります。
私は一切使っていません。
スクリーンショット 2014 07 16 8 12 05

単にExcelに書き出すだけでは意味がないのです。Excelで加工しやすい形にすべきです。

③Excelデータに見出しをつける

集計に使うピボットテーブルでは、見出しが必須だからです。
すべての列に見出しをつけると大変ですので、使う項目だけに見出しをつけ、他には適当に数字、たとえば「1」を入れます。

1行挿入(Ctrl+Shift+;)して、
スクリーンショット 2014 07 16 8 20 18

■スポンサードリンク

セルA1に「1」を入力し、データの入っている範囲で1行目を選択(Shift+→)し、
スクリーンショット 2014 07 16 8 20 26

右方向へコピー(Ctrl+R)で、該当範囲に「1」が入ります。
スクリーンショット 2014 07 16 8 20 34

経費の集計なら、左側の科目しか使いませんので、見出しを入れるのは、次の4カ所(日付、科目、金額、摘要)です。
スクリーンショット 2014 07 16 8 21 18

ただ、弥生会計は、日付の形式が「H.25/04/01」となっていますのでこれを修正します。このままではExcelで日付とみなしません。
置換(Ctrl+H)で、次のように設定して、[すべて置換]をクリックします。
スクリーンショット 2014 07 16 8 21 30

これで、日付として扱えるようになりました。
ようやく下準備完了です。
スクリーンショット 2014 07 16 8 21 36

④Excelデータをテーブルに変換

どこでもいいのでデータを選択してCtrl+Tを押すとテーブルを作れます。
絶対必要ではありませんが、ピボットテーブルを作るにはテーブルにしておいた方がやりやすくてミスも少ないです。
スクリーンショット 2014 07 16 8 28 00

テーブルにするとこうなります。
スクリーンショット 2014 07 16 8 28 04

⑤ピボットテーブルを作る

テーブル内のデータ(どこでもいい)を選択して、[挿入]→[ピボットテーブル]をクリックし、
スクリーンショット 2014 07 16 8 29 45

OKを押します。
スクリーンショット 2014 07 16 8 31 17

右上の項目をそれぞれ、次の画像のように、右下のボックスにドラッグしてください。
スクリーンショット 2014 07 16 8 32 59

これでピボットテーブルが完成します。桁(カンマ)区切りにした方が見やすいので、金額の列をクリックして、Ctrl+Shift+1で表示を整えましょう。
さらに、金額を右クリックして、大きい順に並べ替えるとみやすくなるのでおすすめです。
スクリーンショット 2014 07 16 8 34 58

仕訳の左側(借方)の科目のみ集計してますので、右側(貸方)に科目があると表示されませんが、おおまかにみるにはこれで十分でしょう。
現金、普通預金等の科目も集計されます。不要なものは、次の画像のようにクリックしてチェックを外してください。
スクリーンショット 2014 07 16 8 55 14

この表があると、「車両費で、ガソリン代が結構かかっているなぁ。。」ということに気づいたり、
スクリーンショット 2014 07 16 8 36 15

「消耗品費が多いのは机・椅子を買ったからか〜」などといったことに気づいたりすることができます。
スクリーンショット 2014 07 16 8 36 45

まとめ

数字を集計して、利益がそこそこで税金もそこそこ(許容範囲)ならOKというだけでは、もったいないです。
数字を見て、日々の意思決定に活用しましょう。
難しい分析は必要ありません。見るだけ、比較するだけです。

なお、今回のようなファイルを一度作っておけば、仕訳データの部分だけ最新のものに入れ替え又は最新のデータを追加し、ピボットテーブルで更新(右クリック→[更新])すれば、簡単に表ができます。
最初だけ設定が必要です。
スクリーンショット 2014 07 16 8 40 46

サンプルはこちらです。データは弥生会計に入っているサンプルデータを使っています。
EX-ITサンプル ピボット科目明細

|





■スポンサードリンク
【編集後記】
明日発売のSurfacePro3を買うかどうか、ひとり会議しつつ、いったん注文しましたがキャンセルしました。
やはりMacのソフトが使えないと不便だからです。
ブログネタにもなるので買うのもいいのですが、がまんします。

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

空港から事務所までバイク持ち運び
マイクロソフトストア初利用




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』