PowerPivot for Excelの最も重要な項目の1つは、データセットにルックアップテーブルを追加する機能です。ほとんどの場合、作業しているデータには分析に必要なすべてのフィールドがありません。たとえば、日付フィールドがありますが、四半期ごとにデータをグループ化する必要があります。数式を書くこともできますが、PowerPivot環境で簡単なルックアップテーブルを作成する方が簡単です。
また、このルックアップテーブルを、月の名前や年の最初/後半などの別のグループに使用することもできます。データウェアハウジング用語では、実際には日付ディメンション表を作成しています。この記事では、PowerPivot for Excelプロジェクトを強化するためのディメンションテーブルの例をいくつか紹介します。
新しいテキスト次元(ルックアップ)テーブル
注文データを持つ表を考えてみましょう。テーブルに顧客、受注日、受注合計、注文タイプのフィールドがあるとします。注文タイプフィールドに焦点を当てます。注文タイプフィールドに次のような値が含まれているとします。
- ネットブック
- デスクトップ
- モニター
- プロジェクター
- プリンタ
- スキャナー
- デジタルカメラ
- デジタル一眼レフカメラ
- フィルムカメラ
- ビデオカメラ
- オフィス電話
- スマートフォン
- PDA
- 携帯電話アクセサリー
実際には、これらのコードがありますが、この例を単純なものにするために、これらが注文表の実際の値であると仮定してください。 PowerPivot for Excelを使用すると、オーダーの種類ごとに簡単にオーダーをグループ化できます。
別のグループ化が必要な場合はどうなりますか?たとえば、コンピュータ、カメラ、電話などのカテゴリグループが必要であるとします。注文テーブルにはカテゴリフィールドはありませんが、PowerPivot for Excelのルックアップテーブルとして簡単に作成できます。完全なサンプルルックアップテーブルは以下の通りです 表1 .
手順は次のとおりです。
- ステップ1: ルックアップテーブルのタイプフィールドとは別のリストが必要です。これはあなたの検索フィールドになります。データセットから、オーダータイプフィールドから値の別個のリストを作成します。別の種類の種類の一覧をExcelワークブックに入力します。列にラベルを付ける タイプ .
- ステップ2: 検索列(タイプ)の横にある列に、グループ化する新しいフィールドを追加します。この例では、ラベルと呼ばれる列を追加します カテゴリー .
- ステップ3: 別の値リスト(この例ではタイプ)の各値に対して、対応する値 カテゴリー 値。簡単な例では、 コンピュータ , カメラ または 携帯電話 に カテゴリー カラム。
- ステップ4: コピーする タイプ そして カテゴリー データテーブルをクリップボードにコピーします。
- ステップ5: PowerPivot for Excelの注文データを使用してExcelブックを開きます。 PowerPivotウィンドウを起動します。 貼り付けをクリックすると、新しいルックアップテーブルが表示されます。テーブルに名前を付け、あなたがチェックしていることを確認してください 最初の行を列ヘッダーとして使用する。クリック OK。 PowerPivotでルックアップテーブルを作成しました。
- ステップ6: 関係を作成する タイプ のフィールド 注文 テーブルと カテゴリー ルックアップテーブルのフィールド。クリックしてください 設計 リボンと選択 関係を作成する。選択を 関係を作成する ダイアログ 作成する.
PowerPivotデータに基づいてExcelでピボットテーブルを作成すると、新しい カテゴリー フィールド。 PowerPivot for Excelは内部結合のみをサポートしています。あなたが オーダータイプ ルックアップテーブルに存在しない場合、そのタイプの対応するレコードはすべて、PowerPivotデータに基づくピボットテーブルには存在しません。これは時々チェックする必要があります。
日付次元(ルックアップ)テーブル
ほとんどのPowerPivot for Excelプロジェクトでは、日付ルックアップテーブルが必要になります。ほとんどのデータセットには、いくつかのタイプの日付フィールドがあります。年と月を計算する関数があります。
ただし、実際の月のテキストまたは四半期が必要な場合は、複雑な数式を記述する必要があります。 Dateディメンション(ルックアップ)テーブルを組み込み、メインデータセットの月番号と照合する方がずっと簡単です。注文日付フィールドから月番号を表すために、注文テーブルに列を追加する必要があります。のDAX式 月 私たちの例では =月(受注日)。これは、レコードごとに1〜12の数値を返します。ディメンションテーブルには、月の数値にリンクする別の値が表示されます。これにより、分析に柔軟性がもたらされます。完全なサンプル日付ディメンション表は、次のとおりです。 表2 .
日付ディメンションまたはルックアップテーブルには12レコードが含まれます。月の列の値は1〜12です。他の列には、短縮月のテキスト、完全な月のテキスト、四半期などが含まれます。
- ステップ1: 下の表2の表をコピーして、PowerPivotに貼り付けます。 Excelでこのテーブルを作成することもできますが、時間を節約できます。 Internet ExplorerまたはEdgeを使用している場合は、下の選択したデータから直接貼り付けることができます。 PowerPivotはテストでテーブルの書式設定を取得します。別のブラウザを使用している場合は、最初にExcelに貼り付けてExcelからコピーして、表の書式設定を取得する必要があります。
- ステップ2: PowerPivot for Excelの注文データを使用してExcelブックを開きます。 PowerPivotウィンドウを起動します。クリック ペースト 下の表またはExcelからコピーしたルックアップテーブルが表示されます。テーブルに名前を付け、あなたがチェックしていることを確認してください 最初の行を列ヘッダーとして使用する。クリック OK。 PowerPivotで日付ルックアップテーブルを作成しました。
- ステップ3: 月 のフィールド 注文 テーブルと MonthNumber ルックアップテーブルのフィールド。クリックしてください 設計 リボンと選択 関係を作成する。選択を 関係を作成する ダイアログ 作成する.
再度、日付ディメンションを追加すると、日付ルックアップテーブルの異なる値のいずれかを使用して、ピボットテーブル内のデータをグループ化できます。 4分の1または月の名前でグループ化するのは簡単です。
サンプルディメンション(ルックアップ)テーブル
表1
タイプ | カテゴリー |
ネットブック | コンピューター |
デスクトップ | コンピューター |
モニター | コンピューター |
プロジェクター&スクリーン | コンピューター |
プリンタ、スキャナ、ファックス | コンピューター |
コンピュータセットアップとサービス | コンピューター |
コンピュータアクセサリー | コンピューター |
デジタルカメラ | カメラ |
デジタル一眼レフカメラ | カメラ |
フィルムカメラ | カメラ |
ビデオカメラ | カメラ |
カメラ&ビデオカメラアクセサリー | カメラ |
ホーム&オフィス電話 | 電話 |
タッチスクリーン携帯電話 | 電話 |
スマートフォンとPDA | 電話 |
表2
MonthNumber | MonthTextShort | MonthTextFull | 四半期 | 学期 |
1 | Jan | 1月 | Q1 | H1 |
2 | 2月 | 2月 | Q1 | H1 |
3 | 3月 | 行進 | Q1 | H1 |
4 | 4月 | 4月 | Q2 | H1 |
5 | 5月 | 5月 | Q2 | H1 |
6 | 6月 | 六月 | Q2 | H1 |
7 | 7月 | 7月 | Q3 | H2 |
8 | 8月 | 8月 | Q3 | H2 |
9 | 9月 | 9月 | Q3 | H2 |
10 | 10月 | 10月 | Q4 | H2 |
11 | 11月 | 11月 | Q4 | H2 |
12 | 12月 | 12月 | Q4 | H2 |