役立つ! 総務マガジン

Excelピボットテーブルの仕組みを完全理解

一見すると複雑なピボットテーブルも、仕組みが分かればもっと使いこなせる

第12回では、項目やデータ量の多い表を集計するために、ピボットテーブルを作成する基本の操作を一通り行いました。今回はピボットテーブルの仕組みをしっかりと理解できるように詳しく解説。あわせて、ピボットテーブル活用のカギとなる、集計軸の入れ替えも解説します。

[2018年10月22日公開]

特集:Excelピボットテーブル

データの集計を効率化する「ピボットテーブル」機能を全4回でたっぷり特集! 独特の仕組みを理解して、総務のさまざまな業務で使いこなしましょう。

集計前の表と比較してピボットテーブルの仕組みを理解する

第12回で作成したピボットテーブルを、元のデータとあらためて比べてみましょう。ピボットテーブルは、元のデータの任意の項目を[列]領域および[行]領域に設定して、クロス集計を行います。

元のデータとの見た目は大きく変わりますが、次の図のように、元のデータから二つの項目が行と列に設定されていることが分かれば、戸惑うことはありません。

元のデータの「持出消耗品名」の品名がピボットテーブルの[行ラベル]に、「部署」の部署名が[列ラベル]になっています。そして、[値]領域に設定した「数量」が、品名と部署別に集計されています。

ピボットテーブルの行と列に入る項目を入れ替える

ピボットテーブルは、自在に集計軸を変更できるのが最大の特徴です。上記のピボットテーブルでは「品名」と「部署」のクロス集計でしたが、これを「記入日」と「部署」のクロス集計に変更してみましょう。このように変更すれば、一定期間ごとの各部署の消耗品利用の傾向が分かるようになります。

集計軸の入れ替えはマウス操作で簡単にできます。しかも、元のデータには直接変更を加えないため、安心して何回でも集計をやり直せるのも利点です。

ピボットテーブルの[行ラベル]領域に入る項目を「持出消耗品名」から「記入日」に変更するには、以下のように操作します。

(1)「持出消耗品名」項目をピボットテーブルから削除する

[ピボットテーブルのフィールド]の[行]領域に設定している「持出消耗品名」項目を、[ピボットテーブルのフィールド]の外にドラッグします。

(2)ピボットテーブルから「持出消耗品名」が消えた

[ピボットテーブルのフィールド]の[行]領域が空白になり、ワークシート上に表示されているピボットテーブルからは行ラベルが削除されました。集計できなくなったため、値も表示されなくなっています。

(3)「記入日」項目をピボットテーブルに追加する

[ピボットテーブルのフィールド]にある「記入日」項目を[行]領域にドラッグします。

(4)「記入日」項目がピボットテーブルに表示された

ピボットテーブルの[行ラベル]に、「記入日」項目が挿入され、自動的に「月」ごとにグループ化された状態で表示されました。長い期間の日付のデータを挿入すると、集計しやすいように、自動的に月ごとのグループ化が行われます。[ピボットテーブルのフィールド]の[行]領域にも、「記入日」に加えて「月」が自動的に挿入されています。

これで月ごと、部署ごとの消耗品の持ち出し数を集計するピボットテーブルができました。

集計する値を「数量」から「金額」に変更する

次に、月ごと、部署ごとの消耗品の「持ち出し数」ではなく、「持ち出した消耗品の金額」を集計するようにしてみましょう。このようにすれば、経費の変動がよく分かるようになります。

次のように操作して、ピボットテーブルの[値]を変更しましょう。

(1)ピボットテーブルから「数量」を削除する

[ピボットテーブルのフィールド]の[値]領域に設定されている「合計/数量」項目を[ピボットテーブルのフィールド]の外にドラッグします。

(2)「金額」項目をピボットテーブルに追加する

ピボットテーブルに「数量」が表示されなくなったのを確認してから、[ピボットテーブルのフィールド]で、「金額」項目を[値]領域にドラッグします。

(3)「金額」の合計が集計された

ピボットテーブルに「合計/金額」が表示され、「金額」の合計が集計されました。

(4)「4月」グループを展開する

次に、4月の日ごとの金額を確認してみましょう。[4月]のアウトライン記号([+])をクリックすると[-]に表示が変わり、[4月]のグループが展開されて日ごとの集計結果が表示されました。[-]をクリックすると再びグループが折り畳まれます。

フィルター機能でラクラク抽出

ここまで集計軸の入れ替えを行ってきましたが、[ピボットテーブルのフィールド]には、まだ利用していない[フィルター]という領域もあります。この使い方をマスターしましょう。

[フィルター]領域に項目を設定すると、項目から集計対象を選択できるようになります。例えば「持出消耗品名」項目を設定すれば、どの品名を集計の対象とするかを選べます。

以下の手順では[フィルター]領域に「持出消耗品名」項目を設定し、集計対象の品名品を選ぶ方法を解説します。

(1)「持出消耗品名」項目を[フィルター]領域に設定する

[ピボットテーブルのフィールド]で、「持出消耗品名」項目を[フィルター]領域にドラッグします。

(2)フィルターが設定された

ピボットテーブルの上に[持出消耗品名(すべて)]が表示されました。この部分を「レポートフィルター」または「フィルター」と呼びます。

(3)フィルターを利用する

フィルターを利用し、ここでは「クリアーファイル(A4サイズ半透明)」だけを集計対象とするようにしましょう。フィルターの右にある[▼]をクリックし、表示された項目の一覧から「クリアーファイル(A4サイズ半透明)」をクリックして選択します。その後[OK]ボタンをクリックします。

(4)フィルターが適用された

フィルターが適用され、月ごと、部署ごとの「クリアーファイル(A4サイズ半透明)」にかかった費用が表示されました。このとき、フィルターの[▼]が漏斗(じょうご)のアイコンになり、フィルターを適用中であることを表します。

(5)複数の項目を抽出する

フィルターで複数の項目を選択する場合は、一覧で[複数のアイテムを選択]にチェックマークを付けます。その後、選択したい項目にチェックマークを付けていきます。ここでは「コピー用紙(A3)」「コピー用紙(A4)」「コピー用紙(B5)」の三つにチェックマークを付けて、[OK]をクリックします。

(6)複数の項目が抽出された

3種類あるコピー用紙にかかった金額の合計が集計されました。フィルターでは[持出消耗品名(複数のアイテム)]と表示され、複数の項目が選択された状態であることが分かります。

「経費が多い部署は?」「何の経費が多い?」などの分析に活躍

今回はピボットテーブルの仕組みを理解したうえで、集計軸を品名ごとから月ごとに入れ替えたり、集計する値を数量から金額に入れ替えたりして、異なる軸での集計を行いました。さらに、フィルターを利用して、対象を抽出する(品名を選ぶ)ことも行いました。

このように、ピボットテーブルは集計軸をさまざまに入れ替えたり、特定のデータだけを抽出したりした集計が簡単に行えます。今回のサンプルのような消耗品持ち出しのデータから「どの部署がどの消耗品をよく使っているか?」「この消耗品の消費が増える時期はいつか?」といったことを調べることができ、消耗品が不足する原因を探したり、データに基づいて適切な消耗品の購入時期や数量を調整したりと、業務の改善に役立ちます。

「データ分析」と聞くと難しそうだと思う人も多いかもしれませんが、今回行った作業は、まさにデータ分析の一種です。マウス操作でも簡単にできて、元のデータを見ているだけでは難しかった新発見があります。これまでデータ分析やピボットテーブルに苦手意識があった人も、ぜひ試してみてください。

次回はさらに詳細な分析のために、「スライサー」と「グループ化」、そして「タイムライン」という三種類の抽出・集計の機能を解説します。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事