Excel のテーブル間にリレーションシップを作成する

VLOOKUP を使ってあるテーブルから別のテーブルに列を取り込んだことがありますか? Excel にデータ モデルが組み込まれたため、VLOOKUP は古いやり方になりました。2 つのデータ テーブルの間に、それぞれに含まれる一致データに基づいてリレーションシップを作成できます。それから、Power View シートを作成し、ピボットテーブルなどのレポートをそれぞれのテーブルのフィールドを使って作成できます。テーブルのソースが異なる場合でも作成できます。たとえば、顧客の売上データがある場合に、タイム インテリジェンス データを追加して関連付け、年次または月次の売上パターンを分析したい場合です。

ワークシートのすべてのテーブルが、ピボットテーブルと [Power View フィールド] リストに一覧表示されます。

お使いのブラウザーではビデオがサポートされていません。 Microsoft Silverlight、Adobe Flash Player、Internet Explorer 9 のいずれかをインストールしてください。

リレーショナル データベースから関連するテーブルをインポートすると、Excel では通常、自動的に作成されるデータ モデルにこれらのリレーションシップが作成されます。それ以外の場合は、リレーションシップを手動で作成する必要があります。

  1. ブックに少なくとも 2 つ以上のテーブルがあり、各テーブルには別のテーブルの列にマップできる列があることをご確認ください。

  2. データをテーブルとして書式設定するか、または

    新しいワークシートに外部データをテーブルとしてインポートします。

  3. 各テーブルに意味のある名前を付けます。[テーブル ツール] で [デザイン]、[テーブル名] の順にクリックし、名前を入力します。

  4. それらの 1 つのテーブルの列に、重複しない固有のデータ値があることを確認します。Excel は、1 つの列に一意の値がある場合にだけ、リレーションシップを作成できます。

    たとえば、タイム インテリジェンスを使って顧客の売上を関連付けるには、どちらのテーブルでも同じ形式の日付 (12/01/01 など) が使われ、少なくとも 1 つのテーブル (タイム インテリジェンス) でそれぞれの日付が列内で一度だけリストされている必要があります。

  5. [データ]、[リレーションシップ] の順にクリックします。

ブック内にテーブルが 1 つしかない場合は、[リレーションシップ] が淡色表示されます。

  1. [リレーションシップの管理] ダイアログ ボックスで、[新規作成] をクリックします。

  2. [リレーションシップの作成] ダイアログ ボックスで [テーブル] の矢印をクリックし、一覧からテーブルを選びます。一対多リレーションシップでは、このテーブルは "多" の側に当たります。顧客とタイム インテリジェンスの例では、どの日もたくさんの売上があり得るので、先に顧客売上テーブルを選びます。

  3. [列 (外部)] で、[関連列 (プライマリ)] に関連するデータを含む列を選びます。たとえば、両方のテーブルに日付の列がある場合は、ここでその列を選びます。

  4. [関連テーブル] で、先ほど [テーブル] で選んだテーブルに関連する、少なくとも 1 つのデータ列を含むテーブルを選びます。

  5. [関連列 (プライマリ)] で、[] で選んだ列の値と一致する一意の値を含む列を選びます。

  6. [OK] をクリックします。

Excel のテーブル間のリレーションシップについて

リレーションシップについての注意事項

例: タイム インテリジェンス データのフライト データへの関連付け

"テーブル間のリレーションシップが必要になる場合"

手順 1: リレーションシップで指定するテーブルを決定する

手順 2: あるテーブルからその次のテーブルへのパスを作成するときに使用できる列を見つける

リレーションシップについての注意事項

  • 異なるテーブルからピボットテーブルのフィールド リストにフィールドをドラッグする際に、リレーションシップが存在するかどうかがわかります。リレーションシップを作成するように要求されない場合は、データの関連付けに必要なリレーションシップ情報が Excel に既にあります。

  • リレーションシップの作成は、VLOOKUP の使い方に似ています。Excel がテーブルの行と別のテーブルの行を相互参照できるように、一致するデータを含む列が必要です。タイム インテリジェンスの例では、Customer テーブルに、インテリジェンス テーブルにも存在する日付の値が必要です。

  • データ モデルのテーブル リレーションシップでは、一対一 (1 搭乗者につき 1 枚の搭乗券など) または一対多 (1 フライトにつき多数の搭乗者など) が可能ですが、多対多はできません。多対多のリレーションシップにすると、"循環する依存関係が検出されました" などの循環依存のエラーが発生します。このエラーは、多対多の 2 つのテーブルを直接関連付けた場合、または間接的に関連付けた場合 (各リレーションシップ内では一対多でも、エンド ツー エンドでは多対多に見える、テーブル リレーションシップの連鎖) に発生します。リレーションシップについては、「データ モデルのテーブル間のリレーションシップ」を参照してください。

  • 2 つの列のデータ タイプは、互換性を持っている必要があります。詳細については、「データ モデルのデータ型」を参照してください。

  • リレーションシップを作成する方法は他にもあります。特に、使用する列がわからない場合に、より直感的に作成できる方法があります。詳細については、「Power Pivot のダイアグラム ビューでのリレーションシップの作成」を参照してください。

例: タイム インテリジェンス データのフライト データへの関連付け

テーブル リレーションシップとタイム インテリジェンスについては、Microsoft Azure Marketplace の無償のデータを使って学習できます。膨大なサイズのデータセットが含まれているため、データのダウンロードに時間がかかりすぎないようにするには、高速インターネット接続が必要です。

  1. Power Pivot in Microsoft Excel アドインを起動し、[Power Pivot] ウィンドウを開きます

  2. [外部データの取り込み]、[データ サービスから]、[Microsoft Azure Marketplace から] の順にクリックします。テーブルのインポート ウィザードに Microsoft Azure Marketplace のホーム ページが表示されます。

  3. [料金] で [無料] をクリックします。

  4. [カテゴリ] で [科学と統計] をクリックします。

  5. [DateStream] を探し、[購読] をクリックします。 このタイム インテリジェンス データ フィードの詳細を参照してください。

  6. Microsoft アカウントを入力し、[サインイン] をクリックします。データのプレビューがウィンドウに表示されるはずです。

  7. 一番下までスクロールし、[選択] をクリックします。

  8. [次へ] をクリックします。

  9. [BasicCalendarUS] を選び、[完了] をクリックしてデータをインポートします。インポートにかかる時間は、高速インターネット経由で約 1 分ほどです。終了すると、73,414 行が転送されたことを示す進捗レポートが表示されます。[閉じる] をクリックします。

  10. [外部データの取り込み]、[データ サービスから]、[Microsoft Azure Marketplace から選択] の順にクリックして、2 つ目のデータセットをインポートします。

  11. [種類] で [データ] をクリックします。

  12. [料金] で [無料] をクリックします。

  13. [US Air Carrier Flight Delays] を探し、[選択] をクリックします。

  14. 一番下までスクロールし、[選択] をクリックします。

  15. [次へ] をクリックします。

  16. [完了] をクリックしてデータをインポートします。インポートにかかる時間は、高速インターネット経由で 15 分ほどです。終了すると、2,427,284 行が転送されたことを示す進捗レポートが表示されます。[閉じる] をクリックします。これで、データ モデルに 2 つのテーブルが用意できました。これらを関連付けるには、各テーブルに互換性のある列が必要です。

  17. [BasicCalendarUS] の [DateKey] の日付形式が "12/01/01 12:00:00" になっている点に注目してください。[On_Time_Performance] テーブルには、datetime 列の [FlightDate] もあります。この列の値も、"12/01/01 12:00:00" という同じ形式で指定されます。どちらの列にも同じデータ型の一致するデータが含まれ、少なくとも一方の列 (DateKey) だけに固有の値が含まれます。次のいくつかの手順では、これらの列を使ってテーブルを関連付けます。

  18. [Power Pivot] ウィンドウで [ピボットテーブル] をクリックし、新規または既存のワークシートにピボットテーブルを作成します。

  19. [フィールド リスト] で [On_Time_Performance] を展開し、[ArrDelayMinutes] をクリックして [値] 領域に追加します。ピボットテーブルには、フライトが遅れた合計時間が分単位で表示されているはずです。

  20. [BasicCalendarUS] を展開し、[MonthInCalendar] をクリックして [行] 領域に追加します。

  21. ピボットテーブルに月が一覧表示されていること、ただし分数の合計がすべての月で同じであることに注目してください。同じ値の繰り返しは、リレーションシップが必要であることを示します。

  22. [フィールド リスト] の "テーブル間のリレーションシップが必要である可能性があります" で [作成] をクリックします。

  23. [関連テーブル] で [On_Time_Performance]、[関連列 (プライマリ)] で [FlightDate] を選びます。

  24. [テーブル] で [BasicCalendarUS]、[列 (外部)] で [DateKey] を選びます。[OK] をクリックしてリレーションシップを作成します。

  25. 遅延時間の合計分数が月ごとに違っている点に注目してください。

  26. [BasicCalendarUS] で、[YearKey] を [MonthInCalendar] の上にある [行] 領域にドラッグします。

これで、年、月、カレンダーのその他の値など、さまざまな切り口で到着時間の遅れを確認できます。

ヒント: 既定では、月がアルファベット順で一覧表示されます。Power Pivot アドインを使うと、月が時系列順に表示されるように並べ替えを変更できます。

  1. Power Pivot ウィンドウで [BasicCalendarUS] テーブルが開かれていることを確認します。

  2. [ホーム] テーブルで [列で並べ替え] をクリックします。

  3. [並べ替え] で [MonthInCalendar] を選びます。

  4. [基準] で [MonthOfYear] を選びます。

これで、ピボットテーブルは、月と年の組み合わせ (2011 年 10 月、2011 月 11 月) を月数 (10、11) で並べ替えます。並べ替えの順序の変更は簡単です。[DateStream] フィードにより、このシナリオに必要なすべての列が用意されるためです。別のタイム インテリジェンス テーブルを使っている場合は、実行する手順が変わります。

"テーブル間のリレーションシップが必要になる場合"

ピボットテーブルにフィールドを追加する際、ピボットテーブルで選んだフィールドに対してテーブル リレーションシップが必要かどうかが通知されます。

リレーションシップが必要な場合に [作成] ボタンが表示される

Excel は、リレーションシップが必要であることを通知しますが、使用するテーブルや列の種類、またはテーブル リレーションシップが可能かどうかは通知しません。必要な答えを得るには、以下の手順を試してください。

手順 1: リレーションシップで指定するテーブルを決定する

モデルにテーブルが少しか含まれていない場合、どのテーブルを使うべきかは一目瞭然です。ただし、モデルのサイズが大きい場合は、何らかの策が必要になります。1 つの方法は、Power Pivot アドインのダイアグラム ビューを使うことです。ダイアグラム ビューは、データ モデル内のすべてのテーブルを視覚的に表現します。ダイアグラム ビューを使うと、どのテーブルが、残りのモデルから分離しているかをすばやく判別できます。

分離したテーブルを示すダイアグラム ビュー

注: ピボットテーブルまたは Power View レポートでリレーションシップを使う場合、曖昧で無効なリレーションシップを作成することは可能です。たとえば、すべてのテーブルが、データ モデル内の他のテーブルと何らかの方法で関係している場合に、異なるテーブルから複数のフィールドを組み合わせようとすると、"テーブル間のリレーションシップが必要である可能性があります" というメッセージが表示されます。その原因として最も可能性が高いのは、多対多のリレーションシップが作成されていることです。使いたいテーブルと関連付けられたテーブル リレーションシップの連鎖をたどると、一対多のテーブル リレーションシップがおそらく 2 つ以上見つかります。すべてのケースに当てはまる簡単な解決策はありませんが、集計列を作成して、使いたい列を 1 つのテーブルに統合してみることをお勧めします。

手順 2: あるテーブルからその次のテーブルへのパスを作成するときに使用できる列を見つける

モデルと分離しているテーブルを特定できたら、テーブル内の列を確認し、モデル内の他の列に一致する値がないかどうかを調べます。

たとえば、地区別の製品売上データを含むモデルがあり、これに人口統計データをインポートして、地区別の売上と人口動向との相関関係を調べるとします。人口統計データは別のデータ ソースから取り込まれるため、テーブルはモデルとは分離した状態で用意されます。人口統計データをモデルに統合するには、人口統計テーブルのいずれかに、既に使っているものに対応する列を見つける必要があります。たとえば、人口統計データが地域別に編成されており、どの地域で売上が発生しているかが売上データでわかる場合、共通の列 (都道府県、郵便番号、地域など) を見つけることで、2 つのデータセットを関連付け、ルックアップを指定できます。

一致する値以外にも、リレーションシップを作成する際の要件がいくつかあります。

  • ルックアップ列のデータ値は固有にする必要があります。言い換えると、列内に重複データを含めることはできません。データ モデルでは、null 値と空の文字列は、個別のデータ値である空白と同等に扱われます。つまり、ルックアップ列に複数の null 値を指定することはできません。

  • ソース列とルックアップ列のデータ型は互換性がとれている必要があります。データ型の詳細については、「データ モデルのデータ型」を参照してください。

テーブル リレーションシップの詳細については、「データ モデルのテーブル間のリレーションシップ」を参照してください。

ページの先頭へ

スキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×