タグ別アーカイブ: モダンエクセル

【M言語実践】グループ別に指定桁数にて連番を付与してIDを設定する裏技

 パワークエリはとても便利です。利用者もどんどん増えていると思います。ただ、パワークエリの言語のM言語となると「何ができるの?」となると思いますので、今回は実践での活用例を紹介したいと思います

今回は下の画像の表にある商品に、「グループ別に1から始まる番号」で採番した連番を付与した商品IDを付けたいと思います

今回のポイント

今回はM言語を使用して「グループ別に連番」を付与した後に、”0”で桁数を揃えます

グループ別に連番を付与する方法については過去の記事を参照してください!

こちらは、グループ化機能とM関数のTable.AddIndexColumnを組み合わせます!!

では「”0”で桁数を揃える」という点について、詳細に解説します

例えば、連番が2の場合には「002」にします。もし10の場合には「010」とします

 こちらの桁数を揃える方法については、「例からの列」機能を活用する方法もありますが、今回はM関数の「Text.PadStart」を使用します

このText.PadStartは下のように使います

=Text.PadStar(文字列,桁数,”補う文字列”)

今回は、”0”を補って下の画像のように指定の桁数にします

上の画像では、”1”に”0”を4つ補って5桁にしています

それでは、今回のポイントとなるM関数/Text.PadStartを解説したところで、実際の商品コードの設定に入りたいと思います

文字コードの付与

 今回は、商品グループがCDの場合には「A」、DVDの場合には「B」という風に、商品コードの一桁目を商品グループで表現します

この場合は、条件列を使用するのが一番、手っ取り早いです

例えば、商品グループ名がCDに等しい場合は、Aを出力するといった具合で条件列を設定します

グループ別連番の付与

こちらについての詳細については、過去の記事をご参照願います

概要だけを解説しますと、まずは「すべての行」にてグループ化を行います

すると、グループ別にテーブルが作成されます

その後、カスタム列・作成画面でTable.AddIndexColumnを追加してグループ別に連番を作成します

上の画面でカスタム列を作成した結果が次の画像です

M関数により”0”を補う

 では、グループ別に連番を作成できたので、グループコード/A,B,Cと連番を組み合わせて4桁の商品コードを作成します

グループコード以降は”0”を補って3桁(全体で4桁)にします

M関数/Text.PadStartは次のように使用します

=Text.PadStart(Text.From([連番]),3,”0″)

1番目の引数で「Text.From関数」にて文字列にするのがポイントです

これで”0”を補った3桁が作成できました

この3桁をグループコード/と結合(列のマージ)させれば4桁の商品コードは完成です

次の画像が4桁の商品コードの完成後です

<まとめ>

今回はM言語を使用して、グループ別に1からはじまる連番を採番した上で商品コードを作成しました

ポイントとしてはM関数/Text.PadStartを使用して”0”を指定桁数になるように補う点になります

M関数を使用しない方法もありますが、M関数を使用した方がミスなく作成が行えます

最後に、グループ別に連番を採番する技術はとても実践的な技術です

ぜひ早めに習得しておきましょう!


にほんブログ村

マスタデータの履歴管理を行う方法

パワークエリの「クエリのマージ機能」はとても便利で、エクセル関数のVLOOKUP関数より使いやすいです

「クエリのマージ機能」を有効活用すれば、参照表(マスタデータ)の活用もかなり手軽に行えます

 今回の記事では、「クエリのマージ機能」に加えて「グループ化機能」も活用して、マスタデータを更に有効に活用できるようにする方法を解説します!

マスタデータの履歴管理

マスタデータには顧客マスタや商品マスタなど様々なものがあります

種類は様々でも、絶対的なルールが一つあります

これは、マスタデータには重複があってはならないということです

もし、マスタデータのIDが「1,2,3・・・」と採番されていたとしたら、IDの1が2つあってはならないということです

ところが、

マスタデータの変更履歴を残す必要がある場合があります

例えば、以下の画像のケースです

顧客マスタ

上の画像は顧客マスタです

ID:1番の中尾さんのマスタデータが重複しています

何故かというと、IDが1番の中尾さんが名古屋市から東京に引っ越ししているからです

ただし、データ管理上は前の住所も残しておく必要があります

この場合は、日付が最新(最大)のもののみマスタデータとして表示できるように工夫する必要があります

では次から、日付が最新(最大)のもののみをマスタデータとして表示する為のポイントを2つ紹介します

ポイント

グループ化

パワークエリにはグループ化という機能があり、重複を排除してグループ化しつつ合計処理などの操作を行えます

 今回はグループ化機能の操作を「最大」で指定して、IDの重複がある場合には「最大の日付」のものを抽出できるようにします

複数キーによるマージ

エクセル関数のVLOOKUP関数では、検索値は一つのみ指定できます

パワークエリのマージ機能では、実は、複数列を照合列として指定できます

手順

マスタデータからクエリ作成

解説は、前述のマスタデータをテーブル化してエディタを開くところからははじめさせて頂きます

エディタを開いたら、下の画像の日付の列が時刻表示になってしまっています

こちらは、日付形式に直しておいてください

日付形式に直したら、クエリ名を「顧客マスタ_元」としてください

グループ化による最大日付の抽出

まずは「顧客マスタ_元」クエリを「複製」してクエリ名を「顧客マスタ_グループ化」に変更してください

この後、ポイントで前述したように下の画像のような設定でグループ化を行ってください

グループ化(最大)

グループ化を行うと、前述のID:1番の方の日付が最新の日付になっているはずです

クエリのマージ処理

 では、次にクエリのマージを指定しますが、下の画像の「新規としてクエリをマージ」を指定して、新規にクエリを作成できるようにします

マージ対象のクエリは下の画像のように「顧客マスタ_元」を指定します

ポイントの章で前述したように、複数の列を照合列として指定します

 今回のケースの場合は、IDだけでなく「日付」も指定することで、IDに重複がある場合には、日付も一致したマスタデータのみをマージできるようにします

下の画像のように照合列として複数列を指定する場合には、Ctrlキーを押しながら指定します

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

マージする条件を指定した後は、マージされたクエリの中かから「ID列と日付」以外を展開します

マージされた列を展開した後は、列の順番等を整えてください

<まとめ>

 今回は、パワークエリのグループ化機能とマージ機能をうまく組み合わせてマスタデータの履歴管理を行う方法を解説しました

今回の最大のポイントはグループ化機能です

グループ化を行う際に、操作を「最大」にして日付が最大のもののみを抽出できるようにします

今回解説した内容で一点、注意点があります

マージする際に、グループ化した内容は1番目にくるようにしてください

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

元の顧客マスタは、上の画像にて黄色の印をつけた箇所のように、2番目に来るようにしてください

この順番が狂うと、IDが重複したままになってしまいますので注意が必要です


にほんブログ村

オンライン化に対応した未来を創ります!

【新たなオンラインWEBスクール「HINT」様と新たな「学び」を提供します!】

コロナ禍がもたらしたもの

 新型コロナウイルス感染症が日本で最初に発見されたのは、2020年1月15日だそうです

このコロナ禍により、我々の生活は大きく変化しました

 我々の生活の主体は「リアル」から「オンライン」主体に移行しました

 ネットショッピングを主体とした巣ごもり消費が浸透するとともに、仕事の主戦場が「オフィス」から「リモート」に移行しました

 つまり、我々の生活・仕事はリアルへの対応からオンライン仕様への変化が求められているのです

HINTのサービス

好きな時間に」「学びたい内容だけピンポイントで」「大人数ではなくマンツーマンで

*運営会社:株式会社DEX

 HINTは「学び」についても、ネットショッピングのように手軽に「オンラインで」行えることを目指した新サービスです

 Excellent仕事術では「オンラインで学習するメリット」を追求したこのサービスに参画し、「仕事のオンライン化に対応したエクセル術」の普及を進めます!

オンラインに対応したエクセル術

 オンラインに仕事の主戦場も移行したことにより、個人の仕事の仕方も大きな変更がもたらされています

 会話や人間の経験、勘よりも、データがより重視されるようになりました

 個人が扱うデータ量が増え、データの説明もペーパーではなく、画面を利用して説明する機会が増えました

 一方で、これまでであれば職場でデータの扱いに詳しい人に聞いたり、IT部門に相談して解決できたようなこともなかなか解決が難しくなっています

 Excellent仕事術では「仕事のオンライン化」に対応した新たなエクセル術を、新たなオンラインサービスHINTを通じて提供します!

・エクセル新機能/パワークエリ x データベース理論 ➡ 詳細

 ITのプロの様に、大量のデータを効率良く扱えるようにする「お得な2時間」のレッスンです!

・エクセルダッシュボード ➡ 詳細

 レーシングカーのコクピットにまるで座っているかのように、1画面で組織に必要な数字を魅せるテクニックです!画面内容をボタン一つで切り替えられるので、オンラインでの数字説明に最適です

・関数術 ➡ 詳細

 関数が苦手な人は、関数独自の「同時に複数の参照」が実感できていません

 この同時参照の動きをエクセルシート上で動くアニメで実感できるようにします

新たな未来を創ろう!

コロナ禍がもたらしたのは、実は生活の変化だけではありません

オンラインの浸透により「時間」の感覚が変わってきています

 1年前の常識は通用せず、変化に対応していかないとどんどん取り残されていきます

「思い立ったが吉日」

 もし、この記事を読んで少しでも「響く」ものがあったらぜひご連絡をください!

初心者から上級者まで幅広く対応させて頂きます!

事前問合せは無料です!

新たな未来創りに向け、ぜひお力にならせてください!!


にほんブログ村

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へ

にほんブログ村

M言語に慣れる_2回目~カスタム列~

【M言語は難しくない!】今回は、前回紹介したM言語の特徴を意識しながら、実際に「M関数」を使用したカスタム列を作成してみましょう!

では、前回紹介したM言語の特徴を振り返ってみましょう!

ⅰ.コードのカラー

赤は「文字列」、青はeachなどの「予約語」、緑は「自動記録された値」です

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

M言語は、大文字と小文字の違いを認識します

ⅲ.ステップの引継ぎ

適用したステップは「ステップ名」を通じて、前ステップの内容を引き継ぎます

今回はこの3つの特徴に加え、次の4つ目の特徴も意識しましょう!!

ⅳ.データ形式

 エクセルは表計算ソフトであって、Accessのようなデータベースソフトではありません

 よって、過去の記事で紹介したように、Power Queryではデータ形式を意識した「データの構造化」がエディタを開く際に、自動的に行われます

 当然、Power Queryエディタ(以降、エディタ)内で列を追加する時にも「データ形式」を意識する必要があります

 では、今回意識すべきPower Queryの4つの特徴を確認したところで、M関数を使用した「カスタム列」の追加についての解説を始めたいと思います

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

ファイルデータはこちらになります

アイコン

Mコード2回目_練習 21.42 KB 31 downloads

...

こちらのデータから、次の列をM関数を使用して作成します

A.「姓」と「入会年」を組み合わせたログインID

「入会年」は「整数」のため、M関数を使用して「文字列」に変換します

B.「入会年」「入会月」と「1日」を組み合わせた入会日

M関数にて、3つの整数から日付を作成します

エクセル関数のDATE関数と同じ要領です

なお、上のABの列を作成する前に、ウォーミングアップとこれまでの復習を兼ねて、簡単なカスタム列を作成してみましょう

1.全ての行が「1」の列

まず、データテーブル上で右クリックし、下の画像の黄色の箇所からエディタを開きます

エディタが開いたら、「列の追加」タブから「カスタム列」をクリックします

次に開いた「カスタム列」の画面で「1」を指定します

上の画像のように「1」を指定したら、右下のOKボタンをクリックします

すると全ての行が「1」になっている新たな列「1」が追加されます

では、エディタ画面上の数式バーの中味を詳細に確認してみましょう

まず、()の中の左から1番目の「変更された型」についてです

①ステップ名

こちらは、前ステップの「変更された型」を引き継いでいます

②新しい列名

文字列”1”としてで表示されています

③システム予約語

「each」はシステム予約語なので、で表示されています

④自動記録された値

最後の”1”は自動記録された値なので、で表示されています

①から④まで数式の中味を確認しました

ここで、数式バーに「1」と入力してみましょう

すると、③のeachの意味合いがよく分かると思います

数式バーに1と入力すると「1」が1つだけ表示されます

ですので、「each」というシステム予約語により「それぞれ~」「各々~」のような意味になるのが御理解頂けたと思います

2.ログインID の作成/文字列への変換

2.ログインIDの作成では、記事の冒頭のA.で前述したように「姓」と「入会年」を組み合わせ、ログインIDの列を作成します

なお、「姓」と「入会年」を組み合わせる際には「」を使います

では、1.でも行ったように「カスタム列」の画面から作成を行います

「姓」と「入会年」は、ともに既存の列なので、画面右から挿入します

次の画像の内容で式を記入したら、画面右下のOKボタンをクリックします

ちなみに、上記の内容であれば、画面左下にエラーメッセージは出ません

ところが、出力された新たな列は、全てエラーになります

このエラーが何故発生するかというと、記事の冒頭ので紹介したように、M言語では「データ形式」を意識する必要があるからです

「姓」は「文字列型式」ですが、実は「入会年」は「整数」です

ですので、「入会年」は文字列に変換する必要があります

ここで、M関数を使用します

エクセル関数では次の画像の画面から、該当の関数を探すことができます

M関数でも同じような画面があります

上の画像の「Power Queryの式についての詳細」をクリックすると、下の画像のMicrosoft社の画面に遷移します

上の画像の下に「カテゴリ別の関数」とありますが、こちらの中に「テキスト関数」という関数があります

更に、こちらの「テキスト関数」の中を下にスクロールすると、「Text.From」という関数があります

こちらの関数で「入会年」を「整数」から「文字列」に変換できます

このText関数では、変換対象をText.Form()の”()“の中に入れます

なお、

関数は下の画像の数式バーに直接入力してしまいましょう!

Text関数を使用する際、「Text.Form」の中のTとFのいずれかを小文字にするとエラーになりますので、注意して入力しましょう!

3.入会日の作成/整数から日付作成

こちらは、記事の冒頭のB.にて紹介した、下の画像の「#date」関数を使用した内容になります

こちらのM関数は、#date()()の中に、整数の「年」「月」「日」を設定します

下の画像のエクセル関数の「DATE関数」と同じ要領になりますので、こちらのM関数は取り組みやすいと思います

但し、2.で扱ったText.From関数と違い、この関数は小文字dで始まります

この点については、エラーにならないように注意して入力しましょう!

作成する画面についてですが、こちらは、カスタム列の作成画面で作成します

()の中の最後の「日」は手動で「1」を入力します

新たに作成された列は、次の画像のように出力されます

<まとめ>

今回はカスタム関数の作成画面にて、3つの列を作成しました

1.全て1の列

2.文字列/姓と整数/入会年を組み合わせたログインID

3.整数/「年」「月」「日」を組み合わせた入会日

 1.では、前回、M言語の特徴として紹介した「each」などのコードカラー()についても解説を行いました

 ちなみに、「each」はM言語を扱う際には頻繁に出てきますので、感覚として慣れて置いた方が良いです

 2.では、入会年をM関数のText.Fromを使用して「整数」から「文字列」に変換しました

 記事の冒頭でも紹介したように、M言語では「データ形式」を意識する必要があります

 M言語を扱う際には、今回のようにデータ形式を変換する場面が出てくるはずですので、Text.From関数はぜひ習得しておきましょう!

最後は、#date関数を使用して「入会日」を作成しました

こちらの関数はText.From関数と違い、小文字から始まります

 前回もM言語の特徴として解説しましたが、M言語では大文字と小文字を区別する必要があります

 筆者も何度か、この大文字と小文字の区別を間違えてエラーを出しましたが、意外とエラー原因が些細すぎて原因に気づかないものです

この「大文字と小文字の区別」については特に注意しましょう!

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

参考までに今回使用したファイル(完成版)を添付します

 エディタ画面の「適用したステップ」の表示が、解説に使用した内容とは若干相違がありますので、この点はご了承をお願いします

アイコン

Mコード2回目_完成 24.89 KB 14 downloads

...

では、次回もよろしくお願いします

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

にほんブログ村

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

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

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

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

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

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

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

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

・申込:以下参照

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

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

– 先着20名まで

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

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

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

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

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

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


にほんブログ村

パワークエリ記事一覧

初級編

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

パワークエリエディターで行コピーを行う方法

今回は、通常は行列単位で一括処理を行うパワークエリで「行コピー」を行う方法を解説します

行コピーを行うのに「M言語」が出てきますが、M言語の概念に触れるのにいい題材だと思います

ところで、

空のクエリ波括弧:{}を使用したらどのようになるでしょう?

ちなみに、空のクエリはここから起動できます

*データタブ➡データの取得➡その他のデータソースから➡空のクエリ

では、

試しに、下の画像のように空のクエリの数式タブに{2020,2021}と入力してみます

すると、上の画像のように2020と2021の値から構成されるリストが作成されます

このリストを作成する仕組みを行コピーにも活用します

行コピー

では、実際に行コピーをしてみたいと思います

上のデータがエディタに表示されている状態からカスタム列・作成画面をクリックします

そして、先ほどの波括弧:{2020,2021}を入力します

すると、2020と2021の2つの値から構成されるリストが含まれた列が新たに作成されます

次に、下の画像にて黄色く印を付けた箇所をクリックしてリストを展開します

すると、2020と2021の値に対応するように行コピーが作成されます

もし、波括弧の中味を「2020と2021」でなく「2020,2021,2022」とすれば1行でなく2行がコピーされます

<まとめ>

今回は波括弧:{}を使用してリストを作成した上で、行コピーを行いました

とても簡単な内容ですが、パワークエリをサポートするM言語の仕組みに触れる上ではとてもいい内容です

特に、波括弧の使用の仕方に慣れるだけでもM言語の根幹に触れることができます!

ぜひ実際に手を動かして試してみてください


にほんブログ村

現場発の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へ

にほんブログ村

7月17日勉強会_報告

先週の7月17日にモダンエクセルの勉強会を行いました

多くの皆さんにご出席頂き、誠にありがとうございました

①主催者スピーチ:M言語と分析術について

M言語を活用した操作にて、顧客の2回目のリピート率を簡単に集計する方法を解説しました

アイコン

0717勉強会資料 839.14 KB 12 downloads

...

②パワークエリ・高速化術

今回は先日、Akira Takaoさんからご紹介いただいたTable.Addkeyを簡単に解説しました

③質疑応答

 勉強会後、参加者の方から「パワークエリやM言語は使いこなせば便利なのはわかっているけど、皆さん試行錯誤されているのですね」という声を頂きました

やはりモダンエクセルはまだ歴史が浅いので、操作方法等ががまだ浸透していないところがあります

 例に挙げれば、今回も話題に出た「パワークエリとxls型式が相性が悪いため、xlsの型式のファイルをxlsx形式に保存し直す必要性」等です

勉強会の意義は、そんな実際に活用してみての気づきが共有できる点だと思います

今後も継続して開催していきます!


にほんブログ村