Excel 2013でリレーショナルデータベースを作成する方法

Excelはかつて、ただ置かれたままのスプレッドシートが並ぶ、貧乏人のデータベースのようなものでした。LOOKUP関数を使えばもっと高度なものを作ることはできましたが、設定が非常に面倒でした。

もうそんな必要はありません。Excel 2013のテーブルツールには、グラフとセルのリンク、検索、そして動的に更新されるレポートの作成を簡単にする機能が搭載されています。まさにリレーショナルデータベースのように。Excelはこのようにして、日常的に扱う多くのオフィスデータを処理できます。その設定方法をご紹介します。

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

リレーショナルデータベース（格納されている情報間の関係性を認識するように構造化されたデータベース）は、大量のビジネスデータを扱う上で不可欠です。特定の情報を素早く検索・取得し、同じデータセットを複数の方法で表示し、データエラーや冗長性を削減できます。スプレッドシートでこれを試してみましょう。

Excelを使うとどれだけ簡単になるかをお見せするために、マスターテーブルと詳細テーブルという2つのテーブルを作成します。マスターテーブルは主要なテーブルで、通常は一意のレコード（名前、住所、市区町村、都道府県など）が含まれます。このテーブルは、個人の追加や削除などを除き、ほとんど変更されません。

マスターテーブルの各レコードに対して、マスターテーブルにリンクする詳細テーブル（スレーブテーブルまたは子テーブルとも呼ばれます）に複数のレコードが存在する場合があります。これは1対多の関係と呼ばれます。詳細テーブルのデータ（日次売上、製品価格、数量など）は、通常、常に変化します。

詳細テーブルでマスター情報が重複しないようにするには、「売上ID」などの一意のフィールドを1つだけ使用してリレーションシップを作成し、残りの処理はExcelに任せます。例えば、10人の営業担当者がいて、全員が固有の人口統計情報（マスターテーブル）を持っているとします。各営業担当者は200種類の商品を販売しています（詳細テーブルまたは子テーブル）。毎年末には、担当者別の年間売上高合計を示すレポートだけでなく、都市別の売上高合計を示すレポートも必要です。

このチュートリアルでは、営業担当者の情報を含むマスターテーブルと、年間の四半期ごとの売上合計を示す2つ目のテーブルを作成します。売上IDは、これらのテーブルを結び付けるリレーショナルフィールドです。次に、売上高が最も高かった都市を示すレポート（またはピボットテーブル）を作成します。

Excel を開き、新しい空白のワークシートを選択します。

マスターテーブルを作成する

1. まず、画面下部のタブ (緑色のバー線の上) をダブルクリックし、Masterタグライン Sheet1 に入力します。

2.セル A1 に次のように入力します: 。セルMasterA3 から F3 に次の列ヘッダーを入力します: Sales ID、、、、、、Sales Person。AddressCityStateZip Code

3. セルA4からA13に販売ID番号（この場合101はから）を入力します110。販売IDは、2つのテーブル間のリレーションシップを作成するために使用される一意のデータ値です。

4. 残りのセルに、氏名、住所、市区町村、州、郵便番号を入力します。このサンプルワークシートの情報をコピーするか、独自のデータを作成することもできます。都市別の売上高上位を調べるため、表には複数の都市を入力してください。例えば、ロサンゼルスに3人、ハリウッドに2人、サンフランシスコに2人、サンディエゴに3人の営業担当者がいるとします。

5. データを入力し終わったら、列見出しを含むA3からF13までをハイライト表示します。「スタイル」グループから「テーブルとして書式設定」を選択します。ドロップダウンから、希望する色と書式を選択します。「テーブルとして書式設定」ダイアログボックスが表示され、白いボックスにテーブル範囲が表示されます。「テーブルに見出しを付ける」チェックボックスがオンになっていることを確認し、「OK」をクリックします。

6. 表がハイライト表示された状態で、「表ツール」というテキストの下にある「デザイン」タブを選択します（このオプションは表がハイライト表示されている場合にのみ使用できます）。「プロパティ」グループ（左端）の「テーブル名」のボックスに「」と入力しますMaster。

詳細テーブルを作成する

1. 画面下部の「マスター」タブの横にある「+」記号をクリックして、新しいシートを挿入します。タブをダブルクリックし、Salesタグライン「Sheet2」を上書きします。

2. セルA1にと入力しますTotal Sales for 2013。セルA3からE3にSales ID、Quarter1、Quarter2、Quarter3と入力しますQuarter4。

3. セル A4 から A13 に販売 ID 番号を入力し101ます110。

4. B4 から E13 に、売上金額を表す 40 個のランダムな数字を入力するか、このサンプルテーブルからデータをコピーします。

5. データを入力し終わったら、セルA3からE13までを強調表示します。「スタイル」グループから「テーブルとして書式設定」を選択します。ドロップダウンから、希望する色と書式を選択します。「テーブルとして書式設定」ダイアログボックスが表示され、白いボックスにテーブル範囲が表示されます。「テーブルに見出しを付ける」チェックボックスがオンになっていることを確認し、「OK」をクリックします。

f3 create detail table — 詳細 (Sales) テーブルを作成します。

6. 表がハイライト表示された状態で、「表ツール」というテキストの下にある「デザイン」タブを選択します（このオプションは表がハイライト表示されている場合にのみ使用できます）。「プロパティ」グループの「テーブル名」のボックスに「」と入力しますSales。

f4 name sales table — 詳細 (Sales) テーブルを強調表示して名前を付けます。

ピボットテーブルレポートセクションで関係を設定する

ピボットテーブルの最初のルール：テーブル間の関係は、ピボットテーブルレポートセクションで定義する必要があります。Excelはピボットテーブルレポートセクションから関係接続を認識しないため、最初に関係接続を作成しようとしないでください。また、「データ分析」テーブルでは詳細テーブル（売上）を選択してください。そうしないと機能しません。

1. Salesテーブルに移動し、セルA1からE11までを選択します。「挿入」タブをクリックし、「ピボットテーブル」ボタンをクリックします。

2. [ピボットテーブルの作成] ダイアログボックスで、[テーブルまたは範囲の選択] > [テーブルの範囲] フィールドに「Sales」と表示されていることを確認します。Word や Access などの別のプログラムからテーブル/データベースをインポートする場合は、2 番目のオプション [外部データソースの使用]をクリックします。

3. 2 番目のフィールド「ピボットレポートを配置する場所を選択してください」で、テーブルを別のシートに配置する場合は [新しいワークシート]をクリックし、レポートを Sales テーブルの横に配置する場合は [既存のワークシート]をクリックします。

4. 最後のフィールド（複数のテーブルを分析するかどうかを選択する）では、「このデータをデータモデルに追加」をクリックし、 「OK」をクリックします。

f5 insert pivot table — ピボットテーブルを挿入して作成します。

ピボットテーブルメニューが表示され、左側に「レポートを作成するには、ピボットテーブルフィールドリストからフィールドを選択してください」というヘルプボックスが表示されます。

1. 「ピボットテーブルフィールド」では、現在アクティブなテーブルが1つだけなので、「アクティブ」ボタンが選択されています。「Quarter1」、「Quarter2」、「Quarter3」、「Quarter4」のボックスをクリックすると、左側のグリッドに数値が表示されます。

2. 「すべて」ボタンをクリックし、「マスターテーブル」リンクをクリックします。マスターテーブルのフィールドが表示されます。「City」の横にあるボックスをクリックします。「テーブル間のリレーションシップが必要になる場合があります」という黄色のボックスが表示されます。

3. ここで2つのテーブル間のリレーションシップを定義します。「作成」ボタンをクリックすると、「リレーションシップの作成」ダイアログボックスが表示されます。「テーブル」の下矢印をクリックし、使用可能なテーブルリストから「Sales」を選択します。「列（外部）」の下矢印をクリックし、フィールドリストから「Sales ID」を選択します。

4. 売上IDは両方のテーブルに共通する唯一のフィールドです。「関連テーブル」で「マスター」を選択し、「関連列（プライマリ）」で再び「売上ID」を選択して、 「OK」をクリックします。

f6 select fieldscreate relationship — 販売テーブルとマスターテーブルからフィールドを選択し、リレーションシップを作成します。

Excelが接続を確立し、画面に「都市別売上合計」というレポートを表示します。A1にレポートタイトルを入力すれば完了です。

f7 total sales by city report — 都市別の総売上レポート。

並べ替え、フィルターの作成、他のフィールドによるデータの選択

フィルターは、フィールドで特定のデータを選択するために使用されます。都市でデータをフィルタリングするには、テーブル内の任意の場所をクリックし、都市フィールドをクリックします（右側の小さな矢印に注意してください）。

1. 矢印をクリックすると、「並べ替え - フィルターオプション」ダイアログボックスが開き、「フィルター」と「並べ替え」の選択肢が表示されます。並べ替えを行う場合は、「AからZへ並べ替え」または「ZからAへ並べ替え」をクリックするか、下の図にある「並べ替えの詳細オプション」のオプションを参照してください。

フィルターオプションには、ラベルフィルター、値フィルター、検索（または現在の検索フィールドで特定のレコードを選択）があります。数百件のレコードを含む大規模なデータベースの場合は、検索ボックスに都市名（または都市名の一部）を入力し、砂時計アイコンをクリックして特定のレコード/都市を検索できます。Excelでは、検索ボックスの下のリストに都市名が表示されます。

2. データベースが比較的小さい場合は、まず「すべて選択」ボタンのチェックを外し、目的の都市までスクロールダウンしてボックスをクリックし、「OK」をクリックします。これで、レポートにはその都市の各四半期の売上高合計のみが表示されます。

ラベルフィルターと値フィルターは、検索を絞り込むための追加のフィルタリングオプションです。例えば、ラベルフィルターで「S」で始まるすべての都市を選択すると、サンディエゴとサンフランシスコが表示されます。「S」より小さいすべての都市を選択すると、ハリウッドとロサンゼルスが表示されます。数値フィールドは、他のほとんどのデータベースと同様に、「より小さい」、「より大きい」、「等しい」、「範囲内」などの条件でフィルタリングされます。

f8 sortfilter — 都市別に並べ替えやフィルタリングを行って、カスタム結果を取得します。

3. 別のフィールドを選択して、新しいレポートを素早く作成することもできます。例えば、四半期ごとの売上高と営業担当者ごとの合計を表示したい場合は、「市区町村」のチェックを外し、 「営業担当者」のチェックを入れます。すると、レポートが自動的に作成されます。

4. 次に、「営業担当者」をクリックし、下矢印をクリックして、「並べ替え/フィルターオプション」ダイアログボックスの「すべて選択」のチェックを外します。リスト上の4つの名前をクリックし、「OK」をクリックすると、フィルター処理されたレポートが表示されます。

ピボットテーブル／データベースの選択肢は無限大です。データ分析、セットの作成と管理、フィールドのグループ化、スライサーやタイムラインの挿入、ドリルアップとドリルダウン、データのインポートとエクスポート、さらにカスタムレイアウトとスタイルでレポートをデザイン、数百種類のカラフルなグラフを作成し、それらをすべて印刷して配布するなど、様々な方法があります。

f9 sortfilter2 — 営業担当者別の総売上を表示し、選択した営業担当者別にフィルタリングします。

チャートとスタイル

印刷する前に表に趣向を凝らすには、表にグラフや色、スタイルなどを追加してみましょう。

グラフを追加するには、テーブルを強調表示し、[ピボットテーブルツール] > [分析] > [ツール] > [ピボットグラフ] の順に選択し、グラフのギャラリーからグラフを選択して、[OK ] をクリックします。

色とスタイルを追加するには、「ピボットテーブルツール」>「デザイン」>「ピボットテーブルスタイル」を選択し、スタイルギャラリーからテーブルデザインを選択します。「ピボットテーブルスタイルオプション」グループの「縞模様の行」をクリックすると、奇数行と偶数行の色や網掛けが交互に表示され、見やすくなります。設定はこれだけです。