ピボットテーブルはとても便利な使い方ができるExcelの機能です。「ピボットテーブルは難しくて…」という思い込みは捨てましょう。作るのも使うのも基本テクニックを勉強すれば割りと簡単。集計やデータ分析の業務を段違いに効率化できる優れものです。ピボットテーブルを使いこなせれば応用する範囲が果てしなく広がります。ここではピボットテーブルの基本からちょっと上級の集計の技までを初心者にも分かりやすく図解していきます。
ピボットテーブルとは？ どんなとき使うの？
ピボットテーブルとは、マウス操作だけで様々なデータ分析用の集計表を立ちどころに作成してくれるExcelの機能です。視点を変えた集計表・グラフを作って多方向からのデータ分析が可能になります。
ピボットテーブルの「pivot」の語源は「回転軸」ですが、方向を転換する意味でも使われる英語です。「Pivot table」は「方向を変えて転換する集計表」を意味しています。
ピボットテーブルはどんなときに使うのでしょう？
例えば、以下のような日々の売上表があります。これを元データに必要な集計表を作って現状を分析することになったら、それがピボットテーブルを有効活用するときです。
ピボットテーブルを使えば、売上表のの元データから日付ごと、商品ごとの集計、曜日と商品のクロス集計といった異なる切り口の集計表をすぐに作成できます。
ピボットテーブルの作り方
ピボットテーブルを作る手順や構成を覚えるのは実際に機能を使ってみながら練習するのが早道だと思うので、まずはピボットテーブルを操作してクロス集計表を作ってみましょう。
ピボットテーブル作成前に！元データの作り方
ピボットテーブルの作り方は拍子抜けするほど簡単です。厄介なのは、むしろ集計に使う元データの作成と整備です。ピボットテーブルがうまくいかない場合、集計の元となるデータベースの不備が原因であることが多いので、ここはきっちりやっておきましょう。
ピボットテーブルの元データの表はデータベースの規則にしたがって作成されている必要があります。以下の項目をチェックして下さい。
- １行目に項目（列見出し）を置く➔表のタイトルなど余計なものを置かない
- ２行目からデータを入れ、１データは１行にまとめる
- 結合セルがあれば、解除する➔ 「ホーム」タブ「配置」の「セルを結合して中央揃え」をクリックして解除
- 商品名・会社名・担当者名などは同じ表記に統一する➔（例）ABC株式会社とABC（株）があればどちらかに統一／表記ゆれやミスを抽出などで実装
- 空白行・重複するデータがあれば修正または削除する➔重複データの削除などで実装
- 適切でない表示形式（数値であるべきなのに文字列）を設定していたら「右クリック➔セルの書式設定➔表示形式」で修正する
ピボットテーブル作成前に！元データをテーブル化するメリット
参照元データベースが普通の表のままでもピボットテーブルを作れますが、できればテーブル化しておくことをお勧めします。
データ表のどこかを選択して、「ホーム」タブ➔「スタイル」➔「テーブルとして書式設定」をクリック、一覧から好みのスタイルをクリック、範囲の確認画面で「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」。
- テーブル化した表はデータ管理が簡単
- テーブル化した表はデータを管理する機能が細やかに装備されており、表記ゆれ・重複の抽出がスムーズに行えます。
- テーブル化した元表から作ったピボットテーブルには(空白)が出ない
- 普通の表に集計行（または集計列）があると、作成したピボットテーブルに(空白)という表示が出ます。元表の範囲変更をするか、集計行を削除すれば消えますが、ちょっと面倒です。
テーブル化した表の集計行は「テーブルデザイン」タブの「集計行」にチェックして挿入されます。この集計行があっても、ピボットテーブルに(空白)はできません。
- テーブル化した元表はデータ修正後の更新が簡単
- データを追加・削除した場合、普通の表では「データソースの変更」で集計範囲の変更を指定し直さなければなりませんが、テーブル化してあれば「右クリック➔更新」または「ピボットテーブル分析➔データ➔更新ボタン」操作だけでピボットテーブルに反映されます。日常的にデータが追加される表では特にテーブル機能を使うメリットは大きいと思います。
ピボットテーブルを挿入する
さあ、ピボットテーブルを作ってみましょう。
元になる表（またはテーブル）内のセルをどれか一つ選択し、「挿入」タブのリボン左端にある「ピボットテーブル」ボタンをクリックします。
「ピボットテーブルの作成」ダイアログが開き、「テーブル/範囲」に元表のテーブル名が入っています。特定の範囲をドラッグで指定し直すこともできます。
ダイアログの下方に「ピボットテーブルレポートを配置する場所を選択してください」とあり、ピボットテーブルの作成場所を聞いています。デフォルトで「新規ワークシート」がオンになっているので、特段の理由がない限り、このままで「OK」します。
新しいシートが開き、左側にピボットテーブルが作成されるエリアが挿入され、右側に「ピボットテーブルのフィールド」という作業ウィンドウが開きました。
ピボットテーブルの大枠を作るフィールドの構成と基本操作
ピボットテーブルのフィールドの構成は下図のようになっています。
作業ウィンドウには元表の列の項目が並ぶ「フィールド リスト」と、「フィルター」「列」「行」「値」の各ボックスがあります。
「フィルター」はこの段階では用がありません。クロス集計表の大枠を構成するのは「列」「行」「値」の３つのボックスです。
ピボットテーブルのフィールドで行う基本の操作です。フィールドリストから項目名をそれぞれのボックスにドラッグして指定します。「列」と「行」に入るのは項目名。日付、商品名、地名など。「値」には数値が入ります。
「列」と「行」に配置するフィールドの選択は、何を分析したいかで自ずと決まってきます。例えば、新製品のバージョンごとに年代別の売筋を分析したいとか、系列店舗ごとに月別の売上を比較したいとか、目的がはっきりしているなら、縦軸（行）と横軸（列）に入れるフィールドを選択するのに迷うことはありません。ここでは曜日別・商品ごとのデータの個数を比較するピボットテーブルを作成してみることにしましょう。
元の表には７つのフィールド（列項目）があり、作業ウィンドウに並んだリストと対応しています。
この中で、「曜日」を「行」に、「施術メニュー」を「列」に、「施術数」を「値」に指定します。
フィールドリストからフィールド名をドラッグして、各ボックスに配置します。配置と同時にフィールド名にチェックが付きます。
ピボットテーブルのクロス集計表が作成されました。
ピボットテーブルは「レポートフィルター」「行ラベル」「列ラベル」「集計値」で構成されています。
- レポートフィルター
- 「レポートフィルター」は１行目にあり、データの抽出に使われます。フィルターを使わなければ何も表示されません。
- 行ラベル
- 作業ウィンドウの「行」に配置したフィールドに属するアイテム・縦軸の項目が並ぶエリアです。
- 列ラベル
- 作業ウィンドウの「列」に配置したフィールドに属するアイテム・横軸の項目が並ぶエリアです。
- 集計値
- 作業ウィンドウの「値」に配置したフィールドに属する数値・集計する値のエリアです。
作成後のピボットテーブルのレイアウトを修正する
作ったばかりのピボットテーブルはデザインも見やすさも今イチなので、レイアウトを修正しましょう。
- 集計セルに罫線や縞模様を設定する
- 作成直後のピボットテーブルは罫線がありません。罫線を引いた方が見やすくなると思ったら、ピボットテーブルのどこかを選択すると画面上部に出る「デザイン」タブ➔「ピボットテーブル スタイルのオプション」➔「縞模様」にチェックを入れます。
その上で、「デザイン」タブ➔「ピボットテーブル スタイル」をクリックして一覧を引き出し、集計範囲に色つきセルのないスタイルを選択します。
ピボットテーブルの集計セルに罫線が入りました。行にだけ罫線を引きたい場合は「縞模様（列）」のチェックを外します。
- 列ラベルのセル幅を調整する
- 作成直後のピボットテーブルは列ラベルのセル幅が一定でなく、狭くなっています。レポートのレイアウトが「コンパクト形式」になっているためです。
これをもう少し伸び伸びした表示に変えるには、「デザイン」タブの「レポートのレイアウト▼」をクリック。「アウトライン形式」か「表形式」を選択します。
列ラベルのセル幅が一定のサイズに広がり、集計値が見やすくなりました。
セル幅は普通の表のように手動で修正することもできますが、更新すると元に戻ります。「手動で整えたレイアウトを固定する設定」は以下で解説します。
ピボットテーブルを使う上で役立つピボットテーブルオプションの設定
ピボットテーブルを効率よく使っていくための「ピボットテーブルオプション」のおすすめ設定をご紹介します。
ピボットテーブル上で右クリック、「ピボットテーブル オプション」をクリックして開きます。
- レイアウトを固定する設定
- データを追加・削除してピボットテーブルを更新したり何か設定を変えたりすると、その度に列幅が伸縮し、レイアウトが崩れます。これを回避するためには「ピボットテーブル オプション」➔「レイアウトと書式」の「更新時に列幅を自動調整する」のチェックを外します。この設定により、次回の更新からは列幅も伸縮せず、レイアウトが固定されたままになります。
- ピボットテーブルの更新を自動にする設定
- 参照元のデータベースに追加・削除などの変更を加えた時は、その都度、手動で更新してピボットテーブルに反映させる必要があります。日常的にデータの追加がある場合は面倒なので、ピボットテーブルの自動更新を設定しておきます。
「ピボットテーブル オプション」➔「データ」➔「ピボットテーブルのデータ」➔「ファイルを開くときにデータを更新する」にチェックを入れます。
これで、ピボットテーブルのあるファイルを開く度に自動で更新されるようになります。
注意点が一つ。この設定では不用意にファイルを閉じると、次に開いた時点でピボットテーブルの集計が置き換わります。古いデータの集計表を資料として残したいといった場合には、更新前のピボットテーブルをコピー、別シートに「値の貼り付け」で複製しておきましょう。
ピボットテーブルの使い方【実践１】階層化・グループ化を使った基本のデータ分析
ピボットテーブルは様々な切り口のデータ分析を行う目的で使われます。行ラベルと列ラベルを入れ替えたり、フィールド名を置き換えたりして異なった集計表を簡単に作れます。ただ、デザインもレイアウトも違う集計表を複数並べると、返って分かりにくくなることもあります。
ピボットテーブルには列・行に「都道府県」➔「市区町村」➔「店舗」というような階層を持ったグループを作り、クリックひとつで下位のフィールドを展開させたり折りたたんだりできる仕組みがあります。フィールドに上下関係を持たせ、表示・非表示を切り替えることで集計の視点が柔軟に伸縮するので、一つのピボットテーブル内で、より分かりやすくデータ分析する使い方ができます。
やり方は簡単。作業ウィンドウの「行」「列」ボックスへ複数のフィールドをドラッグ・ドロップしていけば、その上下関係がそのまま階層の上下になります。順序の入れ替えもドラッグでできますし、削除したければ作業ウィンドウの枠外へドラッグすれば消えます。
。
ピボットテーブルでは「日付」は「年」「四半期」で自動グループ化される
「日付」フィールドを作業ウィンドウの行エリアへドラッグすると、元の表に存在しなくても「年」「四半期」「月」などの上位フィールドが自動で生成されます。実装頻度が高いので、Excelの方で勝手に「グループ化」してくれるのです。（元表の「日付」フィールドに属すデータが年度を跨いでいない場合は、自動グループ化で生成される上位フィールドは「月」だけになります）
ピボットテーブルは「年」➔「四半期」の順に階層化され、日付は「月」単位でまとめられています。これだと、日別のデータ分析ができません。
「四半期」のくくりが不要というだけなら、これを作業ウィンドウの「行」から削除するだけで日別の行ラベルが表示されます。グループの構造を見直す必要がある場合は、グループ化された日付の項目セルをどれか選択して、「ピボットテーブル分析」➔「グループ」➔「フィールドのグループ化」ボタンをクリック。
「グループ化」ダイアログボックスが開いたら、行ラベルに表示させたい単位をクリックして選択します。選択の解除もクリックでできます。
ピボットテーブルの使い方【実践２】ちょっと上級の集計方法
ピボットテーブルでは、「値フィールドの設定」の「集計方法」と「計算の種類」で様々な集計列を表示させることができます。
「合計・平均・最大（小）値・標準偏差」を横並び集計してデータ分析に使う
「値フィールドの設定」の「集計方法」を使って、「合計」の隣に「平均」「最大（小）値」「標準偏差」を横並びに表示させる方法です。
あるクラスの５科目のテストの点数のデータ表からピボットテーブルを作成しました。このピボットテーブルの使い方には、下図のように行にだけ項目を並べ、列項目のないシンプルな構成が適しています。
「値」エリアの「合計／点数」の下に同じフィールドを４回ドラッグして配置します。
「合計／点数２」をクリック、メニューから「値フィールドの設定」を開きます。
「集計方法」を「平均」に変更、フィールドの名前を「平均点」に修正します。
「合計／点数３」を「最大値」、「合計／点数４」を「最小値」、「合計／点数５」を「標準偏差」に変更。それぞれ「最高点」「最低点」「標準偏差」と名称を修正。
「合計」「平均」「最大値」「最小値」「標準偏差」を横並びできました。このように指針となる複数の集計値を並べるピボットテーブルの使い方は、見やすく、分りやすい集計表を作るのにとても有効です。実務に活用してみて下さい。
「構成比・累計・前月比・前日比」を集計してデータ分析に使う
「値フィールドの設定」の「計算の種類」を使って、「構成比」「累計」「前月比」「前日比」をピボットテーブルに追加します。
- 商品ごとの構成比を集計
- 個別の商品の売上が全体の売上に占める比率を集計するため、商品名を「行ラベル」にしたピボットテーブルを作成し、作業ウィンドウの「値」エリアの「金額」の下に同じ「金額」フィールドをドラッグで配置します。ピボットテーブルの方にも「合計／金額２」の集計列ができます。
作業ウィンドウの「合計／金額２」をクリック、メニューから「値フィールドの設定」を開きます。
「値フィールドの設定」は「集計方法」と「計算の種類」の２つのタブに分かれています。今回、設定に使うのは「計算の種類」の方。こちらに切り替えて、「計算なし」になっているところを「列集計に対する比率」に変え、「名前の指定」も「合計／金額２」から「構成比」と入力し直します。
比率の小数点以下の桁数を１桁に収めたいという場合は、画面左下にある「表示形式」をクリック。「セルの書式設定」ー「表示形式」の「パーセンテージ」で、「小数点以下の桁数」を▼を押して「１」に変えます。
ピボットテーブルに構成比の集計列が表示されました。
- 売上金額の累計を集計
- 「日」「月」を追って売上の累計を表示するためには、まず、「日付」か「月」フィールドを「行ラベル」にしたピボットテーブルを以下のように作成しています。
前章の「構成比」の時と同じに、「値」エリアの「金額」フィールドをドラッグで追加し、「値フィールドの設定」を開きます。
「計算の種類」を「累計」に、「基準フィールド」を「月」に設定した後で、「名前の指定」のボックス内を「累計」に入力し直します。
ピボットテーブルに「累計」の集計列が追加されました。
- 売上の前月比・前日比を集計
- 前月比・前日比も累計と操作はほとんど変わりません 。作業ウィンドウで「値」エリアにフィールドを増やし、「値フィールドの設定」で「計算の種類」と「基準フィールド」を選びます。ただ１箇所異なるのは、「基準アイテム」を指定する必要があるという点。
前月比・前日比・前年比は、この「基準アイテム」を「（前の値）」と指定します。
そして、「計算の種類」は「基準値との差分の比率」を選択し、「基準フィールド」は前月比の場合は「月」を選択します。「基準フィールドの月の項目ごとに（前の値）との差額の比率」を集計するよう求めているわけです。
ピボットテーブルに「前月比」の集計列が追加されました。
「前日比」は「基準フィールド」を「日付」にし、名前を「前日比」にします。
商品の項目ごとの「前日比」が集計されました。
このように、煩雑な操作も関数も無しに難しい集計を簡単に表示できるのがピボットテーブルの最大の利点です。これらの使い方をマスターして、ビジネスに活用して下さい。
ピボットテーブルの使い方【実践３】数式を使った、もっと上級の集計方法
ピボットテーブルには数式を指定してピボットテーブルに独自の集計列を表示させる機能があります。構成比や前月比は「計算の種類」にリストアップされていますが、リストにない計算でも、集計フィールドとして任意の数式をピボットテーブルに挿入できるのです。
集計フィールドで消費税額を集計する
商品と金額を対応させたピボットテーブルを使って消費税額の集計列を表示させます。
「ピボットテーブル 分析」➔「計算方法」グループ➔「フィールド/アイテム/セット▼」をクリック、すぐ下に表示される「集計フィールド」をクリックします。
「集計フィールドの挿入」ダイアログが開いたら、「消費税」と入力してフィールドに名前を付けます。
「数式」のボックス内の「0」を消して「=」の右横にカーソルを置き、フィールド一覧の「金額」をダブルクリックするか、選択して「フィールドの挿入」ボタンをクリックします。
続けて、「*0.1」と入力します。「=金額*0.1」は「金額✕消費税率」という数式を入力したことになります。（消費税率10％：2023年現在）
「OK」で、ピボットテーブルに「消費税」の金額が追加されました。
集計値同士を計算して「税込金額」を集計する
集計フィールドでは集計値同士を計算させる数式を指定することもできます。
例として、「合計／金額」と「消費税額」の集計値を加算して、「税込金額」をピボットテーブルに追加してみましょう。
「フィールド/アイテム/セット▼」➔「集計フィールド」をクリックして「集計フィールドの挿入」ダイアログを開き、「名前」を「消費税込金額」に変更、「数式」の「=」の後にカーソルを置いて、フィールド一覧の「金額」をダブルクリック。続けて、「＋」と入力します。
その後に続けて、今度はフィールド一覧の「消費税」をダブルクリックします。「数式」欄には「=金額+消費税」と入りました。
「OK」をクリックすると、ピボットテーブルに「合計／消費税込金額」が追加されますが、見出しが長すぎるので、数式バーで「税込金額」に修正しました。
※見出しの名前を変更する時は、「集計フィールドの挿入」ダイアログで入力した名前を最低１文字変えます。同じ名前にするとアラートが出て拒否されます。
基本的な集計方法の多くは「計算の種類」のリストに入っていますが、リストにない集計を行いたい場合に「集計フィールド」を使います。
集計フィールドで計算できるのは列同士－横方向の集計値です。クロス集計で縦方向に行同士の集計値を計算するには「集計アイテム」を使います。集計アイテムを使うと、更に細かい集計値同士の計算が可能です。
ピボットテーブルの作り方は簡単！使い方が分かればExcel上級者にグッと近づく
ピボットテーブルはExcelの極めて優秀な機能です。ピボットテーブルを作るのは思うほど難しくはありません。使い方の基本をしっかり学んだ上で、業務に少しずつ実践的な集計方法を取り入れていきましょう。ピボットテーブルは視点の異なる集計表を簡単に生成してくれますが、数多く作るよりは、ここで図解したように集計列を横に追加したり、数式を組み込んで集計値同士を計算させたりする使い方がお勧めです。ピボットテーブルはできるだけ見やすく、シンプルな構成にして、データ分析に役立てましょう。