ピボット姫と学ぼう！（11）完結編 パワークエリにも「呪い」が？？
ピボット姫と学ぼう！も、今回が最終回です。
ピボットテーブルが罹っていた「呪い」は解けました。
しかし、ピボット姫がみんなから嫌われる「呪い」は、未だ解けません。
今回は「パワークエリ」がテーマです。
サンプルのダウンロードはこちらから！
この記事を読んで、良く判らない！もっと知りたい方には、
毎月５名様 先着で ＺＯＯＭ無料相談３０分 受付中です！
1．パワークエリに「呪い」が！！
表計算の魔女とＡＣＣＥＳＳの魔女が「呪い」を掛けた！
ピボット姫に、ピボットテーブルの呪いを解かれた表計算の魔女は、
あわてて、姉のＡＣＣＥＳＳの魔女に相談しました！
みんながピボットテーブルに続いて、パワークエリも使うようになると、
表計算も、ＡＣＣＥＳＳも使われない・・ということを恐れたからです。
そこで、魔女たちは、パワークエリに恐ろしい「呪い」を掛けました。
Ｅｘｃｅｌ国では、あまり使っていませんが、ＢＩ国では、パワークエリをみんなが使っています。突然、パワークエリに呪いが罹ったのです。
パワークエリ王子は、「みんなから嫌われる呪い」に罹りました。
孤独に苦しんで悩む毎日が続きます。気疲れで、寝込んでしまいました。
すると、夢の中に、Ｅｘｃｅｌ王妃が現れました。
「私はＥｘｃｅｌ王妃です。パワークエリ王子、大丈夫ですよ！
私の娘ピボット姫は、ピボットテーブルの呪いを解きました。
二人で協力すれば、パワークエリの呪いも解けるはずです。
ピボット姫に会いに旅立ちなさい！」
パワークエリ王子はピボット姫を探す旅に！
パワークエリ王子は、直ぐにＢＩ国を出発しました。
孤独で過酷な旅を続けて、Ｅｘｃｅｌ国にたどり着きました。
しかし、長旅の疲れと空腹で気を失ってしまいました。
ラビットが、道端で倒れている旅人を見つけました。
手に持っている紙に「ピボット姫」と書いてあったので、
こっそりと、お城の中に連れてきました。
パワークエリ王子は、食事を摂り、元気を取り戻しました。
ラビットは、ピボット姫を呼びに行きます・・。
最終回は、二人が協力して、パワークエリの呪いと、
みんなから嫌われる呪いに挑戦します！
２．メニューの呪い！
パワークエリ王子とピボット姫の二人が会ったとたん！！
「みんなから嫌われる呪い」に罹っている二人ですが、
呪いを超える「愛」が芽生えたようです。
パワークエリ王子は、ピボット姫から、これまでの苦労と、
ピボットテーブルの「呪い」を解いた話を聴きました。
聡明な頭脳と、みんなのことを思うピボット姫に惹かれていきます。
パワークエリは、Ｅｘｃｅｌに組み込まれてるツールですが、
Ｅｘｃｅｌの外でPower Query エディター という「専用メニュー」で
マウスを操作して、データをＥｘｃｅｌに取り込みます。
ＶＢＡマクロを使わなくても、マウスの設定で手順が登録でき、データの取得・変換が可能で、繰り返し更新が可能になっています。
パワークエリは、Ｅｘｃｅｌのツールではないので、Ｅｘｃｅｌの知識では対応できません。そこは、ピボットテーブルとも似ているところです。
パワークエリのメニューＰｏｗｅｒＱｕｅｒｙエディターで、５つのリボンがあります。さらに、沢山のメニューがあります。
ピボットテーブルと同じで、「メニューの使い方」が判らないことが、
「パワークエリの呪い」の原因だと、二人は気づきました。
パワークエリを使ってみると「困ること」が出てきます！！
「呪い」 更新処理に時間が掛かり、メモリがパンクするのです。
①更新の処理に時間がかかる
②更新すると列幅が元に戻る
③パソコンのメモリがパンクする
これは、ピボットテーブルの「初期設定」の呪いに似た状況です。
パワークエリの「正しい初期設定」は、紹介されていません。
ピボットおじさんの４年間の探求結果を紹介します！
①パワークエリの更新をすると「バックグラウンドで更新」が表示され、
他の操作が可能な状態で更新処理を行います。一見便利そうですが・・
このために、使用するメモリの増加や、計算漏れが生じます。
対応 Ｅｘｃｅｌ／データ／プロパティ
「バックグラウンドで更新」が「有効」⇒☑を外してください。
②列幅の自動調整・・更新すると列幅が変わる［戻る］
対応 プロパティの列幅自動調整☑を外してください。
・・この２つのプロパティの変更は、クエリ毎に個別設定が必要です。
③パワークエリは、更新が終了しても、バックグラウンド・データの待機
をしています。このため、ＣＰＵは動いて、メモリも増加します。
対応 PowerQueryエディター／オプションで、☑を外し解除する
⇒この設定は、PowerQueryエディター Ｅｘｃｅｌ・ブック単位です。
◎「初期設定」のカスタマイズを行うと、快適に更新が可能になります。
みなさん、是非、試してみて下さい！
３．パワークエリにも双子の呪い！
Ｅｘｃｅｌ関数との違いを知ろう！
パワークエリの本を読んで理解したつもりでも、断片的な知識では、正しく使えません！Ｅｘｃｅｌと同じだと信じてはいけません！
ここでは、３つの事例を紹介します。
①四捨五入 事例紹介あり
パワークエリの四捨五入は、ＡＣＣＥＳＳでも使われる「銀行丸め」です。
端数の差が大きくならないように丸めます。
ＥｘｃｅｌのROUND関数とは違いますので、注意してください。。
Ｅｘｃｅｌと同じ使い方をするには、式入力に追加の指定が必要です。
= Table.AddColumn(変更された型1, "四捨五入",
each Number.Round([金額], 1,RoundingMode.AwayFromZero), Int64.Type)
銀行丸めとは、端数が5より小さいなら切捨て、5より大きいならは切上げるというところまでは、通常の四捨五入と同じです。異なっているのは、5の場合の扱いです。通常の四捨五入が切上げるのに対し、銀行丸めは結果が偶数になる方に丸めます。
②日付、時刻 実例紹介あり
特定の日時＝型で指定します。Ｅｘｃｅｌでは、直接差し引きできます。
パワークエリでは、「期間」として別の型で選択する必要があります。
③Ｖｌｏｏｋｕｐ関数との違い
Ｖｌｏｏｋｕｐ関数 Ｅｘｃｅｌの代表的な検索機能です。
検索値から照合して該当する列を１つ取得する［完全一致］と
最大値を一致する［近似一致］の２つがあります。
パワークエリのマージ（結合）
検索値は、複数組み合わせが可能
取得できる列は、１つではなく全部可能
「近似一致」は直接できない ⇒ 応用で対応
検索先が重複していると全部行を取得します！
⇒ １件に絞り込みには、重複行の削除が必要
［Ｖｌｏｏｋｕｐ関数は最初の行 ⇒ 重複なし］
マージ・結合の種類は６種類あります！ 事例紹介あり
Vlookup関数的な使い方・・左外部、右外部 が紹介されていますが、
データの選択 ・・左反、右反 は、データの整備に非常に有効です。
他にも、Ｅｘｃｅｌの感覚で行うと、異なる結果になることがありますので、注意してください。
４．弱点を補い、強化する！
ピボットおじさんは、ピボットテーブルを３０年使いこなすために、
Ｖｌｏｏｋｕｐ関数やＶＢＡマクロで対応してきました。
パワークエリを４年間探求し、実践できるようになりました。
パワークエリは、便利なツールです。
「データの取得と変換」の使い方だけを覚えるだけでは不十分！
どういう場面で使えば「効果」があるのか、これがポイントです！
パワークエリは、ピボットテーブルと組み合わせて使うのが、
一番成果が上がります。
ｎｏｔｅ記事で実践事例を紹介しています！
是非、サンプルで確かめてみて下さい。
【元データの弱点を補う】困った元データを解消！
事例紹介あり ⇒使いにくいデータも利用できます！
①空白行などの不要行・・フィルタ―、行の削除
②セルの結合など・・フィル
③横長データ ・・ 縦型に列のピボット解除
※ピボット姫と学ぼう！（7）Ｅｘｃｅｌと違う？？双子の「呪い」！！
【元データを強化する】ピボットテーブルをもっと活用！
①クエリの追加
②クエリのマージ(結合)
③条件列の追加
④グループ化
⑤列のピボット
⇒ＣＳＶデータ、フォルダーから一括読込などのデータ利用も可能です！
◎推奨：パワークエリから直接ピボットテーブルを作成！
事例紹介：マルチ列 (10)前回の改善です！！「空白」の除外！
①元データに空白行があると、ピボットテーブルでjフィルターで除外しなければなりません。⇒パワークエリで、除外しておきましょう！
②パワークエリのデータを読み込まずに、直接ピボットテーブルにすると、
ファイル容量の増加を防止できます！
※データモデルには追加しません！
①②で、パワークエリの効果が発揮できます！
５．エラーが出るのが宿命！
パワークエリは、Ｅｘｃｅｌ外で処理して、「更新」でデータを読み込むツールです。
「更新」クリックしてクエリの途中でエラーがあると、読み込むことができません。一連処理を「エラーなし」で終了する必要があります。
エラーが出ると「怖い！」と思う人が多いと思いますが、「大丈夫です！」エラーの出る理由を理解し、正しく対処すれば良いのです。「スキル」を身につけましょう。
６．理想のカップル誕生！
ピボット姫とパワークエリ王子は、「呪い」を解きました！
「みんなから嫌われる呪い」も、二人の愛が深まったとたんに解けました！
二人は「理想のカップル」です！
Ｅｘｃｅｌ王妃も、長い幽閉から解放されて、お城に戻りました。
表計算の魔女、ＡＣＣＥＳＳの魔女は、反省を続けています！
２人は自分の覚えた一つのスキルに固執した「偏見」が呪いの元でした。
表計算の魔女も、ＡＣＣＥＳＳの魔女も、ようやく目が覚めて、
必要とされる人には、これまで以上に活用してもらえることが判り
安心したようです。
データの扱い方やＥｘｃｅｌ全体の理解を深めて、
初心者、中級者、上級者の能力とニーズに合わせた柔軟な学び方、
使いこなし方をしたいものです。
７．ピボット姫と学ぼう！ のまとめです
みなさんは、ピボットテーブルの「呪い」を解くことで、Ｅｘｃｅｌとの違いを理解して、それぞれの使い分けを学んでいただいたと思います。
パワークエリとピボットテーブルとを組み合わせは、
ＰｏｗｅｒＢＩのような使い方・・データ分析、情報利用
ＡＣＣＥＳＳのような使い方 ・・業務のシステム化
という、エクセルの「理想形」につながります！
多くの人が、ＤＸの導入、学び直しなどに取り組まれています。
①Ｅｘｃｅｌ作業の改善、脱Ｅｘｃｅｌ・・
②ＢＩツールでの情報活用
③システムの開発、汎用ソフトの導入
ＢＩ［ビジネス・インテリジェンス］
日々のビジネス活動から得られるデータを収集・保存・加工・分析するプロセスや方法の総称です。
Ｅｘｃｅｌ版ＢＩツール
パワークエリ、パワーピボットがＥｘｃｅｌ２０１０からアドインとして提供、Ｅｘｃｅｌ２０１６から実装され「モダン・エクセル」と呼ばれます。
Ｅｘｃｅｌの新関数の開発も進んでいます。
ＢＩツール［専用］
マイクロソフトは、ＰｏｗｅｒＢＩを提供しています。
膨大なデータから必要な情報を抽出し、数字の羅列だけではなくグラフなどの見やすい形に変換してダッシュボード上に表示する専用ツールです。
ＡＣＣＥＳＳ
クエリでシステム化の設定処理を行います。
定型作業や、大量のデータの保守などのシステム化は、Ｅｘｃｅｌと比べてると優位性があります。
アウトプットの設定などは、ピボットテーブルと比べると「自由度」が劣るところがあります。
エクセルの理想形！！
ピボットテーブル ＋ パワークエリ ＝ テンプレート
パワークエリとピボットテーブルを組み合わせる「テンプレート」は、
Ｅｘｃｅｌパワーピボット、ＰｏｗｅｒＢＩ、ＡＣＣＥＳＳのような使い方を可能にしました。私は、エクセルの「理想形」だと思います。
ＢＩツールや、ＡＣＣＥＳＳなどを導入する前に、
パワークエリとピボットテーブルのテンプレートを
試験的に使ってみてください。かなりのことが可能になります。
そして、必要があれば「本格的に移行する」ということをお薦めします。
テンプレートは、準備作業として最適です。決して無駄にはなりません。
「知識」だけではなく「楽に、楽しく、もっと役だつ」エクセルで「実践」しましょう！
◎この記事にご興味のある方は、是非お問い合わせください！
ピボットおじさんの無料相談窓口 ３０分ＺＯＯＭ 受け付けています！
ｎｏｔｅの記事について直接聞きたい・・
ピボットテーブルを使っていても「使いこなせない！」
皆さんのお悩みを30分無料 ＺＯＯＭで解決します。
是非、コメントや、応援をおねがいします。
