スプレッドシート 仕事効率化

簡単データ解析!スプレッドシートのピボットテーブルを解説

スプレッドシートにはExcelと同じくピボットテーブルが作成できます。

ピボットテーブルを使えば、データベースの分析が簡単にできます。

例えば、店舗の売上データにピボットテーブルを使うと店舗ごとや商品ごとの売上高など整理したデータが簡単に調べられます。

今回の例では実際の名古屋市の不動産取引データから、区ごとの取り引などを整理しています。

この記事を読めば基本的なスプレッドシートのピボットテーブルの使い方がわかります。

また、スプレッドシートのピボットテーブルはExcelと少し操作感が違います。

Excelを使っていた人も読んで役に立つはずです。

スプレッドシートのExcelとの違いは下記にまとめてあります。

参考スプレッドシートとは?Excelの代わりに無料で使える表計算ソフトの使い方

Excelは仕事には欠かせないソフトですが、導入するには1万円以上かかります。 事務処理の副業で稼ぎたい人や私生活での小遣い帳作りたい人にとっては、1万円は高すぎます。 そこで紹介したいのがスプレッド ...

続きを見る

ピボットテーブルとは

ピボットテーブルとはデータベースに蓄積された大量のデータを色々な角度で整理できる表を作成できる機能です。

とてもデータ分析に役立つ機能です。

この記事では実例を交えれどのように整理できるかを解説します。

 

ピボットテーブルに用いるデータベースとは

ピボットテーブルを作成するためにはデータベースを用意しなけばなりません。

データベースとは検索や蓄積が容易にできるよう整理された情報の集まりです。

例えば住民基本台帳や銀行の口座などが当たります。

ピボットテーブル作成のためスプレッドシートにデータベースに求められる要件が2つあります。

これが守られてないとピボットテーブルがうまく作れません。

①1行目はデータベースの項目名を入れる

1行目にはデータベースの項目名を入れてください。

上の例でもNO,種類、地域、都道府県名・・と項目名を並べて下にデータが蓄積されています。

ピボットテーブルは一番上の行を項目名だと自動的に判断してデータを整理します。

項目の名称が2行に渡るようなデータベースはうまく整理できません。

②複数行に渡るデータを入力しない。

ピボットテーブルでは複数行に渡るデータを入力してはいけません。

データが埋まっていない空白ができます。

ピボットテーブルはそこでデータが途切れていると判断してしまい、4行目までのデータだけで整理をします。

複数行に渡るデータを入力せずに1行に1件ずつ入力しましょう。

また、できれば1列目のINDEXになるようにしたいです。

データベースを準備する方法

データベースを準備する方法もいくつかの方法があります。

自力で既存データを整形する

分析したいデータがデータベース要件を満たす形式になっていればよいですが、そうでない場合も多々あります。

自力で切り貼りする、またはマクロを利用するなどで整形する必要があります。

整形済みのデータベースをインタネット上で見つける。

今回の名古屋市の不動産取引のデータは下記のサイトから入手しています。

https://www.land.mlit.go.jp/webland/download.html

説明しやすくするため、名古屋市だけのデータに絞っています。

スプレッドシートのImportxml関数を利用する

Importxml関数を利用することで、インターネット上の情報を整理してデータベース形式に並べることができます。

下記の記事を参考にしてください。

参考5分でできる! スプレッドシートのIMPORTXMLを使った不動産情報収集の効率化

不動産投資の情報収取を効率化するために、スプレッドシートのIMPORTXMLがとても便利です。 情報を集めるために不動産情報サイトを利用が便利です。 しかし、不動産情報サイトの欠点は一覧性に欠けるとい ...

続きを見る

参考スプレッドシートのImportxmlで検索サイトの結果を取得する方法

スプレッドシートのImportxmlは情報収集には便利な関数です。 Importxmlの実例を交えた使い方の解説は下記の記事を参照ください。 この関数でGoogle検索の結果を取得できたら、情報収集に ...

続きを見る

 

ピボットテーブルの作成の手順

スプレッドシートでのピボットテーブルの作成の手順を解説していきます。

  1. スプレッドシート上のデータベースのどこかのセルを選択
  2. データのタブを選択
  3. ピボットテーブルを選択
  4. そのまま、新しいシートで作成を選択

データベースを構成するセルの一部にでも触れていると、スプレッドシートはそのデータベース全体を勝手に判断してくれます。

また、ピボットテーブルはデータベースのある表に作成することも可能ですが、特にこだわらないなら、新しいシートに作成すれば良いです。

下が参考の動画になります。

これで新しいシートに空のピボットテーブルが作成されました。

このピボットテーブルに設定を施すことで様々な分析ができます。

 

ピボッドテーブルで区ごとの取引価格の合計を知りたい

栄

栄wikipediaより

守山区

守山区wikipediaより

名古屋市には16の区があり、緑が多い区もあれば開発の盛んな区もあります。

それぞれ不動産の取引額も違うはずです。

実際どの区が取引額が大きいでしょうか?

ピボットテーブルなら簡単に整理できます。

ピボットテーブルをクリックするとピボットテーブエディタが表示されます。

 

候補、行、列、値、フィルタといろいろな項目が表示されます。

基本的に行と列に整理したい区分、値に整理対象の値を選択します。

まずはだけを使って下記の手順で整理できます。

  1. 行の追加を選択
  2. 市町村名を選択
  3. 値の追加を選択
  4. 取り引き価格を選択

上の手順で区ごと取り引き価格の合計値が選択できます。

動画にすると下記になります。

Image from Gyazo

値の選択はデフォルトで合計値が表示されます。

これを集計のオプションをSUMからAVERAGEに変えることで平均値に変えることも可能です。

それ以外にも最大値や最小値、データの個数などいろいろな集計が可能です。

 

取引額が大きな順番(昇順)で並べる

取引額で整理ができたら、ランキングを付けたくなるものです。

ピボットテーブルなら下記の手順で順番に並べることも可能です。

  1. 行の市町村名の左の並び替えを昇順から降順に変更
  2. 行の市町村名の右の並び替えを市町村名から取引価格に変更に変更

動画では下記の手順になります。
Image from Gyazo

名古屋市の中区の不動産取引価格が最大であることが分かりました。

名古屋市の中区は名古屋最大の繁華街である栄があります。

取引額が大きいこともうなずけます。

名古屋市中区wikipediaより

ピボットテーブルの列と行を利用して取り引きの種類ごとに整理

もう少し複雑な分析に進みましょう。

不動産の取り引きには4種類があります。

「土地」、「土地と建物)」、「中古マンション等」、「 農地」の4種類です。

それぞれどの取引が活発なのかピボットテーブルならすぐに分かります。

列の追加を選択して種類を選択するだけです。

これだけで、それぞれの種類ごとにデータを整理できます。

土地の取り引きは中村区が最も盛んだったり、中古マンション取り引きは千種区が最も盛んだったりすることが分かります。

ピボットテーブルのフィルターを利用した整理

さらにフィルターの機能を利用した整理を紹介します。

都市計画法により工業地域、商業地域、住宅地域3つの区分に分かれています。

あなたがも住宅の土地を買うために分析していたとしたら、住宅地域だけで分析したほうが良いです。

フィルターの機能を使えば住宅用の地域合計が表示可能です。

手順は下記です。

  1. フィルタの追加を選択
  2. 地域を選択
  3. 現在のフィルタを選択
  4. 値でフィルタをクリア
  5. 住宅地を選択
  6. OKを選択

下記が手順の動画です。

Image from Gyazo

上記のようにすることで住宅地域に絞った取り引きを確認できます。

例えば中区は住宅用の宅地と土地の取り引きが少ないことがわかります。
ピボットグラフでわかりやすく表示

ピボットテーブルはグラフ化することでわかりやすくビジュアルで表示できます。

このグラフのことをピボットグラフといいます。

  1. 挿入を選択
  2. グラフを選択
  3. ピボットテーブルをクリックしてテーブルエディタを表示
  4. 行の市町村の総額を非表示

手順の動画は下記になります。

Image from Gyazo

作成されたグラフは下のようになります。

グラフにすることで、それぞれの区の大小関係がよりわかりやすくなり、新たな発見につながるかもしれません。

プレゼンテーション資料としてもグラフは欠かせないでしょう。

まとめ

スプレッドシートでのピボットテーブについて解説しました。

ピボットテーブルはExcelの操作感が違うのでぜひこの記事を参考にしてください。

最後まで読んでいただきありがとうございました。

この記事が役に立ちましたらSNSでシェアして頂けると嬉しいです。

-スプレッドシート, 仕事効率化

Copyright© テクブロ , 2020 All Rights Reserved Powered by AFFINGER5.