タグ別アーカイブ: モダンEXCEL

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

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

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

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

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

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

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

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

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

1.今回のポイント

中学数学の方程式

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

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

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

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

空のクエリの作成

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

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

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

2.今回の使用データと作成内容

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

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

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

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

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

.空のクエリ作成

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

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

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

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

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

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

<まとめ>

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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~上級編9回目

クエリのコピー・バックアップ・削除他~上級編16回

今回は、各種クエリに関する処理について解説します

解説する処理内容は全部で5つになります

1.クエリを別ファイルにコピー

2.クエリのバックアップ、復元

3.クエリの数が多い場合のグループ化

4.クエリの一括削除

5.依存関係

最後依存関係は前述の1.~4.全てに関わります

この回で解説する依存関係とは、データソースやクエリのマージ(結合)のことになります

 順序は逆になりますが、今回の事例として使用するファイルを解説する意味もありますので、5.依存関係から解説を行います

5.依存関係

今回使用するファイルは、過去の記事でも使用したファイルです

元データ

 この時には、商品台帳と売上台帳をマージして「売上金額クエリ」を作成し、売上金額を計算しました

では早速、「売上金額クエリ」の依存関係を見てみます

解説対象のクエリ

 まず「売上金額クエリ」の上で右クリックして、Power Queryエディタ(以降、エディタ)を開きます

クエリの編集

エディタが開いたら、表示タブをクリックします

クエリの依存関係

すると、上の画像右の「クエリの依存関係」をクリックできるようになります

下の画像が、「クエリの依存関係」をクリックした時に開く画面です

依存関係

上の図を見ると「売上金額クエリ」は①商品台帳クエリと②売上台帳クエリとマージしており、この2つのクエリに依存しているのが分かります

①と②についても、依存の状況は分かります

①商品台帳クエリ

このクエリは「e:\パワークエリ講座・・・」のフォルダにあるファイルからデータを取得しているのが分かります

依存関係

この 「e:\パワークエリ講座・・・」の上にカーソルを置くと更に詳細が分かります

②売上台帳クエリ

 売上台帳クエリは①の商品台帳クエリと違い、売上金額クエリが存在するファイル内からデータを取得していることが分かります

 上の関係図にはないですが、売上台帳クエリは現在ブック内の「売上台帳テーブル」からデータを取得しています

 ちなみに上の図に、「読み込まれていません」とありますが、こちらはクエリの読込先が「接続のみ」になっていることを表します

 今回、事例とするクエリのことも解説したところで、前述の1.~4.についても解説を行って行きます

1.クエリを別ファイルにコピー

まず、商品台帳クエリを別ファイルにコピーします

商品台帳クエリ

商品台帳クエリ上で右クリックし、「コピー」をクリックします

クエリのコピー

そして、別ファイルで貼り付けます

クエリの貼り付け

では、他2つのクエリに依存している「売上金額クエリ」を別ファイルにコピーしてみます

すると下の図の通り、依存している2つのクエリも同時にコピーされます

売上台帳クエリは「現在のブック」内のテーブルを取得元にしています

別ファイルには、もちろん「売上台帳クエリ」の取得元になるテーブルはありませんので、売上台帳クエリはエラーになります

そして、「売上台帳クエリ」に依存している「売上金額クエリ」もエラーになります

続きを読む クエリのコピー・バックアップ・削除他~上級編16回

文字列の抽出~上級編17回

 エクセル関数で特定の文字列を抽出する場合には、RIGHT関数やLEFT関数、そしてMID関数が良く使われます。今回の解説では、Power Queryでこれらの関数と同じ様に特定の文字列を抽出する方法を解説します。

 また、特定の文字列の位置が不特定の場合、エクセル関数ではFIND関数やSEARCH関数が前述の関数と組み合わせて使用されます

Power Queryでは、特定の文字列の位置が不特定の場合でも一括で抽出できる裏技がありますので、そちらについても解説を行います

例えば、

 下のGIF画像のように、「-」が不規則に出現する文字列の中から「左から2つ目3つ目ハイフン」の文字列を抽出することもできます

では、Power Queryにて特定文字列を抽出する方法について詳細な解説をはじめます

 以降の解説は、全てPower Queryエディタ(以降、エディタ)を開いた状態から解説を行います

エディタ内で使用するタブは「変換タブ」、使用メニューは「抽出」になります

目次

1.左から指定した長さの文字数を抽出

2.右から指定した長さの文字数を抽出

3.位置を指定して文字列の途中から指定した長さを抽出

4.特定の文字列の前後の文字を抽出

5.「特定の文字列の間」の文字を抽出

<まとめ>

1.左から指定した長さの文字数を抽出

エクセル関数では、LEFT関数で行う内容です

こちらは、下の画像の「最初の文字」から抽出を行います

上の画像の「最初の文字」をクリックすると下の画像の画面が開くので、こちらで抽出する長さを指定します

OKボタンを押せば、下のGIFのように左から3文字が抽出されます

2.右から指定した長さの文字数を抽出

こちらはエクセル関数で言えば、RIGHT関数になります

下の画像にある「最後の文字」から指定します

1.の「左から指定した長さの文字数を抽出」と同じ様に、「最後の文字」をクリックした後に開くダイアログボックスにて抽出する長さを指定します

後の処理は、1.と同じです

続きを読む 文字列の抽出~上級編17回

文字列の追加、置換~上級編18回

 

 今回は、既存の文字列に「新たな文字列を追加」する方法と、「文字列を置き換える」方法を解説します

「文字列を置き換える」場合、繰り返し同じ文字が出現する時には、特定の位置の文字だけ置き換える必要が出てくる時があります

その場合には、過去に解説した「文字列の抽出」「列のマージ」を組み合わせて置き換えます

では、本格的な解説をはじめます!

尚、解説はPower Queryエディタ(以降、エディタ)画面内の操作のみになります

目次

1.文字列の追加

2.文字列の置換

3.繰り返し文字の置換

<まとめ>

1.文字列の追加

①既存文字列のに追加

下の画像の文字列の前に文字列「A-」を追加する方法を解説します

なお、文字列を追加する場合、元の文字列を残したまま、新たに「文字列を追加」した列を追加する方法と、元の文字列に対して、「文字列を追加」する方法があります

今回は、元の文字列を残したまま、新たに列を追加する方法で解説します

ですので、下の画像のように「列の追加」タブを使用します

「列の追加」タブ内にある「書式」の右横の▼マークから「プレフィックスの追加」をクリックします

すると下の画像のような画面が開きますので、追加する文字列「A-」を指定します

追加する文字列を指定したら、画面右下のOKボタンを押せば、下のGIF画像のように文字列「A-」が既存の文字列のに追加されています

②既存文字列のに追加

下の画像の文字列の後に文字列「-B」を追加する方法を解説します

基本的には、①の既存の文字列の前に文字列を追加する方法と一緒です

但し、下の画像の箇所にて、クリックする箇所が違います

「プレフィックスの追加」でなく「サフィックスの追加」をクリックします

それ以外の処理は①と一緒です

続きを読む 文字列の追加、置換~上級編18回

エラー発生の予防/列のデータ形式変更と削除について~上級19回~

 Power Queryの便利な点は、普段から使い慣れたエクセルをAccessのようなデータベースとして活用できる点です。このページを訪問して頂いた方の中にもAccessは細かなルールが多くて不便と感じた方もいらっしゃったと思います。

 Power Queryは自動で、Accessの不便な点を補ってくれる機能があります。そのかわり、エラーが出やすい箇所があります

このエラーはPower Queryの2つの特徴と深く結びついています

 Power Queryの2つの特徴について解説しながら、「列名変更」に関するエラーの発生を防ぐ方法について解説します

 エラーの発生を防ぐ方法を理解した時には、「データ形式」も含めてPower Queryへの理解が一段と深まっているはずです!

ところで、

エクセルとAccessの違いとは何でしょう??

エクセルはあくまで表計算ソフトです

Accessはデータベースソフトです

 この2つの違いを、別な言い方で表現すると「データの構造化」ということになります

こちらが、Power Queryの特徴の1つ目の話しです

Accessでは、いきなりデータから入力はできないようになっています

 上の画像のように「データを入力する箱」をフィールド毎(エクセルでは列毎)に「フィールド名/見出し」「データ型」を設定しなくてはいけません

つまり、予め入力するデータの箱を「構造化」しておくわけです

 今回の解説では、上の画像についてはこれ以上は深入りしませんが、上で前述した「データの構造化」を意識して、以降の記事を読み進めてみてください!!

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

 こちらの3列(売上日、商品コード、販売個数)からなるテーブルデータから、下の画像のように商品コードの1列を抽出するクエリを作成しておきます

そして、元のテーブルデータの「販売個数」の列名を変えます

すると、「商品コード」のみを抽出するクエリを更新するとエラーになります

 エラーの中味を見てみると、抽出する「商品コード」とは関係ない列の「名前変更」によりエラーが発生しています

実は、このエラーは前述の「データの構造化」が深く関係しています

では、こちらのエラーを回避する方法を以下、2パターンで解説します

1.ステップ「変更された型」の削除

まずは、前述のクエリの中味をPower Queryエディタ(以降、エディタ)で見てみます

適用したステップは3つあります

こちらの3つのステップを、上から順に各ステップの「数式」を見てみます

①ソース

こちらは、ファイル内の「売上台帳」テーブルをデータソースとして読み込んでいるのが分かります

②変更された型

こちらのステップでエラーが発生しているのが、よく分かります

こちらの数式に含まれる「販売個数」は、既に名前が変更されているのでエラーが発生しています

③削除された他の列

こちらのステップで「商品コード」列のみを抽出しています

 実際には、次の画像のように「商品コード」以外の列を削除してステップが作成されています

①~③のステップの中味を確認したところで、エラー原因となった②のステップを削除してみます

すると、エラーは消えます

仮に元の列名が「販売個数」の列名を、再度変更してもエラーは発生しません

これで、列名の変更でエラーが発生しないクエリに変更できました

ところで、

今回のエラー発生の原因となった「変更された型」のステップとはなんでしょうか?

エディタを開く時には、「変更された型」のステップは自動挿入されています

このステップは記事の冒頭で前述したPower Queryの1つ目の特徴である「データの構造化」と深くかかわっています

試しに下の画像のように、最初からクエリを作成してみます

開いたエディの中味を見ると、「変更された型」が前述のように自動追加されています

そして、各列も自動で「型式」が変更されています

例えば、商品コードの型式は元々は「文字列」でしたが、下の図のように「123」マークの「整数型式」に自動変換されています

下の図のように元のデータにて、数字かどうかを判定する数式「ISNUMBER」で判定するとFALSEになり、「文字列」だったことがよく分かります

記事の冒頭で前述したように、エクセルはあくまで表計算ソフトです

表計算ソフト内にあるデータは、データベースとしては構造化されていません

Power Queryでは、Accessとは違い、自動でエディタ内にて「構造化」の設定を行ってくれているのです

ちなみに、AccessのファイルをPower Queryで読み込むと「変更された型」のステップは発生しません

 AccessファイルをPower Queryで読み込む方法は過去の記事で紹介していますので、興味のある方は試してみてください

話をPower Queryの「構造化」に戻します

「構造化」は元データの全ての列に対して設定を行うので、エディタ内で削除した列も設定の対象です

しかも「構造化」は元の列名に対して行われます

ですので、一見、クエリに関係ない列の「名前の変更」が影響するのです

 但し、「商品コード」が文字列から「整数」に変換されたように、常に正しく「構造化」が行われるわけではないので注意が必要です

自動で変更された型を変更する場合には、下のGIF画像の箇所で調整を行います

下の図のように「文字列」は「テキスト」となっている点に注意してください

上の図の詳細な内容については、一覧でMicrosoft社のHP内で紹介されています

さて、

 エラー発生の対策をしたクエリからは「変更された型」のステップは削除されていますが、こちらのステップを後から追加することもできます

上の画像の黄色の箇所にある、変換タブ内の「データ型の検出」をクリックすると、「変更された型」のステップが追加されます

2.削除の仕方の変更

2.削除の仕方の変更では、1.とは別なアプローチでエラーを解決してみます

まず、エディタ内の画面左側から1.でエラーが発生しなくなったクエリを「複製」してみます

複製したクエリの最終ステップでは、前述のように「商品コード」以外の列を削除しています

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

こちらのステップを削除して、次のGIF画像のように他の2つの列を1つ1つ削除します

2つの列を削除した後、エクセルシートに読込みます

次に、列名を「TEST2」から「TEST3」に変更すると複製したクエリはエラーになります

このエラーの原因は「列の削除の仕方」にあります

下の図のように、列名変更前の「TEST2」の列名を指定して「列の削除」をおこなっているからです

 こちらのエラーについては下の画像の黄色の箇所のように、列の削除の仕方を「列名」を指定しない方法(他の列の削除)で行えば発生しません

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

このエラーはPower Queryの「ハードコード」という特徴と深く結びついています

Power Queryでは、エディタ内の操作がステップとして記録されます

このステップ内には、も記録されます

前述のエラーで言えば、「列名を変更する前」の列名です

こちらは、列名が変更になると「ハードコード」した列名と一致しなくなってしまい、エラーになったのです

<まとめ>

 今回は、エディタ内で削除した「列名」を後から修正した場合に発生するエラーの修正方法について2つ解説しました

 1つ目は「変更された型」という自動で追加されるステップを削除してエラーを回避できるようにしました

2つ目は列の削除の仕方を、削除する列名を特定しない方式に変更しました

2つの方法はそれぞれ、Power Queryの2つの特徴と深く結びついています

 1つ目の「変更された型」に関するエラーは「データの構造化」というPower Queryの特徴と結びついています

 エディタでデータを読み込む際に、全ての列を対象にして「見出し」「データ形式」を設定します

ですので、エディタ内で削除した列の「列名変更」がエラーの原因になります

 こちらの「変更された型」については、「変更された型」のステップを削除する方法ともう一つ、対処方法があります

データタブの「データの取得」をクリックすると一番下に「クエリオプション」が出てきます

こちらをクリックすると次の画像の画面が開きます

こちらの画面で、「非構造化ソースの列と型とヘッダーを検出しない」を指定しておくという方法もあります(自動で検出されていた部分が検出されなくなるので注意が必要です)

ここまでで、Power Queryの1つ目の特徴についての「まとめ」を解説しました

次は2つ目の特徴である「ハードコード」についてです

Power Queryではエディタ内の操作が「ステップ」として記録されます

こちらの「ステップ」には値も直接書き込まれます

ですので、前述の2.で列を削除した際には「列名」も値として記録されました

ですので、エディタ内で削除した列の「列名」が変更になると、列名の「不一致」が発生してエラーになったのです

下の画像で言えば、「TEST2」の列名の列を削除するように数式が設定されていましたが、既に「TEST3」に列名が変更になっていたのです

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

 今回はPower Queryの2つの特徴について、理解を深める機会になったと思います

この2つの特徴を理解しておくと、Power Query自体への理解も深まります

 ぜひ今回を機会に「データの構造化」「ハードコード」について意識した上でPower Queryに取り組んでいきましょう!

では、今回は以上となります

参考までに今回使用したエクセルデータを添付します

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

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

にほんブログ村

変換した列の追加~中級編10回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回、案内したようにエディター画面での変換処理について解説します
エクセルはあくまで表計算ソフトであり、Accessのような大量のデータを扱うのに適したデータベースソフトではありません
Power Queryのエディター画面にある機能を使いこなせば、データベースソフトと同じ様に一括で変換処理が行えます
今回は、エディター画面の「列の追加」タブにある機能の一部を解説します

エディター画面
列の追加画面

解説に使うデータは、初級講座1回目で使用したデータを使います

解説用データ
      販売データ

次から解説する、1~3のステップを通じて、既存の表の横に「連番」と「部門コード」「販売金額」の3列から構成される表を追加します

列追加
連番及び条件付き列追加

.連番追加(インデックス)

取得したデータに、エディター画面で連番を付けます

①エディター画面を開く

まず、既存の表からデータを取得してエディター画面を開きます

取得データ
取得データ

⓶インデックス列

エディター画面が開いたら「列の追加」タブをクリックし、「インデックス列」の横にある▼をクリックします

インデックス列の付け方
インデックス列

ここで、連番の付け方を選ぶことができます

 ・0から始め、増分を1にする

 ・1から始め、増分を1にする

 ・開始番号と増分をカスタマイズする

ここでは1からを選択します

インデックス列
連番

2.条件列

この条件列は従来のエクセルではIF関数に相当するものです

IF関数とは違い、エディタ―画面では設定条件に従い、一括で変換します

それに、IF関数ほど設定の仕方は複雑ではありません

まず、「条件列」をクリックして、設定画面を開きましょう

条件列の追加

新たに開いた「条件列の追加」画面では、以下の6つを設定します

 ①追加される列名

 ②~④設定条件

 ⑤設定条件に合致する場合の出力結果

 ⑥設定条件に合致しない場合の出力結果

今回は、部門名が東京の場合は「T」大阪の場合、つまり東京以外の場合は「O」を出力するので、以下の画面のように設定します

条件設定
条件設定画面

3.読込処理

①列の削除

今回出力する表は以下の様になるので、無駄な列は削除します

出力する表

削除は「削除する列」にカーソルを置いて右クリック⇒削除、の順で行います

列が隣合っている列を一気に削除する場合は、Shiftキーを押しながら削除対象列を指定してください

複数列指定
列削除

②列移動

新たに追加した2列を、まとめて移動します

列移動
列移動

③読込先の指定

今回は、既存シートにエディターで操作したデータを読み込みます

読込

ホームタブで「閉じて次に読み込み」をクリックした後、データインポート画面にて読込位置を指定してください

「表示」はテーブル、「データを返す先」は既存のワークシートを指定します

データのインポート
データのインポート

<まとめ>

今回は、エディター画面で2種類の列を追加する方法を解説しました

1つは連番の追加、2つ目は従来のエクセルで言えばIF関数に相当する条件列の追加です

他にもエディター画面での列削除や列移動についても解説を行いました

今回の解説を通じて、エディター画面がスマホと同じ様に、直感的に操作できる利点を感じて頂けたら嬉しいです!

次回はPower Queryを使用して集計上手になるため、グループ化について解説します

それではガッツで頑張りましょう!

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

にほんブログ村