カテゴリー別アーカイブ: 上級

行削除とヘッダー行の調整~上級編1回目~

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

 Power Queryを使用していると、無駄な行を削除したり、ヘッダー行を整えるなどの行の調整が必要になるケースがあります

 特に、PDFWEBからデータを取得した際や、エディター画面で行列を入れ替えた際に、行の調整が必要になることが多いです

行の調整が必要となるケースを、実際の画像で2点ほど紹介します

 次の画像は、PDFファイルからデータを取得した際、テーブルデータの上に「無駄な行」が実際に入っていた時の画像です

黄色の印をつけた3行は削除する必要があります 

 次に、2点目の事例ですが、下の画像のように、データを取得した際に「見出し行」の位置がずれていたりすることもあります

 本来は、黄色の印をつけた行に「前期」「今期」「前期比」の行がなくてはなりません 

 この回では前述した2点のようなケースに対応できるように、取得したデータの行削除を行ったり、見出し行を整えるテクニックを解説します

 この行調整のテクニックを取得することで、より実践的にPower Queryを活用できるようになります

尚、サンプルデータには、次のファイルデータの2ページ目を使用します

アイコン

サンプルPDFファイル 49.80 KB 654 downloads

...

データを取得

「データの取得」から「ファイルから」⇒「PDFから」を指定します

次に開いた画面で、該当のPDFファイルを指定します

PDFファイル指定

PDF内の取得データを指定

前述の1.を行うと、次のようなナビゲーション画面が開きます

ナビゲーション画面

今回の解説では「Page002」を指定して、ナビゲーション画面下の「データの変換」ボタンを押します

行の削除

今回は、テーブルデータの上、3行を削除するので、ホームタブの「行の削除」から「上位の行の削除」を実行します

「上位の行の削除」をクリックすると、削除する行数を指定する画面が出てきます

上位の行の削除

ここで「3」と入力してOKボタンを押すと、テーブルデータの上3行が削除されています

見出し行の調整

3.の上位3行を削除した状態だと、エクセルに読込んだ際は前述のように見出し行が抜けた状態になります

ですので、「変換」タブに移動し「1行目をヘッダーとして使用」を実行します

実行すれば、下のGIFのように1行目が見出しに設定されます

ここまで行調整したデータをエクセルシート上に読込みを行うと、次の画像のように、きちんと見出しが設定されています

<まとめ>

 今回はPower Queryでデータを取得した際に、エディター画面で行調整する方法を2つ解説しました

1つ目は、行数を指定して行を削除する方法

2つ目は、データの1行目を見出しとして設定する方法

以上の2つを習得すれば、より実践的にPower Queryを活用できるようになります

ではまた次回、ガッツで頑張りましょう!

次回はPower Queryを本領を発揮したテーマです

 Power Queryのテクニックを複数駆使して、セル結合を含む表を有効活用できるようにします

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


にほんブログ村

見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

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

これまで、セル結合を含むデータは自動で編集・加工ができず、手動で活用するしかないものだと思われてきました

実際、セル結合を含むデータは、ピボットテーブルに変換してのデータ抽出は行えません

何故なら、ピボットテーブルにするには、元データに見出しがあり、そして各行に見出しに応じた値や文字列が並んでいる必要があります

セル結合が行われいる箇所があると、見出しが欠けている箇所がでてきてしまいます

そして、セル結合している箇所を一括で修正する方法は無いのです

ところが、Power Query(パワークエリ)の登場により、セル結合を含むデータの有効活用が可能になりました

データの行列を入れ替えた上で、空白の箇所に対して、上下にあるデータを埋めるという作業がPower Queryだと簡単に行えるのです

今回はこの「セル結合を含むデータの有効化」については、2回にわたって解説を行います

もしかしたら、セル結合を含むデータを扱う機会はそんなに頻度がないかもしれません

ただ、今回の解説を通じて「データクレンジング」を行う力は格段に向上するはずですので、普段の業務でデータクレンジングをしている方にはお勧めの記事です!

今回の解説では、主に3つ新たなテクニックを使用します

記事の最後には限定公開動画も添付しますので、参考にしてみてください!

目次

新たなテクニック

1.Power Query Editorを開く

2.ヘッダーを1行目として使用

3.行列入れ替え

4.条件列の追加(空白への変換)

5.フィル(空白)を埋める

<まとめ>

新たなテクニック

ⅰ)ヘッダーを1行目として使用

見出しに含まれているデータ行を、後で列に入れ替えできるように、見出しから外します

ⅱ)行列入れ替え

ⅰ)で見出しからデータの1行目に変換したデータ行を、更に列に入れ替えます

ⅲ)フィル

空白を上(下)にあるデータで埋めます(nullとは空白を意味します)

今回使用する3つのテクニック( ヘッダーを1行目として使用 、 行列入れ替え 、 フィル )を確認したところで、今回の解説で使用するデータの概要を確認しましょう!

四半期と記載された行の1Q、2Qの箇所にて、複数のセルがセル結合されています

こちらのデータを下のGIFのように、ピボットテーブルなどで有効活用できるような形に変換します!

上のデータのサンプルファイルは以下からダウンロードできます

ちなみに、上のGIFで見出しの部分にて1Q、2Qの横に「列1・・・」となっている箇所があるので、今回解説するデータとして紹介したデータと違うと感じる方がいらっしゃるかと思います

これは、普通の範囲を「テーブル」にした時に、自動で挿入されてしまう文字データになります

では、詳細な解説を始めたいと思います

1.Power Query Editorを開く

データタブの「テーブルまたは範囲から」をクリックして、Power Query Editorを開きます

2.ヘッダーを1行目として使用

下の画像のようにPower Query Editorが開いたら、変換タブに移動しましょう

変換タブにて「1行目をヘッダーとして使用」の横にある▼マークをクリックすると「ヘッダーを1行目として使用」が出てきます

こちらの「 ヘッダーを1行目として使用」 をクリックして下さい

すると、見出しにあったデータ(左から四半期ではじまる)がデータの1行目に移動します

この移動により3.の行列入れ替えにて、左から「四半期」からはじまるデータを列に入れ替えることができるようになります

続きを読む 見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

セル結合により見出しが2行の表をデータ活用2~上級編3回目~

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

セル結合された表を有効活用できようにするため、前回行列の入れ替えとフィル機能を組み合わせて、データ変換を行いました

図にすると、下の図のようなイメージになります

セル結合で欠落した箇所を、上の文字で埋めた合わせた形になっています

今回は、更にデータの並びを縦横並びから縦縦並びに変えるピボット解除を組み合わせます

そして、セル結合された「見出しが不完全なデータ」を下のGIFのように見出しが整った形でエクセルシートに読込めるようにします

つまり、欠落したデータを埋めた上で、縦(1Q/4月、5月・)横(商品A、商品B・)の並びを下の図のように縦縦並びにして、値を1列に集約します

それではまず、前回の作業がどこで終了していたかを確認しましょう!

上の画像を確認してください

左から2列のColumn1は、元はセル結合したデータでした

ですので、Power Query Editorに読込んだ際に1Q、列1・・・と不規則なデータになってしまっていました

前回は、この不規則なデータを画像・一番左の列の形にしました

Column1については、前回で既に削除してあります

では、ここから今回の詳細な解説をはじめます

1.1行目をヘッダーとして使用

前回は、行列を入れ替えるために、あえてヘッダーの見出し行をデータの1行目に変換しておきました

1番左の列の見出しは前回、手動で四半期と入力しています

今回は逆に、データの1行目をヘッダーに変換して見出し行を整えます

ちなみに、上の画像の一番左の列のデータ1行目は前回、条件列を出力した時に「Qで終わらないものは空白」で出力した時に空白になってしまっています

では、変換タブに移動し「 1行目をヘッダーとして使用 」をクリックしましょう!

はい、これで見出しが整ったはずなのですがここで問題が1つあります

データの1行目を見出しに持ってきた時に、下の画面のように2つの自動変換が起こっています

一番左の列の見出しが、前述のように元は空白だったので四半期からColumn1に変わってしまっています

そして、月の列のデータの中味も日付形式に変わってしまっています

適用ステップの欄を見てみると、型の変更が追加されているがよくわかります

こちらの2点は、以下の様に変更を行いましょう!

①Column1の名称→四半期に変更

直接、列の名称を変更しましょう

②日付形式→文字型式に変換

こちらは、変更が行われたステップを削除するか、型式の変更を行いましょう

ちなみに、型式の変更で行う場合には、こちらから行えます

2.ピボット解除

それでは前述の縦横並びを、縦縦並びにかえます

四半期と月の列は縦縦なので、こちらの2つの列をカーソルで選択しておき、右クリックしてください

ちなみに複数の列を選択する時には、Shiftキーを押したままで列の選択を行ってください

上の画像のように、右クリックをすると「その他の列のピボット解除」が選択できますので、こちらをクリックしてください

ピボット解除

これで、縦横並びが縦縦並びに変わりました

3.エクセルシートへの読み込み

本題に入る前に、商品の列の名称が「属性」となっているので「商品」に変えておきましょう

これで、エクセルシートに読込む準備が整いましたが、読込先はテーブルにしても、ピボットテーブルにしてもOKです

テーブルで読み込んで、後でピボットテーブルにする方法もあります

では、解説としては一旦はテーブルで読込ます

そして、こちらのデータをピボットテーブルに変えます

これで、元々はセル結合されていて編集・加工が困難だったデータがピボットテーブルにて様々な形式で集計できるようなりました

<まとめ>

2回にわたり、セル結合を含むデータを有効活用する方法を解説しました

1回目では主に以下、3つのテクニックを解説しました

ⅰ)ヘッダーを1行目として使用

ⅱ)行列入れ替え

ⅲ)フィル

2回目の今回は、ピボット解除により「四半期と月が縦」に、そして、「商品が横に並んでいたデータ」を縦と縦の並びに変えました

データが縦と縦に並んだ、見出しが整ったデータすることにより、ピボットテーブルで有効にデータ活用ができる形式になりました

今回の2回の解説は「行列の入れ替え」「ピポット解除」どちらも行っています

この2つの違いを明確に区別して処理が行えるようになると、パワークエリでデータクレンジングする力が飛躍的に向上します

ぜひ、実際のデータで2回の内容にガッツで取り組んでみてください

では今回の解説は以上です

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

次回は四則演算から四捨五入まで、様々な集計方法を解説します

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

にほんブログ村

各種集計/合計から四捨五入まで~上級編4回目

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

エクセルは表計算ソフトなので、エクセル作業に集計処理はつきものです

必要な関数を調べて入力したり、ピボットテーブルにして処理したりする作業はちょっとガッツが必要なものです

Power Queryでは、様々な切り口による集計処理を直感的なクリック操作で行えるのが、大きなメリットです

Power Queryエディター内の「変換タブ」と「列の追加タブ」では、集計処理のためのメニューが豊富に揃っています

集計処理メニュー

今回の解説では、上の画像の左のメニューから順にポイントを絞って解説します

尚、三角関数と情報メニューについては、解説を今回は割愛させて頂きます(指数メニューについても概要だけの解説になります)

今回、解説する上で分かりにくい点が1点あります

変換タブ列の追加タブには、統計以外は同じメニューがあります

違う点は、集計した列を追加するかどうかだけです

今回の解説では、極力、変換タブに絞って解説を行います

目次

1.統計処理メニュー

2.標準メニュー

3.指数メニュー

4.数字を丸める

<まとめ>

1.統計処理メニュー

こちらのメニューは列単位での集計処理メニューになります

「列単位での集計処理」という意味は、下の画像を例にとれば「販売個数の列」を行方向に一括で集計するという意味です

ですから、集計した値は1つだけ出力されます

統計ボタンの右下にある▼マークをクリックすると、様々な集計切り口が出てきます

統計

今回は、合計だけ出力してみます

集計する列をカーソルで指定し「合計」を押します

合計処理

すると、列にある値を合計した値「1011」が出力されました

ちなみに、Power Queryコラムでも書きましたが、こちらの統計処理メニューには、「個別の値のアカウント」という集計方法があります

こちらは、列内にある重複しないリスト数を集計するという優れた機能です

従来のエクセルの使い方であれば、複数回のステップが必要な集計処理でした

「個別の値のアカウント」について興味のある方は、こちらの記事もぜひご参照ください

続きを読む 各種集計/合計から四捨五入まで~上級編4回目

エラー修正_処理ステップエラー~上級編5回目

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

Power Queryは従来のエクセルとは、使用の仕方が違います

ですのでエラーが発生した時の対処については、最初は戸惑います

 但し、Power Queryで発生するエラーは大きく分けて「処理のステップ」に関わるものと「データ自体」に関わるものの2つしかありません

エラーメッセージの内容を確認しながら、修正処理を進めていきましょう!

 「処理のステップ」に関わるエラーの中でも、発生頻度が多いのはファイルの保存先変更などに伴う「データソース取得のステップ」に関するものです

今回は、ファイルの保存先を変更した場合のエラーへの対処について解説します

目次

1.エラーの発見

2.データソースの変更

3.更新処理

<まとめ>

1.エラーの発見

クエリが参照しているファイルが見つからない時にクエリの更新処理を行うと、次のようなメッセージが出ます

上の画像は、クエリが参照しているファイルの保存フォルダを変更したために発生しています

このメッセージが出た場合には、エラーメッセージが出ているクエリの編集をPower Queryエディターで行います(今回はダウンロードは完了していません、のメッセージが出ているクエリで行います)

2.データソースの変更

Power Queryエディターを開くと、次の様なメッセージが出ているはずです

この場合には、画面上の右にある「データソース設定」ボタンをクリックします

データソース設定

次は、画面の左下にある「ソースの変更」をクリックするのですが、ボタンが小さくて分かりずらいので注意が必要です

後はデータソースの変更を、下のGIFのように進めてください

続きを読む エラー修正_処理ステップエラー~上級編5回目

途中のステップを削除した場合のエラー修正処理~上級編6回目

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

 Power Queryは便利だけど、通常のエクセルとは使用方法が違うので苦労する点も多いですよね

 特にPower Queryエディターは、これまでのエクセルとは画面自体が違うので「理解に苦しむ」人もいらっしゃると思います

 Power Queryエディターの画面右にある「ステップの適用」は通常のエクセルでいえば、ショートカットキー:Ctrl+Zに相当する箇所なのです

 こちらの「ステップの適用」は最初は難解に感じる人もいらっしゃるかもしれません

 今回は、「ステップの適用」にて途中のステップを削除した場合の修正方法について解説します

 今回の解説を通じて、Power Queryへの理解の一助になれば幸いです

1.ステップを削除する前の状態の説明

今回は、以前の回で使用したデータを使用します

この回では、下のGIFのように販売単価と販売個数の合計を乗算して、新たな列を作成しました

その後に、新しくできた列の名前を下の画像のように「乗算」から「売上金額」に変更しました

今回は、更に売上金額の列を10万円以上でフィルダーをかけます

すると、適用のステップには次の画像のように1つのステップが追加されます

では、この状態から本題の解説に入ります

2.1つ前のステップを削除

1.で追加してステップの1つ前を削除します

すると、次の画像のようにエラーメッセージが発生します

1.でフィルターを10万以上した際には、あくまで「売上金額」の列に対してフィルターをしています

そのフィルターをした「売上金額」の列が削除されてしまったのでエラーになっています

3.エラーの修正

このエラーを修正するには、2つの方法があります

1つは、フィルターされた行を削除し、列名を変えるところからやり直す方法です

2つ目については、解説を始める前にまず「フィルターされた行のステップ」の1つ前のステップ「挿入された乗算のステップ」をクリックしてみます

上のGIFのように1つ前のステップを選択するとエラーメッセージは消えました

これは、あくまで次のステップである「最後尾のステップ」からエラーになっているということです

ここからが2つめの修正方法の解説です

解説するのはエラーになっていないステップから修正をかける方法です

エラーが起こっていない、「最後尾のステップの1つ前」のステップにカーソルを置いたまま、列名を1.でフィルターをかけた時と同じ「売上金額」に変更します

すると、下のGIF画像のようにエラーが起きていた最後尾のステップ「フィルターされた行」をクリックしてもエラーメッセージは出ません

<まとめ>

 今回は、途中のステップを削除した場合のエラー修正について解説しました。このような場合には、必ずしもエラーが起きたステップを削除した後に「やり直し」処理が必要なわけでありません

 エラーが起きていないステップから再度修正をかける方法もあります

 とはいえ、極力は途中のステップはなるべく削除しないで済むようにはしておきましょう

 尚、解説を端折ってしまいましたが、途中のステップを削除すると必ずエラーが発生するわけではありません

 あくまで、途中のステップを削除することで前後の処理の流れに矛盾が起こる場合にエラーが発生しますので、その点だけ最後に追記させて頂きます

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

次回はデータ自体がエラーになっている場合について解説します

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

にほんブログ村

エラー修正_データ自体のエラー~上級編7回目

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

 今回は、上級編の5回目で事前にアナウンスしたようにPower Queryのエラーのうち、「データ自体」のエラーの修正方法について解説します

 解説に使うデータは5回目と同じく、中級編の2回目と同じデータを使用します

 中級編の1回目では上の2つの表を組み合わせて、下のテーブルを作成していました!

商品台帳の販売単価と売上台帳の販売個数を乗じて、「売上金額」を新たな列として追加してあります

目次

1.エラー内容の確認

2.修正作業

<まとめ>

1.エラー内容の確認

下のクエリのようにエラーが発見された、というところから解説をスタートします

次に、上の画像の「1つのエラーです。」をクリックします

 すると、Power Queryエディターが開き、下の画像のようにエラー箇所が表示されます

今回のエラーは、販売単価と売上金額にてエラーが発生しています

 売上金額は販売個数と「商品台帳.販売単価」を乗じているので、大元の商品台帳のクエリにて「データ自体」の「狂い」生じている可能性があります

 ですので、エラーが発生している可能性があるクエリの中味を確認するため、エディターの矢印マークをクリックします

 すると、下の図のようにクエリーの一覧が表示されます

では、「狂い」生じている可能性がある商品台帳クエリーをクリックします

すると上の画像のようにやはり「狂い」が生じている箇所がありました

では、「Error」の箇所をクリックしてみます

すると、やはり本来は数字が入っているべきところに「!!!」が入っていました

 エラーの中味を確認したら、画面右の「適用したステップ」に追加されたステップを削除して元の画面に戻ります

エラーの箇所が特定できたところで、ここから2つの修正方法を解説します

続きを読む エラー修正_データ自体のエラー~上級編7回目

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

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

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

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

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

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

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

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

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

1.今回のポイント

中学数学の方程式

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

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

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

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

空のクエリの作成

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

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

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

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

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

アイコン

上級編8-9回_演習 19.39 KB 87 downloads

...

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

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

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

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

.空のクエリ作成

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

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

 上の画像の「空のクエリ」をクリックすると、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回目

IF式を組み合わせて列作成~上級編10回目

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

Power Queryは関数やVBAの知識がなくても、直感的にクリックしていくことで処理を進めていけるのが魅力です

 ただ、複雑な処理を行いたい場合にはどうしても複数のIF文をネスト(組み合わせ)し、複数の条件式を組み合わせた新たな列を挿入する必要がでる時もあります

そんな時のためにPower Queryには「列の追加」タブに「カスタム列」というメニューがきちんとあります

 「カスタム列」のメニューの中でIF文を効果的に使えば、一見、複雑に思える「この場合はこう処理して、この場合はこういう風に処理する」といった処理も一定のルールを覚えれば簡単に行えます!

今回は、下の図の売上金額の集計表をもとにして、IF式を組み合わせて新たな列を作成する方法を解説します

売上金額

この記事を最後まで読み終えた時には、一段上のレベルでPower Queryを深く使いこなせるようになっているはずです!

*IF式内にAND条件やOR条件を設定する場合はこちらの記事を参照してください!

アイコン

上級10回目_演習 16.89 KB 116 downloads

...
 

*記事の最後に完成版のサンプルファイルを添付しています

 今回の記事では「IF文」を活用しながら、上の図の表「売上金額」を元にして来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

来期の売上予測のパターンの1つ目は消極的な予測です

ⅰ)地域がアメリカ ➡ 10%増加

ⅱ)それ以外 ➡ 5%増加

2つ目は積極的な予測です

ⅰ)地域がアメリカ ➡ 15%増加

ⅱ)製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加

ⅲ)上記2つ以外 ➡ 5%増加

1つ目は1つのIF文にて、2つの計算式を組み合わせます

2つ目は2つのIF文を組み合わせることで、3つの計算式を組み合わせます

しかも、2つ目のパターンの3番目の計算式は「且つ」なので「AND」を組み合わせます

さて、

今回作成するIf文を使った計算式を説明しました

次に今回のポイントを解説させて頂きます

ポイント

構文

今回のポイントは何と言っても、IF文を使った構文を的確に記述できるかどうかです

If文を1つ使う場合の構文は次の通りとなります

if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

次が2つのIF文を組み合わせた時の構文となります

if 条件式1 then 条件式1に合致する場合 else if 条件式2 then 条件式2に合致する場合 else いずれの条件式にも合致しない場合】

カスタム列

今回のIf文は「列の追加タブ」のカスタム列の画面で作成します

こちらの画面では「新しい列名」「カスタム列の式」を指定します

カスタム列の式には「<<挿入」ボタンにより列を挿入できます

こちらの詳細は以降の記事内で詳細に解説させて頂きます

大文字と小文字の区別

 こちらはM言語のシリーズで本格的に解説する内容なのですが、Power Queryの言語であるM言語では大文字と小文字を厳格に区別します

上記の構文の解説にあるように、「if」「then」「elseif」「else」はいずれも小文字で記入する必要があります

それでは、今回のポイントの3点を解説させて頂いたので、本格的な解説に入りたいと思います

目次

1つのIf文作成:消極的パターン

2つのIf文作成:積極的パターン

<まとめ>

1つのIf文作成:消極的パターン

解説は、Power Queryエディターを開いたところから始めます

主な解説内容は、次の①~③があります

①はIF文を作成するメニューの場所

②IF文を実際に作成する画面の詳細

③②を行う上での注意点

では、まずは①から解説します

① IF文を作成するメニューの場所

まず「列の追加」タブの「カスタム列」をクリックします

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

②IFを実際に作成する画面の詳細

まず、この「カスタム列」画面の解説を3か所に分けて行います

新しい列名➡IF文を使用して新たに作成する列の名称を記入

カスタム列の式➡「=」から右にIF文を記入

<<挿入➡上の「使用できる列」で指定された「列」を式の中に挿入

3番目の「<<挿入」の使い方については、以下、詳細に解説します

まずは上の「使用できる列」の中から該当する「列」をカーソルで選択します

次に画面右下の挿入ボタンを押すと、下のGIF画像のように前述の「カスタム列の式」の欄に選択された列が挿入されます

この「カスタム列画面」の3か所について解説したところで、実際にIF文を記入していきます

IF文の構文は以下の通りとなります

if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

内容はVBAでIF文を書く場合と、ほぼ一緒です

最後に「End IF」を付けないところだけが違います

If文を記入したら、画面右下のOKボタンを押せばPower Queryエディタ画面に反映されます

上のGIF画面で消極パターンのIf文を書いた画面は、次の通りとなります

上の画像の内容をテキストにしたのが次の数式です

if [地域]=”アメリカ” then [売上金額]*1.1 else [売上金額]*1.05】

③②を行う上での注意点

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

上の画像の下に「トークン Eof・・・」なるエラーが出ています

これは、式の構文の中に大文字が入っているからです

式の構文は、下のテキストのように、全て小文字で記入するようにしてください

IF、THEN、ELSE ➡ if、then、else

では、正しく数式を記入したところで下のOKボタンを押します

そうすると、Power Queryエディター画面に新たな列が挿入されます

では、念の為に新たに挿入された列が正しいかどうかをPower Queryエディター上で確認します

まず「売上金額」と「予測式_消極」の列をカーソルで入れ替えます

次に上の両列をカーソルで選択したまま「列のタブ」の「標準」から除算をクリックします

すると、次の様に「(左側)予測式_消去」÷「(右側)売上金額」の結果が出力されます

「標準」機能の計算は、列の位置が左にあるものから自動的に計算されてしまうので、列の位置を入れ替えることを事前に行いました

さて、出力結果は次の通り、正確に出力されていたので「積極パターン」の解説に移ります

2つのIf文作成:積極的パターン

まず、積極パターンの内容を振り返りましょう

・地域がアメリカ ➡ 15%増加(A

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加(B)

・上記2つ以外 ➡ 5%増加(C)

では上記のA~Cを、「カスタム列を作成する画面」に当てはめると次のような式になります

続きを読む IF式を組み合わせて列作成~上級編10回目