タグ別アーカイブ: パワークエリ
クエリの関数化事例～複数シートを一括で編集する
【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】
エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります
一番、困るのは各シートにヘッダーデータがあるようなケースです
このような場合はまずは1シートを処理するクエリを作成します
そして、そのクエリを関数化します
関数化するということは、Y=２X＋１のような式で表せるということです
こちらのXには複数シート名を入れて使いまわせるようにします
SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます
今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります
使用データ
前述のように各シートにはヘッダーデータがあります
その下に欲しい一括処理したいデータがあります
そして、上記のようなシートがファイル内に複数あります
各シートの開始行などは一致している前提です
上の画像は東京のシートですが、次の画像は大阪のシートです
関数化するクエリの作成
まずは1シートを処理するクエリを作成します
こちらのクエリを前述のように関数化します
今回はテーブル化からはじめず、ファイルからデータを取得します
指定するファイルは「今処理しているファイル」です
ファイルを指定したら、ナビゲーター画面が開きます
こちらでは１つのシートを指定し「データの変換」をクリックします
ここでクエリは複製しておきます（１つは関数を作成した後に、関数を適用するクエリになります
下の画像がPower Queryエディタを開いた状態です
必要なのは11行目から下です
こちらは「行の削除」アクションで10行削除します
ここからもう1アクションあります
変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます
これで関数化のもとになるクエリは完成です
関数化
関数化は詳細エディタで行います
具体的に関数化する箇所は「可変の箇所」、シート名の箇所です
ここでソースステップの内容を確認しておきましょう
要はファイル内の複数シートを取得しています
ここからナビゲーション画面で１つのシートを選択しています
ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです
では実際に記号Xを埋め込んでみましょう
（X)=>はXを記号として使用するという合図になります⇒詳細
ここまで行うとクエリが関数に変ります
関数は「myFunction」と名前を変えておきましょう
ここでパラメーターに「名古屋」と入れてみましょう
これで名古屋のシートデータが変換されて出力されます
関数の適用
ここで関数の複製元のクエリを選択し直します
その後は、適用したステップでソースデータを残して後は全て削除します
ここから列の追加タブで作成済みの関数を呼び出します
関数のXに入るのは「Item」になります
これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります
結果、次の画像のようになります
ここからmyFunctionを展開すれば完成です
次の画像が展開した後のものです
＜まとめ＞
今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました
クエリの関数化のポイントは可変の箇所を見つけることです
今回は、1度作成したクエリから可変の箇所を見つけました
そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います
もう少し詳しく「クエリの関数化」について知りたいという方はぜひUdemy講座（動画）もご利用ください
期間限定で「4,800⇒1,200」の割引クーポンが利用できます
（無料期間終了）Udemy講座のリニューアル及び無料クーポンのプレゼントについて
昨年、Udemyにて「Power Queryのメニューにはない裏技ができる！ M関数を学んで使えるようになる講座」をリリースしました。但し、多くの意見・指摘が寄せられたため今回、全面的に動画を撮り直しました
この機会に無料クーポンをこのブログ読者の皆さんにプレゼントしますので、ぜひレビュー投稿をお願い致します
頂いた意見・指摘
これまで多くの意見・厳しい御指摘が寄せられました
一方で、それだけこのテーマについては期待が高かったことを認識するとともに、期待を大きく裏切り大変申し訳ないことをしたと反省しております
これらの指摘・意見を参考にしてほぼすべての動画を撮り直しています
リニューアルポイント
動画の本数は以前と変わりませんが、動画は一から撮り直しました
動画によっては3倍の長さで撮り直しています
動画撮影にあたってはとにかく「間」を取りながら、動画のメリットをだせるように最新の注意を払いました
・作成するクエリの内容を丁寧に説明する
・前段でポイントとなる箇所を図で解説する
・操作動画は事前アナウンスをしながらゆっくりPC操作を行う
こちらがサンプル動画です
レビュー投稿をお願いします！
講座の内容としては他ではなかなか学べない「Power QueryのM言語、M関数を実践的に使うためのテクニック」が詰まった内容となっています
しかも、コードを一から書くわけではないのでそれほど難しくはありません
この講座が終了した時には、Power Queryの凄さや面白さを体感して頂ける内容だと自負しています
こちらが講座の無料クーポンとなります⇒お申込み
ぜひ、講座をご覧いただきレビュー投稿をお願いします
すぐにではないですが、頂いた意見は次に活かしていきます
尚、無料期間は突然終了する可能性がある点は予めご了承ください
グループ別に合計や平均に加えて標準偏差を抽出する方法
【数字は平均だけではよく分かりません。グループ化機能を更に深堀し、分析用の列に標準偏差も加えましょう！】
グループ化機能はとても便利です！グループ毎の集計を簡単に行いつつ、集計した数値を並べることができます
但し、グループ化の集計メニューには「標準偏差」が足りません
平均値を集計しても、バラツキが大きいのか、バラツキが小さいのかで平均値の解釈が違ってきます
例えば、上のグラフにはA～Cという３つのグループがあります
いずれも平均は１５０です
ただ明らかにグループBとグループCでは平均値１５０の解釈が違ってきますよね
今回はグループ化のラインナップに標準偏差をサクッと加える方法を解説します
グループ化の基本
グループ化を行う際、「基本」ではなく「詳細」を選択すれば複数の集計を行うことができます
今回は「合計」「人数」「平均」を集計した後に「すべての行」を集計に加え、後で集計結果を標準偏差に集計し直します
すべての行によるグループ化
「すべての行」のグループ化により、グループ別にテーブルが作成されます
この各テーブルが配置された列を使用してカスタム列を作成します
カスタム列内では「List.StandardDeviation」というM関数を使用します
使用できる列には「標準偏差」を集計する対象の「点数」の列がありません
ただ「標準偏差」の列は各テーブルの集合ですので、⇒標準偏差列⇒テーブル内の「点数」という流れで列を指定します
上の画像の「点数列」は手動で角括弧：[を使用して作成します
これでグループ別に標準偏差が集計されます
＜まとめ＞
今回はグループ別に標準偏差を集計する方法を解説しました
グループ化/すべての行とM関数の組み合わせにより、簡単に標準偏差を集計することができます
ワークシート関数と違い、グループ別に分けて集計する必要もなく、ピボットテーブルと違って直接テーブル化を行えるのでとても便利です
今後もPower Queryの便利術を発信していきます
パワークエリ記事一覧
M関数逆引き辞典はこちらから！
初級編
| タイトル | 内容 |
|---|---|
| Power Queryで操作はどう変わる？～初級講座１回目 | パワークエリとはなにか？そのメリットをスマホに例えて解説します |
| シートからデータ取得～ 初級講座2回目～ | 別シートのデータをテーブル化して取得する方法を解説します |
| ファイルからデータ取得 初級講座3回目 | 別ファイルのデータをテーブル化して取得する方法を解説します |
| フォルダから複数ファイルデータを一括取得～初級講座4回目 | フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します |
中級編
| タイトル | 内容 |
|---|---|
| 複数データの組み合わせ/クエリのマージ～中級編1回目～ | クエリのマージ技術の概要について解説します |
| クエリのマージ/項目追加～中級編2回目～ | 複数のクエリをマージする方法について、実際のデータで解説します |
| クエリのマージ/計算処理～中級編3回目～ | マージした項目を活用して計算処理を行う方法を解説します |
| クエリの追加作成～中級編4回目～ | 同じ項目のクエリを追加して結合する方法を解説します |
| クエリの追加・結合～中級編5回目～ | クエリの追加をピボットテーブルと連携して行う方法を解説します |
| ピボット解除/縦横並びの変換処理～中級編6回目 | 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します |
| セル分割他・変換処理～中級編７回目 | 複数のデータが１つのセルに入っている場合に分割する方法を解説します |
| セル分割他・変換処理２～中級編8回目 | 前回の内容を使いやすい形式にして出力する方法を解説します |
| エディター内のやり直し操作～中級編9回目～ | ワークシートにおけるCtrl＋Zに相当する処理の方法を解説します |
| 変換した列の追加～中級編10回目～ | 連番、条件列（IF関数に相当）及び列削除・移動他を解説します |
| グループ化1～中級編11回目～ | データを階層化して集計する方法を解説します |
| 複数条件でグループ化～中級編12回目～ | データを複数の条件で階層化する方法を解説します |
上級編
| タイトル | 内容 |
|---|---|
| 行削除とヘッダー行の調整～上級編1回目～ | 見出し行/ヘッダーがおかしい場合の対処方法を解説します |
| セル結合により見出しが2行の表をデータ活用１～上級編2回目～ | セル結合された使いにくいデータを使いやすくする方法を解説します |
| セル結合により見出しが2行の表をデータ活用2～上級編3回目～ | セル結合された使いにくいデータを使いやすくする方法を解説します |
| 各種集計/合計から四捨五入まで～上級編4回 | 四則演算の計算から四捨五入まで、様様な集計方法を解説します |
| エラー修正_処理ステップエラー～上級編5回目 | ファイル保存先変更などに伴うエラーの対処方法について解説します |
| 途中のステップを削除した場合のエラー修正処理～上級編6回目 | 途中のステップを削除した場合のエラー対処処理について解説します |
| エラー修正_データ自体のエラー～上級編7回目 | データ自体がエラーになっている場合の対処方法について解説します |
| カスタム関数を自身で登録して使い回す～上級編8回目 | ユーザーが独自で作成するカスタム関数の作成方法を事解説します |
| カスタム関数を自身で登録して使い回す2～上級編9回目 | 作成したカスタム関数を呼び出して活用する方法を解説します |
| IF式を組み合わせて列作成～上級編10回目 | 条件付きカスタム列を作成する方法を解説します |
| 「例からの列」にて変換パターン自作～上級11回目～ | 入力した変換例を汲み取って変換を行う方法を解説します |
| 「 例からの列」による桁数が規則列への対応～上級12回目～ | 「例からの列」による変換方法の応用方法を解説します |
| エディタを開かずにソース変更処理～上級13回 | 「パラメーター」機能を使用してデータソースを変更する方法を解説します |
| シート上からデータソースを変更する方法～上級編14回目 | シート上の入力を変更するだけでデータソースを変更する処理を解説します |
| セルの値を変更するだけで読み込みを変更する方法～上級編15回 | セルの変更内容をクエリに組み入れて、クエリの内容を変更できるようにする方法を解説します |
| クエリのコピー・バックアップ・削除他～上級編16回 | クエリのコピーやバックアップ及び削除、その他グループ化などの方法を解説します |
| 文字列の抽出～上級編17回 | RIGHT関数やLEFT関数、MID関数に相当する内容に加え、特定の文字列を抽出する裏技を解説します |
| 文字列の追加、置換～上級編18回 | 前回の特定の文字列の抽出に加え、文字列の追加や置換方法について解説します |
| エラー発生の予防/列のデータ形式変更と削除について～上級19回～ | エディタ内の操作のちょっとした工夫で、エラー発生を予防する方法を解説します |
M言語編
| タイトル | 内容 | 使用するM関数 |
|---|---|---|
| M言語に慣れる_1回目～コード構造の把握～ | Mコードの基礎的なルール/文字の色、大文字の区別、ステップの参照について解説します | List.Average/平均値・計算 |
| M言語に慣れる_2回目～カスタム列～ | カスタム列・作成画面でM関数を使用して新たな列を作成する方法を解説します | Text.From/文字列への変換 |
| M言語に慣れる_3回目～ダイナミックフィルタリング～ | セルの変更内容をフィルタイングの内容に反映する方法を解説します | List.Max/最大値の計算 |
| M言語に慣れる_4回目～特殊テンプレート作成～ | ２つのクエリを組み合わせて、1担当者に対して複数の勤務日がある表を作成する方法を解説します | – |
| M言語に慣れる_5回目～M関数を検索する方法～ | ワークシート内の関数のように、M関数を参照する方法を解説します | Text.PositionOf/特定の文字列の位置抽出 |
| M言語に慣れる_6回目～3種類の括弧～ | 丸括弧と角括弧そして波括弧とテーブル、リスト、レコードの関係を解説します | – |
| M言語に慣れる_7回目～空のクエリからテーブル作成～ | 空のクエリから、リストやレコード、テーブルを作成する方法解説します | #table/テーブル作成、Table.RenameColumns/列名変更 |
| M言語に慣れる_8回目～eachを使いこなしてM関数作成～ | 数式バーに良く出てくる「each」の用法について解説します | Table.SelectRows/テーブルから特定の行を抽出、List.Select/リストから特定の値を抽出 |
| M言語に慣れる_9回目～複数シートをコード1行で結合～ | ファイル内のシートからデータをまとめるクエリを作成する方法を解説します | Excel.CurrentWorkbook/ワークブックの内容を一括抽出 |
| M言語に慣れる_10回目～グループ毎に連番作成～ | 「グループ化の操作/全ての行」を活用してグループ毎に連番を作成する方法を解説します | Table.AddIndexColumn/連番作成 |
| M言語に慣れる 11回目～前行を参照して計算～ | 連番と波括弧：{}を組み合わせて活用して前行を参照する方法を解説します | – |
| M言語に慣れる～12回目例外処理 | エラーが発生した際の対処処理を行う方法を解説しています | – |
| M言語に慣れる13回～ダイナミックに連続した日付作成 | ワークシート内にある連続性のない日付から、連続する日付を作成した後、他データも紐づける方法を解説します | #date/「年、月、日」から日付を作成、#duration/「日、時間、分、秒」から期間値を作成します、List.Dates/「開始日、リスト個数、増分」から日付のリストを作成、Number.From/値を数値型式にして返します |
| M言語に慣れる14回目～複数行を１つのセルにまとめる | グループ毎のテーブルを作成後、グループ別に１つのセルにテーブルの内容をまとめる方法を解説します | – |
| M言語に慣れる15回目～TEXT関数/文字列関数 | テキスト関数の事例について解説します | Text.Contains/文字列の有無を判定、Text.Replace/文字列の置換 |
| M言語に慣れる16回目～List関数 | リスト関数の事例について解説します | List.Max/最大値の計算 |
| 不規則に散らばっているデータを一括取得～M関数～ | 見出し行/ヘッダーがそれぞれ違うシートの内容を一括でまとめる方法を解説します | List.PositionOf/リストの中から特定文字列のリスト内位置を抽出、Table.RemoveFirstN/テーブルの先頭から指定行数を削除します、Table.PromoteHeaders/先頭行を新しい列見出しにします |
| 参照先バッファー化によるパフォーマンス向上～M関数～ | クエリのパフォーマンスを向上させる方法を解説します | Table.Buffer/参照テーブルのバッファー化 |
| クエリの列名をダイナミックに変更する～M関数使用～ | 入れ子のリストを使用して、ワークシート上の内容でクエリの列名を変更する方法を解説します | Table.RenameColumns/列名変更、Table.ToColumns/テーブルからリスト（入れ子）作成 |
| Power Queryエディタ上で複数条件付き合計処理～M関数使用 | M関数を組み合わせて「SUMIFS関数」と同様の計算をエディタ上で行う方法を解説します | Table.SelectRows/条件に合う行のみを抽出、List.Sum/合計値を抽出 |
| 指定した範囲の値を使用してフィルタリング | Ｍ関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します | List.Contains/リストに指定の値が入っているかを判定 |
| 複数リストを1対1の関係でうまく組み合わせる | 1行に複数の値を持つリストを複数組合わせる場合に、リスト間で1対1の関係になるようにします（例：8月2日/月、火、水➡8月2日/月） | Table.FromColumns/複数のリストからテーブルを作成 |
| 列名をダイナミックにしたピボット解除を行う | 新たな列を追加した場合でも、並べ替える列とそうでない列をコントロールできるようにする | Table.ColumnNames/列名リストを作成 |
| PDF変換の応用～フォルダに入れた複数PDFから都合良くデータを切り取る方法 | 複数のPDFファイルから都合よい箇所だけデータを一括抽出できるようにする | Table.RemoveFirstN/指定行から上の行を削除 List.PositionOf/リストの中から特定文字列のリスト内位置を抽出 |
| 【POWER QUERY裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法 | フォルダ内のファイルを一括で取得する際に、パラメーターを使用して取得するシートを可変にします | Excel.Workbook/Excel ブックの内容を返します。 |
| M関数を使用して、可変で出現する特定文字の下の行を切り捨てる | 特定文字列の下の行を可変で削除します | List.PositionOf/リストの中から特定文字列のリスト内位置を抽出 |
| 置き換えのIF式を簡略化する方法～リスト活用、列削除省略～ | メンテナンスが面倒なIF式を簡略化する方法を解説します | List.Contains/リストに特定の値が含まれていればTRUEを、そうでなければFALSEを返します |
| POWER QUERYならではのカレンダーテーブル活用~経過営業日の算出 | M言語独自のリストの活用により、経過営業日の算出を自由自在に行えるようにします | Day.DayOfWeekName/曜日変換 |
| クエリの関数化事例～複数シートを一括で編集する | ヘッダーデータがある複数シートを一括処理します |
分析編
| タイトル | 内容 |
|---|---|
| POWER QUERYで手軽にABC分析 | パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます |
| RFM分析の「R」を集計する | 注文データから注文者（会員） 別に最終注文データを集計できるようにします |
| RFM分析の「F」を集計する | 注文データから注文者（会員） 別に注文頻度を集計できるようにします |
| RFM分析の「M」を集計してまとめる | 注文データから注文者（会員）別に注文金額を集計できるようにします |
コラム
| タイトル | 内容 |
|---|---|
| 【パワークエリ・チャレンジ】グループ別の小計と合計を表示する | 今回は本来は一律処理のPower Queryに行挿入を行います |
| 【パワークエリ・チャレンジ】列数が可変の表を組み合わせて計算する | List.Accumulate関数を他のM関数と組み合わせて使用します |
| POWER QUERYをクラウドで活用する方法～POWER AUTOMATE使用～ | DataverseとPower Automateを組み合わせて、Power Queryをクラウドで活用します |
| （続編）２つの表にてどの行が追加、削除されている？ ⇒どの列が変更になっている？ | ピボット解除と複合キーを活用して２つの表を比較しやすくします |
| どの行が追加、削除されている？～結合の6種類～ | 結合の各種類をつかいこなせば、行の追加や行の削除の状況が一目でわかります |
| 【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 | 行列双方向にセル結合がある表をテーブル形式に直します |
| 【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法 | 実は行列の入れ替えを行えば、列方向でのフィルター処理が可能です |
| 【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する | 余りの計算とピボット機能を組み合わせて1列のデータから複数列のテーブルデータを作成します |
| 【M言語実践】グループ別に累計を集計する方法 | グループ別に累計値を集計する方法を解説します |
| 【M言語実践】顧客の2回目のリピート率を分析する方法 | 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します |
| M言語実践】グループ別にIDを設定 | グループ別に連番を採番した上で桁数を揃えてIDを作成します |
| マスタデータの履歴管理を行う方法 | マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します |
| POWER QUERYのステップ数を減らす３つの工夫 | 「変更された型」のステップを減らすなどの工夫を解説します |
| エディタからデータ入力してクエリ作成 | エディタ上で直接データを入力する方法を解説します |
| 【豆知識】どこの行が変わったかを調べる方法 | クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します |
| 【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 | ピボット解除と別な方向で並べ替える方法を解説します |
| 【豆知識】ピポット解除時の空欄の扱い | グループ化 |
| 【分析作業用】大量データを1目で把握する | パワークエリの表示タブでは大量のデータを1目で分かるようにする為の工夫がありますので、そちらを解説します |
| 【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法 | グループ化機能を2段階で行うことで、グループ単位の集計と個々の集計とを同時に行う方法を解説します |
| クエリの複製、参照、そしてコピーとの違い～ | クエリを右クリックした時に出てくる「複製」「参照」「コピー」の違いについて解説します |
| POWER QUERYって何？～エディタ上でのショートカットキー | エディタと他ファイルを並行して開く方法も含めて、操作を早く行う方法を解説します |
| POWER QUERYって何？？～時刻を曜日と日付に変換 | 時刻データを曜日や、日付データに変換する方法を解説します |
| POWER QUERYって何？？～経過時間を一括で計算する | ワークシート関数では、直接計算できない経過時間を簡単に計算する方法を解説しています |
| POWER QUERYって何？？～日付を月や四半期単位に変換～ | 日付データを簡単に月や四半期単位に変換する方法を解説します |
| POWER QUERYって何？？～読込んだデータに行番号追加～ | シートに読込んだクエリに行番号を追加する方法を解説します |
| 文字列を記号毎に分割し、行方向に展開する方法 | コンマなどの記号をもとにして文字列を分割しつつ、列方向ではなく、行方向に展開する方法を解説します |
| POWER QUERYって何～既存クエリのステップを一括で削除する方法 | 適用してステップの欄で一気に複数ステップを整理する方法を解説します |
| POWER QUERYって何？12～重複のないリストを一瞬で作成 | 一気に重複を排除したリストを作成する方法を解説します |
| POWER QUERYって何？？11～ユニークな数の集計～ | パワークエリならではの集計方法を1点だけ解説します |
| POWER QUERYって何？～ピボット解除（縦横並び替え）と入れ替えの違い～ | 紛らわしい「ピボット解除」と「行列の入れ替え」の違いについて |
| POWER QUERYって何？？９～WEBページからデータ取得～ | Webページからエクセルデータを直接取得する方法を解説します |
| POWER QUERYって何8？？？～PDFファイルをエクセルに変換～ | PDFファイルからエクセルデータを直接取得する方法を解説します |
| POWER QUERYって何？～列の結合/複合キー活用～ | 複数の列を結合して複合キーを作成・活用する方法を解説します |
| 文字列から空白を取り除く_POWER QUERY | 文字列の前後や、文字列間に入り込んでいる空白を取り除く方法を解説します |
| パワークエリエディターで行コピーを行う方法 | M関数を使用して行をコピーして複数にする方法を解説します |
| MICROSOFT POWER QUERY～ACCESSとEXCEL連携～ | 特殊な方法でAccessと連携する方法を解説します |
| POWER QUERYって？？？合計を計算する | グループ化による合計金額の計算方法を解説します |
| POWER QUERYって何？？？連番作成 | 連番を作成する方法を解説します |
| POWER QUERYって何？？パワークエリの使用メリット | パワークエリを使うメリットの一つは履歴の管理が便利な点があげられます |
| POWER QUERYって何？？？～ACCESSとエクセルの連携～ | 通常の方法にてAccessと連携する方法を解説します |
| ピボット解除を複数列を軸にして実行する | 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します |
| IF 式の中でOR条件とAND条件を使う方法～POWER QUERY～ | ANDとORの指定を小文字で行うのがポイントです |
【パワークエリ・チャレンジ】列数が可変の表を組み合わせて計算する
【M関数を利用することで、数式の修正しなくても列数が可変の表を組み合わせて計算できるようにします】
値を別表で作成した上で計算を行うケースはよくあると思います
上の画像のケースでは、ボーナスを計算するのに基礎となる評点を作成した上で各ポイントをクリアしていれば「ポイント別」に加算点を計算しています
例えば、上の画像で言えば田中さんはポイント1と2をクリアしているので、80*9%と80*10%が加算点の合計点になります
上記では関数で計算していますが、問題はポイント数が変わった場合です
ポイント数が変わるたびに関数を変更しなくてはなりません
今回の記事では、いくつかのM関数を組み合わせたカスタム列を作成することで、計算の対象列数が可変でも計算が正しくできるようにします
使用するデータ
２つのテーブルを使用します
上の画像は「tableMatrix」です
こちらのテーブルを読み込んだクエリでカスタム列を作成します
上の画像は「tablePoint」です
こちらもカスタム列内で使用します
最終的に作成するカスタム列
今回の記事で最終的に作成するカスタム列は次の通りとなります
一見複雑ですが、List.Accumulate関数の箇所を紐解けば一気に理解のハードルが下がります
List.Accumulate関数
次のリスト：myAccumulateを基にしてList.Accumulate関数でリストの合計値を計算してみます
変数：state,currentが出てくるので一見ややこしいです
= List.Accumulate(myAccumulate,0,(state,current)=>(state+current))
文法は次の通りとなります
=List.Accumulate(リスト名,計算の最初の値,計算式）
話を分かりやすくするために、実際にList.Accumulate関数の計算履歴を追ってみたいと思います
計算式1 ⇒state:0(計算の最初の値)+current:1 ⇒1
計算式2 ⇒state:1(計算式1)+curernt:2 ⇒3
計算式2 ⇒state:3(計算式2)+current:3 ⇒6
つまり、リストの中身を変数を使用した計算式にて順次計算して累計処理をしているわけです
では、最終式のList.Accumulate関数の中身を実際に分解していきたいと思います
Table.ColumnNames関数
List.Accumulate関数の第一引数はステップ名：myPointです
myPointはTable.ColumNames関数で算出された値です
Table.ColumnNames関数はテーブルの列名をリスト化します
今回はポイントを設定したtablePointクエリの列名をリスト化します
関数の引数はリスト名を指定するだけでOKです
このリストがList.Accumulate関数の第一引数になるということは、計算式（state+current）内の変数：currentには「ポイント1・・・」などのテキストが繰り返し代入されるとこになります
どうやって計算が行われるのか？と不思議になる読者の方もいらっしゃると思います
この点は次のRecord.Field関数の箇所で解説します
Record.Field関数
Record.Field関数は特定のレコード中から特定のフィールドの値を抽出します
ちなみに、上の画像のtablePoint{0}*はtablePointテーブル/クエリの1行目のレコードになります
今度は第二引数を”ポイント2″を指定してみます
ポイント2のフィールドの値が抽出されます
List.Accumulate関数内では「ポイント1,2・」が繰り返し変数：currentに入ります
この変数：currentはRecord.Field関数の第二引数に設定され、数値が抽出されます
計算式の追跡
では、ここでList.Accumulate関数の計算式を追跡してみましょう
(state,current)=>①state+②Record.Field(tablePoint{0},current)*③Record.Field(_,current)*④[評点]
追跡するのは次の行/レコードです
計算式１:state/0(計算の最初の値)、current/”ポイント1″
①：0(計算の最初の値・第二引数)
②：Record.Field(tablePoint{0},”ポイント1″)=0.09
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2が算出されます
ここで注意点です
③の_はカスタム列を作成しているクエリのレコードをそのまま受け継ぎます
今回の場合は次の画像のレコードを受け継ぎます
計算式２:state/7.2(計算式1の値)、current/”ポイント2″
①：7.2
②：Record.Field(tablePoint{0},”ポイント2″)=0.1
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2+8=15.2が算出されます
計算式３:state/15.2(計算式2の値)、current/”ポイント3″
①：15.2
②：Record.Field(tablePoint{0},”ポイント3″)=0.12
③：Record.Field(_,”ポイント”)=0
④：[評点]=80
①+②*③*④の計算式により15.2+0=15.2が算出されます
一見、数式は複雑そうですが、List.Accumulate関数の変数の中身を紐解くとそれほど難しくないことが分かります
＜まとめ＞
今回は列数が可変の複数の表を組み合わせて計算する方法を解説しました
M関数を組み合わせることで、列数を可変にした計算が可能になります
最後に注意点です
let式を使用していますが、「,」を付ける位置を注意する必要があります
inの前、letの最後は「,」を付ける必要がありません
逆にそれ以外のステップは「,」が必要となります
参考までに使用したファイルを添付します
IT未経験からDX推進！
私は30代半ばからの10年間、朝から終電まで退屈なエクセル作業をして過ごしましました。それからシステム会社に45歳で転職し、RPAと出会いました。
ITの世界の常識が大きく変わる予感がしました。業務を理解している担当者自身が、システムエンジニアの力を借りずに、システム開発を効率的に推進していくことができるのではと考えました。
そこから更にPower Queryなどの「モダンエクセル」、Power BIやPower Automate、Power Appsなどの「Power Platform」が登場し、誰もが手軽にデータを有効活用できる世の中が来ることを確信しました。
「このエクセル作業が効率化できたらいいのに・・・」「このエクセルデータから有効な情報を引き出せたらいいのに・・・」と頭を悩ませている人は数多くいらっしゃいます
IT未経験者でもモダンエクエル、Power Platformを正しく活用すれば、自身の退屈なエクセル業務を削減しつつ、データを活用した有意義なビジネスライフが送れるようになります。
私自身は最初はITの世界に飛び込んだ時は用語さえわからず、相当苦労しました。この時に味わった苦労の一つ一つがこれからエクセル作業を改善していこうとしている皆様のお役に立つと思います。
ところで、皆様はブルース・リーをご存知でしょうか？
ブルース・リーはカンフーの神様、先駆者と呼ばれ、ハリウッドで大活躍し、未だに多くのハリウッドスターに尊敬されている香港生まれのアクションスターです
そのブルース・リーがこんな言葉を残したそうです。
「Don’t think.Feel!（考えるより、まずは感じること）」
ぜひ一緒に手を動かしてITを楽しんでいきましょう！
Excellentなレッスン
ストアカにてレッスンを定期的に行っています
「Don’t think.Feel!（考えるより、まずは感じること）」をモットーに丁寧に解説を行います
オンラインに加えて対面レッスンも行っています
🌟ここまでできるPower BI～計算、分析、操作、魅せる～
Power BIの脱初心者になりたい人向け。動くグラフは必見です！
＊ブログ読者優待割引あり、紹介記事はこちら⇒詳細
🌟Power BIをはじよう～マンツーマン/対面・オンライン両方可
これからPower BIを始めたい人向け
30分からの時間制講座です。＊内容・日時等を事前に問合せをお願いします（提供が可能でないこともあります）
新着情報
人気記事
置き換えのIF式を簡略化する方法～リスト活用、列削除省略～
IF式は便利ですが、作成後のメンテナンスが大変だったりします
１つ内容を追加するだけでも、or以降のコードを追加しなくてはなりません
今回は、リストを使ってIF式の作成を簡略化する方法と、IF式自体を列に置き換える方法を紹介します
List.Contains関数
今回はList.Contains関数をIF関数内で活用します
このM関数はリスト内に該当するものがあれば「TRUE」そうでなければ「FALSE」を返します
上のGIF画像でいえば、リスト{1,2,3}に対して３は含まれているのでTRUE、４は含まれていないのでFALSEを返しています
置き換えのIF式にリストを埋め込む
下の画像の商品コードを①か②にグループ分けをしたいとします
以下のようなイメージです
その時は、通常は以下のようなIF式を使ったカスタム列を挿入すると思います
今回は、or式の部分を前述のList.Contains関数で置き換えます
ただこのままだと、①に置き換える商品コードを追加するには、M関数のメンテナンスが必要です
ですので、次に
{“10001″,”10002”}の部分を別途作成するリストで置き換えます
このような感じです
これで、別途作成するリストの内容を置き換えれば、IF関数の出力も変ります
列削除の省略
置き換えのIF式を作成した後、既存の列を削除することもあるかと思います
工夫をすれば、列削除のステップを省略できます
まずは、
ダミーで変換タブから置き換えを行います
置き換えの内容はなんでもいいです
すると、数式タブの内容が次のようになっているはずです
こちらを次のように置き換えます
A:置き換え前の文字列の箇所は「each [商品コード]」とします
A’:置き換え後の文字列の箇所に先ほどのコード「if List.Contains(myCode,[商品コード]) then “①” else “②”」を使います
ここでポイントは「each」をうまく使うことです
eachを使うことで「各々の行」という意味になります
＜まとめ＞
今回は、置き換えのIF式をうまく簡略化する方法を解説しました
Power Queryはとにかく便利ですので、ついついクエリのステップが長くなったり、後でメンテナンスが面倒になったりするケースもあります
今回のように、工夫すれば簡略化できますので、ぜひ活用してみてください
【Power Query豆知識】ちょっとしたM関数の書き方の工夫でステップ数を削減する方法
どうしてもクエリが長くなってしまい、後で見返しても内容がよくわからず困っているという声をよく聞きます
今回は、M関数の書き方を工夫してステップ数を削減する「ちょいテク」を紹介します
カスタム列の追加時
カスタム関数を追加した後に、形式を行うことがあります
下の画像では、上の画像のランク列をテキスト形式に変更しています
このやり方だと、形式変更のステップが追加されてしまいます
このケースの場合は、カスタム列を作成したステップに戻り、数式バーに一文を足します
足した一文はこちらです
,Text.Type
この一文を足すだけで、カスタム列の追加と形式変更（テキスト形式への変更）が同一ステップで行えます
では、整数形式に直す時にはどうコードを書くのか？
いちいちすべてコードを覚えておく必要はありません
下の画像は整数形式に直した時の数式バーの記述です
一度、整数形式に変更するステップを追加した後に「コードを把握」し、カスタム列の追加ステップを修正した後、形式変更のステップを削除すればいいのです
列の削除
ある列を削除した後に、別の列を削除したくなることはよくあります
この時は、最初に列を削除したステップに、2度目の内容を追加します
下の画像は1度目に列を削除した時の数式バーの内容です
こちらの波括弧：{}内に2度目に削除した列名を追加します
これで、2度目に列を削除したステップは不要となります
列名に空欄が入っている時は注意が必要です
この時は列名をコピーした方が早いです
＜まとめ＞
今回は、ちょっとした長いクエリを短くする工夫を紹介しました
今回の内容で一気にステップ数が減るわけではありません
ただ地道に工夫を積み上げると成果には確実につながりますので、ぜひ今回のような工夫を積み上げるようにしましょう
UDEMYの動画コース「Power Queryを極めたい人必見！！M関数講座」の無料・勉強会の開催報告
昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見！！M関数講座」の無料勉強会を開催しました
参加して頂いた皆様には感謝の一言です！
今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました！
今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました
長いクエリの対策
Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります
ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします
この点が、皆でPower Queryを共有して使う時にも問題となります
今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました
そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました
下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを１つのステップに集約することができます
しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります
let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます
クエリの関数化
「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました
今回解説したのは、クエリ自体を関数化する方法です
通常のカスタム関数では各行にて１つの値（販売単価、製造原価、販売個数⇒利益）が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます
今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです
複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます
参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです
確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました
今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます
データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います
＜まとめ＞
今回は本当に貴重な意見をいくつもお聞きすることができました
今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います
Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います
⇒Udemyの動画・詳細はこちらから
