Excelでリレーショナルデータベース(RDB)を作る【テーブル間を関連付け】
- 「マスタデータの更新時に、
書き換え箇所が多くて手間がかかる…」 - 「より効率的にデータ管理を行いたい…」
- 「Excelでリレーショナルデータベースを構築できないだろうか…」
Excel（エクセル）
本記事では、
目次
リレーショナルデータベース(RDB)とExcelフラットテーブルの違い
テーブルの結合方法を理解するには、
リレーショナルデータベース（RDB）
一方、
両者の違いを以下の表にまとめました。
| 項目 | リレーショナルデータベース（RDB） | Excelフラットテーブル |
|---|---|---|
| データ保持形態 | 役割ごとに分割された 複数 テーブル | 単一 のシート（2次元の表） |
| データの冗長性 | マスタデータの一元化により排除 | 同一データ（名称等） |
| データ変更時の影響 | マスタの1箇所を変更すれば全データに反映 | 全重複箇所の修正が必要（修正漏れのリスク有） |
| 最大レコード数 | 数百万〜数億行以上 | 1シートあたり最大 1,048,576行 |
| 複数人同時編集 | レコード（行） | ファイルロックがかかり原則、 |
実現方法：疑似RDB運用のためのテーブルの「分割」
Excelで疑似的にRDB運用をするには、
- ステップ1. テーブルを分割（正規化）
してデータ管理する - ステップ2. テーブルを紐づけ・結合してデータ利用する
テーブル同士を結びつけるときには、
- 主キー（プライマリキー / Primary Key）
：
テーブル内で各レコードを「一意（ユニーク）」 に識別するためのフィールド。 重複する値（ダブり） や空白（Null） は許容されない。 - 例：社員ID、
商品コード、 顧客ID。
- 例：社員ID、
- 外部キー（フォーリンキー / Foreign Key）
：
別のテーブルの主キーを参照するために、自テーブル内に配置するフィールド。 これによりテーブル間のリレーション（関連付け） が成立する。 - 例：売上テーブル内の「商品コード」
。
- 例：売上テーブル内の「商品コード」
マスタテーブルのみを複数のブックで使い回すだけでもデータを一元管理でき、
本記事では、
正規化とは
データベース設計では、
正規化によって作られるテーブルの種類は、
- 商品情報などの「属性」
データを格納する マスタテーブル - 売上などの「イベント」
の記録データを格納する トランザクションテーブル
| 格納データ | テーブルの種類 | テーブル名のプレフィックス（接頭語） | 例 |
|---|---|---|---|
| 属性 | マスタ（ディメンション） | M_（Master） | 商品リスト、 |
| イベント | トランザクション（ファクト） | T_（Transaction / Table） | 売上明細、 |
マスタテーブルとトランザクションテーブルの例
以降の説明で使用する具体例を示します。
※ 以降の説明では、
【マスタテーブル】
テーブル名： M_商品
| 商品コード | 商品名 | 単価 |
|---|---|---|
| P-01 | ノートPC | 120,000 |
| P-02 | モニター | 35,000 |
| P-03 | マウス | 5,000 |
【トランザクションテーブル】
テーブル名： T_売上明細
| 売上ID | 日付 | 商品コード | 数量 |
|---|---|---|---|
| 1001 | 2026/6/1 | P-01 | 1 |
| 1002 | P-03 | 2 | |
| 1003 | 2026/6/2 | P-02 | 1 |
| 1004 | P-01 | 3 |
M_商品 がマスタテーブル、
Excelで複数のテーブルを紐付ける手法には、
- 手法①. 関数による「XLOOKUP / VLOOKUP（動的参照）
」 - セル単位で他の表からデータを検索・転記する、
従来からある最も身近な手法。
- セル単位で他の表からデータを検索・転記する、
- 手法②. Power Queryによる「マージ（物理結合）
」 - 2つの表を特定のキー（IDなど）
をもとに横方向へ合体させ、 1つの新しいフラットな表 を作り出す手法。 SQLのJOINやVLOOKUP関数の処理を自動化・高度化したもの。
- 2つの表を特定のキー（IDなど）
- 手法③. データモデル（Power Pivot）
による「リレーションシップ（論理関連付け） 」 - 表を合体させず、
それぞれ 独立した状態のまま共通のキーで「繋がり（関係性） 」 だけを定義 する手法。 AccessなどのRDBの仕組みに最も近い。 シート上にはピボットテーブルとして出力できる。
- 表を合体させず、
各手法の使い分け基準
上記の3つの手法は、
| 評価項目 | ③ 関数：XLOOKUP等 | ① Power Query：マージ | ② データモデル：リレーションシップ |
|---|---|---|---|
| 最終出力 | 通常のセル | 結合済みの 1つのテーブル / ピボットテーブル | ピボットテーブル |
| データ量（目安） | 🔴 数千行程度（小規模） | 🟡 数万行〜数十万行程度 | 🟢 数十万〜数百万行以上（大量データ） |
| 処理速度・負荷 | 🔴 行数・関数が増えるとExcelが極端に重くなる | 🟡 行数が増えると更新に時間がかかる | 🟢 インメモリエンジンにより高速・軽量 |
| データの状態 | セル内に他表の値が転記される | 物理的に1つに合体する | 独立した状態を維持する |
| 追加のデータ整形 | 🟢 各自セル上で行える | 🟢 可能（結合後にさらに列加工ができる） | 🟡 不可（集計やDAX計算がメイン） |
使い分けの判断フロー
テーブルを結合するときにどの手法を使うかは、
- 「数千行程度の小さな表で、
その場でパッと1〜2つの項目だけを別表から持ってきたい」 - ⇒ 手法③ 関数（XLOOKUP等）
- ⇒ 手法③ 関数（XLOOKUP等）
- 「結合した後に、
さらに列の分割や条件フィルターなどの加工を加えて、 最終的に1つの表としてシートに出力したい」 - ⇒ 手法① Power Queryのマージ
- 「集計画面（ピボット）
だけがあればよく、 データ量が数十万行以上ある」 -
⇒ 手法② データモデルのリレーションシップ。
表を合体させないため、
データの重複（冗長性） が発生せず、 ファイルサイズを小さく抑えられる。
-
XLOOKUP関数やVLOOKUP関数、
XLOOKUP関数の構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])今回の例における各引数の指定内容は以下の通りです。
- 検索値：T_売上明細（トランザクションテーブル）
内の外部キー「商品コード」 が入ったセル - 検索範囲：M_商品（マスタテーブル）
内の主キー「商品コード」 の列 - 戻り範囲：M_商品（マスタテーブル）
内の取得したい値（「商品名」 や「単価」 ） の列
操作手順
- T_売上明細 テーブルの隣に「商品名」
列を作成する。 - セルに以下の数式を入力する。
=XLOOKUP([@商品コード], M_商品[商品コード], M_商品[商品名])
テーブルの構造化参照を使うと、
Power Queryはテーブル単位でデータの加工が行え、
操作手順
- データの取り込み：結合したい2つの表（M_商品 と T_売上明細）
をPower Queryエディターに読み込む。 - マージの実行：[ホーム] タブ > [クエリのマージ] を選択する。
- キーの指定：2つの表のプレビュー画面が表示されるので、
共通するキー列（「商品コード」 ） をそれぞれクリックして選択する。 - 列の展開：マージすると、
相手方の表が「Table」 という形式で右端に結合される。 セルの右上にある展開アイコンをクリックし、 相手方の表（M_商品） から必要な列（「商品名」 や「単価」 など） だけを選択して展開する。 - 読み込み：整形が終わったら、
[閉じて読み込む] を押してExcelシートに「テーブル」 として出力する。
詳細は下記の記事にまとめています。
データモデルの「リレーションシップ」
操作手順
データモデルへの追加
テーブルのセルを選択し、
リレーションシップの定義
以下のいずれかの方法で、
通常のExcel画面上での設定：
- [データ] タブ > [リレーションシップ] を選択する。
- [新規作成] をクリックし、
関連付けるテーブル（T_売上明細 と M_商品） および列（「商品コード」 ） を選択する。
Power Pivotエディター上での設定：
- Power Pivotエディターの [ホーム] タブ > [ダイアグラムビュー] を開く。
- 関連付けるテーブル間のキー列（「商品コード」
） をドラッグ＆ドロップで線をつなぐ。
ピボットテーブルの作成
- [挿入] タブ > [ピボットテーブル] > [データモデルから] を選択する。
- 「M_商品 の商品名」
をピボットの行に配置し、 「T_売上明細 の数量」 を値に配置する。
これにより、
社内の共通マスタなど、
（手法①）
- テーブルのインポート：Power Queryを使用し、
外部のマスタテーブルを読み込む。 - シート出力：読み込んだマスタテーブルを、
利用先ブック内のシートへ「テーブル」 として一度出力する。 - セル単位の動的参照：利用先ブック内に配置されたマスタテーブルのコピーをセル単位で参照し、
XLOOKUP や VLOOKUP 関数を用いてもう一方のテーブルの列（セル） に値を挿入する。
数式による別ブック参照の仕様・制限
Power Queryでなく、
- セル参照（通常の座標参照）
の場合の挙動：
ソースブック側のシート名・シートレイアウト（セルの位置）の変更によってリンク切れが発生する。 意図しないリンク切れの発生リスクが高く、 メンテナンス性が大幅に低下する。 - 構造化参照（テーブル参照）
の場合の挙動：
ソースブックが開いている状態でなければ数式が正常に計算されない。ファイルを閉じるとエラーや計算不能の状態になる。
（手法②）
- テーブルのインポート：外部ファイルのマスタテーブルと、
利用先ブックのトランザクションテーブルの両方をPower Queryのエディター内へ読み込む。 - テーブルの結合：シート上にデータを出力する前に、
Power Queryエディター内で共通のキーを元に2つのテーブルを横方向に結合する。 - クレンジングとテーブル出力：結合されたテーブルに対して、
不要な列の削除や型変更を行い、 Excelシート上へ1つの「テーブル」 として出力する。
（手法③）
- テーブルのインポート：外部ファイルのマスタテーブルをPower Queryで読み込み、
データ型などの最低限の前処理（クレンジング） を行う。 - データモデルへの追加：Power Queryの出力先として「データモデル」
を選択し、 直接Power Pivotに渡す。 - 論理結合（リレーション）
：データモデルの管理画面（Power Pivotウインドウ） を開き、 共通キーの列同士をドラッグ＆ドロップで結びつけ、 テーブル間のリレーションシップのみを定義する。 - ピボットテーブル配置：データモデルをデータソースとした「ピボットテーブル」
を配置して多角的な分析を行う。
ExcelをRDBとして運用する場合、
1. データ容量と処理速度の限界（ファイル肥大化）
Excelの1シートの行数上限は 1,048,576行 です。
仕様上の上限未満であっても、
2. 同時編集・排他制御の欠如
排他制御 とは、
Excelはもともと同時編集には弱く、
3. データ不整合のリスク（入力規制の限界）
RDBでは、
システム的な強制力（制約条件）
本記事では、
- テーブル設計：属性データを格納する「マスタ」
とイベントデータを格納「トランザクション」 に分割する正規化を行う - テーブル間の紐づけ：目的とデータ量に応じて3つの手法を使い分ける
- XLOOKUPなどの関数
- Power Queryのマージ
- Power Pivot（データモデル）
のリレーションシップ
Excelをデータベースとして使用する場合の注意点・限界、