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

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

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

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

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

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

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

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

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

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

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

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

2_「ピボットテーブルの作成」のダイアログボックスが開きます

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

3_新規ワークシートが作成され、シート上にピボットテーブル表示エリアが、右側にピボットテーブルのフィールド情報が表示されます
それぞれの名称
A:ピボットテーブル表示エリア
B:フィールドセレクション
C:レイアウトセレクション
D:レポートフィルターボックス
E:列ラベルボックス
F:行ラベルボックス
G:値ボックス

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

4_フィールドセレクションからフィールドを選択して、各ボックスにドロップします

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

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

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

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

6_列ラベル右横の「▼」をクリックし、フィルターメニューを表示させます。

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

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

データのルール
・先頭行を1行、列見出しとして設定する
・列見出しに2回以上同じ名称を使用しない
・列には同じ形式のデータを入力する。(日付であれば日付のみ、数値であれば数値のみ)
・1列には1列分のデータを入力する
・1行には1件分のデータを入力する

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

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

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

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

>>経費精算にお悩みの経理の方へ、日本一選ばれている「経費精算システム」

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

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

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

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

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

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

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

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

11_総支払金額に対するその月の支払金額の割合が算出されます。

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

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

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

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

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

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

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

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

15_支払金額が増加した理由を探るために別フィールドを追加してみます。

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

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

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

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

17_「グループ化」機能も説明しておきましょう。

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

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

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

19_次のそれぞれもグループ化してください。

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

20_それぞれの名称を変更します

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

21_それぞれの小計が先頭行に追加されました。

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

まとめ

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

>>経費精算にお悩みの経理の方へ、日本一選ばれている「経費精算システム」

「経理プラス」無料メルマガ会員登録はこちら

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

経費精算システム「楽楽精算」

URLをクリップボードにコピーしました

● 著者

小栗勇人

小栗勇人

1980年生まれ。上場企業と上場企業子会社で経理を10年経験。ExcelやAccessの活用、クラウドサービスの導入、社内基幹システムの構築など、経理業務だけでなく、会社全体を効率化させることを日々実践中。運営ブログ「経理と事務の効率化」をきっかけにExcelの本『経理の仕事がサクサク進むExcel超活用術』を出版。

経理と事務の効率化