analytic-vba のすべての投稿

待機処理

Microsoft社の無料RPA・Power Automate Desktopのアクションの中から、待機処理のアクションを紹介します

待ち秒の作成

こちらを御参照ください

マウスポインターの形が変更になるまで待機

アクション名:マウスを待機する

このアクションではマウスポインタ―の形が変更になるまでフローを中断・待機します

下のGIF画像では、マウスポインタ―がマウスポインタ―の形が矢印からアルファベットのアイの形になるまでフローを中断しています

このアクションを利用すれば、複数テキストボックスの中から入力するテキストボックスを選択してからテキストを入力させることができます

パラメーターの選択

マウスポインタ―が次に変わるのを待機します

マウスポインタ―の形が次のパラメーターで選択した形になるまでフローを中断する「次に変わる」と「無効になる」の中から選択できます

マウスポインタ―

次の画像のように様々な形から設定できます

サンプル

UI要素を変更する必要があります

プロセスを待機する

このアクションにより特定のプロセスの開始、終了までフローを中断・待機することができます

下のGIF画像ではタスクマネージャーで「エクセル」を終了した時、フローを再開しています

パラメーターの選択

プロセス名

一番右側にある「下向きの▼マーク」から選択が行えます

プロセスの待機目的

開始と停止から選択して設定できます

テキスト表示の待機(OCR使用)

アクション名:テキストが画面に表示されるまで待機(OCR)

下のGIF画像ではこのアクションで「TEST」という文字が表示されるまでフローを中断・待機しています

パラメーターの選択(一部省略)

テキストが次の状態になるまで待機

事前に指定したテキストが「表示」もしくは「非表示」になるまで待機をするかを選択できます

検索するテキスト

待機処理になる対象の「テキスト」を設定します

OCRエンジンの設定

処理に使用する言語を指定できます

ウィンドウを待機する

UI要素の表示、非表示などを通じてフローを中断・待機します

下のGIF画像では、A1セルにUI要素を設定してあるエクセルファイルを閉じると同時に、フローが再開されます

パラメーターの選択

ウィンドウの検索

UI要素などを一覧から選択できます

ウィンドウ

UI要素を指定します

ウィンドウが次の状態になるまで待機

次の一覧から選択できます

画像の表示・非表示(消える)を待機

アクション名:画像を待機

下のGIF画像ではこのアクションで画像の非表示(消える)を判定し、フロー再開しています

パラメーターの選択(一部省略)

画像が以下の状態になるまで待機する

「表示される」もしくは「消える」から選択できます

待機する対象の画像

対象となる画像を設定します

詳細はこちらを御確認ください

特定ファイルの待機

詳細はこちらを御確認ください

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

POWER AUTOMATE DESKTOP でAPIを操作してデータを取得するの巻

 APIは「アプリケーション・プログラミング・インターフェイス」の略語で、Webスクレイピングとは違い、あらかじめネット上に公開してあるデータを収集します

今回の記事では、Power Automate Desktopを使用してAPIを操作する方法を解説します

 尚、APIそのものについてはこの記事では詳細には解説しませんので、もっと詳細を知りたい方は他のサイト等を参考にされてください

データの時間

ビジネス+IT

実際に記事の内容を動かしてみて、APIというものを実感して頂けたら幸いです

一番簡単なパターン/登録不要

通常、APIを利用する際にはAPIを利用するサイトで登録を行い、ID等を事前に取得する必要があります

今回は、登録が必要なしで使えるAPIで操作を行います

その代わり、取得するデータも1行だけになります

 Power Automate Desktopでは、JSON形式にてデータを取得し、さらにデータを成型してメッセージで表示します

実際のフローで使用するのは3つのアクションだけになります

使用するAPIは「zipcloud」の郵便番号検索APIになります

ポイント

データの取得

郵便番号検索APIのサイトで示してあるリクエストパラメーターの型式に従って、リクエスト文を指定の箇所に送り、データを取得します

データの変換

取得したデータはJSON形式になっており、人間の目では読みにくい内容になっています

こちらを郵便番号検索APIのサイトで示してあるレスポンスフィールドの階層に従い、人間の読める内容に変換します

リクエストURL及び指定項目

データの取得に使用するURLは以下のURLになります

https://zipcloud.ibsnet.co.jp/api/search

指定項目の中で必須は郵便番号/7桁のみです

試しに上の画像の(例)をそのままブラウザに入力してみます

https://zipcloud.ibsnet.co.jp/api/search?zipcode=7830060

*「search」と「zipcode」の間に「?」が入力されている点にご注目ください(検索対象の郵便番号を変える場合には最後の7桁を変更します)

すると、次のように表示されます

今回は上記の画像の「address1/高知県」と「address2/南国市」をメッセージで表示します

実際のフロー

APIを操作するフローは3つのアクションで構成されます

アクション1:Webサービスを呼出します

zipcloudのサイトに掲示されているリクエストURL(https://zipcloud.ibsnet.co.jp/api/search?zipcode=7830060)をそのまま使いデータを取得します

使用するアクションは「Webサービスを呼出します」になります

パラメーター:URLの箇所に上記のリクエストURLを入力します

このアクションを動かすと、サイトからJSON形式でデータを取得し、以下の変数にデータを格納します

 下の画像は変数:WebServiceResponseに実際にJSON形式のデータが格納されたところです

アクション2:JSONをカスタムオブジェクトに変換

難しそうなアクション名のアクションですが、要は人間の目では読みにくいJSON形式のデータを読みやすく変換します

上記の画像のようにパラメーター:JSONにはデータが格納されている変数:WebServiceResponseを指定します

このアクションで変換されたデータは変数:JsonAsCustomObjectに格納されます

ここでAPIの取得データ・詳細を確認します

以下の画像はzipcloudの「レスポンス」の画面です

データの階層が2段階に分かれていて、1階層目が「status」「message」「results」の3つなのが分かります

そして2階層目が「results」の下の階層の実際のデータ項目/address1等になっています

ですので、このアクションで変換したデータも同じ構造になっています

上記の画像 はこのアクションで変換した内容を格納した変数:JsonAsCustomObjectの内容です

resutlsの項目の詳細表示をクリックすると次の内容になります

今回取得するのは1行だけですので、1行だけになっています

更に詳細表示をクリックします

次のアクションで上記画像の「address1」と「address2」をメッセージで表示します

アクション3:メッセージを表示

前アクションにて、データをJSON形式から人間の目で見える形に変えて格納した変数:JsonAsCustomObjectから、項目別に抜き出してメッセージを表示します

上記の内容の振り返りになりますが、取得したデータは2階層になっています(一部省略)

・results >>(address1,address2・・・)

上記の階層に対応した形で取得データを「address1」と「address2」に分けて抜き出します

・address1 ➡ %JsonAsCustomObject.results[0].address1%

・address2 ➡ %JsonAsCustomObject.results[0].address2%

実際に上記の2つの内容をアクションに反映したのが次の画像です

以上です➡次回に続く

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

にほんブログ村

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

Power Automate Desktop でWEB操作を的確に行うための虎の巻

【RPAでの操作で一番問題となるのは、WEBサイトの操作だと思います。今回はセレクターの編集も含めて、WEB操作の裏技をいくつか紹介します】

 WEBサイトはRPAで操作する機会が多く、しかも「リンクをクリックできない」などの理屈では理解しがたい事も良く起こります

特にPower Automate Desktopの場合には、他のRPAと比べてWEB操作の細かな調整機能は充実しているとは言えません(2022年1月現在)

この記事ではWEBサイトをPower Automate Desktopで操作するための「便利技・裏技」を幾つか紹介していきたいと思います

実際のRPAの作成場面では、様々な技を組み合わせて色々と思考錯誤することになると思います

気を付けて頂きたいのは「完璧主義」に陥らない事です

RPAで100点を狙うのはかなり困難です

それなりのところで、それなりのケリをつけて次の行動に移っていきましょう

 アクション逆引き辞典

待ち処理

これはRPA処理全般の定番です

 RPAは事前に設定された通りに処理を進めていくため、WEBサイト側の操作を待ちきれず、処理の空振りを起こすことがよくあります

待ち処理を入れるアクションはフローコントロールの箇所にあります

待ち秒数は小数点単位でも指定できます

リンクのクリック⇒マウスでホバー+クリック操作

「確かにクリックしているはずなのに・・・」動かないことはよくあります

対処方法として下の画像のように、リンクのクリック処理を「操作対象(UI要素)にマウスを置く」のと同時に「左クリック」を行う操作に置き換えるとうまくいくことがあります

*注意:あくまでリンクのクリック処理がうまく行えるようになる可能性があるということです(必ずうまく行くとは限りません)

セレクタービルダーの編集

WEBサイトは裏ではHTMLコードにより作成されています

このHTMLコードの領域に踏み込んで、WEBサイト内のボタンやリンクを操作するのが今回の主旨です

話の中心になるのは、WEBサイト内のボタンやリンクの場所を表すUI要素の事になります

処理階層の変更

今回は下の画像の箇所をクリックするUI要素について解説します

上記のUI要素を指定すると、アクション内に次のように表示されます

一度設定したUI要素は、Power Automate Desktopの画面右側で編集することができます

上の画像のように画面右のマークをクリックすると、UI要素の編集画面が出てきます

更に表示されたUI要素の右横にあるマークをクリック⇒編集をクリックを2回繰り返します

すると次のようなセレクタービルダー画面が開きます

こちらの画面で、画面左のチェックを変更してみます

上の画像の状態のままだとWEBサイト内の画像の箇所をホバーするイメージです

では、階層の一番下のチェックを外してみます

こうすると、上の画像の「<a>より右の文字列」をホバーします

こうしたセレクタービルダーの階層の変更により、より良くWEB操作をできることがあります

*注意:あくまでリンクのクリック処理がうまく行えるようになる可能性があるということです(必ずうまく行くとは限りません)

カスタム画面 vs ビジュアルエディター画面

セレクタービルダー画面には2種類あります

下の画像はカスタム画面です

上の画像の左下のボタンをオフにすると、ビジュアルエディター画面に変更できます

このビジュアルエディター画面では直接コードを編集することができます

繰返し処理への変数組込/数字指定

UI要素を変えながらクリックしていく場合に、セレクタービルダーに変数を組み込んで繰返し処理を行わせることができます

下のGIF画像では、繰返し処理により「速報」タブ⇒「ライブ」タブとクリックしていっています

上のGIF画像で動かしているフロー内では、UI要素の中に変数を次の画像のように組み入れてあります

上の画像の「%LoopIndex%」はLoop処理アクション内で設定してある変数です

Loopアクションの中で「LoopIndex」は0⇒1⇒2と数字を増やしていきます

同じ様にUI要素に組み込まれた「LoopIndex」も0⇒1⇒2と数字を増やしていきます

同時に指定するUI要素も変更されていくわけですが、この理屈をお話したいと思います

上の画像の「速報」「ライブ」「個人」のタブのUI要素を取得して、ビジュアルエディターの画面内のコードをメモ帳にコピーして並べます

そうすると、規則性が見えてきます

上の画像で言えば「eq()」の中に「数字が動く変数」を組み入れれば、「速報」「ライブ」「個人」のタブのUI要素のコードを表現できることが分かります

繰返し処理への変数組込/文字列指定

上の例でセレクタービルダーで数字が動く変数を組み入れました

文字列でも指定することができます

次の画像の黄色く印を付けた箇所で、それぞれUI要素を取得します

今回は、ビジュアルエディターではなくカスタム画面を開いてみます

すると、3つのUI要素の違いは上の画像の黄色の箇所(IDの箇所)であることが分かります

ですので、今回はIDの箇所に変数を組み入れます

上の画像の変数:myNameを繰返し処理の中で変更する方法ですが、様々な方法があると思います

今回はエクセルを使用した方法を紹介します

エクセルに3つのIDの共通・文字列/ap_以外を入力しておきます

そして、繰返し処理の中でエクセルの1列目([0])を読込、変数に設定します

実際に繰返し処理を動かした様子が下のGIF画像になります

UI要素の名前変更

取得済みのWEB要素を他のアクションで使い回したい時があります

取得したUI要素は一覧の中から選び易いように名前を変更できます

該当のUI要素の上で「F2」キーを押すと、名前を好きな名前に変更できるようになります

*右クリックでも変更できます

UI要素の変更への対応

UI要素は常に固定されているとは限りません

RPAを動かしていると、UI要素の変化に対応する必要な場合があります

下のGIF画像は、テキストを抽出してメッセージを表示するロボットですが、このテキストは常に変更されていくものとします

上のGIF画像で抽出されるテキストは「Excellent仕事術_2022」となっていますが、「仕事術」のテキストはUI要素が変更になった場合も固定で含まれていると仮定します

ビジュアルエディター画面ではこういった場合にそなえて「特定の文字を含む」指定が行えます

次の画像は、上のGIF画像にて「テキストを抽出する際に指定したUI要素」のビジュアルエディター画面です

こちらの黄色く印を付けた箇所を次の画像のように変えると常に「仕事術」のテキストを含むUI要素が扱えるようになります

指定の仕方としては次のようになります

タグの種類名:contains(‘固定テキスト’)

実際に上記の設定でRPAを動かしてみます

サイト内のテキストが「Excellent仕事術_2022⇒仕事術_20220115」のように変わっても正しくテキストを抽出します

この「含む」の指定を行う場合、準備としてサイトの中味のHTMLコードを確認し、タグの種類を取得する必要があります

上の画像でいえば、黄色く印をつけた「p」のことです

確認を行うには、まずはサイト内で必要な場所で右クリックをします

「検証」という文字が見えますのでこちらをクリックします

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

こちらの画面でサイトの中味を確認することができます

ここで扱いたいテキストの前の<p>に注目してみてください

これがHTMLコードを書くときに必ず出てくる「タグ」というものです

詳細はここでは述べませんが、前述のようにこのタグの種類については事前確認が必要です

例えば、「Excellent仕事術_2022」が「見出し」としてサイト内に表示されているとします

するとタグの種類はpではなくh2になります

タグの種類がh2の場合には、セレクタービルダー内での指定も次の画像のように変わります

上のHTMLコードの内容をもっと深堀しておきたい方はぜひこのサイトから学習をはじめてみてください

このprogateは手を動かしながら学べるサイトになっていて、タグの役割なども分かり易く解説しています

画像認識の活用

画像の許容度を上げる方法

画像をクリックする方法はPower Automate Desktopではとても有効です

ただ画像をクリックする方法はとてもセンシティブです

見た目では分からないような、ちょっとした画像の映り方が変わっただけでもクリックできなくなることもあります

この場合には画像認識の許容度を上げることで対処することができます

マウスを画像に移動します」アクション内に詳細という項目があります

上の画像の左下に「詳細」の項目があります

こちらをクリックすると「許容値」を調整する箇所があります

通常、許容値は10で設定されています

例えば、次の2つの画像の上側の画像を許容値:10のままで設定してクリックできるようにしたとします

仮に同じ設定のまま下の画像をクリックすると認識できずにエラーになります

この場合、許容値を20に上げると画像認識が行えるようになります

*注意:ケースバイケースで活用をお願いします(逆に許容度を下げる方がベターなケースもあります)

画像認識+キー操作

RPAでWEBサイトを扱っていると、どうしてもクリックできないところというところも出てきます

この場合には「画像のクリック」を起点にし、矢印による移動やtabキーを組み合わせることで「思わぬ所」もクリックできるようになります

上のGIF画像では、まずは「インポート・エクスポート」の画像をクリックすることにより、通常は見えないメニューを表示した後、「矢印」で移動を行っています

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

にほんブログ村

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

【Power Query裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法

【Power Queryのパラメーターの仕組みを使い、複数のファイルから特定のシートのデータを可変で取得しよう】

Power Queryのフォルダにある複数ファイルから一括でデータを取得する仕組みは驚くほど便利です

この仕組みで大量の転記処理から解放された人も多いのではないでしょうか?

ただこの仕組みには一つだけ問題があります

ファイル内に複数シートがある場合や、シート名が変更になる場合です

今回の記事では、必要に応じてファイル内で取得するシート名を変更できる方法を解説します

今回使用するデータ

今回使用するファイルには次の画像のように複数のシートが入力されています

このような複数シートがある複数ファイルを一つのフォルダに格納しておきます

こちらのフォルダを指定して複数ファイルのデータを一括で取得します

ポイント

パラメーター

今回は過去の記事で解説したパラメーターを使用してシート名を指定します

今回は上の画像にある「sheetName」というパラメーターを作成し、こちらでシート名を可変で指定できるようにします

ダミーの値によるフィルター

こちらも過去記事で紹介した方法です

簡単に可変の値でフィルターをかける方法です

一度、ダミーの値でフィルターをします

その後、前述のようにフィルターをかけた値に可変にするパラメーターを組み入れます

不必要な列の削除、および行のフィルター

今回の処理では、不要な列の削除と行のフィルターを行う箇所が随所にでてきます

フォルダからデータ取得

従来の「フォルダからデータを取得してエディタを開くフロー」と一部違う点があります

今回は上の画像の箇所ではなく、次の箇所から直接エディタを開き、シート選択のステップは省略します

データ成型

エディタを開いて「適用したステップ」を確認すると、次の画像のようにステップが1つしかありません

エディタの中味はというと、次の画像のようにまだファイル内のデータを取得していない状態になっています

こここから、カスタム列作成画面でM関数を使用してデータを成型していきます

その前に、不要なファイルや列を削除していきます

~で始まるファイルはフィルター条件を指定して除いておきます

その後、「Content」「Name」以外の列を削除します

不要なファイルや列を削除したら、カスタム列作成画面でM関数を使用します

M関数:Excel.Workbookでファイル内のデータを取得できるようになります

次の画像が上記の「M関数を含むカスタム列」を挿入した後の画面です

ファイル内のデータを取得した内容を展開できるようになっています

こちらを展開処理します

ただ1回展開処理しても、まだ次の画像のようにデータ成型したと言える状態になっていません

ここから再び不要な列を削除した後、データ/Data列を展開処理します

ここから、データの1行目をヘッダーにします

その後は不必要な列は削除し、不必要な行はフィルターしておきましょう

*シート名の列は残しておきましょう

パラメーターの作成及びシート名でのフィルター処理

ここからはポイントの項で前述したパラメーターを作成します

上の画像の「新しいパラメーター」をクリックして次の画像の画面を開きます

現在の値には取得したいシート名を設定します

これでシート名を可変にするパラメーターは作成できました

その後、ダミーの値でシートをフィルターします

ここで数式バーに注目してください

ダミーでフィルターした値が数式の中に組み込まれています

こちらを設定したパラメーターで置き換えます

フィルターされたシートがData_202202➡Data_202201に変わりました

これで処理は終了です

試しにパラメーターの設定値を他の値に変えてみます

パラメーターの値に合わせてフィルターされるシート名が変更になっています

<まとめ>

今回は複数ファイルの中にある複数シートの中から、特定のシートのデータのみを一括で取得する方法を解説しました

パラメーターとダミーでフィルターを行う仕組みを組み合わせると、意外と簡単にシート名を可変で取得する仕組みが構築できます

ただ、無駄な列の削除や不要な行のフィルター処理が多い点が難点です

この点さえクリアーすれば、一度作成したパラメーターを何度でも使い回せますので、シートの管理がとても楽になります

ぜひ試してみてください

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

にほんブログ村

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

PDF変換の応用~フォルダに入れた複数PDFから都合良くデータを切り取る方法

今回はフォルダに入れた複数PDFファイルから、一部の箇所だけデータを一括で取得します

扱うPDFファイルには、以下の画像のように振込先のデータが含まれています

このPDFファイルから振込先のデータだけを取得します

しかもPDFファイルは複数あり、行数が可変になっています

この処理のポイントは大きく分けて2つあります

ポイント

サンプルファイルの変換

今回の処理で主に作業するのは「サンプルファイルの変換クエリ」になります

複数ファイルの1つを変換するクエリを修正して、複数ファイル全てに変換ルールを適用します

M関数の組み合わせ

過去の記事で、見出し位置が不規則な複数ファイルからデータを取得する方法を解説しました

この際、上の画像の見出しをキーにしてList.PositionOf関数とTable.RemoveFirstN関数の組み合わせ「見出しの上の行」を削除しました

今回も同じようにList.PositionOf関数とTable.RemoveFirstN関数の組み合わせを使用します

・List.PositionOf関数 ➡ 指定文字列のList内の位置を取得

・Table.RemoveFirstN関数 ➡ テーブルから指定行の上の行を削除

フォルダから一括取得

まずは複数PDFファイルが格納されたフォルダからデータを一括取得します

「Fileの結合」画面ではテーブルではなく全ページを指定します

キーの設定

エディタで「サンプルファイルの変換クエリ」を開くと次の画像のようになっています

上の画像の黄色く印をした箇所の「お振込先・・・」以降の行から「備考」の上の行までが今回の取得対象です

「お振込先」の文字列をキーに設定した上で以降の作業を進めます

キーの抽出

キーとなる文字列「お振込先」の4文字を、列の追加タブ内の「抽出」メニューを使用して切り取ります

すると下の画像のように「お振込先」の文字列が抽出した列ができます

この「お振込先」の文字列より上の行を次から削除します

その前にステップ名は分かり易く変更しておきましょう➡Data2

キーの上の行の削除

ここからM関数を使用します

まずカスタム列作成画面にて、List.PositionOf関数を入力して「お振込先」の文字列がある行を特定します

すると10という数字が入った列ができます

「お振込先」の文字列は11行目ですが、M言語は0からはじまるので正しく設定できています

ここからTable.RemoveFirstN関数で上の行を削除します(-1などの調整は必要ありません)

上の画像の数式を文字列にすると次の通りとなります

Table.RemoveFirstN(Data2,List.PositionOf(Data2[最初の文字],”お振込先”))

この数式を作る際には、ステップ名をテーブル名に使用するのもポイントとなります

下の画像が新たに作成されたテーブルになります

テーブルが複数ありますが、必要なのは一つだけですので「行の保持」で余分なテーブルは削除します

テーブル展開

ここから作成したテーブルの展開処理を行います

まずはテーブル以外の列を削除します

この後、必要な列だけ展開します

この時点で大分、形になってきました

なお、この時点で最終アウトプットを行うクエリでエラーが発生しています

これはサンプルファイルの変換クエリで元ある列を削除しているからです

このエラーは各列の型式を変更する最終ステップを削除すれば消えます

では、変換クエリに戻ります

ここからは最終調整です

最終調整

上の画像のように、空欄や「備考」などの文字をフィルターで取り除きます

この状態から行列を入れ替えます

ここからは列名を整えるのですが、お振込先の文字は「列の分割」メニューで切り取っておきましょう

では、最終アウトプットのクエリを見てみましょう

うまくデータが複数作成できています

<まとめ>

今回は複数PDFファイルのデータから、キーのデータより上にある行を削除して必要な箇所だけ抽出しました

M関数のList.PositionOf関数とTable.RemoveFirstN関数をうまく組み合わせれば、意外と簡単に行えます

今回は、抽出データの下にあるデータはフィルタしただけで済みましたが、実際には複雑なパターンもあるかもしれません

その際は過去記事で紹介した「インデックス列と余り、減算の算出」の組み合わせが有効かもしれません

あわせて覚えておいていただけると幸いです

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

にほんブログ村

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

グラフデータを非表示にしてもグラフは表示する方法

グラフを人に見せる際にデータを見せるのは格好悪いと思い、データを非表示にしたらグラフの中味も消えてしまった経験はないですか?

グラフは本来、表示されているデータを元にして作成されるので、元データを非表示にするとグラフの中味は消えてしまいます

ところが、エクセルは便利にできているので、回避する方法がちゃんとあるのです

非表示および空白のセル

グラフデータが消えてしまったグラフ上で右クリックをすると、「データの選択」が見えます

こちらをクリックすると「非表示および空白のセル」という表示が下に見えます

こちらをクリックすると別なダイアログボックスが表示されます

ダイアログボックスの内容は下の画像のようになっています

こちらで「非表示の行と列のデータを表示する」にチェックを入れてください

これで元通りに表示されます

今回は短いですが以上です

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

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

にほんブログ村

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

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

【割り算の余りの算出とピボット機能をうまく使ってVBAと同等の処理を行ってみましょう】

今回は下の画像のように1列のデータから、画像右側のようなテーブルデータを作成してみます

ポイント

ポイントは1列のデータの中で、何行目を何列目にするのかを明確にすることです

「何行目を何列目」にするかが分かれば、実はピボット機能(ピボット解除の逆)で簡単にテーブル化が行えます

インデックス列の追加と余りの計算

解説はエディタからはじめさせて頂きます

1列のデータをテーブル化してエディタを開いたら、空白をフィルターで取り除いた後に、下の画像のようにインデックス列を追加します

その後に「何行目を何列目にするか?」の印を作るために「インデックス列」を3で割り、余りを算出します

上の画像にて黄色の箇所をクリックした後、開いた画面で3を指定します

すると次の画像のような列が追加されます

引き算の追加

このままピボットをすると次の画像のようになります

空欄が混じってしまい、テーブルデータとしての規則性がありません

ですので、上の画像の「インデックス列」の代わりに3行毎集約するキーが必要になります

今回は、インデックス列から剰余の列を引くことで、下の画像のように集約キーを作成します

この引き算の処理により山田さんから始まる3行はに、木村さんから始まる行はに集約されます

引き算は余りの計算と同じ「標準」で行います

ピボット処理

では集約キーができたので、ピボット処理にて剰余の列の内容を列名になるようにします

このピボット処理は、「剰余列」を選択した上で、変換タブから行います

 列のピボットをクリックしたら、値列は列1の内容になるように指定してください

 そして、「詳細設定オプションにて」上の画像のように「集計しない」を指定してください

これで列名を変更して、無駄な列を削除したら完成です

<まとめ>

今回は、意外な方法で1列のデータから複数列のテーブルデータを作成しました

改めてPower Queryのピボットなどの機能は素晴らしいと感じました

 今回のケースは特殊であり、実際の使用機会はあまり想像がつかないですが、データ整形の際には断片的に活用できる機会も多いかと思います

ぜひ一度チャレンジしてみてください

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

POWER AUTOMATE DESKTOP逆引き辞典~テキスト処理~

Microsoft社の無料RPA・Power Automate Desktopのアクションの中から、テキスト処理のアクションを紹介します

  逆引き辞典に戻る

文字列の切り取り

アクション名:サブテキストの取得

エクセル関数で言えばLEFT関数、もしくは、MID関数と同じ働きをします

下のGIF画像ではダイアログボックスに入力された文字列を左から2文字切り取ります

パラメーターの選択/左から文字を切り取る場合

元のテキスト

切り取り対象になる文字列を設定します

開始インデックス

文字列の先頭から文字の切り取りを行う場合には「テキストの先頭」を指定します

長さ

「文字数」のままで設定

文字数

切り取る文字数の長さを設定します

パラメーターの選択/文字列の途中から文字を切り取る場合

元のテキスト

切り取り対象になる文字列を設定します

開始インデックス

文字列の途中から「指定文字の長さ」を切り取る場合には「文字の位置」を指定します

文字の位置

切り取りを開始する位置を指定します

*文字の位置を指定する場合には、カウントが0からはじまる点に注意してください

長さ

「文字数」のままで設定します

文字数

切り取る文字数を指定します

テキストの置換

アクション名:テキストを置換する

このアクションでは、「t➡T」などの文字列の置換を行います

例えば、下の画像では変数:UserInputに「test」が格納されているとしたら、このアクションにて「TesT」に変換を行っています

パラメーターの選択

解約するテキスト

操作対象のテキストを設定します

検索するテキスト

検索対象のテキストを設定します

(途中省略)

置き換え先のテキスト

検索対象のテキストを置き換える内容を設定します

空白のトリミング

アクション名:テキストのトリミング

このアクションでは、指定したテキストの先頭、もしくは末尾の空白文字を取り除きます

例えば、下の画像では、変数:UserInputに格納したテキスト「    TEXT TEXT」を「TEXT TEXT」に変換します

パラメーターの選択

トリミングするテキスト

対象テキストを設定します

トリミング対象

トリミングの対象は下の画像のように、3つのパターンから選択できます

日付・時間の表示形式の指定

アクション名:dateTimeをテキストに変換

例えば、下の画像のように現在の日時を取得したとします

このアクションでは「現在の日時」を好みに応じた型式に変更することができます

下の画像では、このアクションにより「1/17/2022 1:42:21 PM」をyyyyMM形式の「202201」に表示を変更しています

パラメーターの選択

変換するdatetime

変換する対象(変数など)を設定します

使用する型式

「標準」と「カスタム」から選択できます

標準形式orカスタム型式

標準形式の場合は、下の画像のような形式の中から選択して設定できます

カスタム型式の場合は自身で「yyyyMM」などの型式を入力して設定します

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

にほんブログ村

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

今年読まれたブログ記事

今年ももう終わりですね

日が経つのは本当に早いものです

今年はこのブログに12万の方に訪問して頂きました

誠にありがとうございます

今年の最終日に、1年間で最も読まれた記事の振り返りをしたいと思います

1位

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

今年はPower QueryのM言語と出会って衝撃を受けました

M言語ではエクセルを使いながらAccess以上のことが行えるのです

このIF式の組み合わせはM言語の便利さの代表でしょうね

2位

POWER QUERYって何8???~PDFファイルをエクセルに変換~

こちらもPower Queryの記事なのですが、PDFファイルの内容をエクセルのテーブルに変換する内容です

こちらもとても便利です

YOUTUBE動画も結構人気があります

https://www.youtube.com/watch?v=pr14ReXLFC8

3位

読込先の変更~初級講座5回目~

こちらはPower Queryの読込先を「新規のシート」や「既存のシート」、もしくは「接続のみでシートに読込しない」を選択する方法を解説した記事です

特に「接続のみ」を選択することで1シート104万行の壁を超えられるのは衝撃的でした

以上、

今年読まれた記事の1~3位でした

この3つの記事は今年、自分がいかにPower Queryに力を入れたかが分かる内容でした

来年はさらにIT全般に踏み込んで、新たな世界を切り開いていきたいと思っています

特にRPAやAIなどには力を入れていきたいと思います

では、来年もよろしくお願いします

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

にほんブログ村

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

POWER AUTOMATE DESKTOP逆引き辞典~フローコントロール~

 Microsoft社の無料RPA・Power Automate Desktopのアクションの中から、フローコントロールのアクションを紹介します 

  逆引き辞典に戻る

待ち処理

アクション名:Wait

アクションの間に「待ち処理」を入れることで、円滑にアクションを実行していけるようにします

下のGIF画像では「おはよう」のメッセージと「こんにちは」の間に「3秒」の待ち処理を行います

パラメーターの選択

待ち秒を指定します

サブフローの実行

 Mainのフロートは別に「サブフロー」を作成することで、フローを部品化し、柔軟で効率的なシナリオ実行を可能にします

サブフローは次のGIFのように追加して作成します

サブフローは、後で「何を別フローにしたか」を思い出しやすいようなフロー名をつけるように心がけましょう

下のGIF画像では、実際に追加したサブフローを実行しています

 Mainのフローで「こんにちは」のメッセージを表示した後、サブフローで「こんばんは」のメッセージを表示しています

パラメーターの選択

サブフローの実行

上の画像の下向きの▼マークをクリックすると、既に追加してあるサブフローを表示できます

表示してあるサブフローの中から実行するものを選択します

ラベル

フローの流れを変えるのにとても便利な機能です

下のGIF画像では、1と2の選択肢があるリストから2を選ぶと、フローの冒頭の「TEST2ラベル」に移動しています

下の画像が上のGIF画像で動いていたシナリオです

2番目の選択ダイアログ・アクションで2を選ぶと”移動先/TEST2に移動する”➡ラベル/TEST2”というようにフローの流れが変わり、再び選択ダイアログ・アクションが起動します

このラベルはエラー処理でも使えるので、覚えておくと便利です

パラメーターの選択

ラベル名

管理しやすい名前を付けましょう!

*英語から設定しはじめ英数字で指定

移動先

移動先のラベルを設定します

選択のパラメーター

ラベルに移動

移動先のラベルを設定します

*うまく設定できない時は、ラベルの位置を一旦変えてみましょう

特定ファイルの待ち処理

こちらは正確にはファイルの処理になります

アクション名:ファイルを待機します

このアクションでは、指定フォルダに特定のファイルが格納(もしくは削除)されるまでフローが停止します

 下のGIF画像では、フローを起動した後にファイルをフォルダに貼り付けると、次のアクションの「メッセージボックスの表示」が起動しています

パラメーターの選択

ファイルの次の状態を待機します

 特定ファイルを指定フォルダに「格納」するのをトリガーにするのか、もしくはフォルダから特定ファイルを「削除」するのをトリガーにするのかを指定します

ファイルパス

トリガーにするファイルを指定します

フローの終了

アクション名:フローの停止

このアクションで都合の良い箇所でフローを終了します

パラメーターの選択

フローの終了

エラーで終了する場合にはエラーメッセージを表示するかを選択できます

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

にほんブログ村

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