『ピボットテーブル』・・・私にとってExcelの分からない＆使ったことがない機能ですが、ちょっと気になってました。
先日、Googleスプレッドシートにも同様の機能があることを知り、良い機会なので使い方等を調べてみました。
目次
使用環境
私が使用している環境です。
- Mac OS X El Capitan Version 10.11.6
- ブラウザ：Google Chrome Version 54.0.2840.59 (64-bit)
ピボットテーブルとは？
ピボットテーブルとは、下記のような雑然としたデータを、
このようにだったり、
このようにだったりと、
データを見やすいように、行・列・値を自由に組み合わせて、整理・集計する機能です。
使い方
Googleスプレッドシートは、無料で使えるオンラインの表計算（Excelみたいな）アプリです。利用するにはGoogleアカウントが必要です。
手順3. テストデータをスプレッドシートにコピペする
- 下記のテストデータをコピーする（列タイトルも全部）
- スプレッドシートのシート1のA1セルを選択し、テストデータを貼り付ける
日付 曜日 店舗 月 週 商品 単価 数量 金額 2016/08/06 土 A店 8月 08/01 - 08/07 卵 190 20 3800 2016/08/06 土 B店 8月 08/01 - 08/07 牛乳 150 15 2250 2016/08/07 日 A店 8月 08/01 - 08/07 食パン 100 22 2200 2016/08/13 土 B店 8月 08/08 - 08/14 卵 190 14 2660 2016/08/14 日 A店 8月 08/08 - 08/14 バター 180 25 4500 2016/08/20 土 B店 8月 08/15 - 08/21 牛乳 150 16 2400 2016/08/21 日 A店 8月 08/15 - 08/21 食パン 100 12 1200 2016/08/27 土 B店 8月 08/22 - 08/28 バター 180 26 4680 2016/08/28 日 A店 8月 08/22 - 08/28 卵 190 18 3420 2016/08/28 日 B店 8月 08/22 - 08/28 食パン 100 14 1400 2016/09/03 土 A店 9月 08/29 - 09/04 牛乳 150 12 1800 2016/09/03 土 B店 9月 08/29 - 09/04 卵 190 25 4750 2016/09/04 日 A店 9月 08/29 - 09/04 牛乳 150 18 2700 2016/09/10 土 A店 9月 09/05 - 09/11 牛乳 150 24 3600 2016/09/11 日 B店 9月 09/05 - 09/11 バター 180 17 3060 2016/09/17 土 A店 9月 09/12 - 09/18 卵 190 19 3610 2016/09/18 日 B店 9月 09/12 - 09/18 食パン 100 15 1500 2016/09/24 土 B店 9月 09/19 - 09/25 バター 180 24 4320 2016/09/25 日 A店 9月 09/19 - 09/25 牛乳 150 22 3300 2016/09/25 日 B店 9月 09/19 - 09/25 卵 190 18 3420
手順4. ピボットテーブルを作成
- 貼り付けたデータの範囲内のセル（どこでも良い）を１つ選択
連続したデータをひとまとまりと見てくれるようです。
もちろんデータを全部範囲選択でもOK。
- メニューの『データ』-『ピボットテーブル』をクリック
手順5. 行、列、値を設定して完成
- ピボット テーブル 1というシートが開くので、
『レポートエディタ』の『行- フィールドを追加』-『日付』をクリック
- 『レポートエディタ』の『列- フィールドを追加』-『商品』をクリック
- 『レポートエディタ』の『値- フィールドを追加』-『金額』をクリック
以上で、下図のようにピボットテーブルは完成です。
あとは必要に応じて、データの表示形式、罫線、セルの背景色等を設定します。
月、週単位のグループ化
Excelのピボットテーブルはグループ化によって、月ごと、週ごとに集計が出来ますが、Goolgeスプレッドシートにはこの機能がありません。
Goolgeスプレッドシートで、月ごと、週ごとに集計したい場合は、あらかじめ元となるデータに月、週の列を作っておきます。（＊テストデータ参照）
『日付』を『月』に変換
日付データを『月表示』に変換します。
例：2016/08/06 → 8月
下記は、テストデータの『D2』のセルの関数です。
『A2』に日付データが入っています。
=text(A2,"M月")
『TEXT(数値, 表示形式)』関数は、表示形式に従って数値を文字列に変換します。
ちなみに、"2016/08"と表示したい場合は下記のようにします。
=text(A2,"yyyy/MM")
行ラベルに『月』を設定するとピボットテーブルは、下図のようになります。
『日付』を『週』に変換
日付データを、その日を含む週の表示（月曜始まり）に変換します。
例：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
[種類]を3にすると、月〜日が0〜6
となります。
上の式の各部を説明します。
土日を同じ週としているので、月曜が週の先頭になります。
A2から、A2の週の月曜の日付を求める式は、
=A2-weekday(A2,3)
上の式の意味は、
『A2の週の月曜の日付』=『A2』-『A2が月曜から何日目か』
です。
A2（日付）の週の末尾の日曜の日付を求める式は、
=A2-weekday(A2,3)+6
『TEXT』関数で、求めた日付を"08/01"の形式にしています。
それぞれの文字列を"&"を使って結合しています。
ちなみに日曜始まりで計算するには、下記のようにします。
=text(A2-weekday(A2,1)+1,"MM/dd") & " - " & text(A2-weekday(A2,1)+7,"MM/dd")
行に『週』を設定するとピボットテーブルは、下図のようになります。
『日付』を『曜日』に変換
ついでに、日付データを曜日表示に変換する方法も。
例：2016/08/06 → 土
下記は、テストデータの『B2』のセルの関数です。
『A2』に日付データが入っています。
=text(A2,"ddd")
ちなみに『2016/08/06 → 土曜日』と変換するには、下記のようにします。
=text(A2,"dddd")
dが１つ増えます。
行、列、値に複数のラベルを設定
行、列、値には複数のラベルを設定することも出来ます。
表示順序も自由に設定出来ます。
レポートエディタ上でドラッグすることで、後から順序を変更することも出来ます。
あとがき
今まで向き合うことのなかった『ピボットテーブル』という機能。
実際に使ってみると、そんなに難しいものではありませんでした。
『ピボットテーブル』を使わなくても、力技で同様の編集は出来ると思いますが、使えば労力がかなり減ると思います。
頭の片隅にあれば、いつか活きるかなと思いました。
