チュートリアル: Excel にデータをインポートし、データモデルを作成する

このチュートリアルのセクションの内容は次のとおりです。

• データベースからデータをインポートする

• スプレッドシートからデータをインポートする

• コピーと貼り付けを使用してデータをインポートする

• インポートしたデータ間のリレーションシップを作成する

• チェックポイントと確認テスト

このチュートリアルの最後にある確認テストで、学習したことを確認できます。

このチュートリアルのシリーズでは、オリンピックのメダル、開催国、オリンピックの各競技種目のデータを使用します。 各チュートリアルは順番に進めることをお勧めします。 また、チュートリアルでは、PowerPivot を有効にした Excel 2013 を使用します。 Excel 2013 の詳細については、 ここをクリックしてください。 PowerPivot の有効化のガイダンスについては、ここをクリックしてください。

データベースからデータをインポートする

このチュートリアルでは、最初に空白のブックを作成します。 このセクションの目標は、外部データ ソースに接続し、そのデータを Excel にインポートして、さらに分析できるようにすることです。

最初に、インターネットからデータをダウンロードしましょう。 このデータの内容はオリンピックのメダルに関するもので、Microsoft Access データベースです。

1. 次のリンクをクリックすると、このチュートリアルシリーズで使用しているファイルをダウンロードできます。 ダウンロードやマイドキュメント、または作成した新しいフォルダーなど、簡単にアクセスできる場所に4つのファイルをそれぞれダウンロードします。
   > Olympicmedals.accdb Access データベース
   Excel ブック
   を >OlympicSports.xlsx Excel ブック
   を >Population.xlsx Excel ブックを >DiscImage_table.xlsx

2. Excel 2013 で、空白のブックを開きます。

3. [データ] タブの [外部データの取り込み] グループにある [Access データベース] をクリックします。 リボンは、ブックに幅に基づいて動的に調整されるため、リボンのコマンドが次の画面と少し異なるように表示される場合があります。 最初の画面は、ブックが広い場合のリボンを示し、2 番目の画像は、画面の一部のみに表示されるようにブックのサイズを変更した場合を示しています。
   
   
   
   
   
    

4. ダウンロードした OlympicMedals.accdb ファイルを選び、[開く] をクリックします。 次のように、[テーブルの選択] ウィンドウが表示され、データベースのテーブルが表示されます。 データベースのテーブルは、Excel のブックまたはテーブルに似ています。 [複数のテーブルの選択を使用可能にする] チェック ボックスをオンにし、すてべのテーブルを選びます。 [OK] をクリックします。
   
   

5. [データのインポート] ウィンドウが表示されます。

   注: 次の画面に示すように、 このデータをデータモデルに追加するためのウィンドウの下部にあるチェックボックスに注目してください。 データモデルは、2つ以上のテーブルを同時にインポートまたは操作するときに、自動的に作成されます。 データモデルでは、テーブルが統合され、ピボットテーブル、 PowerPivot、Power View を使用して、詳細な分析が可能になります。 データベースからテーブルをインポートする場合は、そのテーブル間の既存のデータベースリレーションシップを使用して、Excel でデータモデルを作成します。 データモデルは Excel では透過的ですが、 PowerPivot アドインを使用して直接表示したり、変更したりすることができます。 データモデルについては、このチュートリアルの後半で詳しく説明します。

   
   [ ピボットテーブルレポート ] オプションを選択します。 Excel にテーブルをインポートし、インポートされたテーブルを分析するためのピボットテーブルを準備して、[ OK]をクリックします。
   
   

6. データがインポートされると、インポートされたテーブルを使用してピボットテーブルが作成されます。
   
   

データが Excel にインポートされ、データ モデルが自動的に作成されると、データを参照できます。

ピボットテーブルを使用してデータを参照する

インポートされたデータは、ピボットテーブルを使用して簡単に参照できます。 ピボットテーブルで、フィールド (Excel の列に似ています) をテーブル (Access データベースからインポートしたテーブルなど) から、ピボットテーブルの各ボックスにドラッグし、データの表示を調整します。 ピボットテーブルは、[フィルター]、[列]、[行]、[値] の ４ つのボックスで構成されています。

フィールドをドラッグする必要がある領域を特定するために、いくつかの実験が必要になる場合があります。 テーブルのフィールドは、必要に応じていくつでもドラッグできます。ピボットテーブルでは、データの表示方法が示されます。 ピボットテーブルのさまざまな領域にフィールドをドラッグして、無料でお試しいただけます。ピボットテーブルにフィールドを配置しても、基になるデータに影響はありません。

オリンピックのメダルのデータについてピボットテーブルで調べてみましょう。最初に、種別、メダルの種類、選手の国または地域別のオリンピック メダリストについて見てみます。

1. [ピボットテーブルのフィールド] で、Medals テーブルの横にある矢印をクリックして展開します。 展開した Medals テーブルの NOC_CountryRegion フィールドを [列] ボックスにドラッグします。 NOC は、National Olympic Committee (国内オリンピック員会) を表し、国または地域の組織単位です。

2. 次に、Disciplines テーブルから Discipline フィールドを [行] ボックスにドラッグします。

3. 種別をフィルター処理し、アーチェリー、飛び込み、フェンシング、フィギュア スケート、スピード スケートの 5 つのみを表示してみましょう。 フィルター処理は、[ピボットテーブルのフィールド] ボックス内またはピボットテーブルの自身の [行ラベル] フィルターから行います。

   1. ピボットテーブル内の任意の場所をクリックして、Excel ピボットテーブルが選択されていることを確認します。 [ ピボットテーブルフィールド ] リストで、[ 規律 ] テーブルが展開されている場合、その [作業分野] フィールドをポイントすると、フィールドの右側にドロップダウン矢印が表示されます。 ドロップダウンをクリックし、[ (すべて選択)] をクリックして すべての選択を解除し、下にスクロールして、射手、ダイビング、フェンス、図スケート、速度スケートを選択します。 [OK] をクリックします。

   2. または、ピボットテーブルの [ 行ラベル ] セクションで、ピボットテーブルの [ 行ラベル ] の横にあるドロップダウンをクリックし、[ (すべて選択) ] をクリックしてすべての選択を解除し、下にスクロールして射手、ダイビング、フェンス、図スケート、速度スケートを選択します。 [OK] をクリックします。

4. ピボットテーブルのフィールド] ボックスの一覧の Medals テーブルから、Medal を [値] ボックスにドラッグします。 値は数値のため、Excel によって Medal が "カウント / Medal" に自動的に変更されます。

5. Medals テーブルから Medal をもう一度選び、[フィルター] ボックスにドラッグします。

6. ピボットテーブルをフィルター処理し、メダル総数が 90 個を超えている国または地域を表示してみましょう。 ここではその方法をご紹介します。

   1. ピボットテーブルで、[列ラベル] の右側のドロップダウンをクリックします。

   2. [値フィルター] をポイントし、[指定の値より大きい] をクリックします。

   3. 最後のフィールド (右側) に「90」と入力します。 [OK] をクリックします。
      

ピボットテーブルは次の画面のようになります。

簡単な操作で、3 種類のテーブルのフィールドを含む、基本的なピボットテーブルが作成されました。 このように簡単に作成できたのは、テーブル間のリレーションシップがすでに作成されていたためです。 ソース データベースにテーブルのリレーションシップが存在しており、1 回の操作ですべてのテーブルをインポートしたため、Excel でこれらのリレーションシップをデータ モデル内に再作成できました。

では、複数の異なるソースのデータをインポートした場合や、後でインポートした場合はどうなるでしょうか。 一般的には、列の一致に基づいてリレーションシップを作成することで、新しいデータを組み込むことができます。 次の手順では、追加のテーブルをインポートし、新しいリレーションシップを作成する方法について学習します。

スプレッドシートからデータをインポートする

次に、別のソースからデータをインポートして、既存のブックからデータをインポートします。次に、既存のデータと新しいデータの間のリレーションシップを指定します。 リレーションシップを使用すると、Excel でデータのコレクションを分析したり、インポートしたデータから興味深い視覚エフェクトを作成したりできます。

最初に、空白のブックを作成し、データを Excel ブックからインポートしましょう。

1. 新しいブックを挿入し、名前を Sports にします。

2. ダウンロードしたサンプル データ ファイルが格納されているフォルダーに移動し、OlympicSports.xlsx を開きます。

3. Sheet1 のデータを選んでコピーします。 セル A1 など、データのあるセルを選ぶ場合は、Ctrl キーを押しながら+ A キーを押すと、隣接するすべてのデータを選べます。 OlympicSports.xlsx ブックを閉じます。

4. Sports ブックを開き、セル A1 にカーソルを置いてデータを貼り付けます。

5. データが強調表示された状態で、Ctrl キーを押しながら T キーを押し、データをテーブルとして書式設定します。 リボンの [ホーム] タブの [テーブルとして書式設定] をクリックして、データをテーブルとして書式設定することもできます。 データには先頭行があるため、次のように [テーブルの作成] ウィンドウの [先頭行をテーブルの見出しとして使用する] チェック ボックスをオンにします。
   
   
   
   データをテーブルとして書式設定すると、多くの利点があります。 名前をテーブルに割り当てることができるため、簡単に識別できます。 テーブル間のリレーションシップを確立することもできるため、ピボットテーブル、PowerPivot、Power View で調査や分析ができます。

6. テーブルに名前を付けます。 [テーブル ツール] の [デザイン] タブの [プロパティ] グループにある [テーブル名] ボックスに、「Sports」と入力します。 ブックは次の画面のように表示されます。
   

7. ブックを保存します。

コピーと貼り付けを使用してデータをインポートする

データを Excel ブックからインポートしたら、次に、Web ページで検索したテーブル、またはコピーして Excel に貼り付けることができる他のソースからデータをインポートしてみましょう。 次の手順では、オリンピックの開催都市をテーブルから追加します。

1. 新しいブックを挿入し、名前を Hosts にします。

2. 次のテーブルを選んでコピーします (先頭行を含む)

1. Excel で、Hosts ブックのセル A1 にカーソルを置き、データを貼り付けます。

2. テーブルとして書式設定します。 このチュートリアルの前半で説明したように、Ctrl キーを押しながら T キーを押してデータをテーブルとして書式設定するか、[ホーム] タブの [テーブルとして書式設定] をクリックします。 データには先頭行があるため、表示される [テーブルの作成] ウィンドウの [先頭行をテーブルの見出しとして使用する] チェック ボックスをオンにします。

3. テーブルに名前を付けます。 [テーブル ツール] の [デザイン] タブの [プロパティ] グループにある [テーブル名] ボックスに、「Hosts」と入力します。

4. [Edition] 列を選び、[ホーム] タブの [数値] グループで、数値として書式設定し、小数点以下の桁数を 0 にします。

5. ブックを保存します。 ブックは次のようになります。

Excel ブックにテーブルをインポートしたら、テーブル間のリレーションシップを作成できます。 テーブル間のリレーションシップを作成すると、2 つのテーブルのデータを組み合わせることができます。

インポートしたデータ間のリレーションシップを作成する

ピボットテーブルのフィールドを使用して、インポートしたテーブルからすぐに開始できます。 フィールドをピボットテーブルに取り込む方法を Excel が判別できない場合は、既存のデータ モデルでリレーションシップを確立する必要があります。 次の手順では、さまざまなソースからインポートしたデータ間のリレーションシップを作成する方法について学習します。

1. Sheet1の ピボットテーブルフィールドの上部にある [ すべて] をクリックして、次の画面に示すように、使用可能なテーブルの完全なリストを表示します。
   

2. 一覧をスクロールして、新しいテーブルが追加されていることを確認します。

3. Sports を展開し、[Sport] をクリックしてピボットテーブルに追加します。 次の画面に示すように、リレーションシップを作成するように求められます。
   
    

   通知が表示されるのは、基になるデータ モデルに含まれていないテーブルのフィールドを使用したためです。 テーブルをデータ モデルに追加する 1 つの方法は、データ モデルにすでにあるテーブルとのリレーションシップを作成することです。 リレーションシップを作成するには、テーブルの 1 つに繰り返されていない一意の値がある必要があります。 サンプル データでは、データベースからインポートした Disciplines テーブルに、SportID という名前の競技コードのフィールドが含まれています。 これらのサンプル競技コードは、インポートした Excel データのフィールドとして表示されます。 それでは、リレーションシップを作成しましょう。

4. 次の画面に示すように、[ピボットテーブルのフィールド] ボックスで、強調表示された領域にある [作成] をクリックし、[リレーションシップの作成] ダイアログ ボックスを開きます。
   
   

5. [テーブル] ボックスの一覧の [Disciplines] をクリックします。

6. [列 (外部)] ボックスの一覧の [SportID] をクリックします。

7. [関連テーブル] ボックスの一覧の [Stores] をクリックします。

8. [関連列 (プライマリ)] ボックスの一覧の [SportID] をクリックします。

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

ピボットテーブルが変更され、新しいリレーションシップが反映されます。 [行] ボックスのフィールドの順序により、ピボットテーブルが適切に表示されていません。 種別は指定した競技のサブカテゴリですが、[行] ボックスの Discipline を Sport の上に配置したため、正しい順序になっていません。 次の画面は、正しくない順序を示しています。

1. [ 行 ] 領域で、[分野] から [スポーツ] を移動します。 これはさらに優れています。次の画面に示すように、ピボットテーブルには、表示するデータが表示されます。
   
   

Excel の内部では、ブック全体で、ピボットテーブル、ピボットグラフ、PowerPivot、Power View レポートで使用できるデータ モデルが構築されています。 テーブル リレーションシップはデータ モデルの基本で、ナビゲーションや計算パスを決めるものになります。

次のチュートリアルでは、Excel 2013、PowerPivot、DAXを使ってデータモデルリレーションシップを拡張します。ここでは、ここで説明した内容に基づいて構築し、 PowerPivotと呼ばれる強力で視覚的な Excel アドインを使用して、データモデルを拡張します。 また、テーブル内の列を計算し、その計算列を使用して、他の関連のないテーブルをデータモデルに追加できるようにする方法についても説明します。

チェックポイントと確認テスト

これまでの内容を復習する

複数のテーブルのデータ (そのいくつかは個別にインポートしたものです) にアクセスするピボットテーブルを含む Excel ブックを作成しました。 データベースからのインポート、別の Excel ブックからのインポート、Excel へのデータのコピーと貼り付けについても学習しました。

これらのデータをまとめるため、Excel が行を関連付ける際に使うテーブル リレーションシップを作成する必要がありました。 また、テーブルの列を別のテーブルのデータに関連付けることが、リレーションシップの作成および関連する行の参照に必要不可欠であることも学習しました。

これで、このシリーズの次のチュートリアルに進む準備ができました。 次のチュートリアルのリンクは次のとおりです。

Excel 2013、Power Pivot、DAX を使ってデータ モデル リレーションシップを拡張する

クイズ

学習内容についてどれくらい理解できましたか? ここで理解度を確認できます。 次のクイズでは、このチュートリアルで学習した機能や要件について質問します。 答えはページの最後に示されています。 それでは挑戦してください。

質問 1: インポートしたデータをテーブルに変換することが重要な理由

A: インポートしたデータはすべてテーブルに自動的に変換されるため、テーブルへの変換操作は不要です。

B: インポートしたデータをテーブルに変換すると、データ モデルから除外されます。 データ モデルから除外されるのは、それらがピボットテーブル、PowerPivot、Power View で使用できる場合のみです。

C: インポートしたデータをテーブルに変換すると、データ モデルから除外できるようになり、ピボットテーブル、PowerPivot、Power View で使用できます。

D: インポートしたデータはテーブルに変換できません。

質問 2: Excel にインポートできるデータ ソースで、データ モデルに含めることができるのは次のうちどれですか？

A: Access データベースやその他多くのデータベース。

B: 既存の Excel ファイル。

C: Web サイトやドキュメント、その他 Excel に貼り付けることができるデータなど、Excel にコピーして貼り付け、テーブルとして書式設定できるデータ ソース。

上記すべて。

質問 3: ピボットテーブルの4つのピボットテーブルフィールド領域でフィールドの順序を変更すると、どうなりますか。

A: 何も変更されません。[ピボットテーブルのフィールド] の各ボックスにフィールドを配置すると、フィールドの順序を変更することはできません。

B: ピボットテーブルの書式設定が変更され、レイアウトに反映されますが、基になるデータは影響を受けません。

C: ピボットテーブルの書式設定が変更され、レイアウトに反映されますが、基になるデータもすべて完全に変更されます。

D: 基になるデータが変更され、新しいデータになります。

質問 4: テーブル間のリレーションシップを作成する場合、必要なものは何ですか。

A: 繰り返されていない一意の値を含む列をテーブルに含めることはできません。

B: 1 つのテーブルを Excel ブックに含めることはできません。

C: 列をテーブルに変換することはできません。

D: 上記のいずれも正しくありません。

クイズの正解

1. 正解: C

2. 正答 D

3. 正答 B

4. 正答 D