Excelで顧客、商品、注文などのデータを整理すると、どの表をどの項目でつなぐべきか迷うことがあります。
このような場面で役立つのが、エンティティとリレーションシップを視覚的に表すE-R図です。
Excelには専門的なデータベース設計ツールほど多くの機能はありませんが、図形、コネクタ、セルの書式設定を組み合わせれば、共有しやすいE-R図を作成できます。
ExcelでE-R図を作る基本は、テーブルを四角形で表し、主キーと外部キーを明記し、コネクタで関係を結ぶことです。
設計の段階で表の役割を分けておくと、データの重複や更新漏れを減らしやすくなります。
この記事では、ExcelでE-R図を作成する流れ、テーブルの設計、リレーションの表現、確認方法を順に解説します。
図形を使った見やすい資料として仕上げれば、システム開発者だけでなく、業務担当者との認識合わせにも活用できるでしょう。
ExcelでE-R図を作成する手順
| A | B | C |
|---|---|---|
| 顧客 | 注文 | 商品 |
| 顧客ID | 注文ID | 商品ID |
| 顧客名 | 顧客ID | 商品名 |
それではまず、Excelの図形機能を使ってE-R図を作成する基本手順について解説していきます。
最初から細部を決め込まず、必要なテーブルを並べて関係を見える化することが作成の出発点です。
作成順序は、対象業務の洗い出し、エンティティの決定、属性の記入、主キーと外部キーの確認、リレーションの接続という流れです。
新しいシートと作図範囲
まず、ブックに新しいワークシートを追加し、シート名を「ER図」や「データベース設計」に変更します。
シートの表示倍率は80パーセントから100パーセント程度にすると、図形と文字の配置を調整しやすくなります。
列幅をあらかじめ少し広げ、行の高さをそろえておくと、下書き用のグリッドとして利用できます。
［表示］タブから目盛線の表示を確認しておくと、テーブルの位置をきれいに整えやすいでしょう。
業務の流れが左から右へ進む場合は、顧客、注文、注文明細、商品というように関連する表を横方向に置く配置が分かりやすいです。
一方で、マスターテーブルと取引テーブルを分けたい場合は、上側にマスター、下側に明細や履歴を置く構成も有効です。
四角形によるテーブルの作成
［挿入］タブの［図形］から四角形を選び、シート上へドラッグして最初のテーブル枠を作成します。
四角形の内部には、テーブル名、主キー、通常の項目、外部キーの順で入力すると、読む人が構造を把握しやすくなります。
たとえば顧客テーブルなら、顧客、顧客ID、顧客名、電話番号、メールアドレスというように項目を並べます。
テーブル名だけは太字にし、塗りつぶしを濃い緑、文字色を白にすると、表の見出しとして区別できます。
項目欄は白または淡い色にし、罫線を残すと、Excelの通常の表に近い見た目になります。
主キーとなる列にはPK、外部キーとなる列にはFKを付け、名称だけでは分からない役割を明示しましょう。
【操作のポイント】図形内の文字が収まらないときは、文字サイズを極端に小さくする前に、図形の横幅と高さを広げます。
コネクタによるテーブル間の接続
テーブルを配置したら、［挿入］タブの［図形］にあるコネクタを使い、関連するテーブル同士を結びます。
単純な線ではなくコネクタを選ぶと、図形を移動した後も接続関係を保ちやすいため便利です。
顧客テーブルの顧客IDと注文テーブルの顧客IDを結ぶ場合は、線の近くに1と多を表す文字を配置します。
顧客1人に対して注文が複数存在するなら、顧客側に1、注文側に多を配置するイメージです。
Excelには本格的なカラス足記法の自動機能はないため、テキストボックスや小さな図形で補う方法が実務的です。
線が交差しすぎると関係が読み取れなくなるため、テーブルの位置を先に調整してから接続します。
【操作のポイント】コネクタの端点が図形の接続ポイントに吸着した状態で配置すると、移動後も線が外れにくくなります。
E-R図に必要なエンティティと属性
| テーブル名 | 主な属性 | 役割 |
|---|---|---|
| 顧客 | 顧客ID、顧客名 | 購入者の管理 |
| 注文 | 注文ID、顧客ID、注文日 | 取引の管理 |
| 商品 | 商品ID、商品名、単価 | 販売品の管理 |
続いては、E-R図に記載するエンティティと属性の考え方を確認していきます。
エンティティは管理対象となる物事であり、データベースでは通常テーブルとして実装されます。
顧客、商品、社員、部署、注文、予約、在庫など、業務上で繰り返し記録する対象がエンティティの候補です。
業務名詞からのエンティティ抽出
エンティティを探すときは、業務の説明文に出てくる名詞へ注目します。
たとえば「顧客が商品を注文する」という業務であれば、顧客、商品、注文が候補になります。
ただし、すべての名詞をテーブルにする必要はありません。
注文日や注文金額のように、注文に付随する情報は注文テーブルの属性として持たせるほうが自然な場合があります。
単独で管理、検索、更新する必要があるかを基準にすると、エンティティと属性を分けやすくなります。
住所を都道府県、市区町村、番地へ分割するかどうかも、検索や集計の目的に応じて決めましょう。
【操作のポイント】候補を思い付くままセルに書き出し、独立して管理する対象だけを図形へ移すと、設計漏れを防げます。
主キーと一意性
主キーは、テーブル内の1件のレコードを重複なく識別する項目です。
顧客名は同姓同名があり得るため、主キーとしては不向きであり、顧客IDのような番号を別に設けるのが一般的です。
注文についても、注文番号や注文IDを主キーにすると、注文日や顧客名が重なっても個別の取引を区別できます。
主キーは空欄を許可せず、同じ値を複数行に登録しないというルールで運用します。
E-R図の主キーを明確にすることは、後からExcelのデータをAccessやSQLデータベースへ移す際にも重要です。
主キーの例は、顧客ID、商品ID、注文ID、社員IDです。
表示用の名称と識別用のIDを分ける設計が、安定したデータ管理につながります。
【操作のポイント】図形の項目名の先頭にPKを付け、主キー行だけ太字または淡い黄色で表示すると、共有時の見落としを減らせます。
属性の粒度とデータ型
属性はエンティティの性質を表す項目です。
商品テーブルなら商品名、単価、分類、販売状態などが属性に該当します。
設計書としてE-R図を使う場合は、項目名だけでなく、文字列、整数、日付、真偽値といったデータ型も併記すると実装の認識差を減らせます。
Excelで入力例を作る場合、1行目をヘッダーとし、2行目以降にデータを入れる構造を想定します。
たとえば商品IDをA列、商品名をB列、単価をC列へ置く場合、C2には数値を入力します。
消費税10パーセント込みの参考価格をD列へ出すなら、D2には =C2*1.1 という数式を入力し、下方向へオートフィルします。
この数式では、C2の単価に1.1を掛けて税込価格を求めます。
数式をコピーする前提なら、1行目は必ず見出し行にし、実データは2行目から開始します。
【操作のポイント】属性名には「日付」「区分」「金額」など意味が分かる言葉を使い、値の形式を名称から推測できるようにします。
リレーションシップとカーディナリティ
| 親テーブル | 子テーブル | 関係 |
|---|---|---|
| 顧客 | 注文 | 一対多 |
| 注文 | 注文明細 | 一対多 |
| 商品 | 注文明細 | 一対多 |
続いては、テーブル同士のつながりを示すリレーションシップとカーディナリティについて確認していきます。
リレーションシップは、別々のテーブルに分けたデータをIDで結び、必要な情報を関連付けるための仕組みです。
一対一は1件と1件の対応です。
一対多は1件の親データに複数の子データが対応する関係です。
多対多は中間テーブルを利用して表現します。
一対多の関連付け
顧客と注文は、一対多の代表例です。
1人の顧客は複数回注文できますが、通常は1件の注文が複数の顧客に属することはありません。
この場合、顧客テーブルの顧客IDを主キーとし、注文テーブルに同じ顧客IDを外部キーとして持たせます。
注文テーブルの顧客IDは、親テーブルの顧客IDを参照するための項目です。
親側の主キーを子側へコピーして関係を作る考え方が、外部キーの基本です。
【操作のポイント】親テーブルを左または上、子テーブルを右または下に置くと、データが流れる向きを読み取りやすくなります。
注文明細による多対多の分解
注文と商品だけを見ると、1件の注文に複数の商品が含まれ、1つの商品も複数の注文に含まれるため、多対多の関係になります。
多対多のままでは、数量や販売時点の単価をどこへ保存するかが曖昧になりがちです。
そこで注文IDと商品IDを持つ注文明細テーブルを作り、注文と注文明細、商品と注文明細をそれぞれ一対多で結びます。
注文明細には、数量、単価、小計といった取引固有の属性を持たせます。
小計をExcelで確認するなら、単価がC列、数量がD列のサンプルで、E2に =C2*D2 と入力します。
先頭のE2だけを作成し、セル右下のフィルハンドルを下へドラッグしてオートフィルすれば、各明細行の小計を計算できます。
商品マスターの現在単価と、注文時点の明細単価を分けて保存する設計は、価格改定後の履歴を守るうえで有効です。
【操作のポイント】多対多を見付けたら、両方のIDを持つ中間テーブルが必要ではないか確認します。
任意関係と必須関係
リレーションを設計するときは、件数だけでなく、関連データが必ず存在するかも考えます。
たとえば、登録直後の顧客には注文が1件もないことがあるため、顧客から注文への関係は注文が任意です。
一方で、通常の注文データには注文者が必要なので、注文から顧客への関係は必須として扱うことが多いです。
この違いをE-R図にメモしておくと、後から入力規則やNOT NULL制約を決める際に役立ちます。
削除時の扱いも設計対象です。
顧客を削除したときに注文履歴も削除するのか、削除を禁止するのか、別の状態で保持するのかを業務ルールに沿って決めましょう。
【操作のポイント】線の近くに「0件可」「必須」などの補足を置くと、図を見るだけで入力条件を共有できます。
Excel図形による見やすいレイアウト
| 配置 | 内容 | 効果 |
|---|---|---|
| 左側 | 基幹マスター | 参照元が明確 |
| 中央 | 取引テーブル | 業務の流れを表現 |
| 右側 | 明細・履歴 | 関係を追いやすい |
続いては、作成したE-R図を見やすく整えるためのExcel図形の使い方を確認していきます。
図が正しくても、線が重なったり文字が詰まったりすると、設計内容は共有されにくくなります。
整列と均等配置
複数の図形を選択し、［図形の書式］タブの［配置］を使うと、左右や上下をそろえられます。
テーブルの上端をそろえ、間隔を均等にすると、関係線の経路も整理しやすくなります。
ドラッグだけで位置を合わせるより、整列機能を使うほうが、修正後も図全体の品質を維持できます。
【操作のポイント】大きさを統一したいテーブルは、最初の図形をコピーして項目名だけを書き換えると効率的です。
色分けと凡例
顧客や商品などのマスターテーブル、注文や入出庫などのトランザクションテーブル、中間テーブルを色分けすると役割が伝わりやすくなります。
ただし、多くの色を使いすぎると意味を覚えにくくなるため、3色程度に絞るのが無難です。
シートの端には、PKは主キー、FKは外部キー、実線は必須関係などを説明する凡例を置きましょう。
淡い青はマスター、淡い緑は取引、淡い黄は中間テーブルというように、色の役割を固定すると資料全体で統一感が出ます。
【操作のポイント】印刷や白黒コピーも想定し、色だけではなくPKやFKの文字でも情報を識別できるようにします。
グループ化と保護
関連する図形とテキストボックスは、まとめて選択してグループ化すると移動が簡単になります。
完成後に意図せず図形がずれることを防ぎたい場合は、シートの保護も検討します。
共有用ファイルでは、編集するセルだけを分け、E-R図のシートは閲覧専用に近い状態へ整える方法もあります。
設計図は更新履歴を残すことも大切です。
ファイル名に作成日や版数を含め、変更したテーブルや項目を別シートに記録すると、古い設計との混同を防げます。
【操作のポイント】図形をグループ化する前に、コネクタの接続先が正しいか確認しておくと、後工程の修正を減らせます。
データベース設計で確認する正規化
| 確認項目 | 確認内容 |
|---|---|
| 重複 | 同じ情報を複数の表へ持たせていないか |
| 繰り返し | 商品名1、商品名2のような列がないか |
| 依存関係 | 主キー以外で決まる項目が混在していないか |
続いては、E-R図をデータベース設計として使う際に確認したい正規化について解説していきます。
正規化とは、データの重複や矛盾が起こりにくいようにテーブルを整理する考え方です。
繰り返し項目の分離
注文テーブルに商品名1、商品名2、商品名3のような列がある場合、商品の数が増えたときに対応できません。
このような繰り返しは、注文明細テーブルを別に作り、1商品を1行で管理する構造へ分けます。
列を横方向に増やすのではなく、明細を縦方向のレコードとして追加できる形が、データベースに適しています。
【操作のポイント】同じ意味の列名に連番が付いている場合は、別テーブルへ分ける必要がないか確認します。
マスター情報の重複防止
注文テーブルへ顧客名、住所、電話番号を毎回保存すると、顧客情報が変更された際に複数行を修正しなければなりません。
顧客情報は顧客テーブルにまとめ、注文テーブルには顧客IDだけを持たせると更新作業を減らせます。
商品名や標準価格についても同様に、商品テーブルで管理する構成が基本です。
ただし、注文時点の配送先や販売単価のように履歴として残すべき値は、注文側や明細側に保存する場合があります。
正規化は機械的に分割する作業ではなく、業務上の履歴を守る判断でもあります。
【操作のポイント】変更される可能性がある情報と、取引時点で固定して残す情報を分けて考えます。
入力規則と参照整合性
E-R図で親子関係を決めたら、子テーブルに存在しない親IDを入力しない仕組みも考えます。
Excelの試作表では、［データ］タブの［データの入力規則］を使い、顧客IDや商品IDをリストから選択できるようにする方法があります。
本格的なデータベースでは、外部キー制約によって参照整合性を保ちます。
E-R図の線は単なる飾りではなく、入力できるデータの条件を示す設計情報です。
【操作のポイント】テスト用データを数件入力し、存在しないIDや重複IDが混ざらないか確認すると設計の弱点を見つけやすくなります。
まとめ エクセルでE-R図を作成する方法（データベース設計）
ExcelでE-R図を作成する際は、最初に業務で管理する対象を洗い出し、エンティティと属性を整理します。
各テーブルには主キーを設定し、親テーブルの主キーを子テーブルの外部キーとして持たせることで、データの関係を表現できます。
顧客と注文のような一対多の関係、注文と商品のような多対多の関係を理解すると、必要な中間テーブルも判断しやすくなります。
Excelの四角形、コネクタ、整列機能を使えば、専門ツールがなくても共有しやすいデータベース設計図を作成できます。
作図後は、同じ情報を複数のテーブルに持たせていないか、繰り返し列が残っていないか、外部キーの参照先が明確かを確認しましょう。
見やすく整えたE-R図を基にすれば、Excelでのデータ管理だけでなく、Access、SQL Server、MySQLなどへ移行する際の土台にもなります。
まずは身近な顧客管理や注文管理を題材に、小さなE-R図から作成してみましょう。
