まずはデータモデルを作らないと始まらない
説明をするにあたって、何かモデルがないと分かりにくいです。
ただ、データモデルを作るのは非常に面倒くさい。
それに、ファイルをダウンロードしてもらうためには、ファイル共有サイトを用意しないといけないので、これも大変。
ならば、他の人がピボットテーブルの練習用に作成したサンプルデータでも借用しましょうかね…いやいや、そんなことしたら違う問題が出てきそうです。
今回のまとめ
今回は先にまとめを書いておきます。
サンプルとするテーブルは作るのが大変なので、どこかで調達、ないしは簡単なもので良いので作ってください
このマガジンではサンプルデータとして「販売店」、「商品」、「数量」および「販売価格」からなるテーブルを用意しましたので、これを使って説明します。
さらに、使用するメジャーは（ピボットテーブルで一度集計すると使用できる「暗黙のメジャー」である）「合計_/_数量」、「カウント_/ _商品」（※「_」は、実際は半角スペース）などを使います。
取り込んだテーブルに最初につく名前は取り込み方で違います。
実はフィルタやスライサーを考えなければ、ピボットテーブルでできる表はそこから数式に変換できます。
閑話休題。では、続きをどうぞ。
ならば、適当なものをでってあげよう
それならば、極めて雑にデータを自作すればいいんです。
そうですね…例えば、こんな感じ。
こんなデータなら簡単にできますし、誰でもできそうです。
なお、テーブルを作成したら、テーブル名を付けておくといいです。
「テーブル1」では使いにくいこともあるでしょうし。
（ただ、味気ないんですよね…これ。）
・・・ということで
サンプルデータを作ってみました。
基本的には上と同じ考え方で作っていますので、かなり雑なデータです。
日付データを入れるかどうか
あと、日付項目を入れるかどうかが問題なんですが、日付を入れるとBIツールのようなことができるので楽しいのですが、ちょっとだけ高度になりますので、自作のサンプルを作成される方は日付項目は入れなくていいです。
ただ、ここで使うサンプルについては、体裁が気になるので入れてあります。
テーブルをデータモデルに取り込む
テーブルを用意したら、これをPower Pivotのデータモデルに取り込みます。
データモデルの取り込み方は2パターン
データモデルにテーブルのデータを取り込む方法はいくつかありますが、大きく分けて次の２つのルートがあります。
Power Pivotのデータモデルに直接取り込む
Power Queryに送ってから、データモデルに取り込む
「Power Queryに送るって、手間だな」とも思うのですが、Power Queryを使うとデータの加工などができるので、そういう必要があるデータを使うときはPower Query経由で取り込むのも一つの手です。
お好きな方で取り込んでください。
取り込まれたテーブルの確認
取り込み処理が終わったら、Power Pivotのウィンドウを開いて、テーブルが取り込まれていることを確認しましょう。
取り込んだテーブルの名前を確認
取り込まれたテーブルはExcel本体のワークシート名がタブの形であるのと同様に、テーブル名の書かれたタブが表示されているはずです。
このテーブル名、取り込み方によってつく名前と修正のやりやすさが違います。
Power Pivotに直接取り込んだ場合は、取り込み元のワークシート上のテーブルの名前と同じものがつきます。名前の変更もPower Pivot上から可能です。
Power Query経由で取り込んだ場合は、Power Queryのクエリ名がテーブル名となります。Power Pivot上での変更はできず、クエリ名を変更することで新しいテーブルとして取り込むことが可能です。（本当は違うのかもしれませんが、こう思っておくとその後の作業し忘れを防止できます。）
取り込み方によっては、または作業が進んでからの修正はけっこう大変です。名前付けは慎重に。
ピボットテーブルを作ってみる
では、ものは試しに、このデータモデル上のテーブルを使ってワークシート上にピボットテーブルを作ってみましょう。
ピボットテーブルの作り方は2とおり
データモデルをソースとしたワークシート上にピボットテーブルを作る方法は2とおりあります。
Power Pivotウィンドウのホームリボンから「ピボットテーブル」ボタンを押す方法
Excel本体の「挿入」リボンにある「ピボットテーブル」ボタンのメニューから「デーらモデル…」を選択する。
バージョンによっては、ボタンを押すと分析するデータの選択のラジオボタンの１つに「このブックのデータモデルを使用する」という選択肢がありますので、それを選択しても可。
集計は普通のピボットテーブルと同じ
とりあえず「販売価格」フィールドの合計を計算してみましょう。
右に出てくるピボットテーブルペインの値フィールドに「販売価格」をドラッグして…できました！（…よね？）
では、ピボットテーブルペインの行フィールドに「販売店」をドラッグして販売店の構成要素ごとの合計を表示しましょう。
さらに、分類1のそれぞれの構成要素が幾つのレコードに含まれているか集計しましょう。
ピボットテーブルペインの値フィールドにある「販売価格」を右クリックして「プロパティ」を選択して、集計方法を「カウント」にします。
ここまでは、普通のピボットテーブルと全く同じですね。
実は…数式にはすぐできるんです。しかし…
今作ったピボットテーブルですが、これを数式にするのは実は簡単です。
「ピボットテーブル分析」リボンの「計算方法」グループの中に「OLAPツール」をクリックすると「数式に変換」という項目が出てきます。これを選択すると…なんと数式に置き換わります。
…「なんだ、これを使えばいいんだ」と思った方、ちょっと待ってください。
実はフィルタやスライサーが…
とりあえず、今作った数式化した元ピボットテーブルは残しておきます。
先ほどのピボットテーブルをもう一度作成して、今度はピボットテーブルペインのフィルタフィールドに「分類2」を追加して、フィルターをかけてみましょう。
フィルター、かかってますね？
そして、「OLAPツール」の「数式に変換」を実行すると…
あれ、フィルターの部分が残っています。集計結果の数値は…変換前と同じですね。
では数式は……「フィルター」なんて名前で指定できるんですね〜これ……って、それをどう書くかを知りたいのにこれじゃわからんって。
残念ながら、書き方を教えてくれません。
これは、スライサーでも同じことが起こります。
ということで、簡単には見逃してはくれないのでした。
おまけ：サンプルデータについて
サンプルデータの内容は、「ある店における2023年4月の果物の売り上げデータ」という体裁のデータとなっています。
データの内容は次のとおりです。
番号：1からの連番を振ってあります。
日付：「売上が発生した日付」という体裁でつけたデータです。
販売店：分類に使うデータです。東京23区に池袋、新宿、渋谷の3店舗、東京都多摩地区に立川、八王子の2店舗がある設定です。どこだか知りたい方は山手線と中央線で検索してください。
商品：分類に使うデータその2です。果実としてりんご、みかん、ぶどうの3種類、果実的野菜としてスイカとメロンの2種類の5品目です。
数量：集計データその1です。その名の通り、その日に売り上げた数量という設定です。
販売価格：集計データその2です。その商品のその日の売上高という設定です。単純に「設定単価に数量を掛けただけ」のお手軽作成です。
ちなみに、作り方は上の説明のとおりで、単純にRANDBETWEEN関数で乱数を作って、対応する名前に置き換えただけです。
この記事が気に入ったらサポートをしてみませんか？