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

By /

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

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Power BI その他…表示数を減らす

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

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

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

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

  2. 次のいずれかの操作を行います。 target=”_blank” title=”データをテーブルとして書式設定” rel=”noopener”>データをテーブルとして書式設定するか、新しいワークシートで target=”_blank” title=”外部データをテーブルとしてインポート” rel=”noopener”>外部データをテーブルとしてインポート します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. target=”_blank” title=”Power Pivot in Microsoft Excel アドインを起動し、[Power Pivot] ウィンドウを開きます” rel=”noopener”>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 は、2012 年 1 月 1 日午前 12:00:00 の形式であることに注意してください。 On_Time_Performance テーブルには datetime 列 FlightDate もあり、値は 2012 年 1 月 1 日 12:00:00 AM の同じ形式で指定されます。 2 つの列には、同じデータ型の一致するデータが含まれており、少なくとも 1 つの列 (DateKey) には一意の値のみが含まれています。 次のいくつかの手順では、これらの列を使用してテーブルを関連付けます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ページの先頭へ

上部へスクロール