役立つ! 総務マガジン

Excelのピボットテーブルでデータを自在に集計

金額や時間の集計が効率的になるピボットテーブル機能を使ってみよう

備品の発注や、勤怠時間の管理など、総務ではデータをまとめて集計する場面が多くあります。関数や小計機能よりも簡単に使えて便利なピボットテーブル機能で、ササッと集計してしまいましょう。

[2018年10月9日公開]

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

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

今後の更新予定

  • 第14回 Excelピボットテーブルの便利な抽出機能(11月12日公開予定)
  • 第15回 Excelピボットグラフでデータを「見える化」(11月26日公開予定)

データの集計はピボットテーブルにお任せ

入力したデータを、どのように集計していますか? 表に入力された数値の合計を求めるだけなら、SUM関数などを使うのが適切です。各部署から集めた大規模なデータを集計する場合には[データ]タブにある[小計]機能も役立ちます。

しかし、単に行や列の合計を求めるだけではない場合、例えば「消耗品の品名ごと、部署ごとの利用数を集計したい」といった場合には、通常の方法では部署や品名ごとに表を分けたりしなければならず、大変な手間がかかってしまいます。

このような高度な集計は、Excelが持っているピボットテーブル機能を使いましょう。元のデータから自在に集計軸を設定し、複雑な集計もシンプルな操作で行えるのがピボットテーブルの特徴です。「ピボット」(pivot)とは「旋回軸」や「方向転換」の意味で、集計の軸を自在に切り替えられることを表します。

通常のワークシートは動きが異なるので、最初はとっつきにくいと思うかもしれません。しかし、マウスでドラッグ・アンド・ドロップするだけで作業できますし、作業の結果はすぐに反映され、少し使ってみると分かりやすさを実感できる機能です。

今回から4回に分けてピボットテーブルの使い方をしっかりと説明していきます。まずは、次に解説する手順を試して、便利さを実感してください。

ピボットテーブルは[挿入]タブから作成

ここでは1カ月分の「消耗品発注表」のデータを基に、「持出消耗品名」および「部署」ごとに持ち出し数を集計します。通常の方法ではデータを加工する煩雑な工程が必要になってしまいますが、ピボットテーブルを使えば簡単です。

(1)ピボットテーブルで集計したいワークシートを開く

始めに、集計したいデータがあるワークシートを開きます。ここでは、第10回で社内の各部署の消耗品持ち出しを一つのワークシートにまとめた「shuukei」シートを開きました。

(2)リスト内の一つのセルを選択し、ピボットテーブルを挿入する

集計したいデータがある表の中の、一つのセルをクリックして選択します。このとき複数のセルを選択しないように注意しましょう。その後、[挿入]タブの[ピボットテーブル]をクリックします。

(3)データの範囲を確認する

ピボットテーブルの設定を行うダイアログボックスが開きます。選択されたテーブルの範囲が正しいことを確認して[OK]をクリックしましょう。ダイアログボックスが表示された状態でも、背後に表示されているワークシートをスクロールすると、選択された範囲(明滅する点線で囲まれた範囲)をご確認いただけます。

(4)新しいワークシートに空のピボットテーブルが挿入された

「Sheet1」という名前の新しいワークシートが作成され、空のピボットテーブルが挿入されました。画面右の作業ウィンドウに[ピボットテーブルのフィールド]が表示されたことを確認してください。

項目をドラッグして集計軸を設定

ピボットテーブルを挿入すると、最初は何もデータの入っていない空のピボットテーブルが表示されます。ここにデータを追加するには、右側の[ピボットテーブルのフィールド]を操作していきます。

(1)[行ラベル]に「持出消耗品名」を設定する

[ピボットテーブルのフィールド]上の「持出消耗品名」の上にマウスカーソルを移動させ、[行]領域にドラッグします。

(2)[行ラベル]に「持出消耗品名」項目が追加された

[行ラベル]に「持出消耗品名」項目が追加され、データが表示されました。

(3)[列ラベル]に「部署」を設定する

続けて、[ピボットテーブルのフィールド]上の「部署」項目を[列]領域にドラッグします。

(4)[列ラベル]に「部署」項目が追加された

[列ラベル]に「部署」項目が追加されました。この画像では、分かりやすくB列から表示させています。

(5)[値]に「数量」を設定する

続けて、[ピボットテーブルのフィールド]上の「数量」項目を[値]領域にドラッグします。

(6)[合計/数量]に「数量」と表示された

ピボットテーブルの[合計/数量]に数値が入りました。

(7)ピボットテーブルが完成した

[行ラベル]に「持出消耗品名」、[列ラベル]に「部署」、[合計/数量]に「数量」項目を設定できました。

単なるデータの塊もピボットテーブルですっきり分かりやすく

ここで、手を加える前のデータ「shuukei」ワークシートと、作成されたピボットテーブルを比べてみましょう。

「shuukei」ワークシート

元のデータは一列になっているため、「持出消耗品名」や「部署」など一つの軸での集計はできますが、二軸の集計は困難です。

作成したピボットテーブル

ピボットテーブルでは、消耗品の部署ごと、品名ごとという二軸での集計ができています。表の一番下には部署ごとの利用した消耗品の合計、一番右には品名ごとの合計も表示されます。

ピボットテーブルを使えば複雑な集計作業も簡単

今回行った「持出消耗品名」と「部署」のように、二つの軸を掛け合わせた集計は「クロス集計」と呼び、データを深く分析するために使われる手法です。ピボットテーブルは、クロス集計に必須の機能だといえます。

また、元のデータはそのままで残して作成できるのも、ピボットテーブルの特徴です。多少操作を失敗しても、データが消えてしまう心配はありません。

今回は、基本的なピボットテーブルの作成だけを行いましたが、次回はピボットテーブルの詳細な機能や、より深い分析ができる機能も説明します。集計軸の切り替え(ピボット)操作にもチャレンジしてみましょう。

協力メディア

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

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

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

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

Excel研修の詳細情報を見る

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

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

Office 365 について見る

おすすめの記事