Excelデータベース: リレーショナルテーブルの作成

Excelは強力なデータベース機能を備えています。リレーショナルデータベースの作成は、マスターテーブルと、それを従属テーブル（（ややこしい言い方ですが）スレーブテーブル、子テーブル、または詳細テーブルと呼ばれる）にリンクすることから始まります。

始める前に、サンプルワークブックをご用意しました。このワークブックを使って、ハウツーに沿って各ステップを練習できます。下の矢印またはリンクをクリックしてダウンロードを開始してください。

ダウンロード

これは複数のスプレッドシートを含むワークブックで、その内容はリレーショナルデータベース、レポート、ピボットテーブルでのExcelタスクの練習に使用できます。JD Sartain

リレーショナルデータベースの仕組み

Excel の組織構造は、データベースの仕組みによく適合しています。データベースとは、関連付けられたアイテム（スプレッドシート）の集合であり、関連付けられると、複数のレコード（テーブル）のグループ内に単一のレコード（行）が作成されます。

スプレッドシート単体ではデータベースですが、リレーショナルデータベースではありません。リレーショナルデータベースは、マスタースプレッドシートテーブルと、そのすべてのスレーブテーブルまたはスプレッドシートを組み合わせたものです。

例えば、あなたの運転免許証は、米国運輸局（DMV）のコンピュータに保存されているフラットファイルデータベース内の単一のレコードです。免許証には、氏名、性別、身長、体重、目の色と髪の色、住所、生年月日、免許証の発行日と有効期限、免許証の等級などの情報が含まれています。

同じ名前、住所、性別、特徴を持つ人が複数存在する場合（例えば、父親と息子）があるため、運転免許証にはすべて免許証番号が付与されています。この固有の番号はキーフィールドと呼ばれ、フラットファイルデータベースを他の関連データベース（リレーショナルデータベース）に接続するために使用されます。

01 基本的なフラットファイルスプレッドシートデータベース — 基本的なフラットファイルスプレッドシートデータベース

この基礎となるマスターデータベース（この例）には、運転免許証に記載されているすべての情報が含まれています。スレーブデータベース、子データベース、または詳細データベースと呼ばれる1つ以上の関連データベースには、個々のドライバー（一意のキーフィールド「免許証番号」で識別される）に関連する追加情報が含まれています。例えば、あるスレーブデータベースには個々のドライバーのすべての運転違反情報が含まれ、別のスレーブデータベースには個々のドライバーの過去の住所がすべて含まれる場合があります。このタイプのリレーショナルデータベースは「1対多」の関係と呼ばれ、各ドライバーは複数の違反や複数の住所を持つことができますが、違反や住所のデータベースには複数のドライバーを含めることはできません。

その他の種類のリレーションシップには、1対1と多対多があります。顧客が製品を購入する販売データベースでは、一部の顧客が特別な割引率（卸売、小売、免税など）を受ける場合があり、そのためには割引率データベースが必要です。これは顧客データベースとの1対1リレーションシップです。1人の顧客が複数の製品を購入でき、同じ製品を複数の顧客が購入できる場合は、多対多リレーションシップになります。

02 リレーショナルデータベースのマスターとスレーブ — リレーショナルデータベースのマスターとスレーブ

リレーショナルデータベースが必要な人

リレーショナルデータベースを作成する理由はたくさんあります。最も明白な理由は、ワークブック内のすべてのスプレッドシートで同じデータが繰り返されないようにするためです。たとえば、マスターデータベースから複数のスレーブデータベースにすべてのドライバーの名前と生年月日を再入力するのは冗長で、時間がかかり、リソースを大量に消費します。また、一方から他方に情報をコピーしたとしても、不要なディスク領域とメモリが使用され、すべての速度が低下します。また、名前と生年月日は実際には一意ではありません。たとえば、Jack Taylor と John Smith は数百人いる可能性があり、その中に同じ生年月日を持つ人がいる可能性は十分にあります。ただし、ライセンス番号だけを使用すると、ドライバーを識別できないため、安全のために少なくとも 3 つのフィールドすべてを使用する必要があります。

リレーショナルデータベースの最も重要な目的は、特定の情報を抽出してレポートを印刷できるクエリとフィルターを作成することです。例えば、上司がフロリダ州サンタローザ郡で300ドルを超える交通違反金を受け取っている人全員、あるいはペンサコーラで12月に免許更新日を迎えるドライバーの数などを示すレポートを作成したいとします。リレーショナルデータベースの容量がなければ、3つ以上のスプレッドシートからこれらの情報を4つ目のスプレッドシートにコピー＆ペーストする必要があり、そして全てが終わった後に上司が「私はサンタローザ郡ではなくサラソタ郡と言いました」と言わないことを祈るしかありません。

03 フロリダ州で300ポンドを超える交通違反の報告 — リレーショナルデータベースレポート - 交通違反切符

リレーショナルデータベースを作成する

いくつかの個別のテーブルまたはスプレッドシートを含むリレーショナルデータベースを作成しましょう。

マスターテーブルの作成

1. 上記の最初のスクリーンショット（「基本的なフラットファイル形式のスプレッドシートデータベース」）に示されているのと同じフィールドを使用し、12～15件のレコードにご自身のデータ（または弊社のデータ）を入力します。入力するフィールドは、ライセンス番号、氏名、住所、市区町村、都道府県、郵便番号、生年月日、有効期限、性別です。

2. 完了したら、ヘッダーを含む表全体（A1からI15まで）を選択します。カーソルが表の範囲内にある限り、Ctrl+Aキーを押すと表全体が一度に選択されます。

3. タブとボタンを選択します:挿入>表

4. 範囲がハイライト表示されている場合（ハイライト表示されているはずです）、範囲のアドレスが「テーブルの作成」ダイアログボックスの「テーブルのデータはどこにありますか？」というフィールドに表示されます。 「テーブルにヘッダーがあります」というチェックボックスをオンにし、「OK」をクリックします。

5. ダイアログウィンドウが閉じたらすぐに、現在の画面の「プロパティ」グループの「テーブル名」フィールドボックス（左上隅）にテーブル名を入力してください。「MASTER」と入力し、Returnキーを押します。

6. テーブルの各フィールド名の横に下矢印が表示されています。下矢印の1つ（例：City）をクリックすると、ダイアログメニューボックスが表示されます。このボックスには、フィルターを含む並べ替えオプションがあり、選択したフィルターに基づいて特定の情報を表示できます。例えば、「すべて選択」フィールドボックスのチェックを外し、 「Miami」と「Destin」というフィールドボックスのみにチェックを入れると、ExcelはMiamiとDestin以外のすべての都市を除外します。

注意：残りのレコードがすべて消えても慌てないでください。完全に消えたわけではなく、単に非表示になっているだけです。再度表示するには、「City」フィールドの横にある下矢印をクリックし、「すべて」ボタンをクリックすると、すぐに表示されます。

ヒント: フィルタードロップダウンメニューの「テキストフィルター」オプションをクリックすると、ポップアップリストからフィルターオプションを選択できます（チェックボックスリストから一致するフィールドを選択するのではなく）。オプションには、「等しい」、「等しくない」、「で始まる」、「で終わる」、「含む」、「含まない」、「カスタムフィルター」などがあります。

7. スプレッドシートの下部にあるタブ（Sheet1）を「マスター」に変更することを忘れないでください。スプレッドシートのタブをダブルクリックすると、名前をすぐに変更できます。

05 フィルターを使用してテーブルから特定のデータを抽出する — フィルターを使用してスプレッドシートのテーブルから特定のデータを抽出します

スレーブテーブル

1. 次に、スレーブテーブルを作成します。ナビゲーションバーの「+」（プラス記号）を押して、2つのスプレッドシートを追加します。これらの違反の1つと、その他のアドレスに名前を付けます。

2. 列A（ライセンス番号）を他の2つのスプレッドシートの列Aにコピーします。列幅を11に調整します。

注:この例のスレーブテーブルは1対多の関係にあるため、ドライバーごとに複数のレコードが存在するため、キーフィールド（ライセンス番号）は複数回繰り返される可能性があります。このフィールドはスレーブテーブルにコピーされているため、データを再入力する必要はありません。複数のエントリがある場合は、各レコードの残りの部分にデータを入力する前に、ライセンス番号をコピーしてください。

3. 以下のスクリーンショット「リレーショナルデータベース：違反スレーブテーブル」に示されているのと同じフィールドを使用し、独自のデータ（または弊社のデータ）を入力します。これらのフィールドは、ライセンス番号（もちろんA列）、違反の種類、違反料金、違反日、有効期限、料金支払日です。

4. 同じナンバープレート番号を持つレコードを約30件入力します。例：ナンバープレート番号F298-6588は3回重複しています。これは、ロジャー・アンダーソン（このナンバープレート番号を持つドライバー）が3回違反を犯していることを意味します。

5. 完了したら、ヘッダー A1:H31 を含む表全体を強調表示します。

6.挿入 >表を選択

7. 「テーブルの作成」ダイアログボックスの「 テーブルのデータはどこにありますか？」というフィールドに範囲アドレスが表示されます。「テーブルにヘッダーがあります」というチェックボックスをオンにして、「OK」をクリックします。

8. ダイアログウィンドウが閉じたらすぐに、現在の画面の「プロパティ」グループの「テーブル名」フィールドボックス（左上隅）にテーブル名を入力してください。「VIOLATIONS」と入力し、Returnキーを押します。

9. いずれかの列/フィールドの横にある下矢印をクリックします。ダイアログメニューボックスが表示されます。並べ替えとフィルターを試して、この表から異なる結果を表示してみてください。