タグ別アーカイブ: Power Query

【M言語実践】グループ別に累計を集計する方法

過去2回の記事でM言語を実践的に活用する事例を紹介しました

この2回の記事で共通するのは「グループ毎に連番を付与」する技術を使用していることです

今回も「グループ毎に連番を付与」する技術を有効活用して、グループ毎に累計を集計します

M関数のList.FirstNと連番をうまく組み合わせれば、意外と簡単に行えます

ポイント

List.FirstNとは?

List.FirstNは指定されたリストから、指定した条件のリストを作成します

文法としては「=List.FirstN(リスト,条件)」と書きます

例えば、

=List.FirstN(リスト,1)とした場合は1個の値が含まれるリストを作成します

=List.FirstN(リスト,2)とした場合は2個の値が含まれるリストを作成します

このList.FirstN関数の第二引数の条件のところに、グループ別の連番を指定することで、累計の元となるリストを作成します

元データ

今回解説に使用する元データは、下の画像のデータです

こちらのデータのグループに、エディタ内で連番をまずは付与します

上記の画像のようにM関数/Table.AddIndexColumnの第一引数にした列/テーブルは、後でList.FirstN関数の第一引数にしますので削除せずに残しておいてください

List.FirstNによるリスト作成

元データに連番を付与したところで、Power Queryエディタ(以降、エディタ)上で、詳細/テーブルの受注金額をM関数の第一引数、連番を第二引数にしてカスタマイズ列を作成してみます

するときちんとリストが作成できています

では、作成されたリストの中味を見てみます

連番(東京)が1の時には、作成した行の値のみが含まれています

では、連番(東京)が2の時はどうでしょう?

1つ上の行の値と該当行の値がリストに含まれています

今度は、大阪の連番1の場合を見てみましょう!

きちんと大阪の1番目の値/1700000のみが含まれています

作成されたリストの合計

List.FirstNにて各行に作成されたリストは累計の元になるリストになります

最後の仕上げとして、こちらのリストをM関数/List.Sumで合計します

すると、下の画像のように累計が算出されます

<まとめ>

 今回は、M関数/List.FirstNとグループ別の連番を組み合わせて、グループ別の累計を集計しました

今回の集計には、行別のリストが度々登場します

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

この辺りは、手を動かして直感的に理解していった方が習得が早いと思います

とにかく手を動かして身に付けていきましょう!
にほんブログ村

ピボット解除を複数列を軸にして実行する

パワークエリのピボット解除はとても便利ですね

 1つ列を軸として選択 ⇒ その他の列のピボット解除、これだけでデータの縦横/↓→並びを縦縦/↓↓に変更できます

でも、複数列を軸にしてピボット解除をする場合にはどうするんだろう?

実はこちらについてもとても簡単にできます

ピボット解除の前に軸にする列を指定するだけです

今回の内容

ピボット解除を行うデータは下の画像です

上の画像の黄色い箇所、カテゴリー地区を軸にして「4月~6月」を横並びから縦並びにかえます

複数列を軸にしたピボット解除

解説はPower Queryエディターからはじめさせて頂きます

ピボット解除を行う前に、軸にする列を下の画像のように選択しておいてください

複数列を同時に選択する場合には、Ctrlキーを押しながら選択してください

このまま「その他の列のピボット解除」をクリックしましょう

そうすれば、選択した列以外の並びが変わります

<まとめ>

ピボット解除はとても便利です

データの並べ替えによるデータのクリーニングはとても重要な作業です。ミスが起こっては困ります

このピボット解除を適切に実行すれば、データクリーニングの質は格段に向上します

今回の内容はぜひ有効活用して頂きたいと思います


にほんブログ村

M言語に慣れる_1回目~コード構造の把握~

【M言語は難しくない!】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう!!

https://youtu.be/HMcsg_B-gm0

 パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

 Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で<赤印の「販売金額」列の1行目の「54000」>の箇所を抽出してみます

エクセルシート上で言えば、「=セル名(例:A9など)」を数式バーに入れる処理です

ちなみに適用したステップには、まだ1つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください!

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

 但し、コード内の角括弧:[]と波括弧:{}の使い方のルールが分からないと小難しく感じてしまうただそれだけなのです

逆に、[]{}の意味が分かるだけでも「かなりのことが行えそう!」と感じませんか?

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前にを伴って付くシート名(例:平均値の計算!A2)みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を3つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

1.コードのカラー

まず、Power Queryエディタ(以降、エディタ)で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード(自動記録)された値

こちらは、エディタ内でステップを追加した際に、ハードコード(自動記録)されたです

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

2.大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう!

3.ステップの引継ぎ

前述の1.で列のマージを行ったエディタ内には、下の画像のように2つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか?

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には<一つ前ステップの内容>を自動的に引き継いでいきます

4.参照ステップ作成

前述の3.ではエディタ内で<一つ前のステップの内容>を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ)カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ)カスタム列の作成

ⅰ)で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

<まとめ>

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

2.大文字と小文字の区別

3.ステップの引継ぎ

 上の3つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、3つの特徴の後には3.ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ)一旦、注文金額の平均値を計算➡参照ステップ

ⅱ)カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ)ⅱ)の処理はエクセルシート上で例えるなら、以下の画像のような処理です

 データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です 

 M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

 では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

記事を最後までお読み下さり、誠にありがとうございました

参考までに解説に使用したエクセルファイルを添付します

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

にほんブログ村

受付終了➡モダンエクセル勉強会_0903

9月3日(金)に「モダンエクセルを実際に使いこなしている方々」にお集まり頂き、「モダンエクセル活用の場を広げる」ための意見交換をしたいと思います!

・主催:Excellent仕事術/当ブログ管理人

・日時:2021年9月3日19時半~20時半(今回は60分です)

・場所:オンライン(ZOOM)

・内容:①主催者発表/約20分、②意見交換及び質疑応答

①主催者発表:パワークエリ/M言語の活用術について

・費用:参加費用は無料です

・申込:以下参照

-記事下のフォームに記入して頂いた後に、ZOOMのURLを送ります

*すぐにURLを送れない事もありますので、その点はご容赦ください

– 先着20名まで

-参加者はモダンエクセルを使いこなしている方に限定させて頂きます

*録画はご遠慮願います、後、途中の入退出もご遠慮ください(やむを得ない事情が発生した場合には主催者にご連絡ください)

*基本は顔出しでお願いします

-頂いた個人情報について、今回開催する勉強会の連絡等の目的には使用せず、厳重に管理致します

参加申し込みの程、お待ちしております

尚、勉強会に関する問い合わせはこちらからお願いします


にほんブログ村

M言語に慣れる_3回目~ダイナミックフィルタリング~

M言語は難しくない!】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう!。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

 ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

 今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

 あくまで、Power Queryエディタ(以降エディタ)にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロ修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです!

目次

今回のポイント

今回使用するデータと行いたいこと

演習1/受注金額1位の担当者を抽出

演習2/最高値の半分未満の担当者抽出

<まとめ>

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

 前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

 クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

今回使用するデータと行いたいこと

今回は次のデータを使用します

演習データ

担当者別に受注金額を管理する表です

 こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります!

演習1/受注金額1位の担当者を抽出

ステップ名を変更

まず、表をテーブル化してエディタを開きます

表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします

エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います

下の画像の「変更された型」を「DATA」に変更しましょう

クエリ名を変更するのと同じ要領でステップ名も変更できます

「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます

ステップ名を変更

なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります

この点は後述します

参照ステップを作成/全体最高値の算出

次に、受注金額の列を選択して「変換タブ」の「統計」から最大値をクリックします

すると上のGIF画像のように、受注金額の最大値が算出されます

ここでも、ステップ名は変更しましょう

この時点では、適用したステップの欄は次の画像のようになっています

最後のステップの「計算された最大」を「Cal-Max」に変更しておきましょう

さて、

ここから、「Cal-Max」を参照ステップとして確定する処理をします

確定処理のために、下の画像の数式マークをクリックします

すると、「Cal-Max」のステップが1つ繰り上がります

上のGIF画像のように「Cal-Maxステップ」の後に「カスタム列1」というステップができます

追加された「カスタム列1」ステップの数式バーは下の画像のように、最大値を算出してある前ステップを参照しています

なお、数式バーが「Cal-Max」ではなく「#”Cal-Max”」というステップ名になっています

こちらは、前述の「ステップ名の変更」でも少し触れましたが、ステップ名が空欄やーなどの記号で文字列が繋げられている場合、#”ステップ名”になります

この点は、必ず意識しておきましょう

「最高値」の担当者を抽出

この<受注金額が「最高値」の担当者を抽出>では主に、3つのことを行います

①前ステップをスキップ ➡ ②最高値をフィルタリング ➡ ③シートへの読込

上記の3つを通じ、前述の参照ステップを活用してダイナミックにフィルタリングを行えるようにします

①前ステップをスキップ

参照ステップを確定した段階では、エディタ内の表示は「Cal-Max」ステップで算出した「最高値」だけが表示されています

最終ステップの「カスタム1」ステップの数式バーの内容を、1つ前の「Cal-Max」をスキップした、「DATA」ステップの参照に変更します

これで、上のGIF画像のようにエディタの表示が、参照ステップを確定する前の表示に戻りました

②最高値をフィルタリング

では、記事の冒頭でも紹介したように、まずはダミー値で受注金額をフィルタリングします

受注金額をフィルタリングするには、まずは下の画像の▼マークをクリックします

それから、下の画像の黄色の箇所にダミーとして、表示されている金額の内の「どれか1つ」を入力します

すると、数式バーが次の画像のような表示になっているはずです

但し、上の画像の右側にある、緑色で自動記録された「50000」はあくまでダミーです

こちらの「50000」を参照ステップの内容に書き換えます

前述したように、#”ステップ名”にしないとエラーになりますので注意が必要です

これで、フィルタリングがダイナミックになったので、エクセルシートに読込みます

③シートへの読込

エクセルシートに読込む前に、担当者名以外の列は削除しておきます

下のGIFが、エクセルシートに読込んだ時の内容です

下の図の左、元データが降順で並んでいるので、受注金額が最高の担当者が正しく抽出されていることがよく分かると思います

では、元データに新たに「既存の最高値」を超える担当者/TESTを加えてみます

そして、エクセルシートに読込んだクエリを更新します

きちんとダイナミックフィルタリングができていることが、上のGIFで確認できました!

演習2/最高値の半分未満の担当者抽

こちらについては、演習1で作成したクエリを複製し、フィルタリング条件を変更するだけで完成です

クエリ複製

エディタ内で、演習1で作成したクエリの上で右クリックし、複製をクリックします

ちなみに、上の画像にあるように演習1のクエリは「Max-Member」というクエリ名で作成してあります

フィルタリング条件変更

前述のクエリの複製を行ったら、下の画像のようにクエリ名をF2キーで変更しておきましょう

クエリ名を変更したら、クエリを開き次の画像の「フィルターされた行」ステップをクリックします

こちらの数式バーにある、条件式の部分を変更します

演習2でフィルタリングに設定する条件は「最高値の半分未満」ですので、上の画像の赤丸部分を次の画像のように変更します

符号についても、変更を行うのがポイントです

シートに読み込み

フィルタリング条件を変更したところで、読込処理を行います

読込先は演習1の下にします

最高値は担当者/TESTの120,000なので、最高値の半分/60,000未満の担当者が正確に抽出されています

では、最高値を130,000に引き上げてみましょう!

そうすると、受注金額が60,000の担当者も抽出対象になるはずです

クエリを更新すると、下のGIFのように受注金額60,000の担当者があらたに抽出されています

これで、演習2で作成したクエリもダイナミックフィルタリングが行えていることを確認できました

<まとめ>

 今回は、前々回に解説した「参照ステップ」をフィルタリングに活用してダイナミックフィルタリングを行うクエリを2つ作成しました

演習1のクエリでは、「最高値」の担当者を自動で抽出できるようにしました

演習2のクエリでは、「最高値」の半額未満の担当者を自動で抽出できるようにしました

 2つのクエリともに、数式バーに記録されたM言語を編集するだけでフィルタリングをダイナミックなものにしました

 今回の解説を通じて、M言語を学習する効果の大きさを感じて頂けたら幸いです

長文に最後までお付き合い頂き誠にありがとうございました

次の回は、カスタム列・作成機能の裏技を使用して、特殊なテンプレートを作成します

https://analytic-vba.com/power-query/m-code/begin-merge-table/

参考までに今回解説したファイルの完成版を添付します

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

にほんブログ村

パワークエリ記事一覧

初級編

タイトル 内容
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/列名リストを作成

分析編

タイトル 内容
POWER QUERYで手軽にABC分析 パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます
RFM分析の「R」を集計する 注文データから注文者(会員)
別に最終注文データを集計できるようにします
RFM分析の「F」を集計する 注文データから注文者(会員)
別に注文頻度を集計できるようにします
RFM分析の「M」を集計してまとめる 注文データから注文者(会員)別に注文金額を集計できるようにします

コラム

タイトル 内容
【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と連携する方法を解説します
ピボット解除を複数列を軸にして実行する 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します

現場発のDXを応援!

実務・現場でのデータ活用を、豊富な経験とモダンエクセルを始めとした実践的なエクセル力で全力応援!

現場の言葉でデータを語ります

 コロナ禍をきっかけとして、ビジネスの現場はオフィスからリモート中心に移行しています。リモート・ワークへの働き方の移行は、実務・現場での「データ」との向き合い方を大きく変えようとしています。リモート・ワークでは、人間の勘ペーパー業務に依存せず、データを重視した働き方が求められています。実務の現場でデータ活用する際に問題となるのは、現場には企業のIT部門やトップコンサルタントの目が届かない、もしくは、理解が困難な世界があることです。Excellent仕事術は、これまでの豊富な経験を活かして実務の現場を明確に見える化し、データ活用の下地を作ります!

何よりもデータを最重視します

 データは事実を雄弁に語ります。データを有効に活用すれば、ビジネスは必ず成長し、個々の生活も豊かになるはずです。但し、データの活用の仕方では事実が正しく見えなくなったり、時間を膨大に浪費してしまい、成果が全くでないこともあります。Excellent仕事術ではデータを最重視し、実務の現場を正しい方向へと導きます!

これまでの豊富な経験を活かします

 Excellent仕事術・ガッツ鶴岡はこれまで、創業型のベンチャーから時代の寵児となった企業まで幅広い環境で働いてきました。業界も「ビックデータ重視」のEC通販から、「人間の勘重視」の食品業界まで、とても幅広く関わらせて頂きました。いずれの環境・業界でも重視してきたのは「データ」「エクセル」です。これまでの経験を活かして、新たなデータ活用の世界を生み出していきます!

新たなエクセル活用術を広めます

 エクセルが誕生してから、20年の月日が経ちます。エクセルはもはや表を作成するだけのツールではありません。データベース/パワークエリを作成したり、ダッシュボードなども作成することができます。

 Excellent仕事術では、これまで海外のエクセル活用事例も数多く研究してきました。海外ではエクセルの機能を実践的に活用している事例が沢山あります。Excellent仕事術では海外の事例を参考にしながら、モダンエクセルなども活用した「初心者から上級者」まで幅広く活用できる「実践的なエクセル術」を研究しながら広めていきます

全力で汗を搔きます

 実務の現場で重要なのは「汗を搔いて体験すること」です。理屈だけでは現場は簡単には変わりません。まずは自らが変わり、率先して「汗を搔く」を重視して実務・現場の「データ活用」を応援していきます!

お仕事の依頼

エクセル新機能一覧

モダンエクセル

モダンエクセルのメインは、Power Queryという技術になります!

 多くのプログラミング言語学習において、まずは最初に「Hellow Word!」を表示するとこから体験します!

Arduinoなどのマイコン工作では「Lチカ」体験です

Power Queryでは、次のような「ETL」体験からスタートです!

Extract ➡ データの抽出

Transform ➡ データの変換

Load ➡ データの読込

まずは、1度「ETL」を体験してみませんか!

必ず、今までは感じなかった新たな発見があるはずです!

初級講座 :データの取得(抽出など)、読込など

中級講座:データの結合などの変換

上級講座:縦横並びの変換などの特殊な変換処理

コラム:PDFファイルをエクセルに変換するなどのテクニックの紹介

M言語

エクセルダッシュボード

 読者の方には、エクセルで数値資料を作成する機会が多い方もいらっしゃると思います。

 実は、数字は見た目です。そして、第一印象で決まります。特に、一目で「数字が細かく詰まっている」と分かる資料はまともに見てもらえません。そして、その資料作成には、膨大な労力がかかってしまっています。

 では、最小の労力で、最大の効果を発揮する資料を作成するにはどうしらいいのか?、その答えは、このダッシュボード術の中にあります。このダッシュボード術は、まるで車や飛行機のコクピットの中にいるような感覚で、一目で【数字を魅せる資料】を作成します。

 このダッシュボード術は、VBAの技術はあまり必要としません。関数の使用も、ごく簡単な関数の組み合わせのみです。一番必要となるのは日本ではあまり紹介されたこなかった、新たなエクセル機能の活用です

エクセルダッシュボード

オプションボタンを活用したダッシュボード

グラフデザイン

 人は見た目が9割と言いますが、グラフも同じです。このコーナーでは、伝わりやすいグラフデザインを手軽に作成できる手法を解説します。グラフデザインの種類は「スピードメーター」「温度計」「画像入り」など、実に様々なデザインを揃えています!

手っ取り早くテンプレートを使って、グラフを作成したいという方のために、テンプレートを集めたコーナーも設けています!

 ⇒グラフデザイン

新ピボットテーブル術~レポート力向上~

 ピボットテーブルはデータを抽出する為の機能として良く知られています。表は別に作成しておき、ピボットテーブルで抽出した数字を、別途作成しておいた表に転記するような使い方をよく目にします。

 実は、ピボットテーブルから直接、魅力的な表作成を行うこともできるんです。下の画像のような、魅力的な表も、ピボットテーブルから直接作成できます。このコーナーでは、テーブル機能とあわせて、ピボットテーブル本来の力を活かした技術を紹介していきます。

新ピボットテーブル

エクセル裏技

 エクセルには日本ではまだあまり知られていない機能が沢山あります。下のGIF画像をご覧ください。実はエクセルでタイマーも作れてしまうのです。しかも、ほんの数行のコードを書くだけで作成することができます。この位はまだ序の口です。このコーナーでは、隠れたエクセルの裏技をどんどん紹介していきます。そして、どんどん業務の省力化と、差別化を行っていきましょう!

 ⇒裏技

VBA

いよいよプログラミング教育が日本でも本格化しています

このExcellent仕事術では、これまでになかった手法でエクセルVBAを広めていきます

RPA 

 RPA(ロボティクス・プロセス・オートメーション)が急速にビジネスの現場に浸透してきています。

 人間が行っていたエクセル作業をRPAが代わりに行ってくれるから⇒これからはあんまりエクセルの技術習得にそんなに力を入れなくていいや・・・そんな風に考えている方もいらっしゃるかと思います。

 実はRPAの浸透により、逆にエクセルの存在感が増してきています。その理由は、“RPAはデジタルデータにより動く労働者”、という点にあります。デジタル労働者はデジタルデータがないと働きません。

 多くの業務の現場では、デジタルデータは、エクセルにより作成されています。つまり、RPAをエクセルとどう連携するかが、RPA活用の鍵になっていきます。

 このコーナーではこの”RPAとエクセルとの連携”をテーマに、各種連携手法を紹介していきます。⇒RPA


アプリ/アドイン

 エクセルでもスマホのように、アプリ/アドインをダウンロードすることができます

 ダウンロードできるアプリの内容は、グラフを自動作成するものから、長い数式を解読するものまで多岐に亘ります

ぜひ、下の記事を参照してご自身のニーズにあったものを見つけてみてください

アプリ

エクセル分析

エクセルには、ソルバーをはじめとする優れた分析機能があります

 ソルバーは、人間が手動で計算したら2、3日は要するようなシミュレーションを一瞬で終了してくれる優れものです

 このブログでは、エクセルの便利機能を組み合わせて、「ABC分析を効率的に実施する方法」などの最新のエクセル分析術を紹介していきます!

エクセル分析

エクセルと英語力

実は、英語力とエクセル力は相関関係にあります

エクセルを作成したのは、英語圏の企業です

エクセルを使う上で、英語の概念が必ず登場します

ですので、英語ができた方がエクセルの上達も早いです

 そもそもエクセルと英語、どちらもビジネスをしていく上ではとても重要なスキルです

 このブログでは、英語力の向上について貢献できるような記事をアップしていきます

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

にほんブログ村