先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました
またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした
もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です
こういった場合はPower Queryのグループ化機能で簡単に価格違いが起こっているグループを見つけることができます
今回は次のデータを使って解説を行いたいと思います
ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします
こちらのデータから価格違いが起こっている地区を見つけます
ポイント
Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します
グループ化の適用
解説はPower Queryエディターからはじめさせて頂きます
ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています
上記の画像の状態からまずグループ化をクリックします
デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します
こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます
グループ化を実際に適用すると下の画像のような状態になります
グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400＜＞最大:1500」となります
条件列の作成
ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「〇」という表示をする列を作成します
条件列は次のように設定します
価格違いがあるグループと明細リストの抽出
上の条件列を作成すると下の画像のような状態になっています
もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります
ただ、下の画像のように店舗と価格もリストにしたいとします
目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります
まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います
後でこちらの「myList」を参照します
次に
関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します
＊参照ステップの記事を参照
これで、グループ化を行う前の状態に戻りました
ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します
List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます
ここから「TRUE」のみをフィルターすればリストは完成です
＜まとめ＞
今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました
ピボットテーブルでも同じことができます
ピボットテーブルの場合には分散や標準偏差を出す方法もあります
グループ内で価格にバラツキがある場合には、分散や標準偏差が０以外になります
