この記事は週末プログラミングの一環です。
Excelでデータ分析が簡単になってきているという話を何度かしています。
昔から使われている機能にピボットテーブルがありますが、標準で作成するとデータソースは一つの表でなければいけません。
しかし、データ分析を少し凝ったものにする場合、データソースが一つの表であるのは不便です。マスター情報など、データベースを分けた方が、データ管理という点では便利だからです。
この記事のテーマは、Excelで複数データを紐付けて、リレーショナルデータベースを構築し、ピボットテーブルで集計できるようにすることです。
Excelの標準機能で実現できる
Excelには「テーブル」という機能があります。
Excelのテーブル機能の使い方まとめ | あなたのExcelスキルが120%活かせるWebアプリ作成ツール -Forguncy（フォーガンシー） | グレープシティ株式会社
テーブル機能自体は、見栄えがよくなったり、値の指定がしやすくなるなどありますが、個人的に気に入っている機能があります。それがリレーショナル機能です。
リレーショナル機能の使い方
これを使って、複数のテーブルを作り、テーブル間のリレーショナルを定義すれば、Excel上でリレーショナルデータベースを作ることができます。
Excel のテーブル間にリレーションシップを作成する – Office サポート
データベース設計の観点からいくと、いくつかのテーブルに正規化で分けたくなるんですよね。そういう場合も、テーブル機能でリレーションを作成すれば、Excelでもちょっとしたリレーショナルデータベースが実現できます。
複数テーブルをまたいでピボットテーブルを作る
連結したテーブルは、ピボットテーブルでまとめて集計することができます。逆にそれ以外に利用方法はあるんでしょうか…
挿入からピボットテーブルを選び、「外部データソースを使用」を選択します。
そして、「テーブル」タブで「このブックのデータモデル」を選びます。前述のデータのリレーションを設定していないと、この選択が表示されないので注意してください。
そうすると、フック内の全てのテーブルを対象にピボットテーブルで扱うことができます。
リレーション機能を使った場合の注意事項
ただ、リレーションを作ると、ピボットテーブルの集計フィールドやグループ化が使えなくなります。これはこれでデメリットですね。。。
リレーションシップで出来なくなること（集計フィールドの追加とグループ化） / ピボットテーブル / Excel2013: haku1569 Excel でらくらく データ分析！
以上、Excelでリレーショナルデータベースを構築して、ピボットテーブルで集計できるようにする方法でした。
