【Excelステップアップ】ピボットテーブルをマスターして、データ集計の達人になる：Tech TIPS
Excelを使って、日別の製品の売り上げなどを集計することはないだろうか。この際、さまざまな条件で並べ替えて、関数などを駆使して集計しているのであれば、ちょっと待ってほしい。Excelには、ピボットテーブルという便利な機能がある。この機能を使えば、簡単に集計が行える。その方法を紹介しよう。
この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。
対象：Office 2013／2016／2019／365
新型コロナウイルス陽性患者を年代別で集計してみた東京都が公開している新型コロナウイルス陽性患者のデータをExcelのピボットテーブル機能を使って、2020年6月8日現在の患者の年代と人数をグラフ化してみた（データは、「東京都_新型コロナウイルス陽性患者発表詳細」ページでダウンロード可能）。ピボットテーブルを使うと、こうしたグラフが数ステップで作成できる。
営業やマーケティング、製品開発といった仕事の中で、製品の売り上げを集計しなければならないことも多いのではないだろうか。ただ、売り上げの報告は、日付と商品名、販売個数といったもので、これらを整理しないと、せっかくのデータが活用できない。このように世の中にあるデータのほとんどが未整理な状態で見やすい形には整形されていない。
こうした「生データ」を見やすく整理するというのも、「Microsoft Excel（エクセル）」でよく行われている仕事だ。こうしたデータ集計作業は、これから説明する「ピボットテーブル（Pivot Table）」を使うと、Excelが生データの大半の整理を自動的に行ってくれ、作業を大幅に省力化できる。さらにピボットグラフ機能は、ピボットテーブルをグラフ化する機能で、整理したデータを分かりやすいグラフの形で見せてくれる。
ピボットテーブルは、かなり高度な機能なので、使う側にも少々理解が必要になる。ただし、一回原理を覚えてしまえば、何ということはない。
度数分布表（ヒストグラム）を作るために、データを再構成し、並べ替え、集計のためにCountIf関数やFrequency関数などを使ったこともあるだろう。しかし、そうしたデータの整理方法は、今日で終わりにした方がよい。ピボットテーブルを使えば、元データを再構成して、数式を入れる必要もなくなるからだ。
ピボットテーブルの超簡単理解
ピボットテーブルとは、未整理の「生データ」の表を整理された表に変換する機能である。生データとは、例えば「英語のテスト結果」などのようにあることがらについての複数のデータをまとめたものだ。1行に1件のデータを入れた「記録（レコード）」を縦に並べて作った表といってもいい。Excelでは、レコードの並びは縦でも横でもいいのだが、ここでは話を簡単にするために、ここでは1つのレコードは1行になっているとする。
世の中には、このような生データが多数ある。例えば、学校のテストの成績、商品の売り上げ情報、コンピュータのログファイル、測定装置が出力する測定結果のデータなどである。
また、複数の生データが個別の表などとして存在し、これをまとめて処理対象とすることがある。学校には、各教科に担当の教師がいて、それぞれのテストは、担当教師が集計する。しかし、生徒全体を見るような場合には、各教科のテスト結果をまとめて1つの表にする必要がある。このような場合、多くの人は、1行に一人の生徒の複数科目の成績を並べた「整理された表」を作るだろう。しかし、ピボットテーブルが使えるようになれば、単に複数の表をつなぎ合わせておけばよく、整理された表はピボットテーブルに作成させることができる。
ピボットテーブルを使う場合表を縦につなげればいい3つのシートを1つにまとめるといった場合、多くのユーザーは、整理された表を作ってしまうが、ピボットテーブルを使えるなら、単に縦につなげた表を作るだけでいい。
未整理の「生データ」を簡単に整理された表に変換できるのがピボットテーブルだ。単純なデータから構成される表に対して、ピボットテーブルを作成することで、自動的に整理、集計された表を作成できる。また、表の組み替えも設定だけで簡単に行え、縦横の項目を入れ替えるといったことも簡単だ。
ピボットテーブルでは元データを自動で解析ピボットテーブルは元データを自動で解析して表を作成するため、ユーザーは、元データに関する情報（上記のデータなら販売数の最大値や最小値、日付の開始、終了日時、商品数など）を事前に調べる必要もない。
そもそも、一般的に表とは、左端の「行」の項目、上部の「列」の項目と、行と列に関係する「値」から構成されている。生データの場合、列の項目を持つ1件の行が縦に並ぶ構造を持つ。
ピボットテーブルで指定する「行」「列」「値」ピボットテーブルでは、表を一般化して「行」「列」「値」の3つの部分の組み合わせにする。それぞれに表示させたい元データの項目を指定するだけで自動的に整理された表を作る。
これに対して、整理された表は、生データ側の列を集計するなどして、合計値などを求めたものになる。つまり、生データと整理された表の間には一定の関係がある。ピボットテーブルは、こうした元データの項目が「行」「列」「値」のどれになるのかを指定することで、自動的に整理された表を作り出すというものだ。
元データと作成されたピボットテーブルの関係元データと作成されたピボットテーブルの間には、「行」「列」「値」の関係がある。日付の範囲や商品が何種類あるのかなどは、Excelが生データを基に自動的に判断してくれる。さらにピボットテーブルは日付のグループ化や小計、総計などを自動的に追加する機能を持つ。
ピボットテーブルを使う
まずは、簡単にピボットテーブルの使い方を説明しよう。ピボットテーブルは、リボンの［挿入］タブの左端にある「ピボットテーブル」で起動する。
この［ピボットテーブルの作成］ダイアログには、元データの範囲を指定する部分があるので、あらかじめ元データを選択して起動してもいいし、起動してから元データの範囲を選択しても構わない。ただし、後の作業を考えると元データの一番上の行には項目名が入っているのが望ましい。というのは、項目名を使ってピボットテーブルの設定を行うからだ。
もう１つの設定項目は、どこにピボットテーブルレポートを置くかだが、これは、新規のワークシートも指定できるし、元データのあるワークシートや他のワークシートなど既に存在しているワークシートを指定することも可能だ。
［OK］ボタンを押してダイアログを閉じれば、指定したピボットテーブルレポートの配置先に「レポートを作成するには、………」という案内が表示される。実際の設定はここではなく、Excelウィンドウの右側に表示される「ピボットテーブルのフィールド」欄で行う。ここには、既に元データにある項目名が登録されている。この項目名を「行」「列」「値」へそれぞれドラッグすれば、ピボットテーブルが自動的に作成される。
どこにどの項目をドラッグするかで、ピボットテーブルの構造が変わってくる。なお、ピボットテーブルでは、日付データ（Excelのシリアル値）の項目は、自動的に「月」でグループ化される。このため、元データの「日付」項目を「行」、または「列」に入れると自動的に各月にまとめられた表示が可能になる。
とはいえ、最初はどういうものができるか分からなくて不安に感じることもあるだろう。だったら、リボンの挿入タブにある「おすすめピボットテーブル」を使って見るとよい。これは、元データをExcelが解析して、適切と思われるピボットテーブルのパターンを提示してくれるというものだ。
［おすすめピボットテーブル］ダイアログの左側には推奨パターン、また右側には選択したパターンで作られるピボットテーブルのプレビューが表示される。
作成されたピボットテーブルにアクティブセルを置けば、設定（前述の「ピボットテーブルのフィールド」欄）が表示されるので、行や列などに何が設定されたのかを見ることができる。
度数分布表を作る
では、実際にデータ分析で比較的よく使われる度数分布表をピボットテーブルで作ってみよう。前出の例と同じく「日付」「商品」「販売数」からなる元データからピボットテーブルを作成し、「列」に「商品」を、「行」には「販売数」をドラッグする。そして「値」にも「販売数」を入れるが、ドラッグした後、クリックしてメニューから［値フィールドの設定］を選び、［選択したフィールドのデータ］ダイアログで「個数」を選ぶ。
これにより、「値」はデフォルト設定の「合計値」ではなく、同じ販売数のレコードが何個あったのかがセル値として使われるようになる。行が「1」で列が「メロンパン」の値は、「メロンパンの販売数が1」だった日が何回あったかを示すことになる。
「値」の各セルは、行と列で指定された元データの集まりに対する計算だ。「平均値」ならば、該当する元データの平均値が、「個数」なら該当する元データの個数がセルの値になる。
次に、行のところにアクティブセルを置き、右クリックメニューから［グループ化］を選ぶ。［グループ化］ダイアログが表示されるので「単位」を適当な数にする。「先頭の値」と「末尾の値」は、Excelが対象セルの最小値と最大値を自動で入力してくれる（もちろん変更することもできる）。このデータでは最大が100個のようなので、10個単位で分割することにして「10」を入れる。［OK］ボタンでダイアログを閉じると「行」の項目が「1〜10」「11〜20」というように10個単位に分割された状態となる。これに応じて、「値」の部分も、元データの販売数が該当範囲に相当する個数になる。1〜10の行は、「販売数が1〜10個の間だった日が何回あったか？」になる。
度数分布表を作る（3）「行」には、対象項目の値が最小値から最大値まで並んでいるので右クリックメニューの［グループ化］を選択する。ダイアログの「単位」に度数の幅（「10」ならば、1〜10個、11〜20個、……）を指定する。
度数分布表を作る（4）ダイアログで指定した範囲で「行」がグループ化され度数分布表が出来上がる。これに対してピボットグラフ（リボンの［挿入］タブ）から縦棒グラフなどを指定すればヒストグラムを作ることができる。
度数分布表を作りたければ、対象となる元データの項目を行や列に指定し、それをグループ化する。値には、同じ項目を指定して「個数」を集計するように設定するわけだ。
こうして完成したピボットテーブルを選択してから［挿入］タブの「ピボットグラフ」をクリックすることで、簡単に度数分布表からヒストグラムを作成できる。
データの検査もできる
人手で収集したデータには間違いがつきものだ。生データを表にまとめるのは、ピボットテーブルを使わなくてもそれほど難しい作業ではないし、手慣れた人なら30分もあれば作ることができるが、データの抜けや重複などの検査を目視などで行うのはかなり時間がかかる。
しかし、ピボットテーブルで度数分布表を作れば抜けや重複を簡単に発見できる。例えば、一日一回の販売数の報告があるとすると、30日では、30件になるはずである。あるいは生徒数40人でテストを行えば、テスト結果は40個あるはずである。こうした「当たり前」の事実とピボットテーブルで作成する表を合わせればデータの抜けや重複は簡単に判別できる。しかし、手動で度数分布表を作るとそれなりの時間がかかってしまう。
例えば、「日付」「商品」「販売数」のサンプルなら、行に「日付」を、列に「商品」を置き、値には、「販売数」の「個数」を指定する。ここではデータの抜けや重複をわざと元データに施してある。こうしてできたピボットテーブルを見ると、1月の「乾パン」の個数が32個になっていて重複があり、2月の「アンパン」の個数が28個と１つ少なく抜けがあることが分かる。行の日付項目は、月で自動的にグループ化されているが、月の前にある「＋」記号をクリックして1日ごとの表示を行えば、抜けているところや重複している日付もすぐに分かる。
新型コロナウイルス陽性患者数のデータでピボットテーブルを演習してみよう
ピボットテーブルは、適切な形式の元データがあればよく、元データは整理されている必要は全くない。また、各項目の最大値や最小値、どのような種類の値が含まれているのかは、Excelが勝手に調べてくれるので、ユーザーは対象の表の概要を理解している必要さえない。このため、どこかから持ってきた「生データ」であっても、一定の手順で整理した表にすることができる。
ピボットテーブルは強力ではあるが、必ずしも全てのユーザーの要望を満足できるわけでもない。ビジネスの現場などでは、商品の売れ行きやサイトのアクセス数などのビジネスに関連したデータに関して、前年比などの時間的な分析を行うことがある。ピボットテーブルでは、日付データ（Excelの日付シリアル値）に関しては、秒から時間、月や四半期などの連続した時間単位でグループ化する機能があり、月ごと、年ごとといった単位で集計する機能がある。
しかし、残念ながら、連続する時間単位以外ではグループ化することができず、時間が連続しない曜日別や時間帯別にグループ化することができない。こうした分析を行うには、Weekday関数で曜日だけの情報、あるいはHour関数などで時刻だけの情報を含む項目を追加し、これを使って分析する必要がある。
曜日で集計する場合はWeekday関数で事前に列を作成ピボットテーブルは曜日別に集計する機能はないため、「曜日」列を追加し、Weekday関数を設定する。これで各レコードに曜日を表す「1」〜「7」の数値が付き、ピボットテーブルは曜日別の集計が可能になる（曜日列に"aaaa"を書式設定すると曜日名で表示できる）。ただし「曜日」列は特定の日付を表すシリアル値ではないことに注意。
あいにくと筆者の手元には、こうした販売データがないので、東京都が発表している新型コロナの感染者数のデータを使って、これを分析してみることにしよう（東京都オープンデータカタログサイト「東京都_新型コロナウイルス陽性患者発表詳細」）。
幸運なことに前述の東京都のデータには、最初から曜日の項目がある（おそらく東京都の担当者の方もExcelのピボットテーブルで分析を行っていると推察する）。まず東京都のサイトからCSVファイルを入手しよう。データ（CSVファイル）は、「東京都_新型コロナウイルス陽性患者発表詳細」ページのタイトル下のリンクをクリックするとダウンロードできる。
手始めに曜日ごとの発表感染者数を度数分布表化してみよう。「行」に「曜日」を、「値」には「No」の個数（個数／No）を設定する。「No」は行ごとに異なる数字が振られたユニークな数値で重複がなく、事実上感染者に番号を割り振ったのと同じものになっている。これをカウントすることで曜日ごとの感染者数を出すことができる。
東京都の新型コロナウイルス陽性患者を曜日ごとに集計してみる東京都の新型コロナウイルス陽性患者データには、発表された曜日の欄があるので、これと報告数（Noの個数）を使えば、曜日別の報告数をグラフ化できる。
これをグラフ化するには、［挿入］タブ−［ピボットグラフ］をクリックする（［ピボットテーブル分析］タブ−［ピボットグラフ］でもよい）。［縦棒］−［集合縦棒］を選んで縦棒グラフにすると、曜日別の発表感染者数が出る。
次は年齢別の感染者数を見てみよう。今度は行に「患者_性別」、「列」に「患者_年代」、値に「個数/No」を指定する。また、グラフは［縦棒］−［100％積み上げ縦棒］を選び、絶対値ではなくパーセント表示にしてみる。すると、下画面のようになる。
このように、ピボットテーブルやピボットグラフを一回作ると、設定を変更するだけでデータをさまざまな表現に簡単に変更でき、しかも、元データに関しての「最大値」や「最小値」、対象の数などの知識は全く必要ない。
そもそもExcelで行う仕事は、表やグラフ自体を作ることが目的なのではなく、何らかの判断をするための材料を提供することであり、そのために表を整理したり、グラフ化を行ったりするわけだ。その作業を簡略化できるピボットテーブルの機能を使わないのは大きな損失ともいえる。最初のハードルは高く感じられても、ここまで説明したように単純なデータを使って実際に試してみれば、それほど難しいものでないことはすぐに理解できるはずだ。Excelを使うなら、ピボットテーブルの利用を避けることは「半分損している」と言わざるを得ない。また、応用範囲が広く、情報を整理した表を作成するなら必ず利用できる機能でもある。
Copyright© Digital Advantage Corp. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。