第14課 連続データをピボットテーブルで分析する

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

準備問題
ブック task08.xlsx の 来店者 シートは各行が来店者の情報に対応しているデータが入っている。
1. 店舗コードと店舗マスタシートに入っているデータを使って、店舗名を新しい列（店舗名）に入れなさい。
2. 来店者属性と、属性マスタシートに入っているデータを使って、来店者の性別を新しい列（性別）に入れなさい。
3. 入店曜日コードと曜日コードシートに入っているデータを使って、入店曜日を新しい列（入店曜日）に入れなさい。

1. バイトは何人配置したらいいだろう

• 零細コンビニチェーンではあるのだけれど、やっぱり店員はバイトを雇わないと回せない。
• いったい何人くらい雇えば、暇なバイトが沢山いて人件費が無駄、という状況も、お客が沢山並んでいるのにレジが回らないという状況にならないだろう。

例題1. 曜日とか時間帯で来客数を集計する

• 1日のなかで、何時頃にお客は多いのだろうか。また、曜日ごとに来店タイミングには差があるのだろうか
• 店舗によって、忙しい曜日とか時間帯は違うかもしれないのだけれど、とりあえず白楽店について考えてみよう

Step 1 まず、白楽店について、単純に曜日と来店時間で集計してみる

来店者シートのデータを使って、白楽店の曜日、来店時間ごとの来店者数を集計してみると以下のような感じ。

Step 2 データから特徴を読み取ってみる

白楽店のデータを見ると、どうも白楽店では

1. 曜日によって来店者数が大幅に違う、特に土日は落ち込んで、平日は多いらしい。
2. 時間帯によって来店者数がかなり違う。当たり前だけれど、夜中は少なくて、お昼時と夕方は比較的多いらしい。

という特徴があるらしい。時間帯ごとの差は「ご飯時に買い物にくる人が多い」ということである意味当たり前だけれど、曜日による違いはちょっとべつ。この問題を強調するために曜日を平日（月〜金）と週末（土曜、日曜）に分けてみよう。

Step 3 曜日を使って「平日」、「週末」に分ける

ピボットテーブル側でいじって「平日」「週末」を分けることもできるのだけれど、ここでは後の作業の都合もあって元のデータに「平日」と「週末」を区別するための新しい列（平日・週末）を追加する。

曜日コードシートを見ると、平日の曜日コードは1〜5、週末の曜日コードは6と7となっているようなので、曜日コードが5以下か、そうでないかで平日と週末を判断できる。というわけで、簡単なIF関数1つでOK。

たとえばこんな感じ。

Step 4 データを更新したので、ピボットテーブルも更新する

上の例のように、元々ピボットテーブルのデータ範囲になっていたところに新しい列を追加した場合は、変更後のデータを参照するために新しいピボットテーブルを作る必要はない。

下図のように、ピボットテーブルの上にマウスポインタを置いて、右ボタンクリックをすると出てくるメニューから「更新」を選ぶと、新しいデータがピボットテーブルに読み込まれる。

「更新」後はこんな感じ。確かに新しいフィールド名（平日・週末）が追加されている。

Step 5 曜日の代わりに「平日・週末」を使ってみる

列ラベルを平日・週末に入れ替えてみると、こんな感じ。すごく差があるように見えるけれど、これは平日（月曜から金曜までの5日間）と週末（土曜と日曜の2日間）の合計なので、合計している日数が違う。5日分の方が多いのはアタリマエ。

その辺を補正するために日数で割ってやると、週末は189÷2=94.5、平日は1422÷4=284.4となる。日数の補正をしてもだいぶ差があるので、「白楽店では平日には週末より沢山お客がくるので、平日のバイトをしっかり確保しないといけない」と言えそう。

練習問題1

1. みなとみらい店、反町店についても 、それぞれ曜日による来店者数の差があるかどうかチェックしなさい。
2. みなとみらい店、反町店についても、それぞれ平日・週末による来店者数の差があるかどうかチェックしなさい。
3. （☆）上の結果から、バイト確保についてどういう配慮が必要か、マネージャーとしてまとめなさい。

練習問題2（☆☆）

曜日コードシートに、平日と週末を表す列平日・週末を追加し、例題1でIF関数で行った処理を VLOOKUP関数でやってみなさい。

例題2　時間帯からシフト名を検索する

• 例題1での集計は、時間については1時間刻みだった
• でも、零細コンビニチェーンでの標準的なシフトは、朝シフト: 7:00〜11:59, 昼シフト: 12:00〜16:59, 　夜シフト: 17:00〜21:59, 深夜シフト: 22:00〜7:00 なので、このままではシフトごとの来客数が分からない
• 例題1で使ったような IF関数でできないことはないけれど、何をやっているのか分かりづらい。
• 時間からシフトの表を検索する→表の検索といったら VLOOKUP()

Step 1　入店時刻（時）を使ってシフト表1シートからシフト名を検索たいが上手くいかない

シフト表1を見ると、以下のような表になっている。時間帯に入っているのはシフトの切れ目の時間だけで、たとえば「15時はどのシフト？」を今まで使ったVLOOKUP()で検索しようとすると#N/Aエラーになる。時間帯の中に15がないのだから、これは当たりまえ。

でも、それでは困る。

（表にはシフトその１という名前をつけて計算した）

Step 2近い値でも検索できる VLOOKUP()を使ってみる

VLOOKUP()関数の第4引数、いままでは何も考えずに FALSE　を入力してきたが、FALSEがあるなら TRUEもあるはず。実は、ある。このへんはヘルプでVLOOKUP()を調べると一応書いてあるのだけれど、とても日本語として分かりづらい。以下、引用

検索の型 省略可能です。VLOOKUP を使用して検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを指定する論理値です。
TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。

余りにも何が書いてあるか分からないので、もうちょっと整理してみる。TRUEを指定すると、以下のことが起きる。

• 検索値（第1引数）と完全に一致する値が検索対象範囲（第2引数）の左端列にあれば、一致した行の中から第3引数で指定された列にあるセルの値を返す→FALSEと同じ動作
• 検索値（第1引数）と完全に一致する値が検索対象範囲（第2引数）の左端列になければ、検索対象範囲の左端列を検索して、検索値を超えた行の１つ上の行を「一致した行」として扱う。

細かい話は　Tips 検索の型（第4引数）がTRUEの時の VLOOKUP を参照。大事なことは「左端列の値が昇順に並べ替えられていること」。Excelはこの条件を期待して左端列の値を検索しているらしい。

とりあえず、シフト表1の F4セルに入っている VLOOKUP()の第4引数を TRUE にしてみよう。できあがりはこんな感じ。

なんか上手くいったようだ。

練習問題3

1. 例題2で扱った、検索の型（第4引数）が TRUEである VLOOKUP関数を使って、入店時刻（時）とシフト表1に入っているデータから新しい列シフトにシフト名を入力しなさい。
2. 上で作成したシフトを行ラベル、平日・週末を列ラベルにして、白楽店の来店者数集計表を作りなさい
3. （☆）平日は5日、週末は2日なので、2で作った集計表の値は直接比較できない。日数で割って調整した結果を使って、シフトごとの来店者数のグラフを平日と週末に分けて作りなさい。（ヒント：　ピボットテーブルの値を使って計算したいときは、一旦「値の貼付」で単純な数値データにしておくと安心）

練習問題4（☆）

AKB総選挙シートの得票数を、0〜19999, 20000〜39999…と、20000票単位でまとめたものを行ラベルに、回数を列ラベルにして人数を数えて、総選挙の回数ごとにどのように得票数の分布が変わったか説明しなさい。
（ヒント：　票数に NA という「数字でない値」が入っているため、ピボットテーブルの「グループ化」機能は使えません。VLOOKUP関数（第4引数がTRUE）などを使って、20000票単位でまとめる作業は事前に行っておくこと）

練習問題5（☆☆）

1. 今回の話は、実はシフト表2のような表を使って処理すれば、これまで使っていた検索の型（第4引数）がFALSEのVLOOKUP()でも処理できることを確認しなさい。
2. これまで使っていた検索の型（第4引数）がFALSEのVLOOKUP()では処理できない、あるいは処理が困難なケースを考えなさい。
3. シフト表1には、なぜか24という（実際には存在しないはずの）時間帯と、それに対応するシフトとして#N/Aというエラー値が定義されている。時間帯の入力で誤りが混入している可能性がある場合に、この「余計なデータ」にどういう意味があるのか考えなさい。

確認問題
ブック task08.xlsx の 売上データ シートに入っているデータを使って以下の作業をしなさい
1.商品コードと商品マスタシートに入っている情報をつかって、商品コードに対応する商品分類コードを入れた新しい列（商品分類コード）を作りなさい。また、店舗コードと店舗マスタシートに入っている情報を使って、店舗コードに対応する店舗名を入れた新しい列（店舗名）を作りなさい。
.2. 上で作成した商品分類コードと分類マスタシートに入っている情報をつかって、「時間が経つと廃棄しないといけない商品」（おにぎり類、弁当類、ホットスナック、まんじゅう類）と、「それなりに長持ちするので、それほど時間管理に気を遣わないでいい商品」（甘い飲物、甘くない飲物、菓子類）に商品を分類する列（廃棄区分）を作成しなさい。後でピボットテーブルを作成するために使うので、列の内容は人間が見て分かるようなもの（たとえば「要廃棄」と「長期保存」など）にしなさい。
3 零細コンビニチェーンでの店舗配送のタイミングは 5時、11時、16時、21時の4回である。入店時刻（時）のデータを利用して、5〜10時を「配送1」、11〜15時を「配送2」、16〜20時を「配送3」、21〜4時を「配送4」として区分する新しい列（配送時間帯）を作りなさい。
4. 行ラベルに配送時間帯、列ラベルに廃棄区分をとって、白楽店での売上個数の合計を求めるピボットテーブルを作成しなさい。
できあがり例はこんな感じ（これは、みなとみらい店のデータなので、白楽店とは違います）。

※列ラベルに #N/Aが入っているのは、「来店しているけど、何も買わなかった人」の分のデータが、商品コードも商品分類コードも未定義だから。

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

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