今回は、Excelのデータモデルのリレーションシップで表をつなぐ方法を紹介します。
データモデルで表をつなぐ理由
Excelで複数の表を扱うとき、すべての情報を1つの大きな表にまとめようとすると、同じ情報が何度も出てきて管理しにくくなります。売上一覧、商品マスター、顧客マスター、担当者一覧などは、別々の表として管理したほうが分かりやすいことがあります。
Excel データモデル
リレーションシップを使うと、共通するキーをもとに複数の表を関連付け、ピボットテーブルでまとめて集計できます。VLOOKUPやXLOOKUPで列を追加しなくても、表同士の関係を使って分析できます。
たとえば、売上一覧には商品コードだけを持たせ、商品名やカテゴリは商品マスターに置きます。データモデルで商品コードを関連付ければ、売上一覧と商品マスターを組み合わせてカテゴリ別売上を集計できます。
リレーションシップに必要な考え方
リレーションシップでは、2つの表を共通の列でつなぎます。この共通の列をキーとして扱います。キーになる列は、片方の表で重複しない値になっていることが重要です。
- 売上一覧の商品コード
- 商品マスターの商品コード
- 顧客一覧の顧客ID
- 担当者マスターの社員番号
- 日付テーブルの日付
売上一覧では同じ商品コードが何度も出てきますが、商品マスターでは商品コードごとに1行だけにします。この関係が整っていると、集計時に商品カテゴリや商品名を正しく参照できます。
マスター表を整える
リレーションシップを作る前に、マスター表の重複や空白を確認します。商品コードが重複していると、どの商品情報を使えばよいか判断できません。空白のコードがある場合も、関連付けが不安定になります。
マスター表は、列名を分かりやすくし、1行1件で管理します。見出し行が複数ある表や、途中に小計行がある表は、データモデルに向いていません。
データモデルに追加する流れ
Excelでは、テーブル化したデータをデータモデルへ追加できます。複数の表をデータモデルに入れ、リレーションシップを設定すると、ピボットテーブルで一緒に使えます。
- 各表をExcelテーブルにする
- テーブル名を分かりやすく変更する
- ピボットテーブル作成時にデータモデルへ追加する
- リレーションシップの管理を開く
- 共通するキー列を指定して表を関連付ける
- ピボットテーブルで複数の表の項目を使う
テーブル名は「売上一覧」「商品マスター」のように内容が分かる名前にします。既定のまま「テーブル1」「テーブル2」にしておくと、リレーションシップ設定やピボット作成時に迷いやすくなります。
キー列のデータ型をそろえる
同じ商品コードに見えても、片方が数値、片方が文字列だと関連付けがうまくいかないことがあります。先頭ゼロがあるコードは文字列として扱い、両方の表で形式をそろえます。
空白や余分なスペースも確認します。見た目では同じでも、前後に空白が入っていると別の値として扱われることがあります。
ピボットテーブルで活用する
リレーションシップを設定すると、ピボットテーブルで複数の表のフィールドを組み合わせられます。売上一覧の金額を値に置き、商品マスターのカテゴリを行に置く、といった使い方ができます。
- 売上一覧の金額を集計する
- 商品マスターのカテゴリで分類する
- 顧客マスターの地域で集計する
- 担当者マスターの部署別に見る
- 日付テーブルで年月別に集計する
元の売上一覧にカテゴリ列を追加しなくても、マスター表との関係で集計できる点が便利です。マスター情報を修正すれば、関連する集計にも反映しやすくなります。
集計結果が合わないとき
集計結果が想定と違う場合は、リレーションシップの向きやキー列を確認します。商品コードに空白がある、マスターに存在しないコードが売上一覧にある、マスター側でコードが重複している、といった原因が考えられます。
ピボットテーブルだけを見ても原因が分かりにくい場合は、元表でキー列を確認します。未登録コードを抽出するチェック表を作っておくと、更新時にも確認しやすくなります。
データモデルを使うときの注意点
データモデルは便利ですが、表の作り方が曖昧だと扱いにくくなります。どの表が明細で、どの表がマスターなのかを決めておくことが大切です。
- 明細表とマスター表を分ける
- キー列を固定する
- マスター表の重複をなくす
- 列名を分かりやすくする
- 更新手順を決めておく
表を分けると、最初は複雑に見えることがあります。しかし、情報の重複を減らせるため、長く使う集計では管理しやすくなります。
リレーションシップは、表をきれいにつなぐための土台です。キー列の品質が低いと、集計結果にも影響します。
日付テーブルを用意する考え方
月別や四半期別に集計したい場合は、日付テーブルを用意すると管理しやすくなります。売上一覧の日付列だけでも集計はできますが、年月、月名、年度、四半期などを日付テーブルに持たせておくと、ピボットテーブルで期間を扱いやすくなります。
日付テーブルも、ほかのマスター表と同じように日付をキーとして関連付けます。売上一覧の日付と日付テーブルの日付が同じ型であることを確認します。年度の開始月が通常のカレンダーと違う会社では、年度や期の列を日付テーブルに持たせると、毎回計算列を作らずに集計できます。
表を追加するたびにリレーションシップを増やすのではなく、分析に必要な関係だけを作ることも大切です。使わない表や曖昧なキーをつないでおくと、後から見たときに構造が分かりにくくなります。データモデルは、表の数より関係の明確さを優先します。
共有するブックでは、どの列をキーにしているかをメモしておくと引き継ぎやすくなります。表名とキー列が分かれば、更新時の確認も進めやすくなります。
まとめ
Excelのデータモデルのリレーションシップを使うと、複数の表を共通キーでつなぎ、ピボットテーブルでまとめて集計できます。売上一覧と商品マスター、顧客一覧と地域情報などを分けて管理しながら分析できます。
リレーションシップを作る前には、キー列の重複、空白、データ型を確認します。マスター表は1行1件で整え、明細表との関係を分かりやすくしておきます。
Excel データモデル
リレーションシップは、VLOOKUPで列を増やし続ける方法とは違う集計の考え方です。表の役割を分け、キーを整えることで、更新しやすい分析環境を作れます。