シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理

公開日：2023-02-28　最終更新日：2023-11-09

シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理

「パワク12本目」
Power Query（M言語）の問題と解答・解説

問題に対する解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。

この問題は、パワクについてのお問い合わせがあったので、それに解答する目的で作成しました。

ページ内目次

パワク12本目
解答コード
GUIでの作成
List.Count , List.Sum , List.Max
複数キーのサンプル

パワク12本目

#パワク12本目
シート関数の、
COUNTIFS
SUMIFS
MAXIFS
これらと同じ結果をPower Queryで行ってください。
元データは「テーブル1」とします。

シート関数

D2=COUNTIFS(テーブル1[キー列],テーブル1[@キー列])
E2=SUMIFS(テーブル1[値列],テーブル1[キー列],テーブル1[@キー列])
F2=MAXIFS(テーブル1[値列],テーブル1[キー列],テーブル1[@キー列])

Power Query

解答コード

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    入力TBL = Table.TransformColumnTypes(ソース,{{"キー列", type text}, {"値列", Int64.Type}}),
    追加COUNTIFS = Table.AddColumn(入力TBL, "COUNTIFS", 
        each let _item = [キー列] in List.Count(Table.SelectRows(入力TBL, each [キー列] = _item)[キー列])),
    追加SUMIFS = Table.AddColumn(追加COUNTIFS, "SUMIFS", 
        each let _item = [キー列] in List.Sum(Table.SelectRows(入力TBL, each [キー列] = _item)[値列])),
    追加MAXIFS = Table.AddColumn(追加SUMIFS, "MAXIFS", 
        each let _item = [キー列] in List.Max(Table.SelectRows(入力TBL, each [キー列] = _item)[値列]))
in
    追加MAXIFS

GUI操作での作成

解答コードはM言語で直接記述したものになります。
以下では、同様の結果を得るためのGUI操作での作成になります。

「テーブル1」のクエリを作成

「テーブル1」を選択してクエリを作成
クエリ名は「テーブル1」とします。

「テーブル1」をグループ化

再度「テーブル1」を選択してクエリを作成
グループ化します。

「集計の追加」で新しい列を3列にして、以下のように指定します。

このクエリ名は「テーブル1T」とします

「新しいクエリ」→「結合」→「新規としてクエリをマージ」

「テーブル1」に対して「テーブル1T」を左外部結合します。
それぞれ「キー列」を選択します。

結合されたTableを展開

「ｷｰ列」は不要なのでチェックを外します。

作製されたクエリ

テーブル1

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"キー列", type text}, {"値列", Int64.Type}})
in
    変更された型

テーブル1T

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"キー列", type text}, {"値列", Int64.Type}}),
    グループ化された行 = Table.Group(変更された型, {"キー列"}, {{"COUNTIFS", each Table.RowCount(_), Int64.Type}, {"SUMIFS", each List.Sum([値列]), type nullable number}, {"MAXIFS", each List.Max([値列]), type nullable number}})
in
    グループ化された行

マージ1

let
    ソース = Table.NestedJoin(テーブル1, {"キー列"}, テーブル1T, {"キー列"}, "テーブル1T", JoinKind.LeftOuter),
    #"展開された テーブル1T" = Table.ExpandTableColumn(ソース, "テーブル1T", {"COUNTIFS", "SUMIFS", "MAXIFS"}, {"テーブル1T.COUNTIFS", "テーブル1T.SUMIFS", "テーブル1T.MAXIFS"})
in
    #"展開された テーブル1T"

List.Count , List.Sum , List.Max

※Learn List.Count
※Learn List.Sum
※Learn List.Max

構文

List.Count(list as list) as number
List.Sum(list as list, optional precision as nullable number) as any
List.Max(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any

List.Countはリスト内の項目の数を返します。
List.Sumリスト内のnull以外の値の合計を返します。
List.Maxリスト内の最大の項目が返されます。

それぞれシート関数とほぼ同様です。
オブションはあまり必要性はないと思いますが、詳細はについてはLearnを参照してください。

複数キーのサンプル

上記は単一キーのサンプルでした。
一応SUMIFSと書いていますし複数キーのサンプルも掲載しておきます。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    入力TBL = Table.TransformColumnTypes(ソース,{{"キー列", type text}, {"値列", Int64.Type}}),
    追加SUMIFS = Table.AddColumn(入力TBL, "SUMIFS", 
        each let _item = [キー列],_item2 = [キー2列] 
        in List.Sum(Table.SelectRows(入力TBL, each [キー列] = _item and [キー2列] = _item2)[値列]))
in
    追加SUMIFS

合計SUMIFSの部分だけのサンプルです。
複数条件をandで結ぶだけになります。
他も同様になりますので、やってみてください。

同じテーマ「PowerQuery（M言語）入門」の記事

別ブックの最終シートの取り込み

3本目別ブックの最終シート（一番右の最後のシート）をパワクで取り込んでください。バス・ブック名の指定は任意シート数が変化しても、とにかく一番最後のシートを取り込むという事です。#パワク#PowerQuery 解答コード let ソース=Excel.Workbook(File.Contents("D:\パ…

列数不定のCSVの取り込み

4本目列数不定のCSVをパワクで取り込んでください。1行目をヘッダーして使用してください。※バス・ファイル.名の指定は任意 ※文字コードはUTF8（BOMなし） ※テストデータはメモ帳等で作成してください。

CSVのA列が日付の行だけを取り込む

5本目列数不定のCSVのA列が日付の行だけをパワクで取り込んでください。4本目の続きです。4本目にA列日付で抽出するステッブを追加してください。A列は日付型で取り込んでください。A列の列名は任意。#PowerQuery#M言語解答コード let ソース=Csv.Document(File.Contents(&qu…

A列のヘッダー名を変更する

6本目 CSVをパワクで取り込みます。5本目の続きです。A列のヘッダーは本来は「日付」が正しいのですが、たまに「日付け」となっている場合もあるようです。※さらに他表記の場合もある。A列のヘッダを「日付」に統一して出力するようにしてください。

「売上」が数値の行のみ取り込む

7本目 CSVをパワクで取り込みます。列「売上」が非数値および空の行を削除して、「売上」が数値の行のみ取り込んでください。#PowerQuery#M言語解答コード let ソース=Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),

2つのテーブルのマージ

8本目ブック内に、「tbl個人」「tbl都道府県」があります。この2つのテーブルを結合して、添付サンブルのように出力してください。※「tbl個人」の都道府県には「県」の文字が入っていません。出力は「県」の入った県名で。

グルーブ内の最小・最大

9本目ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。商品コード毎の日付の最小値と最大値（最古日付と最新日付）を出力してください。※サンブルデータはALT #PowerQuery#M言語解答コード let ソース=Excel.CurrentWorkbook(){[Name="tbl伝票…

グルーブ内の最小・最大

有効な最新単価の取得

10本目「tbl伝票」（日付,コード）「tblマスタ」（コード,日付,単価）マスタの日付は（これ以降有効となる）適用開始日です。「tbl伝票」に単価を付して出力してください。※例.2/5のA01はマスタの2/1が適用されます。

新旧マスタの差異比較

11本目 2つのテーブル「TBL新」「TBL旧」統合して差異の区分を追加してください。TBL旧のみ："削除" TBL新のみ："新規" 両方で内容違い："変更"（名称等はTBL新を出力）新旧同じ：空欄 ※サンブルデータはALT #PowerQuery#M言語…

シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
その他
PowerQuery（M言語）入門
シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

Power Query（M言語）入門
シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理