開閉ボタン
ユーザーメニュー
ユーザーメニューコンテンツ
ログイン

  • 会員限定
  • 2018/10/23

Excel ピボットテーブルの使い方をわかりやすく解説、クロス集計を簡単にする方法とは

連載:今日から使えるExcel最強活用術

クロス集計はデータ分析において非常に有効な集計方法ですが、集計表を一から手作業で入力して作成するのは大変です。そこで有効なのがExcelの「ピボットテーブル」と呼ばれる機能です。ここではピボットテーブルの基本的な使い方や、実践テクニックをわかりやすい図とともに解説します。

藤井 直弥、大山 啓介

藤井 直弥、大山 啓介

藤井 直弥(ふじい・なおや)
Excel研修講師。業務改善コンサルタント。研修やセミナーの受講生は延べ一万人を超える。研修の主たるテーマは「多くの人が日々の直面している日常の業務を、いかに効率よく、そしてミスなく遂行するか」といった業務改善策の提案。そのための実践ノウハウや、Excelを用いたビジネス分析の手法などを一人でも多くの人に伝えることを使命として日々奮闘している。Excelの業務効率化を極めることが人生の目標の1つ。

大山 啓介(おおやま・けいすけ)
編集者・著述家。Excelの解説書籍を中心に、WordやPowerPointなどのOffice製品の解説書籍の執筆・編集などを行う。Excelとは20年以上の付き合いで、あらゆる機能の使い方を熟知している。日夜、Excelに苦手意識のある人に大して「どのように解説したら、よりわかりやすいか」を探求しており、多くの経験と実績を積み重ねている。自称Excel博士。趣味は京都探訪とロードバイク。


クロス集計でデータの“ウソ”を見抜く

 列見出しに年度、行見出しに製品名を入力して、表の交点にその年度のその商品の販売数を記入する――このような、2つの項目の一方を縦軸、もう一方を横軸に取って、その両者が交わるセルにデータを入力する集計法のことを「クロス集計」といいます。

●クロス集計の例
photo
クロス集計すると、各データを個別に確認できるため、データ分析を正確に行うことができる

 クロス集計は、データ分析を行う際の必須テクニックの1つです。なぜなら、クロス集計されていないデータ、たとえば、下表のような「分解されていないデータ」は誤解を招く可能性があるためです。 下表のように商品群が1つの項目にまとめられてしまうと、2016年度よりも2017年度のほうが好調なように感じられます。しかし、実際には商品C以外の販売数は減っています(上表参照)。

●分解されていないデータの例  
photo
すべての商品を合計した販売数だけを見ると、昨対比で順調に伸びているように見える
 この「商品Aと商品Bの売上は落ちているが、商品Cの売上で全体をカバーしている」という状況が、狙いどおりの望む状況であるならば問題ありませんが、これが意図しない状況であるならばきちんと分析することが必要です。しかし、まとめられたデータからは詳細をつかむことができません。

Excelでクロス集計を行う「ピボットテーブル」

photo
『Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術』
画像をクリックするとアマゾンに移動します
 集計表を一から手作業で入力して作成するのは大変です。時間と労力をかければ手作業で作成することも不可能ではありませんが、 そのようなことは絶対にしないでください。

 Excelでクロス集計を行うには「ピボットテーブル」と呼ばれる機能を使用します。有名な機能なので、名前だけは知っている、という人も多いかもしれません。

 ピボットテーブルを使用すれば、下図のように、羅列されたデータから、意味のあるクロス集計表を簡単な操作で、迅速かつ確実に作成できます。また、分析項目をマウス操作で手軽に切り替えることも可能です。

●ピボットテーブルでクロス集計する
画像
集計前のデータ。それぞれのデータが個別に入力されている。
この状態ではデータから有効なデータ分析を行うのは困難

画像
ピボットテーブルで集計したデータ。ピボットテーブルを利用すると
羅列されただけのデータが、意味のある情報に整理される

 本項では、このピボットテーブルの基本的な使い方を詳しく解説します。なお、実践テクニックについては次項以降で詳しく解説します。

ピボットテーブル用の表を準備する

 ピボットテーブルを使用するには、事前に「ピボットテーブル用の表」を準備する必要があります。具体的には次のような表を作成します。

・表の1行目(行見出し)に、必ず項目名を入れる
・数値や日付値は、Excelが正しく読み込めるように整える
●Excelの置換機能で不要な文字を削除する 
画像
置換前。C列の日付と時刻の間に「T」が含まれているため日時ではなく、文字のデータとして扱われてしまう。これではピボットテーブルで正確な分析ができない
画像
置換後。Excelの置換機能で、日付と時刻の間にある「T」を半角スペースに置き換えた。これでExcelはC列の値を日時データして認識できる
 まずは「表の1行目(行見出し)に、必ず項目名を入れる」ということを覚えておいてください 。もし空欄があるとエラーになります。次に、「Excelが正しく読み込めるように整える」とはどういうことでしょうか。具体例を見てみましょう。

 たとえば「1個」のようにセル内に単位が直接入力されていたり、「2015-0106T19:01」のように日付と時間の間に余計な文字列が入力されていたりすると、Excelはこれらの値を数値や日付値ではなく、「文字列データ」として認識します。

 その結果、これらの値を使って合計値を求めたり、期間を集計したりすることができなくなります。みなさん自身が入力しているセルにはこのようなデータは存在しないと思いますが、データをCSVファイルなどから取り込んだ場合に、こうした不要な文字が挿入されることがあるので注意してください

 もし不要な文字が含まれている場合は、右図のようにExcelの置換機能を使って、不要な文字を削除したり、半角スペースに置換したりしてください。

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

 それでは実際にピボットテーブルを実行してみましょう。手順はたったの5ステップです。ここではピボットテーブルで各商品の販売個数を計算してみます。元となるデータは9000件の販売履歴です。

画像
(1)分析したい表の中にカーソルを移動し、[挿入]タブの[ピボットテーブル]をクリックする。


画像
(2)[ピボットテーブルの作成]ダイアログが表示される。正しい範囲が選択されていることを確認する。
(3)[OK]ボタンをクリックする。

画像
(4)画面右側に表示されるサイドバーで、集計表を作成する。[商品名]の項目を[行]、[販売個数]の項目を[値]にドラッグする。

画像
(5)これで商品ごとの販売個数の合計を求める集計表が作成される。商品Aと商品Bの売れ行きが良いことが分かる。

(ピボットテーブルの見た目を整えたいときは、表全体をコピーして、別のシートに貼り付けましょう。これで、普通の表と同じように書式を設定できます。)

データの見え方は集計方法で変わる

 データを分析する際に大切なことは、2つのデータ間にどのような相関関係があるのか確認することです。つまり、一方が変わった際に、もう一方がどのように変わるのかを明らかにすることです。

 たとえば「担当者」と「商品の種類」の2つの視点で販売個数を集計すると、「各担当者が得意とする商品」が見えてくるかもしません。また、「時期」と「商品の種類」の2つの視点で売上金額を集計すると、「各商品の売れ行きが良い時期」が浮かび上がってくるでしょう。

 このように、2つのデータ間に有意な相関関係があることを見い出すには、ピボットテーブルを使用して、集計表を何度も作り直しながら、さまざまな角度で集計結果を見ていく方法が有効です。ピボットテーブルを使用すると、わずか数ステップの簡単な操作で、次のページの図のように表を作り直すことができます。

【次ページ】集計表は簡単に作り直せる

Office、文書管理・検索 ジャンルのトピックス

Office、文書管理・検索 ジャンルのIT導入支援情報

PR

ビジネス+IT 会員登録で、会員限定コンテンツやメルマガを購読可能、スペシャルセミナーにもご招待!