神奈川大学 経済学部
2017年度経済情報処理II

第12課  ピボットテーブルで分析する。列ラベル・行ラベルの整列・選択

第12課の目標

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

準備問題
(1)データファイル(task06.xlsx)を右ボタンクリックしてダウンロードして保存し、ブロックを解除しておきなさい。
(2)売上データシートに、販売価格仕入価格で、商品ごとの利益を計算する列(利益額)を追加しなさい。
(3)売上データシートに、店舗コードに対応した店舗名を入れた列(対応関係は店舗マスタシートに入っている)と、商品分類コードに対応した商品分類名を入れた列を(対応関係は分類マスタシートに入っている)追加しなさい(VLOOKUPを使うこと)

1 データを制限した集計

例題1. 店舗別利益金額の計算

Step 1. 集計に使いたい項目(来店月、店舗名、利益額)が含まれている列を選択する

あんまり細かいことを考えるのは面倒なので、下の例では全データ(A列からQ列)を全部選択している。

Step 2. 集計するフィールドを指定する

下図の例では、列ラベル(表頭)に店舗名、行ラベル(表側)に来店月、Σ 値に利益額を指定した。

Step 3. 集計表ができて……できてない?

上の操作を行うと、こんな集計表ができてしまった。#VALUE ばかりで全く集計できていない。これはいったいどうしたことだろう。

Step. 4 データをチェック

Step 5. 商品分類コードを使って、買い物をした人だけを集計に入れる

(1)集計する内容を制限するためのフィールド(この場合は商品分類コード)を「レポートフィルター」にドラッグする

(2)レポートフィルタに使うフィールドを設定すると、集計表の上にフィルタを設定するダイアログが出てくる。今は何も選んでいないので「(すべて)」と表示されている。

(3)をクリックすると下のような画面が開く。今回は商品分類コードが0〜6の範囲に入っているものを全て選びたいので、「複数のアイテムを選択」をチェックする。

(4)すると、それぞれにチェックボックスが表示されるので、0から6以外のチェックを外す。

(5)できあがりはこんな感じ。

Step 6. グラフも作っておこう

練習問題1. データの解釈(☆)

例題1の結果から、月別・店舗別の利益額合計の変動を読み取り、その理由を考えてみる。

練習問題2. 利益額の平均も出してみる

例題1と同様に、月別・店舗別の利益額平均を集計してみる。この場合は何かめだった特徴はありそうか?

練習問題3. いろいろな集計(☆☆)

  1. 店舗別、入店時間帯(0〜2, 3〜5, 6〜8, 9〜11, 12〜14, 15〜17, 18〜20, 21〜23と3時間単位で)ごとの利益額合計
  2. 店舗別、曜日ごとの利益額合計

Tips: Excelでの日付や時刻の扱い

2. 項目の並びが気に入らないので直す

例題2. 表頭の項目の順番を並べ替える

Step1 例題1で作った集計表を用意する

この表頭の並びを、左から白楽、みなとみらい、反町にしたい。

Step2. 列ラベルの横のをクリックする。

Step 3 出てきたダイアログのなかから「その他の並べ替えオプション」を選ぶ

昇順や降順は、普通の並べ替え規則を使って並べ替える方法。この場合は「昇順」を選ぶと みなとみらい、白楽、反町の順になり、「降順」を選ぶと 反町、白楽、みなとみらい の順になってしまって思い通りにならない。

Step 4. 出てきたダイアログから「手動」を選ぶ

上述の通り昇順や降順を選んでも(以下略)

Step 5. 「みなとみらい」を右に動かして順番を変える

※「白楽」を左に動かしても結果は同じ

(1)列ラベルの「みなとみらい」をクリックしてアクティブセルにする

(2)マウスポインタをアクティブセルの周囲の太線に合わせると、みたいに変わるので、そこまで持っていく。

(3)マウスの左ボタンを押して、押したまま動かしたい先までマウスポインタを移動する(ドラッグする)。行き先には線がでるのでわかりやすい。

(4)できあがりはこんな感じ

ちゃんと白楽、みなとみらい、反町の順になっている。

(5)もちろん、ピボットグラフも自動的に系列の順序が変わっている

練習問題4 店舗と商品グループでやってみる

月の代わりに商品グループで集計すると、下の図のようになるが、商品の並びが分かりづらい。一番右側にある「弁当類」が「菓子類」と「甘い飲料」の間にくると、「左側は食べ物、右側は飲物」という感じになって良さそうなので、そのように並べ替えてみる。

練習問題5 AKB総選挙のデータで上位7位までに入った人だけの集計(☆)

行ラベルに名前、列ラベルに回数を取って、各回1〜7位に入った人の順位が分かる集計表を作ってみる。

できあがりはこんな感じ

(ヒント)総計を計算しないようにするには、ピボットテーブルを右ボタンクリックして出てくるダイアログから[ピボットテーブル オプション]を選び、集計とフィルター-総計で、「行の総計を表示する」「列の総計を表示する」のチェックを外す。

確認問題
ブック task06.xlsx に入っているデータを使って以下の集計表とグラフを作成しなさい。
(1)行ラベルに店舗名をとって、店舗ごとの利益合計を計算する集計表を作成しなさい。
(2)行ラベルに入っている店舗名の順番を、上から白楽、みなとみらい、反町の順になるように並べなさい。
(3)利益額の縦棒グラフを作成しなさい。
イメージはこんなかんじ

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