資料を作成する度に、毎回毎回、同じ数式を入力していませんか？Power Queryではカスタム関数という仕組みにより、事前登録したカスタム関数を呼び出して使い回していくことができます

今回とあわせて2回に分けて、カスタム関数についての解説を行います

　私が昔、企画の仕事をしている時は、会議の前日は必ず数値資料を作成していました

　「前期比」「利益率」「単価」などを資料に盛り込むわけですが、毎回決まった数式を表に入力していました

毎回、同じ数式を入力するので体で覚えてしまったことをよく覚えています

　この記事を見て下さっている読者の方の中にも似たような経験のある方がいらっしゃるのでないでしょうか？

　カスタム関数をマスターして、「同じ数式の再入力」は自身で事前作成した「カスタム関数」の呼出し処理だけで代用できるようにしましょう！

　本格的な解説に入る前に、「今回のポイント」と「今回使用するデータと、作成するカスタム関数」について解説を行います

１．今回のポイント

中学数学の方程式

中学時代にこんな問題を数学の時間に出された経験はありませんか？

Y=aX+bの式に（X=2,Y=5)を代入するといった問題です

今回のカスタム関数を作成する時にも、同じような考え方をします

ただ、今回は代入するのが「列の名前」になります

空のクエリの作成

今回はA.の方程式を、空のクエリを作成してから指定します

「空のクエリ」はこれまでのPower Queryの解説では出てこなかった方法です

　更に空のクエリを作成した後、Power Queryエディター画面から詳細エディター画面を開き、下の画像の「let」と「in」の箇所に方程式を指定します

２．今回の使用データと作成内容

　今回のポイントを2点解説したので、次に今回使用するデータと作成するカスタム関数について解説します

今回使用するデータは、次の画像にあるデータになります

　今回作成するカスタム関数は、黄色く塗られた列の値を使用して、下の画像の利益を計算する関数になります

ちなみに、上の画像の「利益」の列には以下の数式が入力されています

　使用するデータと作成するカスタム関数について解説したところで、本格的な解説に入ります

３．空のクエリ作成

データタブの「データの取得」から「その他のデータソースから」を開きます

開いた一覧の一番下に「空のクエリ」があります

　上の画像の「空のクエリ」をクリックすると、Power Queryエディターが開きます

次にPower Queryエディターのホームタブから「詳細エディター」を開きます

すると、次のような画面が開きます

上の画像の「Let」の下と「in」の下に、ポイントAで解説した方程式を入力していきます

＜まとめ＞

　今回は、カスタム関数を作成するにあたってのポイントを2点解説した後、ポイントAで解説した、方程式を入力する画面を開くところまでを解説しました

　詳細エディターの画面は少しややこしいところもありますが、方程式の作成の仕方をイメージしてもらえればOKです

では次回は、カスタム関数を完成させます！

にほんブログ村

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

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

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

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

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

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

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

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

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

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

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

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

目次

１．関数の設定

２．カスタム関数の呼出し

＜まとめ＞

１．関数の設定

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

利益=（販売単価-製造原価）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

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

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

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

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

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

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

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

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

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

２．カスタム関数の呼出し

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

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

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

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

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

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

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

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

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

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

＜まとめ＞

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

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

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

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

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

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

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

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

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

にほんブログ村