内容
Excel でピボット テーブルを作成する場合、最初のダイアログ ボックスで、最初の範囲を設定し、ピボット テーブルを挿入する場所を選択するように求められますが、目立たないが非常に重要なチェックボックスが下にあります – このデータをデータモデルに追加します (このデータを追加 データモデルへ) そして、少し高いところにあるスイッチ この本のデータモデルを使用する (このワークブックのデータモデルを使用):
残念ながら、長い間ピボット テーブルに慣れ親しんでおり、仕事でうまく使用している多くのユーザーは、これらのオプションの意味をよく理解せず、まったく使用しないことがあります。 そして無駄に。 結局、データ モデルのピボット テーブルを作成すると、従来の Excel ピボット テーブルと比較して、いくつかの非常に重要な利点が得られます。
ただし、これらの「パン」を間近で検討する前に、まず、このデータ モデルが実際に何であるかを理解しましょう。
データモデルとは
データ・モデル (MD または DM = Data Model と省略されます) は、表形式のデータ (必要に応じて相互にリンクされた XNUMX つ以上のテーブル) を格納できる Excel ファイル内の特別な領域です。 実際、これは Excel ワークブック内に埋め込まれた小さなデータベース (OLAP キューブ) です。 Excel 自体のシート上の通常の (またはスマートな) テーブルの形式でのデータの従来のストレージと比較して、データ モデルにはいくつかの重要な利点があります。
- テーブルは最大 2億行、Excel シートは 1 万を少し超えるサイズに収まります。
- 巨大なサイズにもかかわらず、そのようなテーブルの処理 (フィルタリング、並べ替え、それらの計算、建物の概要など) が実行されます。 とても早い Excel自体よりもはるかに高速です。
- モデル内のデータを使用して、次を使用して追加の (必要に応じて非常に複雑な) 計算を実行できます。 組み込みの DAX 言語.
- データ モデルにロードされるすべての情報は非常に重要です。 強く圧縮 特別なビルトイン アーカイバを使用すると、元の Excel ファイルのサイズが適度に増加します。
モデルは、Microsoft Excel に組み込まれた特別なアドインによって管理および計算されます – パワーピボットそれについてはすでに書いています。 有効にするには、タブで 開発者 クリック COM アドイン (開発者 — COM アドイン) 適切なボックスにチェックを入れます。
タブの場合 開発者 （デベロッパー）リボンには表示されませんが、オンにすることができます ファイル – オプション – リボン設定 (ファイル - オプション - リボンのカスタマイズ). 上記の COM アドインのリストにあるウィンドウに Power Pivot がない場合、お使いのバージョンの Microsoft Office には含まれていません🙁
表示される [Power Pivot] タブには、大きな薄緑色のボタンがあります。 マネジメント （管理）をクリックすると、Excel の上に PowerPivot ウィンドウが開き、現在の本のデータ モデルの内容が表示されます。
途中で重要な注意事項: Excel ワークブックには XNUMX つのデータ モデルのみを含めることができます。
テーブルをデータ モデルにロードする
モデルにデータをロードするには、まずテーブルを動的な「スマート」キーボード ショートカットに変更します。 Ctrlキー+T タブでわかりやすい名前を付けます コンストラクタ （設計）. これは必須のステップです。
次に、次の XNUMX つの方法のいずれかを使用して選択できます。
- ボタンを押します モデルに追加 (データモデルに追加) タブ パワーピボット タブ ホーム （ホーム）.
- チームの選択 挿入 – ピボットテーブル (挿入 — ピボット テーブル) チェックボックスをオンにします このデータをデータモデルに追加します (このデータをデータモデルに追加). この場合、モデルにロードされたデータに従って、ピボット テーブルもすぐに構築されます。
- [詳細設定]タブ 且つ （日にち） ボタンをクリック テーブル/範囲から (テーブル/範囲から)テーブルを Power Query エディターに読み込みます。 このパスは最長ですが、必要に応じて、Power Query が非常に強力な追加のデータ クリーニング、編集、およびあらゆる種類の変換を実行できます。
次に、コーミングされたデータがコマンドによってモデルにアップロードされます ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…). 開いたウィンドウで、オプションを選択します 接続を作成するだけ (接続のみ作成) そして、最も重要なことに、チェックマークを付けます このデータをデータモデルに追加します (このデータをデータモデルに追加).
データモデルの概要を構築します
要約データ モデルを構築するには、次の XNUMX つの方法のいずれかを使用できます。
- ボタンを押す 要約表 （ピボットテーブル） PowerPivot ウィンドウで。
- Excel でコマンドを選択する 挿入 – ピボットテーブル モードに切り替えます この本のデータモデルを使用する (挿入 — ピボット テーブル — このワークブックのデータ モデルを使用).
- チームの選択 挿入 – ピボットテーブル (挿入 — ピボット テーブル) チェックボックスをオンにします このデータをデータモデルに追加します (このデータをデータモデルに追加). 現在の「スマート」テーブルがモデルにロードされ、モデル全体の要約テーブルが構築されます。
データをデータ モデルにロードして要約を作成する方法がわかったので、これによって得られるメリットと利点を調べてみましょう。
利点 1: 数式を使用しないテーブル間のリレーションシップ
通常の集計は、XNUMX つのソース テーブルのデータを使用してのみ作成できます。 販売、価格表、顧客名簿、契約記録など、それらが複数ある場合は、まず VLOOKUP などの関数を使用して、すべてのテーブルからデータを XNUMX つに収集する必要があります。 （VLOOKUP）、 索引 （索引）、より露出 （マッチ）、SUMMESLIMN （スミフス） など。 これは長くて退屈で、大量のデータを使って Excel を「思考」に駆り立てます。
データ モデルの要約の場合は、すべてがはるかに単純です。 Power Pivot ウィンドウでテーブル間のリレーションシップを XNUMX 回設定するだけで十分です。 これを行うには、タブで パワーピボット ボタンを押す マネジメント （管理） 次に、表示されるウィンドウで – ボタン チャートビュー (ダイアグラムビュー). テーブル間で共通の (キー) 列名 (フィールド) をドラッグしてリンクを作成する必要があります。
その後、データ モデルの集計で、集計領域 (行、列、フィルター、値) に関連するテーブルの任意のフィールドを挿入できます。すべてがリンクされ、自動的に計算されます。
利点 2: 一意の値をカウントする
通常のピボット テーブルでは、いくつかの組み込みの計算関数 (合計、平均、カウント、最小、最大など) のいずれかを選択できます。データ モデルの概要では、この標準リストに非常に便利な関数が追加され、一意の (繰り返しのない値) の数。 たとえば、その助けを借りて、各都市で販売されているユニークな商品 (範囲) の数を簡単に数えることができます。
フィールドを右クリック – コマンド 値フィールドのオプション そしてタブで 操作 選択する 異なる要素の数 (個別カウント):
利点 3: カスタム DAX 数式
ピボット テーブルでさまざまな追加計算を実行する必要がある場合があります。 通常の集計では、これは計算フィールドとオブジェクトを使用して行われますが、データ モデルの集計では、特別な DAX 言語 (DAX = Data Analysis Expressions) でメジャーが使用されます。
メジャーを作成するには、タブで選択します パワーピボット Command メジャー – メジャーの作成 (メジャー — 新しいメジャー) または、[ピボット フィールド] リストでテーブルを右クリックして、 メジャーを追加 (対策追加) コンテキストメニュー：
開いたウィンドウで、次のように設定します。
- テーブル名作成されたメジャーが格納される場所。
- 小節名 – 新しいフィールドのわかりやすい名前。
- 説明 –オプション。
- 式 – 最も重要なことは、ここでは手動で入力するか、ボタンをクリックするためです fx リストからDAX関数を選択します。これにより、メジャーを値領域に投入したときに結果が計算されます。
- ウィンドウの下部では、リスト内のメジャーの数値形式をすぐに設定できます カテゴリー.
DAX 言語は、個々の値ではなく、列とテーブル全体で動作するため、常に理解しやすいとは限りません。つまり、従来の Excel 式の後で、考え方を再構築する必要があります。 ただし、大量のデータを処理する能力を過大評価するのは難しいため、それだけの価値があります。
利点 4: カスタム フィールド階層
多くの場合、標準レポートを作成するときは、フィールドの同じ組み合わせを特定の順序でピボット テーブルに投入する必要があります。たとえば、次のようになります。 年-四半期-月-日または カテゴリ-製品または 国-都市-クライアント など。データ モデルの概要では、この問題は独自のモデルを作成することで簡単に解決できます。 階層 — カスタム フィールド セット。
Power Pivot ウィンドウで、ボタンを使用してグラフ モードに切り替えます。 チャートビュー タブ ホーム (ホーム — ダイアグラム ビュー)、で選択 Ctrlキー 必要なフィールドを右クリックします。 コンテキストメニューにはコマンドが含まれます 階層を作成 (階層を作成):
作成された階層の名前を変更し、必要なフィールドをマウスでドラッグして、後で XNUMX 回の操作でそれらを要約に入れることができます。
メリット 5: カスタム ステンシル
前の段落の考え方を続けて、データ モデルの概要で、各フィールドに独自の要素セットを作成することもできます。 たとえば、都市のリスト全体から、自分の担当地域にある都市だけのセットを簡単に作成できます。 または、顧客、商品などだけを特別なセットに集めます。
これを行うには、タブで ピボットテーブル分析 ドロップダウンリストで フィールド、アイテム、およびセット 対応するコマンドがあります (分析 — フィールド、Iアイテムとセット — 行/列アイテムに基づいてセットを作成します):
開いたウィンドウで、要素の位置を選択的に削除、追加、または変更し、結果のセットを新しい名前で保存できます。
作成されたすべてのセットは、ピボットテーブル フィールド パネルの別のフォルダーに表示され、そこから新しいピボットテーブルの行と列の領域に自由にドラッグできます。
利点 6: テーブルと列を選択的に非表示にする
これは小さいですが、場合によっては非常に快適な利点です。 フィールド名または PowerPivot ウィンドウのテーブル タブを右クリックすると、コマンドを選択できます。 クライアント ツールキットから隠す (クライアント ツールから非表示):
非表示の列またはテーブルは、ピボットテーブル フィールド リスト ペインから消えます。 ユーザーからいくつかの補助列 (たとえば、リレーションシップを作成するためのキー値を持つ計算列または列) またはテーブル全体を非表示にする必要がある場合に非常に便利です。
メリット 7. 高度なドリルダウン
通常のピボット テーブルの値領域の任意のセルをダブルクリックすると、Excel では、このセルの計算に関与したソース データ フラグメントのコピーが別のシートに表示されます。これは非常に便利な機能で、正式にはドリルダウンと呼ばれます (通常は「失敗」と言います)。
データ モデルの概要では、この便利なツールがより微妙に機能します。 興味のある結果が表示されたセルの上に立つと、その横に虫眼鏡が表示されたアイコンをクリックできます（ エクスプレス トレンド) をクリックし、関連するテーブルで関心のあるフィールドを選択します。
その後、現在の値 (モデル = エクスプローラー) がフィルター領域に入り、集計がオフィスによって作成されます。
もちろん、このような手順を何度も繰り返して、興味のある方向に一貫してデータを掘り下げることができます。
利点 8: ピボットをキューブ関数に変換する
データ モデルの概要で任意のセルを選択し、タブで選択した場合 ピボットテーブル分析 Command OLAP ツール – 数式に変換 (分析 — OLAP ツール — 数式に変換)、その後、要約全体が自動的に数式に変換されます。 行と列の領域のフィールド値と値の領域の結果は、特別なキューブ関数 CUBEVALUE と CUBEMEMBER を使用してデータ モデルから取得されます。
技術的には、これは要約ではなく、数式を含む複数のセルを扱っていることを意味します。つまり、要約では利用できないレポートの変換を簡単に行うことができます。たとえば、新しい行または列を中央に挿入します。レポートの、要約内で追加の計算を行い、必要な方法でそれらを配置するなど。
同時に、もちろんソース データとの接続は維持され、将来、ソースが変更されたときにこれらの式が更新されます。 美しさ！
- Power Pivot と Power Query を使用したピボット テーブルでの計画と事実の分析
- 複数行のヘッダーを持つピボット テーブル
- Power Pivot を使用して Excel でデータベースを作成する