フィルターなしの HTML

Office: Excel 2013 のデータ モデルとリレーションシップ

Office: Excel 2013 のデータ モデルとリレーションシップ

  • Comments 1
  • Likes

Posted by Diego M. Oppenheimer
8 月 23 日

 

このブログ投稿は、Excel チームのプログラムマネージャーである Diego Oppenheimer がお届けします。

 

本日、このブログ記事を書かせていただくことをたいへん嬉しく思います。Excel でデータ分析を簡単に行うための別の方法について説明できるだけでなく、新しいデータ モデル機能とリレーションシップ機能についても紹介できるからです。これらの機能は、Excel でデータ分析を行う方

法を永遠に変えることになるでしょう。

ピボットテーブルの凄さと便利さをご存じない方は、こちらの記事 (ピボットテーブルレポートとピボットグラフ レポートの概要) またはこちらのトレーニング (ピボットテーブル I: Excel 2007 でピボットテーブルレポートを使ってみる) をご覧ください。これらの記事の中には少々古いものもありますが、原則と機能は変わっていません。

では、早速始めましょう。

 

家を探す

昨年の今ごろ、妻と私は、シアトルに家を買うことを検討していました。米国で最悪の交通渋滞に悩まされることは覚悟しました。そこで、Excel 名人と称する人たちと同様に、自分たちのパラメーターに合ったデータのテーブルを含むスプレッドシートを作成しました。これらのデータは、Zillow.com や Redfin.com などの多数の不動産サイトで簡単に見つかりました。ですが、こうしたサイトのうち、情報に基づいて意思決定できるだけの関連データをすべて含んでいるところは 1 つもありませんでした。このような場合は、データモデルを使用すると、複数のソースからのデータを組み合わせて、より複雑な分析を行うことができます。

最初の状態は次に示すとおりです。このワークブックはダウンロードすることも可能です。

 

初めてのデータモデル

上記のファイルを開くと、大量のデータが含まれたテーブルがあることがわかります。まず、データを簡単に選別できるよう、ピボットテーブルを作成します。[挿入] タブでピボットテーブルを選択すると、次のダイアログボックスが表示されます。

[ピボットテーブルの作成] ダイアログボックスには、[このデータをデータ モデルに追加する] という新しいオプションがあります。このデータモデルというのは、いったい何でしょうか。

「データ モデルは、Excel でデータを統合するための新しい方法です。モデルを利用することで、複数のテーブルからデータを取得するピボットテーブルやその他のレポートを作成できます。テーブル間にリレーションシップが定義され、各行をどのように関連付けるかが決められるため、データにはまとまりがあります。」続きを読む...

つまり、新しいデータ モデルにより、多数の異なるソースからのデータをデータ ソース間の "リレーションシップ" を作成することにより組み合わせることができる "モデル" を構築することが可能になります。データベースの知識がある方は、テーブル間の連結を作成するのに似ていると考えることができます。ただし、すべてのテーブルは Excel 内に存在します。

このデータをデータ モデルに追加することを選択したのは、他のソースから取得するデータと組み合わせて、分析の精度をより完全なものにするためです。

 

ピボットテーブルの作成

最初に、選択した物件の数を郵便番号別に確認できるようにします。それには、[ZIP] フィールドを [] にドラッグし、[LISTING ID] フィールドを [] にドラッグします。既定では、これによって "LISTING ID の総計" が表示されますが、必要なのは "個数" です。そこで、"総計" という見出しを右クリックして [値フィールドの設定]" をクリックし、[データの個数] に変更します。以下に示すように、この段階でふるいにかける必要がある物件は 165 件だけです。

分析に役立つよう、いくつかのフィールドをさらにピボットテーブルに追加することにします。[LIST PRICE][DAYS ON MARKET]、および [SQFT] を追加し、[値フィールドの設定] を [平均] に変更します。

この時点で、ピボットテーブルは次のようになっています。

また、フィールド リストは次のようになっています。

これで、条件を満たす物件の数を郵便番号ごとに表示し、平均価格、床面積、市場経過日数 (不動産仲介業者によると、このデータは交渉時に役立つそうです) などの追加データも含むレイアウトができました。

 

郵便番号からわかること

あらゆる不動産サイトを見ていて気付くのは、物件については膨大な情報が表示されている一方、その近所のことはほとんどわからないということです。選択した郵便番号の地域の人口統計をもっと詳しく把握することはできないでしょうか。

さいわい、この種の問い合わせを行うのに適したマーケットプレイスがあります (http://datamarket.azure.com)。ここは非常に便利です。詳細については、こちらをご覧ください。Microsoft/Live-ID が必要ですが、無料で取得できます。

米国住所の例となりますが、検索ボックスに「Demographics」と入力し、「2010 Key US Demographics by ZIP Code, Place and County (Trial)」というデータセットを検索します。このデータ セットへのリンクをクリックすると、最初のオプションは [Explore Data Set] です。これは私の目的にぴったりのオプションです。

次に、データ セットを関連性のあるものだけに絞り込みます。そこで、[GeographyType] を郵便番号、[StateAbbreviation] を [WA] にそれぞれ設定し、[RUN QUERY] をクリックします。これで必要な情報が表示されました。


これを Excel に取り込むには、この Web サイトの 2 つの情報が必要です。そこで、以下の情報をどこかにコピーしておきます。

(1) 現在表示されているクエリの URL

(2) プライベート アカウント キー

以上がわかれば、検索結果をデータ モデルに簡単に追加できます。Excel で、[データ] タブを表示し、[その他のデータソース]、[Windows Azure Marketplace から選択] を選択します。

Web サイトから保存した情報を入力します。


テーブルを選択します。

[完了] をクリックし、[接続の作成のみ] を選択します。

メモ: なぜ [接続の作成のみ] を選ぶのか、と思われるかもしれません。これは、データを Excel シートに組み込むのではなく、データ モデルに直接組み込むためです。ここでは元のテーブルと組み合わせて使用するだけなので、テーブルとしてシートに組み込んだり、新しいピボットテーブルやピボットグラフを作成したりする必要はありません。PowerView レポートが何をするものなのか知りたい場合は、Sean Boon によるこちらの投稿(英語)をご覧ください。

 

データの組み合わせ

元のピボットテーブルのフィールド リストに戻ると、上部がわずかに変更されているのがわかります。

[選択フィールド] というタブ (選択済み) と、[すべてのフィールド] というタブがあります。フィールドリストは、新たに作成したデータ モデルを参照するための最良の方法であり、そのために使用するのが [すべてのフィールド] タブです。このタブには、データモデルに追加されたすべての接続またはテーブルが表示されます。実際に見てみましょう。

元のテーブル (Table 1) と、データ マーケットから追加したテーブルの両方があるのがわかります。

ここで便利な機能を紹介します。それぞれの郵便番号について、もう少し詳しい情報が知りたいとします。そこで、demog1 テーブルのフィールドをピボットテーブルに追加します。最初に追加するのは [MedianAge2010] です (私たちは同年代の人たちが住む地域に住みたいと思っています。共通点を持つ隣人に出会う可能性が高くなるからです)。

ここで 2 つのことに気付きます。まず、すべての郵便番号で年齢の中央値が同じ値になっています。したがって何かが間違っている可能性があります。

次に、フィールド リストに小さなメッセージが表示されています。

この Excel からのメッセージは、2 つのテーブルの間にリレーションシップが必要であることを示しています。そこで、リレーションシップを作成します。

リレーションシップを使用するときは、いくつかの点に注意する必要があります。

· 選択した両方の列に、同じ種類のデータ (この場合は郵便番号) が含まれている必要があります。

· リレーションシップは、一方のテーブルの列に一意の値が含まれている場合にのみ機能します。

· 関連列 (プライマリ) は、常に一意の (重複しない) 値を含んでいる列である必要があります。

· ピボットテーブルで関連テーブルの内容を値に配置できるようにするには、関連テーブルのフィールドを行または列に含める必要があります。

最後の条件を満たすために、[ZIP] を削除し、[GeographyId] を行に追加しますこれでピボットテーブルは次のようになります。

このように大量の空白が表示されているのは、マーケットプレイス データのすべての郵便番号について、条件に一致する物件が Table 1 にあるわけではないからです。これらの空白は、[行ラベル] の横の下向き矢印をクリックし、[値フィルター][指定の値に等しくない] を順にクリックすると簡単にフィルタリングできます。

これで、対象の郵便番号だけが表示されました。さらに、最良の郵便番号を見つけ出すために、いくつかのフィールド、条件付き書式、および並べ替えを追加します。結果は次のようになります。

価格と平均 SQFT が中程度で、失業率と空家率が低い 98103 が有力な候補のようです。[ListingID] の個数を追加すると、この地域で条件を満たす物件は 8 件あることがわかります。

 

データ モデルの詳細

この最後の例では、データ モデルでできることのごく一部を紹介したに過ぎません。さらに詳しいことは今後のブログ投稿で紹介する予定ですが、以下にポイントをまとめておきます。

  • 各ブックに設定できるデータ モデルは 1 つだけです。
  • Excel 内の任意のテーブルをデータ モデルに追加できます。
  • ほとんどのデータ ソースをデータ モデルに追加できます (SQL、Odata、Atom フィード、Excel テーブルなど)。
  • データ モデルのテーブルに行の制限はありません。
  • 複数のテーブルにまたがってリレーションシップを定義できます。

以上の情報で新しいデータ モデルの機能について興味を持っていただければ幸いです。ご意見、ご質問があれば、以下にお気軽にご記入ください。またツイッター @doppenhe でもメッセージをお寄せください。

添付ファイル: HousingData.zip

  • 記事と関係ないコメントですみません。

    オフィス2013への無償アップグレード対象はオフィス2010ですが、オークションで新品として購入した場合はどうなるのでしょうか。そのあたりもはっきりさせてください。

    あまり高いお金を払いたくない人のためにもよろしくおねがいします。