【動画付き #29】Excelのピボットテーブルを徹底解説!基礎をマスターして、ドラッグ&ドロップのみでデータ分析が完了

Excel#29アイキャッチ
この記事を読んでわかること
  • ピボットテーブルとはどのような機能なのか
  • ピボットテーブルの作成方法
  • 表中の数値をより見やすくするテクニック

Excelのピボットテーブルは、関数を使わずに簡単にデータを分析できる便利な機能です。

しかし、慣れていない方にとっては独特な操作手順などから苦手意識を感じることもあるのではないでしょうか。

ピボットテーブルは、一度基礎や操作方法を覚えることで、簡単にデータ分析を行うことができます。

本記事では、ピボットテーブル解説の第一弾として、「ピボットテーブルの基礎」についてご紹介します。

本記事はYouTube『Youseful / 人材教育の教科書』チャンネルより、以下の動画を記事にまとめたものです。動画だけではなく、テキストで復習したい方もいるはず。全38の記事と動画を組み合わせて、Excelの基本操作をマスターしていきましょう。

▼前回の記事では、大量のデータから必要なデータのみに一瞬で絞り込む「フィルター機能」についてご紹介しています。

目次

ピボットテーブルとは

ピボットテーブルは、ドラッグ&ドロップをするだけで大量のデータを一瞬で分析できる機能です。

多様な関数を覚えたりしなくてもデータ分析が可能になります。

ピボットテーブルの使い方

以下のワークシートを使ってご紹介します。

「どの従業員が(ID・氏名)」「いつ(日付)」「いくら(売上)」「何を(商品名)」売ったのかをまとめたデータベースになっています。

Excel#29の1

今回は、上記のデータベースとピボットテーブルを使って、以下のような表を作成します。

「いつ(日付)」(縦軸)と「何を(商品名)」(横軸)を軸に、「いくら(売上)」売ったのかがわかりやすくまとまっています。

Excel#29の2

ピボットテーブルを使ってデータベースから表を作成する

ピボットテーブル レポートを作成する

ピボットテーブルを使って、前述のデータベースから分析表を作成します。

手順を見ていきましょう。

手順
  1. データベース内のいずれかのセルを選択
  2. メニューバー>挿入>「ピボットテーブル」(一番左)をクリック
    ~ピボットテーブルの作成ウィンドウが開く~
  3. テーブル/範囲(T)」にデータの範囲を記入
    ※[シート名][始点のセル番地][終点のセル番地]
    (例:Sheet!$B$2:$F$999)
  4. ピボットテーブル レポートを配置する場所を選択する
    新規のワークシート(N):レポートを出力する際に、新しいシートを自動で追加してレポートを表示
    ※本記事ではこちらを選択
    既存のワークシート(E):指定した既存のシートにレポートを表示
  5. [OK]をクリック
    ~ピボットテーブル レポートが作成される~
Excel#29の3
2. 「ピボットテーブル」(一番左)をクリック
Excel#29の4
3. 「テーブル/範囲(T)」にデータの範囲を記入 4. ピボットテーブル レポートを配置する場所を選択する

これで、ブック(Excelファイル)上に、指定したデータ範囲が対象のピボットテーブル レポートを作成することができました。

「ピボットテーブル〇[数字]」と記載された欄(シート左下)と「ピボットテーブル…」(シート左側)と記載されたダイアログが表示されています。

Excel#29の5
「ピボットテーブル〇[数字]」と記載された欄(シート左下)
Excel#29の6
「ピボットテーブル…」(シート左側)

ピボットテーブル レポートを編集する

上記では、表を作成する基盤が出来上がった状態です。しかし、ピボットテーブルは自分で列(横軸)、行(縦軸)、値(分析する値)を設定しなければ表は完成しません。

そこで次に、「ピボットテーブル…」(シート左側)レポートを自分がほしい項目がまとまった表に編集していきます。


たとえば、「いつ、どの商品が、どれだけ売れたのか」を示す表を作成するために、

  • 「日付」を行
  • 「商品名」を列
  • 売上(金額)を値

に設定するとします。

手順
  1. 項目のリストから、「日付」を「」の欄にドラッグ&ドロップする
    ~「ピボットテーブル〇[数字]」と記載された欄に年代の行ラベル(「2018年」「2019年」「2020年」「総計」)が表示される~
  2. 項目のリストから、「商品名」を「」の欄にドラッグ&ドロップする
    ~「ピボットテーブル〇[数字]」と記載された欄に商品名の列ラベル(「Excel Pro」「ブログ広告枠」「動画広告枠」「総計」)が表示される~
  3. 項目のリストから、「売上」を「」の欄にドラッグ&ドロップする
    ~「ピボットテーブル〇[数字]」と記載された欄に値が入る~
Excel#29の7
1. 項目のリストから、「日付」を「行」の欄にドラッグ&ドロップする
Excel#29の8
1. ~「ピボットテーブル〇[数字]」と記載された欄に年代の行ラベルが表示される~
Excel#29の9
2. 項目のリストから、「商品名」を「列」の欄にドラッグ&ドロップする
Excel#29の10
2. ~「ピボットテーブル〇[数字]」と記載された欄に商品名の列ラベルが表示される~
Excel#29の11
3. 項目のリストから、「売上」を「値」の欄にドラッグ&ドロップする
Excel#29の12
3. ~「ピボットテーブル〇[数字]」と記載された欄に値が入る~

これで、「いつ、どの商品が、どれだけ売れたのか」を示す表を作成できました。

合わせて覚えたい数字表記のテクニック2選

大きな数値にカンマ(,)をうつ

しかし、これでは大きな数字がそのまま記載されていて、単位などが読みにくくなっています。

そこで、3桁ずつに「,(コンマ)」をうちましょう。

方法は3パターンあります。

手順1
  1. コンマをうちたいセル範囲を選択
  2. 指定した範囲にコンマをうつ
    ショートカット:Ctrl+Shift+1
ショートカット Ctrl+Shift+1 で、指定したセルの範囲にコンマを打つことができます。
手順2
  1. コンマをうちたいセル範囲を選択
  2. 1000単位でコンマをうつ
    ショートカット:Alt+H+K
ショートカット Alt+H+K で、指定したセルの範囲に1000単位でコンマを打つことができます。
手順3
  1. コンマをうちたいセル範囲を選択
  2. セルの書式設定のダイアログボックスを表示する
    ショートカット:Ctrl+1
  3. 数値」をクリック
  4. 桁区切り(,)を使用する(U)」に✓をいれる
  5. [OK]をクリック
Excel#29の15
3.「数値」をクリック 4.「桁区切り(,)を使用する(U)」に✓をいれる

いずれかの操作を行うと、数値に1000単位で「,」を打つことができ、大きな数字でも見やすくなります。

ショートカットまとめ
  • 指定された範囲にコンマをうつ:Ctrl+Shit+1
  • 1000単位でコンマをうつ:Alt+H+K
  • セルの書式設定ダイアログを開く:Ctrl+1

値を比率(パーセンテージ)で表示する

値の欄は、初期設定では数値で表示されますが、比率(%)で表示することも可能です。

手順を見ていきましょう。

手順
  1. 数値→%に変換したいセルのいずれかを選択する
  2. メニューバー>ピボットテーブル ツール>アクティブなフィールド>「フィールドの設定」をクリック
    ~値フィールドの設定ウィンドウが開く~
  3. 計算の種類」をクリック
  4. 計算の種類(A)」のドロップダウンをクリック
    ~表示形式の選択肢が表示される~
  5. 任意の表示形式をクリック
    – 総計に対する比率:全体を母数をした比率
    – 行集計に対する比率:年間総売り上げを母数とした商品の売上額の比率
Excel#29の16
2. 「フィールドの設定」をクリック
Excel#29の17
3. 「計算の種類」をクリック~ 5. 任意の表示形式をクリック

これで、値を比率(%)に変換することができました。
こちらの技もきちんとマスターして、より見やすく役に立つ表を作ることができるようにしておきましょう。

まとめ

本記事では、ピボットテーブルの基礎についてご紹介しました。
ピボットテーブル基礎のポイントは3つです。

  1. ピボットテーブルは大量のデータを分析するためにデータベース形式の表を用いなければならない
  2. 項目(見出し)をドラッグ&ドロップして、集計表を作ることができる
  3. 集計表は自由にカスタマイズできる

次回の記事では、ピボットテーブルの重要な概念である「ドリルダウン・グループ化」についてご紹介します。
▼次回の記事はこちら

気に入ったらシェア!
  • URLをコピーしました!
目次