Excelデータベースの正規化とは？表/テーブルの分割手順と実務解説
- 「Excelテーブルでデータ管理しているが、
重複が多くて修正するときが大変…」 - 「Excelでリレーショナルデータベースを再現したいが、
テーブル設計の考え方がわからない…」 - 「表をマスタとトランザクションに分割する方法が知りたい…」
このような問題は、
本記事では、
「Excelデータベース」
Excelで「データベース」
Excelによるデータベースの基本については、
» Excelでデータベースの作成方法｜データ管理の基本ルールから限界まで解説
目次
テーブルを分割せずに1つの表に情報を詰め込み続けると、
重複データの発生
同じ顧客が複数回購入するたびに、
更新時異状（アノマリー）
データを追加・修正・削除するたびに、
| 異状の種類 | 具体的な問題 |
|---|---|
| 修正異状 | 顧客の住所変更に伴い、 |
| 挿入異状 | 売上が発生していない段階では、 |
| 削除異状 | 特定の売上レコードを削除すると、 |
分割（正規化）
取引履歴（イベント情報）
「株式会社A」
正規化後（リレーショナル状態）
「イベント（売上の事実）
「株式会社A」
T_売上（トランザクション）
2種類のテーブル：マスタとトランザクション
データベース設計では、
| 格納データ | テーブルの種類 | テーブル名のプレフィックス（接頭語） | 例 |
|---|---|---|---|
| 属性 | マスタ（ディメンション） | M_（Master） | 商品リスト、 |
| イベント | トランザクション（ファクト） | T_（Transaction / Table） | 売上明細、 |
テーブルの種類の通称はさまざま
テーブルの呼び方は文脈によって異なります。
- テーブル設計での呼び方：「マスタ」
と「トランザクション」 - Power Pivot（データモデル）
での呼び方：「ディメンション」 と「ファクト」 - Excelでのプレフィックス（接頭語）
：M_（Master） と T_（Transaction / Table） - ※ T_ は "Transaction" ではなく "Table" を指し、
マスタテーブル以外のテーブルに付けることが一般的です。
- ※ T_ は "Transaction" ではなく "Table" を指し、
テーブル名の設定方法など、
正規化は、
ステップ1：「1セル1値」
正規化の最初は、
第1正規形の定義
1つのセルに1つの値のみを格納し、
以下のような入力は、
- 1つのセルにカンマやセル内改行で複数の値を記入している
- セルを結合して複数のセルに1つの値を表示している
- 「1月売上」
「2月売上」 「3月売上」 のように、 同一項目を複数列に並べている（クロス集計表）
ステップ2：マスタとトランザクションの分離（第2・第3正規化）
第2・第3正規化は、
| キー（決定項目） | キーに直接従属する属性 | 分離先テーブル |
|---|---|---|
| 商品ID | 商品名、 | M_商品 |
| 顧客ID | 顧客名、 | M_顧客 |
| 受注番号 | 受注日、 | T_売上 |
理論上は第4正規形・第5正規形なども存在しますが、
注意点：第2・第3正規化はあえて止める（スタースキーマの適用）
Excelでは正規化を進めすぎると、
スタースキーマの構造
スタースキーマとは、
| テーブルの形 | 特徴 | デメリット |
|---|---|---|
| スノーフレークスキーマ（第3正規形まで分解） | データの重複が一切なく、 | 使用する際に大量のテーブルを結合（JOIN） |
| スタースキーマ（正規化を一部緩めた構造） | テーブルの数が少なく、 | 同じデータが何度も重複して保存されるため、 |
業務システム向け（受発注・顧客管理など）
基本的には、
- マスタとトランザクションに分離する
- マスタからさらにマスタを分離するのは、
特定の条件に該当する場合のみ（後述） とする。
参考：第2・第3正規化とは
第2・第3正規化を完全に進めると、
第2・第3正規化を行った形（正規形）
第2正規形の定義
複合主キーの一部のキーにしか依存しない属性を、
※ 部分関数従属 とは、
第3正規形の定義
主キー以外の列に依存する属性を、
※ 推移的関数従属 とは、
実務ではエンティティを意識すると早い
第3正規化の理論（推移的関数従属の有無）
そもそも「カテゴリID」
第2階層以降のテーブルに切り出すときの判断基準
Excelデータベースでは原則としてスタースキーマを維持します。
- データ量が膨大で、
重複によるサイズ増大が問題になる場合：大量の行を持つトランザクションテーブルに、 選択肢の少ない属性（長い文字列など） が全行で重複しているケース。
- 粒度が異なるデータが混在する場合：トランザクションテーブルに「日単位の列」
と「月単位の列」 が混在しているケース（1対多の関係が崩れている場合） 。
TIP：重複が多い場合に見やすくする設定（セル結合の代替）
条件付き書式で「上の値と同じなら非表示」
【設定例】
- 数式 ： =B3=B2 （B3が開始セルの場合）
- 書式 ： フォントの色を薄い灰色
- 適用先 ： ``
ステップ3：単一主キー列の追加（複合キーの場合）
テーブルの主キーが 複合キー （行を一意に識別するときに複数列の値を使う構造）
単一主キーの作成方法としては、
- 連番を設定する（推奨）
- 複合キーの値を結合して生成する
マスタテーブルには、
主キーを設定するメリットを整理すると下記の通りです。
- 1行の抽出・参照が簡単になる
- 並び替え後に元の順序に戻せる
- 同一名称（同姓同名など）
でもレコードを区別できる
ステップ4：重要な記録はトランザクションテーブルに保持
重要な「取引時点の記録」
重要な記録項目としては、
- 金額
- 配達先住所
- ※ 取引後も、
納品対応・再発送・返品処理・問い合わせ対応などで参照される可能性がある
- ※ 取引後も、
テーブル間で発生する重複データの扱い方は、
- トランザクションテーブル：取引時点の記録（スナップショット）
を保持する - マスタテーブル：常に最新の正しい状態のみを保持する
RDBでは、
| 方法 | 用途 | 特徴 |
|---|---|---|
| XLOOKUP / VLOOKUP / INDEX+MATCH 関数 | 単一の値を参照する場合 | リアルタイムで更新される。 |
| Power Query マージ | 2つのテーブルを結合する場合 | 大量データの結合に向く。 |
| Power Pivot リレーションシップ | 複数テーブルを結合する場合 | 大量データを高速に処理できる。 |
Excelは表計算ソフトであり、
- 容量と速度の限界 ：データ量が増えると動作が極端に重くなり、
強制終了のリスクが高まる。 シートには104万行の上限があり、 ビッグデータの格納には向かない。 - 同時編集が困難 ： 複数人による同時更新には特化しておらず 、
データの消失や不整合の発生リスクがある。 - 参照整合性の欠陥 ：一方のテーブルを修正しても、
関連する他方のテーブルは自動更新されない。 矛盾が生じたままになるリスクがある。 - 人的ミスの発生 ：セル値の誤消去や、
不適切なデータ型の入力を防ぐ仕組みが弱い。 - セキュリティの脆弱性 ：ファイル全体の流出リスクが常に存在する。
詳細：» Excelデータベースの限界｜専用システムとの違いを比較【ビッグデータに対応】
以下の状況に当てはまる場合は、
- ファイルを開く操作や計算処理に数分以上かかる。
- 「誰かがファイルを開いていて更新できない」
という状況が頻繁に起きている。 - 参照数式が複雑化しており、
メンテナンスが困難になっている。 - データの不整合を修正する作業に、
膨大な時間を費やしている。
本記事では、
- テーブル分割のメリット ： データの重複による容量肥大化と、
修正・挿入・削除時のデータ不整合（アノマリー） を防ぐ。 - Excelテーブルの設計方針 ： 第1~3正規化によりテーブルをマスタとトランザクションに分離するが、
「スタースキーマ」 を採用してテーブル階層を深くしない。 - 第1正規化 ： 1セルに1値のみを格納し、
セルの結合や繰り返し列を完全に排除する。 - 第2・第3正規化 ： 主キーや他の項目に依存する属性（商品名や顧客住所など）
を別テーブルに分離する。
- 第1正規化 ： 1セルに1値のみを格納し、
Excelデータベースの注意点・限界や、