- 会員限定
- 2018/10/23 掲載
Excel ピボットテーブルの使い方をわかりやすく解説、クロス集計を簡単にする方法とは
藤井 直弥(ふじい・なおや)
Excel研修講師。業務改善コンサルタント。研修やセミナーの受講生は延べ一万人を超える。研修の主たるテーマは「多くの人が日々の直面している日常の業務を、いかに効率よく、そしてミスなく遂行するか」といった業務改善策の提案。そのための実践ノウハウや、Excelを用いたビジネス分析の手法などを一人でも多くの人に伝えることを使命として日々奮闘している。Excelの業務効率化を極めることが人生の目標の1つ。
大山 啓介(おおやま・けいすけ)
編集者・著述家。Excelの解説書籍を中心に、WordやPowerPointなどのOffice製品の解説書籍の執筆・編集などを行う。Excelとは20年以上の付き合いで、あらゆる機能の使い方を熟知している。日夜、Excelに苦手意識のある人に大して「どのように解説したら、よりわかりやすいか」を探求しており、多くの経験と実績を積み重ねている。自称Excel博士。趣味は京都探訪とロードバイク。
クロス集計でデータの“ウソ”を見抜く
列見出しに年度、行見出しに製品名を入力して、表の交点にその年度のその商品の販売数を記入する――このような、2つの項目の一方を縦軸、もう一方を横軸に取って、その両者が交わるセルにデータを入力する集計法のことを「クロス集計」といいます。●クロス集計の例
クロス集計は、データ分析を行う際の必須テクニックの1つです。なぜなら、クロス集計されていないデータ、たとえば、下表のような「分解されていないデータ」は誤解を招く可能性があるためです。 下表のように商品群が1つの項目にまとめられてしまうと、2016年度よりも2017年度のほうが好調なように感じられます。しかし、実際には商品C以外の販売数は減っています(上表参照)。
Excelでクロス集計を行う「ピボットテーブル」
Excelでクロス集計を行うには「ピボットテーブル」と呼ばれる機能を使用します。有名な機能なので、名前だけは知っている、という人も多いかもしれません。
ピボットテーブルを使用すれば、下図のように、羅列されたデータから、意味のあるクロス集計表を簡単な操作で、迅速かつ確実に作成できます。また、分析項目をマウス操作で手軽に切り替えることも可能です。
●ピボットテーブルでクロス集計する
本項では、このピボットテーブルの基本的な使い方を詳しく解説します。なお、実践テクニックについては次項以降で詳しく解説します。
ピボットテーブル用の表を準備する
ピボットテーブルを使用するには、事前に「ピボットテーブル用の表」を準備する必要があります。具体的には次のような表を作成します。・表の1行目(行見出し)に、必ず項目名を入れる
・数値や日付値は、Excelが正しく読み込めるように整える
たとえば「1個」のようにセル内に単位が直接入力されていたり、「2015-0106T19:01」のように日付と時間の間に余計な文字列が入力されていたりすると、Excelはこれらの値を数値や日付値ではなく、「文字列データ」として認識します。
その結果、これらの値を使って合計値を求めたり、期間を集計したりすることができなくなります。みなさん自身が入力しているセルにはこのようなデータは存在しないと思いますが、データをCSVファイルなどから取り込んだ場合に、こうした不要な文字が挿入されることがあるので注意してください。
もし不要な文字が含まれている場合は、右図のようにExcelの置換機能を使って、不要な文字を削除したり、半角スペースに置換したりしてください。
ピボットテーブルの基本的な使い方
それでは実際にピボットテーブルを実行してみましょう。手順はたったの5ステップです。ここではピボットテーブルで各商品の販売個数を計算してみます。元となるデータは9000件の販売履歴です。(3)[OK]ボタンをクリックする。
(ピボットテーブルの見た目を整えたいときは、表全体をコピーして、別のシートに貼り付けましょう。これで、普通の表と同じように書式を設定できます。)
データの見え方は集計方法で変わる
データを分析する際に大切なことは、2つのデータ間にどのような相関関係があるのか確認することです。つまり、一方が変わった際に、もう一方がどのように変わるのかを明らかにすることです。たとえば「担当者」と「商品の種類」の2つの視点で販売個数を集計すると、「各担当者が得意とする商品」が見えてくるかもしません。また、「時期」と「商品の種類」の2つの視点で売上金額を集計すると、「各商品の売れ行きが良い時期」が浮かび上がってくるでしょう。
このように、2つのデータ間に有意な相関関係があることを見い出すには、ピボットテーブルを使用して、集計表を何度も作り直しながら、さまざまな角度で集計結果を見ていく方法が有効です。ピボットテーブルを使用すると、わずか数ステップの簡単な操作で、次のページの図のように表を作り直すことができます。
【次ページ】集計表は簡単に作り直せる
関連タグ
関連コンテンツ
PR
PR
PR