タグ別アーカイブ: M

M言語に慣れる_7回目~空のクエリからテーブル作成~

【M言語はむずかしくない!】今回は、空のクエリから、3種類の括弧を使ってテーブルを作成します。テーブルを作成した後は、M関数も作成を行います!その際、M関数を使いやすくする方法についてもあわせて解説します!!

前回は2種類の括弧を使い、エディタ内で各種参照を行いました

角括弧 / [] ➡ リスト、列の抽出

波括弧 / {} ➡ レコードの抽出

前回で、M言語における2種類の括弧の役割がおぼろげながら見えてきたと思います

 今回、3種類の括弧を使ってテーブルを作成した後は、過去に紹介した方法とは別の方法でM関数を参照する方法についても解説します!

3種類の括弧に慣れると、M言語がグンと理解し易くなりますし、応用も効きます!

ぜひ、実際に手を動かして取り組んでみてください

まずは、空のクエリを作成するところから行います

なお、

他のクエリも後程参照したいので、演習を行う際には、既存クエリが存在するファイルを使用してください

目次

準備作業/空のクエリを作成

リスト作成

レコード作成

テーブル作成

M関数の用法を参照・作成/列名変更

<まとめ>

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

リスト作成

リストは波括弧 / {}を使用して作成します

①値が1個

では、数式バーに={“a”}と入力してみます

リストができ、値がリストに1つ入りました

②値が2個

では、={1,2}と入力してみます

リストができ、値がリストに2つ入りました

③値が10個

こちらは、実際に使う機会がないとは思いますが、エディタ内でデータの構造化が行われていることを肌で感じてもらうために紹介します

={1..10}と入力すると、1から10まで自動的に値が作成されます

④リストが2個:各値1個

波括弧の中にコンマ / ,を介して2つ波括弧を入れると、リストが2個できます

入力はこちらのように入力します

= {{“a”},{“b”}}

上の画像の上下どちらかの”List”をクリックすると、適用したステップ画面に「ナビゲーション」が追加されます

上のGIF画像を見ると、ファイルやフォルダからデータを取得する時に出てくる「ナビゲーター」画面の意味合いが良く分かると思います

⑤リストが2個:各値2個

入力はこちらのように入力します

= {{“a”,”b”},{1,2}}

上の画像の、下のリストをクリックすると、下の画像のようにリスト内容が表示されます

レコード作成

角括弧 / []を使用して作成します

①値1

こちらは=[見出し1=値1]と入力します

①値2

こちらは=[見出し1=値1, 見出し2=値2 ]と入力します

レコードは行方向で通常並んでいますが、今回のように数式バーに直接入力した時は、表示のされ方が列方向になります

テーブル作成

テーブルを作成する場合には、これまでの解説で登場しなかった丸括弧 / ()も使用します

そして、丸括弧のまえに=#tableをつけてます

=#tableとコードを書くことにより、丸括弧 / ()内の内容がテーブルになります

ちなみに#の後に続く、tは小文字のtですので、その点は注意しておきましょう!

①1列:値1

こちらは、=#table({列名1},{{値1}})と入力します

②1列:各列値2

こちらは、= #table({列名1},{{値1},{値2}})と入力します

③2列:各列値2

こちらは、

= #table({列名ⅰ,列名ⅱ},{{ⅰ値1,ⅰ値1},{ⅱ値2,ⅱ値2}})

波括弧の組み合わせが多くなので、上の内容をコピーして修正してみる方法でもいいかもしれません

M関数の用法を参照・作成/列名変更

M関数参照

では、エディタ画面の左から他のクエリを開きます

他のクエリが開いたら、どの列でもいいので、列名変更を行ってみます

すると、数式バーには「Table.RenameColumns」というM関数が表示されているはずです

表示を確認したら、M関数の丸括弧の部分を、下のGIF画像のように全部削除してください

すると、下の画像のようにTable.RenameColumns関数の解説が表示されます

上の画像の解説画面を下にスクロールしていくと、使用例が出ています

これは「CustomerNum」という列名を「CustomerID」に変えるという使用例です

上の画像の使用例には、黄色の印が2か所あります

黄色の箇所の間には、もう一つM関数が入っています

こちらは、通常のテーブルの内容に置き換えておきます

黄色の間の箇所を、置き換えた内容が以下です

Table.RenameColumns(
テーブル名,
{列名変更前, 列名変更後}
)

こちらの内容をコピーして活用し、先ほど空のクエリにて作成したテーブルの列名を変更しましょう!

M関数作成

まずは、「Table.RenameColumns(」の部分を、空クエリで作成したテーブルの前にコピーします

そして、残りの箇所を上の画像の後ろにコピーします

コピーする時には、3行に分かれている内容を1行に集約しておいてください

,
{列名変更前, 列名変更後}
)

コピーした後の画像がこちらです

こちらを既存の列名/氏名と新列名/名前に置き換えます

置き換えた後は、下の画像のように「氏名」の列名の列が「名前」に変更されています

<まとめ>

今回は、大きく分けて2つのことを行いました

 1つ目は、3種類の括弧を使用して、空のクエリにて「リスト」「レコード」「テーブル」を作成しました

 2つ目は、空のクエリで作成した「テーブル」にて列名を変更するM関数を使用しました

 M関数を使用する前に、他クエリで列名変更を行い、記録されたM関数から解説を表示しました

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

そして、こちらの使用例をカスタマイズして、M関数を使用しました

 今回は2回に分けて、3種類の括弧について解説しましたが、括弧の意味・役割をおぼろげながらでも理解できていると、紹介した使用例なども活用しやすくなります

ぜひ、今回の解説を機に3種類の括弧に強くなっていきましょう!

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

次回は「each」コードについて慣れていきましょう!

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


にほんブログ村

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

M関数の入力/Excel.CurrentWorkbook

記事の冒頭でも解説しように、 「=Excel.CurrentWorkbook()」を入力します

上の画像のように3つテーブルが表示されたら、2つ処理をします

まず、上の画像の「Name」の列を削除します

次に下の画像の黄色に印をつけた箇所から、各テーブルの内容を展開します

展開する際には、次に開いた画面にて、「元の列名をプレフィックスとして使用します」のチェックを外しておきます

上の画像のOKボタンをクリックすると次の画像のように、各テーブルが展開します

出荷日については、データ形式を日時型式ではなく、日付形式にしておきます

読み込み処理

読み込み処理は、新規のテーブルに行います

上の画像の「閉じて次に読み込む」をクリックし、次に開いた「データのインポート」画面にて、新規のシートを指定します

新しく追加されたシートは、シート名を「小計」に変更しておきましょう!

データの循環対策

まず、新しく作成したクエリをエディタで開き直します

エディタを開いたら、適用してステップの「ソース」を選択します

すると、下の画像のように4つのテーブルが表示されています

循環対策として、テーブル名に「Data」を含むものだけが展開されるようにフィルターをかけるステップを追加します

フィルターをかける際には、下の画像のように「指定の値で始まる」を指定し、「指定の値」として「Data」を指定します

すると、次の画像のように「Data」から始まるテーブル名のテーブルだけが表示されます

ここまで、行ったらエディタを閉じて読み込みます

シート追加テスト

適切に循環対策が機能しているかどうかを調べるため、「Data_2020年4月」のテーブル、及びシートを追加します

追加するシートには1行だけデータがありますので、循環対策が適切であれば、更新処理時に1行だけが増えるはずです

では、更新を行ってみます

上のGIF画像のように、適切に1行増えています

<まとめ>

今回はM関数「=Excel.CurrentWorkbook()」を 空のクエリを入力して、各シートのテーブルを一括でまとめました

 後からテーブル/シートを追加したとしても「データの結合」処理を行わずに済むので大変楽な方法かと思います

但し、1点だけ注意点があり、それが循環対策です

 複数のテーブルをまとめた内容自体が、「まとめ」の内容に含まれないようにフィルター処理を追加しました

Power Queryにおけるエラー対策として、フィルター処理はとても有効です

この点は今回を機に、しっかりおさえておきましょう!

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

今回使用したファイルの完成版を添付しておきます

次回はグループ毎に連番を付与する方法を解説します

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


にほんブログ村

M言語に慣れる_10回目~グループ毎に連番作成~

【M言語は難しくない】今回はデータをグループ化した際に、1から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります

コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります!

しかも知られざるグループ化機能を使えば、1つのM関数を入力するだけでできてしまいます

まさに魔法です

ぜひ、実際に手を動かしてこの魔法を体験してください!

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

今回のポイントは2つあります

グループ化

グループ化する際に、よく選択される操作は「合計」などです

今回は「すべての行」という操作を選択します

 この「すべての行」を選択して操作を行うと、グループ毎テーブルが作成されます

M関数作成

連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます

 今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します

 M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります

今回の使用データ

今回は下の画像にあるデータの「部門」列をグループ化します

グループ化した後は、グループ毎に連番を振ります

以下が実際に使用するサンプルデータです

グループ化の実施

解説は、前述のデータをテーブル化し、エディタを開いたところから行います

エディタを開いたらまずは、グループ化を行います

グループ化は「ホームタブ」の「グループ化」メニューから行います

次に開いた画面では、前述のように操作を「全ての行」で設定します

・グループ化対象列 ➡ 「部門」列

・新しい列名 ➡ 「部門列」

・操作 ➡ 「すべての行」

空欄のままでOKです

上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています

M関数の入力

関数の参照

次に「列の追加」タブから「インデックス列」を挿入してみます

すると、確かに連番は作成されますが、グループ毎の連番ではありません

こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう

関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します

テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります

=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)

このM関数では、引数を4つ指定します

・テーブル名

・新列名

・連番の開始番号:通常は1から指定します

・連番の増分:通常は1つずつ連番を増やします

この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します

なお、既に追加した連番については削除しておきましょう

カスタム列作成

それでは、「列の追加」タブから「カスタム列」をクリックします

次に開いた画面では、M関数にてカスタム列を作成していきます

使用するM関数は、前述の次の関数です

=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)

こちらは、入力補完機能を活用して入力していきます

まずは、「INDEX」と入力してみましょう

すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります

↓で2つ下にカーソルを移動し、TABキーで該当のM関数を選択します

次に、前述のようにテーブルを引数の1つ目に指定します

こちらは、列の挿入で代用します

画像に alt 属性が指定されていません。ファイル名: 3つのテーブル.gif

上のGIF画像のように「部門別」の列は3つのテーブルから構成されています

ですので、下の画像の「部門別」を挿入すれば、自動的に3つのテーブルを引数として設定できます

下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します

残りの3つの引数は、次の内容となります

・新列名 ➡ 連番

・連番の開始番号 ➡ 1

・連番の増分 ➡ 1

下の画像のように、上で紹介した3つの引数を設定したら、OKボタンを押します

すると、新しく追加した「連番」の列名の列に更に3つテーブルが作成されます

この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります

その前に、他の列は削除しておきます

他の列を削除したら展開処理します

上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう!

すると上の画像のように展開されます

きちんとグループ毎に連番も作成されています

グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います

<まとめ>

今回はグループ毎に連番を作成する方法について解説しました

作成にあたってはポイントが2点ありました

1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です

画像に alt 属性が指定されていません。ファイル名: グループ化全ての行.jpg

こちらの処理により、グループ毎にテーブルが新規に作成されます

 2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です

 カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます

 今回の方法はエディタ内の1つの列に、複数のテーブルが作成される点が少しトリッキーです

 但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです

 グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます(例:連番1のレコードのみを集める等)

 今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います!

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

参考までに今回使用したファイルを添付します

次回は、前行を参照する方法を解説します

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


にほんブログ村

【M言語に慣れる】_15回目_TEXT関数/文字列関数

【M言語は難しくない】今回は、TEXT関数の中から、2つの事例を紹介したいと思います。通常のエクセル関数と同じ様に、M言語でも文字列を操作する関数は多数あります。興味のある方はぜひ、こちらのMicrosoft社のページも参照してみてください

目次

文字列の有無を判定する関数

文字列を置き換える関数

<まとめ>

文字列の有無を判定する関数

 Text.Contains関数は、ある文字列の中に指定した文字列があるかとうかを判定し、「TRUE」か「FALSE」で返す関数です

以下の一文は、Microsoft社のページからの抜粋になります

Text.Contains(“Hello World”, “Hello”)

上の ように記述した場合には、「TRUE」を返します

次もMicrosoft社のページからの抜粋になりますが、この場合は「FALSE」を返します

Text.Contains(“Hello World”, “hello”)

ちなみに、

 M言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です

では、実際にPower Queryエディタ(以降、エディタ)で実際にこの関数を操作してみます

使用するデータは、「抽出」という1列だけがあるこちらのデータになります

 上の画像のデータから、Text.Contains関数で文字列「0」を含む行を判定し、「TRUE」もしくは「FALSE」を返してみます

まず、「列の追加」タブからカスタム列・作成画面を開きます

こちらの画面でカスタム列の式に「Text.Contains」と入力します

上のGIF画像のように「Text.Con・・・」と入力する途中で、該当の関数が出てきますので、Tabキーで確定します

次にText.Contains関数の丸括弧の中を、以下の画像のように確定します

すると、エディタ内に「0」が含まれる行を判定した列が追加されます

文字列を置き換える関数

 Text.Replace関数は、ある文字列の中から、指定した文字列を発見して置き換える関数です

以下の一文は、Microsoft社のページからの抜粋になります


Text.Replace(“the quick brown fox jumps over the lazy dog”, “the”, “a”)

この場合、返される文字列は次のようになります

“a quick brown fox jumps over a lazy dog”

上記の場合、”the”が”a”に置き換えられています

 では、Text.Contains関数と同じデータを使用して、「0」を「a」に置き換えて見ましょう!

 こちらもText.Contains関数と同じ様に、カスタム列作成画面からM関数を使用します

上の図では、カスタム列作成画面に次のような式を入力しています

=Text.Replace([抽出],”0″,”a”)

では、上記の式を入力してカスタム列作成画面のOKボタンを押します

すると、エディタ内に文字列が置き換えられた列が追加されます

<まとめ>

今回は、Text関数の中から2つだけ事例を紹介しました

 1つ目は、文字列が含まれるかどうかを判定する「Text.Contains関数」でした

2つ目は、文字列を置き換える「Text.Replace関数」でした

 もし、通常のメニューにない操作をM関数で行う場合には、前述のMicrosoft社のページをぜひ参照してみてください

 ちなみに、カスタム列作成画面でM関数を入力する際に、自動で候補の関数が表示されます

 該当の関数を選択した場合、Text関数の場合であれば「TextText」と重なってエラーになるケースがあります

こちらのエラーについては、注意が必要です

今回の解説は以上となります

長文を最後まで読んでいただき誠にありがとうございました

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


にほんブログ村

不規則に散らばっているデータを一括取得~M関数~

 今回は上の画像のようにデータが複数シートに散らばっており、しかも行見出し位置/ヘッダー行不規則なデータを一括で取得します

今回の最大のポイントは、List関数で行見出しの位置を取得する点です

そこさえクリアすれば、意外と簡単にできます

では、今回使用するデータと行いたい事の確認から解説を始めます

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

今回使用するのは次のファイルです

このファイルの中には3つのシートがあります

但し、前述のように3つのシートそれぞれの行見出し位置が違います

 2020年1月のデータは1行目から始まっていますが、2月のデータは2行目から始まります

そして、3月のデータは3行目からです

 この3つのシートからM関数を組み合わせてデータを一括で取得するのが、今回行いたい事です

今回のポイント

 今回の最大のポイントは、前述のようにList関数で行見出し位置を取得することです

 行見出し位置を取得するには、行見出しにある「客先コード」をキーにして、M関数「List.PositionOf」を使います

List.PositionOf関数の他にも2つM関数を組み合わせます

List.PositionOf

List.PositionOf関数はリストの中から、指定の値の位置を取得します

書き方は、次の通りとなります

List.PositionOf(リスト名,取得したい値)

ここで、注意点が1点あります

M言語は0ベースである点です

例えば、List.PositionOf関数を使用すると、次の画像の「客先コード」の位置はではなく「0」とでます

これは、M言語が0で始まる仕組みになっているからです

Table.RemoveFirstN

Table.RemoveFirstNは、テーブルから指定した行数を削除するM関数です

書き方は次のように書きます

Table.RemoveFirstN(テーブル名, 削除する行数)

Table.PromoteHeaders

Table.PromoteHeadersは、1行目を見出しに昇格させるM関数です

Power Queryエディタ内にも同じメニューがあります

今回の内容は、後述しますがM関数で行った方がベターです

このM関数の書き方は、次の通りとなります

Table.PromoteHeaders(テーブル名)

今回のポイントである、3つのM関数の内容を確認したところで、本格的な解説をはじめます

ファイルの読込

まず最初に、前述のサンプルファイルの読込処理を行います

サンプルファイルとは別のファイルで、下の画像の処理を行います

 上の画面の「ブックから」で前述のサンプルファイルを指定した後、Power Queryエディタ(以降、エディタ)が開くと次の画像のような状態になっています

上の画像の左から2つ目の列にある「Data」が今回のポイントの一つです

 各行にある「Table」の文字の横をクリックすると、次のGIF画像のように中味が見れます

次からはこの「Data」列を活用して、カスタム列をM関数により作成します

カスタム列の作成

List.PositionOf関数で行位置を抽出

では、カスタム列・作成画面を開きましょう

 ここで確認ですが、行位置を取得する対象は下の画像の「客先コード」の文字です

 上の画像を見ると、「客先コード」にはⅰ)Data/Table➡ⅱ)Column1➡ⅲ)客先コードの順で指定するとたどり着けるようになっています

 カスタム列・作成画面でも、M関数/List.PositionOfでの引数設定時に上の流れを使用して指定します

上の画像のようにList.PositionOf関数の引数を次の様に指定します

一引数:[Data][Column1]

第二引数:”客先コード”

そして、カスタム列・作成画面の下にあるOKボタンを押すと次の画像のように客先コードの位置が出力されます

あくまでM言語は0から始まるので、その点についてはご注意ください

 1行目の内容では、客先コードは1行目にありますがM言語のベースに従って0と出力されています

Table.RemoveFirstNで不要な行の削除

 List.PositionOf関数により行見出しの位置は抽出できましたので、「行見出しの位置-1」分の行数をM関数/Table.RemoveFirstNを使用して削除します

Table.RemoveFirstN関数の書き方は、前述の通り次の通りです

Table.RemoveFirstN(テーブル名, 削除する行数)

今回は上の第一引数のテーブル名には、[Data]を入力します

そして、第二引数には上のList.PositionOf関数をー1をせずにそのまま設定します

 上の画像の通りに数式を指定してOKボタンを押すと、「本来、行見出しにするべき行/客先コードのある行」が次の画像のように1行目に来ています

但し、このまま作成されたテーブルを展開すると不都合な点が1点あります

 本来、行見出しにするべき行が行見出し/ヘッダーになっていませんので、このまま展開作業をすると不要な行見出しがデータとして入り込んでしまいます

ですので、もう一つのM関数で不要な行を削除できるようにします

Table.PromoteHeadersで見出し行を作成

では、最後の仕上げとして各テーブルの1行目を見出し行/ヘッダーにします

 こちらは、M関数/Table.PromoteHeadersの()の中にこれまでの内容を入れるだけです

Table.PromoteHeaders関数の内容を反映した結果が次の画像です

 上の画像の内容で、各テーブルを展開処理すればいいのですが、その前に既存の列は削除しておきましょう

「他の列の削除」が終了したら、後は各テーブルを展開するだけ終了です

<まとめ>

 今回は、List.PositionOf関数をはじめとする3つのM関数を組み合わせて、不規則なデータを一括で処理できるようにしました

最大のポイントは、不規則な行見出しの位置をM関数で取得する点です

 それさえできれば、後は機械的にM関数を組み合わせるだけで一括処理ができるようになりました

今回の題材は、M関数の魅力、そしてM言語の魅力を知るのにいい題材だったと思います

ぜひ手を動かして体験してみてください

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

参考までに、今回の内容の完成版のファイルを添付します

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

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

にほんブログ村

カスタム関数を自身で登録して使い回す2~上級編9回目

 今回はPower Queryでカスタム関数を作成した後、実際に作成した関数を呼び出す方法を解説します

まずは、前回の内容の「振り返り」から行いたいと思います

前回は、カスタム関数を作成する際の「ポイント」と「作成画面」について解説しました

 解説の対象となる、これから完成しようとしてる「カスタム関数」は次の画像の「販売単価」「製造原価」「販売個数」から「利益」を算出する関数です

 上の画像の数式バーにあるように、利益を算出するのに数式を入力していたところを、カスタム関数を呼び出すだけで「利益」を自動算出できるようにします

カスタム関数のポイントとしては、2点を解説しました

1点目は、中学時代に習った「方程式を思い出す」です

カスタム関数はこの方程式と同じ様な考えで作成します

そして、代入するのは「列の名前」になるのが特徴です

2点目は、カスタム関数の作成画面についてです

前回は、上の画面を開いたところまでを行いました

 では、この「詳細エディター」画面にてカスタム関数を設定するところから解説をはじめます

目次

1.関数の設定

2.カスタム関数の呼出し

<まとめ>

1.関数の設定

今回は、次の数式を詳細エディター上に関数として設定します

利益=(販売単価-製造原価)x 販売個数

ここで、詳細エディターへの設定方法を分かり易くするために、前回のポイントで解説した方程式を使って解説します

前回のポイントで、方程式の事例として解説したのは次の方程式です

Y=aX + b

上の方程式を詳細エディターに設定したとすると、次の画像のようなイメージになります

詳細エディター設定例

letの箇所は2段の設定になります

1段目:Y = (a,X,b) =>

2段目:ax + b

1段目で「変動する数」として使用する記号を、=を挟んで右辺と左辺に分けて設定した後に、「=>」の2文字で2段目に繋ぎます

そして、2段目では方程式の右辺を設定します

inの箇所はYを設定するだけです

方程式を例に取って、詳細エディターの設定イメージをお伝えしたところで、本題の関数を設定します

利益=(販売単価-製造原価)x 販売個数

上の数式の「変動する数」はそれぞれ、以下のように設定するものとします

・利益 ➡profit

・販売単価 ➡price

・製造原価 ➡cost

・販売個数 ➡quantity

では、上の「変動する数」を詳細エディターにあてはめると下の図のようになります

画面下に以下の文字が出ていたら、右下の「完了」を押します

すると、下のような画像に切り替わります

画面には「パラメーターの入力」と出ていますが、こちらは無視していいです

ここからは通常のクエリを作成した時と同じように、エクセルシートに読込処理を行うわけですが、画面右にてクエリー名を「利益」としておきます

読込処理を行った後は、通常のクエリーと同じ様に画面右に表示されています

マークは通常のクエリとは異なっています

ちなみに、後でこの「利益」の名前を使用して「関数呼び出し」を行います

これで、カスタム関数は完成です

2.カスタム関数の呼出し

では、利益額を差し込む表をテーブル化してPower Queryエディターを開きます

Power Queryエディターが開いたら、「列の追加」タブにて「カスタム関数の呼出し」をクリックします

「カスタム関数の呼出し」をクリックすると次のようなダイアログが開きます

カスタム関数の呼出し

このダイアログの上にある「新しい列名」は「利益」に変えておきます

次に下のGIF画像のように、カスタム関数「利益」を呼出します

カスタム関数を呼び出し

後は、詳細エディターで設定した「変動する数」に「列名」を代入します

ちなみに、列名を代入するには、下の画像の▼マークをクリックしておく必要があります

「列名」を代入して、ダイアログボックス右下の「OK」ボタンをクリックすると下の画像のように「利益」の列が作成されています

これで、関数の呼出しも終了したので、エクセルシートにPower Queryエディターのデータを呼出します

事前に計算しておいた利益額とも一致しているようです

<まとめ>

 今回は、2回にわたって「カスタム関数」について解説を行いました

2回目の回では、カスタム関数を詳細エディタに設定した後に、別のクエリ画面からカスタム関数を呼び出して新たな列を設定しました

 要領としては、中学数学の方程式に「列名」を代入するイメージになります

 但し、詳細エディターで「let」「in」それぞれの段に設定する式については1度では正確に覚えられないので、参照用のメモ等を作成しておくと効率がいいです

参考までに今回使用したデータと完成版を添付しておきます

アイコン

上級編8-9回目 27.37 KB 22 downloads

...

 ちなみに、1度作成したカスタム関数を修正する場合には、まずは、下の画像のように、クエリの編集をクリックします

次にPower Queryエディターが開いたら、下のGIF画像のように▼マークをクリックすれば修正できます

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

次回は条件付きのカスタム列を作成する方法を解説します

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

にほんブログ村