神奈川大学　経済学部　基本科目

第11課　ピボットテーブルの使い方、簡単な集計

↑経済情報処理（2017）ホームページに戻る

準備問題
(1)データファイル（task05.xlsx）を右ボタンクリックしてダウンロードして保存し、ブロックを解除しておきなさい。
(2)手作業シートの「店舗名」の列に、「店舗コード」に対応する店舗名を入力しなさい。ただし、店舗コードと店舗名の対応表は店舗マスタシートに入っている（VLOOKUPを使うこと）。

1 とりあえず、手作業でやってみる

• ピボットテーブルは、Excelのいろいろな機能の中でも、特に「何をやっているのか」が分かりにくい機能。
• いきなりExcelでやってしまうと、何をやっているのか全くわからない可能性があるので、まず手でやってみよう。

例題1. 属性ごとの集計表を作ってみる（手作業）

表1（原データ）は、POSデータの中からごく一部のデータだけを抽出したようなものである。このデータを使って、「各店舗で、各月に売上が何件記録されているか」をカウントしてみよう。

表１. 原データ

表中①に入るのは行方向（表側）が「白楽」かつ列方向（表頭）が「3月」の売上。表1をその条件でチェックすると該当するのは取引番号318440の1件だけである。
表中②は行方向（表側）が「白楽」かつ列方向（表頭）が「4月」であるから、該当するのは取引番号が｛322120, 323820, 325800, 337860｝の4件。
表中③は行方向（表側）が「反町」かつ列方向（表頭）が「3月」であるから、該当するのは取引番号が484310の1件。
表中④は行方向（表側）が「反町」かつ列方向（表頭）が「4月」であるから、該当するのは取引番号が｛486520, 490270, 491660｝の3件。、

この数値を集計表に書き込むとこんな感じ。

この表をみると、3月と4月で取引件数に大きな差があるように見える（見えるだけなので、本当に差があるかどうかをチェックする技法は統計学の講義を取ろう）。

練習問題 1. 上のデータ（表1）から縦に商品グループ名、横に来店月をとった、取引件数の表を作りなさい。

できあがりのイメージ

2 Excelを使ってやってみよう

• この程度の件数なら、手作業で数えても大丈夫だが、データが100件もあったらかなり嫌になる。
• ピボットテーブルを使うと、こういう操作が簡単にできる。

ピボットテーブルを作成するには？　→　Excel編 Q.53

例題2. 属性ごとの集計表を作ってみる（Excel版）

Step 1. データの値をピボットシートにコピーする

• 手作業シートの中では、VLOOKUPで店舗名を求めているが、VLOOKUPはコンピュータにとっても大変な処理を行う関数。
• その上で、また大変なピボットテーブルを作成するのはコンピュータがかわいそうなので、値だけを空のシートにコピーしておく。
• もちろん、VLOOKUPで検索しているデータが変わる予定があるときは値だけの貼付など選んではいけない（アタリマエ）。コンピュータの都合を優先しすぎて、結果が正しくないのでは意味が無い。

式や関数が入っているセルの値のみをコピーするには？　→ Excel編 Q3 (2)

（1）手作業シートのA1:G10を選択し、Ctrl+Cでクリップボードにコピーする。
（2)アクティブシートをピボットシートに切り替える。
（3）A1セルをアクティブセルにする。
（4)右ボタンクリックしてポップアップメニューを出し、貼付のオプションから（値）を選ぶ。

できあがりはこんな感じ。VLOOKUPが入力してあったところが、単なる文字列になっている。

Step 2. ピボットテーブルに使うデータ範囲を選択して、ピボットテーブルを挿入する

(1)ピボットシートの範囲A1:G10を選択する。
(2)挿入タブのテーブルグループからピボットテーブルボタンをクリックする。

Step 3. ピボットテーブルを作成する場所を指定する

• ピボットテーブルボタンを押すと、以下のようなダイアログが表示される。
• 上のように、予めデータが入っている範囲を選択してあれば、自動的にその範囲が「テーブル範囲」に入力されている。
• ピボットテーブルを配置する場所は、よほど特殊な理由がなければ、「新規ワークシート」のままでいい。

Step 4. 集計項目を設定する

• Step3で、ピボットテーブルレポート作成先を「新規ワークシート」にした場合は、データが入っているシートの左側に新しいシートが作成されて、自動的にアクティブシートが移動する。
• シートの右側に以下のようなダイアログ（ピボットテーブルのフィールドリスト）が表示されるので、上にある「レポートに追加するフィールド」を下の方にドラッグ＆ドロップすると自動的に集計してくれる。
• 「集計項目」は単純に件数を数えたい場合は、四則演算できない項目（名前など）を使うといい。

実際に例題１と同じ集計をするように、項目を設定するとこんな感じ。結果も例題1と同じ。

練習問題 2.

練習問題1で手作業で作成した集計表と同じ内容の集計表を、ピボットテーブルを使って作ってみなさい。

3. 月別の売上を分析してみる

• さて、これでやっと本番の作業ができるようになった。
• といっても、本部から届いたデータは1/100抽出なのでいろいろ処理には注意が必要。

例題3 　本部から届いたデータを眺めてみる

データは、task05.xlsxの売上データシートに入っている。

このデータはコンビニの売上データが元になっているので、内容は大体レシートに書いてある内容と同じようなものになる。以下に示したレシートの例は、売上データシートの2〜4行目（来店者ID=0）に対応するもの。

来店者ID

来店者を区別するデータ。レシートでいえばレシート番号に相当する。ただし、このコンビニでは万引き対策として入店した人は片っ端から体重をはかっているため、買い物をしていない人（入店したけど、そのまま出て行った人）にも来店者IDを割り当てている。

店舗コード /店舗名

どのお店のデータかを表す。レシートで言えば右上に印刷されている店舗名に対応する。レシートは人間用なので「みなとみらい」と表示しているが、POSデータ側では2という数字になっていることに注意。

来店者属性

コンビニのレジで会計を処理するときに、レジ担当者がお客のようすを見て「この人男だな、10代だな」と判断したらしい。来店者のこういう情報は「10代男性に売れ筋のスナック」みたいな分析をするときに使える。

入店時刻/退店時刻

お店に入った時刻と出て行った時刻。レシートには入店時刻を印刷していないけれども、コンビニ側では覚えている設定。

入店重量/退店重量/購入重量

万引き対策で前に使ったデータ。入店時と退店時の重量は出入り口で測定し、購入した重さはレジを通した商品で判断している。

取引明細ID

一人の来店者が買う商品数（1枚のレシートに載る商品数）はわからないので、商品1つずつを別の明細として管理している。個数が入っていないのは、このコンビニでは同じものを2つ以上買うとレシートの別の行になるから。入店したけど買い物をしなかった人の場合は、取引ID以降の列がすべて空欄になる。

商品コード

何が売れたかを記録しているもの。これがないと話にならない。

来店月

分析の都合上、退店時刻から月だけを抜き出したデータを入れてある。

商品グループ

商品マスタに入っているデータなのでPOSデータとしては不要。ここでは分析の都合で入れてある。

販売価格

販売価格なんて商品マスタに入っているからいいじゃん？　と思うかもしれないが、商品の値段が変わったりすることはよくあることなので、「取引が発生したときにはいくらだったか」という情報は記録しておいた方がいい。

仕入価格

仕入価格も販売価格と同様に「そのときに売った商品の仕入価格」という形で記録しておかないとあとで困る。

練習問題3, この領収証はどのデータに対応しているか？

下に示した領収証に対応するデータは売上データシートのなかで何行目から何行目にあるか探しなさい。

練習問題4. データから領収証を再現する

来店者IDが100の人が受け取った領収証はどんな感じだったと思われるか。例題で出したレシートのような感じで、データから読み取れる内容を整理しなさい。

例題4. 月×店舗で合計売上額を計算する

データは、task05.xlsxの売上データシートを使って、目標としてはこんな集計表を作りたい。表のそれぞれのセルには、各店舗の月ごとの売上合計が入るイメージ。

Step 0. 店舗名を入れておく

売上データシートの店舗コード列の右側に新しい列を挿入し、店舗名という見出しを付けて、VLOOKUP関数を使って、店舗コードに対応する店舗名を入れておくこと。

Step 1. ピボットテーブル機能で使うデータ範囲を指定する

• ピボットテーブルではデータを行と列での範囲で指定する代わりに列だけで指定することもできる。
• ここではその方法をやってみる。

(1)データ範囲は売上データシートのA列からO列なので、行アドレス部分をA列からN列までドラッグすると選択される。

(2)挿入タブのテーブルグループからピボットテーブルボタンをクリックする。

Step 2. ピボットテーブルのフィールドを指定する。

(1)表頭は店舗名なので、「列ラベル」に店舗名をドラッグする。
(2)表側は来店月なので「行ラベル」に来店月をドラッグする。
(3)集計する値は売上なので、個々の商品の販売価格を合計すればいい。そこで、販売価格を「Σ値」にドラッグする。

Step 3. 集計方法を「合計」に変更する

• ここまでの作業をするとこんな感じ。なんかそれっぽい表ができているが集計結果が変。
• いくら何でも店舗の月あたり売上金額が722円×100（抽出倍率）＝72200円では店がつぶれてしまう。
• よく見ると、左上に表示されている集計内容が「データの個数/販売価格」になっている。
• 合計金額を知りたいのだから、この集計方法を変えないとダメ。

(1)「Σ　値」の中にある販売金額をクリックすると、メニューが出てくるので、中から「値フィールドの設定」を選ぶ

(2)「集計方法」がデータの個数になっているので、その上にある「合計」をクリックして集計方法を変更する。

できあがりはこんな感じ、これでも白楽は3月の売上総額が19611円×100=1,961,100円だったりして結構厳しそう。

Step 4. グラフにしてみる

ピボットテーブルで作成した集計表は、ピボットグラフという機能を使うとすぐにグラフ化できる。月ごと、店舗ごとの変動がわかりやすくなるように、横軸に月、縦軸に売上金額をとった棒グラフにしてみる。

(1)作成したピボットテーブルを選択した状態で、ピボットテーブルツール - 分析-ツールグループからピボットグラフをクリック

(2)普通のグラフ作成と同様にグラフの形式を聞くダイアログが出てくるので、左上の「縦棒」から「集合縦棒」を選んでOKをクリックするとグラフが描画される。

(3)このままだと横軸とか縦軸が何を表しているかわかりづらいので、グラフを選択した状態でピボットグラフツール-デザイン-グラフのレイアウト-[グラフ要素を追加]から軸ラベルを選び、第一横軸ラベルを「来店月」、第一縦軸ラベルを「月間売上額（100円）」にする。

白楽店だけ、2, 3, 8, 9月の売上額が大きく落ち込んでいることがわかる。

練習問題5 データの解釈

• なぜ白楽店だけ2,3,8,9月の売上が大きく落ち込んでいるのだろうか。もっともらしい理由を考えなさい。

練習問題6. 集計してみよう1

• 例題4と同様に、来店月×店舗名で集計表をつくりなさい。ただし、集計する内容は仕入価格の合計にすること。

練習問題7. 集計してみよう2

• AKB総選挙シートには、AKB総選挙の1回〜6回の得票数が入力されている。このデータを使って、列方向（表頭）に「何回目の総選挙か」という回数、行方向（表側）に「誰」という人を配置して、各回の得票数を一覧表にしなさい。

練習問題8 集計してみよう3（☆☆）

• 例題で、白楽店の売上は月によって大きな違いがあることがわかった。それでは、曜日ごとの変動はどうなっているだろうか？
• 幸い、売上データの中には日付情報が入っているので、ここから曜日を求めることができる。
• Excelでは日付から曜日を求めるために関WEEKDAY()という関数が用意されている。この関数は、曜日を1〜7の数値で返す（細かい仕様はヘルプを参照）。

1. WEEKDAY()関数を使って、売上データに新しい列曜日を追加し、曜日×店舗で売上額がどのように変化するか集計しなさい。
2. また、横軸に曜日、縦軸に売上額を取ってグラフ化しなさい。
3. 曜日による変動がありそうなら、その理由を考えなさい。

Tips: VLOOKUP関数の結果が　#N/A となってしまう場合の処理

VLOOKUP関数の第4引数が FALSE になっている場合、第2引数で指定される検索範囲に第1引数で与えられるキーが存在しなければ VLOOKUP関数は #N/A というエラーを返す。#N/A は数値ではないため、このエラーが出てしまうとその後の計算は全部 #N/Aになってしまう。このエラーを計算式で処理するためには ISNA関数を使う。

ISNA関数は、引数を1つとり、引数が #N/A の場合は TRUE、そうでなければ FALSE という論理値を返す。例えば、練習問題3のように「データがあればそのデータ、なければ0を返す」という処理をしたい場合は、IF関数の第1引数に ISNA関数を入れて、VLOOKUP関数の返値によって処理を変えればよい。

Tips: VLOOKUP関数は何をやっているのか？

↑経済情報処理（2017）ホームページに戻る

©2017, Hiroshi Santa OGAWA
このサイトにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.