2/27(Tue)

今日の生放送

ピボットテーブルの使い方とは?初心者にも分かりやすく解説!

<目次>
1:ボットテーブルとは?
2:ピボットテーブルの構成要素
3:ピボットテーブルの基本の使い方
4:ピボットテーブルで詳細なデータ分析をする方法
5:ピボットテーブルの小技と便利な機能
6:ピボットテーブルを作成する時の注意点
7:まとめ

企業において非常に高いシェアを誇るオフィスソフトが、マイクロソフトのExcelです。様々な管理表や報告書、レポートの作成などで日常的に利用している方も多いでしょう。しかし、業務をしながら覚えることが多いため「機能を使いこなせていない」「効率的な活用方法がわからない」などの悩みの声もあるようです。

ここではエクセル機能のうち、複雑な数式なしにデータの整理や分析ができる、ピボットテーブルの使い方をわかりやすく解説します。

Schooオリジナル授業『Excelピボットテーブルのビジネス活用法』を参考にした内容ですので、授業を併せて受講すれば、ピボットテーブルを使ったダッシュボード(リアルタイムのデータを可視化する表)がスムーズに作れるようになるでしょう。

Excelピボットテーブルのビジネス活用法

< コース概要 >

Excelのピボットテーブルを使って、ダッシュボード (表の可視化を簡単に行えるシート) を作る過程を学ぶ全4回のコース授業です。

収集したデータを業務効率化や事業の成長に役立てたいと考えている方、データ分析人材に関するキャリアに興味がある方にぴったりの内容になっています。

先生プロフィール

木村 幸子

木村 幸子(きむら さちこ)
テクニカルライター、IT Olive代表。電機メーカーでのマニュアル開発業務、編集プロダクション勤務を経て独立。講習で教えた人数は8,000人、Office関連の著書は50冊を超える。近著に「Excelピボットテーブル データ集計・分析の「引き出し」が増える本(翔泳社)」「スピードマスター 1時間でわかる エクセルVLOOKUP関数[改訂2版](技術評論社)」「もっと早く知りたかった! Excel 暗黙のルール(マイナビ出版)」など。

 

ピボットテーブルとは?

ピボットテーブル とは

 

どんな機能?

ピボットテーブルとは売上一覧表などのデータ(元表)を項目別に自動集計するExcelの機能です。例えば、上図のように売上一覧表から顧客別の売上や商品別の売上を簡単に集計できます。ピボットテーブルは主に次の2つの用途で使われます。

  • ・集計したデータを項目ごとに整理して、分析する
  • ・共有されたデータの構造を理解する

企業内で収集・蓄積されたデータは分析の担当者が整理しない限り、データが示す意味や、企業が抱える課題は明確になりません。ピボットテーブルはデータを項目ごとに分類し、項目の組み合わせによってデータを分析(クロス集計)できる機能なのです。

<クロス集計とは>

クロス集計とは2つ以上のデータ項目の掛け合わせでデータに意味付けまたは分析をする、データ集計の方法です。「商品別の売上」「年齢別の合格率」などのように、収集したデータをより詳細に分析する時に使用します。クロス集計には以下のようなメリットがあります。

  • ・集計したデータ項目が互いにどのような影響を与えているのか分かりやすくなる
  • ・詳細なデータ分析と分析結果の可視化が同時にできる

一方、クロス集計には以下のようなデメリットもあるため、クロス集計の特性を理解した上で、ピボットテーブルを用いたデータ分析をすると良いでしょう。

  • ・項目を細分化させすぎるとデータ数が少なくなり、分析結果に統計的意味がなくなる
  • ・詳細な分析をする場合はクロス集計で用いた元データ以外のデータが必要になる

またデータを項目別に自動集計してくれるピボットテーブルは、データ分析をする上でよくある「自分が作成していない表データを分析する」場面でも役立ちます。

複雑な数式や特別な関数を使わずに大量のデータを分析できるため、日々業務でデータを扱うビジネスパーソンにとって、ピボットテーブルは効率化のための必須のスキルなのです。

 

ピボットテーブルのメリットとは

データ分析においてピボットテーブルを用いる主なメリットは次のとおりです。

  • ・マウス操作だけで行える
  • ・数式や関数が不要
  • ・元表を更新すると集計結果が自動更新される(更新機能がある)

表計算ソフトでデータ分析をするには、数式や関数を用いる場合が大半です。一方、ピボットテーブルは数式や関数が不要なので、簡単にデータ管理や分析ができます。

 

ピボットテーブルの構成要素

ピボットテーブル 使い方

ピボットテーブルは上図のように3つのエリアに分けられています。各エリアの概要は以下のとおりです。

  • ・行ラベル:縦軸/行の見出し
  • ・列(フィールド)ラベル:縦軸/列の見出し
  • ・値:集計結果の数値が表示されるエリア

またピボットテーブルの右側には、ピボットテーブルで集計・表示させる項目を選択する「作業ウィンドウ」があります。

ピボットテーブル 集計

シートの右側に表示されている「ピボットテーブルエディタ」または「作業ウィンドウ」内では表示されている項目を行・列・フィルタ(一部の項目のみを表示させるエリア)のいずれかに追加またはドラッグ&ドロップしてピボットテーブルを作成します。

 

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

ピボットテーブル 使い方

ここまで解説してきたように、ピボットテーブルはこれからデータ分析のスキルを向上させたいと考えている方やすぐにデータを活用したいと考えている方に役立つ表計算機能です。次では、ピボットテーブルの基本的な使い方を順に解説します。

 

Excelで元表を作成する

ピボットテーブル 表

ピボットテーブルは原型になる表(元表)にまとめられたデータを自動集計する機能なので、まずは元表を作成しましょう。元表は上図のように、並び替えや抽出(フィルタ)、データの検索・集計をするのに適している「データベース形式」で作成します。

 

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

ピボットテーブル 作り方

続いて表計算ソフトのメニュータブ「挿入>ピボットテーブル>テーブルまたは範囲から」の順でクリックして、新規ワークシートにピボットテーブルを作成します。この時、元表を「テーブル化」している場合はテーブル名から、していない場合はセル番地の指定でピボットテーブルを作る元表を指定します。

 

ピボットテーブルの列/行に表示したい項目(フィールド)を選択する

ピボットテーブル 便利すぎ

元表から抽出したデータが集計されたピボットテーブルを任意のシートに作成したら、ピボットテーブルの列/行に表示したい項目にチェックを入れて、選択しましょう。

項目を選択することでピボットテーブル内で集計されるデータの項目が絞られた状態になります。また、チェックボックスを選択する場合は各項目は自動で各ボックスに配置されますが、各項目を直接ドラッグ&ドロップすることで行や列の指定ができます。

 

追加したフィールドを移動・削除する

集計するデータの項目を選択したあとに、フィルター・列・行のボックスに選択したデータ項目をドラッグ&ドロップで移動させることもできます。

またフィルター・列・行のボックスに自動で移動されてしまっているデータ項目があった場合はボックスの外にデータ項目を出すか、削除を選択しておきましょう。

 

ピボットテーブルで詳細なデータ分析をする方法

ピボットテーブル 分析

ここまでは基礎知識として、ピボットテーブルを構成するエリアや、ピボットテーブルを使ってできることを解説してきました。次では応用知識として、ピボットテーブルを用いて詳細なデータ分析をする方法を解説します。

 

並び替え・フィルター

データを分析していると、集計したデータの中から売り上げの上位項目を知りたい場合や、指定した項目だけのデータを参照したい場合があります。このような集計データの詳細を分析したい場面に便利なのが、ピボットテーブルの「並び替え・フィルター」機能です。

「並び替え・フィルター」機能は次のような場面で役立ちます。

  • ・指定した項目のみ表示させる
  • ・指定した順番(五十音/数値の大小)で項目を表示させる
  • ・基準値以上や上位数値のデータだけを表示させる

集計したデータを項目ごとに詳細に分析するのは骨が折れる作業ですが、ピボットテーブルの「並び替え・フィルター」機能は2種類のマウス操作で実行できます。ピボットテーブルで集計した結果を並び替えたい場合は次のようにマウスを操作します。

  • ・昇順/降順:右クリックから並び替え→昇順/降順を選択
  • ・独自の順序:ドラッグ

またピボットテーブルのデータを並び替える際の画面は以下のとおりです。

ピボットテーブル フィルター

上図のように、並び替えたい項目のセルをクリックした後、プルダウン表示されているメニューの中から「並び替え」を選択して「昇順」または「降順」を右クリックすれば並び替えが完了します。

 

グル―プ化

ピボットテーブルの「グループ化」とは、日付(11/12や11/30など)や時刻などのデータを単位ごとにまとめて表示させる機能です。

商品ごとの売り上げ一覧表で四半期ごとのデータを表示させたい場合は、下図のようにピボットテーブル上の値を右クリックしてプルダウンメニューから「グループ化」または「○○グループを作成」を選択してみましょう。

ピボットテーブル グループ化

ピボットテーブル グループ化 条件

四半期だけではなく、月や年など様々な期間のグループ化もできるので、部門の実績や会計データを集計してレポートを作成する場合に役立ちます。

※参照:Microsoftサポートページ

 

スライサー機能

ピボットテーブルの「スライサー機能」とは、選択した項目のみ集計したい時に使うデータの抽出機能です。商品名や顧客名など、文字フィールドでデータを抽出します。コントロールキーとマウスクリックをすれば、複数の項目に関するデータ抽出もできます。

ピボットテーブル スライサー 使い方

スライサー機能をピボットテーブルに追加する場合は、「ピボットテーブル分析」タブ内の「スライサーの挿入」をクリックしましょう(スプレッドシートの場合は「データ」タブ内の「スライサーを追加」を選択)。

すると上図のように元表のフィールド名が一覧で表示されます。フィールドごとに別のスライサーが作成されるようになっているため、一覧の中から抽出したいデータのフィールド名にチェックを入れましょう。またこの際に、複数フィールドの選択もできます。

ピボットテーブル スライサー 並び替え

フィールド名にチェックを入れると、上図のように、選択したフィールドのデータのみがピボットテーブルに抽出されます(ここでは「コーヒー」を選択したため、ピボットテーブルにはコーヒーのデータのみが表示されています)。

 

タイムライン機能

ピボットテーブルの「タイムライン機能」とは、スライサー機能と同じようにある特定の項目のみ集計したい時に使うデータの抽出機能です。時系列のフィールドでデータを抽出します。

ピボットテーブル タイムライン機能 使い方

ピボットテーブルにタイムライン機能を追加する場合、まずはピボットテーブル内のセルを1つ選択します。その後、「ピボットテーブル分析」タブ内の「タイムラインの挿入」をクリックしましょう。

すると前述のスライサー機能と同じようにフィールド名が一覧で表示されるので、抽出したいフィールド名を選択します(ここでは日付を選択)。

ピボットテーブル タイムライン できない

すると上図のように、ピボットテーブル上に表示させたい期間を設定できる画面になるので、ピボットテーブルに表示させたいデータの期間を設定しましょう。

 

ピボットグラフ

ピボットグラフとは、集計したデータをビジュアライズする機能です。グラフ化したいピボットテーブルのセルを選択した状態でピボットテーブル分析メニューからピボットグラフを選択すると、グラフデザインの候補が現れます。

ピボットグラフ 作成できない

ピボットグラフを活用すれば、集計された数字の大小や期間ごとの差、データの傾向を直感的に伝達・理解できるでしょう。

 

ピボットテーブルの小技と便利な機能

ピボットテーブル 機能

ここまでピボットテーブルの基本機能を実際の操作画面とともに解説してきました。次では、基本機能に加えて知っておくと、データ整理やデータ分析が楽になる、便利な機能をご紹介します。

 

列幅を固定にする

ピボットテーブルではデフォルトの設定で、データを組み替えるごとに列幅が自動調整されるようになっています。自動で列幅が調整されれば、列幅を気にする必要がないので、基本的には便利な機能です。

一方、データ分析をする担当者が「見やすい」と感じる列幅と自動調整の列幅が異なる場合もあるでしょう。このような場合は、ピボットテーブル上で右クリックをして、「ピボットテーブルオプション」を選択します。

するとダイアログボックスが表示されるので、ボックス内の「レイアウトと書式」タブを選択して「更新時に列幅を自動調整する」のチェックを解除しましょう。

 

値フィールドの変更して合計以外の値を表示する

ピボットテーブルの値フィールドでは、自動的に合計数値が集計されます。そのため、値フィールドに合計以外の値を表示したい場合は作業ウインドウ内に表示されている「値」のボックスに表示されている「合計」を次の手順で変更しましょう。

  • ・「値」のボックスに表示されている「合計」ボタンを右クリック
  • ・ 表示されたプルダウンメニューから「値フィールドの設定」を選択
  • ・「平均」「最大」「最小」などから表示したい値を選択

ピボットテーブル 値フィールド

 

表示桁数を揃えて見やすくする

また値フィールドを変更する際に、表示される値の桁数を揃える設定も次の手順で同時に行えます。

  • ・「値フィールドの設定」を選択、左下の「表示形式」をクリック
  • ・「セルの書式設定」ボックスから「数値」を選択
  • ・ 桁区切りや小数点以下の表示を設定

大量の値データをピボットテーブルで整理・分析する場合は表示桁数を揃えることで、ステークホルダーも数値の把握がしやすくなるでしょう。

 

間違えて設定した項目をボックス外にドラッグ&ドロップで削除

ピボットテーブルの行/列に誤ったフィールドを追加してしまうこともあるでしょう。このような場合は、追加したフィールドをボックスの外(表計算シートの方向)にドラッグ&ドロップすれば簡単に削除できます。

なおスプレッドシートでピボットテーブルを使う場合は、フィールドの右上に表示されている「×」をクリックして削除しましょう。

 

「おすすめピボットテーブル」機能

ピボットテーブルは「ピボットテーブルエディタ」または、「作業ウィンドウ」を用いて列/行/値に表示させたい項目を設定することで、元表からデータを簡単に集計できます。一方、表計算ソフトを使い慣れていない方にとっては難易度が高いとも言えるでしょう。

Excelでは表計算ソフトが自動的に判断して、おすすめの集計方法を提案してくれる「おすすめピボットテーブル」機能があります。設定手順は以下のとおりです。

ピボットテーブル おすすめピボットテーブル

  • ・「ピボットテーブル」内の「分析」タブをクリック
  • ・ 右上の「おすすめピボットテーブル」をクリック
  • ・ 表示された集計方法から目的に合った形式を選ぶ
 

ピボットテーブルを作成する時の注意点

ピボットテーブル 更新

ピボットテーブルはデータに基づいた意思決定のサポートに最適なツールです。しかし、分析したデータが理解しにくかったり、エラーが含まれていれば、ステークホルダーにとって最適なダッシュボードまたは提案とは言えません。

最後にピボットテーブルを用いたデータ分析で注意すべき点を3つご紹介します。Schooオリジナル授業『Excelピボットテーブルのビジネス活用法』ではピボットテーブルの使い方に加えて、データクリーンの方法も体系的に学べるので、ぜひ活用してくださいね。

 

ピボットテーブルのデータが最新か確認する

ピボットテーブルは元表にまとめられたデータを集計・可視化する機能です。そのため、元表にデータが追加された場合や元表のデータが編集・削除された場合は「データの更新」「データソースの変更」が必要です。

デフォルトではデータが自動更新されないため、以下の方法でピボットテーブルに集計されるデータを最新に更新するよう、習慣化しておきましょう。

  • ・ピボットテーブルをクリックし、リボンの「ピボットテーブルツール」を表示させる
  • ・分析>更新の順にクリック(またはAltキー+F5キー)

またブックを開いた際、自動でピボットテーブルのデータが更新される設定は以下の手順で行います。

  • ・ ピボットテーブルをクリックし、リボンの「ピボットテーブルツール」を表示させる
  • ・ 分析>オプションの順にクリック
  • ・「データ」タブで「ファイルを開くときにデータを更新する」にチェック

※参照:Microsoftサポートページ

 

項目数が多いラベルは列に入れない

集計・分析したデータをステークホルダーや他のメンバーと共有する際に注意したいのが「視認性」や「分かりやすさ」です。

ピボットテーブルの見やすさを向上させるには、項目数が多いラベルは列に入れないようにしましょう。なぜなら、項目数が多いラベルを列に設定すると、縦長のピボットテーブルになってしまうからです。

縦長のピボットテーブルはデータを一目で確認できないので、せっかくまとめたデータが相手に理解されない状態になってしまいます。

 

空白のセルに0を表示させる設定をする

対象のデータ個数が「0」である場合にセルを空白にすると、ピボットテーブルを確認した人の中には「記入漏れかもしれない」と考える人もいるでしょう。このようなデータの読み違いを防ぐために、空白のセルに「0」が表示されるように設定します。手順は以下のとおりです。

  • ・ ピボットテーブルをクリックしてリボンに「ピボットテーブルツール」を表示させる
  • ・「分析」タブをクリック
  • ・「ピボットテーブル」をクリック>「オプション」をクリック
  • ・ 書式セクションの[空白セルに表示する値]のテキストボックスに"0"を入力

データ分析はグラフやダッシュボードを共有するだけではなく、分析に基づいた意思決定に役立つような注意と工夫が欠かせません。ここでご紹介した注意点を実践して、データ分析の質を高めていきましょう。

 

まとめ

データ分析人材の育成が重要度を増している中で、分かりやすい表の作成やダッシュボードの作成ができるスキルの価値は今後ますます高まっていくと言えるでしょう。

Schooではピボットテーブルを含む、OAスキルやデータ分析に関する授業が月額980円で受け放題です。ぜひ活用してくださいね。

今日の生放送

  • このエントリーをはてなブックマークに追加

まとめ記事の記事一覧