自習用コンテンツ その12(https://automate.sct.co.jp/knowledge/13961/)の内容は如何でしたでしょうか？
簡単に出来てしまった方も、難しかった方もいたかと思います。
下記では、解説を書いていきます。
(解答例のコードは、ページ最下部に書いています。)
■観点
今回のコンテンツでは、「Excelによるデータ検索」および「RPAならではの時短方法」を観点としました。
対象のデータは3万件近くあり、ループで上の行から1つずつチェックしていると膨大な時間がかかるデータとなっています。
本解説の方針としては
A. 「商品コード」「ブランドコード」「サイズコード」「カラーコード」の文字列を結合した列を作成(その際、フォーマットも行う)
B. Aの列を利用し、Match関数で特定できるようにする
C. ヒットした場合は、その列のE列を取得する
とします。
※別の方法として、SQLでデータ取得する方法もありますが、今回は考えないものとします。
それぞれの項目をクリックすると詳細を展開できます。
■1. 変数を作成する
最低限必要は変数は以下の2種類です。
・Match関数の結果を格納する変数
・Match関数の結果をもとに取得した在庫数を格納する変数
<AMVARIABLE NAME="var_position" VALUE="" />
<AMVARIABLE NAME="var_data" VALUE="" />
■2. 作業用列を作成する
以下の要領で、作業用の列を作成します。
-----------------------------------
・商品在庫状況のExcelを開く → 「Excel - 開く」アクション
・セル「F2」をアクティブセルにする → 「Excel - セルを選択」アクション
・セル「F2」に数式を埋め込む → 「Excel - 値の設定」アクション
・ショートカットキーでオートフィルを実施 → 「入力 - キーストロークの送信」アクション
-----------------------------------
数式は、「=TEXT(A2,"000")&TEXT(B2,"00")&C2&D2」をF2セルに埋め込みます。
これにより、桁調整をしつつ全文字列を結合した文字列を取得可能です。
桁調整をすることにより、「891」「2」→「89102」、「89」「12」→「08912」と区別されるため、必ずユニークになります。
F2セルに式が挿入され、かつF2セルがアクティブセルとなっている状況となれば、ショートカットキーでオートフィルが可能です。
「Ctrl + Shift + End」 → 「Ctrl + d」で最下部まで式を反映させます。
<AMEXCEL SESSION="ExcelSession1" WORKBOOK="%GetDesktopDirectory()%\00013961\商品在庫状況.xlsx" />
<AMEXCEL ACTIVITY="activate_cell" SESSION="ExcelSession1" CELLREF="F2" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="=TEXT(A2,"000")&TEXT(B2,"00")&C2&D2" CELLREF="F2" />
<AMINPUT KEYSTROKES="{CTRL}{SHIFT}{END}" />
<AMINPUT KEYSTROKES="{CTRL}d" />
■3. 作業用列からデータを検索するセルを作成
今回は作業用列が最後列にあるため、VLOOKUP関数がそのままでは利用できません。
そのため、MATCH関数を使用します。
H2列を検索結果を表示するセル、I2列を検索する値を入力するセルとすると、以下の数式をH2列にセットします。
「=MATCH(I2, F1:F65536, 0)」
始点は1行目から、終点は十分に大きい値を指定し、完全一致で検索します。
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="=MATCH(I2, F1:F65536, 0)" CELLREF="H2" />
■4. 注文詳細を開き、データセットに格納
顧客注文詳細.xlsxを別のExcelSessionで開き、内容をデータセット化します。
※のちに「データセット名.CurrentRow」を使用する関係上、1行目からデータ行として取得しています。
2行目からデータセット化を実施すると、データの書き込み時に「データセット名.CurrentRow」に+1行して間隔調整を行う必要があります。
<AMEXCEL SESSION="ExcelSession2" WORKBOOK="%GetDesktopDirectory()%\00013961\顧客注文詳細.xlsx" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession2" ACTION="rangebyreference" RESULTDATASET="ds_detail" STARTCELLREF="A1" LASTCELL="YES" />
■5. ■4のデータセットに従い、ループ
「Loop - データセット」アクションを使用し、ループを開始します。
■4では、1行目のヘッダー行もデータ部分に含んでいるため、データセットの2行目から開始します。
<AMLOOP ACTIVITY="dataset" DATASET="ds_detail" FROM="2" />
-------------------ループ開始：ここから-------------------
■6. MATCH関数で検索する文字列を指定し、結果を取得
■3で設定した式において参照されているセルに、顧客注文詳細.xlsxにおいて指定されているキー項目
「商品コード」「ブランドコード」「サイズコード」「カラーコード」
を結合した値を入力します。
この際、「商品コード」は3桁、「ブランドコード」は2桁になるように先頭に0を追加しなくてはなりません。
そのため、「format」関数を利用して、桁調整を行いつつそれらの文字列を結合します。
※数字を先頭0埋め3桁にするには、「Format(ds_detail.A, "000")」のように指定します。
その後、Match関数の結果を変数「var_position」に格納します。
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="%Format(ds_detail.A, "000") & Format(ds_detail.B, "00") & ds_detail.C & ds_detail.D%" CELLREF="I2" />
<AMWAIT SCALAR="100" MEASURE="milliseconds" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" RESULTVARIABLE="var_position" CELLREF="H2" />
■7. 結果がヒットするか否かで処理を分ける
・Match関数の結果、結果なし(=「#N/A」)であれば「該当なし」と書込み
・Match関数の結果、数字が返ってくれば(=「#N/A」以外の場合)「該当行の値取得」「結果の書込み」「出荷OK/NGの判断」を書込み
結果のデータの書込み先は、現在のデータセットの処理中の行であるため、「ds_details.CurrentRow」で取得可能です。
また、書込み先の列は「F列」のため、「F%ds_details.CurrentRow%」で書込み先を指定します。
結果が見つかった際には、その数字を基にしてデータを取得します。
E列のデータを取得する為、商品在庫状況データのE列・「var_position」行からデータを取得し、「var_data」に格納します。
その後、在庫データが発注数よりも大きいかどうかを判別し、注文詳細のG列の該当行に書き込みを行います。
<AMIF EXPRESSION="%var_position% = "#N/A"" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="該当商品無し" CELLREF="F%ds_detail.CurrentRow%" />
<AMELSE />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" RESULTVARIABLE="var_data" CELLREF="E%var_position%" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%var_data%" CELLREF="F%ds_detail.CurrentRow%" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%If(var_data - ds_detail.E < 0, "出荷NG", "出荷OK")%" CELLREF="G%ds_detail.CurrentRow%" />
<AMIF ACTIVITY="end" />
-------------------ループ終了：ここまで-------------------
■8. ■5のループの終了
「Loop - End loop」で、■5のループを終了させます。
<AMLOOP ACTIVITY="end" />
■9. Excel 2種を閉じる
処理が終了した後、Excelを終了させます。
注文詳細は更新しているので保存し、在庫状況一覧は保存せずに終了します。
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />
今回構築する処理は以上になります。
(以下、構築例)
■処理構築例（展開するにはクリック）
<AMVARIABLE NAME="var_position" VALUE="" />
<AMVARIABLE NAME="var_data" VALUE="" />
<!--商品一覧開く-->
<AMEXCEL SESSION="ExcelSession1" WORKBOOK="%GetDesktopDirectory()%\00013961\商品在庫状況.xlsx" />
<AMEXCEL ACTIVITY="activate_cell" SESSION="ExcelSession1" CELLREF="F2" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="=TEXT(A2,"000")&TEXT(B2,"00")&C2&D2" CELLREF="F2" />
<AMINPUT KEYSTROKES="{CTRL}{SHIFT}{END}" />
<AMINPUT KEYSTROKES="{CTRL}d" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="=MATCH(I2, F1:F65536, 0)" CELLREF="H2" />
<!--注文詳細開く-->
<AMEXCEL SESSION="ExcelSession2" WORKBOOK="%GetDesktopDirectory()%\00013961\顧客注文詳細.xlsx" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession2" ACTION="rangebyreference" RESULTDATASET="ds_detail" STARTCELLREF="A1" LASTCELL="YES" />
<AMLOOP ACTIVITY="dataset" DATASET="ds_detail" FROM="2" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession1" NEWVALUE="%Format(ds_detail.A, "000") & Format(ds_detail.B, "00") & ds_detail.C & ds_detail.D%" CELLREF="I2" />
<AMWAIT SCALAR="100" MEASURE="milliseconds" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" RESULTVARIABLE="var_position" CELLREF="H2" />
<AMIF EXPRESSION="%var_position% = "#N/A"" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="該当商品無し" CELLREF="F%ds_detail.CurrentRow%" />
<AMELSE />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" RESULTVARIABLE="var_data" CELLREF="E%var_position%" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%var_data%" CELLREF="F%ds_detail.CurrentRow%" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession2" NEWVALUE="%If(var_data - ds_detail.E < 0, "出荷NG", "出荷OK")%" CELLREF="G%ds_detail.CurrentRow%" />
<AMIF ACTIVITY="end" />
<AMLOOP ACTIVITY="end" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession2" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />