【Excel】エクセルでE-R図を作成する方法（データベースの関係を整理する）

Excelで顧客、注文、商品などのデータを管理していると、どの表がどの情報とつながるのか分かりにくくなることがあります。

このようなときに役立つのが、データベースの構造を視覚的に表現するE-R図です。

E-R図は専門的な作図ソフトがなくても、Excelの図形、コネクタ、セルの罫線を活用すれば作成できます。

最初にエンティティ、主キー、関連の3要素を整理してから図形を配置することが、見やすいE-R図を作る近道です。

この記事では、ExcelでE-R図を作成し、データベースの関係を整理する方法を詳しく解説していきます。

 

ExcelでE-R図を作成する手順

それではまず、Excelの図形機能を使ってE-R図を作成する基本手順について解説していきます。

顧客テーブル	注文テーブル	商品テーブル
顧客ID	注文ID	商品ID
顧客名	顧客ID	商品名
電話番号	商品ID	単価

 

作図前にデータ表を洗い出す作業

Excelで図を描き始める前に、管理対象となるデータを表単位で洗い出しましょう。

たとえば販売管理では、顧客情報、商品情報、注文情報、注文明細といった表が候補になります。

顧客名、住所、電話番号を顧客テーブルにまとめ、商品名や単価を商品テーブルにまとめるように、同じ種類の情報を集約します。

1つの表に異なる種類の情報を詰め込みすぎないことが、データの重複や更新漏れを防ぐ基本です。

注文情報には注文日や顧客IDを置き、注文した商品が複数になる場合には注文明細を別表にする考え方が適しています。

紙やExcelの別シートに候補を書き出すと、必要なエンティティを見落としにくくなります。

 

挿入タブから図形を配置する操作

表の候補を決めたら、Excelの挿入タブを開き、図形から角丸四角形または四角形を選択します。

ワークシート上でドラッグし、顧客、注文、商品などのエンティティごとに図形を1つずつ配置します。

図形の中をクリックすれば文字を入力できるため、先頭行にはテーブル名、その下には列名を入力しましょう。

テーブル名を太字にし、主キーとなる項目にはPK、外部キーとなる項目にはFKを付けると、読み手が構造を判断しやすくなります。

図形の大きさと見出しの位置をそろえることで、簡易的な図でも業務資料として読みやすい仕上がりになります。

図形の書式タブでは、塗りつぶしを薄い色にし、枠線を濃い色にすると文字とのコントラストを確保できます。

 

コネクタでテーブル間を結ぶ方法

図形の配置が終わったら、挿入タブから図形を選び、線にあるコネクタを使用します。

通常の直線ではなくコネクタを選ぶと、図形を移動した際にも接続線が追従するため、後からレイアウトを整えやすくなります。

顧客テーブルの顧客IDと注文テーブルの顧客IDのように、同じ値で結び付く列を意識して線を引きます。

線の近くに一対多を表す1とNを入力しておくと、関係の意味がさらに明確です。

顧客1人は複数の注文を持てるため、顧客側を1、注文側をNと表現できます。

 

エンティティと属性の整理

続いては、E-R図の土台となるエンティティと属性の整理について確認していきます。

分類	例	役割
エンティティ	顧客	管理対象そのもの
属性	顧客名	対象の詳細情報
識別子	顧客ID	1件を特定する値

 

エンティティを名詞で見つける考え方

E-R図のエンティティは、業務の中で繰り返し登場する対象から見つけます。

たとえば予約管理であれば、利用者、施設、予約、支払などが代表的な候補です。

担当者や部門のように、将来的に検索、集計、履歴管理を行う対象も独立したエンティティになる可能性があります。

一方で、住所やメールアドレスは顧客に付随する詳細情報なので、通常は顧客テーブルの属性として扱います。

独立して複数件を管理する必要があるかを基準にすると、表を分ける判断がしやすくなります。

たとえば顧客が複数の配送先を登録できる場合、配送先は顧客の単なる属性ではなく、独立した配送先テーブルとして設計する余地があります。

 

属性名を分かりやすく決める方法

属性名は、誰が見ても用途を推測できる名前にそろえます。

顧客の番号を表す列なら顧客ID、注文を受けた日なら注文日、商品の販売価格なら販売単価のように具体的な名前が有効です。

同じ意味の列に対して、ある表では番号、別の表ではコードというように表現を混在させると、関連を確認しにくくなります。

顧客IDと顧客コードのどちらを使うかを先に決め、関連するテーブルでも同じ命名に統一しましょう。

 

不要な重複を見つける視点

Excelの一覧表には、同じ顧客名や商品名が何度も入力されていることがあります。

注文ごとに顧客の住所や電話番号を保存すると、住所変更があったときに複数行を修正しなければなりません。

この状態では修正漏れが起こりやすく、同じ顧客なのに異なる住所が残る問題につながります。

そこで顧客情報は顧客テーブルに1回だけ保存し、注文テーブルには顧客IDを保存して関連付けます。

文字情報を繰り返して保存する代わりにIDで参照する構造が、データベースを整理する重要な考え方です。

 

主キーと外部キーの設定

続いては、テーブル同士の関係を正確に示す主キーと外部キーについて確認していきます。

テーブル	主キー	外部キー
顧客	顧客ID	なし
注文	注文ID	顧客ID
注文明細	明細ID	注文ID、商品ID

 

主キーでレコードを一意に識別する仕組み

主キーは、テーブルの各行を重複なく識別するための列です。

顧客テーブルなら顧客ID、商品テーブルなら商品ID、注文テーブルなら注文IDを主キーにできます。

顧客名は同姓同名になる可能性があり、メールアドレスも変更される場合があるため、主キーとしては安定しないことがあります。

連番や管理用コードを主キーにすると、名前や住所が変わっても同じデータとして追跡できます。

主キーには空白や重複がない値を選ぶことが基本です。

Excelで管理段階のIDは、先頭のゼロを保持したい場合もあるため、セルの表示形式を文字列にするか、入力ルールを決めておくと安心です。

 

外部キーで別テーブルを参照する構造

外部キーは、別のテーブルの主キーを保存し、データ同士を結び付ける列です。

注文テーブルの顧客IDは、顧客テーブルの顧客IDを参照する外部キーになります。

この関係により、注文テーブルには顧客名や住所を何度も入力しなくても、どの顧客の注文かを判断できます。

商品を複数購入する注文では、注文明細テーブルに注文IDと商品IDを持たせる構造がよく使われます。

外部キーはExcel上で自動検証されるものではありませんが、E-R図で明示しておくと、後でAccess、SQL Server、MySQLなどに移行するときにも役立ちます。

 

複合キーと明細テーブルの扱い

注文明細のように、注文IDと商品IDの組み合わせで行を区別したいケースがあります。

このような複数列を組み合わせた識別子を複合キーと呼びます。

ただし同じ商品を同じ注文で複数行に分ける可能性がある場合、注文IDと商品IDだけでは一意になりません。

その場合は明細IDという連番を主キーに追加し、注文IDと商品IDを外部キーとして持たせる設計が分かりやすいでしょう。

将来の入力パターンを想定して主キーを決めることが、後から表を作り直す手間の軽減につながります。

 

リレーションシップの描画

続いては、Excelの図形とコネクタを使ってリレーションシップを見やすく描画する方法を確認していきます。

関係	意味	例
一対一	双方が1件ずつ対応	社員と社員詳細
一対多	片方が複数件に対応	顧客と注文
多対多	双方が複数件に対応	注文と商品

 

一対多の関係を線と記号で表す方法

顧客と注文の関係は、実務で頻出する一対多のリレーションシップです。

1人の顧客が複数回注文できる一方で、1件の注文は通常1人の顧客に属します。

Excelでは顧客テーブルと注文テーブルの間にコネクタを引き、顧客側に1、注文側にNを配置すると関係を表せます。

記号の形式は必ずしも厳密な表記法に統一しなくても構いませんが、図全体で同じルールを使うことが重要です。

一対多の線は、主キーを持つ親テーブルから外部キーを持つ子テーブルへ向かう関係として読むと理解しやすくなります。

 

Excel上で配置を整える画面操作

Excelでは表示タブの目盛線を活用すると、図形の位置をそろえやすくなります。

複数の図形を選択し、図形の書式タブにある配置から左揃え、上下中央揃え、左右に整列などを選びましょう。

図形の間隔を均等にし、テーブル名が重ならないように余白を確保すると、印刷したときにも読みやすくなります。

関連線が交差しそうな場合は、親テーブルを左または上、子テーブルを右または下に配置する規則を作ると整理しやすいでしょう。

 

多対多を中間テーブルに置き換える考え方

注文と商品は、1件の注文に複数の商品が含まれ、1つの商品も複数の注文に含まれるため、多対多の関係です。

データベースでは多対多を直接保存するのではなく、注文明細のような中間テーブルを置いて二つの一対多に分解します。

注文明細には注文ID、商品ID、数量、販売単価などを保存できます。

これにより、いつ、どの商品を、何個、いくらで販売したかを1行ずつ記録できます。

多対多の関係を見つけたら中間テーブルを追加するという視点は、E-R図を実用的な設計図へ近づけるポイントです。

 

Excelで管理表とE-R図を連携させる方法

続いては、作成したE-R図をExcelの管理表や数式の設計へ活用する方法を確認していきます。

A列	B列	C列
商品ID	商品名	単価
P001	ノート	120
P002	ペン	80

 

テーブルごとにシートを分ける構成

E-R図で分けたエンティティは、Excelでも原則として別シートまたは別テーブルに分けて管理します。

顧客、商品、注文、注文明細をそれぞれ独立したシートにすると、列の役割が明確になります。

各シートの1行目にはヘッダーを入力し、2行目以降に実際のデータを入力する構成にしましょう。

Excelのテーブル機能を使う場合は、見出しを含む範囲を選択してCtrlキーとTキーを押すと、フィルターや自動拡張を利用できます。

E-R図とシート名を一致させることで、図を見た人が実際のデータの保存場所をすぐに確認できます。

 

外部キーから名称を参照する数式

注文表に商品IDだけを入力し、商品名や単価を商品マスタから取得する場合は、XLOOKUP関数が便利です。

注文シートのA列に商品ID、B列に商品名、C列に単価を表示する場合を考えます。

この数式は、注文シートのA2に入力された商品IDを商品シートのA2からA100で検索し、対応するB列の商品名を返します。

検索値はA2、検索範囲は商品IDの列、戻り範囲は商品名の列という順番です。

最後の空の文字列は、該当する商品IDがない場合にエラー表示を出さず空白にする指定です。

単価を取得する場合は、戻り範囲を商品シートのC2からC100へ変更します。

先頭セルで数式を入力した後、セル右下のフィルハンドルを下へドラッグすれば、3行目以降にも数式をオートフィルできます。

検索範囲の行番号には絶対参照のドル記号を付けると、オートフィル時に検索範囲がずれません。

 

入力規則でIDの誤入力を防ぐ方法

外部キーの入力ミスを減らすには、データの入力規則でリストを作成する方法が有効です。

注文表の商品IDを入力するセル範囲を選択し、データタブのデータの入力規則からリストを選択します。

元の値には商品マスタのID列を指定し、登録済みの商品IDだけを選べるようにします。

これにより、存在しない商品IDやスペルの違うIDが注文表に入力されるリスクを下げられます。

Excelだけで簡易データベースを運用する場合でも、E-R図に沿って入力規則を設計するとデータ品質が安定します。

 

E-R図を見やすく保つレイアウト

続いては、関係が増えても読みやすいE-R図を保つレイアウトについて確認していきます。

配置の要素	整え方
親テーブル	左側または上側に配置
子テーブル	右側または下側に配置
中間テーブル	関係する2表の間に配置

 

親テーブルと子テーブルの位置関係

図の左から右へ、または上から下へデータの流れをそろえると、初めて見る人でも関係を追いやすくなります。

顧客や商品などの基礎マスタは左側に置き、注文や明細などの取引データは右側に置く配置が分かりやすいでしょう。

親テーブルがどれかを明確にできるため、外部キーの参照先を探す時間も短縮できます。

図の中で同じ種類のテーブルを近い色にする方法もありますが、色だけに依存せず、テーブル名とPK、FKの表示を残すことが大切です。

線の交差を減らすことは、E-R図を読みやすくする最も効果的な改善です。

 

項目数が多い場合の分割方法

1つのテーブルに項目が多く、図形が縦に長くなった場合は、まず本当に必要な列だけを表示する方法があります。

設計レビュー用のE-R図では、主キー、外部キー、主要な属性だけを記載しても関係性を確認できます。

すべての列を確認する詳細版が必要なら、概要版とは別のシートに作成するとよいでしょう。

業務領域が広い場合には、販売管理、在庫管理、会計管理のようにテーマ別にE-R図を分割する選択肢もあります。

 

印刷と共有を意識した仕上げ方

Excelで作成したE-R図は、A3用紙やPDFで共有する場面もあります。

ページレイアウトタブで印刷の向きを横にし、拡大縮小を1ページに収める設定にすると全体を確認しやすくなります。

ただし縮小しすぎると項目名が読めなくなるため、図を分割する判断も必要です。

図形と線をまとめて選択し、グループ化しておくと、レイアウトが崩れにくくなります。

更新日、作成者、対象システム名を図の余白に記載することで、共有後の版管理もしやすくなります。

 

まとめ Excelでデータベースの関係を整理するE-R図の作成方法

ExcelでE-R図を作成するには、最初に顧客、商品、注文などのエンティティを洗い出し、それぞれの属性を整理します。

各テーブルには重複しない主キーを設定し、関連するテーブルには外部キーを配置することで、データベースの関係が明確になります。

顧客と注文のような一対多の関係は、Excelの図形とコネクタ、1とNの記号で表現できます。

注文と商品のような多対多の関係では、注文明細のような中間テーブルを追加すると、実際のデータ構造に近いE-R図になります。

E-R図は単なる説明資料ではなく、Excel管理表のシート構成、ID列、入力規則、検索数式を決めるための設計図として活用できます。

図形の位置、線の交差、テーブル名の表記をそろえ、更新しやすいレイアウトに整えましょう。

Excelで管理しているデータが複雑になってきたと感じたら、まずは主要な表だけで簡単なE-R図を作るところから始めてみてください。