Excelは膨大な量のデータを分析できますが、データモデル機能を活用してデータを整理しなければ、作業効率が悪すぎるかもしれません。この機能を使うと、共通の列に基づいてリレーションシップを作成することで、複数のテーブルのデータを統合できます。このモデルはバックグラウンドで動作し、ピボットテーブルオブジェクトやその他のレポート機能を簡素化します。この記事では、データモデル機能を使って2つのテーブル間のリレーションシップを作成し、ピボットテーブルを作成する方法を説明します。
Windows 10 64ビットシステムでExcel 2016を使用しています。ご自身のデータを使用することも、デモ用の.xlsxファイルをダウンロードすることもできます。データモデルはバージョン2013と2016で利用可能です。Excel 365のブラウザ版はピボットテーブルオブジェクトをサポートしていますが、ブラウザではデータモデルを実装できません。
単純な問題
さて、あなたが大規模な食料品フランチャイズで働いていて、棚配置データを分析したいとしましょう。商品テーブルをインポートしましたが、各商品には棚配置コードが割り当てられています。しかし、このコードはあなたにとっては意味がありません。そこで、役立つ説明が含まれた棚配置コードのテーブルをインポートします。しかし、各レコードに説明を追加するにはどうすればよいでしょうか？
多くの人は、元のデータセットに列を追加するためにVLOOKUP()を使うでしょう。これは馴染みのある関数であり、分析するレコードが数千件に上る場合を除いてはうまく機能します。しかし、処理速度は遅くなるものの、それでも十分に機能します。その後、各製品の説明が追加されたデータセットを分析するには、ピボットテーブルを使用する可能性が高いでしょう。Excelのデータモデルのおかげで、VLOOKUP()を完全に回避し、ピボットテーブルに直接移動できます。
Excelのデータモデルは、共通フィールドを使用して2つ（またはそれ以上）のデータセット間のリレーションシップを作成します。この場合、共通フィールドは図Aに示すように棚コードです。左側のデータテーブルと右側の参照テーブルの2つのテーブルがあります。Excelのデータモデル機能を使用すると、VLOOKUP ()などの関数を使用せずに値をグループ化および分析する際に、棚コードではなく説明フィールドが表示されます。棚コードではなく説明を表示することで、最終的な製品の読みやすさが向上します。
図A
棚コード フィールドによって関連付けられた 2 つのデータ セット。
データベースを扱ったことがある方なら、「リレーションシップ」という言葉はご存知でしょう。もしこの言葉に馴染みがない方のために説明すると、リレーションシップとは、2つのデータセットを共通の列（フィールド）の値で結び付けることです。2つのデータセットを関連付けることで、データを意味のある方法で組み合わせることができます。
参照: Excel のデータ検証の力を活用する (無料 PDF) (TechRepublic)
データをテーブルオブジェクトに変換する
通常のデータセット間でリレーションシップを作成することはできません。データモデルはテーブルオブジェクトでのみ機能します。サンプルデータセットはすでに変換されていますが、変換方法を知っておく必要があるかもしれません。幸いなことに、簡単です。
- データ セット内の任意の場所をクリックします。
- Ctrl + t キーを押すか、[挿入] タブをクリックして、[表] グループの [表] をクリックします。
- 「テーブルにヘッダーを付ける」オプションをオンまたはオフにします。この場合は「ヘッダーを付ける」になっています（図B）。
- [OK]をクリックします。
- 新しいテーブルを選択したまま、名前ボックス コントロール (数式バーの左側 (図 C )) にわかりやすい名前を入力します。必ず Enter キーを押します。元のデータ セットに ProduceTable という名前を付けました。
図B
データ セットをテーブルに変換します。
図C
テーブル オブジェクトに名前を付けると、作業が簡単になります。
この手順を繰り返して、参照値をテーブルに変換し、「ShelfCodesTable」という名前を付けます。両方のデータセットが変換されたら、それらの間のリレーションシップを作成する準備が整います。
関係を築く
両方のデータセットがテーブルオブジェクトになったら、それらの間にリレーションシップを作成できます。そのためには、「データ」タブをクリックし、「データツール」グループの「リレーションシップ」をクリックします。現在、リレーションシップは設定されていないため、ダイアログは空です。「新規」をクリックして開始します。表示されるダイアログで、「テーブル」と「関連テーブル」のドロップダウンをクリックします。図Dに示すように、両方のテーブルオブジェクトが表示されます。（デモファイル以外のファイルを操作している場合は、他のオブジェクトが表示される場合があります。）
図D
データ モデルは両方のテーブル オブジェクトを参照できます。
テーブルには、なんらかの方法で分析するデータが含まれています。関連テーブルには、報告されるデータをより有意義にするルックアップ値が含まれています。何が起こっているかを理解することが重要なので、ここで立ち止まって確認しましょう。製品テーブルには、分析するデータが含まれています。これは、担当者別の製品棚情報のリストです。すべての値が繰り返されています。各人物は複数回出現し、各果物は複数回出現し、月と棚コードも複数回出現します。ルックアップ テーブルは、一意の行を含む小さいテーブルです。各行には、棚コードと説明が含まれています。このテーブルには重複する値はありません。各棚コードは、ルックアップ テーブルでは 1 回だけ出現しますが、農産物データ セットでは複数回出現する可能性があります。
共通列は「棚コード」列です。この2つの列を使って、2つのテーブルオブジェクトを関連付けます。リレーションシップを作成するには、「テーブル」ドロップダウンから「ProduceTable」を選択し、「関連テーブル」ドロップダウンから「ShelfCodesTable」を選択します。
次に、「列（外部）」および「関連列（プライマリ）」ドロップダウンを使用して、テーブルオブジェクト（棚コード）を関連付ける列を特定します。図Eは、選択されたテーブルと列を示しています。
図E
リレーションシップ内のテーブルと関連列を設定します。
列の設定については少し説明が必要です。「列（外部）」は、農産物テーブル内の重複可能な列を定義します。「関連列（プライマリ）」は、ルックアップデータセット内の一意の列を定義します。2つの列には同じ値が含まれます。簡単に言うと、ルックアップデータセット内の値を検索し、農産物レコードと一緒に表示します。
4つの設定をすべて選択したら、次のステップに進む準備が整います。「OK」をクリックし、「閉じる」をクリックしてください。Excel は自動的にリレーションシップを作成します。この処理には数秒かかる場合があります。Excel は、棚コードフィールドに基づいてデータモデル内のデータを結合します。データモデルにはデータとリレーションシップが含まれていますが、ユーザーからは見えません。ここで注目すべき重要な点は、データモデルソリューションは、LOOKUP() 関数を使用した式が満載のシートよりも大幅に少ないメモリを必要とするということです。また、大きなブックではパフォーマンスが向上します。リレーションシップを作成すると、Excel はこれらのテーブルをドロップダウンのワークシートテーブルではなく、データモデルテーブルとして識別します。
Excel で何が行われたかを確認するには、「データツール」グループの「データモデルの管理」をクリックしてください。このオプションがグレー表示されている場合、サポート対象のバージョンがインストールされていません。ご安心ください。この機能（Power Pivot）のメリットは引き続きご利用いただけますが、結合されたテーブルは表示できません。この機能を初めて使用する場合は、プロンプトが表示されたら「有効にする」をクリックして、データ分析アドインを有効にする必要がある場合があります。
最初は、結果は元のデータセット、つまり ProduceTable と同じです。「表示」グループの「ダイアグラムビュー」をクリックすると、図 Fに示すように、2つの Table オブジェクト間の一対多リレーションシップを示すダイアグラムが表示されます。これで、新しいリレーションシップに基づいてピボットテーブルを作成できます。続行するには、PowerPivot ウィンドウを閉じてください。
図F
アスタリスクは多側を識別し、1 は一側を識別します。
ピボットテーブルを作成する
これで、両方の Table オブジェクトを評価するピボットテーブルを作成する準備が整いました。「挿入」タブをクリックし、「テーブル」グループの「ピボットテーブル」をクリックします。ピボットテーブルを使い慣れている方は、「このブックのデータモデルを使用する」という新しいオプションに気付くかもしれません。このオプションは、ブックにリレーションシップが含まれている場合にのみ表示されます。図 Gに示すように、このオプションを選択します。「新しいワークシート」オプションを選択し、「OK」をクリックして続行します。
図G
データ モデルを使用してピボットテーブルを作成します。
ピボットテーブルフィールドウィンドウには、両方のテーブルオブジェクトが表示されます。テーブルアイコンの上部に太線があることにご注意ください。これは、テーブルがリレーションシップの一部であり、データモデルに含まれていることを意味します。境界線が空の場合、テーブルはリレーションシップの一部ではありません。
それでは、各人が商品を棚に並べた回数をカウントするピボットテーブルを作成しましょう（少し不自然な例ですが、シンプルで無駄な手順は発生しません）。図Hに示すように、青果テーブルを展開し、「人員」と「棚コード」を「行」に、「月」を「列」に、「棚コード」を「値」にドラッグします。（Excelはデフォルトでカウントを表示します。）[デザイン]タブで、データとラベルを区別するために、「ライトブルー」、「ピボットスタイル」を「ライト9」に設定しました。
図H
シンプルなピボットテーブルを作成します。
ご覧の通り、これらの棚コードは、少なくともほとんどの人にとっては意味がありません。ここでデータモデルの魔法が活躍します。行セクションから棚コードフィールドを削除します。次に、上部ペインでShelfCodesTableを展開し、「説明」列を行セクションにドラッグします。図Iに示すように、意味のある説明が表示されるようになりました。
図I
意味のないコードの代わりに説明的なラベルを表示します。
一度コツをつかめば、今のように列を置き換える必要はありません。あのちょっとした演出は、効果を出すためだけのものでした！
潜在的
一見すると、VLOOKUP() 関数を追加する代わりにリレーションシップを作成しただけの、単なる作業交換のように思えますが、それは例が単純だからです。リレーションシップを作成すれば、様々な用途に活用できます。ぜひ、実際の状況で適用してみてください。そうすれば、リレーションシップを構築することの価値を実感していただけるはずです。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
以下も参照:
- Microsoft Word でスタイルを変更およびカスタマイズする 5 つの方法 (TechRepublic)
- COUNTIFS() を使用して複数のデータセットを比較する方法 (TechRepublic)
- Office Q&A: 大きな問題と Excel の重複レコードの検出を解決する簡単な Word の置換テクニック (TechRepublic)
- Excel の数式を使用して複数列のリストを比較する方法 (TechRepublic)
- Excelの条件付き書式を使ってリストを比較する方法（TechRepublic）
- Microsoft、Excelに新たな地理・株価データタイプを追加（ZDNet）
