【支出管理】神Excel機能『ピボットテーブル』を君は使いこなしているか！？

Faust3rdのブログ『40歳未だ初見プレイ　人生攻略×仕事攻略×投資実況』はこちらからどうぞ！

Excelは表計算ソフト、、くらいの認識で、四則演算や一覧表作成くらいにしか使っていないのは、本当に宝の持ち腐れ。ここでしっかり機能を理解して、「実質無料（外注コスト無し）」で、がっちり支出管理をやってしまいましょう！

ちなみにワタシは、ソフトウェア予算も文化もほぼない中小企業に業務立て直しのコンサルに入ったときに、このピボットテーブル機能だけで、「年間5億円分くらいの支出明細」をきれいに分析し、次に打つべき対策提示を明示して、なんとか黒字フローへ導いたという経験もあります。

いいデータベースソフトを使うというのも、もちろん良いのですが、データ集計～分析するまでの理屈やノウハウを知っているか知らないかで、その後の創意工夫がだいぶ変わります。

高いソフト、アプリケーションをパッケージ導入しても、それが本当に機能して多大な生産性向上を達成できるかは、何の管理もしていない時点ではどちらにしても誰にも測れません。

ピボットテーブルなら何度か試したことあるよ？という人も、まったくエクセルなんて開かんぞ！？というあなたもまずはここで一通り、支出管理の方法と分析手法を一緒に学んでいってみましょう！

※この記事のキャプチャ画面はExcel2016を使っていますが、どの世代のExcelにも機能自体は入ってますので、ぜひトライしてください。

簡単に言ってしまえば、「大量の羅列データ」を、ワンタッチで「キーワードごとに集計してくれる」という機能です。

画面で見た方が早いですね。

たとえば、こんな感じの日々の雑多な日計表、一覧表が作ってあったとすると、

数十秒の操作で、以下のような集計表をつくれます。

これくらいまとまってると、分析のし甲斐がありますよね？

では、この表の作り方を一通り見ていきましょう。

◆ピボットテーブルの作成手順　①元データをつくる

まずは、元になる日計表データを作り込んでいきます。

この時、この元の表の作り込み次第で、そのあとの分析や作業に影響がだいぶ出てきますので、以下の手順でしっかりとやってみましょう。

◆最初の行へ「分類したくなりそうな項目」を並べ、下へひたすら入力していく

上の例だと「月」「日」「カテゴリ」「商品名」といった項目ですね。

作成時のポイント
・月、日は別のセルに分けておく。打ち込みやすいし、あとで役に立つ
・「カテゴリ分け」「中分類」などを入力しておくと、あとで分析をしやすい

既に何かしらの日計表データがある場合
テキトーに作成されたデータだと、うまく集計ができない可能性が高いので、きれいに打ち直す必要があります。ex.「3(半角)」と「３(全角)」は区別されてしまう。
少し大変ですが、『置換機能（「Ctrl」キーを押しながら「H」キー）』などをうまく使ってまずはきれいにしてしまいましょう。

◆ピボットテーブルの作成手順　②作った表を「全部ドラッグ」～ボタン一つで集計完了

元の表データができてしまえばこちらのものです。（あとはずっと俺のターン！）

まずは、できあがっている表をすべてドラッグ（選択）をして、上の「挿入」タブにある【ピボットテーブル】をクリックしましょう。

すると、以下のような画面が出てきます。

この画面を出してからテーブル範囲を指定してもいいのですが、「大体の人が、指定し間違える」ので、先に全部ドラッグしてしまう方が、特に最初はよいと思います。

あと、「ピボットテーブルレポートの配置場所」も、慣れないうちは「新規ワークシート」がオススメです。表があるブックの中で、新しいシートが自動で作成されます。

ここまできたら、あとは楽しい集計祭りです！

◆ピボットテーブルの作成手順　③できた集計表を見たい項目へ整える

ピボットテーブルフィールド（「月」「商品名」など）の項目をドラッグアンドドロップで、下にある【フィルター】【列】【行】【値】に好きなように入れていきます。

フィルターに「月」、列に「大カテゴリ」、行に「日」、値に「仕入れ価格（合計）」を入れてみました。

こうすると、【当月（3月）の、日ごと・カテゴリごとの仕入れ集計】の表が出来上がります。

あとは、自分が知りたいタテヨコ集計にいろいろとアレンジをして、たくさん試してみてください。

数値の集計方法も「合計」だけではなく、【値フィールドの設定】というところで、「平均値」「値の個数」「最大値」「最小値」など、さまざまな計算式に変更ができます。

出来上がった集計表の一番下に（空白）という項目が出ていますが、これは「日」を打ち損じている欄が元データにある、ということなので、元の一覧表へ戻って、この（空白）がなくなるように修正をしてみましょう。（元データを直したら、表の上で右クリックして【更新】）

◆できあがった表の分析手法

こうして出来上がった表は、的を射た項目設定ができていれば、日ごろその業務にがんばっているアナタにこそ、【ピーンとくる】数値が出ているのではと思います。

ここには、改善のヒントが詰まっているので、ぜひ、ピーンとくるまで、表をいろいろと集計しなおしてみましょう。

あなたの課題は何か　（支出額、片寄り、異常値erc.)

こういう、課題意識（問題の原因の仮説を立てて）をもって、数値検証をしてみましょう。

やみくもに眺めていても、時間の浪費をしてしまいますので、

「きっとこういう現象が起きている」

という仮説意識が、分析をする際には重要なポイントになります。

◆まとめ　

さて、アナタの今の事業課題を解決するヒントになったでしょうか。

ワタシはよく、問題解決には

ズームイン・ズームアウト思考をしてみよう

と、提案をして実践をしています。ズームインとは深堀り、ズームアウトとは俯瞰することをイメージします。

よくわからないことって、問題にしっかりピントが合っていない【ピンボケ案件】だということが非常に多いです。なので、深堀と俯瞰がいい薬として作用します。数値的な分析としては、このピボットテーブル分析も、非常に思考と検証がはかどりますね。できあいのシステムではなかなこうはいきません。

ITは、ものすごい（高額な）アプリケーション、ソリューションを使わずとも、こういう基本機能を使いこなすだけでも、解決できる問題ってまだまだたくさんあります。

ぜひ、課題解決にピボットテーブル分析、活用してみてくださいね！