Excel PowerQueryでクエリを増やさずにSUMIFの様なことをする方法

PowerQueryでクエリを増やさずにSUMIFのような処理をする方法について紹介します。グループ化したクエリを作成せずに、単独のクエリ内で完結する方法を目指します。この方法は、SUMIFだけでなく、COUNTIF、AVERAGEIF、MINIF、MAXIFなどの関数にも適用できます。

操作自体はグループ化したクエリを作ってマージするのが簡単だと思いますが、再利用するわけでもないのにクエリは増やしたくありません。単独のクエリ内で完結する方法に絞っていきます。

画面操作だけでなくM言語に触れる必要がありますが、M言語どころかPowerQuery自体の初心者です(投稿時点で2ヶ月目)。間違っている点や他にいい方法があれば指摘して頂けると助かります。

入力データと期待する出力データ

まず、サンプルデータとどういう出力が欲しいのか確認します。ここではMINIF相当の処理をしますが、他の集計をしたい場合は適宜読み替えてください。

入力データ

商品	売価	販売日
A	100	1月1日
B	150	1月2日
A	120	1月3日
B	80	1月4日

期待する出力データ

各商品について、「売価」の最低額を「最低販売額」として追加します。

商品	売価	販売日	最低販売額
A	100	1月1日	100
B	150	1月2日	80
A	120	1月3日	100
B	80	1月4日	80

List.Min関数を使う方法(カスタム列の追加)

こちらのページに載っていた方法です。カスタム列を追加するだけなので、後からステップを見返した場合にわかりやすいと思います。

Create sumif in PowerQuery (not DAX, and not use group by feature)

追加方法

カスタム列を追加して、カスタム列の式を次の様にしてください。「変更された型」は直前のステップ名です。

let _item = [商品] in
List.Min(
Table.SelectRows(#"変更された型", each [商品] = _item)[売価]
)

数式バーに表示される式全体だと次の様になります。

= Table.AddColumn(変更された型, "最低販売額", each let _item = [商品] in
List.Min(
Table.SelectRows(#"変更された型", each [商品] = _item)[売価]
))

必要に応じてList.Minの部分は変更してください。

• List.Count
• List.Sum
• List.Average
• List.Min
• List.Max

どういう処理をやっているのか

1. _item変数に計算しようとしている行の「商品」の値を代入する
2. 「商品」列の値と_itemが合致する行をTable.SelectRowsで取り出す
3. 取り出した行についてList.Minで「売価」列の最小値を求める

素直に読むと、1行ごとにテープルから一致する行を取り出しに行くようです。単純に列を追加するだけで済みますが、件数が多くなると処理が遅くなってくると思います。

変数への代入でlet inがいるのかと思いましたがまったく関係なく、1つのステップに複数の命令を入れたい場合に必要なもののようです。

サポートされていないパイプライン演算子

ChatGPTがパイプライン演算子を使うと簡単に書けますと提案してきましたが、どうやらExcelのPowerQueryではサポートされていないようです。こっちの方が分かりやすいのに残念です。

let _item = [商品]
in #"変更された型"
|> Table.SelectRows((each [商品] = _item))
|> List.SelectColumns({"売価"})
|> List.Min

新しいクエリを作らずにグループ化したものを結合する方法

1つのクエリ内でグループ化して、次のステップでグループ化前のテープルと結合させる方法です。先に集計してからマージするので速いと思いますが、他の人が見た場合に何をやっているのか分からなくなると思います。

作業手順

1. 「商品」列を基準にグループ化する(必要に応じて最小・合計などの集計列を作る)
2. 現在操作しているテーブル同士を「商品」列を基準にマージする
3. 数式バーを編集して結合するテーブルの片方をグループ化前のステップにする

ステップ名と編集する数式の例

ステップ一覧

• ソース
• 変更された型
• グループ化された行
• マージされたクエリ数

数式の変更例

「グループ化された行」を1つ前の「変更された型」に変更します。

変更前
= Table.NestedJoin(グループ化された行, {"商品"}, グループ化された行, {"商品"}, "グループ化された行", JoinKind.LeftOuter)

変更後
= Table.NestedJoin(変更された型, {"商品"}, グループ化された行, {"商品"}, "グループ化された行", JoinKind.LeftOuter)

クエリ全体
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"商品", type text}, {"売価", Int64.Type}, {"販売日", type datetime}}),
グループ化された行 = Table.Group(変更された型, {"商品"}, {{"最低販売額", each List.Min([売価]), type nullable number}}),
マージされたクエリ数 = Table.NestedJoin(変更された型, {"商品"}, グループ化された行, {"商品"}, "グループ化された行", JoinKind.LeftOuter)
in
マージされたクエリ数