Excelのテーブル機能でリレーショナルデータベースをつくる

この記事は週末プログラミングの一環です。

Excelでデータ分析が簡単になってきているという話を何度かしています。

Excelでピボットテーブル使う人に全力でPowerQueryをお勧めしたい

昔から使われている機能にピボットテーブルがありますが、標準で作成するとデータソースは一つの表でなければいけません。

しかし、データ分析を少し凝ったものにする場合、データソースが一つの表であるのは不便です。マスター情報など、データベースを分けた方が、データ管理という点では便利だからです。

この記事のテーマは、Excelで複数データを紐付けて、リレーショナルデータベースを構築し、ピボットテーブルで集計できるようにすることです。

 

Excelの標準機能で実現できる

Excelには「テーブル」という機能があります。

Excelのテーブル機能の使い方まとめ | あなたのExcelスキルが120%活かせるWebアプリ作成ツール -Forguncy(フォーガンシー) | グレープシティ株式会社

テーブル機能自体は、見栄えがよくなったり、値の指定がしやすくなるなどありますが、個人的に気に入っている機能があります。それがリレーショナル機能です。

 

リレーショナル機能の使い方

これを使って、複数のテーブルを作り、テーブル間のリレーショナルを定義すれば、Excel上でリレーショナルデータベースを作ることができます。

Excel のテーブル間にリレーションシップを作成する – Office サポート

データベース設計の観点からいくと、いくつかのテーブルに正規化で分けたくなるんですよね。そういう場合も、テーブル機能でリレーションを作成すれば、Excelでもちょっとしたリレーショナルデータベースが実現できます。

 

複数テーブルをまたいでピボットテーブルを作る

連結したテーブルは、ピボットテーブルでまとめて集計することができます。逆にそれ以外に利用方法はあるんでしょうか…

挿入からピボットテーブルを選び、「外部データソースを使用」を選択します。

外部データソースを使用

そして、「テーブル」タブで「このブックのデータモデル」を選びます。前述のデータのリレーションを設定していないと、この選択が表示されないので注意してください。

このブックのデータモデル

そうすると、フック内の全てのテーブルを対象にピボットテーブルで扱うことができます。

 

リレーション機能を使った場合の注意事項

ただ、リレーションを作ると、ピボットテーブルの集計フィールドやグループ化が使えなくなります。これはこれでデメリットですね。。。

リレーションシップで出来なくなること(集計フィールドの追加とグループ化) / ピボットテーブル / Excel2013: haku1569 Excel でらくらく データ分析!

 

以上、Excelでリレーショナルデータベースを構築して、ピボットテーブルで集計できるようにする方法でした。