わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

2つのテーブルをExcelデータモデルに追加してキューブ関数でデータを取り出す【MOSエキスパート2016】

以前からMOS試験(ExcelのExpertレベル)ではキューブ関数が出題されていますが、全国にある試験会場からキューブのサーバに接続することはできないので、デモ画面(仮想の画面)で出題されていました。

しかし、Excel2016の試験では出題傾向が変わり「Excelデータモデル」からデータを取得する問題しか出題されなくなりました。そのため、キューブ関数が何の役に立つのか、何が便利なのかが全く分からない試験問題になってしまいました。しかも、問題に使われるExcelのファイルにはすでにデータモデルが設定されていて、そこからキューブ関数でデータを取り出すことだけが出題されるため、対策テキストの問題を解いても理解できない受験者が多いようです。

そこで、テーブルをデータモデルに追加して、キューブ関数を使うまでの一連の流れを解説します。この作業はMOSには出題されませんが、キューブ関数を理解する上で必須の知識ですから、ぜひ練習しておきましょう。

※なお、この記事はExcel2016で作成しており、Excel2010以前には対応していません。

 

 

目次

0.MOS Excel Expertについて

MOSエキスパート試験の概要と、MOSの練習問題についてはFOM出版の対策テキストをご覧ください。

また、「キューブ」とは何か、ということについてはこちらの記事をご覧ください。

 

1.テーブルを作りテーブル名をつける

問題

2つのシートにそれぞれ表を入力した。これらをテーブルにして「選手」「チーム」という名前をつけなさい。

f:id:waenavi:20191018093513j:plain

f:id:waenavi:20191018093531j:plain

 

解説

原則として1つのシートに1つの表を作ります。このとき、リスト形式の表を作らなければなりません(参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。1つ目の表を全部選択します。

f:id:waenavi:20191018093648j:plain

 

挿入タブ、テーブルをクリックします。

f:id:waenavi:20191018093717j:plain

 

「先頭行をテーブルの見出しとして使用する」のチェックを入れます。

f:id:waenavi:20191018093739j:plain

 

テーブルをクリックすると、テーブルツール・デザインタブが表示されます。

f:id:waenavi:20191018093825j:plain

 

その左端にテーブル名を入力する欄があります。ここに「選手」と入力します。これでテーブル名をつけることができました。

f:id:waenavi:20191018093856j:plain

 

同様に、2つ目の表を全部選択して、挿入タブのテーブルをクリックします。

f:id:waenavi:20191018093923j:plain

 

「先頭行をテーブルの見出しとして使用する」のチェックを入れます。

f:id:waenavi:20191018093945j:plain

 

テーブル名を「チーム」にします。

f:id:waenavi:20191018094025j:plain

 

名前ボックスにも表示されます。

f:id:waenavi:20191018094045j:plain

 

2.リレーションシップ

問題

2つのテーブル間にリレーションシップを作成しなさい。なお、リレーションシップに使用する列は「チームID」とする。 

解説

(1)リレーションシップの作成

2つのテーブルには「チームID」があります。

f:id:waenavi:20191018094201j:plain

f:id:waenavi:20191018094205j:plain

 

データタブ、リレーションシップをクリックします。

f:id:waenavi:20191018094235j:plain

 

新規作成をします。

f:id:waenavi:20191018094301j:plain

 

テーブルは「選手」、列(外部)は「チームID」です。関連テーブルは「チーム」、関連列(プライマリ)は「チームID」です。

f:id:waenavi:20191018094419j:plain

 

これでリレーションシップを設定することができました。

f:id:waenavi:20191018094453j:plain

 

(2)リレーショナルデータベース

これら2つの表はもともと1つの表として作成することも可能です。チーム名とリーグはVLOOKUP関数で検索すればよいです。

f:id:waenavi:20191018094624j:plain

 

しかし、[チームID]-[チーム名]-[リーグ]のデータは重複しており、この関係が変わることはありません。関係が変わらないデータを重複して入力することは非効率であり、データの管理の仕方として間違っています。

f:id:waenavi:20191018094651j:plain

 

そこで、この部分だけを分離して、別の表を作成します。

f:id:waenavi:20191018094806j:plain

 

いっぱんにこの表のことをマスタといいますが、Excelでは関連テーブルと呼びます。関連テーブルの左端はコード番号(ID)になるようにします。この列のことを主キー(プライマリキー)といい、Excelでは関連列といいます。

f:id:waenavi:20191018095458j:plain

 

分離すると、元の表はコードだけ入力すればよいことになります。この列のことを外部キーといいます。

f:id:waenavi:20191018095737j:plain

 

作成した2つのテーブルはそのままでは1つのデータベースとはなりません。リレーションシップを作成することによって1つのデータベースとして扱われます。このようなデータベースのことをリレーショナルデータべース(RDB)といいます。

f:id:waenavi:20191018100144j:plain

 

3.Excelデータモデル

問題

リレーションシップを設定した2つのテーブルが、Excelデータモデルに追加されたことを確認しなさい。

解説

(1)すでにデータモデルのテーブルになっている

もう一度、リレーションシップをクリックします。

f:id:waenavi:20191018100226j:plain

 

作成したリレーションシップの編集をします。

f:id:waenavi:20191018100254j:plain

 

データモデルのテーブル」と表示されています。2つのテーブルがExcelデータモデルに追加されたことを表しています。

f:id:waenavi:20191018100314j:plain

 

(2)データモデル

リレーションシップを設定するとExcelデータモデルに追加されます。

f:id:waenavi:20191018100519j:plain

 

Excelデータモデル(PowerPivotデータモデルともいう)は、Excelで作成した複数のテーブルをまとめて1つのデータベースにしたものです。テーブルのほかに、Accessのデータや外部ファイル、外部サーバのデータを取り込むこともできます。

f:id:waenavi:20191017203529j:plain

 

作業しているExcelブック(ファイル)に保存されるものなので、外部のサーバにアクセスすることはありません。また、他のExcelファイルに影響を与えるものでもありません。

ちなみに、外部のデータベースにアクセスするときには識別するために「接続名」をつけます。

f:id:waenavi:20191018101214j:plain

 

この接続名は、接続の際に自由に名前を付けることができますが、Excelデータモデルには自動的に「ThisWorkbookDataModel」という接続名が付きます。したがって、キューブ関数でこのデータモデルにアクセスするときには必ず、接続名は「ThisWorkbookDataModel」としなければなりません。

f:id:waenavi:20191018101553j:plain

 

4.データモデルでピボットテーブルを作る

問題

次のピボットテーブルを作成しなさい。

f:id:waenavi:20191018102519j:plain

 

解説

挿入タブ、ピボットテーブルをクリックします。

f:id:waenavi:20191018102052j:plain

 

「このブックのデータモデルを使用する」を選びます。また、新規ワークシートを選びます。

f:id:waenavi:20191018102128j:plain

 

ピボットテーブルのフィールドにはチームと選手の2つのテーブルがあります。

f:id:waenavi:20191018102250j:plain

 

テーブル「チーム」からリーグとチーム名、テーブル「選手」から打席、HRを取得します。

f:id:waenavi:20191018102335j:plain

 

2つのテーブルからデータを取得して、1つのピボットテーブルを作ることができました(参考:【Excel】ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ)。

f:id:waenavi:20191018102022j:plain

 

別解

ピボットテーブルを作成するときに「外部データソースを使用」を選び、接続の選択をクリックします。

f:id:waenavi:20191018102702j:plain

 

テーブルの中から、ブックのデータモデルのテーブル(2テーブル)を選びます。

f:id:waenavi:20191018102803j:plain

 

このとき、接続名には「ThisWorkbookDataModel」と表示されます。

f:id:waenavi:20191018102847j:plain

 

5.キューブ関数で取り出すことの意味

問題

セルB1に「右」の打席を表すメンバーを取得して項目名を表示しなさい。また、A列のチーム名がキューブ関数で取得されたものであることを確認して、セルB2以降に各チームの右打者のHRの合計本数を表示しなさい。

f:id:waenavi:20191018103010j:plain

 

解説

キューブ関数の使い方についての詳しい解説はこちらの記事をご覧ください。

 

セルに次のような数式を入力します。

  • =CUBEMEMBER("ThisWorkbookDataModel","[選手].[打席].[右]")

「右」と表示されます。「右」と直接入力するのと何が違うのかと思うかもしれませんが、それはデータモデルから取り出しているからそのように思うだけであって、外部のサーバにアクセスしたときのことを考えましょう。外部のサーバーに「右」という項目があるのかどうかが分からないのでそれを探して表示しているのです。なければエラーが返ってくるのでIFERROR関数を使って別の処理をすることができます。

f:id:waenavi:20191018103311j:plain

 

A列も同じようにCUBEMEMBER関数でチーム名を取得しています。

f:id:waenavi:20191018103439j:plain

 

次の数式を入力します(参考:キューブ関数とは何か、CUBE関数の使い方をわかりやすく解説します【MOS Excel2016合格対策】)。

  • =CUBEVALUE("ThisWorkbookDataModel",A2,$B$1,"[Measures].[合計 / HR]")

これでホームランの合計本数を求めることができます。

f:id:waenavi:20191018103513j:plain

 

これはピボットテーブルで求めた値と同じです。ピボットテーブルで集計したものをわざわざキューブ関数で取り出して何が便利なのかと思うかもしれませんが、これも、外部のサーバにアクセスしたときのことを考えましょう。外部のサーバーにある集計値(メジャー、Measures)を直接Excelに表示できるというのが本来の使い方なのです。

f:id:waenavi:20191018103545j:plain

 

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]