本記事では、AutoMateでのExcel操作における、一時作業領域の利用法について記載します。
AutoMateでExcelの作業を扱う際、大量データの照合などの用途でExcelをデータセット化し、ループを回している場合などがあるかもしれません。
そうすると、まず目的のデータにたどり着くまでの間に大量のデータを読み飛ばし、ヒットする行を見つけるだけでも30秒かかる、といった状況になり得ます。
仮に対象データが存在しなかったとすると、時間をかけてデータセットを一周し、ムダなまま次のループに、ということになります。
データが多いほど効率が悪くなっていくので、タスクの実行時間もますます延びてしまいます。
その際、AutoMateのループ + IFなどAutoMateのアクションだけで完結させるのではなく、作業領域としてExcelを使用すると効率化につながる場合があります。
例えば、人の操作であれば、Excelで検索をかける際は「Ctrl + F」で検索窓を出して検索をかけたり、作業用の列に数式を入れたりします。
AutoMateでも同様のことをすればよく、作業用のセルやシートを使って処理をすれば思っているよりもタスクが単純になります。
以下のような2種のExcelがあるとします。
(↓のリンクからダウンロードし、任意の場所に解凍してください)
https://www-1.sct.co.jp/download/automate/Sample_Task/00009693/00009693.zip
商品の在庫状況一覧シートと、顧客からの注文書があり、顧客からの注文書をもとに在庫を調べて出荷可能かどうかを書込む処理を考えます。
ただし、商品数は10000を超えており、このリストを毎回上から順に調べていると日が暮れてしまいます。
また、商品コードは連番になってはいないので、仮に存在しない商品コードを入力されていると、10000行のデータ比較が全てムダになります。
↓こんなタスクの組み方をすると、単純に4件検索するだけでも5分を超えます(約7分、タスクアドミニストレーターからの手動実行時)。ヒットしたらbreakするように作れば少しは早くなりますが・・・。
<AMEXCEL SESSION="ExcelSession_Zaiko" WORKBOOK="%GetDesktopDirectory()%\00009693\商品在庫状況.xlsx" />
<AMEXCEL SESSION="ExcelSession_Chumon" WORKBOOK="%GetDesktopDirectory()%\00009693\顧客注文詳細.xlsx" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession_Zaiko" ACTION="rangebyreference" RESULTDATASET="ds_Zaiko" STARTCELLREF="A1" LASTCELL="YES" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession_Chumon" ACTION="rangebyreference" RESULTDATASET="ds_Chumon" STARTCELLREF="A1" LASTCELL="YES" />
<AMLOOP ACTIVITY="dataset" DATASET="ds_Chumon" FROM="2" />
<AMLOOP ACTIVITY="dataset" DATASET="ds_Zaiko" FROM="2" />
<AMIF EXPRESSION="%ds_Chumon.A% = %ds_Zaiko.A%" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession_Chumon" NEWVALUE="%ds_Zaiko.B%" CELLREF="C%ds_Chumon.CurrentRow%" />
<AMIF ACTIVITY="end" />
<AMLOOP ACTIVITY="end" />
<AMLOOP ACTIVITY="end" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession_Zaiko" SAVETYPE="do_not_save" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession_Chumon" />
一方、Excelは文字列検索が得意です。MATCH関数やVLOOKUP関数など、関数が豊富に用意されています。
そのため、Excelのシート上に作業領域を作成し、そこで関数を使えばよさそうです。
作業用のセルやシートは事前に用意しておく必要は無く、AutoMateで毎回実行時に作成してしまえば良いです。
■1. 作業用の式の組み立て
まず、MATCH関数で、目的のデータがそのシートの何行目にあるかを調べる方策を考えます。
Excelの式は、以下のように組み立てます。
「=MATCH(検索する値, シート名!$A$1:$A$30000, FALSE)」
検索範囲は、ヘッダーを含む1行目から、「ここまでデータが伸びることはない」部分を固定値で決めて構いません。
なおかつ、検索条件は「完全一致」とします。
今回は毎回「検索する値」の部分を書き換えて式を入力するのではなく、別のセルを参照して式の結果を決める方式を採用します。
そのため、今回利用する式は
「=MATCH(A1, シート名!$A$1:$A$30000, FALSE)」
とし、A1セルに検索値を書込む方式とします。
■2. AutoMateでの処理の流れ
検索用の式が決まれば、あとは処理の流れを考えます。
以下、AutoMateでの処理の流れです。
2-1. MATCH関数の結果を格納する変数および結果行のデータ(在庫)を格納する変数の作成
2-2. 注文データのデータセット化
2-3. 検索対象のブックに対して、作業用シートを作成(シート名：TEMP_DATAなど)
2-4. 作業用の式を2-3のシートのB1列などに書き込み
2-5. 2-2のデータセットに従いループ
2-6. 在庫状況の作業用シートのA1列に検索値(商品コード)を書込み
2-7. 2-4で式を入力したセルの値を取得
2-8. 2-7の結果が"#N/A"でなければその行のB列を取得し書込み、"#N/A"の場合は「該当なし」と書込み
2-9. ループ終わり
2-10. 在庫データは保存せず終了
2-11. 注文データを保存して終了
■3. その他
今回はExcelの「MATCH」関数を使用しましたが、「VLOOKUP」関数でも同様のことが可能です。
ただし、どちらの場合も、特定の列がユニークになっていることが前提となりますのでご留意ください。
■4. サンプル
以下、「MATCH」関数を使用した場合の一例です。
<!--変数宣言-->
<AMVARIABLE NAME="var_row" DESCRIPTION="MATCH関数の結果を格納する変数" />
<AMVARIABLE NAME="var_zaiko" DESCRIPTION="MATCH関数の結果、取得した行にある値を格納する変数" />
<!--ブックを開く-->
<AMEXCEL SESSION="ExcelSession_Zaiko" WORKBOOK="%GetDesktopDirectory()%\00009693\商品在庫状況.xlsx" />
<AMEXCEL SESSION="ExcelSession_Chumon" WORKBOOK="%GetDesktopDirectory()%\00009693\顧客注文詳細.xlsx" />
<!--注文データのデータセット化-->
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession_Chumon" ACTION="rangebyreference" RESULTDATASET="ds_Chumon" STARTCELLREF="A1" LASTCELL="YES" />
<!--在庫データに作業用シートを挿入-->
<AMEXCEL ACTIVITY="add_worksheet" SESSION="ExcelSession_Zaiko" WORKSHEETNAME="TEMP_DATA" LASTWORKSHEET="YES" />
<!--作業用シートに式をセット 検索対象のシート名は「Sheet1」なので、式の検索対象にセットする-->
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession_Zaiko" NEWVALUE="=MATCH(A1, Sheet1!$A$1:$A$30000, FALSE)" CELLREF="B1" WORKSHEET="TEMP_DATA" />
<AMLOOP ACTIVITY="dataset" DATASET="ds_Chumon" FROM="2" />
<!--データ検索-->
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession_Zaiko" NEWVALUE="%ds_Chumon.A%" CELLREF="A1" WORKSHEET="TEMP_DATA" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession_Zaiko" RESULTVARIABLE="var_row" CELLREF="B1" WORKSHEET="TEMP_DATA" />
<!--検索結果があれば在庫数量を取得、見つからない(#N/A)なら処理を飛ばす-->
<AMIF EXPRESSION="%var_row% = "#N/A"" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession_Chumon" NEWVALUE="該当なし" CELLREF="C%ds_Chumon.CurrentRow%" />
<AMELSE />
<!--MATCH関数の結果をもとにしてデータを取得する-->
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession_Zaiko" RESULTVARIABLE="var_zaiko" CELLREF="B%var_row%" WORKSHEET="Sheet1" />
<AMEXCEL ACTIVITY="set_cell" SESSION="ExcelSession_Chumon" NEWVALUE="%var_zaiko%" CELLREF="C%ds_Chumon.CurrentRow%" />
<AMIF ACTIVITY="end" />
<AMLOOP ACTIVITY="end" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession_Zaiko" SAVETYPE="do_not_save" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession_Chumon" />
大量データの照合などで時間がかかってしまっており、高速化したい、という場合には是非ともご活用ください。