タグ別アーカイブ: Power Query

意外にも知られていないエクセルの便利機能・便利技~ガッツの日記 第13回

【エクセルはあまりに機能が多すぎるので、知られていない便利機能・便利技があります】

仕事柄、色々なところで色々なエクセルの活用の仕方に出会います

エクセルの活用者と話していて気づくのは、意外にも知られていない機能や便利技が結構あることです

今回の記事では「便利」だけど意外に「あまり知られていない」機能や便利技について、私の独断と偏見でピックアップして紹介します

計算の手動化

ワークシート内に数式を膨大に使用している場合、セルに入力するたびに膨大な計算が走ります

ですので、ワークシートが所謂「重たい」という状態になります

この場合、解決方法としては本来はデータベース等を導入することが考えられます

ただすぐに導入できる方法もあります

それが「計算の手動化」です

文字通り、数式の計算を止めます

そして、必要な時だけ計算します

数式タブには「計算方法の設定」というメニューがあり、こちらで数式の計算を止めることができます

ちなみに、通常は「自動」になっています

そして必要な時だけ「F9」ボタンを押せばファイル内(Shiftを押しながらだとシート内)の関数が計算されます

いや、入力するたびに計算結果が見たいんです、もしくは数式の計算をシート内に絞っても計算が遅いんです、という場合もあると思います

この場合は、VBAを使う方法があります

詳細はこの記事では書きませんが、VBAコードを入力というイベントに応じて動かす方法があります(イベントプロシージャーと言います)

イベントプロシージャーは、該当シートで右クリックすると表示される「コードの表示」で設定できます

次に開いた画面では、下の画像のように画面上の2つの欄を「Worksheet」「Change」に設定します

これで、入力したセルの行位置及び列位置を取得できます

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myCol As Long

myRow = Target.Row ←行位置
myCol = Target.Column ←列位置

下の画像の事例では「手動で計算」の設定のままC列に入力した場合、1つ右のセルだけ計算されます

実際のVBAコードは次のようになります

If myCol = 3 Then
ActiveSheet.Cells(myRow, myCol + 1).Calculate

End If

入力位置が3列目(C列)だった場合、該当シートの入力位置がの一つ右を再計算(Calculate)

するわけです

これであれば、入力位置の行だけを計算できますので、かなり計算は軽くなります

テーブル機能

この機能を知ったのは恐らく5年ぐらい前だと思いますが、意外と浸透していないという実感があります

データが集まった表をテーブルにすると、何かと表内のデータを便利に扱えるようになります

下の画像では、1つのセルに関数を入力したことで、全ての行に関数が反映されています

つまり、データを扱う単位がセルではなく行単位になっています

テーブル機能には、上記のような便利な機能が満載なのです

この「満載」を具体的な一言で言い換えると、位置から名前への変換ということかもしれません

従来、エクセルを使おうとしたらセル単位という縛りの中でしか動けません

A列の5行目、C列の10行目といった感じでセルの位置をいちいち指定するしかありません

例えば、以下の画像のB列の合計を計算しようとしたら、マウスを動かしてC列の該当範囲を選択するしかありません

しかも、C列の範囲が拡大したら選択し直す必要があります

ところが、テーブル機能を使えば名前でセル位置を指定できます

テーブル機能では、作成したテーブルに名前を付けることができ、テーブル内の列名も自動的に活用できるからです

実は列名の指定もワンクリックでできます

上の画像のように、テーブルの該当「列名」の上にカーソルを置き、すっと上に少し動かすと、下向きの矢印がでます

これでテーブルの列名が指定できます

テーブルの列名で指定すれば、列を移動しようが、行の範囲が拡大しようが適切な範囲を合計できます

このようにテーブル機能は便利な機能が満載ですが、表をテーブルにするのは「Ctrl+T」のショートカットキー一つだけで行えます

とにかく簡単にテーブル機能は活用できますので、ぜひ今日から使用してみてください(→詳細記事

表をテーブルにしたら、テーブルに名前を付けるのもお忘れなく!

最後に、もう一点だけ付け加えさせてください

表をテーブルにするとMicrosoft のメニュー自体をうまく扱えるようになるという点です

これは具体的にはどういうことかというと、Microsoft のPower Platformではエクセルのデータをテーブルで使用するように設計されているからです

Power Appsなんかもテーブルからアプリがワンクリックで作れるようになっています→関連記事

ぜひテーブル機能は使いこなしていきましょう!

クエリの関数化

こちらはPower Queryの活用の仕方の話です

関数というと、SUM関数とかVLOOKUP関数を思い浮かべる方が多いと思います

数学でいうと、Xを使った(X+1)x(X+2)などの公式が関数と言えます

Xの値が何になっても一度作成した関数を使いまわして計算が行えます

クエリの関数化とは、一度作成したクエリをXを絡める関数にして使いまわすという意味です

Power Queryは大分、浸透したと思いますが、こちらのクエリの関数化はまだ知名度はいまいちという感じです

どういう場面で使うかというと、一番使えるのがシート別もしくはファイル別にデータが散らばっているケースです

例えば、以下のようにシート別にデータが散らばっていたとします

しかも、欲しいデータの上に無駄なデータがあります

これがもし、100シートあったとしたらデータをまとめあげる作業はどう行いますか?

手動であればお手上げだと思います

ところが、

クエリの関数化であれば1シートに対して行った処理を、ほぼワンクリックで複数シートに使いまわすことにより、簡単にデータの一元化が行えます

詳細についてはこちらの記事(→クエリの関数化)を参照して頂くとして、今回の記事では概念だけ解説いたします

上の画像は1シートからデータを取得し、無駄な2行を取り除いてテーブルを出力するクエリのソースコードです

実は黄色の箇所に「A」というシート名が入っています

つまり、ファイルの中のシートを取得した中からAシートを処理しているという訳です

この黄色の箇所を数学の関数でいうXに代えるわけです

以下の画像は関数化したクエリの一部です

このXにどういう風に各シートを連続的に割り当てるかというと、このソースステップの列を使用します

今回の事例では、実際にはItem列にある各シート名をフィルターして使用します

これでシート別などにデータが分散している問題をVBAなしで一気に解決できます

フリーアドイン

フリーアドイン機能もあまり知られていないと思います

スマホの便利アプリを利用するような形で、外部から便利機能を補って使用できます

こちらについては、アドインが沢山あるので「Formula Forge」というアプリの概要だけ今回は紹介します

例えば、こんな複雑なIF関数があったとします

読者の皆さんの中には「こんなの序の口だよ」と思われた方もいらっしゃると思います

Formula Forgeを使うと複雑な数式でも図解を一瞬でしてくれます

前任者の複雑なエクセルを引きついで苦しんでいる方にはおすすめのアドインです

<まとめ>

今回は意外と知られていない便利機能・便利技を大きく分けて4種類を紹介しました

とにかく知っているか知っていないかでこの辺りは差が大きく出ますのでぜひ押さえておいて欲しいところです

ところで、

私が思うに、エクセルの機能を最大限使いこなすために一番大事なことは「エクセルの限界」を知ることだと思います

どうしてもエクセルの方が相対的に劣る機能があります

例えば、エクセルは表計算ソフトなので大量のデータを扱うのはあまり得意でないですし、Power Platformなどかなり便利なクラウド機能も出てきています

ですので、最終的にはエクセルの機能を深堀するだけでなく「正しい脱エクセル」も意識する必要があると思っています

この点はまた別途詳しく書きたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

フォルダ内の最新ファイルのみを処理して出力するクエリを作成する

【取得するファイルを自動で可変にする、究極のPower Query術です!】

この記事を見ている読者の方の中には、毎日システムから出力されるファイルを処理しているという方もいらっしゃると思います

この場合、Power Queryで出力ファイルを処理する場合は、既存のクエリのデータソースを変更するか、新規にクエリを作成するしかありません

実は、フォルダ取得のクエリ技術を利用すれば驚くほど簡単に「常に最新」のファイルを取得できます

フォルダ取得

下の画像のフォルダには拡張子が「xlsx」のファイルと「csv」のファイルが1つづつ格納されています

こちらのフォルダを取得先にしてクエリを作成してみます

上の画像では、3行目のcsvのファイルに対応する箇所がエラーになっています

ソースステップでフィルター

上の画像では最終ステップがエラーになっていました

ここで最初のステップ、「ソース」を選択してみます

そうすると、フォルダ内のファイルが一覧化されています

では、もっと右にスクロールしてみます

そうすると「Date created」という列があります

こちらの列では該当のフォルダにファイルが格納された時刻が記載されています

今、記事を書いている時間は「6月26日17時01分」なのですが、試しにフォルダに新たなファイルを格納してみます

そして、Power Queryエディターのプレビューの更新処理を行ってみます

すると新たなファイルが「17時01分」に追加されています

ですので、こちらの「Date created」の列でフィルターを行うと、常に最新のファイルだけを処理することができるのです

ここで「最も早い」「最も遅い」という日本語訳が分かりにくいかもしれません

あくまで「最も遅い」、つまりファイルが格納された時刻が最も遅いものが最新のものになります

これで最新のファイルを取得する仕組みは完成です

もし、エクセルファイル(xlsx)の中でフィルターしたいという場合には、先に拡張子でのフィルターを入れておきましょう

その後に「Data created」列のフィルターを入れます

では、もう一度試してみましょう

今、フォルダがこの状態です

このフォルダに「担当_田中.xlsx」ファイルを格納します

そうすると、エクセルファイルで最新の「担当_田中」のデータだけがクエリに反映されます

<まとめ>

今回は最新のファイルだけを反映するクエリを作成しました

ポイントは2つです

1.フォルダからファイルを取得すること

2.作成したクエリのソースステップでフィルタ処理をすること

驚くほど簡単ですよね

最後に、場合によってはファイルの名前、つまり「name」列でフィルターが必要なケースがあることもお伝えしておきます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Queryに強くなる~M言語/コードに慣れましょう~ 第10回

【Power Queryはあくまでコードでできています。これが今回のポイントです】

Power Queryはとても便利です。メニューをクリック操作していくだけでかなりのことができます

但し、メニュー操作に慣れてしまうと、エラーが起こった時に対処方法が分からなかったりします

そして、もう一つ問題があります

Power Queryが登場してから数年が経ちました

他の方が作成したPower Queryをメンテナンスする機会も増えていくと思います

この時、コード自体を理解できないとメンテナンスは行いにくいです

ですので、これからはPower Queryで書かれているMコードを理解できることも重要になっていくと思います

私も他の方が作成したPower Queryを修正する仕事をしていますが、Mコードを直接理解できないと仕事になりません

今回の記事を通じて、Mコードに慣れる機会にして頂けると嬉しいです

但し、今回の記事ではMコードを書くことは意図していません

あくまで、Mコードを読めるように訓練していく必要性と、Mコードを読む勘所を掴んで頂きたいと思います

今回のポイント

Power QueryのMコードはStepが連なってできています

ここがM言語の最大のポイントです

しかも前後左右、Step間で整合性が取られています

このStepに慣れるとそのままM言語に慣れることができます

Stepの中身の確認

ここからは実際に同じように手を動かしてみて頂くと分かりやすいと思います

たとえば、以下の画像のような平均金額を計算したPower Queryがあるとします

もちろんStepは「ソース」からはじまっています

Stepの中身は2つの方法で見ることができます

一つは数式バーです

二つめは詳細エディターです

こちらはホームタブから見ることができます

詳細エディターをクリックするとコードを丸ごと見ることができます

コードの中身を見て頂くと前後、Stepがつながっているのが良くわかります

let
①ソース = Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content],
②変更された型 = Table.TransformColumnTypes(①ソース,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),
③計算された平均 = List.Average(②変更された型[売上金額])
in
③計算された平均

Stepの意味合い

試しにこちらの数式の中身を「ソース」に変えてみます

画像に alt 属性が指定されていません。ファイル名: image-40.png

そうするとどうなるでしょう?

表示はソースStepの内容になります

つまり、Step名自体に意味があります

Step名そのものが「データテーブル」を意味します

よくあるエラー

ここからは実践的な内容として、よく起こるエラーと対策について紹介します

ソースの変更

ソースステップの数式を見てみましょう!

数式の中身は次のようになっています

= Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content]

試しにデータソースとなっている「売上データ」を「売上データ2」に変えてみましょう!

すると次の画像のようなエラーが発生します

この手のエラーはよく発生します

この場合、元のテーブル名に戻すか次のように数式を変更します

そうすればエラーは解消されます

変更された型

今度はソースステップで列名を変更してみます

そうすると以下の画像のようなエラーがでます

こちらはよく起こるタイプのエラーです

ところで「変更された型」Stepとはなんでしょう?

= Table.TransformColumnTypes(#”名前が変更された列 “,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}})

この「変更された型」Stepでは、各列の形式を設定しています

ここで1つ前にStepも含めて「変更された型」Stepを見てみましょう!

①名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
変更された型 = Table.TransformColumnTypes(名前が変更された列,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),

①名前が変更された列Stepでは「商品」列を「商品2」列に列名を変更しています

次の①変更された型では「Table.TransformColumnTypes(名前が変更された列」となっているので、①の名前が変更された列のテーブルデータを引き継いでます

つまり、「商品2」の列名を持つテーブルデータを引き継いでます

でも、②変更された型Stepでは商品列を「type text」、文字列形式にしています

{“商品”, type text}

ということは、既に存在しない列名を文字列形式に変更しようとしています

ですので、前述のエラーメッセージが出るわけです

画像に alt 属性が指定されていません。ファイル名: image-49.png

ではどうやってエラーを解消したらいいでしょう?

一番シンプルなのは変更された型Stepを削除することです

これで、Step間の整合性が取れるからです

let
ソース = Excel.CurrentWorkbook(){[Name=”売上データ2″]}[Content],
名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
計算された平均 = List.Average(名前が変更された列[売上金額])
in
計算された平均

では、どうやって形式変更を行ったらいいでしょうか?

手動で各列を変更するか、すべての列を選択した上で「データ型の検出」を行います

ちなみに、「変更された型」の自動設定を解除したかったら、ファイルタブから「クエリのオプション」を選択します

次に開く画面で「データの読み込み」から「非構造化ソース・・・を検出しない」を設定しておきます

そうすれば、次から「変更された型」Stepは自動で設定されなくなります

<まとめ>

今回の記事では、M言語/コードに慣れるきっかけになるような事例を幾つか紹介させて頂きました

この記事だけで飛躍的にPower Queryの技術が向上するわけではないですが、コードに注目していけば継続的に技術が向上していくはずです

今回の記事は本当に触りだけですが、本格的にM言語を学びたい方はこちらの記事をぜひご利用ください

尚、Udemyで動画コース「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしています

期間限定ですが、無料クーポンを提供させて頂きますので、ぜひご利用ください!

終了したら、レビュー投稿の程よろしくお願いします!

今後の参考にさせて頂きます!!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

クエリの関数化事例~複数シートを一括で編集する

【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】

エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります

一番、困るのは各シートにヘッダーデータがあるようなケースです

このような場合はまずは1シートを処理するクエリを作成します

そして、そのクエリを関数化します

関数化するということは、Y=2X+1のような式で表せるということです

こちらのXには複数シート名を入れて使いまわせるようにします

SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます

今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります

使用データ

前述のように各シートにはヘッダーデータがあります

その下に欲しい一括処理したいデータがあります

そして、上記のようなシートがファイル内に複数あります

各シートの開始行などは一致している前提です

上の画像は東京のシートですが、次の画像は大阪のシートです

関数化するクエリの作成

まずは1シートを処理するクエリを作成します

こちらのクエリを前述のように関数化します

今回はテーブル化からはじめず、ファイルからデータを取得します

指定するファイルは「今処理しているファイル」です

ファイルを指定したら、ナビゲーター画面が開きます

こちらでは1つのシートを指定し「データの変換」をクリックします

ここでクエリは複製しておきます(1つは関数を作成した後に、関数を適用するクエリになります

下の画像がPower Queryエディタを開いた状態です

必要なのは11行目から下です

こちらは「行の削除」アクションで10行削除します

ここからもう1アクションあります

変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます

これで関数化のもとになるクエリは完成です

関数化

関数化は詳細エディタで行います

具体的に関数化する箇所は「可変の箇所」、シート名の箇所です

ここでソースステップの内容を確認しておきましょう

要はファイル内の複数シートを取得しています

ここからナビゲーション画面で1つのシートを選択しています

ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです

では実際に記号Xを埋め込んでみましょう

(X)=>はXを記号として使用するという合図になります⇒詳細

ここまで行うとクエリが関数に変ります

関数は「myFunction」と名前を変えておきましょう

ここでパラメーターに「名古屋」と入れてみましょう

これで名古屋のシートデータが変換されて出力されます

関数の適用

ここで関数の複製元のクエリを選択し直します

その後は、適用したステップでソースデータを残して後は全て削除します

ここから列の追加タブで作成済みの関数を呼び出します

関数のXに入るのは「Item」になります

これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります

結果、次の画像のようになります

ここからmyFunctionを展開すれば完成です

次の画像が展開した後のものです

<まとめ>

今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました

クエリの関数化のポイントは可変の箇所を見つけることです

今回は、1度作成したクエリから可変の箇所を見つけました

そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います

もう少し詳しく「クエリの関数化」について知りたいという方はぜひUdemy講座(動画)もご利用ください

期間限定で「4,800⇒1,200」の割引クーポンが利用できます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Queryならではのカレンダーテーブル活用~経過営業日の算出

【M言語独自のリスト作成を通じて、経過営業日を自由自在に算出する】

エクセルのワークシートには経過営業日を計算してくれる便利な関数があります。Power Queryではその手の関数はないですが、独自のカレンダーテーブル活用により自由自在に計算方法をカスタマイズできます

上の画像のような連続した値を作成する方法は過去の記事でも紹介しています

今回の記事ではこういったPower Query・M言語独自のテクニックが詰まった内容になっています

ポイント

今回のポイントは関数などで一律に計算するのではなく、日付を並べたうえで計算を行います

上の画像のように該当する日付を並べて、曜日に変換し「0,1」を並べます

そして、最後に「0,1」の列を合計します

この合計が経過営業日です

ですので、後から条件式の追加などが容易です

上の画像では祝日が考慮されていませんが、祝日の考慮はもちろん、その他の条件の考慮も容易に行えます

カレンダーテーブルの作成

カレンダーテーブルの作成はリスト作成⇒テーブルに変換の流れで作成します

「..」を使用すると連続した値のリストが作成できます

この仕組みを利用して、他クエリからまずは連続した日付のリストを作成します

他のクエリとは下の画像のような2つの日付を記したクエリになります

期間_単数

こちらのクエリの各列の1行目を参照して、空のクエリでリストを作成します

*{0}で1行目を参照します

ここではNumber.From関数で一旦数値に変換しています

= {Number.From(期間単数[期間A]{0})..Number.From(期間単数[期間B]{0})}

連続した数値を作成した後は、テーブルに変換します

後は日付形式に変換を行います

曜日への変換

曜日への変換は「Date.DayOfWeekName関数」を使用します

曜日だけではなく、祝日も考慮が必要です

祝日については別途テーブルを作成しておく必要があります

こちらは作成した空のクエリのマージします

そして、曜日との集約を「条件列」で行います

これで「0,.1」を並べる列を作成する準備ができました

0,1列の作成及び合計

0,1を並べる列はカスタム列作成画面でif関数を使用して作成します

これで0,1が並ぶ列/Digitを作成することができました

尚、上記の式ですと「起算日の曜日」が休日でない場合には自動的に計算に含まれています

それでは本題に戻りますが、最後にこちらのDigit列を対象にして、変換タブから合計処理を行います

これで経過営業日を算出できました

ちなみに祝日クエリをマージした時に、日付の並びが変わってしまいますので、並び替えを行っておくと計算過程が分かりやすいです

計算方法の調整

起算日には曜日・祝日に関わらず計算に入れる(0を立てる)、もしくは計算に入れないなどの調整にはインデックス列を使用します

上記の画像のように、起算日の行になるのは「インデックス列」の1になります

ですので、if式をインデックスが1の時には値が0(もしくは1)になるように修正します

逆に計算の終点の日を曜日・祝日に関わらずに計算に入れる場合にはLis.Max関数を使用します

複数行計算する場合

複数行にて計算する場合には、カスタム関数を使用します

前述のクエリの詳細エディタを開き、コードに記号を埋め込んで関数化します

={Number.From(myDate[起算日A]{0})..Number.From(myDate[起算日B]{0})}

⇒{Number.From(A)..Number.From(B)},

上の画像のAとBの記号が、下の画像の各列と対応します

そして、記号を埋め込んで完了ボタンを押すと、関数は完成です

こちらの関数を複数行あるクエリで呼び出します

この際、前述の記号に各該当列を割り当てます

これで、各行に関数が割り当てられます

後は関数で作成した列を展開して終了です

<まとめ>

今回は経過営業日をPower Query独自のカレンダーテーブルにより算出する方法を解説しました

もしかしたら、上記の内容で「この画像の起算日の計算式は?」という内容もあるかもしれませんが、ご容赦頂ければと思います

今回の方法ですと、ワークシート関数で一律に計算するより、分かりやすく、カスタマイズしやすいです

ぜひ、手を動かしながら試してみて頂きたいと存じます!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

(無料期間終了)Udemy講座のリニューアル及び無料クーポンのプレゼントについて

昨年、Udemyにて「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしました。但し、多くの意見・指摘が寄せられたため今回、全面的に動画を撮り直しました

この機会に無料クーポンをこのブログ読者の皆さんにプレゼントしますので、ぜひレビュー投稿をお願い致します

頂いた意見・指摘

これまで多くの意見・厳しい御指摘が寄せられました

一方で、それだけこのテーマについては期待が高かったことを認識するとともに、期待を大きく裏切り大変申し訳ないことをしたと反省しております

これらの指摘・意見を参考にしてほぼすべての動画を撮り直しています

リニューアルポイント

動画の本数は以前と変わりませんが、動画は一から撮り直しました

動画によっては3倍の長さで撮り直しています

動画撮影にあたってはとにかく「間」を取りながら、動画のメリットをだせるように最新の注意を払いました

・作成するクエリの内容を丁寧に説明する

・前段でポイントとなる箇所を図で解説する

・操作動画は事前アナウンスをしながらゆっくりPC操作を行う

こちらがサンプル動画です

https://youtu.be/m53fBnCyJgA

レビュー投稿をお願いします!

講座の内容としては他ではなかなか学べない「Power QueryのM言語、M関数を実践的に使うためのテクニック」が詰まった内容となっています

しかも、コードを一から書くわけではないのでそれほど難しくはありません

この講座が終了した時には、Power Queryの凄さや面白さを体感して頂ける内容だと自負しています

こちらが講座の無料クーポンとなります⇒お申込み

ぜひ、講座をご覧いただきレビュー投稿をお願いします

すぐにではないですが、頂いた意見は次に活かしていきます

尚、無料期間は突然終了する可能性がある点は予めご了承ください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

パワークエリ記事一覧

M関数逆引き辞典はこちらから!

初級編

タイトル 内容
Power Queryで操作はどう変わる?~初級講座1回目 パワークエリとはなにか?そのメリットをスマホに例えて解説します
シートからデータ取得~ 初級講座2回目~ 別シートのデータをテーブル化して取得する方法を解説します
ファイルからデータ取得 初級講座3回目 別ファイルのデータをテーブル化して取得する方法を解説します
フォルダから複数ファイルデータを一括取得~初級講座4回目 フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します

中級編

タイトル 内容
複数データの組み合わせ/クエリのマージ~中級編1回目~ クエリのマージ技術の概要について解説します
クエリのマージ/項目追加~中級編2回目~ 複数のクエリをマージする方法について、実際のデータで解説します
クエリのマージ/計算処理~中級編3回目~ マージした項目を活用して計算処理を行う方法を解説します
クエリの追加作成~中級編4回目~ 同じ項目のクエリを追加して結合する方法を解説します
クエリの追加・結合~中級編5回目~ クエリの追加をピボットテーブルと連携して行う方法を解説します
ピボット解除/縦横並びの変換処理~中級編6回目 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します
セル分割他・変換処理~中級編7回目 複数のデータが1つのセルに入っている場合に分割する方法を解説します
セル分割他・変換処理2~中級編8回目 前回の内容を使いやすい形式にして出力する方法を解説します
エディター内のやり直し操作~中級編9回目~ ワークシートにおけるCtrl+Zに相当する処理の方法を解説します
変換した列の追加~中級編10回目~ 連番、条件列(IF関数に相当)及び列削除・移動他を解説します
グループ化1~中級編11回目~ データを階層化して集計する方法を解説します
複数条件でグループ化~中級編12回目~ データを複数の条件で階層化する方法を解説します

上級編

タイトル 内容
行削除とヘッダー行の調整~上級編1回目~ 見出し行/ヘッダーがおかしい場合の対処方法を解説します
セル結合により見出しが2行の表をデータ活用1~上級編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回目~特殊テンプレート作成~ 2つのクエリを組み合わせて、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回目~複数行を1つのセルにまとめる グループ毎のテーブルを作成後、グループ別に1つのセルにテーブルの内容をまとめる方法を解説します
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/合計値を抽出
指定した範囲の値を使用してフィルタリング M関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します 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をクラウドで活用します
(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている? ピボット解除と複合キーを活用して2つの表を比較しやすくします
どの行が追加、削除されている?~結合の6種類~ 結合の各種類をつかいこなせば、行の追加や行の削除の状況が一目でわかります
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 行列双方向にセル結合がある表をテーブル形式に直します
【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法 実は行列の入れ替えを行えば、列方向でのフィルター処理が可能です
【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する 余りの計算とピボット機能を組み合わせて1列のデータから複数列のテーブルデータを作成します
【M言語実践】グループ別に累計を集計する方法 グループ別に累計値を集計する方法を解説します
【M言語実践】顧客の2回目のリピート率を分析する方法 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します
M言語実践】グループ別にIDを設定 グループ別に連番を採番した上で桁数を揃えてIDを作成します
マスタデータの履歴管理を行う方法 マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します
POWER QUERYのステップ数を減らす3つの工夫 「変更された型」のステップを減らすなどの工夫を解説します
エディタからデータ入力してクエリ作成 エディタ上で直接データを入力する方法を解説します
【豆知識】どこの行が変わったかを調べる方法 クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します
【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 ピボット解除と別な方向で並べ替える方法を解説します
【豆知識】ピポット解除時の空欄の扱い グループ化
【分析作業用】大量データを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って何??9~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の指定を小文字で行うのがポイントです