ピボットテーブル１１Q (12)VLOOKUP関数とパワークエリ・マージは同じ？！データ分析に大きな違いが・・！！
ピボットテーブルは「即席ラーメン」理論より
「即席ラーメン」を自分好みの美味しい一品にするには、
「即席ラーメン」を正しく調理するだけでなく、
トッピングなどで自分好みの「味変」＝アレンジが必要です。
Ｅｘｃｅｌを使って、データ分析で役立つ情報を得るには、
「ピボットテーブルを正しく使う」だけでは、実現できません！
「元データ」の作り込みが、不可欠です！
あなた好みの「味変」＝アレンジで、データ分析を変身させましょう！
１．データ分析は「元データ」で変身する！
私たちが、毎月、実績を集計し、レポートを作ったりするのは、
データ分析で役立つ「情報」を得て、仕事や経営に生かすためです。
私が皆さんに紹介しているのは、
情報力 ＝ データ活用力 ✖ データ分析力
データ分析力 ⇒ ピボットテーブル
データ活用力 ＝「元データ」の作り込み ⇒ パワークエリ
最大化するツールが、「実践テンプレート」です。
私は、２５年間、VLOOKUP関数で「検索」して、
データの補強をしていました。
セルに式入力、列全部にコピー、VBAマクロで自動化など、
非常に手がかかりました。
５年前から、パワークエリを使い始めました。
マージ「結合」を使うと、マウス操作だけで、
検索と選択が合わさった様々なデータ結合が可能になりました。
ＶＢＡマクロも使わずに、繰り返し「更新」できるのです。
ピボットテーブルのデータ分析もレベルアップして、
総合的な「情報の取得」の向上につながりました。
ピボットテーブル、パワーピボットでは、「リレーションシップ」で、
強力な検索機能が使えます。
ＤＡＸ式を覚えて高度な分析も可能になりますが、
ピボットテーブルの最強の武器「集計フィールド」が使用できないので、
私は、パワークエリとピボットテーブルを使う「実践テンプレート」、
簡易・管理システム化を皆さんにお薦めします。
２．VLOOKUP関数「検索」とは！！
ＶＬＯＯＫＵＰ関数・・セル毎の「検索」機能！
構文 VLOOKUP(検索値, 範囲, 列番号, [検索の型])
◎検索値は１つ、同時複数は不可［１つに集約すれば可能］
検索先 範囲
検索値 1 ： ｍ 複数該当でも 最初の １項目
◎検索範囲 検索値の右側のみ
※左側取得するには、INDEX関数＋MATCH関数、
あるいは、XLOOKUP関数
◎検索結果 列番号で指定・・個別指定
◎検索の型 完全一致、近似一致の使い方が可能
VLOOKUP関数の課題・・関数の使い方を覚えないとならない！
①検索値は１つしか使用できない⇒検索用のデータが必要
②検索は右側に限定⇒XLOOKUP関数。もしくはINDEX+MATCH関数に
③検索は１項目だけしかできない⇒検索範囲から番号で指定する
④セル単位での入力⇒列全体で使うためにコピーが必要
もしくはテーブルで「構造化参照」を使用。
自動計算の負担大！計算時間、メモリ、ファイル容量が大きくなる。
VLOOKUP関数は「セル」に式入力し、計算結果をセルに表示します。
私もそうですが、Ｅｘｃｅｌの「検索」は１件になることに慣れています。
でも、データ分析を向上させるには、データを整える工夫必要です。
これ、「即席ラーメンの味変」と同じです。
３．リレーションシップも「検索」機能！
VLOOKUP関数の課題を解決する新機能が、「リレーションシップ」です。
「データモデル」で、Ｅｘｃｅｌブックだけでなく様々なデータを、関連付けて使えるようになりました。
①検索値は１つしか使用できない⇒検索用のデータが必要という課題は
残りますが、②～④の課題は解決できるようになりました。
大きなメリットがありますが、私が一番愛好するピボットテーブルの最大利点の「集計フィールド」が使用できなくなります。
パワーピボットは、「ＤＡＸ式」で補うことができますが、習得のハードルが上がりました。
「検索」の課題・・「検索モレ」
「検索」では、検索値で、検索先がなかったらエラーになります。
「検索モレ」とは、検索先にあるのに、検索値そのものが無かったら？
そんなことないだろう！どうでしょうか。
前年実績と比較する場合など、「年月」が抜けていることが生じるのです。
例：売上・・１年前と今年を比較してみましょう！
２０２３年４月 今年あり ・・ 去年の検索 できる
５月 今年なし ・・ 去年の検索 できない！
検索元に抜けがあると「検索モレ」が生じて、正しく集計できません。
パワーピボットのＤＡＸ関数では、前年実績を検索できます。
その際に、この検索モレが生じない「工夫」がされています。
・「日付テーブル」を作成し、日付の検索モレが生じないようにする
・ DAX タイムインテリジェンス関数を使えるようにする
・ リレーションシップで紐づけをする
「日付テーブル」で年月の「検索モレ」は解消できますが、
他の場合に生じることがあるので、要注意です。
従来のピボットテーブルの機能で、「計算の種類」で集計された数値で比較ができるので、是非、利用してください。
４．パワークエリ・マージは「結合」！
パワークエリは、ＶＬＯＯＫＵＰ関数「検索」と同じツールと
思われていますが、違うツールと理解してください！
リレーションシップのような「検索」関連付けも可能です。
元データの作り込みは、「検索」だけでは解決できないということです。
マージは６種類の「結合」！
パワークエリのマージ(結合)には、６種類の結合機能があります。
左外部、右外部の２種類では、ＶＬＯＯＫＵＰ関数のような「検索」を上回る使い方が可能です。さらに、４種類の「選択・除外」の機能があります。
そして、「追加」機能などができる「データ作り込み」の専用ツールです。
「検索」①左外部、②右外部
◎検索キーを複数設定
◎検索結果も、「項目」を指定して、同時に取得可能
マージ・結合では、検索先の「重複」に注意！
検索元 列 検索先 範囲
検索キー 1 ： ｍ ⇒ ｎ 複数あると
⇒ ｎ件の「結合」ができます！
◎１件だけの取得にしたい場合は、「重複の排除」をしてください。
VLOOKUP関数は、検索先での重複は、最初の１件だけの取得になります。
リレーションシップでは、「エラー」になります。
パワークエリとの違いを、理解しましょう。
さらに、③完全外部、④内部、⑤左反、⑥右反の「選択」機能があります。
５．パワークエリ・マージ「結合」の活用！
パワークエリは、データの取得と変換のツールです。
クエリの関連をデータ処理の流れで管理しています。
リレーションシップでの関連付けと似ていますが、高度な「結合」、「追加」が可能です。
それでは、マージ「結合」の使い方を掘り下げてみましょう！
①左外部、②右外部「検索」・・「重複」の活用です。
「検索先」で複数件ある場合の有効活用例
所要量計算、標準原価計算に応用できます。
納入先別、花束別 構成[レシピ]・・白菊、黄菊など
納入先別、花束別 日別納入量
を、マージ・左反で、お花の所要量計算ができます。
完全外部、内部で「検索モレ」、「完全一致」を選択できます
③完全外部 「検索モレ」が起きない結合です。
今年も前年も、両方が「結合」されます
④内部 ・・両方が一致するものだけ、になります。
⑤左反、右反で「選択・除外」
さらに、素晴らしい機能が、左反・右反です。
⑤左反［右側が除かれる］ 右を追加
⑥右反［左側が除かれる］ 左を追加
マージ・結合では「選択」ですが、相手のデータを「追加」すると、
最新累計のデータや、マスタの整備が、出来るのです。
直近累計、直近マスタ ＋ 追加データ、マスタ
－ 重複を除外［マージ］ ⇒ 最新累計、最新マスタ
直近累計、直近マスタ を ［範囲］で名前をつけてクエリに読込めば、
ＶＢＡマクロを使わずに、パワークエリだけで可能になります！
６．パワークエリは「データ活用」に最適！
パワークエリは、マウス操作で設定できて、Ｅｘｃｅｌの関数、マクロを使わずに、「データ活用」＝データ分析を活かすためのアレンジができる
必要不可欠なツールです。
「即席ラーメン」をピボットテーブルに例えれば、即席ラーメンの「具材、味付け」に関わる重要な役割を果たしています。
◎クエリのマージ、追加だけでなく、ＩＦ関数の代替もできます
条件列の追加では、様々な条件を組み込んで列を追加できます。
さらに複雑な式にカスタマイズも可能です。
Excel数式と比べて非常に判りやすく、列全体の設定でコピー不要です。
◎ＶＢＡマクロの代替
パワークエリは、マウス操作でステップを登録します。
・マ―ジ、追加 ・・累計データの更新
・ピボット解除、ピボット・・比較データの作成
という高度な処理でも、ＶＢＡプログラミングを代替してくれます。
パワークエリとピボットテーブルは、どちらも「更新」クリックで、
最新データを読み込んで、処理してくれます。
「すべて更新」をクリックすれば、連続して処理するので、
システム化が実現できるのです。
ＰｏｗｅｒＢＩやＡＣＣＥＳＳのような使い方が、可能になります。
この記事にご興味のある方は、是非お問い合わせください！
是非、コメントや、応援をおねがいします。
有料相談、個人レッスンは下記の講座でお受けしています！ご希望の日時をリクエストしてください。※優待価格でのご案内いたします
