Power Pivot Zero

• まずはPower Pivotで何ができるか？を見ていくために、Power Pivotを使わない場合、今どうやっているか？を見ていきます

  • 売上データ_2016_2017.xlsxを開きます

  • ここには2016年から2017年の売上データが、日ごとにあります

  • 実務でよくありますがSAPなどの基幹システムからダウンロードすると、平気でこうしたマスター番号だけがダウンロードされるってよくありますよね

  • でも、こんな数字見ても何かわかりません

  • このデータを集計して、どういった商品が売れているか？をピボットをかけたい

  • そのためには、Vlookupで情報を足していたと思います

  • 商品番号と商品マスターを使って、商品マスターより、商品名を持ってきます

  • これでピボットをかけて、商品名で売上を分析する。実務でよくある光景です

  • でも、同じくよくあるのがせっかくピボットを用意したと思ったら上司が色はどうなんだっけ？とか商品セグメントではどうだっけ？と聞いてくる

  • そうなると、もう一度売上データにVlookupでデータを持ってきてという作業が発生する

  • それに実務だと、その場で答えられないとなにか居心地が悪い

  • なので、事前に全部のデータを持ってくることに。ほとんど使わないデータのために容量を食い、処理も遅くなる

  • 今回の場合、商品マスターがまだ情報が少ない方。もっと多くなると実は細かい分析に興味があっても、もう一度データを成形することを考えるとやめてしまう。これは実務で多い

  • 気になっても調べない。分析ではなく単に表を作るだけになってしまう

  • それにこの方法は元データをいじるため、無意識にデータを変えてしまう危険性もある。元データが更新された時にはいちいちVlookupをし直さないといけない

  • こういったVlookup問題を解決してくれるのが、Power Pivotの１機能であるData Model

  • 次のレクチャーではPower PivotのData Modelを使って、Vlookupを使うことなく、商品マスターのどの項目でもストレスなくピボット分析をする方法をみていきます

• Power PivotのData Model機能を使って、売上データと商品マスターをひっつけます

  • このレクチャーでは、Power PivotのData Model機能を使って、Vlookupをすることなく、売上データとマスターデータをつなげます

  • もし、みなさんが今、ピボットテーブルを組む前に、マスターデータから項目をVlookupで持って来ているならば、このPower PivotのData Model機能はぜひ使ってみて欲しいと思います

  • すぐに役立つ即効性の高い機能です

  • 新しいエクセルをたちあげ、"data Model.xlsx"と名付けます

  • これからData Modelという機能を使います。ここからの一連の作業で、（元ファイルをみせながら）売上データと商品マスターを商品番号によってつなげていきます

    • もし、売上データにおいて前のレクチャーで列を追加していたら、削除してもとに戻しておいてください。ピボットテーブルも

  • データ / データの取得 / ファイルから / ブックから

  • つなげたいデータを選択する。まずは売上データ

  • ナビゲーター / 読み込み / 読み込み先

  • 接続の作成のみ / このデータをデータモデルに追加 をチェックし、OK

  • クエリの接続というウィンドウが開き、読み込まれたデータ名が表示される

  • ここで一時保存。ここで中断してもOK

  • 同じ要領で商品マスターを読み込む

  • ナビゲーター / 読み込み / 読み込み先

  • 接続の作成のみ / このデータをデータモデルに追加 をチェックし、OK

  • 今、データ自身は見えてませんが裏ではデータは取り込まれています

  • 次にこれらのデータを商品番号でひっつけます

  • データ / リレーションシップ によりリレーションシップの管理ウィンドウが開きます

  • 新規作成 / リレーションシップの作成ウィンドウが開く

  • ここでひっつけるデータを選択する。ここで注意なのがまずは必ずマスターテーブルじゃない方を先に選ぶ

  • 今回の場合だと売上データ。例えば、みなさんが実務で在庫情報と商品マスターをひっつける場合ならば、じゃない方は在庫情報

  • 売上データの商品番号を選択。エクセル2016だと、『データモデルのテーブル：』という表記はないかも

  • 商品マスターを選んだ途端に商品番号が出てくる。もし、みなさんが実務で行う時、項目名が一緒であれば自動で入ることが多い。でも、こっちは商品番号であっちはProduct Masterといった具合に項目名が違えばマニュアルで選択する必要がある

  • OKを押し、リレーションシップの管理ウィンドウも閉じる

  • もうこれでつながっています。ピボットテーブルを作ります

  • B2にセルを置いて、挿入 / ピボットテーブル

  • このブックのデータモデルを使用する をチェックし、OK

  • 従来のデータを選択することもなく、ピボットテーブルができる

  • クエリと接続ウィンドウはいらないので閉じる

  • ピボットテーブルのフィールドの見え方が違うかも。これは単なる好み

  • 項目を見ると、Vlookupもしていないのに、商品マスター内の全ての項目が選べる

  • まずは売上データで受注数量を値に、右クリックで表示形式 / 数値 / 桁区切り

  • もうこれで商品マスターのどの項目を使ってもピボットテーブルで分析ができる

  • いかがでしょうか？これまでのVlookupでデータを用意するのに比べて格段に早いです

  • しかもデータ量を見ると、売上データよりも小さい

  • 売上データも、ここにVlookupでデータを足していけばかなり大容量になり、その上でピボットテーブルを組むと動作も遅くなります

  • そうした心配もPower PivotのData Model機能にはありません

  • このPower PivotのData Model機能、すぐに役立つ機能ですので、ぜひ使ってみていただきたいと思います

  • 私もはじめてやった時は「おお～」となりました

  • 以上、Power PivotのData Model機能を使って、Vlookupをすることなく、売上データとマスターデータをつなげる方法でした

• ここでは、ピボットテーブルを組む前に行っていたデータ加工を簡単にしてくれるPower Pivotの機能の一つ。Power Query Editorを見ていきます

  • まずは従来はどういうやり方をしていたか？を見ていきます

  • 売上データを見てみます。ここには、受注数量と単価はありますが売上がありません

  • 通常ですと元データに一列足します

  • 次に地域マスターを見てみます

  • 地域マスターには余計なワークシートがあったり、不要な行列があります

  • これも分析前に削除をしてVlookup集計しやすく加工していたと思います

  • 再度、売上データを見てください

  • 2017年までのデータがありますが、新たに2018年のデータが出てきたらどうするか？

  • または、今、2016年と2017年が同じファイルがありますが、各年度がバラバラだったらどうするか？

  • 一つのファイルにまとめる必要が出てきます

  • そうするとせっかく先ほど用意した売上の列をやり直す必要がでてきます

  • こうした加工をまるっと引き受けてくれるのがPower Query Editorです

  • Editorというくらいなので、データの編集を行ってくれます

  • Power Query Editorは、こうした加工をドラッグ＆ドロップを中心に行います

  • ですので、VBAのようにデータの加工が自動でできたとしても、習得に恐ろしく時間がかかることはありません

  • また、エクセルでがんばって加工しようとすれば、速度を上げるために、やれショートカットを覚えなさい、とかマウスを使うなとか余計なことを言われます

  • Power Query Editorはそういったメンドクサイことはありません

  • 次のレクチャーからさっそくPower Query Editorを使っていきましょう

• このレクチャーでは、Power Query Editorによって単価 x 数量をして売上を出してみます

  • 新しくエクセルを立ち上げてやってみましょう。Power Query Editorという名前にしてみます

  • 売上データを取り込みます

  • 先ほどは、閉じて読み込みとしましたが、ここでデータの変換とします

  • Power Query Editorを開きます。ここでデータの加工ができます

  • 最初はとっつきにくいと思います。でもざっくり言うと、列の変換、列の追加ができます

  • その他の処理はこちらのホームで行います

  • まずは使ってみましょう

  • やりたいのは、受注数量と、売価円単価を掛けた一列を加えることです

  • 列の追加 / カスタム列 をクリック

  • 新しい列名を「売上」とし、受注数量 x 売価円単価をします

  • データ型を整数にします

  • データ型とは、その列にどんなデータが入っているか？を示すものです

  • 例えば、販売日は日付データです。あとは数字ですね

  • これでやりたい加工は済みました

  • ホーム / 閉じて読み込む

  • ピボットテーブルを開きます

  • ピボットテーブルに選べる項目を見ると「売上」が追加されています。ピボットテーブルで選んでみます

  • 右クリック / 表示形式 / 数字 / 桁区切りをします

  • Ctl + Sで上書き保存をしておきましょう

  • 以上、ここではPower Query Editorによる列の追加を見ていきました

  • 次のレクチャーでは、Power Query Editorによる行の削除をします

• ここでは元データに余計な行列があった場合の加工を見てみます

  • 前のレクチャーのエクセルを続けて使います

  • 地域マスターを見てみます

  • ワークシートが２つあり、１つは丸ごと余計なデータです

  • また、必要なデータが入っているワークシートも余計な行列が入っています

  • なお、地域マスターの地域番号、これは、売上データの地域番号に対応します

  • こちらを読み込んで、Power Query Editorにより加工をしてみます

  • データ / データの取得 / ファイルから / ブックから

  • 地域マスターを選び、データの変換

  • １行目を削除します

  • ホーム / 行の削除 / 上位の行の削除

  • １行削除します

  • １行目をヘッダーとして使用

    • これが一つのボタンとしてあるのはそれだけよくあるケースだから

  • ちなみにデータ型を見ると、国名、地域はテキストになっている。よく使うのはこのテキストと数字と日付。選択肢は多いけれど使うのは少ない。またPower Query Editorが自動で判断してくれ、それは概ね正しい

  • ホーム / 閉じて読み込む / 閉じて次に読み込む

  • 接続の作成のみ / データモデルに接続にチェック

  • ピボットテーブルのフィールドで、すべてを見てみる

  • 地域マスターがあるので、国名を入れてても、おかしな値がでる

  • これはData Modelによってつながっていないから

  • 前のレクチャーの要領でリレーションシップを組みます

  • 売上データと地域マスターは地域番号でつながっていました

  • データ / リレーションシップ

  • 新規作成

  • 最初は、マスターテーブルじゃない方。なので、売上データ。地域番号を選択

  • 地域マスターを選択すると自動的に地域番号が出てくる

  • OKと押した途端にピボットテーブルに正しい値が出てきます

  • ここでは、習ったことを２つ使いました

  • 元データの加工を行った上で、Data Modelによってリレーションシップを組みました

  • こういったケースは実務でよくあると思います

  • また、ここで強調したいのは売上列の追加も、地域マスターの加工も、どちらもPower Query Editor上での加工であり、元データは一切触っていません

  • ですので、間違って元データを壊してしまう心配もありません

  • 以上、Power Query Editorによるデータの加工でした

• Power Query Editorによってバラバラに格納されている売上データを一つにまとめてます

  • 前のレクチャーで作ったファイルは閉じて、新しいエクセルファイルを再度立ち上げます

  • 名前はadd.xlsxとします

  • このレクチャーのダウンロードには、売上データの2016年～2018年分が年度毎に格納されていますのでダウンロードしてください

  • 今回やりたいのはこれらのデータを１つにまとめてピボットテーブルを作成することです

  • 実務でもよくあるケースだと思います

  • 普通は、いずれかのファイルにコピペでまとめます

  • その上でVlookupによって項目を追加していきます

  • これをPower PivotのPower Query Editorでやってみます。どうなるでしょうか？さっそくはじめます

  • （売上データ2016～2018は開いたまま）

  • エクセルを開きます

  • データ / データの取得 / ファイルから / ブックから / データの変換でまずは売上データ2016を読み込みます

    • エラーが出る。これは元ファイルが開いているから。注意を促す

  • 今までは、エクセルからデータ / データの取得 / ファイルから / ブックからという手順を踏みました。今回はPower Query Editor上でデータの取り込みをします

  • Power Query Editorが開くので、このままさらにデータの読み込みを続けます

  • ホーム / 新しいソース /  ファイル / エクセルで売上データ2017を読み込みます。その後同じ要領で2018を読み込みます

  • クエリウィンドウを見てみます。2016, 2017, 2018と年度は違えど３つのクエリ / テーブルともに同じ形式です

  • つまり地域番号、商品番号、販売日、受注数量、売価（円単価）といった項目があります

  • こうした時、Power Query Editorによって簡単にこれらを１つにまとめることができます

  • ホーム / クエリの追加 / クエリの追加

  • ちなみに、クエリを新規クエリとして追加とは、新たに１つまとめテーブルを立ち上げる、ということです

  • ここでは2016年に2017年と2018年をひっつけます

    • まず確認として2016年のクエリを選択し、販売日のフィルターを見てみます。2016年しかありません

  • 2016年のクエリを選択した上で、ホーム / クエリの追加 / ３つ以上のテーブルにチェック

  • 2017年と2018年をダブルクリックして追加。OK

  • 一見なにも変わってないように見えますが、販売日でフィルターをかけようとすると、2018年までデータがあります

  • さて、やりたいことはできたのですが、クエリ名が2016年のままだと実体を表しません

  • このクエリ名は簡単に変えられます。クエリの設定のプロパティでクエリ名を変えます

  • 売上データ_2016～2018とします

    • ちなみに、じゃあ2017と2018は削除してもいいか？と思うが削除できない。まとめる際につかっているから

  • これでやりたいことはできたので、ホーム / 閉じて読み込む / 閉じて次に読み込む

  • 接続の作成のみ / このデータをデータモデルに追加するにチェックして、OK

  • ピボットテーブルを作ってみます

    • 今回はカッコよくショートカットを使ってみましょう。ALT + N + V

  • 挿入 / ピボットテーブルの作成 / このブックのデータモデルを使用する、にチェックしてOK

  • 売上データ2016~2018の受注数量を見てみる

  • ピボットテーブルには2016年～2018年の３年分のデータが入っている

    • クエリの名前を変えたのは、ピボットテーブルのフィールドに表示されるから。分かりやすい名前にしておくとよい

  • ここではやらないが、もちろんData Model機能で、リレーションシップを組むことで、マスターデータとも簡単につなげることができ、マスターデータの項目でも分析ができる

  • このようにPower Query Editorなら簡単にばらばらのデータをまとめることができます

  • もちろんこれまでやったデータモデル機能によって、Vlooupをすることなく情報を足すことができます

  • 実務でよくあるのが、同じフォーマットで、支店がワークシート別であったり、支店ごとにエクセルが送られてくるケース

  • ピボット分析のためにイチイチ一つのファイルにまとめるのは大変だし、ミスのもと。たまに全部支店を入れないことも

  • Power Query Editorならミスも防げるし、なにしろ簡単

  • ぜひ使ってみて欲しい

  • 以上、ここではバラバラであった売上データをPower Query Editorにより１つにまとめました

  • このセクションでは、ピボットを組む前の下準備を、Power Query Editorによってどう簡単にできるか？を見てきました

  • 次のセクションでは、もし、データの更新があった場合、新規のデータファイルを入手した場合の対処法を見ていきます

  • また一から加工をやり直さなくてはならないのでしょうか？一緒にみていきましょう

• もし、既に読み込みをしているファイルに対して、新たに情報の追加があった場合の対処方法をみてみます

  • 前に作ったData Model.xlsxを開いてみます

  • このファイルが接続しているのは、売上データファイルです

  • このファイルを開いてみます

  • データは2016年と2017年だけのものです

  • なので、データは2016年から2017年までのものしかありません

  • ここで2018年のデータを入手したとして、これが元データに追加されたというケースを想定してみます

  • 元データに2018年のデータをはりつけます

    • 足した部分の最初にマーカーをつけておきます

  • これだけだと、ピボットを見ても2018年のデータは反映されません

  • もし既存データに更新があった場合、データ / すべて更新をクリックします

  • こうすることで元データに追加された2018年のデータも無事ピボットテーブルに反映されました

  • 売上データのように日々更新されるようなデータであれば、つないだファイルが頻繁に更新されることが多々あります

  • その場合でも、これまでやったようにイチイチファイルを取り込んで、加工をして、リレーションシップを組んで、といった作業は不要です。更新をするだけです

  • 以上、既に読み込みしているファイルを更新する方法でした

• もし、取り込んだファイルとは別のファイルを読み込む必要が出た場合の対処を説明します

  • 引き続きData Model.xlsxを開いてみます

  • 今は、2016年～2018年のデータが入っています

  • 今、読み込んでいるのは売上データ2016_2017です。もとは2016年と2017年のデータしかなかったところに2018年を足したわけです。

  • この状態で売上数量のスクリーンショットを撮っておきます

  • ここで一旦もとの状態に戻します。元ファイルから2018年データを削除し、更新することで元に戻します

  • 例えばここで、読み込みファイル自体を変えるケースを想定してみます

  • 実務ではデータの更新があった時、元ファイルとは別のファイルが用意されることがあります

  • Dailyで排出されるファイルで、ファイル名が日々違います

  • こうした時でも、Power Pivotならこれまで行ってきた加工をそのまま使いつつ、元データの切り替えができます

  • ここでは元データを売上データ2016_2017から、売上データ2016_2017_2018に切り替えます

    • このレクチャーのダウンロードにある売上データ2016_2017_2018.xlsxをダウンロードしてください

  • このファイルは最初から2016年から2018年のデータが一緒になったものです

  • ですので、こうして切り替えた結果も、元データに2018年を足したものと一緒になるはずです

  • Power Query Editorを開きます

  • データ / クエリと接続をクリックします

  • どのクエリでもいいのでダブルクリックしてPower Query Editorを開きます

  • ホーム / データソース設定をクリックします

  • 切り替えたいデータを選択します。今回は売上データです

  • ソースの変更をクリック。新しいソース元のアドレスを指定します。今回は売上データ2016_2017_2018です

  • OKを押して閉じます。ホーム / 閉じて読み込む

  • このように簡単にソース元を変えることができます

  • しかも、これまで設定したリレーションシップもそのままです

    • 商品マスターの項目でピボットを組んでみます

  • これまでの加工はそのままでデータのみが新しくなったイメージです

  • Power Query Editorやデータモデルがすごいのが、こうした加工は記録されているため、実務では毎日のようにある、データの更新に対していちいち最初からやり直さなくて済む、ということです

  • 以上、参照元ファイルの切り替え方法でした

• 知識だけでなく、スキルを定着させるために課題にチャレンジしてもらいます

  • 内容はこれまでこのコースでやってきたことの復習です

  • ぜひ一度やってみていただきたい。分かったつもりでも「あれ？」ってなることがある。私もそうでした

  • チャレンジしていただき、のちに回答解説を動画で行っていますので、答え合わせをしていただきたい

  • Practiceというフォルダを用意します。ここに下記のデータを入れます

    • 売上データの2016, 2017, 2018

    • 商品マスター

    • 地域マスター

  • このPracticeの直下に新規ファイルのPractice.xlsxを作成

  • その上で、これらのファイルをまとめて、アメリカの商品セグメント別の売上を示すピボットテーブルを用意してみて欲しい

  • お気づきになるかもしれませんが、これはデモで行ったことと同じ加工です

  • いくつかヒントを出します

    • 大きく分けて２つのことをPower Pivotで行います。Data Modelによるリレーションシップと、Power Query Editorによるデータ加工です

    • 最初に、Power Query Editorにより加工をし、その後にリレーションシップを組みます

    • Power Query Editorに読み込む際、１つづつ読み込むよりは、連続して読み込んだ方がいいのです

      • Power Query Editor上で、ホーム  / 新しいソースを使います

    • 売上データについて

      • 元データには売上の１列がありません。課題は売上を示したいです

      • また、売上データは年度毎にバラバラになっています

      • ですので、バラバラのデータを１つにまとめる加工と売上の１列を加えるという２つのことをします。順序は１つにまとめる方が先です

      • バラバラであるために、クエリの追加処理によりテーブルを１つにする必要があります

      • 商品マスター、地域マスターとリレーションシップを組むのは、クエリの追加処理により2016年から2018年までをマージしたクエリに対してです

      • まとめたファイルは、分かるようにクエリの名前を変えておくと良いでしょう

      • 欲しいのは売上です。列の追加、カスタム列により売上を示す１列を足す必要があります

        • データ型の変更を忘れず

    • 地域マスターについて

      • 不要なワークシートがあります

      • 行を削除するだけではなく、地域番号、地域、国名の行をヘッダーとして認識させる必要があります。不要な行を削除しただけだと、ヘッダーにはなりません

        • １行目をヘッダーにする、というそのまんまのボタンがあります

    • データの読み込み時に要注意

      • ホーム / 閉じて読み込む / 閉じて次に読み込む

      • 接続の作成のみ / データモデルに接続にチェック

    • リレーションを組むのは、データ / リレーションシップ

      • 最初に選択するのは、マスターデータではない方

    • ピボット作成時の注意

      • B2にセルを置いて、挿入 / ピボットテーブル

      • このブックのデータモデルを使用する をチェックし、OK

  • 次のレクチャーにはここで紹介した内容をテキストでまとめています。課題を行う際のヒントにしていただければと思います

  • それでは課題にチャレンジしていただいて、回答解説のレクチャーでまたお会いしましょう

課題のヒントをテキストにて示します

• 課題の解説をします

  • Practice.xlsxという新しいエクセルファイルを作成します

  • データを全て取り込みます。データ取り込みは、データ / データの取得 / ファイルから / ブックから

    • ナビゲーターが開きますので、ワークシートを選択し、データの変換をクリック

    • そうするとPower Query Editorが開きます

    • 他のファイルも読み込みます。ホーム / 新しいソース / ファイル / エクセル

    • ５つのファイルが読み込まれたことを確認

      • 地域マスターの不要ワークシートはこの段階で取り込まない

  • 地域マスターを加工します

    • クエリウィンドウで、地域マスターをクリック

    • ホーム / 行の削除 / 上位の行の削除 / 1と入れてOK

    • １行目をヘッダーとして使用

  • 売上データを１つにまとめます

    • データを一つにまとめるのは、列の変換でも、列の追加でもありません。それ以外はホームにあります

    • 売上データ_2016をクリックし、ホーム / クエリの追加

      • 響きから「クエリのマージ」と間違えやすいので注意

    • 3つ以上のテーブルをクリックし、2017と2018を選択

    • クエリの名前を売上データ2016～2018にする

      • フィルターにより2018年までのデータがある旨を確認

    • クエリ名を変えます

  • 売上データ2016～2018に売上の１列を足します

    • 売上データ2016～2018をクリック

    • 列の追加 / カスタム列

    • 列名を売上とし、単価 x 数量を行う。ここではわざとカスタムのまま残す

    • もし列名を入れ忘れても後から簡単に変えられる（わざと間違えてやってみる）

    • データ型を変えておく

  • 閉じて次に読み込み

    • ホーム / 閉じて読み込む / 閉じて次に読み込む

    • 接続の作成のみ / このデータをデータモデルに追加

  • リレーションシップの作成

    • データ / リレーションシップ / 新規作成

      • マスターデータじゃない方が先

      • 売上データ2016～2018を作成することに注意

      • 商品マスター、地域マスターと商品番号、地域番号でリレーションシップを組む

  • ピボットテーブルの作成

    • セルB2にカーソルを置き、挿入 / ピボットテーブル

    • このブックのデータモデルを使用する をチェックしOK

    • 行に国名、と商品セグメントを入れる

    • 値に売上を入れる

      • 右クリック / 表示形式 / 数値 / 桁区切りをしておく

    • これで完成

  • いかがでしょうか？実際にやってみると「あれ？」ってなったんではないでしょうか？

  • 一度でできなくても、回答をみつつやってみて、またやり直してみることがおススメです

  • なんどか練習してプロセスを覚えてしまえば、実務でも使える。知っているだけではなかなかイザ実戦投入っていう時に手がとまってしまいます

  • 以上、課題の回答解説でした

お疲れ様でした。これでこのコースのレクチャーは全て終了です。このコースが、みなさんがPower Pivotを使うきっかけになれば、こんなにうれしいことはないです。もっとPower Pivotを学習したい！というみなさまのために、さらに詳しいコースをご用意しています。次のレクチャーにまとめていますのでぜひご覧ください。

これまでご受講いただきありがとうございました！