経理業務に使えるピボットテーブルの活用方法

「ピボットテーブル」とは

ピボットテーブルとは、1つのデータから多様な集計ができるExcel機能の1つです。
ピボットテーブル機能だけで書籍が出版されているくらいですから、難しい機能と思っている人も多いと思いますが、操作方法はとても簡単です。

様々な資料作成を求められる経理にとって、１つのデータから色々な視点からの資料が作成できるピボットテーブルは最も利用価値の高いExcelの機能と言えます。

「ピボットテーブル」を作成して理解する

ピボットテーブルを理解するには、実際に操作して作成してみるのが最も理解しやすいです。
まず細かい説明をする前に手順どおりに作成してみてください。

こちらからサンプルデータをダウンロードしてください。

サンプルデータは経費精算システム「楽楽精算」で処理した経費支払データを少し加工して作成したものです。

①メニュータブの「挿入」を選択し、「ピボットテーブル」をクリックします。

②「ピボットテーブルの作成」のダイアログボックスが開きます。
分析するデータの範囲が自動的に挿入されていますので、そのまま「OK」をクリックします。

③新規ワークシートが作成され、シート上にピボットテーブル表示エリアが、右側にピボットテーブルのフィールド情報が表示されます。

フィールドセレクションからフィールドを選択して、各ボックスにドロップします。
行ラベルボックスに「支払先名」
列ラベルボックスに「支払予定日」
値ボックスに「金額」

次のとおりにピボットテーブルが作成されました。

このピボットテーブルから以下のことがわかります。
「各業者に毎月いくら支払っているのか」
「いつもの月に比べて支払い金額が多い、少ない」
「今月のみ発生した支払い先はどこか」

6ヶ月間の表が作成されましたが、フィルター機能を使うことで表示する月数を絞ることができます。
列ラベル右横の「▼」をクリックし、フィルターメニューを表示させます。

「ピボットテーブル」を作成するためのデータのルールを知る

ピボットテーブルがどのようなものか理解していただけたかと思います。
ピボットテーブルを作成する上で、元となるデータがルールに従って作成されているかが重要です。
ルールに従っていないデータからではピボットテーブルは作成できません。

ピボットテーブルが作成できるデータ

ピボットテーブルが作成できないデータ

比率を算出する資料を作成する

より高度な資料を作成してみましょう。
値フィールドには、同じフィールド値を何度も使うことが可能です。
数値を集計するだけでなく、「平均値」や「行集計に対する比率」などさまざまな値を算出することが可能です。

1．値ボックスに「金額」を追加します。

「合計 / 金額2」という列が追加されました。

2．列ラベル「合計 / 金額2」を選択した状態で右クリックし、「計算の種類」→「行集計に対する比率」をクリックします。

総支払金額に対するその月の支払金額の割合が算出されます。
つまり、アクセスメーカーという会社に、1～3月の3ヶ月で「494,272円」支払い、2月分支払い金額の「125,906円」で割った値「25.47％」が表示されます。

3．列ラベル「合計 / 金額2」の枠部分を選択すると、列「合計 / 金額2」が一括で選択されます。

4．「ホーム｣→「条件付き書式」→「データバー」→「塗りつぶし(単色)」を選択します。

比率が高いほどデータバーが長くなりますので、相対的に費用が多く発生した月がわかります。

詳細な分析をするためのフィールドを追加する

支払金額が増加した理由を探るために別フィールドを追加してみます。
行ラベルのどれかの項目（説明では（株）プリントファクト）をダブルクリックすると「詳細データの表示」ダイアログボックスが開きますので、「内訳名」を選択すると下階層が追加表示されます。

左端の「＋、－」ボタンを押すことで、展開や折りたたむことができます。

分析には色々な方法がありますが、分析の基本は「比較」と「掘り下げる」ことです。
全体を見て比較して異常値を見つけ、その異常値を掘り下げる。
ピボットテーブルを利用することで、そのような分析が容易にできます。

「グループ化」機能も説明しておきましょう。
行ラベルの「支払先名」を削除し、「勘定科目名」を表示させます。
「10地代家賃」「15事務経費」を選択した状態で右クリックを押し、メニューから「グループ化」を選択します。

行ラベルボックスに「勘定科目2」が、行ラベルに「グループ1」が追加されました。

次のそれぞれもグループ化してください。
「K-10地代家賃」「K-11水道光熱費」「K-13通信料」「K-15事務経費」「K-16運送料」
「リース固定債務」「長期未払金」
「グループ2」と「グループ3」が表示されました。

それぞれの名称を変更します
「グループ1」→「販管費」
「グループ2」→「売上原価」
「グループ3」→「固定債務」

右クリックで「”勘定科目名2”の小計」をクリックします。
それぞれの小計が先頭行に追加されました。

毎月の支払いにおいて、どのグループの支出割合が多いかがわかります。

まとめ

ピボットテーブルがどのようなものかと、一部の便利な機能について説明しました。
ピボットテーブルが便利な点は、様々な角度から値を集計できるピボットテーブルは経理にはなくてはならないものです。
サンプルデータに解説で利用しなかった項目も用意してありますので、行ラベルや列ラベルを変えて、他にどのような集計ができるのかを試してみてください。

この内容は更新日時点の情報となります。掲載の情報は法改正などにより変更になっている可能性があります。