はじめに
Power Automate Desktop の DataTable (データテーブル) 変数から値を検索する処理について、2021年8月に追加された"Excel ワークシートのセルを検索して置換する" アクション を使用してループを使わずに高速で検索する方法をまとめた記事です。
本記事は、@sys_zero さんの Power Automate Desktop「ループを使わないリスト内の検索」 を参考に、データのサンプルやフローの構成を作成しております。
この方法のメリットと前提条件
DataTable に格納されている値を検索するには、一般的に Loop や For Each などのループのアクションを使用し、1行ずつ値を検索する方法があります。
この場合、データテーブルの行数が多ければ多いほど検索に時間がかかる傾向となります。
Power Automate Desktop には、2021年8月現在、DataTable の内容を検索するアクション (VB.Net の DataTable.Select() にあたるようなもの) がありませんので、それの代替となるようなアクションとして、"Excel ワークシートのセルを検索して置換する" アクションを使用します。
以下に、DataTable 内の値をループで検索した場合と "Excel ワークシートのセルを検索して置換する" アクションで検索した場合にかかる秒数の差を記載します。
| "For Each" アクションを使用したループ処理 | "Excel ワークシートのセルを検索して置換する" アクション | |
|---|---|---|
| 1,000行での検索 | 9.2 秒 | 2.3 秒 |
| 10,000行での検索 | 53.2 秒 | 2.3 秒 |
| 100,000行での検索 | 382.7 秒 | 3.02 秒 |
レコード数が多いほど、"Excel ワークシートのセルを検索して置換する" アクションを使った検索が有効なことがわかると思います。
この方法のメリット
1) ループを使って検索するより高速に処理ができます。
2) 完全一致や部分一致や大文字小文字の区別など、Excel の"検索と置き換え" 機能と同様のオプションを使って検索を行うことができます。
3) "すべての一致" オプションを有効にすることで、検索対象の値が複数個 DataTable 内に存在する場合でも、1回のアクションで見つけることができます。
利用にあたっての前提条件
1) Power Automate Desktop のバージョンは、2.12 以上である必要があります。
2) Excel アプリケーションが必要となります。
3) 処理対象のデータの扱いは、Excel の仕様と制限に順じます。(行数や列数など)
4) データの量や PC のスペックによっては、上記制限に該当しない場合でも処理が正常に動作しない可能性があります。
フローの作成
今回作成するフローの全体像は以下の通りです。
フローの全体像
ここからは、各アクションについて説明します。
1. データテーブルの作成
検索を行う DataTable を作成します。
実際は、システムからスクレイピングしたデータなどを格納した DataTable に対して行いますが、今回は、サンプルの DataTable を作ってしまいます。
"変数の設定" アクションで、 2列 * 1200行のデータテーブルをサンプルとしてハードコードしてます。
出来上がる DataTable は以下のイメージのデータです。
| name | value |
|---|---|
| a001 | 10 |
| b001 | 15 |
| c001 | 25 |
| d001 | 100 |
| a002 | 300 |
※1200 行まであります。
2. 検索する値を入力するメッセージボックス
DataTable から検索する値を入力するため、"入力ダイアログを表示" アクションを選択します。
プロパティを設定し、実行すると以下のメッセージが表示されます。
3. Excel の起動
空の Excel を起動します。
"空のドキュメントを使用" を選択するだけで、OK です。
4. DataTable の Excel への書き込み
”Excel ワークシートに書き込み” アクションを使用し、DataTable の内容を Excel 上で検索するために、書き込みます。
特定のセル位置に DataTable 変数を書き込みを行うと指定した位置を起点にデータがガバっと書き込まれます。
今回は、セル A1 に貼り付けます。
実行すると DataTable の内容が以下のように Excel に書き込みされます。
5. Excel 上で DataTable 内容の検索
"Excel ワークシートのセルを検索して置換する" アクションで、上で貼り付けたデータを検索します。
今回は、特定の値の検索だけ行いたいので、"検索モード" は "検索" とし、"すべての一致" も "無効" にしています。
設定の詳細は、以下の通りです。
| プロパティ | 値 | 備考 |
|---|---|---|
| Excel インスタンス: | %ExcelInstance% | |
| 検索モード: | 検索 | |
| すべての一致: | 無効 | |
| 検索するテキスト: | %UserInput% | メッセージボックスで入力した検索値 |
| 一致するサポート案件: | 有効 | 大文字小文字の区別する |
| セルの内容が完全に一致する: | 有効 | 検索値との完全一致 |
| 検索条件: | 行 | |
| 見つかった列のインデックス: | FoundColumnIndex | |
| 見つかった行のインデックス: | FoundRowIndex |
6. Excel の終了
検索は終わったので、"Excel を閉じる" アクションで Excel を閉じます。
保存も行いません。
7.検索結果の表示
"メッセージを表示" アクションで、Excel 上で検索した結果を表示します。
表示するメッセージには、以下のメッセージを設定します。
検索したキーワードの存在有無とどの位置にデータがあるかを表示します。
DataTable の
%FoundColumnIndex% 列目
%FoundRowIndex% 行目
に%UserInput%が存在します。
改めて実行時の動作を確認します。
まとめ
この方法はデータ数が多い場合、ループ処理より高速にデータを検索することができます。
また、Excel の検索と置き換え機能と同様に部分一致や大文字小文字を区別した検索も行うことができます。
この記事の主役である、"Excel ワークシートのセルを検索して置換する" アクションは、Excel 操作のアクションですが、複数の処理と組み合わせることが、Power Automate Desktop の処理の自動化の幅を色々と広げることができると思います。