読者です 読者をやめる 読者になる 読者になる

モーリーのメモ

プログラミングやCG作成等、アプリ開発を中心に情報を収集中!

ピボットテーブルの使い方を調べる!:Googleスプレッドシート

 『ピボットテーブル』、、、Excelの分からない、使ったことがない機能なのですが、ちょっと気になってました。
 先日、Googleスプレッドシートにも同様の機能があることを知り、良い機会なので使い方等を調べてみました。
  f:id:mmorley:20161027235918p:plain:w350

使用環境

 私が使用している環境です。

ピボットテーブルとは?

 ピボットテーブルとは、↓のように雑然としたデータを、
 f:id:mmorley:20161027235942p:plain:w300
 ↓のようにだったり、
 f:id:mmorley:20161022223738p:plain:w300
 ↓のようにだったりと、
 f:id:mmorley:20161022223755p:plain:w500
 行、列、値を自由に組んで、データを整理・集計する機能です。

使い方

 Googleスプレッドシートは、無料で使えるオンラインの表計算Excelみたいな)アプリです。
 利用するにはGoogleアカウントが必要です。

2.新しいスプレッドシートを作成

  1. 画面右下の赤い『+』をクリック
    f:id:mmorley:20160511135546p:plain
    『無題のスプレッドシート』が開きます。
  2. 名前を変更
    名前は何でも構いません。
    f:id:mmorley:20160511140418p:plain:w500

3.テストデータをスプレッドシートにコピペする

  1. 下記のテストデータをコピーする(列タイトルも全部)
  2. スプレッドシートのシート1のA1セルを選択し、テストデータを貼り付ける

日付曜日店舗商品単価数量金額
2016/08/06A店8月08/01 - 08/07190203800
2016/08/06B店8月08/01 - 08/07牛乳150152250
2016/08/07A店8月08/01 - 08/07食パン100222200
2016/08/13B店8月08/08 - 08/14190142660
2016/08/14A店8月08/08 - 08/14バター180254500
2016/08/20B店8月08/15 - 08/21牛乳150162400
2016/08/21A店8月08/15 - 08/21食パン100121200
2016/08/27B店8月08/22 - 08/28バター180264680
2016/08/28A店8月08/22 - 08/28190183420
2016/08/28B店8月08/22 - 08/28食パン100141400
2016/09/03A店9月08/29 - 09/04牛乳150121800
2016/09/03B店9月08/29 - 09/04190254750
2016/09/04A店9月08/29 - 09/04牛乳150182700
2016/09/10A店9月09/05 - 09/11牛乳150243600
2016/09/11B店9月09/05 - 09/11バター180173060
2016/09/17A店9月09/12 - 09/18190193610
2016/09/18B店9月09/12 - 09/18食パン100151500
2016/09/24B店9月09/19 - 09/25バター180244320
2016/09/25A店9月09/19 - 09/25牛乳150223300
2016/09/25B店9月09/19 - 09/25190183420

4.ピボットテーブルを作成

  1. 貼り付けたデータの範囲内のセル(どこでも良い)を1つ選択
    (連続したデータをひとまとまりと見てくれる模様。
     もちろんデータを全部範囲選択でもOK。)
  2. メニューの『データ』-『ピボットテーブル』をクリック
    f:id:mmorley:20161028000025p:plain:w500

5.行、列、値を設定して完成

  1. ピボット テーブル 1というシートが開くので、
    『レポートエディタ』の『行- フィールドを追加』-『日付』をクリック
    f:id:mmorley:20161027164618p:plain:w500
  2. 『レポートエディタ』の『列- フィールドを追加』-『商品』をクリック
    f:id:mmorley:20161027164637p:plain:w500
  3. 『レポートエディタ』の『値- フィールドを追加』-『金額』をクリック
    f:id:mmorley:20161027164656p:plain:w500

以上で、下図のようにピボットテーブルは完成です。
あとは必要に応じて、データの表示形式、罫線、セルの背景色等を設定します。
 f:id:mmorley:20161027164713p:plain:w500

月、週単位のグループ化

 Excelのピボットテーブルはグループ化によって、月ごと、週ごとの集計が出来ますが、Goolgeスプレッドシートにはありません。
 月ごと、週ごとの集計をしたい場合は、あらかじめ元となるデータに月、週の列を作っておきます。(*テストデータ参照)

『日付』を『月』に変換

 日付データを月表示に変換します。
 例:2016/08/06 → 8月
 下記は、テストデータの『D2』のセルの関数です。
 『A2』に日付データが入っています。

=text(A2,"M月")

 『TEXT(数値, 表示形式)』は表示形式に従って数値を文字列に変換します。
 
 ちなみに、"2016/08"と表示したい場合は下記のようにします。

=text(A2,"yyyy/MM")

 行ラベルに『月』を設定するとピボットテーブルは、下図のようになります。
 f:id:mmorley:20161028014211p:plain:w500

『日付』を『週』に変換

 日付データをその日を含む週表示(月曜始まり)に変換します。
 例:2016/08/06 → 08/01 - 08/07
 下記は、テストデータの『E2』のセルの関数です。
 『A2』に日付データが入っています。

=text(A2-weekday(A2,3),"MM/dd") & " - " & text(A2-weekday(A2,3)+6,"MM/dd")

 『WEEKDAY(日付, [種類])』は、曜日を数値で取得します。
 [種類]を1にすると、日〜土が1〜7
 [種類]を2にすると、月〜日が1〜7
 [種類]を2にすると、月〜日が0〜6
 となります。
 
 上の例では、土日を同じ週にしたいので
 A2-weekday(A2,3)で、A2の週の先頭(月曜)の日付を求めています。
 A2-weekday(A2,3)+6で、A2の週の末尾(日曜)の日付を求めています。
 『TEXT』関数で、"08/01"の形式にしています。
 文字列は"&"でつなぐことが出来ます。
 
 ちなみに日曜始まりで計算するには、下記のようにします。

 =text(A2-weekday(A2,1)+1,"MM/dd") & " - " & text(A2-weekday(A2,1)+7,"MM/dd")

 行に『週』を設定するとピボットテーブルは、下図のようになります。
 f:id:mmorley:20161028014413p:plain:w500

『日付』を『曜日』に変換

 ついでに、日付データを曜日表示に変換する方法も。
 例:2016/08/06 → 土
 下記は、テストデータの『B2』のセルの関数です。
 『A2』に日付データが入っています。

=text(A2,"ddd")

 ちなみに『2016/08/06 → 土曜日』と変換するには、下記のようにします。

=text(A2,"dddd")

 dが1つ増えます。

行、列、値に複数のラベルを設定

 行、列、値には複数のラベルを設定することも出来ます。
 表示順序も自由に設定出来ます。
 レポートエディタ上でドラッグすることで、後から順序を変更することも出来ます。
 f:id:mmorley:20161028020405p:plain:w500

あとがき

 今まで向き合うことのなかった『ピボットテーブル』という機能。
 実際に使ってみると、そんなに難しいものではありませんでした。
 『ピボットテーブル』を使わなくても、力技で同様の編集は出来ると思いますが、使えば労力がかなり減ると思います。
 頭の片隅にあれば、いつか活きるかなと思いました。